第8章 存储过程

1、 8.1 8.1 存储过程存储过程8.2 8.2 触发器触发器8.3 8.3 游标游标8.1 存储过程8.1.1存储过程的特点和基本类型存储过程的特点和基本类型 存储过程（存储过程（Stored Procedure）是一组编译）是一组编译好存储在服务器上的完成特定功能好存储在服务器上的完成特定功能T-SQL代码，是代码，是某数据库的对象。客户端应用程序可以通过指定某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。有参数）来执行存储过程。1. 特点n存储过程已在服务器注册。存储过程已在服务器注册。

2、n存储过程具有安全特性（例如权限）和所有权链存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。接，以及可以附加到它们的证书。n存储过程可以强制应用程序的安全性。存储过程可以强制应用程序的安全性。n允许标准组件式编程，增强重用性和共享性允许标准组件式编程，增强重用性和共享性 n存储过程是命名代码，允许延迟绑定。存储过程是命名代码，允许延迟绑定。n存储过程可以减少网络通信流量。存储过程可以减少网络通信流量。n能够实现较快的执行速度。能够实现较快的执行速度。2. 分类分类n系统存储过程：以系统存储过程：以sp_为前缀；为前缀；n用户自定义存储过程：用户自定义存储过程：T-SQL

3、存储过程和存储过程和CLR存储过程两种；存储过程两种；n扩展存储过程。扩展存储过程。8.1.2创建和调用存储过程 在在SQL Server中，可以使用两种方中，可以使用两种方法创建存储过程：法创建存储过程：使用图形化工具创建存储过程使用图形化工具创建存储过程使用使用Transact-SQL创建存储过程创建存储过程 当创建存储过程时，需要确定存储过程的三当创建存储过程时，需要确定存储过程的三个组成部分：个组成部分：n所有的输入参数以及传给调用者的输出参数所有的输入参数以及传给调用者的输出参数;n被执行的针对数据库的操作语句，包括调用其他被执行的针对数据库的操作语句，包括调用其他存储过程的语句存储

4、过程的语句;返回给调用者的状态值，以指明调用是成功还是返回给调用者的状态值，以指明调用是成功还是失败。失败。 1. 使用图形化工具创建存储过使用图形化工具创建存储过程程 在在SSMS中，展开所需创建存储过程的数据库中，展开所需创建存储过程的数据库 展开展开【可编程性可编程性】 右键单击右键单击【存储过程存储过程】【新建存储过程新建存储过程】例例8-1：创建一个简单的存储过程。：创建一个简单的存储过程。(创创建查询赵良宇的借书数量的存储过程。建查询赵良宇的借书数量的存储过程。)【查询查询】菜单菜单【指定模板参数的值指定模板参数的值】borrowed_numn删除代码删除代码“p1 int = 0

5、p1 int = 0，p2 int = 0”p2 int = 0”n将代码将代码“SELECT p1SELECT p1，p2”p2”更改为更改为“select rname,lendnum from reader where rname=赵良赵良宇宇”语法格式如下：语法格式如下：execute 整型变量整型变量=存储过程名存储过程名;分组标识号分组标识号|存储过程变量存储过程变量参数参数=参量值参量值|变量变量 output|default,.n with recomplile执行存储过程执行存储过程执行存储过程执行存储过程nborrowed_num 或或nexec borrowed_num2.

6、 使用Transact-SQL创建存储过程create procedure 所有者所有者.存储过程名存储过程名;整数整数形参名形参名 类型类型变参名变参名 类型类型 outputwith recompile|encryption|recompile,encryptionas sql语句语句存储过程名n必须符合标识符规则，且对于数据库及其所必须符合标识符规则，且对于数据库及其所有者必须唯一；有者必须唯一；n要创建局部临时存储过程，在存储过程名前要创建局部临时存储过程，在存储过程名前面加一个面加一个“#”字符（字符（#存储过程名）；存储过程名）；n要创建全局临时存储过程，在存储过程名前要创建全局临

