第10章存储过程

1、第10章 存储过程关键词：存储过程创建存储过程执行存储过程存储过程的参数查看存储过程修改存储过程删除存储过程学习要求：本章主要阐述了存储过程的创建和使用方法。并且全面地、系统地介绍了存储过程的概念、存储过程的优点；系统存储过程的特点及用途；创建存储过程的方法；查看、修改和删除存储过程的方法。重点分析了存储过程的创建和使用方法。学习和掌握本章，是对SQL Server 2000数据库的灵活运用。10.1 概述、存储过程是为实现某个特定任务而编写的一段代码。、存储过程的特点： 可以包含一条或多条Transact-SQL语句。 可以接受输入参数并可以返回输出值。 一个存储过程可以

2、调用另一个存储过程。 会返回执行情况的状态代码给调用它的程序。、存储过程的优点： 实现模块化编程。一个存储过程可以被多个用户共享和重用。 加快程序的运行速度。第一次执行后的存储过程会在缓冲区中创建查询树，使得第二次执行时不用进行预编译。 可以减少网络流量。存储过程存储在服务器上，只有触发执行存储过程的命令和返回结果才在网络上传输。 可以提高数据库安全性。可以只授予用户访问存储过程的权限，而不授予其直接修改数据表的权限。、存储过程的分类： 系统存储过程。由系统自动创建，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。 用户自定义存储过程。由用户

3、创建并能完成某一特定功能的存储过程。10.2 创建存储过程、使用CREATE PROCEDURE语句创建存储过程格式：CREATE PROCEDURE  procedure_name ； number parameter data_typeVARYING  = defaultOUTPUT，nWITHRECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sq

4、l_statement n 其中各参数含义如下： procedure_name：新存储过程的名称。 number：对同名的过程分组。 parameter：过程中的参数。可以声明一个或多个参数。存储过程最多可以有2100个参数。 data_type：参数的数据类型。 VARYING：指定作为输出参数支持的结果集(由存储过程动态构造，内容可以变化)。仅适用于游标参数。 default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。 OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEUTE。使用OUTPUT参数可将信息返回给

5、调用过程。 RECOMPILE：表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。 ENCRYPTION：表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。 FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。 sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。创建存储过程时应该注意下面几点： 存储过程的最大大小为128MB。 用户定义的存储过程只能在当前数据库中创建(临时过程除外，临时过程总是在t

6、empdb中创建)。 在单个批处理中，CREATE PROCEDURE语句不能与其他Transact-SQL语句组合使用。 存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程。嵌套的最大深度不能超过32层。 存储过程如果创建了临时表，则该临时表只能用于该存储过程，而且当存储过程执行完毕后，临时表自动被删除。 创建存储过程时，“sq_statement”不能包含下面的Transact-SQL语句：SET SHOWPLAN_TEXT、SET SHOWMAN_ALL、CREATE VIEW、CREATE DEFAULT、CREATE

7、0;RULE、CREATE PROCEDURE和CREATE TRIGGER。例1：创建用于检索所有学生的成绩记录的存储过程stud_degree。USE school-判断stud_degree存储过程是否存在，若存在，则删除IF EXISTS (SELECT name FROM sysobjects WHERE name = stud_degree AND type =P) DROP PROCEDURE stud_degreeGOU

8、SE schoolGO-创建存储过程stud_degreeCREATE PROCEDURE stud_degree    /* 无参过程  */ASSELECT student.sno，student.sname，ame，score.degreeFROM student，course，scoreWHERE student.sno=score.sno AND o=oORDER BY student.snoGO通过下述SQL语句执

9、行该存储过程：USE school-判断stud_degree存储过程是否存在，若存在，则执行它IF EXISTS (SELECT name FROM sysobjectsWHERE name = stud_degree AND type =P) EXEC stud_degreeGO例2：创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在student表中查询此学生的信息。USE school-判断stud_info存储过程是否存

10、在，若存在，则删除IF EXISTS (SELECT name FROM sysobjects WHERE name = stud_info AND type =P) DROP PROCEDURE stud_infoGOUSE schoolGO-创建存储过程stud_infoCREATE PROCEDURE stud_infos_no char(5)      

