总复习T-SQL练习.ppt

用户自定义函数编程
在SQL Server 2005中用户自定义函数作为一个数据库对象来管理,可以在Management Studio查询窗口中利用T-SQL命令来创建(CREATE FUNCTION),修改(ALTER FUNCTION)和删除(DROP FUNCTION)它。举例如下:
-- 定义
CREATE FUNCTION (***@D1 datetime,***@D2 datetime) RETURNS INT
AS
BEGIN
RETURN (SELECT cast((***@d2-***@d1) as int))
END
-- 使用
SELECT (getdate(),cast('2006-01-28' as datetime))
1
15、定义一个用户标量函数,用以实现判断并返回三个数中的最大数。
解:
CREATE FUNCTION [dbo].[MAXNUM]
(***@NUM1 INT,***@NUM2 INT,***@NUM3 INT) RETURNS INT
AS
BEGIN
DECLARE ***@I INT
SET ***@I=***@NUM1
IF (***@I<***@NUM2) SET ***@I=***@NUM2
IF (***@I<***@NUM3) SET ***@I=***@NUM3
RETURN ***@I
END
-- 使用举例
SELECT (123,434,543)
2
16、定义一个用户标量函数,用以实现判断并返回一个日期时间数据位于该年的第几天。
解:
CREATE FUNCTION [dbo].[dayofthisyear]
(***@date datetime)
RETURNS INT
AS
BEGIN
DECLARE ***@year INT,***@day INT,***@d1 DATETIME
SET ***@year=year(***@date)
SET ***@d1=cast((cast(***@year as varchar(4))+'-1-1') AS datetime)
SET ***@day=0
WHILE (***@d1<=***@date)
BEGIN
SET ***@day=***@day+1
SET ***@d1=***@d1+1
END
RETURN ***@day
END
3
存储过程和触发器编程
例题,设有订货数据库模式如下:
(1) 产品表Product(产品号p_id,产品名称p_name,成本单价price,
库存数量quantity)
(2) 订货商Customer(订货商号c_id,订货商名称c_name,所在城市city,
联系人person,联系电话c_telephone)
(3)订货表Order(订单号o_id,订货商号c_id,产品号p_id,订货单价o_price,
订货数量o_quantity,订货日期o_date)
,实现按订货商名称统计某年份该订货商订货总金额和盈利总金额的功能,输入参数是订货商名称和订货年份,输出参数是订货总金额和盈利总金额。
4
CREATE PROCEDURE GetCustomerSum
***@ame varchar(30),
***@l_oyear int,
***@l_orderSum numeric(10,2) OUTPUT,
***@l_gainSum numeric(10,2) OUTPUT
AS
BEGIN
SELECT ***@l_orderSum = SUM(o_price * o_quantity),
***@l_gainSum = SUM((o_price - price) * o_quantity)
FROM Order O JOIN Customer C ON (=)
Join Product P ON (=)
WHERE = ***@ame AND year() = ***@l_oyear;
END
5
-SQL程序,调用该存储过程,统计并输出订货商名称为大连海运公司在2010年的订货总金额和盈利总金额。
DECLARE ***@Lcname varchar(30),
***@Loyear int,
***@LorderSum numeric(10,2),
***@LgainSum numeric(10,2)
SET ***@Lcname=‘大连海运公司';
SET ***@Loyear=2013;
EXEC GetCustomerSum ***@Lcname, ***@Loyear ,***@LorderSum OUTPUT,***@LgainSum OUTPUT
--显示
SELECT ***@Lcname a