存储过程的创建和使用

存储过程的创建和使用第1页，共54页，2023年，2月20日，星期四本章学习目标了解存储过程的概念了解使用存储过程的优点了解系统存储过程的特点及用途掌握创建存储过程的方法掌握执行存储过程的方法掌握查看和修改存储过程的方法掌握删除存储过程的方法第2页，共54页，2023年，2月20日，星期四9.1概述第3页，共54页，2023年，2月20日，星期四9.1.1存储过程的概念在使用Transact-SQL语言编程的过程中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer服务器通过过程名来调用它们，这些过程就叫做存储过程。存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。第4页，共54页，2023年，2月20日，星期四9.1.1存储过程的概念存储过程有以下特点：存储过程中可以包含一条或多条Transact-SQL语句。存储过程可以接受输入参数并可以返回输出值。在一个存储过程中可以调用另一个存储过程。存储过程可以返回执行情况的状态代码给调用它的程序。第5页，共54页，2023年，2月20日，星期四9.1.2存储过程的优点使用存储过程有很多优点，具体如下：实现了模块化编程，一个存储过程可以被多个用户共享和重用。存储过程具有对数据库立即访问的功能。使用存储过程可以加快程序的运行速度。使用存储过程可以减少网络流量。。使用存储过程可以提高数据库的安全性。第6页，共54页，2023年，2月20日，星期四9.1.3存储过程的分类在SQLServer中的存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。系统存储过程：由系统自动创建，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。在SQLServer2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。用户自定义存储过程：是指封装了可重用代码的模块或例程，由用户创建，能完成某一特定的功能。可以接受输入参数，返回输出参数。第7页，共54页，2023年，2月20日，星期四9.1.3存储过程的分类在SQLServer2005中，用户自定义存储过程有两种类型：Transact-SQL存储过程和CLR存储过程。Transact-SQL存储过程：是指保存的Transact-SQL语句集合。CLR存储过程：是指对Microsoft.NETFramework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。第8页，共54页，2023年，2月20日，星期四9.2创建存储过程在SQLServer中，可以使用两种方法创建存储过程：当创建存储过程时，需要确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其他存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。第9页，共54页，2023年，2月20日，星期四9.2.1使用SQLServer管理控制台创建存储过程在SQLServer管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，右击其中的“存储过程”，在弹出的快捷菜单中选择“新建存储过程…”选项。第10页，共54页，2023年，2月20日，星期四9.2.1使用SQLServer管理控制台创建存储过程例9-2-1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在T_STUDENT表中查询05541班学生的学号、姓名、性别、出生日期和政治面貌五个字段的内容。在创建存储过程的窗口中，单击“查询”菜单，选择“指定模板参数的值”，会弹出“指定模板参数的值”对话框。在“指定模板参数的值”对话框中将“Procedure_Name”参数对应的名称修改为“StuInfo”，单击“确定”按钮，关闭此对话框。在创建存储过程的窗口中将对应的SELECT语句修改为以下程序代码：SelectS_NUMBERAS学号,S_NAMEAS姓名,SEXAS性别,BIRTHDAYAS出生日期,POLITYAS政治面貌FROMT_STUDENTWHERELEFT(S_NUMBER,5)='05541'输入完毕后，单击窗口工具栏上的“执行”按钮执行以上程序段，就会创建一个新的存储过程“StuInfo”。第11页，共54页，2023年，2月20日，星期四9.2.2使用Transact-SQL语句创建存储过程可以使用CREATEPROCEDURE命令创建存储过程，考虑下列几个事项：CREATEPROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。必须具有数据库的CREATEPROCEDURE权限。只能在当前数据库中创建存储过程。不要创建任何使用sp_作为前缀的存储过程。第12页，共54页，2023年，2月20日，星期四9.2.2使用Transact-SQL语句创建存储过程用CREATEPROCEDURE创建存储过程的语法如下：

CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][OUT|OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}]ASBEGINsql_statement