11、60;         /* 有参过程·形参 */ASSELECT *FROM studentWHERE sno=s_noGO通过下述SQL语句执行该存储过程：USE school-判断stud_info存储过程是否存在，若存在，则执行它IF EXISTS (SELECT name FROM sysobjects WHERE name = stud_info

12、 AND type =P) EXEC stud_info 105 /*实参*/GO例3：创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在student表中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。USE school-判断stud_age存储过程是否存在，若存在，则删除IF EXISTS (SELECT name FROM sysobjects WHERE name =

13、0;stud_age AND type =P) DROP PROCEDURE stud_ageGOUSE schoolGO-创建存储过程stud_ageCREATE PROCEDURE stud_ages_no char(5),                /* 有参过程 */age int OUTPUT&

14、#160;           /* 返回参数 */AS- 定义并初始化局部变量，用于保存返回值DECLARE errorvalue intSET errorvalue=0- 求此学生的年龄SELECT age=YEAR(GETDATE()-YEAR(sbirthday)FROM studentWHERE sno=s_no- 根据程序的执行结果返回不同的值IF (ER

15、ROR<>0) SET errorvalue=ERRORRETURN errorvalue                 /* 带回结果值 */GO该过程的调用在后面介绍。、使用企业管理器创建存储过程使用企业管理器创建存储过程的操作步骤如下：（1）打开企业管理器，展开服务器组，并展开相应的服务器。（2）打开“数据库”文件夹，并打开要创建存储过程的数据库。（3）选择“存储过程

16、”选项，右击鼠标，执行“新建存储过程”命令，打开创建存储过程对话框。（4）在“文本”列表框中显示了CREATE PROCEDURE语句的框架，可以修改要创建的存储过程的名称，然后加入存储过程所包含的SQL语句。（5）单击“检查语法”按钮可以检查创建存储过程的SQL语句的语法是否正确。（6）如果要将其设置为下次创建存储过程的模板，可单击“另存为模板”按钮。（7）完成后，单击“确定”按钮即可创建一个存储过程，如下图10-1所示。图10-1存储过程属性界面、使用向导创建存储过程使用向导创建一个存储过程insert_table8_1，对应的操作步骤如下：（1）在企业管理器中，执行“工具”下拉

17、菜单中的“向导”命令，打开“选择向导”对话框。（2）在“数据库”文件夹选择“创建存储过程”向导，单击“确定”按钮，出现创建存储过程向导欢迎对话框。单击“下一步”按钮。（3）在出现“选择数据库”对话框中，选择数据库后，单击“下一步”按钮，如下图10-2所示。图10-2选择向导界面（3）在出现“选择数据库”对话框中，选择数据库后，单击“下一步”按钮，如下图10-3所示。图10-3创建存储过程向导1（4）在出现“选择存储过程”对话框中列出了所有表，以及可以对表进行的插入、删除和更新操作。可以通过选中每个表对应的复选框来确定要对表进行的操作。例如，选择table8表后面的“插入”、“删除”、“更新”栏

18、中的复选框。 单击“下一步”按钮，如下图10-4所示。图10-4创建存储过程向导2（5）出现“正在完成创建存储过程向导”对话框。若单击“完成”按钮，即可完成存储过程的创建，如下图10-5所示。图10-5创建存储过程向导3（6）单击“编辑”按钮，打开“编辑存储过程属性”对话框，可编辑存储过程，如下图10-6所示。图10-6编辑存储过程属性（7）单击“编辑SQL”按钮，即可打开“编辑存储过程SQL”对话框，其中的列表框显示了创建该存储过程的Transact-SQL语句，可以在已有的Transact-SQL语句的基础上进行编辑，可以单击“分析”按钮来执行语法检查，如下图10-7所示。图10

19、-7编辑存储过程 SQL（8）单击“确定”按钮，返回到前面的“正在完成创建存储过程向导”对话框。10.3 执行存储过程执行存储过程使用EXECUTE语句。格式：  EXECUTE  return_status =  procedure_name  ；number  | procedure_name_var    parameter =   value | variable

20、60; OUTPUT  |   DEFAULT  ，n  WITH RECOMPILE 主要参数含义： EXECUTE： 此语句是批处理的第一条语句时可以省略该关键字。 return_status： 是一个可选的整型变量，保存存储过程的返回状态。 procedure_name_var：是局部定义变量名，代表存储过程名称。 parameter：是过程参数(有参过程调用时使用)。 OUTPUT： 指定存储过程必须返回一个参数。 WITH R