7、时存储过程，在存储过程名前面加两个面加两个“#”字符（字符（#存储过程名）；存储过程名）；n名称不能超过名称不能超过128个字符。个字符。;整数n 可选的整数，用来对同名的存储过程分组，可选的整数，用来对同名的存储过程分组，以便用一条以便用一条drop procedure语句将同组的过程一语句将同组的过程一起删除；起删除； 例：名为例：名为orders的应用程序使用的过程可以命名为的应用程序使用的过程可以命名为orderproc;1、orderproc;2等，等，drop procedure orderproc语句可以删除整个组。语句可以删除整个组。存储过程的处理方式nrecompile：每执

8、行一次存储过程都要重新编译，：每执行一次存储过程都要重新编译，可能降低了执行速度，但有助于数据的最后处理；可能降低了执行速度，但有助于数据的最后处理；nencryption：加密：加密syscomments系统表中包含系统表中包含create procedure的语句；的语句； 例例8-2：创建一个多表查询的存储过程。：创建一个多表查询的存储过程。 (创建创建查询查询程鹏程鹏的编号、借阅图书编号、图书名称以及借的编号、借阅图书编号、图书名称以及借出日期的存储过程。出日期的存储过程。)create procedure borrowed_book1asselect reader.rid,reade

9、r.rname,borrow.bid,book.bname,borrow.lenddatefrom borrow,reader,bookwhere borrow.rid=reader.ridand borrow.bid=book.bidand rname=程鹏程鹏create procedure borrowed_bookasselect r.rid,r.rname,b.bid,k.bname,b.lenddatefrom reader r inner join borrow b on r.rid=b.ridinner join book k on b.bid=k.bidwhere rname

10、=程鹏程鹏执行存储过程执行存储过程nborrowed_book 或或nexec borrowed_book 例例8-3：输入参数为某人的名字。：输入参数为某人的名字。(例例8-2中的中的rnme可以是输入的任意值，即创建查可以是输入的任意值，即创建查询任意读者的编号、姓名、借阅图书编号、询任意读者的编号、姓名、借阅图书编号、图书名称以及借出日期的存储过程。图书名称以及借出日期的存储过程。)create procedure borrowed_book2name varchar(10)asselect reader.rid,reader.rname,borrow.bid,book.bname,bo

11、rrow.lenddatefrom borrow,reader,bookwhere borrow.rid=reader.ridand borrow.bid=book.bidand rname=namecreate procedure borrowed_book2name varchar(10)asselect r.rid,r.rname,b.bid,k.bname,b.lenddatefrom reader r inner join borrow b on r.rid=b.ridinner join book k on b.bid=k.bidwhere rname=name 执行存储过程：直接

12、传值：直接传值：nEXEC borrowed_book2 程鹏程鹏执行存储过程：变量传值：变量传值：declare temp1 char(20)set temp1=程鹏程鹏exec borrowed_book2 temp1 例例8-4：使用默认参数：使用默认参数(如果没有输入如果没有输入参数，则查询所有读者的借书情况，如果参数，则查询所有读者的借书情况，如果输入参数，则查询该读者的借书情况输入参数，则查询该读者的借书情况)create procedure borrowed_book3name varchar(10)=nullasif name is nullselect reader.rid,

13、reader.rname,borrow.bid,book.bname,borrow.lenddatefrom borrow,reader,bookwhere borrow.rid=reader.ridand borrow.bid=book.bidelseselect reader.rid,reader.rname,borrow.bid,book.bname,borrow.lenddatefrom borrow,reader,bookwhere borrow.rid=reader.ridand borrow.bid=book.bidand rname=namecreate procedure b

14、orrowed_book3name varchar(10)=nullasif name is nullselect r.rid,r.rname,b.bid,k.bname,b.lenddatefrom reader r inner join borrow b on r.rid=b.ridinner join book k on b.bid=k.bidelseselect r.rid,r.rname,b.bid,k.bname,b.lenddatefrom reader r inner join borrow b on r.rid=b.ridinner join book k on b.bid=

