第6章SQLServer存储过程

1、第9章 存储过程lSQL语句与前后台交互：前前台台后台数据库后台数据库 执行执行select 命令，并将结果返回命令，并将结果返回商品查询界面根据关键词写出根据关键词写出select 语句语句lSQL编写好的语句与前后台交互：前前台台后台数据库后台数据库 自动执行已编写好的命令，将结果返回自动执行已编写好的命令，将结果返回商品查询界面根据关键词写出调用语句根据关键词写出调用语句什么是存储过程4q存储过程（procedure）类似于C语言中的函数q用来执行管理任务或应用复杂的业务规则q存储过程可以带参数，也可以返回结果int sum(int a,int b) int s; s =a+b; ret

2、urn s ;存储过程相当于C语言中的函数l存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。l利用SQL Server创建一个应用程序时，SQL是主要的编程语言。使用SQL进行编程，有两种方法:l其一是，在本地存储SQL程序，并创建应用程序向SQL Server发送命令来对结果进行处理。l其二是，可以把部分用SQL编写的程序作为存储过程存储在SQL Server中，然后创建应用程序来调用存储过程，对数据结果进行处理。6存储过程-单个 SELECT 语句SELECT

3、 语句块SELECT语句与逻辑控制语句可以包含可以包含q存储过程可以包含数据操纵语句、变量、逻辑 控制语句等存储过程的优点7q 执行速度更快：q SQL Server 2005会事先把存储过程编译成二进制可执行代码，无需对其进行编译q 允许模块化程序设计 ：q 存储过程在创建完毕之后可以在程序中多次被调用而不必重新编写该T-SQL语句q 提高系统安全性:q 当用户访问一个或多个数据表，但没有存取权限时可以设计一个存储过程来存取这些表中的数据q 减少网络流通量：在客户端调用时只使用存储过程名及参数即可，网络传送流量比完整的T-SQL程序小得多存储过程的分类l在SQL Server 2005中有多

4、种可用的存储过程。在SQL Server 2005中存储过程分为三类：系统提供的存储过程、用户自定义存储过程和扩展存储过程。l1. 1. 系统存储过程系统存储过程lSQL Server 2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQL Server提供支持。通过系统存储过程，SQL Server中的许多管理性或信息性的活动（如获取数据库和数据库对象的信息）都可以被顺利有效地完成。l系统存储过程的名称都以系统存储过程的名称都以

5、“sp_”sp_”开头开头存储过程的分类l2. 2. 用户自定义的存储过程用户自定义的存储过程l用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程，是封装了可重用代码的SQL语句模块。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，以及返回输出参数。在SQL Server中，用户自定义的存储过程有两种类型：T-SQL存储过程或CLR存储过程。我们所提到的用户定义的存储过程主要指T-SQL存储过程。l3. 3. 扩展存储过程扩展存储过程l扩展存储过程允许使用高级编程语言（例如C）创建应用程序的

6、外部例程，从而使得SQL Server的实例可以动态加载和运行DLL。扩展存储过程直接在SQL Server实例的地址空间中运行。l扩展存储过程的名称都以扩展存储过程的名称都以“xp_”xp_”开头开头常用的系统存储过程10系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_s

7、tored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。l用SQL语句创建带参数的存储过程语法格式如下：l CREATE PROC EDURE procedure_name ;number l parameter data_type = default OUTPUT ,.n l WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION l AS sql_statement .n 和和C#语言的函数一

8、样，参数可选语言的函数一样，参数可选参数分为输入参数、输出参数参数分为输入参数、输出参数 输入参数允许有默认值输入参数允许有默认值lprocedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。l;number：该参数是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE 语句即可将同组的过程一起删除。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROP PROCEDURE orderproc 语句将删除整个组。lparameter：存储过程中的输入和输出参数。lda

9、ta_type：参数的数据类型。lDefault：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必指定该参数的值也可执行过程。lOUTPUT：表明参数是返回参数。该选项的值可以返回给EXECUTE。使用 OUTPUT参数可将信息返回给调用过程。lRECOMPILE：表明SQL Server不保存存储过程的计划，该过程将在运行时重新编译。lENCRYPTION：表示SQL Server加密syscomments 表中包含语句文本的条目。lsql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。l在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数

10、，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程内的信息传出到外部。lsql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。l但是存储过程的内容有如下限制：l1不能包含下列语句：la)Create View b)Create Trigger lc)Create Default d)Create Procedurele)Create Rule (创建规则)l2不能再同一存储过程中创建，删除，重建同一对象名l3不能引用尚未存在的对象l4虽然可以引用临时表，但临时表数据清空，容易报错，因此谨慎使用临时表。l2使用使用SQL Server Manageme

