第五章 存储过程、触发器和数据完整性(2012)

7.CASE语句 (1) 格式一 CASE <表达式> WHEN <条件表达式1> THEN <表达式1> [[WHEN <条件表达式2> THEN <表达式2>][…]] [ELSE <表达式n>] END 【例5.4】用CASE语句格式一实现：在对stock表的查询中，当仓 库号的值是“供电局1号仓库”、“供电局2号仓库”、“供电 局3号仓库”时分别返回“北京”、“上海”、“广州”，否 则返回“未知”。 SELECT mat_num,mat_name,speci,amount,unit,total, warehouse=CASE warehouse WHEN '供电局1#仓库'THEN '北京' WHEN '供电局2#仓库'THEN '上海' WHEN '供电局3#仓库'THEN '广州' ELSE '未知' END FROM stock
(2) 格式二 CASE WHEN <条件表达式1> THEN <表达式1> [[WHEN <条件表达式2> THEN <表达式2>][…]] [ELSE <表达式n>] END 【例5.5】用CASE语句格式二实现：在对stock表的查询中，当 仓库号的值是“供电局1号仓库”、“供电局2号仓库”、 “供电局3号仓库”时分别返回“北京”、“上海”、“广 州”，否则返回“未知”。 SELECT mat_num,mat_name,speci, amount,unit,total, warehouse =CASE WHEN warehouse='供电局1#仓库'THEN '北京' WHEN warehouse='供电局2#仓库'THEN '上海' WHEN warehouse='供电局3#仓库'THEN '广州' ELSE '未知' END FROM stock
注:1)如果SELECT语句返回多个数值，则局部变量取最后一个 返回值。 2)SELECT语句的赋值功能和查询功能不能混合使用，否则 系统会产生错误信息。
5.1.2 显示信息
1．PRINT语句 注意：使用PRINT语句只能显示字符数据类型。 2．RAISERROR语句 语法如下： RAISERROR (<错误号>| <’错误消息’>, [严重度][, 状态[, 参数1][, 参数2]])
5.1.5 流程控制语句
【例5.1】在电力抢修工程数据库中，如果stock表 中存在库存量低于1的物资，就显示文本：the amount is not enough；否则显示所有物资信息。
IF exists(SELECT * FROM stock where amount<1) PRINT ' the amount is not enough!' ELSE BEGIN SELECT * FROM stock END
5.2.2 创建存储过程
2．带参数的存储过程
【例5.7】创建一个存储过程，通过输入的仓库名称 显示出该仓库的所有库存物资信息。 CREATE PROCEDURE exp2 @ckmc varchar(50) AS SELECT * FROM stock WHERE warehouse=@ckmc
【例5.8】创建一个带输入参数的存储过程，向stock表中添加一个 新的数据行。 CREATE PROCEDURE exp3 @mno char(8), @mname varchar(50), @mspeci varchar(20) AS INSERT INTO stock(mat_num,mat_name,speci) VALUES(@mno,@mname,@mspeci) 执行该存储过程： EXECUTE exp3 'm030','护套绝缘电线','BVV-35' 或者： EXECUTE exp3 @mno='m030', @mname='护套绝缘电线', @mspeci='BVV-35‘ 或者： EXECUTE exp3 @mname='护套绝缘电线', @mspeci='BVV-35', @mno='m030'
5.2.1 存储过程的基本概念
存储过程和非存储过程操作示意
5.2.2 创建存储过程
创建存储过程的SQL语句格式为：
CREATE PROCEDURE 存储过程名 [；版本号] [ {@参数 数据类型} [ VARYING ] [= 默认值][ OUTPUT ], ……] [ WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
执行： DECLARE @total int EXECUTE sum_mat '20110001', @total OUTPUT PRINT '该项目领取物资总量为：'+ CAST(@total AS varchar(20))
5.2.4
修改和删除存储过程
修改存储过程的语句是： ALTER PROCEDURE 存储过程名 [；版本号] [{@参数 数据类型} [ VARYING ] [=默认值][ OUTPUT ], ……] [WITH{ RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [ FOR REPLICATION ] AS SQL语句 删除存储过程的语句是： DROP PROCEDURE 存储过程名
第五章 存储过程、触发器和数据完整性
SQL Server 编程结构 存储过程 触发器 数据库完整性
1
2
3
4
5.1 SQL Server编程结构
5.1.1 变量
1．局部变量的声明格式为： DECLARE @局部变量名 数据类型 [, @局部变量名 数据类型…]
例：下面的语句声明了两个变量variable1和 variable2，数据类型分别为int和datetime。 DECLARE @variable1 int, @variable2 datetime
注:在同一个DECLARE语句中，可以同时定义多个变量，变 量之间用逗号隔开。
5.1.1 变量
2．为局部变量赋值可以采用SET语句或SELECT语句： SET @变量名=表达式 SELECT @变量名=表达式 SELECT 列1,……,列n @变量名=表达式 FROM 表名 WHERE 条件表达式
5.2 存储过程
5.2.1存储过程的基本概念 存储在数据库服务器中的一组编译成单个执 行计划的SQL语句。在使用Transact-SQL语言编程 的过程中，可以将某些需要多次调用以实现某个 特定任务的代码段编写成一个过程，将其保存在 数据库中，并由SQL Server服务器通过过程名调 用，称为存储过程。 优点：1)运行效率高，提供了在服务器端快速执行 SQL语句的有效途径。 2)降低了客户机和服务器之间的通信量。 3)方便实施企业规则。
5.1.5 流控制语句
4．GOTO语句 语法形式如下： GOTO lable …… lable: 5．RETURN语句 语法格式为： RETURN 整型表达式
6．WAITFOR语句 语法形式如下： WAITFOR {DELAY ‘时间’ | TIME ‘时间’} 其中，DELAY表示等候由“时间”参数指定的 时间间隔，TIME表示等候到指定的“时间”为止。 时间参数的数据类型为datetime，但不带日期， 格式为’hh:mm:ss’。
（1）不能在同一个批处理中删除数据库对象（表、视图或 存储过程等），然后又引用或重新创建它们。 （2）不能在同一个批处理中，修改表的列后又引用它。 （3）用SET语句设置的选项只在批处理结束时才使用，可以 将SET语句与查询在批处理中组合起来，但有些SET选项不 能在批处理中使用。
5.1.5 流程控制语句
1．BEGIN…END语句 语法形式如下： BEGIN 语句 ．．．．．． END 2．IF…ELSE语句 语法形式如下： IF 条件表达式 语句 [ELSE [IF条件表达式] 语句 ] 执行过程为：如果条件表达 式为真，则执行IF后面的 语句或语句块，如果条件 表达式为假，则执行ELSE 后面的语句或语句块。
[ FOR REPLICATION ]
AS SQL语句
5.2.2 创建存储过程
1．基本存储过程
【例5.6】创建一个最简单的存储过程，用于返回 stock表中的所有记录。 CREATE PROCEDURE exp1 执行exp1: AS EXECUTE exp1 SELECT * 或者： FROM stock EXEC exp1 执行存储过程: EXECUTE [@<状态变量>=] 存储过程名 [@<参数>=] {<值>|@<变量>}…]
注意：IF语句常与关键字子EXISTS结合使用，用于检测是否存在满 足条件的记录，只要检测到有一行记录存在，就为真。
5.1.5 流程控制语句
3．WHILE循环语句 语法形式如下： WHILE 逻辑表达式 语句 【例5.2】将stock表中所有物资单价增加10%，直到有一个 物资单价超过15000或单价总和超过50000为止。 WHILE (SELECT sum(unit) FROM stock)<50000 BEGIN UPDATE stock set unit = unit *1.1 IF EXISTS(SELECT * FROM stock WHERE unit>15000) break ELSE continue END