数据库系统概论——存储过程和触发器

GOTO语句
GOTO语句的语法形式为 跳转语句，跳转到标有标识符指定位置 GOTO label …… label:
【例9】利用GOTO语句求出从1加到5的总和
RETURN语句


终止语句，用于无条件终止一个查询、存储过 程或者批处理。 RETURN语句的语法形式为
RETURN [〈整数〉]
【例4】统计学号为“05101101”的学生的选课数目，如果 不少于三门课就显示“你选了××门课。很好，你完成了 任务！“否则显示“你选了××门课。选课太少，加 油！”。（其中××表示选课数目）。
USE School DECLARE @cn smallint, @text varchar(100) SET @cn=(SELECT count(StudentCode) FROM T_Grade WHERE StudentCode='05101101') IF @cn>=3 BEGIN SET @text='你选了'+CAST(@cn AS char(2)) /* CAST函数将@cn的值转换为长度为2的字符数据*/ SET @text=@text+'门课。很好，你完成了任务！' END ELSE BEGIN SET @text='你选了'+CAST(@cn AS char(2)) SET @text=@text+'门课。选课太少，加油！' END SELECT @text AS 选课提示
and ，or ，not Between…and，In，Like，Exists + 用于字符串数据的连接
Fra Baidu bibliotek
逻辑运算符


连接运算符

运算符的优先顺序

*、/、% +、=、>、<、>=、<=、<> not and between、in、like、or =
算术运算符 比较运算符 逻辑运算符 赋值运算符
例11 建立一个事务，用来将mag_dept表中depid为1 的记录的depmanager字段的值更改为“王涓涓”
例12 建立一个事务，用来更改mag_dept表中 depid为1以及depid为2的记录的depmanager字段 的值，并回滚事务到保存点
存储过程


存储过程：可以将一些固定的操作集中起来由SQL Server 数据库服务器完成，以实现某个任务；存储过程允许声明 变量、输出参数、返回单个或者多个结果集以及返回值； 存储过程存在于数据库内，可由应用程序调用执行。 存储过程分为两类：
计算自然对数lnx ROUND（x, n） 将x四舍五入为指定的长度或精度n 计算x的正弦值
(2) 日期时间函数
函数名 函数功能
DAY(x)
MONTH(x) YEAR(x)
返回指定日期中所表示的日，x是日期，例如：‘2008-11’
返回指定日期中所表示的月 返回指定日期中所表示的年份
GETDATE(x) 返回当前系统日期和时间
CASE 搜索函数
语法格式： CASE WHEN 条件表达式 THEN 结果表达式 ... [ELSE结果表达式] END 【例6】统计每个学生平均成绩并划分等级。
SELECT StudentCode AS '学号', STR(AVG(Grade),5,2) AS '平均成绩', CASE WHEN AVG(Grade)>=90 THEN 'A' WHEN AVG(Grade)>=80 THEN 'B' WHEN AVG(Grade)>=70 THEN 'C' WHEN AVG(Grade)>=60 THEN 'D' WHEN AVG(Grade)<60 THEN 'E' END AS '等级' FROM T_Grade GROUP BY StudentCodeSELECT
变量

局部变量

由用户定义和使用，可赋值并参与运算 作用域 批处理、触发器、存储过程 声明 declare @变量名 数据类型[,…] 赋值 select @变量名=值 或 SET @局部变量名=表达式 【例1】DECLARE @x float, @var char(8) 该定义语句定义了变量x是浮点实型，变量var是长度为8 的定长字符数据类型。
变量（续）

全局变量

由系统定义与维护 作用

跟踪服务器范围和特定会话期间的信息

不能显式声明或赋值 变量名前必须有@@ 值查看方式

Select @@变量名
函数
（1）字符串函数 （2）日期和时间函数 （3）数学函数 （4）转换函数 （5）系统函数 （6）聚合函数 （7）用户自定义函数
SQL Server
存储过程和触发器
主要内容
Transact-SQL编程基础 事务管理 游标 存储过程 触发器

1 Transact-SQL编程基础




标识符、运算符、变量 函数 流程控制语句 GOTO语句 RETURN语句 打印输出语句 注释语句
标识符


标识符是由用户定义的有意义的可识别的字 符序列，通常用来标识服务器、数据库、数 据库对象、常量、变量等 命名标识符时必须遵循以下规则
常用函数 (1) 数学函数
函数名
ABS(x) ATN(x) COS(x) EXP(x) LOG(x) SIN(x)
函数功能
计算x的绝对值 计算x的反正切值 计算x的余弦值 计算ex
函数名
SQRT(x) SIGN(x) TAN(x) RAND([n])
函数功能
计算x的平方根 返回x的特征符号 计算x的正切值 产生0～1之间的随机float值
流程控制语句
2．BEGIN…END语句 定义语句块：

多条T-SQL语句使用BEGIN…END组合起来形成一个语句块。 可以嵌套使用

语法形式为 BEGIN SQL语句1 SQL语句2 … END
流程控制语句
3．WHILE…CONTINUE…BREAK语句 条件循环语句

Continue可以使程序跳过其后面的语句，回到while循环的第 一行命令; Break使程序完全跳出循环，结束while语句。
多分支的选择语句----CASE