15、k.bidwhere rname=name 执行存储过程：执行存储过程：EXEC borrowed_book3执行存储过程：直接传值：nEXEC borrowed_book3 程鹏例8-5：利用输出参数计算阶乘。use libraryif exists(select name from sysobjectswhere name=factorial and type=P)drop procedure factorialgocreate procedure factorialin float,out float outputasdeclare i intdeclare s floatset i=1

16、set s=1while i=inbeginset s=s*iset i=i+1endset out=s调用存储过程：declare ou floatexec factorial 5,ou outputprint ou执行结果：执行结果：1208.1.3 修改和删除存储过程 存储过程可以根据用户的要求或者基存储过程可以根据用户的要求或者基表定义的改变而改变。使用表定义的改变而改变。使用ALTER PROCEDURE语句可以更改先前通过执行语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程，语句创建的过程，但不会更改权限，也不影响相关的存储过但不会更改权限，也不影响相关的

17、存储过程或触发器。程或触发器。alter procedure 所有者所有者.存储过程名存储过程名;整数整数形参名形参名 类型类型变参名变参名 类型类型 outputwith recompile|encryption|recompile,encryptionas sql语句语句EXEC sp_renameEXEC sp_rename 存储过程原名存储过程原名, ,存储过程新名存储过程新名1. 重命名存储过程 使用使用DROP PROCEDUREDROP PROCEDURE语句从当前数据库中删除一个或多个用户定义的存储语句从当前数据库中删除一个或多个用户定义的存储过程或存储过程组，语法格式如下：过

18、程或存储过程组，语法格式如下： drop proceduredrop procedure 所有者所有者.存储过程存储过程( (组组) )名名,.n,.n2. 删除存储过程8.1.5 使用系统存储过程和扩展存储过程nsp_helpnsp_helptextnsp_dependsnsp_stored_procedures n 【例1】 创建一个简单的无参数的存储过程：在Sales数据库中，创建存储过程proc_Employees，查询采购部的员工信息。use salesgocreate proc proc_employeesasselect * from employeeswhere 部门部门=采购

19、部采购部exec proc_employeesn 【例2】 创建一个带有输入参数的存储过程proc_goods，查询指定员工所进商品信息。use salesgocreate proc proc_goods 员工编号员工编号 char(6)=1001as select * from goods where 进货员工编号进货员工编号=员工编号员工编号goexec proc_goods-或或exec proc_goods 员工编号员工编号=default-或或exec proc_goods 员工编号员工编号=1001-或或exec proc_goods 1001执行存储过程，查询执行存储过程，查询1

20、001号员工所进号员工所进的商品的信息的商品的信息exec proc_goods 员工编号员工编号=1002-或或exec proc_goods 1002执行存储过程，查询执行存储过程，查询1002号员工所进号员工所进的商品的信息的商品的信息n【例3】 创建一个带有输入和输出参数的存储过程proc_GNO，查询指定厂商指定名称的商品所对应的商品编号。create proc proc_gno 商品名称商品名称 varchar(20),生产厂商生产厂商 varchar(30), 商品编号商品编号 int outputas select 商品编号商品编号=商品编号商品编号 from goods wh

