第11章 存储过程汇总

《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系什么是存储过程经过前四章的洗礼，相信您对MicrosoftSQLServer程序设计已有了基本的认识。第07章Transact-SQL程序设计第08章数据的查询、汇总、统计和分析第09章添加、修改和删除数据记录第10章视图本章开始更高级的内容。存储过程包含一些Transact-SQL语句，并以特定的名称存储在数据库中。存储过程是一种数据库对象。可以在存储过程中声明变量、有条件执行以及其他各项强大的程序设计功能。它能够包含执行各项数据库操作的语句，并且可以调用其他的存储过程。能够接收输入参数并以输出参数的形式将多个数据值返回给调用程序。具体示例：试着看看，看得懂吗？后面会详细介绍CREATEPROCEDUREgetPerson@mNamevarchar(10),@mCur_salarymoney本章主题什么是存储过程创建存储过程细说CREATEPROCEDURE命令执行存储过程重命名存储过程修改存储过程删除存储过程必杀技！不可不学！学习如何使用SQL来写storedprocedure《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系=cur_salary*1.2WHEREname=@mNameANDcur_salary=@mCur_salary11.1.1.存储过程的优点允许模块化程序设计更快的执行速度，存储过程被预先编译和优化并存储在数据库中有效降低网络流量较好的安全，如限制某人对某个表操作，却又必须要求他对该表执行特定的操作。11.2.创建存储过程CREATEPROCEDURE命令在查询分析器中执行命令。11.3.细说CREATEPROCEDURE命令CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系页;number是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROPPROCEDUREorderproc语句将除去整个组。VARYING指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。11.3.1.指定存储过程的名称存储过程名称最长不超过128字符为避免与系统存储过程相混淆，尽量不要以sp_开头11.3.2.指定存储过程的语句CREATEPROCEDURE命令不能够与其他的Transact-SQL命令位于同一个批处理中。下面的代码会出错：USENorthwindSQL--与下面的CREATEPROCEDURE在同一批处理中CREATEPROCEDUREmyprocASSELECT*FROM飞狐工作室《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系页修改为：USENorthwindSQLGOCREATEPROCEDUREmyprocASSELECT*FROM飞狐工作室注解：每一个存储过程最好只负责完成一项工作一个很好的建议：在创建存储过程前，先在SQL查询分析器中编写并测试要包含在存储过程中的程序代码，等一切无误后，再加入CREATEPROCEDURE命令将它创建成存储过程。存储过程可以访问表、视图，并可以调用其它存储过程。存储过程的最大长度是128MB（其实不太可能达到这个长度的）11.3.3.输入参数将数据值传递给存储过程，该数据值也称为输入参数。{@parameterdata_type}[VARYING][=default][OUTPUT]参数的名称必须以@开头。data_type指定输入参数的数据类型。default用来指定输入参数的默认值。一个存储过程最多可以拥有2100个参数（包括输入参数和输出参数）。范例一：利用传入的参数选出选定的姓名的员工资料。/*档案名称:Demo111.sql*/USENorthwindSQLIFEXISTS(SELECTnameFROMsysobjects《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系='DemoProc1'ANDtype='P')DROPPROCEDUREDemoProc1GOCREATEPROCEDUREDemoProc1@namevarchar(10)ASSELECT身份证号码,姓名,出生日期,年龄=DATEDIFF(yy,出生日期,GETDATE())FROM飞狐工作室WHERE姓名=@nameGO执行该存储过程：EXECUTEDemoProc1‘许建仁’--方法之一或EXECUTEDemoProc1＠name=‘许建仁’--方法之二--参数名一定要与定义时的名称一致--您更喜欢哪一种方法？任务：创建存储过程，根据传入的学生姓名，显示学生的基本信息；根据传入的学号，显示选修的课程；根据传入的班级名称，显示该班级中的学生信息。创建存储过程，根据传入的课程信息，将该课程信息新增到course表中；创建存储过程，根据传入的学生学号和入学成绩，更新该学生的入学成绩；创建存储过程，根据传入的学号，将该学生的选课信息删除。《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系页范例二：/*档案名称:Demo112.sql*/USENorthwindSQLIFEXISTS(SELECTnameFROMsysobjectsWHEREname='DemoProc2'ANDtype='P')DROPPROCEDUREDemoProc2GOCREATEPROCEDUREDemoProc2@BeginningDatedatetime='01/01/1900',@EndingDatedatetime='12/31/2001'ASSELECTa.客户编号,a.公司名称,a.地址,a.联系人,a.电话,b.订单号码,b.订单日期,b.运费,b.收货人,c.产品编号,c.单价,c.数量,c.折扣FROM客户aINNERJOIN订货主档bINNERJOIN订货明细cONb.订单号码=c.订单号码ONa.客户编号=b.客户编号WHEREb.订单日期BETWEEN@BeginningDateAND@EndingDateGO执行该存储过程：EXECUTEDemoProc2‘07/01/1996’,’07/31/1996’《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系@BeginningDate=‘07/01/1996’,@EndingDate=’07/31/1996’--参数名一定要与定义时的名称一致另一种调用方法：EXECUTEDemoProc2--使用参数的默认值任务：创建存储过程，定义2个日期参数（参数默认值为19500101，19600101），显示该日期段出生的飞狐工作室中的人员信息。任务：创建存储过程，根据传入的产品类别名称（参数默认值为饮料），显示该类别下的产品信息。范例三：/*档案名称:Demo113.sql*/USENorthwindSQLIFEXISTS(SELECTnameFROMsysobjectsWHEREname='DemoProc3'ANDtype='P')DROPPROCEDUREDemoProc3GOCREATEPROCEDUREDemoProc3@idvarchar(10)='[A-M]%',@namevarchar(10)='许%',@addressvarchar(10)='%市%'ASSELECT*FROM飞狐工作室WHERE《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系@idAND姓名LIKE@nameAND家庭地址LIKE@addressGO执行该存储过程：EXECUTEDemoProc3'[ALM]%','_建_','%天津市%'EXECUTEDemoProc3任务：创建存储过程，根据传入的产品名称（参数默认值为%汁），显示产品信息。11.3.4.输出参数范例一：示范将传入参数相乘后将结果返回。/*档案名称:Demo114.sql*/USENorthwindSQLIFEXISTS(SELECTnameFROMsysobjectsWHEREname='DemoProc4'ANDtype='P')DROPPROCEDUREDemoProc4GOCREATEPROCEDUREdbo.DemoProc4@mult1int,@mult2int,@resultintOUTPUTASSET@result=@mult1*@mult2GO《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系页执行该存储过程：/*档案名称:Demo115.sql*/DECLARE@answerintEXECDemoProc412,33,@answerOUTPUTSELECT'运算结果是:',@answer范例二：计算出指定部门的平均薪资、最大薪资和最低薪资。/*档案名称:Demo116.sql*/USENorthwindSQLIFEXISTS(SELECTnameFROMsysobjectsWHEREname='DemoProc5'ANDtype='P')DROPPROCEDUREDemoProc5GOCREATEPROCEDUREdbo.DemoProc5@departmentvarchar(10),@averagemoneyOUTPUT,@maximummoneyOUTPUT,@minimummoneyOUTPUTASSELECT@average=AVG(目前薪资),@maximum=MAX(目前薪资),@minimum=MIN(目前薪资)FROM飞狐工作室WHERE部门=@departmentGO《SQLServer数据库应用与开发》第4稿11存储过程沙洲职业工学院电子信息工程系页执行该存储过程：/*档案名称:Demo117.sql*/USENorthwindSQLDECLARE@departmentvarchar(10)DECLARE@averagedecimal(19,4)DECLARE@maximumdecimal(19,4)DECLARE@minimumdecimal(19,4)SELECT@department='生产制造部'EXECDemoProc5@department,@averageOUTPUT,@maximumOUTPUT,@minimumOUTPUTSELECT'平均薪资:'+CONVERT(n