第8讲完整性-触发器+数据库编程基础
战中的触发器例子修改为mysql格式触发器。
本节课重点：
1.主键、外键表级、列级定义
2。外键的级联删除，级联更新
3.约束定义
4.添加约束，删除约束
5.触发器编写
1.数据库完整性
1.1什么是数据库完整性
1.数据库完整性(DB Integrity)是指DBMS应保证的DB的一种特性-在任何情况下的正确性、有效性和一致性
2.广义完整性:语义完整性、并发控制、安全控制、DB故障恢复等
3.狭义完整性:专指语义完整性, DBMS通常有专门门的完整入管理机制与程
序来处理语义完整性问题。(本讲专指语义完整性)
1.2为什么会引发数据库完整性的问题呢?
1.不正当的数据库操作，如输入错误、操作失误、程序处理失误等
2.数据库完整性管理的作用
(1)防止和避免数据库中不合理数据的出现
(2)DBMS应尽可能地自动防止DB中语义不合理现象
(3)如DBMS不能自动防止，则需要应用程序员和用户在进行数据库操作时处处加以小心，每写一条SQL语句都要考虑是否符合语义完整性，这种工作负担是非常沉重的，因此应尽可能多地让DBMS来承担。
1.3DBMS怎样自动保证完整性呢?
1.DBMS允许用户定义-一些完整性约束规则(用SQL-DDL来定义)
2.当有DB更新操作时，DBMS自动按照完整性约束条件进行检查，以确保更新操作符合语义完整性
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6DN7ALCo-1589426325013)(assets/1584939690592.png)]](https://i-blog.csdnimg.cn/blog_migrate/1eeb78e2d86cacc9ff1f67cdde12fd93.png)
3.完整性约束条件(或称完整性约束规则)的- -般形式
Integrity Constraint::=(O，P,A，R)
(1)数据集合(O):约束的对象?列、多列(元组)、元组集合,列或表
(2) 谓词条件§:什么样的约束?需要定义
(3)触发条件(A):什么时候检查?更新时默认检查
(4)响应动作®:不满足时怎么办?默认拒绝
含义:对于O对象在A条件下触发,让DBMS触发P条件,看是否满足,满足时,允许查询执行程序,不满足就要响应R
1.4数据库完整性分类
1.按约束条件分类:
(1)域完整性约束条件
施加于某一列上,对给定列上所要更新的某一候选值是否可以接受进行约束条件判断,这是孤立进行的
(2)关系完整性约束条件
施加于关系/table.上,对给定table.上所要更新的某一候选元组是否可以接受进行约束条件判断,或是对一个关系中的若干元组和另一个关系中的若干元组间的联系是否可以接受进行约束条件判断
2.按照约束来源分类
(1)结构约束
来自于模型的约束，例如函数依赖约束、主键约束(实体完整性)、外键约束(参照完整性) ,只关心数值相等与否、是否允许空值等;
(2)内容约束
来自于用户的约束，如用户自定义完整性,关心元组或属性的取值范围。例如Student表的Sage属性值在15岁至40岁之间等。
3.按约束状态分类
(1)静态约束—列.表完整性
要求DB在任一时候均应满足的约束;例如Sage在任何时候都应满足大于0而小于150(假定人活最大年龄是150)。
(2)动态约束—触发器
要求DB从-状态变为另一状态时应满足的约束;例如工资只能升, 不能降:工资可以是800元,也可以是1000元;可以从800元更改为1000元，但不能从1000元更改为800元。
2 .sql语句实现数据库的静态完整性
2.1 Create Table
1.create table 有3种功能:定义关系模式,定义完整性约束,定义物理存储特性
2.整个表定义格式
CREATE TABLE tablename
-- 第一列
( colname datatype
[ DEFAULT {default_ constant | NULL} ]
[col_ constr {col_ constr...}]
|，table_ constr
-- 第二列
{, { colname datatype
[DEFAULT { default_ constant I NULL} ]
[col_ constr {col_constr...} ]
|，table_ constr }
...});
-- 即
CREATE TABLE TBNAME
(列名 类型 [默认值] [列约束],表约束),
(列名 类型 [默认值] [列约束],表约束)...;
-- 注意:上面的表约束前面要逗号隔开,
-- 而且表约束可以统一写在列最后3.Col_constr 列约束展开
➢一种域约束类型，对单一列的值进行约束
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-swI44tuP-1589426325014)(assets/1584948191910.png)]](https://i-blog.csdnimg.cn/blog_migrate/fb96ee8adb48eaf854fcc264abe3fc6d.png)
➢Col_ constr列约束:只能应用在单一列 上，其后面的约束如UNIQUE,
PRIMARY KEY及search_ cond只能是单一列唯一 、单一列为主键、 和单一列相关
➢check()中的约束条件可以是where中的任何子句
注意：外键时，delete可以变为update，实现级联更新。
4.table_constr表约束：
是应用在关系上，即对关系的多列或元组进行约束， 列约束是其特例
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GYFVJKVS-1589426325017)(assets/1584948606817.png)]](https://i-blog.csdnimg.cn/blog_migrate/c9fef32df91b32462ac4e4297e323fa3.png)
5.示例:
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ui4fNtFB-1589426325017)(assets/1584948823658.png)]](https://i-blog.csdnimg.cn/blog_migrate/6271f5d70e5497a34723b322bf6bb6ff.png)
6.当一个表定义完成后,可以使用Alter table进行表结构的修改,(不同系统可能有差异) SQLSERVER格式如下:
alter table tbname
[add (完整列定义)]
[drop (column 列名)]
[modify (完整新列定义)]
[add constraint 约束名以及约束条件]
[drop constraint 约束名]
[drop primary key];mysql中建立主键外键语法
https://blog.csdn.net/hengxingtoongxue/article/details/89529465
mysql中修改的常用约束的例子
2.2-断言Assertion
1.一个断言就是一个谓词表达式，它表达了希望数据库总能满足的条件
2.表约束和列约束就是一些特殊的断言
3.SQL还提供了复杂条件表达的断言。其语法形式为：
- CREATE ASSERTION CHECK
- 当一个断言创建后，系统将检测其有效性，并在每一次更新中测试更新是 否违反该断言。
- 断言测试增加了数据库维护的负担，要小心使用复杂的断言。
- 示例
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WjwE3DOm-1589426325018)(assets/1584949743150.png)]](https://i-blog.csdnimg.cn/blog_migrate/43e27a8aa076678ea1b117af3818e203.png)
3.用Sql语言实现数据库动态完整性
触发器
1.OPAR均需要定义,当然有的也可以省略
2.Trigger是一种过程完整性约束(相比之下，Create Table中定义的都是非过程性约束), Trigger是一段程序，该程序可以在特定的时刻被自动触发执行，比如在一次更新操作之前执行，或在更新操作之后执行
3.定义:
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6eHHpSL1-1589426325020)(assets/1584952086927.png)]](https://i-blog.csdnimg.cn/blog_migrate/e7e3b0979740bcb69ed1a1ba809d7430.png)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p6VoFmoa-1589426325022)(assets/1584952203476.png)]](https://i-blog.csdnimg.cn/blog_migrate/5e6eaaf2190808123558fbdb43bcc42e.png)
4.示例:
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nZePO5Z2-1589426325024)(assets/1584951835532.png)]](https://i-blog.csdnimg.cn/blog_migrate/e25e5d72d30eab196ede02eade386fa2.png)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d4AjDKqb-1589426325025)(assets/1584952403855.png)]](https://i-blog.csdnimg.cn/blog_migrate/667b7345d04e075302c12ebd88fb6954.png)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NZ38nXE2-1589426325027)(assets/1584952495381.png)]](https://i-blog.csdnimg.cn/blog_migrate/d32d1cd677a8c63211c5ce1295a2f464.png)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mjvGj8AT-1589426325028)(assets/1584953369155.png)]](https://i-blog.csdnimg.cn/blog_migrate/c8906fc272e71b2f8fbddff655081628.png)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zuUwXuuV-1589426325029)(assets/1584953406070.png)]](https://i-blog.csdnimg.cn/blog_migrate/6779f127f733fa6cb5471d94afe1bde0.png)
较为复杂:注意dean时一列,是一个集合,不止一个,因此P为此条件较为复杂.满足时执行R响应动作,提示.
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cS6R8LIK-1589426325030)(assets/1584954095611.png)]](https://i-blog.csdnimg.cn/blog_migrate/41030d5afb23fed681032c16101edd3e.png)
老师给的pptMYsql触发器补充
或者触发器创建的语法如下：
CREATE TRIGGER 触发器名
BEFORE | AFTER
INSERT | DELETE | UPDATE
ON 表名
FOR EACH ROW
< 触发体 >1) INSERT 触发器
在 INSERT 语句执行之前或之后响应的触发器。
• 在 INSERT 触发器代码内，可**引用(reference)**一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
• 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
2) UPDATE 触发器
• 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
• 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值
• 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
• OLD 中的值全部是只读的，不能被更新。
3) DELETE 触发器
-
在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
OLD 中的值全部是只读的，不能被更新。
-
删除触发器创建的语法如下：
DROP TRIGGER 触发器名;
注意：
-
若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。
-
若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。
-
仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行AFTER触发程序。
-
同一个表不能拥有两个具有相同触发时刻和事件的触发器。
例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，
但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，
或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。
**【例4-7】**创建触发器delete_trigger触发器，触发器将记录哪些用户删除了department表中的数据，以及删除的时间。
- 首先创建merch_log的日志信息表，用于存储用户对表的操作。
CREATE TABLE merch_log
( who VARCHAR(30),
oper_date DATE
);-
在department表上创建触发器，它会在用户对department表使用DELETE操作时触发，并向
merch_log表添加操作的用户名和日期。
CREATE TRIGGER delete_trigger
AFTER DELETE
ON department
FOR EACH ROW
INSERT INTO merch_log(who,oper_date) VALUES(USER(),SYSDATE());
- 为了测试该触发器是否正常运行，在department表中删除10号部门的记录；并查询日志信息表merch_log。
DELETE FROM department WHERE deptno=10;
SELECT * FROM merch_log;【例4-8】本例实现级联更新。在修改department表中的deptno之后（AFTER）级联地、自动地修改employee表中原来在该部门的雇员的deptno。
CREATE TRIGGER tr_dept_emp
AFTER UPDATE
ON department
FOR EACH ROW
UPDATE employee SET deptno=NEW.deptno
WHERE deptno=OLD.deptno;【例4-9】删除【例4-8】建立的tr_dept_emp触发器。
DROP TRIGGER tr_dept_emp;4.李月军第三章.数据库编程
4.1 Mysql编程基础
4.1.1常量与变量
1.常量
1）字符串常量
字符串常量指用单引号或双引号括起来的字符序列。在MySQL中推荐使用单引号。
【例3-1】查询表emp中ename值为SCOTT的雇员信息。
SELECT * FROM emp
WHERE ename='SCOTT';2）数值常量
数值常量可以分为整数常量和小数常量。
【例3-2】将表emp中，SCOTT雇员的comm值改为1250（要求用科学记数法表示）。
UPDATE emp SET COMM=1.25E+3
WHERE ename='SCOTT';3）日期和时间常量
日期和时间常量使用特定格式的字符日期值表示，用单引号括起来。
【例3-3】查询表emp中1981年以后雇用员工的ename和hiredate信息。
SELECT ename,hiredate FROM emp
WHERE hiredate>'1981/12/31';
-- 也可写为'1981-12-31'4）布尔值常量
布尔值只有true和false两个值，SQL命令运行结果用1代表true，用0代表false。
【例3-4】查询表emp中所有雇员的姓名ename和工资sal是否大于等于2000的判断结果。
SELECT ename,sal>2000 FROM emp;
5）NULL值
NULL值适用于各种字段类型，通常表示“不确定的值”，NULL值参与的运算，结果仍为NULL值。
【例3-5】将表emp雇员SCOTT的comm列值改为NULL值，然后再在NULL值的基础上加1250元，请考虑最终comm列值是什么?
UPDATE emp SET comm=NULL
WHERE ename='SCOTT';
UPDATE emp SET comm=comm+1250
WHERE ename='SCOTT';\2. 变量
1）局部变量
（1）局部变量的定义与赋值,使用在一般语句，存储函数中不用
SET @局部变量名=表达式1
[,@局部变量名=表达式2,……]
或者使用into形式，如下面示例（2）局部变量的显示
SELECT @局部变量名[,@局部变量名,……]【例3-7】查询表emp中雇员’SMITH’的job和hiredate值赋给变量job_v、hiredate_v，并显示两个变量的值。
SELECT job,hiredate INTO @job_v,@hiredate_v
FROM emp WHERE ename='SMITH';
SELECT @job_v,@hiredate_v; 【例3-8】根据name变量所给的值查询指定员工的信息。
SET @name='SCOTT';
SELECT * FROM emp WHERE ename=@name;2）全局变量
全局变量是MySQL系统提供并赋值的变量。用户不能定义全局变量，只能使用。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rltrkHrg-1589426325033)(C:\Users\english\AppData\Roaming\Typora\typora-user-images\1585115805780.png)]
4.1.2 常用系统函数
1.字符串函数
1）计算字符串字符数的函数和字符串长度的函数
CHAR_LENGTH(str)返回字符串str所包含的字符个数。
LENGTH(str)返回值为字符串的字节长度。一个汉字是3个字节，一个数字或字母是1个字节。
【例3-10】示例。
SELECT CHAR_LENGTH('CHINA'),LENGTH('CHINA')
SELECT CHAR_LENGTH('中国') 字符数,
LENGTH('中国') 字符串长度;2）合并字符串函数
CONCAT(s1,s2,……)返回结果为连接参数产生的字符串，
如果任何一个参数为NULL，则返回值为NULL。
【例3-11】示例。
SELECT CONCAT('MySQL版本：',@@version) ;3）字符串大小写转换函数
LOWER(str)是将字符串str中的字母字符全部转换成小写字母。
UPPER(str)是将字符串str中的字母字符全部转换成大写字母。
【例3-12】示例。
SET @name='sCOtt';
SELECT * FROM emp
WHERE UPPER(ename)=UPPER(@name);4）删除空格函数
LTRIM(str)返回删除前导空格的字符串str；
RTRIM(str)返回删除尾部空格的字符串str；
TRIM(str)返回删除两侧空格的字符串str。
【例3-13】示例。
SET @name=' SCOtt ';
SELECT * FROM emp
WHERE UPPER(ename)=TRIM(UPPER(@name));5）取子串函数
SUBSTRING(str,start,length)返回字符串str从start开始长度为length的子串。
【例3-14】返回emp中ename值以’S’开头的雇员信息。
SELECT * FROM emp
WHERE SUBSTRING(ename,1,1)='S';
或
SELECT * FROM emp WHERE ename LIKE 'S%';\2. 数学函数
- ABS(x)返回x的绝对值。
- PI()返回圆周率π的值。
- SQRT()返回非负数的二次方根。
- MOD (m,n)返回m被n除后的余数。
- ROUND(x,y)把x四舍五入到y指定的精度返回。如果y为负数，则将保留x值到小数点左边y位。
补充： MOD(-10,3)=-1,MOD(10,-3)=1
负数除法规则查询
【例3-15】示例。
SELECT SQRT(ROUND(ABS(-4.01*4.01),0)),
MOD(-10,3),MOD(10,-3)\3. 日期和时间函数
1）获取当前系统的日期及取日期的年、月、日函数
- CURDATE()返回当前系统日期，格式为’YYYY-MM-DD’。
- YEAR(d)、MONTH(d)、DAY(d)分别返回日期或日期时间d的年、月、日的值。
【例3-16】示例。
SELECT CURDATE(),YEAR(CURDATE()),
MONTH(CURDATE()),DAY(CURDATE());2）获取当前系统日期时间函数
CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()4个函数作用相同，
均返回当前系统的日期时间，格式为’YYYY-MM-DD HH:MM:SS’。
【例3-18】示例。
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),
NOW(),SYSDATE();【例3-19】上面例3-17也可用如下命令方式实现。
SELECT ename 姓名,
YEAR(SYSDATE())-YEAR(hiredate) 工作年限
FROM EMP WHERE ename='SMITH';
\4. 系统信息函数
- USER()返回当前登录的用户名。
- DATABASE()返回当前使用数据库名。
- VERSION()返回MySQL服务器版本号。
【例3-20】示例。
SELECT CONCAT('MySQL版本号：',VERSION(),
';用户：',USER(),DATABASE()) AS 登录信息;
\5. 条件控制函数
1）IF()函数
IF(条件表达式,v1,v2) 如果条件表达式是真则函数返回v1值，否则返回v2的值。
【例3-21】查询表emp前5条记录，显示ename和comm字段的值，当comm字段值为NULL时，显示值为0，否则显示当前字段的值。
SELECT ename,IF(comm IS NULL,0,comm) 奖金
FROM emp LIMIT 5;
2）CASE()函数
CASE 表达式
WHEN v1 THEN r1
WHEN v2 THEN r2
……
[ELSE rn]
END
【例3-22】查询’SMITH’所在部门名称。
SELECT ename 姓名,
CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
END 部门名称
FROM emp
WHERE ename='SMITH';\6. 数据类型转换函数
CAST(x AS 新类型 )、CONVERT(x 新类型）2个函数作用相同，
将一个类型的值转换为另一种类型的值。
【例3-23】示例。
SELECT ename,sal INTO @name,@salary -- 赋值
FROM EMP WHERE ename='SMITH';
SELECT CONCAT(@name,'的工资是',CAST(@salary AS CHAR(7))) 信息;4.2 程序控制流语句
4.2.1 语句块、注释和重置命令结束标记
1. 语句块
BEGIN
SQL语句 ｜ SQL语句块
END
说明：
① BEGIN…END语句块包含了该程序块的所有处理操作，允许语句块嵌套。
② 在MySQL中单独使用BEGIN…END语句块没有任何意义，
只有将其封装在存储过程、存储函数、触发器等存储程序内部才有意义。
2. 注释
1）单行注释
使用“##”符号作为单行语句的注释符，写在需要注释的行或语句单方。
【例3-24】示例。
##取两个数的最大值
SET @x=5,@y=6; ##定义两个变量并赋值
SELECT IF(@x>@y,@x,@y) 最大值;
2）多行注释
使用/和/括起来可以连续书写多行的注释语句。
【例3-25】示例。
/*在使用MySQL执行update的时候，如果不是用主键当where语句，会报错，
使用主键用于where语句中则正常。因为MySQL运行在safe-updates模式下，
该模式会导致非主键条件下无法执行update或者delete命令，
执行命令 SET SQL_SAFE_UPDATES = 0修改下数据库模式。*/
SET SQL_SAFE_UPDATES=0;
UPDATE dept_c SET deptno=50
WHERE deptno=10;3. 重置命令结束标记
DELIMITER 符号
【说明】
（1）符号可以是一些特殊符号，如两个“#”、两个“@”、两个“$”、两个“%”等。
但避免使用反斜杠“/”字符，因为它是MySQL的转义字符。
（2） 恢复使用分号作为结束标记，执行 DELIMITER ; 命令即可。
【例3-26】示例。
DELIMITER @@
SELECT * FROM emp@@
DELIMITER ;
SELECT * FROM emp;4.2.2 存储函数
\1. 存储函数的创建
CREATE FUNCTION 函数名([参数名 参数数据类型[,…]])
RETURNS 函数返回值的数据类型
BEGIN
函数体;
RETURN 语句;
END\2. 调用存储函数
SELECT 函数名([参数值[,…]])
【例3-27】创建存储函数name_fn，根据所给的部门编号deptno值，函数返回该部门的部门名称dname。
DELIMITER @@
CREATE FUNCTION name_fn(dno DECIMAL(2))
RETURNS VARCHAR(14)
BEGIN
RETURN(SELECT dname FROM dept
WHERE deptno=dno);
END@@
DELIMITER ;
SELECT name_fn(20);4.2.3 条件判断语句
\1. 程序中变量的使用
1）声明变量(在存储函数，存储过程，触发器中需要用declare)
DECLARE 局部变量名[,局部变量名,……] 数据类型 [DEFAULT 默认值];
2）为变量赋值
SET 局部变量名=表达式1
[,局部变量名=表达式2,……];
【3-29】创建求任意两个数和的存储函数sum_fn()。
DELIMITER @@
CREATE FUNCTION sum_fn(a DECIMAL(5,2),b DECIMAL(5,2))
RETURNS DECIMAL
BEGIN
DECLARE x,y DECIMAL(5,2);
SET x=a,y=b;
RETURN x+y;
END@@
DELIMITER ;
SELECT sum_fn(7,3);2．IF语句
1）形式一
IF <条件> THEN
SQL语句块1;
[ELSE
SQL语句块2; ]
END IF;【例3-30】 创建函数max_int，判断整型变量a和b 的大小。
DELIMITER @@
CREATE FUNCTION max_fn(a int,b int)
RETURNS INT
BEGIN
IF a>b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END@@
DELIMITER ;
SELECT CONCAT('最大值:',CONVERT(max_fn(7,8),CHAR(3))) ;
-- 注意数据类型转换为字符类型2）形式二
I
F <条件1> THEN
SQL语句块1;
ELSEIF <条件2> THEN
SQL语句块2;
……
ELSE
SQL语句块n;
END IF;【例3-31】 创建判断某一年是否为闰年的函数leap_year()。
闰年的判断条件为：年值能被4整除但不能被100整除，或者能被400整除。
DELIMITER@@
CREATE FUNCTION LEAP_YEAR(YEAR_DATE INT)
RETURNS VACHAR(30)
BEGIN
DECLARE LEAP BOOLEAN;
IF MOD(YEAR_DATE ,4)<>0 THEN
SET LEAP=FALSE;
ELSEIF MOD(YEAR_DATE,1OO)<>0 THEN
SET LEAP=TRUE;
ELSEIF MOD(YEAR_DATE,400)<>0 THEN
SET LEAP=FALSE;
ELSE
SET LEAP=TRUE;
END IF;
IF LEAP THEN
RETURN CONCAT(CONVERT(YEAR_DATE,CHAR(4)),'YES');
ELSE
RETURN CONCAT(CONVERT(YEAR_DATE,CHAR(4)),'NO');
END IF;
END@@
DELIMITER ;
SELECT LEAP_YEAR(2000);
3．CASE语句
1）形式一
CASE <表达式>
WHEN <表达式值1> THEN SQL语句块1;
WHEN <表达式值2> THEN SQL语句块2;
……
WHEN <表达式值n> THEN SQL语句块n;
[ ELSE SQL语句块n+1; ]
END;【例3-32】判断显示emp表中前3条记录的姓名和职务。
SELECT ename 姓名,CASE job
WHEN 'SALESMAN' THEN '销售员'
WHEN 'CLERK' THEN '管理员'
ELSE '经理'
END AS 职务
FROM EMP LIMIT 3;2）形式二
CASE
WHEN <条件1> THEN SQL语句块1;
WHEN <条件2> THEN SQL语句块2;
……
WHEN <条件n> THEN SQL语句块n;
ELSE SQL语句块n+1;
END;
【例3-33】 判断显示emp表前3条记录的姓名ename、基本工资sal和工资等级。
SELECT ename,sal,CASE
WHEN sal BETWEEN 700 AND 1200 THEN '一级'
WHEN sal BETWEEN 1201 AND 1400 THEN '二级'
WHEN sal BETWEEN 1401 AND 2000 THEN '三级'
WHEN sal BETWEEN 2001 AND 3000 THEN '四级'
ELSE '五级'
END 工资等级
FROM emp LIMIT 3;4.2.4 循环语句
1．LOOP循环
标签：LOOP
SQL语句块;
IF <条件表达式> THEN
LEAVE 标签;
END IF;
END LOOP;
【例3-34】LOOP循环语句示例。创建sum_fn()存储函数，返回1~n的和。
DELIMITER @@
CREATE FUNCTION sum_fn(n int)
RETURNS INT
BEGIN
DECLARE s,i INT;
SET s=0,i=1;
loop_label: LOOP
SET s=s+i;
SET i=i+1;
IF i>n THEN
LEAVE loop_label;
END IF;
END LOOP;
RETURN s;
END@@
DELIMITER ;
SELECT sum_fn(5);2．WHILE循环
WHILE <条件表达式> DO
SQL语句块;
END WHILE;【例3-35】WHILE循环语句示例。创建sum_fn()存储函数，返回1~n的和。
DELIMITER @@
CREATE FUNCTION sum_fn(n int)
RETURNS INT
BEGIN
DECLARE s,i INT;
SET s=0,i=1;
WHILE i<=n DO
SET s=s+i;
SET i=i+1;
END WHILE;
RETURN s;
END@@
DELIMITER ;
SELECT sum_fn(5);3．REPEAT循环
REPEAT
SQL语句块;
UNTILE <条件表达式>
END REPEAT;
【例3-36】REPEAT循环语句示例。创建sum_fn()存储函数，返回1~n的和。
DELIMITER @@
CREATE FUNCTION sum_fn(n int)
RETURNS INT
BEGIN
DECLARE s,i INT;
SET s=0,i=1;
REPEAT
SET s=s+i;
SET i=i+1;
UNTIL i>n
END REPEAT;
RETURN s;
END@@
DELIMITER ;
SELECT sum_fn(5);4.3 存储过程
使用存储过程的优点有:
(1)存储过程在服务器端运行，执行速度快。
(2)存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。
(3)确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。
n 4.3.1 创建存储过程
CREATE PROCEDURE 存储过程名()
BEGIN
过程体;
END【例3-37】创建存储过程emp_p，在emp表中查询职工编号为7369员工的姓名和工作。
DELIMITER @@
CREATE PROCEDURE emp_p()
BEGIN
SELECT ename,job FROM emp
WHERE empno=7369;
END@@n 4.3.2 调用存储过程
CALL 存储过程名();
【例3-38】调用执行例3-37所创建的存储过程。
DELIMITER ;
CALL emp_p();
n 4.3.3 存储过程的参数
带参数的存储过程创建语法格式如下。
有三种类型参数
- 当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存 储过程支持三种类型的参数:输入参数、输出参数和输入/输出参数，关键字分别是INOUT和INOUT。
- IN输入参数使数据可以传递给-一个存储过程。当需要返回一个答案或结果的时候，存储过程使用OUT输出参数。INOUT输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数，但是名称后面的括号是不可省略的。
CREATE PROCEDURE 存储过程名(
[ IN | OUT | INOUT] 参数1 数据类型,
[ IN | OUT | INOUT] 参数2 数据类型,……
)
BEGIN
过程体；
END1．IN参数
【例3-39】创建一个向DEPT表中插入新记录的存储过程dept_p1。
DELIMITER @@
CREATE PROCEDURE dept_p1(
IN p_deptno DECIMAL(2,0),
IN p_dname VARCHAR(14),
IN p_loc VARCHAR(13)
)
BEGIN
INSERT INTO dept
VALUES(p_deptno,p_dname,p_loc);
END@@
-- 进行验证
DELIMITER ;
CALL dept_p(50,'HR','CHINA');
SELECT * FROM dept WHERE deptno=50; 2．OUT参数
【例3-40】创建存储过程dept_p2，该过程根据提供的部门编号，返回部门的名称和地址。
DELIMITER @@
CREATE PROCEDURE dept_p2(
IN i_no DECIMAL(2,0),
OUT o_name VARCHAR(14),
OUT o_loc VARCHAR(13)
)
BEGIN
SELECT dname,loc INTO o_name,o_loc FROM dept
WHERE deptno=i_no;
END@@
DELIMITER ;
-- 声明两个变量表示输出参数，用select显示变量
CALL dept_p2(10,@v_dname,@v_loc);
SELECT @v_dname,@v_loc;3．INOUT参数
【例3-41】使用INOUT参数实现两个数的交换。
DELIMITER @@
CREATE PROCEDURE swap(
INOUT p_num1 int,
INOUT p_num2 int
)
BEGIN
DECLARE var_temp int;-- 存储函数内变量定义
SET var_temp=p_num1;
SET p_num1=p_num2;
SET p_num2=var_temp;
END@@
DELIMITER ;
SET @v_num1=1;-- 定义一般局部变量并赋值
SET @v_num2=2;
CALL swap(@v_num1,@v_num2);
SELECT @v_num1,@v_num2;n 4.3.4 删除存储过程
DROP PROCEDURE 存储过程名;
【例3-42】删除已创建的存储过程emp_p。
DROP PROCEDURE emp_p;
n 4.4 游 标
4.4.1 游标定义和使用
MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行(零行或多行)。使用简单的SELECT语句，例如，没有办法得到第- -行、下一行或前10行，也不存在每次- -行地处理所有行的简单方法(相对于成批地处理它们)。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。MySQL游标只能用于存储过程(和函数)。
1．声明游标
在能够使用游标前**，必须声明(定义)**它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
DECLARE 游标名 CURSOR FOR SELECT 语句;2．打开游标
一旦声明后**，必须打开游标以供使用**。这个过程用前面定义的SELECT语句把数据实际
检索出来。查询结果送到了游标工作区.
OPEN 游标名;
3．提取数据
在一个游标被打开后，可以使用FETCH语句分别访问它的每- -行。FETCH指定检索
什么数据(所需的列)，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行(不重复读取同一行)。
FETCH 游标名 INTO 变量名1[，变量名2……];
4．关闭游标
CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。
CLOSE 游标名;
【例3-43】创建存储过程emp_p，用游标提取emp表中7788雇员的姓名和职务。
DELIMITER @@
CREATE PROCEDURE emp_p()
BEGIN
DECLARE v_ename VARCHAR(14); --声明变量
DECLARE v_job VARCHAR(13);
DECLARE emp_cursor CURSOR -- 声明游标 ，只能在存储过程和存储函数中
FOR SELECT ename,job FROM emp WHERE empno=7788;
OPEN emp_cursor; --打开游标
FETCH emp_cursor INTO v_ename,v_job; --查询中是两个列，因此赋值给这两个变量
CLOSE emp_cursor; -- 关闭游标
SELECT v_ename,v_job; --显示变量
END@@
DELIMITER ;
CALL emp_p();-- 调用存储过程【例3-44】创建存储过程emp_p1，用游标显示工资最高的前3名雇员的姓名和工资。
DELIMITER @@
CREATE PROCEDURE emp_p1()
BEGIN
DECLARE v_ename VARCHAR(14);
DECLARE v_sal DECIMAL(7,2);
DECLARE i INT;
DECLARE mycursor CURSOR
FOR SELECT ename,sal FROM emp ORDER BY sal DESC
LIMIT 3; -- 显示前三名limit
SET i=1;
CREATE TABLE result( -- 将结果建立一个表来保存
ename VARCHAR(14),
sal DECIMAL(7,2)
);
OPEN mycursor; -- 打开游标
WHILE i<=3 DO
FETCH mycursor INTO v_ename,v_sal; --取游标中相应行存入变量
INSERT INTO result VALUES(v_ename,v_sal);-- 将此数据插入结果表
SET i=i+1; --循环进行
END WHILE;
CLOSE mycursor;
SELECT * FROM result;
END@@
DELIMITER ;
CALL emp_p1();-- 调用存储过程，有一个问题，如果多次调用的话，那么那个新建表的操作会有问题，因为会重复建立相同的表，所以可以先把表放在外面进行建立。4.4.2 异常处理
DECLARE 错误处理类型
HANDLER FOR 错误触发条件
自定义错误处理程序;
1.错误处理类型: COUNTINUE和EXIT
- COUNTINUE表示:错误发生后立即执行自定义错误处理程序，然后忽略该错误继续执行其他语句;
- EXIT表示:错误发生后立即执行自定义错误处理程序,然后立刻停止其他语句的执行;
2.错误触发条件。错误触发条件定义了自定义错误处理程序运行的时机。错误触发条件的形式如下:
SQLSTATE 'ANSI标准错误代码'
｜MySQL错误代码
｜SQLWARNING -- 所有以01开头的SQLSTATE代码的速记
｜NOT FOUND -- 所有以02开头的SQLSTATE代码的速记
｜SQLEXCEPION -- 对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。【例3-45】创建存储函数emp_ins_fun，向emp表插入一条记录,empno和ename字段的值为7396, ‘MARY’，已知7369雇员编号已存在于emp表中，违背了主键约束。
DELIMITER @@
CREATE FUNCTION emp_ins_fun(no DECIMAL(4,0),name VARCHAR(14))
RETURNS VARCHAR(20)
BEGIN
INSERT INTO emp(empno,ename)
VALUES(no,name);
RETURN '插入成功';
END@@
DELIMITER ;
SELECT emp_ins_fun(7369,'MARY');
-- 上面会出现异常
-- 如下修改
DELIMITER @@
CREATE FUNCTION EMP_INS_FUN(NO DECIMAL(4,0),NAME VARCHAR(14))
RETURNS VARCHAR(20) CHARACTER SET utf_8 --设置中文字符
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
RETURN '违反主键约束！'
INSERT INTO EMP(EMPNO,ENAME)VALUES(NO,NAME);
RETURN '插入成功'
END@@
DELIMITER ;
SELECT EMP_INS_FUN(7369,'MARY');【例3-46】创建存储过程emp_up_pro，使用游标更新emp_c表（与emp表相同）中的comm值。
DELIMITER @@
CREATE PROCEDURE emp_up_pro()
BEGIN
DECLARE v_empno DECIMAL(4,0);
DECLARE v_sal DECIMAL(7,2);
DECLARE v_comm DECIMAL(7,2);
DECLARE flag BOOLEAN DEFAULT TRUE;
DECLARE comm_cur CURSOR --声明游标
FOR SELECT empno,sal FROM emp_c;
DECLARE CONTINUE HANDLER FOR NOT FOUND --异常处理
SET flag=FALSE;
OPEN comm_cur; -- 打开游标
WHILE flag DO
FETCH comm_cur INTO v_empno,v_sal; --取游标的值给变量赋值
IF v_sal<500 THEN SET v_comm=v_sal*0.25;
ELSEIF v_sal<1000 THEN SET v_comm=v_sal*0.2;
ELSEIF v_sal<3000 THEN SET v_comm=v_sal*0.15;
ELSE SET v_comm=v_sal*0.12;
END IF; -- if语句结束
UPDATE emp_c SET comm=v_comm
WHERE empno=v_empno;
END WHILE; -- 循环结束
CLOSE comm_cur;--关闭游标
END@@
4.5 嵌入式SQL
n 4.5.1 SQL****与宿主语言接口
\1. 区分主语言语句与SQL语句
当主语言为C语言时，语法形式为：
EXEC SQL SQL语句;
\2. 嵌入式SQL语句与主语言的通信
程序执行过程中，主语言需要和SQL语句进行信息交换，其间的通信过程如下。
（1）SQL语句将执行状态信息传递给主语言。主语言得到该状态信息后，可根据此状态信息来控制程序流程，以控制后面的SQL语句或主语言语句的执行。向主语言传递SQL执行状态信息，主要用SQL通信区（SQL Communication Area，SQLCA）实现。
（2）主语言需要提供一些变量参数给SQL语句。该方法是在主语言中定义主变量（Host Variable），在SQL语句中使用主变量，将参数值传递给SQL语句。
（3）将SQL语句查询数据库的结果返回给主语言作进一步处理。如果SQL语句向主语言返回的是一条数据库记录，可使用主变量；若返回值为多条记录的集合，则使用游标。
n 4.5.2 SQL****通信区
SQLCA是一个数据结构，在程序的主语言中用EXEC SQL INCLUDE SQLCA加以定义。SQLCA中有一个系统变量SQLCODE，用来存放每次执行SQL语句后返回的代码。
应用程序每执行一条SQL语句之后均测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则在SQLCODE存放错误代码。程序员可以根据错误代码查找问题。
因此，系统变量SQLCODE用于向主语言提供SQL语句执行的状态。例如，在执行更新语句UPDATE后，SQLCA用如下状态之一返回其执行结果。
（1）执行成功（SQLCA=SUCCESS），并有更新的行数；
（2）违反完整性约束，更新操作被拒绝执行；
（3）没有满足更新条件的行，一行也没有更新；
（4）由于其他原因，执行出错。
n 4.5.3 主变量的定义与使用
1．主变量的定义
【例3-47】主变量定义示例。
EXEC SQL BEGIN DECLARE SECTION;
char msno[4],mcno[3],givensno[5];
int mgrade;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
2．在SELECT语句中使用主变量
【例3-48】在SELECT查询中使用主变量示例。根据主变量givensno值查询成绩表grade中学生的学号、课号和分数。
EXEC SQL SELECT 学号,课号,分数
INTO msno,mcno,mgrade
FROM grade
WHERE 学号=:givensno;
3．在INSERT语句中使用主变量
【例3-49】在INSERT中使用主变量示例。某学生选修了一门课程信息，将其插入成绩表grade中，假设学号、课号、分数已分别赋给主变量hsno、hcno、hgrade。
EXEC SQL INSERT INTO grade(学号,课号,分数)
VALUES(:hsno,:hcno,:hgrade);
4．在UPDATE语句中使用主变量
【例3-50】在UPDATE中使用主变量示例。更新grade表中指定学生指定课程的分数。
EXEC SQL UPDATE grade
SET 分数=:mgrade
WHERE 学号=:msno and 课号=:mcno;
5．在DELETE语句中使用主变量
【例3-60】在DELETE中使用主变量示例。删除grade表中指定学生信息。
EXEC SQL DELETE FROM grade
WHERE 学号=:msno;
4.5.4 嵌入式SQL中的游标定义与使用
1．声明游标
EXEC SQL DECLARE 游标名 CURSOR
FOR SELECT 语句;
2．打开游标
EXEC SQL OPEN 游标名;
3．提取游标中的记录
EXEC SQL FETCH FROM 游标名 INTO 主变量[，主变量，…];
4．关闭游标
EXEC SQL CLOSE 游标名;
n 4.5.5 动态SQL语句
（1）动态SQL预备语句
EXEC SQL PREPARE 动态SQL语句名 FROM 共享变量或字符串;
（2）动态SQL执行语句
EXEC SQL EXECUTE 动态SQL语句名;
n 【例3-62】下面两个C语言的程序段说明了动态SQL语句的使用技术。
① EXEC SQL BEGIN DECLARE SECTION;
char *query;
EXEC SQL END DECLARE SECTION;
scanf("%s",query);
EXEC SQL PREPARE que FROM :query;
EXEC SQL EXECUTE que;
这个程序段表示从键盘输入一个SQL语句到字符数组中；字符指针query指向字符串的第1个字符。
如果执行语句只做一次，那么程序段最后两个语句可合并成一个语句：
EXEC SQL EXECUTE IMMEDIATE :query;
② char *query=“DELETE FROM grade WHERE 学号=?”;
EXEC SQL PREPARE dynprog FROM :query;
char sno[5]=“1001”;
EXEC SQL EXECUTE dynprog USING :sno;
n 4.6 小 结
n 本章介绍了MySQL编程的基础知识，变量的声明和使用，及编程中常用的系统函数。
n 存储程序中的IF语句可执行简单条件判断、二重分支判断和多重分支判断。当使用IF语句时，注意END IF是两个词，而ELSEIF是一个词。CASE语句执行多重分支判断。LOOP语句、WHILE语句和REPEAT语句执行循环控制操作的方法。
n 在使用SELECT语句查询数据库时，查询返回的数据存放在结果集中。用户在得到结果集后，需要逐行逐列地获取其中存储的数据，从而在应用程序中使用这些值，游标机制可完成此类的操作。
n 当存储程序运行错误时，可以使用异常处理的方法来处理发生的错误。
n 存储过程是指用于执行特定操作的SQL语句的集合，在需要时可以直接调用，提高代码的重用性和共享性。
INTO msno,mcno,mgrade
FROM grade
WHERE 学号=:givensno;
3．在INSERT语句中使用主变量
【例3-49】在INSERT中使用主变量示例。某学生选修了一门课程信息，将其插入成绩表grade中，假设学号、课号、分数已分别赋给主变量hsno、hcno、hgrade。
EXEC SQL INSERT INTO grade(学号,课号,分数)
VALUES(:hsno,:hcno,:hgrade);
4．在UPDATE语句中使用主变量
【例3-50】在UPDATE中使用主变量示例。更新grade表中指定学生指定课程的分数。
EXEC SQL UPDATE grade
SET 分数=:mgrade
WHERE 学号=:msno and 课号=:mcno;
5．在DELETE语句中使用主变量
【例3-60】在DELETE中使用主变量示例。删除grade表中指定学生信息。
EXEC SQL DELETE FROM grade
WHERE 学号=:msno;
4.5.4 嵌入式SQL中的游标定义与使用
1．声明游标
EXEC SQL DECLARE 游标名 CURSOR
FOR SELECT 语句;
2．打开游标
EXEC SQL OPEN 游标名;
3．提取游标中的记录
EXEC SQL FETCH FROM 游标名 INTO 主变量[，主变量，…];
4．关闭游标
EXEC SQL CLOSE 游标名;
n 4.5.5 动态SQL语句
（1）动态SQL预备语句
EXEC SQL PREPARE 动态SQL语句名 FROM 共享变量或字符串;
（2）动态SQL执行语句
EXEC SQL EXECUTE 动态SQL语句名;
n 【例3-62】下面两个C语言的程序段说明了动态SQL语句的使用技术。
① EXEC SQL BEGIN DECLARE SECTION;
char *query;
EXEC SQL END DECLARE SECTION;
scanf("%s",query);
EXEC SQL PREPARE que FROM :query;
EXEC SQL EXECUTE que;
这个程序段表示从键盘输入一个SQL语句到字符数组中；字符指针query指向字符串的第1个字符。
如果执行语句只做一次，那么程序段最后两个语句可合并成一个语句：
EXEC SQL EXECUTE IMMEDIATE :query;
② char *query=“DELETE FROM grade WHERE 学号=?”;
EXEC SQL PREPARE dynprog FROM :query;
char sno[5]=“1001”;
EXEC SQL EXECUTE dynprog USING :sno;
n 4.6 小 结
n 本章介绍了MySQL编程的基础知识，变量的声明和使用，及编程中常用的系统函数。
n 存储程序中的IF语句可执行简单条件判断、二重分支判断和多重分支判断。当使用IF语句时，注意END IF是两个词，而ELSEIF是一个词。CASE语句执行多重分支判断。LOOP语句、WHILE语句和REPEAT语句执行循环控制操作的方法。
n 在使用SELECT语句查询数据库时，查询返回的数据存放在结果集中。用户在得到结果集后，需要逐行逐列地获取其中存储的数据，从而在应用程序中使用这些值，游标机制可完成此类的操作。
n 当存储程序运行错误时，可以使用异常处理的方法来处理发生的错误。
n 存储过程是指用于执行特定操作的SQL语句的集合，在需要时可以直接调用，提高代码的重用性和共享性。
扫一扫
1452
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
