数据库技术与应用--SQLServer2005第10章存储过程与触发器.docVIP

第10章 存储过程与触发器 【例10-1】 创建存储过程，从表goods和表goods_classification的联接中返回商品名、商品类别、单价。 CREATE PROCEDURE goods_info AS SELECT goods_name,classification_name,unit_price FROM goods g INNER JOIN goods_classification gc ON g.classification_id = gc.classification_id EXEC goods_info 【例10-2】 使用ALTER PROCEDURE语句更改存储过程。 （1）创建存储过程employee_dep，以获取总经理办的男员工。 CREATE PROCEDURE employee_dep AS SELECT employee_name，sex，address，department_name FROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男 AND e.department_id=D001 GO SELECT o.id，c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = P AND o.name = employee_dep GO ALTER PROCEDURE employee_dep WITH ENCRYPTION AS SELECT employee_name，sex，address，department_name FROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男 GO sp_helptext employee_dep DROP PROCEDURE employee_dep GO 【例10-3】 创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。 CREATE PROC sell_info @employee_name varchar(20)AS SELECT employee_name,goods_name,classification_name,order_num FROM employee e INNER JOIN sell_order s ON e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification_id=g.classification_id WHERE employee_name LIKE @employee_name EXEC sell_info 东方牧 CREATE PROC myproc @val1 int，@val2 int，@val3 int AS … EXEC myproc 10,20,15 EXEC myproc @val2=20,@val1=10,@val3=15 【例10-4】 创建存储过程price_goods，通过输入参数在goods表中查找商品，以输出参数获取商品单价。 CREATE PROC price_goods @goods_name varchar(80)=NULL, @price_goods real OUTPUT AS SELECT @price_goods=unit_price FROM goods WHERE goods_name=@goods_name DECLARE @price real EXEC price_goods Canon LBP2900,@price OUTPUT SELECT @price 【例10-5】 创建存储过程，输入商品类别，返回各种商品名称。在存储过程中，用值15表示用户没有提供参数；值-101表示没有输入商品类别；值0表示过程运行没有出错。 /*存储过程在出错时设置出错状态*/ CREATE PROC cl_goods @cl_name varchar(40)=NULL AS IF @cl_name=NULL RETURN 15 IF NOT EXISTS (SELECT * FROM goods_classifica