《SQL Server 2000管理与应用开发教程 教学课件 PPT 作者 王晶 第9章》由会员分享，可在线阅读，更多相关《SQL Server 2000管理与应用开发教程 教学课件 PPT 作者 王晶 第9章（99页珍藏版）》请在金锄头文库上搜索。
1、,SQL Server 2000管理及 应用开发教程,授课教师： 职务：,第9章 存储过程、触发器和游标,课程描述 本章将介绍SQL Server 2000中3个常用的对象，即存储过程、触发器和游标。使用SQL Server 2000开发数据库应用系统时，会经常用到这些对象。,本章知识点,存储过程 触发器 游标概述,9.1 存储过程,使用Transact-SQL程序时，可用如下两种方法存储和执行程序： 可以在本地存储程序，并创建向SQL Server发送命令并处理结果的应用程序。 也可以将程序在 SQL Server中存储为存储过程，创建执行存储过程及处理结果的应用程序。,9.1.1 存储过程
2、概述,存储过程是Transact-SQL语句的预编译集合，这些语句在一个名称下存储并作为一个单元进行处理。 使用SQL Server中的存储过程而不使用存储在客户计算机本地的Transact-SQL程序包括如下的优势： 1允许模块化程序设计 2可以更快地执行 3减少网络流量 4可作为安全机制使用,9.1.2 创建存储过程,1使用CREATE PROCEDURE语句 2使用企业管理器中的菜单命令,1使用CREATE PROCEDURE语句,CREATE PROCEDURE语句的作用是创建存储过程。它的语法结构如下： CREATE PROC EDURE 存储过程名 parameter data_t
3、ype VARYING = default OUTPUT AS SQL语句 .n ,【例9.1】,创建存储过程IncreaseWage，它的功能是将表EmpInfo中所有员工的工资数据增加10%，具体语句如下： USE HrSystem GO CREATE PROCEDURE IncreaseWage AS UPDATE EmpInfo SET Wage = Wage * 1.1 GO,2使用企业管理器中的菜单命令,在企业管理器中，展开要创建存储过程的数据库，例如本书使用的数据库HrSystem。右击“存储过程”节点，然后单击“新建存储过程”命令，打开新建存储过程对话框。,9.1.3 执行存储
4、过程,使用Transact-SQL语言的EXCUTE语句可以执行存储过程。EXCUTE语句的语法结构如下： EXEC UTE return_status = | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE ,【例9.2】,执行存储过程IncreaseWage，并查看执行结果，具体语句如下： USE HrSystem GO EXEC IncreaseWage SELECT * FROM EmpInfo GO,9.1.4 存储过程的参数,在定义存储过程时，可以同时指定参数，格
5、式如下： 参数名 数据类型 =默认值 OUTPUT, . n,【例9.3】,创建存储过程add_proc，它的功能计算两个参数之和并将其输出，具体语句如下： CREATE PROCEDURE add_proc num1 INT = 0, num2 INT = 0 AS DECLARE num3 INT SET num3 = num1 + num2 PRINT num3,【例9.3】,存储过程add_proc定义了两个参数num1和num2，它们都是输入参数，参数类型为INT，默认值为0。 在查询分析器中指定上面的命令，创建存储过程add_proc。然后再执行如下命令，运行存储过程： EXEC
6、add_proc GO 运行结果为： 0,【例9.3】,因为在执行存储过程时没有带参数，所以两个参数的值都被设置为默认值0，它们的和等于0。 在查询分析器中指定上面的命令，创建存储过程add_proc。然后再执行如下命令，运行存储过程： EXEC add_proc 13, 25 GO 运行结果为： 38,【例9.4】,创建存储过程add_proc1，它的功能是计算两个参数之和，并将结果使用输出参数返回，具体语句如下： CREATE PROCEDURE add_proc1 num1 INT = 0, num2 INT = 0, num3 INT OUTPUT AS SET num3 = num1
7、 + num2,【例9.4】,在查询分析器中执行如下代码，可以将存储过程add_proc1的输出参数值保存到num变量中。 DECLARE num AS INT EXEC add_proc1 12, 23, num OUTPUT PRINT num 运行结果为： 38,【例9.5】,创建存储过程AvgWage，它的功能是根据给定的部门名称计算平均工资，并将结果使用输出参数返回，具体语句如下： CREATE PROCEDURE AvgWage depname varchar(100), wage float OUTPUT AS DECLARE depid int SET depid = 0 -
8、根据参数中指定的部门名称depname, 获取部门编号 SELECT depid = Dep_Id FROM DepInfo WHERE Dep_Name=depname IF depid = 0 BEGIN SET wage = 0 PRINT 指定的院系记录不存在 END ELSE BEGIN SELECT wage = AVG(Wage) FROM EmpInfo WHERE Dep_Id=depid GROUP BY Dep_Id END GO,【例9.5】,在查询分析器中执行如下代码，可以将存储过程AvgWage的输出参数值保存到wage变量中，并打印平均工资数据。 DECLARE
9、wage float EXEC AvgWage 办公室, wage OUTPUT PRINT wage,9.1.5 存储过程的返回值,可以在存储过程中使用RETURN语句返回一个状态值，返回值只能是整数。,【例9.6】,创建存储过程AvgWage1，它的功能是根据给定的部门名称计算平均工资，并将结果使用输出参数返回。如果指定的部门存在，则返回1；否则返回0。具体语句如下： CREATE PROCEDURE AvgWage1 depname varchar(100), wage float OUTPUT AS DECLARE depid int SET depid = 0 - 根据参数中指定的部
10、门名称depname, 获取部门编号 SELECT depid = Dep_Id FROM DepInfo WHERE Dep_Name=depname IF depid = 0 RETURN 0 ELSE BEGIN SELECT wage = AVG(Wage) FROM EmpInfo WHERE Dep_Id=depid GROUP BY Dep_Id RETURN 1 END GO,【例9.6】,在查询分析器中执行如下代码，可以将存储过程AvgWage1的输出参数值保存到wage变量中。 DECLARE wage float DECLARE result int EXEC resul
11、t = AvgWage1 办公室, wage OUTPUT - 检查返回值 IF result = 1 PRINT wage ELSE PRINT 没有对应的记录,9.1.6 查看和修改存储过程,1使用企业管理器查看和修改存储过程 2使用查询分析器查看和修改存储过程 3使用企业管理器重命名存储过程 4使用ALTER PROCEDURE语句修改存储过程 5使用sp_rename重命名存储过程,1使用企业管理器查看和修改存储过程,在企业管理器中展开指定的数据库，选中“存储过程”项，可以查看指定数据库中定义的所有存储过程。 双击指定的存储过程，可以打开存储过程属性对话框，查看存储过程的定义情况。,2
12、使用查询分析器查看和修改存储过程,右键单击要编辑的存储过程，在弹出菜单中选择“编辑”，可以在右键的窗格中显示指定存储过程的代码,3使用企业管理器重命名存储过程,在企业管理器，展开“数据库”文件夹，选择存储过程所在的数据库，单击“存储过程”文件夹。在右侧的窗口中，列出了选择数据库的所有存储过程。 右击要重命名的存储过程，选择“重命名”菜单项，就可以在当前位置上修改存储过程的名字。,4使用ALTER PROCEDURE语句 修改存储过程,ALTER PROCEDURE语句的基本语法如下： ALTER PROC EDURE 存储过程名 参数名 数据类型 VARYING = default OUTPU
13、T ,.n WITH ENCRYPTION AS SQL语句 .n ,【例9.7】,使用ALTER PROCEDURE语句修改存储过程IncreaseWage，对其进行加密处理，具体语句如下： USE HrSystem GO ALTER PROCEDURE IncreaseWage WITH ENCRYPTION AS UPDATE EmpInfo SET Wage = Wage * 1.1 GO,5使用sp_rename重命名存储过程,系统存储过程sp_rename的功能是更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。它的语法结构如下： sp_rename objname
14、 = object_name , newname = new_name , objtype = object_type ,【例9.8】,将存储过程add_proc重命名为add_proc2，则可以使用以下命令： EXEC sp_rename add_proc, add_proc2 GO 运行结果为： 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 object 已重命名为 add_proc2。,9.1.7 删除存储过程,可以在企业管理器中，右击要删除的存储过程，选择“删除”命令，并确认删除。也可以使用DROP PROCEDURE语句来删除存储过程。 DROP PROCEDURE语句的语
15、法结构如下： DROP PROCEDURE 存储过程名 ,.n ,【例9.9】,删除存储过程add_proc，可以使用以下命令： DROP PROCEDURE add_proc GO,9.1.8 系统存储过程,1sp_databases 2sp_tables 3xp_cmdshell 4xp_logininfo 5xp_msver,1sp_databases,列出驻留在SQL Server数据库的基本情况。在查询分析器中执行此存储过程。,2sp_tables,列出当前环境下可查询的所有对象列表。在查询分析器中执行此存储过程。,【例9.10】,使用sp_tables存储过程返回所有用户表，可以使用如下命令： USE HrSystem GO EXEC sp_tables table_type = “TABLE“ GO,3xp_cmdshell,【例9.11】使用xp_ cmdshell存储过程执行命令，查看数据库服务器打开的端口及创建的连接，代码如下： USE master GO EXEC xp_cmdshell netstat -na GO,4xp_logininfo,列出当前账户的基本信息，包括账户类型、账户的特权级别、账户的映射登录名和账户访问 SQL Serv
