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

5.1.5 流程控制语句
4．GOTO语句 语法形式如下： GOTO lable …… lable: 5．RETURN语句 语法格式为： RETURN 整型表达式
6．WAITFOR语句 语法形式如下： WAITFOR {DELAY ‘时间’ | TIME ‘时间’} 其中，DELAY表示等候由“时间”参数指定的 时间间隔，TIME表示等候到指定的“时间”为止。 时间参数的数据类型为datetime，但不带日期， 格式为’hh:mm:ss’。
5.2.2 创建存储过程
3．带默认参数的存储过程
【例5.9】创建一个带默认参数的存储过程，通过传递的参 数显示物资的名称、规格、项目名称、是否按期完工等信 息，如果没有提供参数，则使用预设的默认值。 CREATE PROCEDURE exp4 @mname varchar(50)='%绝缘%', @pno char(8)='20110001' AS 执行创建的存储过程exp4。 SELECT EXECUTE exp4mat_name,speci,prj_name,prj_status 或者： FROM stock, salvaging, out_stock EXECUTE exp4 '%绝缘电线' WHERE stock.mat_num=out_stock.mat_num 或者： and salvaging.prj_num=out_stock.prj_num EXECUTE exp4 @pno='20110001' and mat_name like @mname 或者： EXECUTEand salvaging.prj_num=@pno exp4 ‘护套绝缘电线 ','20110001'
5.1.5 流程控制语句
【例5.3】使用WAITFOR语句表示等待一分钟后，显示 stock表。等到中午12:00:00时，显示salvaging表。 WAITFOR DELAY '00:01:00' SELECT * FROM stock WAITFOR TIME '12:00:00' SELECT * FROM salvaging
5.2.2 创建存储过程
注意: 为了确保Create命令能成功执行，可以在Create Procedure之前执行如下语句：
IF EXISTS (SELECT name FROM sysobjects WHERE name='exp3' and type='P') DROP PROCEDURE exp3 GO
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
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
wk.baidu.com 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'
注:1)如果SELECT语句返回多个数值，则局部变量取最后一个 返回值。 2)SELECT语句的赋值功能和查询功能不能混合使用，否则 系统会产生错误信息。
5.1.2 显示信息
1．PRINT语句 注意：使用PRINT语句只能显示字符数据类型。 2．RAISERROR语句 语法如下： RAISERROR (<错误号>| <’错误消息’>, [严重度][, 状态[, 参数1][, 参数2]])
5.2.2 创建存储过程 4．带输出参数的存储过程
【例5.10】创建一个存储过程，求某个抢修工程领 取物资的总数量。 CREATE PROCEDURE sum_mat @pn char(8), @sum int OUTPUT AS SELECT @sum=sum(amount) FROM out_stock WHERE prj_num=@pn
[ FOR REPLICATION ]
AS SQL语句
5.2.2 创建存储过程
1．基本存储过程
【例5.6】创建一个最简单的存储过程，用于返回 stock表中的所有记录。 CREATE PROCEDURE exp1 执行exp1: AS EXECUTE exp1 SELECT * 或者： FROM stock EXEC exp1 执行存储过程: EXECUTE [@<状态变量>=] 存储过程名 [@<参数>=] {<值>|@<变量>}…]
执行： 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 存储过程名
5.2.1 存储过程的基本概念
存储过程和非存储过程操作示意
5.2.2 创建存储过程
创建存储过程的SQL语句格式为：
CREATE PROCEDURE 存储过程名 [；版本号] [ {@参数 数据类型} [ VARYING ] [= 默认值][ OUTPUT ], ……] [ WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
（1）不能在同一个批处理中删除数据库对象（表、视图或 存储过程等），然后又引用或重新创建它们。 （2）不能在同一个批处理中，修改表的列后又引用它。 （3）用SET语句设置的选项只在批处理结束时才使用，可以 将SET语句与查询在批处理中组合起来，但有些SET选项不 能在批处理中使用。
5.1.5 流程控制语句
注意：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
第五章 存储过程、触发器和数据完整性
SQL Server 编程结构 存储过程 触发器 数据库完整性
1
2
3
4
5.1 SQL Server编程结构
5.1.1 变量
1．局部变量的声明格式为： DECLARE @局部变量名 数据类型 [, @局部变量名 数据类型…]
例：下面的语句声明了两个变量variable1和 variable2，数据类型分别为int和datetime。 DECLARE @variable1 int, @variable2 datetime
1．BEGIN…END语句 语法形式如下： BEGIN 语句 ．．．．．． END 2．IF…ELSE语句 语法形式如下： IF 条件表达式 语句 [ELSE [IF条件表达式] 语句 ] 执行过程为：如果条件表达 式为真，则执行IF后面的 语句或语句块，如果条件 表达式为假，则执行ELSE 后面的语句或语句块。
5.2 存储过程
5.2.1存储过程的基本概念 存储在数据库服务器中的一组编译成单个执 行计划的SQL语句。在使用Transact-SQL语言编程 的过程中，可以将某些需要多次调用以实现某个 特定任务的代码段编写成一个过程，将其保存在 数据库中，并由SQL Server服务器通过过程名调 用，称为存储过程。 优点：1)运行效率高，提供了在服务器端快速执行 SQL语句的有效途径。 2)降低了客户机和服务器之间的通信量。 3)方便实施企业规则。
5.1.3 注释语句
语法为： /*注释文本*/ 或 -- 注释文本
5.1.4 批处理
• 批处理是成组执行的一条或多条T-SQL指令，被作 为整体进行语法分析、优化、编译和执行。如果 批处理的任何部分在语法上不正确，或批处理参 照的对象不存在，则整个批处理无法执行。 • GO语句用于指定批处理语句的结束处，单独占用 一行。GO本身并不是T-SQL语句的组成部分，它只 是一个用于表示批处理结束的前端指令。 • 注意：
注:在同一个DECLARE语句中，可以同时定义多个变量，变 量之间用逗号隔开。
5.1.1 变量
2．为局部变量赋值可以采用SET语句或SELECT语句： SET @变量名=表达式 SELECT @变量名=表达式 SELECT 列1,……,列n @变量名=表达式 FROM 表名 WHERE 条件表达式
(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