第6章SQLServer存储过程

第9章存储过程SQL语句与前后台交互：前台后台数据库执行select…命令，并将结果返回<传递>商品查询界面根据关键词写出select…语句<返回>SQL编写好的语句与前后台交互：前台后台数据库自动执行已编写好的命令，将结果返回<调用>商品查询界面根据关键词写出调用语句<返回>什么是存储过程4存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果intsum(inta,intb){ints;s=a+b;returns;}存储过程相当于C语言中的函数存储过程（StoredProcedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。利用SQLServer创建一个应用程序时，SQL是主要的编程语言。使用SQL进行编程，有两种方法:其一是，在本地存储SQL程序，并创建应用程序向SQLServer发送命令来对结果进行处理。其二是，可以把部分用SQL编写的程序作为存储过程存储在SQLServer中，然后创建应用程序来调用存储过程，对数据结果进行处理。6存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含存储过程可以包含数据操纵语句、变量、逻辑控制语句等存储过程的优点7执行速度更快：SQLServer2005会事先把存储过程编译成二进制可执行代码，无需对其进行编译允许模块化程序设计：存储过程在创建完毕之后可以在程序中多次被调用而不必重新编写该T-SQL语句提高系统安全性:当用户访问一个或多个数据表，但没有存取权限时可以设计一个存储过程来存取这些表中的数据减少网络流通量：在客户端调用时只使用存储过程名及参数即可，网络传送流量比完整的T-SQL程序小得多存储过程的分类在SQLServer2005中有多种可用的存储过程。在SQLServer2005中存储过程分为三类：系统提供的存储过程、用户自定义存储过程和扩展存储过程。1.系统存储过程SQLServer2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQLServer提供支持。通过系统存储过程，SQLServer中的许多管理性或信息性的活动（如获取数据库和数据库对象的信息）都可以被顺利有效地完成。系统存储过程的名称都以“sp_”开头存储过程的分类2.用户自定义的存储过程用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程，是封装了可重用代码的SQL语句模块。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，以及返回输出参数。在SQLServer中，用户自定义的存储过程有两种类型：T-SQL存储过程或CLR存储过程。我们所提到的用户定义的存储过程主要指T-SQL存储过程。3.扩展存储过程扩展存储过程允许使用高级编程语言（例如C）创建应用程序的外部例程，从而使得SQLServer的实例可以动态加载和运行DLL。扩展存储过程直接在SQLServer实例的地址空间中运行。扩展存储过程的名称都以“xp_”开头常用的系统存储过程10系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。用SQL语句创建带参数的存储过程语法格式如下：

CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]ASsql_statement[...n]和C#语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。;number：该参数是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起删除。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROPPROCEDUREorderproc语句将删除整个组。parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。Default：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必指定该参数的值也可执行过程。OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。RECOMPILE：表明SQLServer不保存存储过程的计划，该过程将在运行时重新编译。ENCRYPTION：表示SQLServer加密syscomments表中包含语句文本的条目。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程内的信息传出到外部。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。但是存储过程的内容有如下限制：1>不能包含下列语句：a)CreateViewb)CreateTriggerc)CreateDefaultd)CreateProceduree)CreateRule(创建规则)2>不能再同一存储过程中创建，删除，重建同一对象名3>不能引用尚未存在的对象4>虽然可以引用临时表，但临时表数据清空，容易报错，因此谨慎使用临时表。2>使用SQLServerManagementStudio建立存储过程：选择本地数据库->可编程性-->存储过程-->新建存储过程，然后弹出创建存储过程的查询编辑器窗口，其中加入了一些创建存储过程的代码。然后单击菜单栏查询指定模板参数的值选项：其中Author为存储过程的作者Createdate：创建存储过程的时间Description：存储过程说明以上三项为参数的可选项，内容可以为空@param1为第一个输入参数名默认为@p1Datatype_For_Param1为第一个输入参数的类型默认为intDatatype_For_Param2为第一个输入参数的类型默认为int设置完指定模板参数值选项后单击确定，返回到创建存储过程查询编辑器窗口，这是该窗口内容已经改变在“Insertstatementsforprocedurehere”下输入T-SQL代码：然后单击“执行”按钮完成操作，使用SQLServerManagementStudio建立存储过程归根结底与直接使用T-SQL语言创建过程相差不多，只不过有些参数可以用模板来添加而已，但是要设计强大的存储过程还是需要熟悉CreateProcedure语句。19创建不带参数的存储过程创建存储过程，查看本次考试平均分以及未通过考试的学员名单20创建不带参数的存储过程CREATEPROCEDUREproc_stu

ASDECLARE@writtenAvgfloat,@labAvgfloat

SELECT@writtenAvg=AVG(writtenExam),

@labAvg=AVG(labExam)

FROMstuMarksprint'笔试平均分：'+convert(varchar(10),@writtenAvg)print'机试平均分：'+convert(varchar(10),@labAvg)IF(@writtenAvg>70AND@labAvg>70)print‘本班考试成绩：优秀'ELSEprint‘本班考试成绩：较差'print'--------------------------------------------------'print‘参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,writtenExam,labExam

FROMstuInfoINNERJOINstuMarksON

stuInfo.stuNo=stuMarks.stuNo

WHEREwrrttenExam<60ORlabExam<60GOproc_stu为存储过程的名称笔试平均分和机试平均分变量显示考试成绩的等级显示未通过的学员调用存储过程21EXECUTE（执行）语句用来调用存储过程调用的语法

EXEC

过程名[参数]

EXECproc_stu创建带参数的存储过程22存储过程的参数分两种：输入参数输出参数

intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）传入参数值输入参数：用于向存储过程传入值，类似C语言的按值传递;

输出参数：用于在调用存储过程后，

返回结果，类似C语言的按引用传递;

返回结果带输入参数的存储过程23问题：修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。分析：在述存储过程添加2个输入参数：@writtenPass笔试及格线@labPass机试及格线带输入参数的存储过程24CREATEPROCEDUREproc_stu

@writtenPassint,

@labPassint

ASprint'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO输入参数：笔试及格线输入参数：机试及格线查询没有通过考试的学员带输入参数的存储过程25EXECproc_stu60,55调用带参数的存储过程

假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分--或这样调用：EXECproc_stu@labPass=55,@writtenPass=60机试及格线降分后，李斯文（59分）成为“漏网之鱼”了26输入参数的默认值带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线问题：如果试卷的难易程度合适:@writtenPassint=60,

@labPassint=60

，则调用者还是必须如此调用：EXECproc_stu60,60，比较麻烦这样调用就比较合理：EXECproc_stu55EXECproc_stu

笔试及格线55分，机试及格线默认为60分笔试和机试及格线都默认为标准的60分27CREATEPROCEDUREproc_stu

@writtenPassint=60,

@labPassint=60

ASprint'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO笔试及格线：默认为60分机试及格线：默认为60分查询没有通过考试的学员输入参数的默认值28EXECproc_stu--都采用默认值EXECproc_stu64--机试采用默认值EXECproc_stu60,55--都不采用默认值调用带参数默认值的存储过程--错误的调用方式：希望笔试采用默认值，机试及格线55分EXECproc_stu,55

--正确的调用方式：EXECproc_stu@labPass=55输入参数的默认值带输出参数的存储过程29如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了问题：修改上例，返回未通过考试的学员人数。带输出参数的存储过程30CREATEPROCEDUREproc_stu

@notpassSumintOUTPUT,@writtenPassint=60,@labPassint=60ASSELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenP