【精品】存储过程精品ppt课件.ppt

《【精品】存储过程精品ppt课件.ppt》由会员分享，可在线阅读，更多相关《【精品】存储过程精品ppt课件.ppt（102页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存储过程本章教学重点及要求n了解存储过程的概念、分类及优点。n掌握SQLServer2005中使用对象资源管理器创建和调用存储过程的方法。n掌握SQLServer2005中使用T-SQL语句创建和调用存储过程的方法。n掌握存储过程的查看、修改、删除和重命名等常用操作。第8章数据库基础8.1 存储过程概述8.2 存储过程的创建和执行8.3 查看和修改存储过程8.4 重命名存储过程8.5 删除存储过程8.6 存储过程的重新编译8.7 小结 8.1存储过程概述n存储过程（StoredProcedure）是指封装了服务器中T-SQL语句集合的数据库对象。对这些语句进行封装的目的就是便于以后的重复使用。

2、这些语句集合经过编译后存储在数据库中。以后，用户可以通过指定存储过程的名字并给出相应的参数（如果该存储过程带有参数）来执行它。n在编写存储过程时，数据库开发人员就可以使用SQLServer中所有主要的编程结构，如变量、数据类型、输入/输出参数、返回值、选择结构、循环结构、函数和注释等。8.1存储过程概述n8.1.1存储过程的分类n从功能上来看，SQLServer支持以下5种存储过程。n系统存储过程：系统存储过程主要存储在master数据库中，并且带有sp_前缀。系统存储过程主要从系统表中获取信息，从而为DBA管理SQLServer2005提供支持。n用户自定义存储过程：也称本地存储过程，是由用

3、户自行创建，并存储在用户数据库中的存储过程。8.1存储过程概述n8.1.2存储过程的优点n存储过程允许标准组件式编程n存储过程具有较快的执行速度n存储过程能够减少网络流量n存储过程可被作为一种安全机制来充分利用n存储过程允许进行模块化程序设计n存储过程可以自动完成需要预先执行的任务8.2存储过程的创建和执行n8.2.1系统表sysobjectsnSQLServer中，关于SQLServer数据库的一切信息都保存在它的系统表中，通常把这样的表称为元数据表。例如，在数据中创建的表、视图、用户自定义函数、存储过程、触发器等对象，都要在sysbojects中记录。n系统表sysobjects的主要字段

4、：Name（名称）、Id（标识符）和Type（类型）。8.2存储过程的创建和执行n8.2.2存储过程的创建n用T-SQL命令创建n语法格式：nCREATEPROC|PROCEDUREschema_name.procedure_name;numbernparametertype_schema_name.data_typenVARYING=defaultOUTPUT,.n8.2存储过程的创建和执行n8.2.2存储过程的创建n用T-SQL命令创建n语法格式：nWITH,.nnFORREPLICATIONnAS;.n|8.2存储过程的创建和执行n8.2.2存储过程的创建n用T-SQL命令创建n语法格式

5、：其中，procedure_option、sql_statement和method_specifier的定义如下：n:=ENCRYPTIONRECOMPILEEXECUTE_AS_Clausen:=BEGINstatementsENDn:=EXTERNALNAMEassembly_name.class_name.method_name8.2存储过程的创建和执行n8.2.2存储过程的创建n用T-SQL命令创建n参数含义：nSchema_name：存储过程所属架构名；nprocedure_name：新存储过程的名称。n；number：用于对同名过程进行分组的可选整数。nparameter：过程中的

6、参数。8.2存储过程的创建和执行n8.2.2存储过程的创建n用T-SQL命令创建n参数含义：ntype_schema_name.data_type：过程中的参数以及所属架构的数据类型。nVARYING：指定作为输出参数支持的结果集。ndefault：参数的默认值。nOUTPUT：指示参数是输出参数。8.2存储过程的创建和执行n8.2.2存储过程的创建n用T-SQL命令创建n参数含义：nRECOMPILE：指示该过程在运行时重新编译。nENCRYPTION：指示SQLServer将CREATEPROCEDURE语句的原始文本转换为密文格式。nEXECUTE_AS_Clause：指定在其中执行存储

