第08章存储过程和触发器.ppt

《第08章存储过程和触发器.ppt》由会员分享，可在线阅读，更多相关《第08章存储过程和触发器.ppt（35页珍藏版）》请在优知文库上搜索。

1、SQL Server 2005 SQL Server 2005 数据库数据库应用与开发应用与开发清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 第第08章章 存储过程和触发器存储过程和触发器 清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 第第08章章 存储过程和触发器存储过程和触发器 清华大学出版社清华大学出版社清华

2、大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 存储过程的主要用途：存储过程的主要用途：n提高了处理复杂任务的能力。提高了处理复杂任务的能力。n增强了代码的复用率和共享性。存储过程一旦增强了代码的复用率和共享性。存储过程一旦创建后即可在程序中调用任意多次。创建后即可在程序中调用任意多次。n减少了网络中数据的流量。存储过程在服务器减少了网络中数据的流量。存储过程在服务器注册，加快了过程的运行速度。注册，加快了过程的运行速度。n加强了系统的安全性。存储过程具有安全特性加强了系统的安全性。存储

3、过程具有安全特性（例如权限）和所有权链接，用户可以被授予（例如权限）和所有权链接，用户可以被授予权限来执行存储过程而不必直接对存储过程中权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。引用的对象具有权限。清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.1.1 存储过程的类型存储过程的类型SQL Server 2005支持的存储过程的类型主要有如下支持的存储过程的类型主要有如下4类。类。（1）系统存储过程。）系统存储过程。SQL Server

4、 2005 中的许多管中的许多管理活动都是存储过程执行的。理活动都是存储过程执行的。n从物理意义上讲，系统存储过程存储在源数据从物理意义上讲，系统存储过程存储在源数据库中，并且带有库中，并且带有 sp_ 前缀。前缀。n从逻辑上讲，系统存储过程出现在每个系统定从逻辑上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的义数据库和用户定义数据库的 sys 构架中。构架中。（2）用户定义的存储过程。用户可以自己创建存）用户定义的存储过程。用户可以自己创建存储过程。存储过程是指封装了可重用代码的模块储过程。存储过程是指封装了可重用代码的模块或例程。用户存储过程有两种类型：或例程。用户存储过程有两

5、种类型：清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.1.1 存储过程的类型存储过程的类型nTransact-SQL 存储过程是指保存的存储过程是指保存的 Transact-SQL 语句集合，可接受和返回用户提供的参数。语句集合，可接受和返回用户提供的参数。nCLR 存储过程是指对存储过程是指对 Microsoft.NET Framework 公共语言运行时公共语言运行时(CLR)方法的引用，方法的引用，可以接受和返回用户提供的参数可以接受和返回用户

6、提供的参数（3）临时存储过程。以）临时存储过程。以“#”或或“#”为前缀，表示局为前缀，表示局部临时存储过程和全局临时存储过程。部临时存储过程和全局临时存储过程。（4）扩展存储过程。以）扩展存储过程。以xp_为前缀，是为前缀，是SQL Server 2005的实例可以动态加载和运行的的实例可以动态加载和运行的 DLL。清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.1.2 存储过程的设计原则存储过程的设计原则用户创建存储过程时，应注意遵循以下几点原则。

7、用户创建存储过程时，应注意遵循以下几点原则。n存储过程最大不能超过存储过程最大不能超过128MB。n用户定义的存储过程只能在当前数据库中创建。用户定义的存储过程只能在当前数据库中创建。n存储过程是为了处理那些需要被多次运行的存储过程是为了处理那些需要被多次运行的Transact-SQL语句集。语句集。nSQL Server允许在存储过程创建时引用一个不存允许在存储过程创建时引用一个不存在的对象，系统只检查创建存储过程的语法。执在的对象，系统只检查创建存储过程的语法。执行时，存储过程引用了一个不存在的对象，则这行时，存储过程引用了一个不存在的对象，则这次执行操作将会失败。次执行操作将会失败。n存

8、储过程可以嵌套使用。嵌套的最大层次可以用存储过程可以嵌套使用。嵌套的最大层次可以用 NESTLEVEL函数来查看。函数来查看。清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.1.3 常用系统存储过程的使用常用系统存储过程的使用SQL Server 2005提供了许多系统存储过程提供了许多系统存储过程,下面介绍几种常下面介绍几种常用的系统存储过程。用的系统存储过程。（1）sp_helpdb 用于查看数据库名称及大小。用于查看数据库名称及大小。（2）sp_

