sql 数据库 第十二章 SQL Server 2008存储过程.ppt

1、SQL Server 数据库程序设计,授课教师：姜 姗,存储过程和触发器,掌握存储过程的定义方法 掌握执行存储过程的方法 理解触发器的概念 掌握利用触发器实现数据完整性的方法,12.1 存储过程,12.1.1 存储过程概述 存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数） 来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。,存储过程是利用SQL Server所提供的Transact-SQL语言所编写的程序。存储过程的运用范围比较广

2、，可以包含几乎所有的T-SQL语句，例如数据库存取语句、流程控制语句、错误处理语句等，使用起来十分有弹性。 存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中供以后调用，以提高代码的执行效率。,存储过程已在服务器注册而且在运行时不需要再对存储过程进行编译，执行速度快、效率高。 存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。 存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应用程序不受 SQL Injection（SQL 注入）攻击。 存储过程允许模块化程序设计。存储过程

3、一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。 存储过程可以减少网络通信流量。一个需要数百行 Transact-SQL 代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。,使用存储过程有如下好处：,1. 用户定义的存储过程 用户定义的Transact-SQL存储过程中包含一组Transact-SQL 语句集合，可以接受和返回用户提供的参数。 2. 扩展存储过程 扩展存储过程是指 Microsoft SQL Server 的实例可以动态加载和运行的 DLL，是由用户使用编程语言（例如C）创建的自己的外部例程，扩展

4、存储过程一般使用sp_或xp_前缀。 3. 系统存储过程 由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_，例如常用的显示系统信息的sp_help存储过程。,存储过程的分类,12.1.2 创建存储过程,1. 利用Create Procedure语句创建 Create Procedure语句的语法格式为： Create Proc | Procedure procedure_name ; number parameter data_type Varying = default Out | Output Readonly ,.n With E

5、ncryption | Recompile For Replication As ; .n ;,说明： procedure_name：存储过程名。 ; number：是可选整数，用于对同名的过程分组。 parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 data_type：参数以及所属架构的数据类型。 VARYING：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变。仅适用于cursor参数。 default：参数的默认值。如果定义了 default 值，则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL

6、。,OUTPUT：指示参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。 ENCRYPTION：指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为加密格式。 RECOMPILE：指示数据库引擎不缓存该过程的计划，该过程在运行时编译。 FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。 ：存储过程执行的T-SQL语句。,例如：创建一个存储过程，查看student表里的所有记录，并运行该存储过程。其代码如下： CREATE PROC pr_studentCodes AS SELECT * FROM student G

7、O EXEC pr_studentCodes,具体的步骤为： 1）在选定的数据库下打开【可编程性】节点。 2）找到【存储过程】节点，单击鼠标右键，在弹出的快捷菜单中选择【新建存储过程】，如图12.1所示。 3）在新建的查询窗口中可以看到关于创建存储过程的语句模板，在其中添加相应的内容，单击工具栏的执行即可。,SQL Server Management Studio中创建存储过程,例如：使用SQL Server Management Studio创建一个存储过程pr_student，其作用是查看student数据库中某学生记录。 步骤一：打开“新建存储过程” 出现的创建存储过程的查询编辑器窗格。

8、,步骤二：单击菜单栏上的“查询”“指定模板参数的值”选项，弹出如下对话框。设置完毕后，单击“确定”按钮。,步骤三：将“- Add the parameters for the stored procedure here”下面第一行最后的逗号和第二行删除（因为没有参数，只有一个等号）。在“- Insert statements for procedure here”下输入T-SQL代码，这里输入“SELECT * FROM student WHERE sno=p1”。单击“执行”按钮完成操作，最后的结果如图。,12.1.3 修改和删除存储过程,1.利用对象资源管理器修改存储过程 具体的步骤为：