7、过程的安全上下文。8.2存储过程的创建和执行n8.2.2存储过程的创建n用T-SQL命令创建n参数含义：nFORREPLICATION：使用FORREPLICATION选项创建的存储过程可用作存储过程筛选器，且只能在复制过程中执行。n：要包含在过程中的一个或多个T-SQL语句。n：用于创建CLR存储过程8.2存储过程的创建和执行n8.2.2存储过程的创建n用ManagementStudio创建n在对象资源管理器中，找到要创建存储过程的数据库，依次点击“可编程性可编程性”-“存储过程存储过程”。右击“存储过程存储过程”，选择“新建存储过程新建存储过程”，会在右面的查询编辑器中自动生成一个创建存储

8、过程的模板，进行修改，编写自己的源代码后，单击工具栏中的“执行执行”按钮即可。8.2存储过程的创建和执行n8.2.2存储过程的创建n例题n【例8-1】编写存储过程up_getallstudents，用于获取学生表students的所有记录。n代码如下：nUSEjxglnGO8.2存储过程的创建和执行n8.2.2存储过程的创建n例题nIFEXISTS(SELECTnameFROMsysobjectsWHEREname=up_getallstudentsANDtype=p)nDROPPROCEDUREup_getallstudentsnCREATEPROCEDUREup_getallstudent

9、snASSELECT*FROMstudents8.2存储过程的创建和执行n8.2.2存储过程的创建n例题n【例8-2】编写存储过程up_getcountofstudents,用于获取学生表student的记录个数。代码如下：nCREATEPROCEDUREup_getcountofstudentsnASnBEGINnSELECTcount(*)FROMstudentsnEND8.2存储过程的创建和执行n8.2.3存储过程的执行nSQLServer2005中执行存储过程有两种方式。n在工具栏中单击“新建查询新建查询”，然后在右面的查询编辑器界面中输入执行存储过程的T-SQL代码，单击工具栏中的“

10、执行执行”按钮。8.2存储过程的创建和执行n8.2.3存储过程的执行nSQLServer2005中执行存储过程有两种方式。n在对象资源管理器中，找到要执行的存储过程，右击该存储过程，选择“执行存储过程执行存储过程”，若无参数，则会直接执行。若有参数，会打开一个“执行过程执行过程”窗口。在该窗口的相应表格中，输入各个参数的值，然后单击“确定确定”按钮，即可执行。8.2存储过程的创建和执行n8.2.3存储过程的执行nSQLServer2005中执行存储过程有两种方式。n在Managestudio的工具栏中单击“新建新建查询查询”，然后在右面的查询编辑器界面中输入执行存储过程的T-SQL代码，单击工

11、具栏中的“执行执行”按钮。8.2存储过程的创建和执行n8.2.3存储过程的执行nExecute语句的语法nExecute可以用来执行系统存储过程、用户自定义存储过程或扩展存储过程等。在执行存储过程时，若语句是批处理中的第一条语句，可以省略Execute。Execute的语法如下：8.2存储过程的创建和执行n8.2.3存储过程的执行nExecute语句的语法nEXEC|EXECUTEreturn_status=nprocedure_name;numbern|procedure_name_varparmater=nvalue|variableOUTPUTDEFAULTn,nwithrecompil

12、e;8.2存储过程的创建和执行n8.2.3存储过程的执行nExecute语句的参数含义nreturn_status：可选的整型变量，用于保存存储过程的返回状态。nprocedure_name：调用存储过程的完全或不完全名称。8.2存储过程的创建和执行n8.2.3存储过程的执行nExecute语句的参数含义nnumber：可选的整数，用来对同名的存储过程进行分组。nprocedure_name_var：局部定义变量名，代表存储过程名称。nparameter：过程参数，在createprocedure语句中定义。8.2存储过程的创建和执行n8.2.3存储过程的执行nExecute语句的参数含义nv

