数据库课件补充存储过程

1、存储过程存储过程的概念创建与执行 存储过程的操作存储过程的概念一、存储过程 存储过程是由一些SQL语句和控制语句组成的被封装起来的过程，它驻留在数据库中。 可以被客户应用程序调用，也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似，存储过程可以通过名字来调用，而且它们同样有输入参数和输出参数。 在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。 系统存储过程 由系统提供的存储过程，可以作为命令执行各种操作。 系统存储过程定义在系统数据库master中，其前缀是sp_。用户存储过程 在用户数据库中创建的存储过程，用来完成用户指

2、定的数据库操作。 创建与执行一、创建 可以使用三种方法创建存储过程 ： 1、使用创建存储过程向导创建存储过程。 2、利用SQL Server 企业管理器创建存储过程。 3、使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。1、 使用创建存储过程向导创建存储过程 在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导” ，则出现欢迎使用创建存储过程向导对话框。根据以下各图提示可完成创建存储过程。图10-1 新建SQL Server组图10-2 欢迎使用创建存储过程向导对话框图10-3 选择数据库对话框 图10-4 选择数据库对象对话框图10-5 完成

3、创建存储过程向导对话框图10-6 编辑存储过程属性对话框图10-7 编辑存储过程SQL对话框2、. 使用企业管理器创建存储过程 、在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项，或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，均会出现创建存储过程对话框。 、在文本框中可以输入创建存储过程的SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮。图10-8 选择新建存储过程对话框（1

4、）图10-9 选择新建存储过程对话框（2）图10-10 新建存储过程对话框图10-11 设置权限对话框3、 使用T-SQL语句创建存储过程命令格式：CREATE PROCEDURE 存储过程名 参数 数据类型 长度 参数 数据类型 长度 OUTPUTAS SQL语句 二、执行命令格式：EXEC UTE 存储过程名 参数名= 参数值 参数值1，参数值2，根据返回值类型的不同，我们可以将存储过程分为三类：1）返回记录集的存储过程。返回记录集的存储过程的执行结果是一个记录集，典型的例子是从数据库中检索出符合某一个或几个条件的记录。2) 返回数值的存储过程（也可以称为标量存储过程）。返回数值的存储过程

5、执行完以后返回一个值，例如在数据库中执行一个有返回值的函数或命令；3) 行为存储过程。仅仅是用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。 例1、在XK数据库中创建一个名为P_Student的存储过程，该存储过程返回学生表中所有班级代码为20000001的记录。 USE Xk GO CREATE PROCEDURE p_Student AS SELECT * FROM Student WHERE ClassNo=20000001只返回单一记录集的存储过程例1的执行 USE Xk GO EXEC p_Student例2、在XK数据库中创建一个名为P_StudentPar

6、a的存储过程，该存储过程根据指定的班级返回该班级代码对应的Student表中的记录 CREATE PROCEDURE p_StudentPara ClassNo VARCHAR(8) AS SELECT * FROM Student WHERE ClassNo=ClassNo向存储过程中传递参数例2的执行 EXEC p_StudentPara 20000002 GO EXEC p_StudentPara ClassNo=20000001 GO 例3、创建存储过程P_ClassNum,要求能根据用户给定的班级代码，统计该班的人数，并将人数以输出变量返回给用户。 CREATE PROCEDURE

7、p_ClassNum ClassNo VARCHAR(8),ClassNum SMALLINT OUTPUT AS SET ClassNum= ( SELECT COUNT(*) FROM Student WHERE ClassNo=ClassNo ) GO 例3的执行DECLARE ClassNo VARCHAR(8),ClassNum SMALLINTSET ClassNo=20000001EXEC p_ClassNum ClassNo,ClassNumPRINT ClassNum存储过程的操作一、查看存储过程 存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存

8、放在系统表 ments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。2、使用系统存储过程来查看用户创建的存储过程 sp_help objname=存储过程名 显示存储过程的参数及其数据类型 sp_helptext objname=存储过程名 显示存储过程的源代码二、 修改存储过程命令格式： ALTER PROCEDURE 存储过程名 参数 数据类型 长度 参数 数据类型 长度 OUTPUT AS SQL语句1、使用企业管理器查看用户创建的存储过程 在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。 例：修改前边提到的例2ALTER PROCEDURE p_StudentParaClassName VARCHAR(20) ASSELECT ClassName,StuNo,StuName,Pwd FROM Student,ClassWHERE Student.ClassNo=Class.ClassNo AND ClassName LIKE %+ClassName+%三、重命名存储过程1、企业管理器 2、命令方式：sp