第四讲 T-SQL 语言中的存储过程 ( 二 )

1. 第四讲 T-SQL语言中的存储过程(二)

2. 实施 存储过程参数与状态值 存储过程和调用者之间通过参数交换数据，可以按输入的参数执行，也可由参数输出执行结果。调用者通过存储过程返回的状态值对存储过程进行管理。存储过程的参数在创建过程时声明。SQL Server2005支持两类参数：输入参数和输出参数。 (1)输入参数输入参数允许调用程序为存储过程传送数据值。要定义存储过程的输入参数，必须在CREATE PROCEDURE语句中声明一个或多个变量及类型。

3. 创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。 (1) 使用服务管理器创建用户自定义存储过程 在对象资源管理器管理器中选择要创建用户定义函数的数据库（Sales），在可编程菜单中选择“存储过程”，右键单击存储过程，在弹出的快捷菜单中选择“新建存储过程命令” 出现创建存储过程窗口。在文本框中可以输入创建存储过程的Transact_SQL语句，单击“执行”按钮，即可创建该存储过程。

4. （2）使用SQL语句创建存储过程 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

5. 在服务管理器中使用查询方式执行以上SQL语句如图所示在服务管理器中使用查询方式执行以上SQL语句如图所示

6. 实施 存储过程sell_info以@employee_name变量作为输入参数，执行时，可以省略参数名，直接给参数值。在SQL查询分析器中输入命令：EXEC sell_info '东方牧'运行结果如图所示。参数值可以包含通配符“%”，例如，查找所有姓“钱”的员工的销售情况可以使用以下命令：EXEC sell_info '钱%'

7. （2）输出参数输出参数允许存储过程将数据值或游标变量传回调用程序。OUTPUT关键字用以指出能返回到调用它的批处理或过程中的参数。为了使用输出参数，在CREATE PROCEDURE和EXECUTE语句中都必须使用OUTPUT关键字。 OUTPUT关键字用以指出能返回到调用它的批处理或过程中的参数。为了使用输出参数，在CREATE PROCEDURE和EXECUTE语句中都必须使用OUTPUT关键字。

8. 例创建存储过程price_goods，通过输入参数在goods表中查找商品，以输出参数获取商品单价。例创建存储过程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 EXECUTE语句还需要关键字OUTPUT以允许参数值返回给变量。 执行price_goods存储过程的代码如下： DECLARE @price real EXEC price_goods 联想',@price OUTPUT SELECT @price 运行结果是商品名为联想'的商品单价： 9500

9. 返回存储过程的状态 (1)用RETURN语句定义返回值 存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。 如果存储过程没有显式设置返回代码的值，则SQL Server返回代码为 0，表示成功执行；若返回-1~-99之间的整数，表示没有成功执行。也可以使用RETURN语句，用大于0或小于-99的整数来定义自己的返回状态值，以表示不同的执行结果。

10. 例 创建存储过程，输入商品类别，返回各种商品名称。在存储过程中，用值15表示用户没有提供参数；值-l01表示没有输入商品类别；值0表示过程运行没有出错。 CREATE PROC cl_goods @cl_name varchar(40)=NULL AS IF @cl_name=NULL RETURN 15 IF NOT EXISTS (SELECT * FROM goods_classification WHERE classification_name=@cl_name) RETURN -101 SELECT g.goods_name FROM goods_classification gc,goods g WHERE gc.classification_id =g.classification_id AND gc.classification_name=@cl_name RETURN 0

11. (2)捕获返回状态值 在执行过程时，要正确接收返回的状态值，必须使用以下语句； EXECUTE @status_var=procedure_name 上例的存储过程cl_goods执行时使用以下语句： DECLARE @return_status int EXEC @return_status=cl_goods ‘台式机’ IF @return_status=15 SELECT '语法错误' ELSE IF @return_status=-101 SELECT '没有找到该商品类别' 执行时，将对不同的输入值返回不同的状态值及处理结果。

12. 实施 修改存储过程修改存储过程可以通过SQL Server 管理平台和Transact SQL语句实现。1. 使用服务管理器修改存储过程用服务管理器修改用户定义存储过程，选择要修改存储过程，单击右键，从快捷菜单中选择“修改”选项，打开图所示的“修改”命令。从快捷菜单中还可以选择“删除”选项，则可删除存储过程。

13. 例使用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 执行存储过程employee_dep， (2) 查看employee_dep存储过程的文本信息 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

14. (3)使用ALTER PROCEDURE语句对employee_dep过程进行修改，使其能够显示出所有男员工，并使employee_dep过程以加密方式存储在表syscomments中 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

15. 执行修改后的存储过程employee_dep，结果如图：

16. 从系统表sysobjects和syscomments提取修改后的存储过程employee_dep的文本信息可以运行步骤（2）中的代码，结果如图 这是由于在ALTER PROCEDURE语句中使用WITH ENCRYPTION关键字对存储过程employee_dep的文本进行了加密，其文本信息显示为null。也可以使用系统存储过程sp_helptext显示存储过程的定义，其命令格式如下： sp_helptext employee_dep。我们可以再来看看结果。

17. 2.使用ALTER PROCEDURE语句修改存储过程LTER PROCEDURE语法规则是： ALTER PROC[EDURE ] procedure_name [ ; number ] [{@parameter data_type} [VARYING][=default] [OUTPUT]] [ ,...n ] [WITH { RECOMPILE | ENCRYPTION|RECOMPILE, ENCRYPTION}] [FOR REPLICATION]AS sql_statement [ ...n ] 删除存储过程 3.使用DROP PROCEDURE删除存储过程DROP PROCEDURE的语法如下：DROP PROCEDURE {procedure_name} [ ,...n ] 例如删除创建的存储过程sell_info：DROP PROCEDURE sell_info GO

18. 存储过程和函数的区别 1.如果返回多个参数值最好使用存储过程，如果只有一个返回值可以使用函数。 2.函数一般不用变量形参，用函数名直接返回函数值；而存储过程如有返回值则必须用变量形参返回。 3.存储过程无类型，不能给存储过程赋值，函数有类型，最终要将函数值传给函数名。 4.函数定义是一定要进行函数类型的说明，存储过程不进行类型的说明。 5.调用方式不同。函数的调用出现在表达式中，过程的调用由独立的过程调用语句来完成。 6.存储过程一般会给设计成若干个运算结果，完成一系列的数据处理，而函数往往只是为了求得一个函数值。

19. 创建带参数的存储过程 创建带参数的存储过程首先要在存储过程中声明该参数，每个存储过程参数都必须用惟一的名称进行定义。与标准的Transact-SQL变量相同，参数名必须以@为前缀，并且遵从对象标识符规则。当用户不提供该参数的值时可以使用一个默认值来代替。 在执行带参数的存储过程时，既可以通过显式指定参数名称并赋予适当的值，也可以通过提供在CREATE PROCEDURE语句中给定的参数值（不指定参数名称）来向存储过程传递值。

20. 本讲小结 存储过程可以接受参数、返回状态值和参数值，并且实现嵌套调用。Transact-SQL支持按位置和按名称两种方法传递参数。按名字传递参数比按位置传递参数具有更多的灵活性。但是按位置传递参数却具有更快的速度。存储过程可以使用RETURN语句返回整形状态值，表示过程是否成功执行，或者过程失败的原因。存储过程可以使用alter语句进行修改，也可以使用系统存储过程修改并加密。存储过程在参数的功能和使用上跟函数有一定的区别。