9、helptext 用于显示规则、默认值、未加密的存储过用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。程、用户定义函数、触发器或视图的文本。（3）sp_renamedb 用于重命名数据库。用于重命名数据库。（4）sp_rename 用于更改当前数据库中用户创建对象（如表、用于更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。列或用户定义数据类型）的名称。（5）sp_helplogins查看所有数据库用户登录信息。查看所有数据库用户登录信息。（6）sp_helpsrvrolemember 用于以查看所有数据库用户所用于以查看所有数据库用户所属的角色信息。

10、属的角色信息。清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.2.1 创建存储过程创建存储过程1使用使用SQL Server Management Studio创建存储创建存储过程过程利用利用SQL Server Management Studio创建存储过程创建存储过程就是创建一个模板，通过改写模板创建存储过程。就是创建一个模板，通过改写模板创建存储过程。具体参考步骤如下。具体参考步骤如下。（1）启动）启动SQL Server Management

11、Studio，在对，在对象资源管理器中，展开象资源管理器中，展开“数据库数据库”|teaching|“可可编程性编程性”|“存储过程存储过程”。（2）如图）如图8.1所示，右击所示，右击“存储过程存储过程”节点，选择节点，选择“新建存储过程新建存储过程”菜单命令。菜单命令。清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.2.1 创建存储过程创建存储过程1使用使用SQL Server Management Studio创建存储过创建存储过程程（3）系统弹

12、出存储过程模板，如图）系统弹出存储过程模板，如图8.2所示，用户可所示，用户可以参照模板在其中输入合适的以参照模板在其中输入合适的Transact-SQL语句。语句。（4）单击工具栏中的）单击工具栏中的“执行执行”按钮，即可将存储过按钮，即可将存储过程保存在数据库中。程保存在数据库中。（5）刷新）刷新“存储过程存储过程”节点，可以观察到下方出现节点，可以观察到下方出现了新建的存储过程。了新建的存储过程。清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.2

13、.1 创建存储过程创建存储过程2使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程CREATE PROCEDURE语句的语法格式如下：语句的语法格式如下：CREATE PROC EDURE procedure_name ;number parameter_data_type VARYING =default OUTPUT ,n WITH RECOMPILE|ENCRYPTION FOR REPLICATION AS sql_statament ,n 例例8.1创建一个存储过程，输出所有学生的姓创建一个存储过程，输出所有学生的姓名、课程名称和期末成绩信息。名、课程名称和期末

14、成绩信息。程序代码如下程序代码如下:CREATE PROCEDURE student_scoreASSELECT sname,cname,final FROM student s,course c,score sc WHERE s.studentno=sc.studentno and c.courseno=sc.courseno清华大学出版社清华大学出版社清华大学出版社.SQL Server 2005.SQL Server 2005.SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 8.2.1 创建存储过程创建存储过程例例8.2创建一个存储过程，输出指定学生的姓名

15、及课创建一个存储过程，输出指定学生的姓名及课程名称、期末成绩信息。程名称、期末成绩信息。程序代码如下程序代码如下:CREATE PROCEDURE student_score1 student_name nchar(8)ASSELECT sname,cname,final FROM student s,course c,score sc WHERE s.studentno=sc.studentno and c.courseno=sc.courseno and.sname=student_name AS sql_statament ,n 例例8.3 创建一个存储过程，用输出参数返回指创建一个存储

16、过程，用输出参数返回指定学生的所有课程的期末成绩的平均值定学生的所有课程的期末成绩的平均值程序代码如下程序代码如下:CREATE PROCEDURE student_score2 student_name nchar(8),average numeric(6,2)OUTPUT ASSELECT average=AVG(final)FROM student s,course c,score sc WHERE s.studentno=sc.studentno and c.courseno=sc.coursenoand s.sname=student_name例例8.4 创建一个存储过程，用输出参数返回指定学生的所创建一个存储过程，用输出参数返回指定学生的所有课程的期末成绩的平均值，若不指定学生姓名，则返回有课程的期末成绩的平均值，若不指定学生姓名，则返回所有学生的所有课程的期末成绩的平均值。所有学生的所有课程的期末成绩的平均值。程序代码如下程序代码如下:CREATE PROCEDURE student_score3 student_name nchar(8)=NULL,average nu