11、nt Studio建立存储过程：建立存储过程：l选择本地数据库-可编程性-存储过程-新建存储过程，然后弹出创建存储过程的查询编辑器窗口，其中加入了一些创建存储过程的代码。l然后单击菜单栏查询指定模板参数的值选项：l其中Author为存储过程的作者lCreate date：创建存储过程的时间lDescription：存储过程说明l以上三项为参数的可选项，内容可以为空lparam1为第一个输入参数名默认为p1lDatatype_For_Param1为第一个输入参数的类型默认为intlDatatype_For_Param2为第一个输入参数的类型默认为intl设置完指定模板参数值选项后单击确定，返回到

12、创建存储过程查询编辑器窗口，这是该窗口内容已经改变l在“Insert statements for procedure here”下输入T-SQL代码：l然后单击“执行”按钮完成操作，使用SQL Server Management Studio建立存储过程归根结底与直接使用T-SQL语言创建过程相差不多，只不过有些参数可以用模板来添加而已，但是要设计强大的存储过程还是需要熟悉Create Procedure语句。19创建不带参数的存储过程创建不带参数的存储过程q创建存储过程，查看本次考试平均分以及未通过考试的学员名单20创建不带参数的存储过程CREATE PROCEDURE proc_stu

13、AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(writtenExam), labAvg=AVG(labExam) FROM stuMarks print 笔试平均分：+convert(varchar(10),writtenAvg) print 机试平均分：+convert(varchar(10),labAvg) IF (writtenAvg70 AND labAvg70) print 本班考试成绩：优秀 ELSE print 本班考试成绩：较差 print - print 参加本次考试没有通过的学员： SELECT

14、 stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE wrrttenExam60 OR labExam60 GOproc_stu为存储过程的名称笔试平均分和机试平均分变量 显示考试成绩的等级 显示未通过的学员 调用存储过程21qEXECUTE（执行）语句用来调用存储过程q调用的语法EXEC 过程名 参数EXEC proc_stu创建带参数的存储过程22q存储过程的参数分两种：q 输入参数q 输出参数int sum (int

15、 a, int b) int s; s=a+b; return s;c=sum（5, 8）传入参数值q 输入参数： 用于向存储过程传入值，类似C语言的按值传递;q 输出参数： 用于在调用存储过程后， 返回结果，类似C语言的 按引用传递; 返回结果带输入参数的存储过程23问题：问题：修改上例：由于每次考试的难易程度不一样，每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。分析：分析：在述存储过程添加2个输入参数：writtenPass 笔试及格线 labPass 机试及格线 带输入参数的存储过程24CREATE PROCEDURE proc_stu writt

16、enPass int, labPass int AS print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO输入参数：笔试及格线输入参数：机试及格线查询没有通过考试的学员带输入参数的存储过程25EXEC proc_stu 60,55 q调用带参数的存储过程 假定本次考

17、试机试偏难，机试的及格线定为55分，笔试及格线定为60分-或这样调用：EXEC proc_stu labPass=55,writtenPass=60机试及格线降分后，李斯文（59分）成为“漏网之鱼”了26输入参数的默认值l带参数的存储过程确实比较方便，调用者可根据试卷的难易度，随时修改每次考试的及格线问题：如果试卷的难易程度合适:writtenPass int=60, labPass int=60 ，则调用者还是必须如此调用： EXEC proc_stu 60,60，比较麻烦这样调用就比较合理：EXEC proc_stu 55EXEC proc_stu 笔试及格线55分，机试及格线默认为60分

18、 笔试和机试及格线都默认为标准的60分27CREATE PROCEDURE proc_stu writtenPass int=60, labPass int=60 AS print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO笔试及格线：默认为60分机试及格线：默认为6

19、0分查询没有通过考试的学员输入参数的默认值28EXEC proc_stu -都采用默认值 EXEC proc_stu 64 -机试采用默认值 EXEC proc_stu 60,55 -都不采用默认值 q调用带参数默认值的存储过程-错误的调用方式：希望笔试采用默认值，机试及格线55分EXEC proc_stu ,55 -正确的调用方式：EXEC proc_stu labPass=55 输入参数的默认值带输出参数的存储过程29q如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了 问题：修改上例，返回未通过考试的学员人数。带输出参数的存储过程30CREATE PROCEDURE proc_stu notpassS