21、ere 商品名称商品名称=商品名称商品名称 and 生产厂商生产厂商=生产厂商生产厂商-执行存储过程，查询惠普公司打印机商品编号执行存储过程，查询惠普公司打印机商品编号declare 商品编号商品编号 int exec proc_gno 打印机打印机, 惠普公司惠普公司,商品编号商品编号 outputprint 该商品编号为：该商品编号为：+cast(商品编号商品编号 as char(6)n 【例4】 创建带有参数和返回值的存储过程：在Sales数据库中创建存储过程ProcSum ByGoods。查询指定厂商指定名称的商品在某年某月的总销售量。执行存储过程，查询惠普公司执行存储过程，查询惠普公

22、司2004年年10月的月的打印机销售总量打印机销售总量 【例例5】创建一个名为创建一个名为FindEmployee的的存储过程，可以用它来找出存储过程，可以用它来找出sales数据库的数据库的Employees表中员工编号为指定值（输入表中员工编号为指定值（输入参数）的记录的参数）的记录的“姓名姓名”字段的名称，另字段的名称，另外指定一个参数外指定一个参数LineNum作为输出参数，作为输出参数，且必须在存储过程中判断员工编号不能为且必须在存储过程中判断员工编号不能为空串，是的话要打印出出错信息，并返回空串，是的话要打印出出错信息，并返回错误值错误值0，如果查询成功在输出变量，如果查询成功在输

23、出变量LineNum中保留所影响的行数，然后返回中保留所影响的行数，然后返回值值1。【例例6 创建存储过程创建存储过程proc_GoodsSell，嵌套调用存储过，嵌套调用存储过程程proc_gno，查询指定厂商指定名称商品的销售情况，查询指定厂商指定名称商品的销售情况】create proccreate proc pro proc c_Good_Goods sSellSell 商品名称商品名称 varchar(20),varchar(20),指定厂商指定厂商 varchar(30)varchar(30)asas declaredeclare 商品编号商品编号 intint exec proc

24、_gnoexec proc_gno 商品名称商品名称,指定厂商指定厂商,商品编号商品编号outputoutput selectselect 商品编号商品编号, ,数量数量as as 销售数量销售数量, ,售出时间售出时间, ,售货员工编号售货员工编号 from sell where from sell where 商品编号商品编号=商品编号商品编号execexec proc_GoodsSellproc_GoodsSell 打印机打印机,惠普公司惠普公司 注意：n 嵌套的存储过程会增加复杂级别，使得性能问题的故障诊断比较困难，所以要谨慎选择嵌套的存储过程。思考：例思考：例6 不用嵌套调用如何实现

25、？不用嵌套调用如何实现？create proccreate proc proc_GoodsSell2 proc_GoodsSell2 商品名称商品名称 varchar(20),varchar(20),生产厂商生产厂商 varchar(30)varchar(30)asas select sell. select sell.商品编号商品编号,sell.,sell.数量数量as as 销售数量销售数量, ,售出时间售出时间, ,售货员工编号售货员工编号 from goods,sell from goods,sell where goods. where goods.商品编号商品编号=sell.=se

26、ll.商品编号商品编号 and and 商品名称商品名称=商品名称商品名称 and and 生产厂商生产厂商=生产厂商生产厂商execexec pro_GoodsSell2pro_GoodsSell2 打印机打印机,惠普公司惠普公司 create proccreate proc proc_GoodsSell3 proc_GoodsSell3 商品名称商品名称 varchar(20),varchar(20),生产厂商生产厂商 varchar(30)varchar(30)asas select select 商品名称商品名称, ,生产厂商生产厂商,sell.,sell.数量数量 as as 销售数

27、量销售数量, ,售出时间售出时间, ,姓名姓名 as as 售货员工姓名售货员工姓名 from goods,sell,employees from goods,sell,employees where goods. where goods.商品编号商品编号=sell.=sell.商品编号商品编号 and sell.and sell.售货员工编号售货员工编号=employees.=employees.编号编号 and and 商品名称商品名称=商品名称商品名称and and 生产厂商生产厂商=生产厂商生产厂商execexec pro_GoodsSell3pro_GoodsSell3 打印机打印机

28、,惠普公司惠普公司 思考：创建存储过程查询指定时间内的销思考：创建存储过程查询指定时间内的销售总金额？售总金额？create proc create proc Proc_sellQryProc_sellQry startTime datetime startTime datetime,endTime ,endTime datetimedatetime, , sumTotal sumTotal float outputfloat outputasas select sumTotal select sumTotal= =sumsum(sell.(sell.数量数量* *零售价零售价) ) from goods from goods,sell,sell where goods where goods. .商品编号商品编号=sell.=sell.商品编号商品编号 and and 售出时间售