13、alue：过程中参数的值。nvariable：用来保存参数或返回参数的变量。noutput：指定存储过程必须返回一个参数。ndefault：根据过程定义，提供参数的默认值。nwithrecompile：指定在执行存储过程是重新编译执行计划。8.2存储过程的创建和执行n8.2.3存储过程的执行n【例8-3】使用execute执行存储过程up_getallstudents。n首先打开managementstudio，在新建的查询编辑器窗口中输入代码：nEXECup_getallstudentsn点击“执行”按钮即可8.2存储过程的创建和执行n8.2.4带有参数的存储过程n存储过程可以带参数。通过参

14、数，存储过程可以接受参数，并把它们传递给过程中的语句，另外，数据也可以通过输出参数从存储过程返回。n创建带参数的存储过程首先要在存储过程中声明该参数。参数名以开始，遵循标识符规则，并且这些参数必须是Transcat-SQL的合法数据类型。当用户不提供该参数值时可以使用默认值来代替。8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n【例8-5】编写一个存储过程up_insertstudent，完成学生表students数据的插入。nCREATEPROCEDUREup_insertstudentnsidvarchar(15),snamevarchar(30),nssexchar(10

15、),sbirthdatetime,saddressvarchar(300),semailvarchar(50),smobilephonevarchar(50),shomephonevarchar(50)8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数nASBEGINnINSERTINTOjxgl.dbo.studentsn(stu_id,stu_name,stu_sex,stu_birthn,Stu_address,stu_email,stu_mobilephone,stu_homephone)VALUESn(sid,sname,ssex,sbirth,saddress,sema

16、il,smobilephone,shomephone)nEND8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n执行过程的代码如下：nUSEtestnGOnEXECup_insertstudent09,肖玉峰,男,1975-02-25,山东省滕州市木石镇,13073258271,33467598.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n【例8-6】编写存储过程up_delStudentByName，根据输入的学生姓名，删除该学生记录。nCREATEPROCEDUREup_delStudentByNamensnamevarchar(30)ASnBEGINnDEL

17、ETEFROMjxgl.dbo.StudentnWHEREstu_name=snamenEND8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n【例8-6】编写存储过程up_delStudentByName，根据输入的学生姓名，删除该学生记录。n调用存储过程的代码如下：nUSEjxglnGOnEXECup_delstudentbyname马啸天8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n【例8-7】编写一个存储过程up_getStudentInformationByName，根据输入的学生姓名，显示该学生的学号、姓名、课程名和成绩。nCREATEPROCEDU

18、REup_getStudentInformationByNamensnamevarchar(30)-参数定义nASnBEGIN8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n【例8-7】编写一个存储过程up_getStudentInformationByName，根据输入的学生姓名，显示该学生的学号、姓名、课程名和成绩。nSELECTs.stu_id,stu_name,cour_name,scoreFROMstudentss,coursesc,course_scorecsnWHEREs.stu_id=cs.stu_idANDncs.cour_id=c.cour_idANDstu

19、_name=snamenEND8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n【例8-7】编写一个存储过程up_getStudentInformationByName，根据输入的学生姓名，显示该学生的学号、姓名、课程名和成绩。n调用存储过程的代码如下：nUSEjxglnGOnEXECup_getStudentInformationByName陈琛军8.2存储过程的创建和执行n2带默认值的参数带默认值的参数n【例8-8】编写一个存储过程up_insertstudentwithdefault，给参数定义默认值，完成学生表students数据的插入。nCREATEPROCEDUREd

20、bo.up_insertstudentwithdefaultnsidvarchar(15),snamevarchar(30),-参数定义nssexchar(10)=男,sbirthdatetime,saddressvarchar(300)=,semailvarchar(50)=,smobilephonenvarchar(50)=,shomephonevarchar(50)=nAS8.2存储过程的创建和执行n2带默认值的参数带默认值的参数nBEGINnINSERTINTOjxgl.dbo.studentsn(stu_id,stu_name,stu_sex,stu_birthn,Stu_addre

21、ss,stu_email,stu_mobilephone,stu_homephone)nVALUES(sid,sname,ssex,sbirth,saddress,semail,nsmobilephone,shomephone)nEND8.2存储过程的创建和执行n2带默认值的参数带默认值的参数n调用存储过程的代码如下：nUSEjxglnGonEXECup_insertstudentwithdefaultsid=11,sname=赵小乐,sbirth=1976-07-058.2存储过程的创建和执行n3带输出参数带输出参数n【例8-9】编写一个存储过程up_getAvgScoreByName，根据

