第6章 SQL Server 存储过程

第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)CreateTriggerc)CreateDefaultd)CreateProceduree)CreateRule(创建规则)2不能再同一存储过程中创建，删除，重建同一对象名3不能引用尚未存在的对象4虽然可以引用临时表，但临时表数据清空，容易报错，因此谨慎使用临时表。2使用SQLServerManagementStudio建立存储过程：选择本地数据库-可编程性--存储过程--新建存储过程，然后弹出创建存储过程的查询编辑器窗口，其中加入了一些创建存储过程的代码。然后单击菜单栏查询指定模板参数的值选项：其中Author为存储过程的作者Createdate：创建存储过程的时间Description：存储过程说明以上三项为参数的可选项，内容可以为空@param1为第一个输入参数名默认为@p1Datatype_For_Param1为第一个输入参数的类型默认为intDatatype_For_Param2为第一个输入参数的类型默认为int设置完指定模板参数值选项后单击确定，返回到创建存储过程查询编辑器窗口，这是该窗口内容已经改变在“Insertstatementsforprocedurehere”下输入T-SQL代码：然后单击“执行”按钮完成操作，使用SQLServerManagementStudio建立存储过程归根结底与直接使用T-SQL语言创建过程相差不多，只不过有些参数可以用模板来添加而已，但是要设计强大的存储过程还是需要熟悉CreateProcedure语句。19创建不带参数的存储过程创建存储过程，查看本次考试平均分以及未通过考试的学员名单20创建不带参数的存储过程CREATEPROCEDUREproc_stuASDECLARE@writtenAvgfloat,@labAvgfloatSELECT@writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam)FROMstuMarksprint'笔试平均分：'+convert(varchar(10),@writtenAvg)print'机试平均分：'+convert(varchar(10),@labAvg)IF(@writtenAvg70AND@labAvg70)print‘本班考试成绩：优秀'ELSEprint‘本班考试成绩：较差'print'--------------------------------------------------'print‘参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrrttenExam60ORlabExam60GOproc_stu为存储过程的名称笔试平均分和机试平均分变量显示考试成绩的等级显示未通过的学员调用存储过程21EXECUTE（执行）语句用来调用存储过程调用的语法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,@labPassintASprint'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam@writtenPassORlabExam@labPassGO输入参数：笔试及格线输入参数：机试及格线查询没有通过考试的学员带输入参数的存储过程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=60ASprint'--