存储过程T-SQL游标

10
加密存储过程
如果用户不想让其他人查看存储过程的定义文本，可以 在定义存储过程的同时，对其进行加密，来保护代码的 安全性，通过加密的存储过程可以正常使用，但是无法 查看该存储过程的内容，在SQL Server Management Studio中也不能修改，只能通过ALTER语句来修改。 CREATE PROCEDURE exp6 With encryption as Select * From 安徽 查看存储过程 Order by name 源代码
18
3.9.1 IF…ELSE语句
–在实际程序中，IF…ELSE语句中不止包含一条语句，而 是一组的SQL语句。为了可以一次执行一组SQL语句，这 时就需要使用BEGIN…END语句将多条语句封闭起来。 其语法格式为： BEGIN {sql_statement | statement_block } 块 */ END –说明：BEGIN...END语句块允许嵌套。
– 备注：如果嵌套了两个或多个 WHILE 循环，则内层的 BREAK 将退 出到下一个外层循环。将首先运行内层循环结束之后的所有语句，然 后重新开始下一个外层循环。
22
3.9.2 WHILE语句
–例5－12：一个小循环程
DECLARE @X int SET @X=0 WHILE @x<3 BEGIN SET @x=@X+1 PRINT 'x='+convert(char(1),@x) --类型转换函数convert END GO 执行结果： x=1 x=2 x=3
23
【例】分析下列程序的功能。
DECLARE @sum int， @n int，@i int SELECT @n=0，@i=10; WHILE (@i>=10 and @i<=30) BEGIN IF @i%3=0 BEGIN PRINT @i SET @i=@i+1 SET @n=@n+1 CONTINUE END IF @i=22 BREAK SET @i=@i+1 END PRINT '有'+convert(char(2),@n)+ '个数能被3整除'
2
存储过程优点
1.存储过程运行的速度比 较快，比SQL语句执行速 度快2~10倍。
2.存储过程可以接受参数、 输出参数、返回单个或多 个结果集以及返回值。可 以向程序返回错误原因。
3.存储过程运行稳定，错 误较少。
4.存储过程主要是在服务 器上运行，减少对客户机 的压力。
5.存储过程可以包含程序 流、逻辑以及对数据库的 查询。
24
【例】程序功能：
该程序用于输出能被3整除的整数。其中用了 CONTINUE语句和BREAK语句。当程序执行到@i为22时， 直接跳出循环。故其执行结果如下： 12 15 18 21 有4个数能被3整除
25
3.9.3 GOTO语句 GOTO语句将执行语句无条件跳转到标签处，并从 标签位置继续处理。GOTO语句和标签可在过程、 批处理或语句块中的任何位置使用。其语法格式为： GOTO label
CREATE PROC [ EDURE ] procedure_name [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ]] [ ,...n ]
参数是返 回参数
指定过程要 执行的操作 { RECOMPILE | ENCRYPTION | RECOMPILE , [ WITH
17
3.9.1 IF…ELSE语句
–例10：嵌套IF图书查询
IF exists(SELECT * FROM 图书 WHERE 书名 = '天龙八部') SELECT 书名,作者 FROM 图书 WHERE 书名= '天龙八部' ELSE IF (SELECT Max(出版年度) FROM 图书 WHERE 书名 like '%水文%')>2005 SELECT 书名,作者 FROM 图书 WHERE 书名 like '%水文%' ELSE PRINT '没有符合您意愿的图书！！'
DROP PROCEDURE exp6
14
3.9 流程控制语句
T-SQL 语言支持基本的流控制逻辑，它允许按照给定的 某种条件执行程序流和分支，T-SQL 提供的控制流有： IF…ELSE分支，CASE多重分支，WHILE循环结构， GOTO语句，WAITFOR语句和RETURN语句。
15
3.9.1 IF…ELSE语句
as
Insert into 安徽
Valus(@ ID, @ AREA, @ PERIMETER,
@ BNDRY_ID, @ NAME, @ DIQU)
7
存储过程参数的使用
例3 创建带参数的存储过程，完成输入某一个字，输出包含 该字的记录。 CREATE PROCEDURE exp2 @name varchar(8) As
书的名称和出版社，否则显示“抱歉，图书
馆内没有该类藏书！！”
IF exists(SELECT * FROM 图书 WHERE 书名 like ‘%水文%’) SELECT 书名,作者 FROM 图书 WHERE 书名 like ‘%水文%’ ELSE PRINT ‘抱歉，图书馆内没有该类藏书！！’ 执行结果：
语法格式：
（二） IF...ELSE语句 IF <条件表达式> <SQL语句组1> ELSE <SQL语句组2>
注意：使用select语句作为这个条件表达式时，必须用 圆括号将select语句括起来。
16
3.9.1 IF…ELSE语句
–例9：--如果图书中有水文类的书，输出该类
6.可以在单个存储过程中 执行一系列SQL语句。
7.增加网络流量，降低网 络负担。如果使用单条调 用语句的方式，就必须传 输大量的SQL语句。
3
存储过程分类
系统存 本地存 临时存 远程存 扩展存 CLR过 储过程 储过程 储过程 储过程 储过程 程
系统创建 的存储过 程，存储 在系统数 据库中， 以 “sp_” 为开头 在用户数 据库中创 建的用户 存储过程， 完成特定 数据库操 作任务 SQL Server 2005新 增的存储 过程，针 对.NET 使用
20
3.9.1 IF…ELSE语句
IF exists(SELECT * FROM 图书 WHERE 书名 like ‘%水文%’) BEGIN SELECT @book2=COUNT(*) FROM 图书 WHERE 书名 like ‘%水文%’ PRINT ‘与水文相关的书有’ +RTRIM(CAST(@book2 AS char(4)))+'册' END ELSE PRINT ‘没有与水文相关的图书！！’ 执行结果：
WAITFOR语句，称为延迟语句，设定在达到指定时间 或时间间隔之前，或者指定语句至少修改或返回一行之 前，阻止执行批处理、存储过程或事务。其语法格式为： –WAITFOR { DELAY 'time_to_pass' /* 设定等待时间 */ | TIME 'time_to_execute' /* 设定等待带某一时 刻 */ } –备注：执行 WAITFOR 语句时，事务正在运行，并 且其他请求不能在同一事务下运行。WAITFOR 不更 改查询的语义。如果查询不能返回任何行， WAITFOR 将一直等待，或等到满足 TIMEOUT 条件 （如果已指定）。
21
3.9.2 WHILE语句
设置重复执行 SQL 语句或语句块的条件。只要指定的条件为 真，就重复执行语句。可以使用 BREAK 和 CONTINUE 关 键字在循环内部控制 WHILE 循环中语句的执行。 –语法： WHILE 逻辑表达式 Begin T-SQL语句组 [break]/*终止整个语句的执行*/ [continue]/*结束一次循环体的执行*/ END
/* 语句
19
3.9.1 IF…ELSE语句 –例5－11：BEGING END图书查询
DECLARE @book1 int,@book2 int IF exists(SELECT * FROM 图书 WHERE 作者 =‘金庸') BEGIN SELECT @book1=COUNT(*) FROM图书 WHERE 作者=‘金庸' PRINT ‘金庸的书数量 '+RTRIM(CAST(@book1 AS char(4)))+'册' /*CAST：转换数据类型，RTRIM：去右空格*/ END ELSE PRINT ‘没有金庸的书！'
ENCRYPTION } ] AS sql_statement
12
存储过程修改 ALTER PROCEDURE exp6 as Select distinct DIQU From 安徽
13
存储过程删除
使用命令删除存储过程
DROP PROC [ EDURE ] procedure_name [ ,…n ]
9
存储过程参数的使用
例5 创建带参数的存储过程，完成输入某行政区划代码前4位， 统计该地区下辖县（市）有几个。
CREATE PROCEDURE exp5 @BNDRY_ID varchar(8) As Select count(*) From 安徽 Where BNDRY_ID like @BNDRY_ID+ ‘%’
属于本地 存储过程， 只能在一 个用户会 话中使用
从远程服 务器上调 用的存储 过程，即 非本地存 储过程