22、给定的学生姓名，获取该生的平均成绩。nCREATEPROCEDUREdbo.up_getAvgScoreByNamenanamevarchar(30),navgScoreintoutput-参数定义nAS8.2存储过程的创建和执行n3带输出参数带输出参数nBEGINnSELECTavgScore=avg(score)FROMstudentss,course_scorecsWHEREns.stu_id=cs.stu_idands.stu_name=anamenEND8.2存储过程的创建和执行n3带输出参数带输出参数n调用过程代码如下：nUSEjxglnGOnDECLAREavgScoreintn

23、EXECup_getAvgScoreByName陈琛军,avgScoreoutputnPRINTavgScorenEND8.2存储过程的创建和执行n4带返回值的存储过程带返回值的存储过程nprint语句：Print接受字符串表达式。其语法格式如下：nPRINTanyASCIItext|local_variable|function|string_exprn参数说明：nanyASCIItext：文本字符串nlocal_variable：字符串类型的局部变量nfunction：返回结果为char或varchar类型数据的函数nString_expr：返回字符串的表达式：8.2存储过程的创建和执行n

24、4带返回值的存储过程带返回值的存储过程n【例8-10】编写一个存储过程up_insertstudent2，在插入学生数据前，先判断一下学号是否存在，如果存在，输出“学号已经存在”的消息，否则，插入该学生数据，并返回“数据插入成功”的消息。nCREATEPROCEDUREdbo.up_insertstudent2nsidvarchar(15),snamevarchar(30),-参数定义nssexchar(10)=男,sbirthdatetime,saddressvarchar(300)=,8.2存储过程的创建和执行n4带返回值的存储过程带返回值的存储过程nsemailvarchar(50)=,

25、smobilephonevarchar(50)=,nshomephonevarchar(50)=nASnBEGINnIFEXISTS(SELECT*FROMstudentsWHEREstu_id=sid)nPRINT(要插入的学生的学号已经存在)nELSE8.2存储过程的创建和执行n4带返回值的存储过程带返回值的存储过程nBEGINnINSERTINTOjxgl.dbo.studentsn(stu_id,stu_name,stu_sex,stu_birthn,Stu_address,stu_email,stu_mobilephone,stu_homephone)nVALUESn(sid,sna

26、me,ssex,sbirth,saddress,semail,smobilephone,shomephone)nPRINT(恭喜，数据插入成功)nENDnEND8.2存储过程的创建和执行n4带返回值的存储过程带返回值的存储过程nreturn语句：return语句可以从过程、批处理或语句块中退出，不执行其后继语句。其语法格式如下：nRETURNinteger_expressionn参数说明：integer_expression是返回的整型值。注意：若无特别指明，存储过程返回0表示执行成功，非0值表示失败。8.2存储过程的创建和执行n1不带默认值的参数不带默认值的参数n【例8-6】编写存储过程up

27、_delStudentByName，根据输入的学生姓名，删除该学生记录。n调用存储过程的代码如下：nUSEjxglnGOnEXECup_delstudentbyname马啸天8.2存储过程的创建和执行n8.2.3存储过程的执行n【例8-3】使用execute执行存储过程up_getallstudents。n首先打开managementstudio，在新建的查询编辑器窗口中输入代码：nEXECup_getallstudentsn点击“执行”按钮即可8.2存储过程的创建和执行n8.2.3存储过程的执行n【例8-3】使用execute执行存储过程up_getallstudents。n首先打开mana