END第13页，共54页，2023年，2月20日，星期四9.2.2使用Transact-SQL语句创建存储过程其中，各参数的意义如下：schema_name：过程所属架构的名称。procedure_name：新存储过程的名称。@parameter：过程中的参数。[type_schema_name.]data_type：参数以及所属架构的数据类型。VARYING：指定作为输出参数支持的结果集。仅适用于cursor参数。第14页，共54页，2023年，2月20日，星期四9.2.2使用Transact-SQL语句创建存储过程default：参数的默认值。OUTPUT：指示参数是输出参数。{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}RECOMPILE：表明SQLServer不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。<sql_statement>：要包含在过程中的一个或多个Transact-SQL语句。第15页，共54页，2023年，2月20日，星期四创建存储过程1、创建普通的存储过程例：在student数据库中，创建一个名称为myproc的存储过程，该存储过程的功能是从数据表student_info中查询所有男同学的信息。USEstudentGOCREATEPROCEDUREmyprocASSELECT*FROMstudent_infoWHEREsex='男'GO第16页，共54页，2023年，2月20日，星期四2、创建带有参数的存储过程例：在student数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向数据表stud_info中插入一条记录，新记录的值由参数提供。第17页，共54页，2023年，2月20日，星期四USEstudentGOCREATEPROCEDUREInsertRecord(@snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10))ASINSERTINTOstud_infoVALUES(@sno,@sn,@sex,@age,@dept)GO

第18页，共54页，2023年，2月20日，星期四3、创建具有参数默认值的存储过程例：在student数据库中，创建一个名称为InsertRecordDef的存储过程，该存储过程的功能是向数据表student中插入一条记录，新记录的值由参数提供，如果未提供系别dept的值时，由参数的默认值代替。第19页，共54页，2023年，2月20日，星期四USEstudentGOCREATEPROCEDUREInsertRecordDef(@snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)='无')ASINSERTINTOstud_infoVALUES(@sno,@sn,@sex,@age,@dept)GO第20页，共54页，2023年，2月20日，星期四4、创建能够返回值的存储过程例：在student数据库中，创建一个名称为Query_Study的存储过程，该存储过程的功能是从数据表stud_info中根据学号查询某一同学的姓名和系别。第21页，共54页，2023年，2月20日，星期四USEstudentGOCREATEPROCEDUREQuery_Study(@snochar(6),@snchar(20)OUTPUT,@deptchar(10)OUTPUT)ASSELECT@sn=sn,@dept=deptFROMstud_infoWHEREsno=@snoGO

第22页，共54页，2023年，2月20日，星期四例9-2-2：创建一个存储过程StuScoreInfo，完成的功能是在表T_STUDENT、表T_COURSE和表t_SCORE中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。程序清单如下：--打开STUDENT数据库USESTUDENT--查询是否已存在此存储过程，如果存在，就删除它IFEXISTS(SELECTnameFROMsysobjectsWHEREname='StuScoreInfo'ANDtype='P')DROPPROCEDUREStuScoreInfoGO第23页，共54页，2023年，2月20日，星期四--创建存储过程CREATEPROCEDUREStuScoreInfoASSelect班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2),

学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2),S_NAMEAS姓名,SEXAS性别,T_COURSE.C_NAMEAS课程名称,t_SCORE.SCOREAS考试分数FROMT_STUDENT,T_COURSE,t_SCOREWHERET_STUDENT.S_NUMBER=t_SCORE.S_NUMBERANDT_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO第24页，共54页，2023年，2月20日，星期四例9-2-2：创建一个带有参数的存储过程Stu_Info，该存储过程根据传入的学生编号，在T_STUDENT中查询此学生的信息。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='Stu_Info'ANDtype='P')DROPPROCEDUREStu_InfoGO--创建存储过程USESTUDENTGO第25页，共54页，2023年，2月20日，星期四CREATEPROCEDUREStu_Info@S_NUMBERvarchar(10)ASSelect班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2),