9、1）在对象资源管理器中口中，找到需要修改的存储过程节点，在其上单击鼠标右键，弹出快捷菜单，如图12.3所示。 2）在快捷菜单中，单击【修改】菜单，弹出修改窗口，可以在现有存储过程定义的基础上进行修改。 3）修改完成后，单击工具栏上的【执行】按钮，即可完成存储过程的修改。,2.利用Alter Procedure语句修改 Alter Procedure语句的语法格式如下： Alter Proc | Procedure schema_name. procedure_name ; number parameter type_schema_name. data_type Varying = defaul

10、t Out Put ,.n With Encryption | Recompile For Replication As ; .n ;,例如：修改pr_studentcodes存储过程，使其按照学号排序，其代码为： ALTER PROC pr_studentCodes AS SELECT * FROM student ORDER BY sno,3. 利用对象资源管理器删除存储过程 步骤为： 1）在对象资源管理器中口中，找到需要删除的存储过程节点，在其上单击鼠标右键，弹出快捷菜单，如图12.6所示。 2）在快捷菜单中，单击【删除】菜单，弹出确认删除窗口，选择【确定】即可删除。,4.使用Drop

11、Procedure语句删除函数 使用Drop Procedure语句可以从当前数据库中删除一个或多个存储过程或过程组，具体的语法形式如下。 Drop Proc | Procedure schema_name. procedure ,.n 例如，删除存储过程Pr_student，其代码如下： DROP PROC Pr_student,12.1.4 执行存储过程,若要执行存储过程，可以使用 EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行存储过程。 Execute语句的语法格式为： Exec | Execute return_status =

12、module_name ;number | module_name_var parameter = value | variable Output | Default ,.n With Recompile ,说明： return_statuts：可选的整型变量，存储模块的返回状态。这个变量在用于 EXECUTE 语句前，必须在批处理、存储过程或函数中声明过。 parameter：参数名 value：参数值 variable：用来存储参数或返回参数的变量。 OUTPUT：指定模块或命令字符串返回一个参数。该模块或命令字符串中的匹配参数也必须已使用关键字 OUTPUT 创建。使用游标变量作为参数时

13、使用该关键字。 DEFAULT：根据模块的定义，提供参数的默认值。,例如：执行pr_studentcodes存储过程 exec pr_studentCodes,Management Studio中执行存储过程,例如利用上面所建的存储过程pr_student查看stu_info数据库中某学生记录， 步骤一：在“对象资源管理器”窗格里，右击“pr_student”选项，在弹出的快捷菜单里选择“执行存储过程”选项，弹出“执行过程”对话框 步骤二：，这里可以看到该存储过程有哪些参数。在“值”文本框里输入p1的值“200515001”，然后单击“确定”按钮。,步骤三：系统弹出如图所示的运行存储过程的窗格

14、。窗格上半部分是SQL Server 2008自动添加的运行存储过程的代码，下面是运行的结果集。,2. 参数传递 如果存储过程含有参数并且没有指定默认值，则调用存储过程时必须对参数赋值，可以使用两种方式传递参数：按顺序赋值和按名赋值。 按顺序给参数进行赋值时，不需要给出参数的名称，并且调用语句中值的顺序必须和存储过程定义中参数定义的顺序保持一致。如果某参数有默认值，可以使用Default指明该参数使用默认值，如果该参数且位于参数列表的末尾，则Default可以省略。,例：建立存储过程并执行。 create proc pr_grade (sno char(9), cname char(8), g

15、rade int output) as select grade=grade from sc,course where o=o and sno=sno and cname=cname,顺序传参,declare score int exec pr_grade 200515002,操作系统,score output select score,按名称传参,declare score int exec pr_grade sno=200515002,cname=操作系统,grade=score output select score,3. 用存储过程返回数据 存储过程可以用两种方式向调用程序返回数据：利用输出参数和利用Return语句。 1）利用输出参数返回数据 如果在存储过程定义参数时使用了Output关键字，则存储过程执行结束时可以将参数的当前值返回给调用程序。,例：create proc pr_count (sdept varchar(8), num int output) as select num=count(*) from student where sdept=sdept,declare peop int exec pr_count sdept=CS,num=peop output select peop,2）利用Return语句返回执行状态 在