Transcript PPT下载

第四讲 T-SQL语言中的存储过程(二)
实施
存储过程参数与状态值
存储过程和调用者之间通过参数交换数据，
可以按输入的参数执行，也可由参数输出执行结果。调
用者通过存储过程返回的状态值对存储过程进行管理。
存储过程的参数在创建过程时声明。SQL Server2005支
持两类参数：输入参数和输出参数。
(1)输入参数输入参数允许调用程序为存储过程传送数据值
。 要 定 义 存 储 过 程 的 输 入 参 数 ， 必 须 在 CREATE
PROCEDURE语句中声明一个或多个变量及类型。
创建带参数的存储过程，从表employee、sell_order、goods、
goods_classification的连接中返回输入的员工名、该员工
销售的商品名、商品类别、销售量等信息。
(1) 使用服务管理器创建用户自定义存储过程
在对象资源管理器管理器中选择要创建用户定义函数的数据库
（Sales），在可编程菜单中选择“存储过程”，右键单击
存储过程，在弹出的快捷菜单中选择“新建存储过程命令”
出现创建存储过程窗口。在文本框中可以输入创建存储过程
的Transact_SQL语句，单击“执行”按钮，即可创建该存储
过程。
（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
在服务管理器中使用查询方式执行以上SQL语句如图所示
实施
存储过程sell_info以@employee_name变量作为输入参数，执行时，可以
省略参数名，直接给参数值。在SQL查询分析器中输入命令：EXEC
sell_info '东方牧'运行结果如图所示。参数值可以包含通配符“%”
，例如，查找所有姓“钱”的员工的销售情况可以使用以下命令：
EXEC sell_info '钱%'
（2）输出参数输出参数允许存储过程将数据值或游标变量传
回调用程序。OUTPUT关键字用以指出能返回到调用它的批处
理 或 过 程 中 的 参 数 。 为 了 使 用 输 出 参 数 ， 在 CREATE
PROCEDURE 和 EXECUTE 语 句 中 都 必 须 使 用 OUTPUT 关 键 字 。
OUTPUT关键字用以指出能返回到调用它的批处理或过程中的
参数。为了使用输出参数，在CREATE PROCEDURE和EXECUTE
语句中都必须使用OUTPUT关键字。
例 创建存储过程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
返回存储过程的状态
(1)用RETURN语句定义返回值
存储过程可以返回整型状态值，表示
过程是否成功执行，或者过程失败的原因。
如果存储过程没有显式设置返回代码的
值，则SQL Server返回代码为 0，表示成功
执行；若返回-1~-99之间的整数，表示没有
成功执行。也可以使用RETURN语句，用大于0
或小于-99的整数来定义自己的返回状态值，
以表示不同的执行结果。
例
创建存储过程，输入商品类别，返回各种商品名称。在存储过程中，
用值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
(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 '没有找到该商品类别'
执行时，将对不同的输入值返回不同的状态值及处理结果。
实施
修改存储过程修改存储过程可以通过SQL Server 管理平台和Transact SQL语
句实现。1. 使用服务管理器修改存储过程用服务管理器修改用户定义
存储过程，选择要修改存储过程，单击右键，从快捷菜单中选择“修
改”选项，打开图所示的“修改”命令。从快捷菜单中还可以选择“
删除”选项，则可删除存储过程。
例使用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
(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
执行修改后的存储过程employee_dep
，结果如图：
从系统表sysobjects和syscomments提取
修改后的存储过程employee_dep的文本信息可以运
行步骤（2）中的代码，结果如图
这是由于在ALTER PROCEDURE语句中使用
WITH ENCRYPTION关键字对存储过程
employee_dep的文本进行了加密，其文本信息显
示为null。也可以使用系统存储过程sp_helptext显
示存储过程的定义，其命令格式如下：
sp_helptext employee_dep。我们可以再来看看结
果。
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
存储过程和函数的区别
1.如果返回多个参数值最好使用存储过程，如果只有
一个返回值可以使用函数。
2.函数一般不用变量形参，用函数名直接返回函数值
；而存储过程如有返回值则必须用变量形参返回。
3.存储过程无类型，不能给存储过程赋值，函数有类
型，最终要将函数值传给函数名。
4.函数定义是一定要进行函数类型的说明，存储过程
不进行类型的说明。
5.调用方式不同。函数的调用出现在表达式中，过程
的调用由独立的过程调用语句来完成。
6.存储过程一般会给设计成若干个运算结果，完成一
系列的数据处理，而函数往往只是为了求得一个函
数值。
创建带参数的存储过程
创建带参数的存储过程首先要在存储过程中声明该参
数，每个存储过程参数都必须用惟一的名称进行定
义。与标准的Transact-SQL变量相同，参数名必须
以@为前缀，并且遵从对象标识符规则。当用户不提
供该参数的值时可以使用一个默认值来代替。
在执行带参数的存储过程时，既可以通过显式指定参
数名称并赋予适当的值，也可以通过提供在CREATE
PROCEDURE语句中给定的参数值（不指定参数名称）
来向存储过程传递值。
本讲小结
存储过程可以接受参数、返回状态
值和参数值，并且实现嵌套调用。TransactSQL支持按位置和按名称两种方法传递参数。
按名字传递参数比按位置传递参数具有更多
的灵活性。但是按位置传递参数却具有更快
的速度。存储过程可以使用RETURN语句返回
整形状态值，表示过程是否成功执行，或者
过程失败的原因。存储过程可以使用alter语
句进行修改，也可以使用系统存储过程修改
并加密。存储过程在参数的功能和使用上跟
函数有一定的区别。