28、gementstudio，在新建的查询编辑器窗口中输入代码：nEXECup_getallstudentsn点击“执行”按钮即可8.2存储过程的创建和执行n8.2.3存储过程的执行n【例8-4】使用对象资源管理器执行存储过程up_getcountofstudents。n在对象资源管理器中，首先找到要执行的存储过程“up_getcountofstudents”，右击，选择“执行存储过程”。8.2存储过程的创建和执行n8.2.3存储过程的执行n在随后出现的如右图所示的窗口中，输入各个参数的值（若有），后单击“确定”按钮即可。8.2存储过程的创建和执行n8.2.4带参数的存储过程n通过参数，存储过程可

29、以接受参数，并把它们传递给过程中的语句，另外，数据也可以通过输出参数从存储过程返回。创建带参数的存储过程首先要在存储过程中声明该参数，其命名规范与T-SQL的局部变量相同。8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数n对于不带默认值参数的存储过程，当调用该存储过程时，必须对存储过程中的所有参数进行赋值，否则，无法正常调用。n例8-5：编写一个存储过程up_insertstudent，完成学生表students数据的插入。8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数n代码如下：nCREATEPROCEDUREup_insertstudentnsidvarc

30、har(15),snamevarchar(30),-参数定义nssexchar(10),sbirthdatetime,saddressvarchar(300),nsemailvarchar(50),smobilephonevarchar(50),nshomephonevarchar(50)nAS8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数nBEGINnINSERTINTOjxgl.dbo.studentsn(stu_id,stu_name,stu_sex,stu_birth,nStu_address,stu_email,stu_mobilephone,stu_homepho

31、ne)nVALUES(sid,sname,ssex,sbirth,saddress,semail,nsmobilephone,shomephone)nEND8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数n调用代码如下：nUSEtestnGOnEXECup_insertstudent09,肖玉峰,男,1975-02-25,山东省滕州市木石镇,13073258271,33467598.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数n例8-6：编写存储过程up_delStudentByName，根据输入的学生姓名，删除该学生记录。n代码如下：nCREATEPROCED

32、UREup_delStudentByNamensnamevarchar(30)-参数定义nASnBEGINnDELETEFROMjxgl.dbo.StudentnWHEREstu_name=snamenEND8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数n调用存储过程的代码如下：nUSEjxglnGOnEXECup_delstudentbyname马啸天8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数n例8-7：编写存储过程up_getStudentInformationByName，根据输入的学生姓名，显示该学生的学号、姓名、课程名和成绩。n代码如下：nCR

33、EATEPROCEDUREup_getStudentInformationByNamensnamevarchar(30)-参数定义nAS8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数BEGINSELECTs.stu_id,stu_name,cour_name,scoreFROMstudentss,coursesc,course_scorecsWHEREs.stu_id=cs.stu_idANDcs.cour_id=c.cour_idANDstu_name=snameEND调用存储过程的代码如下：USEjxglGOEXECup_getStudentInformationByNa

34、me陈琛军8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数nBEGINnSELECTs.stu_id,stu_name,cour_name,scoreFROMstudentss,coursesc,course_scorecsnWHEREs.stu_id=cs.stu_idANDcs.cour_id=c.cour_idANDstu_name=snamenEND8.2存储过程的创建和执行n1.不带默认值的参数不带默认值的参数n调用存储过程的代码如下：nUSEjxglnGOnEXECup_getStudentInformationByName陈琛军8.2存储过程的创建和执行n2.带默

35、认值的参数带默认值的参数n例8-8：编写存储过程up_insertstudentwithdefault，给参数定义默认值，完成学生表students数据的插入。nCREATEPROCEDUREdbo.up_insertstudentwithdefaultnsidvarchar(15),snamevarchar(30),-参数定义nssexchar(10)=男,sbirthdatetime,saddressvarchar(300)=,nsemailvarchar(50)=,smobilephonevarchar(50)=,nshomephonevarchar(50)=nAS8.2存储过程的创建和

36、执行n2.带默认值的参数带默认值的参数nBEGINnINSERTINTOjxgl.dbo.studentsn(stu_id,stu_name,stu_sex,stu_birth,Stu_address,stu_email,stu_mobilephone,stu_homephone)VALUESn(sid,sname,ssex,sbirth,saddress,semail,nsmobilephone,shomephone)nEND8.2存储过程的创建和执行n2.带默认值的参数带默认值的参数n调用存储过程的代码如下：nUSEjxglnGonEXECup_insertstudentwithdefau