学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2),S_NAMEAS姓名,SEXAS性别,BIRTHDAYAS出生日期,POLITYAS政治面貌FROMT_STUDENTWHERES_NUMBER=@S_NUMBERGO第26页，共54页，2023年，2月20日，星期四例9-2-3：创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的学生编号，在T_STUDENT中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='Stu_Age'ANDtype='P')DROPPROCEDUREStu_AgeGO--创建存储过程USESTUDENTGO第27页，共54页，2023年，2月20日，星期四CREATEPROCEDUREStu_Age@S_NUMBERvarchar(10),@AgeintOUTPUTAS--定义并初始化局部变量，用于保存返回值DECLARE@ErrorValueintSET@ErrorValue=0--求此学生的年龄SELECT@Age=YEAR(GETDATE())-YEAR(BIRTHDAY)FROMT_STUDENTWHERES_NUMBER=@S_NUMBER--根据程序的执行结果返回不同的值IF(@@ERROR<>0)SET@ErrorValue=@@ERRORRETURN@ErrorValueGO第28页，共54页，2023年，2月20日，星期四9.3执行存储过程存储过程创建成功后，保存在数据库中。在SQLServer中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name|@procedure_name_var}

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n]]}]第29页，共54页，2023年，2月20日，星期四9.3执行存储过程其中，各选项的含义如下：EXECUTE：执行存储过程的命令关键字。@return_status：是一个可选的整型变量，保存存储过程的返回状态。procedure_name：指定执行的存储过程的名称。@procedure_name_var：是局部定义变量名，代表存储过程名称。@parameter：是在创建存储过程时定义的过程参数。第30页，共54页，2023年，2月20日，星期四注意：如果省略EXECUTE关键字，则存储过程必须是批处理中的第一条语句，否则会出错。例：执行数据库student中的存储过程myproc。EXECUTEmyproc例：执行数据库student中的存储过程InsertRecord。EXECUTEInsertRecord@sno=‘S1’,@sn=‘王大伟’,@sex=‘男’,@age=18,@dept=‘计算机系’例：执行数据库student中的存储过程InsertRecordDefa。EXECUTEInsertRecord@sno='S10',@sn='高平',@sex='女',@age=18SELECT'姓名'=@sn,'系别'=@dept9.3执行存储过程第31页，共54页，2023年，2月20日，星期四例：执行数据库student中的存储过程Query_Study。DECLARE@snchar(20)DECLARE@deptchar(10)EXECUTEQuery_Study'S10',@snOUTPUT,@deptOUTPUT第32页，共54页，2023年，2月20日，星期四9.3执行存储过程例9-3-1：执行前面创建的StuInfo存储过程，它是一个无参的存储过程。程序清单如下：USESTUDENTEXECStuInfo或直接写存储过程的名称：USESTUDENTGOStuInfo第33页，共54页，2023年，2月20日，星期四9.3执行存储过程例9-3-2：执行存储过程StuScoreInfo。程序清单如下：USESTUDENTEXECStuScoreInfo第34页，共54页，2023年，2月20日，星期四9.3执行存储过程例9-3-3：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。程序清单如下：USESTUDENTGOEXECUTEStu_Info'0554101'或：USESTUDENTGOEXECUTEStu_Info@S_NUMBER='0554101'第35页，共54页，2023年，2月20日，星期四9.3执行存储过程例9-3-4：执行存储过程Stu_Age，该存储过程有一个输入参数“学号”，另外，还有一个输出参数@Age。存储过程执行完后，有一个返回的状态值，这个值可以从变量@ErrorValue得到。USESTUDENTGODECLARE@stuAgeint,@ReturnValueint,@S_NUMBERchar(10)SET@S_NUMBER='0554101'EXECUTE@ReturnValue=Stu_Age@S_NUMBER,@stuAgeOUTPUTPRINT'本程序的执行结果：'PRINT'程序的返回值='+CAST(@ReturnValueASchar(2))PRINT'学号为"'+RTRIM(@S_NUMBER)+'"的学生的年龄是'+CAST(@stuAgeASchar(2))+'岁。'第36页，共54页，2023年，2月20日，星期四9.3执行存储过程或：USESTUDENTGODECLARE@stuAgeint,@ReturnValueint,@S_NUMBER1char(10)SET@S_NUMBER1='0554101'EXECUTE@ReturnValue=Stu_Age@S_NUMBER=@S_NUMBER1,@Age=@stuAgeOUTPUTPRINT'本程序的执行结果：'PRINT'程序的返回值='+CAST(@ReturnValueASchar(2))PRINT'学号为"'+RTRIM(@S_NUMBER1)+'"的学生的年龄是'+CAST(@stuAgeASchar(2))+'岁。'第37页，共54页，2023年，2月20日，星期四9.4查看和修改存储过程第38页，共54页，2023年，2月20日，星期四9.4.1查看存储过程1．使用SQLServer管理控制台查看用户创建的存储过程在SQLServer管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，单击其中的“存储过程”，在右边的窗口中就会显示出当前数据库中的所有存储过程。第39页，共54页，2023年，2月20日，星期四9.4.1查看存储过程2．使用系统存储过程查看用户创建的存储过程sp_help：用于显示存储过程的参数及其数据类型sp_help[[@objname=]name]参数name为要查看的存储过程的名称。第40页，共54页，2023年，2月20日，星期四9.4.1查看存储过程sp_helptext：用于显示存储过程的源代码sp_helptext[[@objname=]name]参数name为要查看的存储过程的名称。第41页，共54页，2023年，2月20日，星期四9.4.1查看存储过程sp_depends：用于显示和存储过程相关的数据库对象sp_depends[@objname=]’object’参数object为要查看依赖关系的存储过程的名称。第42页，共54页，2023年，2月20日，星期四9.4.1查看存储过程sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_procedures[[@sp_name=]'name'][,[@sp_owner=]'owner'][,[@sp_qualifier=]'qualifier']其中，[@sp_name=]'name'用于指定返回目录信息的过程名；[@sp_owner=]'owner'用于指定过程所有者的名称；[@qualifier=]'qualifier'用于指定过程限定符的名称。第43页，共54页，2023年，2月20日，星期四9.4.1查看存储过程例9-4-1：使用系统存储过程查看Stu_Age存储过程的参数及其数据类型。例9-4-2：使用系统存储过程查看StuScoreInfo存储过程的相关的数据库对象。第44页，共54页，2023年，2月20日，星期四9.4.2修改存储过程1．使用SQLServer管理控制台修改存储过程使用SQLServer管理控制台可以很方便地修改存储过程的定义。在SQLServer管理控制台中，展开存储过程，右击要修改的存储过程，从弹出的快捷菜单中选择“修改”选项，则会出现与创建存储过程时类似的窗口。在该窗口中，可以直接修改定义该存储过程的Transact-SQL语句。第45页，共54页，2023年，2月20日，星期四9.4.2修改存储过程2．使用Transact-SQL语句修改存储过程使用ALTERPROCEDURE语句可以更改存储过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTER{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][[OUT[PUT]][,...n][WITHENCRYPTION]ASsql_statement[...n]第46页，共54页，2023年，2月20日，星期四9.4.2修改存储过程修改存储过程时，应该注意以下几点：如果原来的过程定义是使用WITHENCRYPTION创建的，那么只有在ALTERPROCEDURE中也包含这个选项时，这个选项才有效。每次只能修改一个存储过程。用ALTERPROCEDURE更改的存储过程的权限保持不变。第47页，共54页，2023年，2月20日，星期四例9-4-3：修改前面创建的Stu_Info存储过程，使之完成以下功能：根据传入的学号，在表T_STUDENT、表T_COURSE和表t_SCORE中查询此学生的班级、学号、姓名、性别、考试课程名称和考试分数。USESTUDENTGOALTERPROCEDUREStu_Info@S_NUMBERvarchar(10)ASSelect班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2),

学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2),S_NAMEAS姓名,