简介
这三者属于数据库中高级应用,也是熟练使用数据库的基础,本篇博客复习回顾一下之前学习的内容。SQL语句是高度非过程化的编程语言,优势在于易学、方便使用。但是高度非过程化也造成了灵活度不够的缺点。而这三个高级特性可以提高灵活性,扩展数据库的功能。
存储过程相当于函数的调用,游标相当于一组数据集的下标操作,而触发器相当于一个事件机制。
以下所有代码都在MySQL5.7上进行执行。
在这里先给出该笔记用到的数据库表的结构:
Student
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | char(4) | NO | PRI | NULL | |
| Sname | varchar(20) | YES | UNI | NULL | |
| Ssex | char(1) | YES | | NULL | |
| Sage | smallint(6) | YES | | NULL | |
| Sdept | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Course
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Cno | char(4) | NO | PRI | NULL | |
| Cname | varchar(20) | NO | | NULL | |
| Cpno | char(4) | YES | MUL | NULL | |
| Ccredit | smallint(6) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
SC
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | char(4) | NO | PRI | NULL | |
| Cno | char(4) | NO | PRI | NULL | |
| Grade | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
SC_U
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Sno | char(4) | NO | PRI | NULL | |
| Cno | char(4) | NO | PRI | NULL | |
| Oldgrade | smallint(6) | NO | | NULL | |
| Newgrade | smallint(6) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
存储过程
一次存储过程,个人认为可以当做一次函数调用。
创建方式:
$ DELIMITER //
$ CREATE PROCEDURE [procedure name] (args)
BEGIN
--- 这里是存储过程
END;//
$ DELIMITER ;
参数使用IN
说明是传入参数,使用OUT
是传出的参数。最好是先重定义终止符。
代码实例,求出所有超过指定分数的所有分数的平均值。
DELIMITER //
CREATE PROCEDURE AvgGrade(IN minGrade SMALLINT, OUT avgGrade DECIMAL)
BEGIN
SELECT AVG(Grade) FROM SC WHERE Grade >= minGrade
INTO avgGrade;
END;//
DELIMITER ;
调用过程:
CALL AvgGrade(90, @avgRes);
SELECT @avgRes;
删除过程:
DROP PROCEDURE AvgGrade;
游标
游标相当于一组数据集的下标。因为SQL语句没有规定下标,所以 使用游标进行操作,这在存储过程内部结合循环处理更加合适。注意在MySQL中,游标只能在存储过程中使用。
DELIMITER //
CREATE PROCEDURE ProcessOrder()
BEGIN
--声明2个局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o CHAR(4);
--声明游标
DECLARE cur CURSOR FOR SELECT Sno FROM Student;
--循环终止状态
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
--进入循环状态
OPEN cur;
REPEAT
FETCH cur INTO o; --这里进行游标移动写入数据
UNTIL done END REPEAT;
CLOSE cur;
END; //
DELIMITER ;
触发器
触发器相当于SQL的事件处理机制,指定一个触发器,并绑定事件,那么可以在事件发生的时候,执行指定的代码。下面给出一个例子,更新SC表的内容,如果更新的分数超过原来分数的1.05倍,那么把数据写入SC表。
DELIMITER //
CREATE TRIGGER SC_T AFTER UPDATE ON SC
FOR EACH ROW
BEGIN
IF NEW.Grade >= OLD.Grade*1.05 THEN
INSERT INTO SC_U VALUES(NEW.Sno, NEW.Cno, OLD.Grade, NEW.Grade);
END IF;
END;//
DELIMITER ;