37、ltsid=11,sname=赵小乐,nsbirth=1976-07-058.2存储过程的创建和执行n3.带输出参数带输出参数n在创建存储过程时，可以用关键字output来创建一个输出参数。另外，在调用带输出参数的存储过程时，也必须给出output关键字。n例8-9：编写一个存储过程up_getAvgScoreByName，根据给定的学生姓名，获取该生的平均成绩。nCREATEPROCEDUREdbo.up_getAvgScoreByNamenanamevarchar(30),navgScoreintoutput-参数定义nAS8.2存储过程的创建和执行n3.带输出参数带输出参数nBEGINn

38、SELECTavgScore=avg(score)FROMstudentss,course_scorecsWHEREns.stu_id=cs.stu_idands.stu_name=anamenEND8.2存储过程的创建和执行n3.带输出参数带输出参数n调用存储过程的代码如下：nUSEjxglnGOnDECLAREavgScoreintnEXECup_getAvgScoreByName陈琛军,avgScoreoutputnPRINTavgScore8.2存储过程的创建和执行n4.带返回值参数带返回值参数n在调用存储过程时，用户有时需要根据存储过程的返回值判断存储过程执行是否成功。存储过程中，返

39、回值的方式有以下几种：nprint语句：print语句可以将用户定义的消息返回给客户端。Print可接受字符串表达式。其语法格式如下：nPRINTanyASCIItext|local_variable|function|string_exprn其中：参数分别表示本字符串、字符串类型的局部变量、返回结果为char或varchar类型数据的函数或返回字符串的表达式8.2存储过程的创建和执行n4.带返回值参数带返回值参数n例8-10：编写存储过程up_insertstudent2，在插入学生数据前，先判断一下学号是否存在，如果存在，输出“学号已经存在”的消息，否则，插入该学生数据，并返回“数据插入成

40、功”的消息。n代码如下：nCREATEPROCEDUREdbo.up_insertstudent2nsidvarchar(15),snamevarchar(30),-参数定义nssexchar(10)=男,sbirthdatetime,saddressvarchar(300)=,8.2存储过程的创建和执行n4.带返回值参数带返回值参数nsemailvarchar(50)=,smobilephonevarchar(50)=,nshomephonevarchar(50)=nASnBEGINnIFEXISTS(SELECT*FROMstudentsWHEREstu_id=sid)nPRINT(要插入

41、的学生的学号已经存在)nELSE8.2存储过程的创建和执行n4.带返回值参数带返回值参数nBEGINnINSERTINTOjxgl.dbo.studentsn(stu_id,stu_name,stu_sex,stu_birth,Stu_address,stu_email,stu_mobilephone,stu_homephone)VALUESn(sid,sname,ssex,sbirth,saddress,semail,nsmobilephone,shomephone)nPRINT(恭喜，数据插入成功)nENDEND8.2存储过程的创建和执行n4.带返回值参数n调用过程代码如下：nUSEjxg

42、lnGOnEXECup_insertstudent2sid=09,sname=张小飞,sbirth=1983-02-01,ssex=男8.2存储过程的创建和执行n4.带返回值参数nreturn语句：return语句可以从过程、批处理或语句块中退出，不执行其后继语句。其语法格式如下：nRETURNinteger_expressionn其中，返回值为整型表达式。n例8-11：编写存储过程up_delStudentByName2，根据输入的学生姓名，删除该学生记录。该过程返回delete语句影响的数据行数。8.2存储过程的创建和执行n4.带返回值参数nCREATEPROCEDUREup_delStu

43、dentByName2nsnamevarchar(30)-参数定义nASnBEGINnDELETEFROMjxgl.dbo.Studentsn WHEREstu_name=snamenRETURNrowcountnEND8.2存储过程的创建和执行n4.带返回值参数n调用存储过程的代码如下：nUSEjxglnGOnDECLAREret_valintnEXECret_val=up_delstudentbyname2肖玉峰nSELECTret_val8.2存储过程的创建和执行n5带变量的存储过程n在存储过程中可以定义局部变量（变量名），用于保存存储过程中的临时结果。n例8-12：编写存储过程up_g