21、ECOMPILE： 指定在执行存储过程时重新编译执行计划。10.4 存储过程的参数、使用参数例4：定义如下一个存储过程：CREATE PROCEDURE insert_table8_1(c1_1     int，           /*c1_1和c2_2是两个参数*/ c2_2     datetime)AS INSERT INTO&#

22、160;test.dbo.table8 (c1，c2)           VALUES (c1_1，c2_2)GO可以使用下面的SQL语句调用该存储过程：USE testGOEXEC insert_table8_1 2，2005-10-1GO或：EXEC insert_table8_1 c1_1=3，c2_2=2005-12-1、使用默认参数例5：创建一个存储过程insert_table8_2，该存储过程中包含两个参数，其默认值分别为

23、10和2005-5-1。USE testGODELETE table8         /*删除表中全部记录*/GOCREATE PROCEDURE insert_table8_2/*c1_1和c2_2是两个参数，分别设置了默认值*/ (c1_1 int=10，         c2_2 datetime=2005-5-1)AS INSERT IN

24、TO table8(c1，c2) VALUES (c1_1，c2_2)GO-执行存储过程insert_table8_2，不带参数EXEC insert_table8_2-执行存储过程insert_table8_2，带一个参数EXEC insert_table8_2 20-执行存储过程insert_table8_2，带二个参数EXEC insert_table8_2 30，2004-12-20GOSELECT * FROM table8GO执行结果如下：c1  

25、60;       c2                             - -10          2005-05-01

26、0;00:00:00.00020          2005-05-01 00:00:00.00030          2004-12-20 00:00:00.000可以看到，当调用存储过程时没有指定参数值时就自动使用相应的默认值。 、使用返回参数返回参数应用OUTPUT进行说明。例6：创建一个存储过程average，它返回两个参数st_name和st_avg，分别

27、代表姓名和平均分。USE schoolGOCREATE PROCEDURE average(st_no int，st_name char(8) OUTPUT，      /* 返回参数 */st_avg float OUTPUT          /* 返回参数 */)ASSELECT st_name=stu

28、dent.sname，st_avg=AVG(score.degree)FROM student，scoreWHERE student.sno=score.snoGROUP BY student.sno，student.snameHAVING student.sno=st_no执行该存储过程，查询学号为“108”的学生姓名和平均分：DECLARE st_name char(8)DECLARE st_avg floatEXEC average 108，st_name OUTPUT

29、，st_avg OUTPUTSELECT 姓名=st_name，平均分=st_avgGO执行结果为：姓名       平均分             - - 曾华       79.5、存储过程的返回值使用RETURN语句来指定一个返回值。例7： 创建存储过程test_ret，根据输入的参

30、数来判断返回值。USE testGOCREATE PROC test_ret(input_int int = 0         -参数，并设了默认值)ASIF input_int=0 RETURN 0           -如果输入的参数等于0，则返回0IF input_int>0 RETURN 1000

31、60;    -如果输入的参数大于0，则返回1000IF input_int<0  RETURN -1000    -如果输入的参数等于0，则返回-1000 执行该存储过程：DECLARE ret_int intEXEC ret_int=test_ret 1PRINT 返回值PRINT -PRINT ret_intEXEC ret_int=test_ret 0PRINT ret_intEXEC

32、0;ret_int=test_ret -1PRINT ret_int执行结果为：返回值-10000-1000例8：执行例3的存储过程stu_age，该存储过程有一个输入参数“学号”，另外还有一个输出参数age。这个值可以从变量errorvalue得到。USE schoolGODECLARE stuage int，returnvalue int，s_no char(10)SET s_no=105EXEC returnvalue=stu_age s_no，stuage OUTPUT -调用存储过程 stu_age  PRINT 本程序的执行结果：PRINT 程序的返回值=+CAST(returnvalue AS char(2)PRINT 学号为“+RTRIM(s_no)+”的学生的年龄是+CAST(stuage AS char(2)+岁。程序的执行结果如下：本程序的执行结果：程序的返回值=0学号为“105”的学生的年龄是31岁。函数与存储过程的区别：1、函数必须有且只能有一个返回值；存储