简单 CASE 函数形式：将某个表达式与一组 简单表达式进行比较以确定结果。 CASE 搜索函数形式：计算一组条件表达式 以确定结果
简单CASE函数
语法格式： CASE 输入表达式 WHEN 情况表达式 THEN 结果表达式 ... [ELSE 结果表达式] END 说明： 1)当表达式的值与某个情况表达式的值相等时，返回相 应结果表达式的值 。 2)如果全部都不相等，则返回 ELSE中的结果表达式， 若没有ELSE子句，则返回NULL值。
创建存储过程
常用存储过程的语法格式： CREATE PROC[DURE] 存储过程名 {@形式参数 数据类型}[VARYING][=默认值][OUTPUT] AS SQL语句1 … SQL语句n 说明：



“形式参数”名称必须符合标识符规则； OUTPUT表示该参数是可以返回的，可将信息返回调用者； 如果有多个参数，可以依次按以上参数定义规则列出，用逗号“,” 隔开。


系统存储过程（存储在master数据库中） 用户自定义的存储过程。

使用存储过程有以下优点：
1)可以在一个存储过程中执行多条SQL语句； 2)可多次调用； 3)创建时就在服务器端进行了编译，节省SQL语句的运行时间； 4)提供了安全机制，它限制了用户访问SQL语句的权利，只为特定用 户开放存储过程。
例13 建立一个名为“全部雇员”的存储过程，用
来查询mag_emp表的所有记录
例14 建立一个名为“雇员查询”的存储过程，
图4-51 变量查询示例1
【例3】将学号为“05101103”的学生姓名存放到变量 @sname中。
DECLARE @sname varchar(16) SELECT @sname= (SELECT StudentName FROM T_Student WHERE StudentCode='05101103') SELECT @sname as '姓名'
RIGHT(s, n) 返回字符串s右边的若 n字符 LEN(s) LTRIM(s) RTRIM(s) 返回字符串s的长度 （字符的个数） 删除字符串s开始处的 空格 删除字符串s结尾处的 空格
流程控制语句
1．IF…ELSE语句 条件判断语句，可嵌套 语法形式为 IF〈条件表达式〉 〈SQL语句1〉|〈SQL语句块1〉 [ELSE 〈SQL语句2〉|〈SQL语句块2〉]

【例5】查询07级女同学的住校情况。
SELECT StudentName AS '姓名', CASE LiveInDorm 字段：是否住校 WHEN 0 THEN '未住校' 的表达 WHEN 1 THEN '住校' END AS '是否住校' FROM T_Student WHERE Sex='女' AND LEFT(StudentCode,2)='07'

第一个字符必须是下列字符之一：

字母、下划线(_)、@或者# 字母、数字、_、#、$、@等符号
后续字符可以是：


不能使用SQL中的关键字和运算符，不允许 嵌入空格或其它特殊字符
运算符

算术运算符

+ ，- ，*， / ，% =
赋值运算符

比较运算符

= ， > ，< ， >= ，<= ，<>
(3) 字符串函数
函数名 ASCII(s) CHAR(n) LEFT(s, n) 函数功能 返回字符串s最左端字 符的ASCII码 将ASCII转换为字符 返回字符串s左边的n 个字符 函数名 STR(n) SPACE(n) 函数功能 将数字数据n转换为字符数据 返回n个空格
SUBSTRING 返回字符串s起始m长度为n 的 (s, m, n) 子串 LOWER(s) UPPER(s) REPLACE(s 1, s2, s3) 将字符串中的字母转换为小写 字母 将字符串中的字母转换为大写 字母 用s3替换s1中包含的s2
打印输出语句
可以显示变量的值 PRINT语句的语法如下 PRINT „<字符串>‟ | @<局部变量> | @@<全局变量>
例10 利用PRINT语句判断变量值的示例
注释语句
（1）整块注释
/* 注释块 */
（2）从行的后部分注释 语句 ——注释
事务管理
Begin Transaction ——事务开始 SQL语句组 Commit Transaction ——提交事务 说明： 用户定义的事务也称为显式事务 事务只用于数据修改语句 可以在commit transaction之前使用roolback transaction取 消事务并撤销对数据所作的任何改变，命令为： roolback transaction [savepoint_name] Savepoint_name为保存点，是用户放在事务中的一个标记， 指明回滚点 事务管理通过确保要么完成整个事务，要么回滚事务，来确 保数据库的一致性和可恢复性
【例2】定义变量c_code和score，并分别用SET和 SELECT语句为它们赋值，然后查询所有课程号为 c_code,且成绩小于score的学生成绩信息。
DECLARE @c_code char(6), @score Numeric(3,1) SET @c_code='130001' SELECT @score=80 SELECT * FROM T_Grade WHERE CourseCode=@c_code AND Grade<@score
while <表达式>

语法形式为 while <表达式> WHILE <条件表达式> … continue <语句或语句块> … [ BREAK或CONTINUE]
结束 本次 循环 结束 整个 循环
… break …
【例8】计算10！。
DECLARE @p int, @i smallint, @text varchar(100) SET @p=1 SET @i=1 WHILE @i<=10 BEGIN SET @p= @p*@i SET @i=@i+1 END SET @text='1×2×3×……×10='+CAST(@p AS char(10)) SELECT @text AS 计算结果