44、etAvgScoreByName2，根据输入的学生姓名，计算该学生的平均成绩。根据该生平均成绩与全体学生平均成绩的关系，返回相应信息。nCREATEPROCEDUREup_getAvgScoreByName2nanamevarchar(30),nresStrvarchar(30)output8.2存储过程的创建和执行n5带变量的存储过程nASnBEGINnDECLAREcurAvgdecimal(18,2)nDECLAREtotalAvgdecimal(18,2)nSELECTtotalAvg=avg(score)FROMcourse_scorenSELECTcurAvg=avg(score)

45、FROMstudentss,course_scorecsWHERE8.2存储过程的创建和执行n5带变量的存储过程ns.stu_id=cs.stu_idands.stu_name=anamenIFcurAvgtotalAvgnSETresStr=高于平均分nELSEnSETresStr=低于平均分nPRINT(总平均分为+CONVERT(varchar(18),totalAvg)8.2存储过程的创建和执行n5带变量的存储过程nPRINT(该生平均分为+CONVERT(varchar(18),curAvg)nPRINT(resStr)nENDn调用存储过程的代码如下：nUSEjxglnDECLAR

46、Eresstringvarchar(30)nEXECup_getAvgScoreByName2陈琛军,resstringoutput8.2存储过程的创建和执行n6使用OUTPUT游标参数nOUTPUT游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器。n例8-13:编写一个带有output游标参数的存储过程up_getstudent_cursor，再 编 写 一 个 过 程up_printstudentbycursor，对游标中的数据进行显示。8.2存储过程的创建和执行n6使用OUTPUT游标参数n存储过程getstudent_cursor的代码如下：nCREATEPRO

47、CEDUREdbo.getstudent_cursornstudent_cursorCURSORVARYINGOUTPUTnASnBEGINnSETstudent_cursor=CURSORnFORWARD_ONLYSTATICFORnSELECTstu_id,stu_name,stu_birth,stu_sexnFROMstudents;8.2存储过程的创建和执行n6使用OUTPUT游标参数n存储过程getstudent_cursor的代码如下：nOPENstudent_cursor;nEndn存储过程printStudentByCursor的代码如下：nCREATEPROCEDUREdbo

48、.printStudentByCursornASnDECLAREMyCursorcursornDECLAREaxhvarchar(15)8.2存储过程的创建和执行n6使用OUTPUT游标参数nDECLAREasnamevarchar(30)nDECLAREasbirdatetimenDECLAREassexchar(10)nBEGINnEXECdbo.getstudent_cursorstudent_cursor=MyCursoroutputnFETCHNEXTFROMMyCursorintoaxh,asname,asbir,assex8.2存储过程的创建和执行n6使用OUTPUT游标参数nW

49、HILE(FETCH_STATUS=0)nBEGINnPRINT(学号：+axh+姓名：+asname+出生年月：+nCONVERT(varchar(30),asbir,120)+性别：+assex)nFETCHNEXTFROMMyCursorintoaxh,asname,asbir,assexnEND8.2存储过程的创建和执行n6使用OUTPUT游标参数nCLOSEMyCursor;nDEALLOCATEMyCursor;nENDn调用存储过程的代码如下：nUSEjxglnGOnEXECprintStudentByCursor8.3查看和修改存储过程n8.3.1 存储过程的查看存储过程的查看n存储过程创建成功后，可以查看存储过程的定义代码。存储过程的查看可以通过以下sys.sql_modules、object_definition和sp_helptext等系统存储过程或视图查看。n1sys.sql_modulesnsys.sql_modules是SQLServer的系统视图。通过该视图，可以查看数据库中的存储过程。查看方法如下：n在打开的查询编辑器中输入如下代码：select*fromsys.sql_modules。执行该代码，在查询结果中的definition字段内就是每个存储过程的详细定义代码8.3.1 存储过程的查看存储过程的查看n2object_definitionn