Mysql极简笔记(6)---存储过程、游标、触发器

最新推荐文章于 2024-04-29 15:14:25 发布

chuck-Gemini

最新推荐文章于 2024-04-29 15:14:25 发布

阅读量183

点赞数 1

分类专栏： # MySQL

本文链接：https://blog.csdn.net/chucksun0426/article/details/105273805

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本篇目录

一、存储过程
二、游标
三、触发器

一、存储过程

1、概念

定义：存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字，并给定参数(需要时)来调用执行。

存储过程：详细参考链接点此进入

2、应用示例

DELIMITER //									-- 更改分隔符为//
CREATE PROCEDURE ordertotal(					-- procedure：存储过程关键字
	IN onumber INT,								-- IN：输入变量（交互用）数据类型INT
	OUT ototal DECIMAL(8,2)						-- OUT:输出变量（交互用）数据类型DECIMAL
)
BEGIN											-- begin：存储过程体开始关键字
	SELECT SUM(item_price*quantity)				-- 正常语句
	FROM orderitems
	WHERE order_num = onumber
	INTO ototal;								-- 存储结果到ototal,即返回值
END//											-- end：存储过程体结束关键字
DELIMITER ;										-- 更改分隔符为；

权限：可以详细的控制创建权限，使用权限等
分隔符：用MySQL命令行客户端，为防止过程体内分号分隔符被执行破坏过程体完整性，临时变更分隔符为//

控制语句

if-else 语句：

BEGIN
	DECLARE var INT;  
	SET var=parameter+1;  
	IF var=0 THEN
		INSERT INTO t VALUES(17);  
	END IF;  

	IF parameter=0 THEN
		UPDATE t SET s1=s1+1;  
	ELSE
		UPDATE t SET s1=s1+2;  
	END IF;  
END//

case语句

BEGIN
	DECLARE var INT;  
	SET var=parameter+1;  
	CASE var  
		WHEN 0 THEN   
			INSERT INTO t VALUES(17);  
		WHEN 1 THEN   
			INSERT INTO t VALUES(18);  
		ELSE
			INSERT INTO t VALUES(19);  
	END CASE;  
END//

while语句

BEGIN
	DECLARE var INT; 
	SET var=0;  
	WHILE var<6 DO  
		INSERT INTO t VALUES(var);  
		SET var=var+1;  
	END WHILE; 
END//

repeat语句

BEGIN   
	DECLARE v INT;  
	SET v=0;  
	REPEAT
		INSERT INTO t VALUES(v);  
		SET v=v+1;  
	UNTIL v>=5 END REPEAT;  
END//

3、调用

CALL ordertotal(20005,@total);

说明：20005是输入，total为输出结果的接收变量，变量前要加@（存储过程内不用加），若要显示结果或使用结果也要加@total，例如：SELECT @total,表头为@total
未删除的存储过程，则一直保存在数据库中

4、删除及属性查询

DROP PROCEDURE ordertotal;
SHOW PROCEDURE STATUS LIKE 'ordertotal'

显示存储过程的详细信息

二、游标

1、概念

定义：由select语句返回的结果集包括满足该语句的where子句中条件的所有行。但是有时候应用程序并不总能将整个结果集作为一个单元来处理，这些应用程序需要一种机制以便每次处理结果集中的一行或一部分行。游标就是提供这种机制的结果集扩展。
应用：游标主要用在存储过程、触发器和T-SQL脚本中。
组成：
1. 游标结果集(Cursor Result Set)由定义该游标的select语句返回的行的集合
2. 游标位置(Cursor Position)指向这个集合中某一行的指针

游标：详细参考链接点此进入

2、应用示例

DELIMITER //														-- 更改分隔符为//
CREATE PROCEDURE processorders(OUT o INT)							-- procedure：存储过程关键字
BEGIN																-- begin：存储过程体开始关键字
	DECLARE done BOOLEAN DEFAULT 0; 								-- 声明循环结束判断变量done
	DECLARE ordernumbers CURSOR FOR (SELECT order_num FROM orders);	-- cursor:声明游标，它依附在FOR后面的列上 
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1；		-- 声明句柄
	OPEN ordernumbers ;												-- 打开游标，不打开不可用游标
	REPEAT															-- 循环语句
		FETCH ordernumber INTO o; 								    -- FETCH语句：将当前行数据存入变量o
	UNTIL done END REPEAT;											-- 判断句柄是否结束循环
	CLOSE ordernumbers ;											-- 关闭游标，默认遇到END也执行这个语句
END //																-- end：存储过程体结束关键字
DELIMITER ;															-- 更改分隔符为；

declare语句次序：1、局部变量声明 2、游标声明 3、句柄声明
句柄：CONTINUE HANDLER，它意思是在条件出现时被执行的代码，示例意思为当出现SQLSTATE ‘02000’ 错误时，设置done为1，“SQLSTATE ‘02000’ 错误”为循环超出最后一行，还有‘NOT FOUND’

3、fetch语句

FETCH [模式参数] 游标名 INTO 变量名

NEXT：默认项，读取当前行的下一行，若是第一次读取，为第一行FETCH ordernumber INTO o;
PRIOR：读取当前行的上一行，并把这个“上一行”置为当前行
FIRST：返回游标中的第一行并将其作为当前行FETCH FIRST ordernumber INTO o;
LAST：返回游标中的最后一行并将其作为当前行
ABSOLUTE n或 ABSOLUTE nvar（绝对位置）
FETCH ABSOLUTE 3 ordernumber INTO o; FETCH ABSOLUTE nvar ordernumber INTO o;
- n或nvar为正数：提取从游标头开始的第n行，并将返回的行变成新的当前行；
- n或nvar为负数，提取从游标尾之前的第n行，并将返回的行变成新的当前行；
- n或nvar为0，则没有行返回；
RELATIVE n或 RELATIVE nvar（相对位置）
FETCH RELATIVE 3 ordernumber INTO o; FETCH RELATIVE nvar ordernumber INTO o;
- n或nvar为正数：提取从当前行之后的第n行，并将返回的行变成新的当前行；
- n或nvar为负数，提取从当前行之前的第n行，并将返回的行变成新的当前行；
- n或nvar为0，则返回当前行；

三、触发器

1、概念

定义：触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用
特点：

名称：同一数据库中触发器名称唯一
依赖：触发器依附于表
响应：触发器响应活动UPDATE、INSERT、DELETE，此三个动作之前和之后，所以一个表最多6个触发器

2、创建

CREATE  TRIGGER 触发器名 BEFORE|AFTER UPDATE|INSERT|DELETE
ON 表名 FOR EACH ROW
BEGIN
 -- 触发器逻辑....
END;

并列选择：BEFORE和AFTER二选一，动作类似
触发器逻辑：若为一条语句，可将“BEGIN … END”替换为一个语句
FOR EACH ROW:表示针对每一行进行监控
虚拟表：NEW表：读写，对应AFTER UPDATE | INSERT；OLD表：只读，对应BEFORE UPDATE | DELETE
动作之后为NEW,动作之前为OLD
```
CREATE  TRIGGER insert_tri AFTER INSERT ON orders 
FOR EACH ROW INSERT SELECT NEW.order_num
```
功能：当在表orders中插入数据时，显示order_num；