第十四章存储过程存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能，存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。存储过程具有以下优点：1、可以在单个存储过程中执行一系列SOL语句。2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。14.1存储过程的分类(1）系统存储过程系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_。(2）本地存储过程本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以sp_为前缀。(3）临时存储过程临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#"，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。（4）远程存储过程远程存储过程指从远程服务器上调用的存储过程。(5）扩展存储过程在SQLServer环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQLServer系统中，并且按照使用存储过程的方法执行。14.2户存储过程的创建与执行1.通过SQL命令创建和执行存储过程步骤如下：(1）定义如下存储过程USE schoolGOCREATEPROCEDUREaASSELECT student.sno,sname,course.cno,cname,degreeFROM student,score,courseWHERE student.sno=score.sno AND score.cno=course.cnoGO(2）调用存储过程EXECaGO1）创建存储过程
第十四章 存储过程 存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单 元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变 量、有条件执行以及其它强大的编程功能。 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返 回单个或多个结果集以及返回值。 存储过程具有以下优点： 1、可以在单个存储过程中执行一系列 SQL 语句。 2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。 3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 14.1 存储过程的分类 (1) 系统存储过程 系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程 定义在系统数据库 master 中，其前缀是 sp_。 (2) 本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据 库操作，其名称不能以 sp_为前缀。 (3) 临时存储过程 临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储 过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。 (4) 远程存储过程 远程存储过程指从远程服务器上调用的存储过程。 (5) 扩展存储过程 在 SQL Server 环境之外执行的动态链接库称为扩展存储过程，其前缀是 sp_。使用时 需要先加载到 SQL Server 系统中，并且按照使用存储过程的方法执行。 14.2 户存储过程的创建与执行 1. 通过 SQL 命令创建和执行存储过程 步骤如下： (1) 定义如下存储过程 USE school GO CREATE PROCEDURE a AS SELECT student.sno,sname,course.cno,cname,degree FROM student,score,course WHERE student.sno=score.sno AND score.cno=course.cno GO (2) 调用存储过程 EXEC a GO 1) 创建存储过程
语法格式：CREATE PROC[EDURE] procedure_name[; number]/*定义存储过程名*//*定义参数的类型*/[ @parameter data_ type ][VARYING ] [= default ] [OUTPUT]]/*定义参数的属性*/[,...][WITH(RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION)]/*定义存储过程的处理方式*[FORREPLICATION]ASsql_statements/*执行的操作*/2）存储过程的执行通过EXEC命令可以执行一个已定义的存储过程。语法格式：[EXEC[UTE]] [@return_status=](procedure_name[;number]I@procedure_name_var)[[@parameter=] value|@variable[OUTPUT]I[DEFAULT]][,..n]一、设计简单的存储过程例14.1：利用school数据库中的school表、score表和course表，编写一无参存储过程用于查询所有学生的选修课程成绩记录。CREATEPROCEDUREaASSELECT student.sno,sname,course.cno,cname,degreeFROM student,score,courseWHERE student.sno=score.sno AND score.cno=course.cnoEXECa二、使用带有参数的存储过程例14.2编写一存储过程，根据school数据库的student、score、course三个表查询指定学生的选修课程成绩情况。CREATEPROCEDUREb@snochar(5)ASSELECTstudent.sno,sname,course.cno,cname,degreeFROM student,score,courseWHERE student.sno=score.snoAND score.cno=course.cnoAND student.sno=@snoEXECb'101'三、带有通配符参数的存储过程例14.3利用shool数据库中student表、score、course表创建一存储过程c，查询指定课程名的选修情况。该存储过程在参数中使用了模糊查询，如果没有提供参数，则使用预设的默认值。CREATEPROCEDUREc@cnamevarchar(16)=%计算机%
语法格式： CREATE PROC [ EDURE ] procedure_name [ ; number ] /* 定义存储过程名*/ [ { @parameter data_type } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUTPUT ] ] /*定义参数的属性*/ [ ,.n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }] /*定义存储过程的处理方式*/ [ FOR REPLICATION ] AS sql_statements /*执行的操作*/ 2) 存储过程的执行 通过 EXEC 命令可以执行一个已定义的存储过程。 语法格式： [ EXEC [ UTE ] ] [ @return_status = ] { procedure_name [ ;number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } [ ,.n ] 一、设计简单的存储过程 例 14.1 利用 school 数据库中的 school 表、score 表和 course 表，编写一无参存储过程用于 查询所有学生的选修课程成绩记录。 CREATE PROCEDURE a AS SELECT student.sno,sname,course.cno,cname,degree FROM student,score,course WHERE student.sno=score.sno AND score.cno=course.cno EXEC a 二、使用带有参数的存储过程 例 14.2 编写一存储过程，根据 school 数据库的 student、score、course 三个表查询指定学 生的选修课程成绩情况。 CREATE PROCEDURE b @sno char(5) AS SELECT student.sno,sname,course.cno,cname,degree FROM student,score,course WHERE student.sno=score.sno AND score.cno=course.cno AND student.sno=@sno EXEC b '101' 三、带有通配符参数的存储过程 例 14.3 利用 shool 数据库中 student 表、score、course 表创建一存储过程 c，查询指定课程名 的选修情况。该存储过程在参数中使用了模糊查询，如果没有提供参数，则使用预设的默认 值。 CREATE PROCEDURE c @cname varchar(16)='%计算机%
ASSELECT student:sno,sname,course.cno,cname,degreeFROM student,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoANDcnameLIKE@cnameEXECcEXECc操作%EXECc"%计%四、带有OUTPUT参数的存储过程例14.4编写一存储过程，统计指定课程选修人数，存储过程中使用了输入和输出参数。CREATEPROCEDUREm@cnamechar(16),@numintoutputASSELECT@num=COUNT(sno)FROMscore,courseWHEREscore.cno-course.cnoANDcnameLIKE@cnameDECLARE@num intEXECm操作系统，@numoutputSELECT@numAS借阅次数五、带有OUTPUT游标参数的存储过程例14.5在school数据库的student表上声明并打开一个游标。CREATEPROCEDUREstudentcursor@student_curCURSORVARYINGOUTPUTASSET@student_cur=CURSORFORWARD_ONLYSTATICFORSELECT sno,sname,ssex,classFROM studentOPEN@student_curDECLARE @mycursor CURSOREXECstudent_cursor@student_cur=@mycursorOUTPUTFETCHNEXTFROM@mycursorWHILE@@FETCH STATUS=0FETCHNEXTFROM@mycursorCLOSE@mycursorDEALLOCATE@mycursor六、使用WITHENCRYPTION选项例14.6创建加密过程（使用sp_helptext系统存储过程获取关于加密的存储过程的信息）。CREATEPROCEDUREencryptionthisWITHENCRYPTIONASSELECT*FROM studentEXECsp_helptext'encryptionthis七、创建用户定义的系统存储过程例14.7创建存储过程sp_showtable,显示以student开头的所有表名及其对应的索引名。如
AS SELECT student.sno,sname,course.cno,cname,degree FROM student,score,course WHERE student.sno=score.sno AND score.cno=course.cno AND cname LIKE @cname EXEC c EXEC c '操作%' EXEC c '%计%' 四、带有 OUTPUT 参数的存储过程 例 14.4 编写一存储过程，统计指定课程选修人数，存储过程中使用了输入和输出参数。 CREATE PROCEDURE m @cname char(16),@num int output AS SELECT @num=COUNT(sno) FROM score,course WHERE score.cno=course.cno AND cname LIKE @cname DECLARE @num int EXEC m '操作系统',@num output SELECT @num AS 借阅次数 五、带有 OUTPUT 游标参数的存储过程 例 14.5 在 school 数据库的 student 表上声明并打开一个游标。 CREATE PROCEDURE student_cursor @student_cur CURSOR VARYING OUTPUT AS SET @student_cur=CURSOR FORWARD_ONLY STATIC FOR SELECT sno,sname,ssex,class FROM student OPEN @student_cur DECLARE @mycursor CURSOR EXEC student_cursor @student_cur=@mycursor OUTPUT FETCH NEXT FROM @mycursor WHILE @@FETCH_STATUS=0 FETCH NEXT FROM @mycursor CLOSE @mycursor DEALLOCATE @mycursor 六、使用 WITH ENCRYPTION 选项 例 14.6 创建加密过程（使用 sp_helptext 系统存储过程获取关于加密的存储过程的信息）。 CREATE PROCEDURE encryption_this WITH ENCRYPTION AS SELECT * FROM student EXEC sp_helptext 'encryption_this' 七、创建用户定义的系统存储过程 例 14.7 创建存储过程 sp_showtable,显示以 student 开头的所有表名及其对应的索引名。如
果没有指定参数，该存储过程将返回以course开头的所有表名及对应的索引名。CREATEPROCEDUREsp_showtable@tablevarchar(2O)=course%ASSELECT sysobjects.name,sysindexes.nameFROMsysobjects,sysindexesWHERE sysobjects.id=sysindexes.idANDsysobjects.nameLIKE@tableANDindid>0ANDindid<>255-EXECsp_showtableEXEC sp_showtable'student%2.利用企业管理器创建用户存储过程（1）在SQLServer企业管理器的目录树中，选中school数据库文件夹下的存储过程图标右击，出现一快捷菜单，选择菜单项“新建存储过程”，进入新建存储过程窗口，如图所示；(2）在新建存储过程窗口输入定义的存储过程，然后选择确定"按钮。存储过程屈性一新建存钻过程常规名称N《新建存储过程权限P国所有著创建日期文本虹CREATEPROCEDURE[OWNER](PROCEDURENAME】AS司45,1/1另存为模板(S)检查语法()确定取消帮助14.3用户存储过程的编辑修改使用ALTERPROCEDURE命令可修改已存在的存储过程。语法格式：ALTERPROC[EDURE]procedure_name[;number]I@parameterdata_type[ VARYING ][- default ][OUTPUT]][,...n][WITH(RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statements14.4用户存储过程的删除如果确认一个数据库的某个存储过程与其它对象没有任何依赖关系，则可用
果没有指定参数，该存储过程将返回以 course 开头的所有表名及对应的索引名。 CREATE PROCEDURE sp_showtable @table varchar(20)='course%' AS SELECT sysobjects.name,sysindexes.name FROM sysobjects,sysindexes WHERE sysobjects.id=sysindexes.id AND sysobjects.name LIKE @table AND indid<>0 AND indid<>255 ◼ EXEC sp_showtable ◼ EXEC sp_showtable 'student%' 2. 利用企业管理器创建用户存储过程 (1) 在 SQL Server 企业管理器的目录树中，选中 school 数据库文件夹下的存储过程图标右 击，出现一快捷菜单，选择菜单项“新建存储过程”，进入新建存储过程窗口，如图所示； (2) 在新建存储过程窗口输入定义的存储过程，然后选择“确定”按钮。 14.3 用户存储过程的编辑修改 使用 ALTER PROCEDURE 命令可修改已存在的存储过程。 语法格式： ALTER PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,.n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }] [ FOR REPLICATION ] AS sql_statements 14.4 用户存储过程的删除 如果确认一个数据库的某个存储过程与其它对象没有任何依赖关系，则可用
DROPPROCEDURE语句永久地删除该存储过程。语法格式：DROPPROCEDURE(procedure)[,..n]功能：从当前数据库中删除一个或多个存储过程或存储过程组。参数含义：procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除
DROP PROCEDURE 语句永久地删除该存储过程。 语法格式： DROP PROCEDURE { procedure } [ ,.n ] 功能：从当前数据库中删除一个或多个存储过程或存储过程组。 参数含义： procedure 指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存 储过程同时删除