MySQL必知必会-第23章 使用存储过程（CREATE PROCEDURE、CALL、DROP PROCEDURE、SHOW PROCEDURE STATUS）

存储过程

1.存储过程相关概念

1.1 存储过程的定义

1）存储过程实际上是一种函数，使用MySQL语言进行编程，内部可以封装SQL语句以及各种运算；
2）区别于视图：存储过程和视图有很大的差别。
视图只能封装单个SQL语句（这个SQL语句可以很复杂），是为了避免重复使用复杂的SQL语句；
存储过程内部可以封装多个SQL语句，同时还能运行复杂的计算逻辑；

1.2 存储过程的意义

优点：
1）简化复杂的操作；
2）把一系列处理步骤都封装在同一存储过程中，就可以用反复建立这一系列处理步骤，防止错误，保证数据的一致性；
3）提高性能，存储过程比使用单独的SQL语句更快；【重点】
总结：简单、安全、高性能；
缺点：
1）存储过程的编写比基本SQL语句更加复杂；
2）许多数据库管理员限制存储过程的创建权限，只开放存储过程的使用权限

2.使用存储过程

2.1 创建简单的存储过程

-- 1.创建存储过程的基本语法
CREATE PROCEDURE 存储过程名(输入/输出参数列表)
BEGIN
	存储过程体
END;
-- 2.创建一个返回平均成绩的存储过程
CREATE PROCEDURE getAvgScore()
BEGIN
	SELECT AVG(score) as score_AVG
	from scoretable;
END;

【说明】
1）这里定义了一个存储过程，名为getAvgScore；
2）没有输入参数（传入参数），也没有输出参数（返回值）
3）通过如下方式调用，参考调用函数；

-- 1. 调用存储过程的基本语法
CALL 存储过程名(实际输入输出参数列表);
-- 2. 这里调用求平均分的存储过程
CALL getAvgScore();

2.2 创建包含输出参数（返回值）的存储过程

-- 参数声明格式  IN/OUT 参数名 参数类型
-- OUT表示该过程执行完之后，某个结果会输出到该变量，可以在这个变量的位置上用实际变量来接收其输出的值
-- IN表示外部输入到存储过程中的值
-- INOUT可以表示内部输出也可以表示外部输入
-- 1.例子
CREATE PROCEDURE productpricing(OUT pl DECIMAL(8,2),OUT ph DECIMAL(8,2)OUT pa DECIMAL(8,2))
BEGIN
	SELECT MIN(prod_price) INTO pl -- 这里SELECT ... INTO将SELECT出来的值赋值给输出变量pl
	FROM products; 
	
	SELECT MAX(prod_price) INTO ph -- 这里【SELECT ... INTO】将SELECT出来的值赋值给输出变量ph
	FROM products; 	
	
	SELECT AVG(prod_price) INTO pa -- 这里通过SELECT ... INTO将SELECT出来的值赋值给输出变量pa
	FROM products;
END
-- 2.如何调用上述存储过程
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
-- 3.通过select 实际参数    的方式检索返回值
select @pricelow,@pricehigh,@priceaverage;

【说明】
1）上述存储过程输出了三个OUT变量(形式参数) pl ph pa
在调用过程中需要定义@pricelow,@pricehigh,@priceaverage
三个“实际参数”分别用来接收输出的pl ph pa三个值；
2）所有的MySQL变量都需要以@符号打头：
3）调用完存储过程之后，通过select 实际参数的方式检索存储过程的返回值；

2.3 创建包含输入参数的存储过程

例子：创建一个存储过程，接收一个订单号，返回该订单号里所有物品价格的合计

-- 1.创建存储过程
CREATE PROCEDURE orderTotal(IN onumber INT,OUT ototal DECIMAL(8,2))
BEGIN
	SELECT SUM(item_price*quantity) -- 商品价格×商品数量
	from orderitems
	where order_num = onumber		-- 订单编号等于传进来的形参
	INTO ototal;	-- 将统计完的合计返回给输出形参 ototal
END;

-- 2.调用存储过程
-- 这里的@total是用于接收输出的实际参数，区别于用OUT方式声明的输出形参
CALL orderTotal(20005, @total);
-- 3.检索输出结果/返回值
select @total; -- 使用实际参数

2.4 创建复杂存储过程

例子：
1）获得合计；
2）把营业税有条件的添加到合计里；
3）返回合计；

-- I. 创建存储过程
CREATE PROCEDURE ordertotal(-- 参数列表
	IN onumber INT,  -- INT类型的输入形参
	IN taxable BOOLEAN, -- BOOLEAN类型的输入形参
	OUT ototal DECIMAL(8,2) -- DECIMAL(8,2)类型的输出形参
)COMMENT 'Obtain order totalm, optionally adding tax' -- 添加存储过程的备注
BEGIN
	-- 1.声明变量, 最后赋值给ototal输出
	DECLARE total DECIMAL(8,2) -- 声明DECIMAL(8,2)类型的变量total用于存储结果的中间中
	-- 2.声明变量 存储税率
	DECLARE taxrate INT DEFAULT 6; -- 声明INT类型的变量taxrate用于存储税率基数。初始化为 6
	
	-- 3.获得订单总额
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber -- 只统计输入形参onumber 指定的订单号的总额
	INTO total; -- 将总额中间结果存储给total变量
	
	-- 4.判断是否需要缴税
	IF taxable THEN -- 根据输入形参taxable判断是否进行下一步计算
		-- 需要，添加税率
		SELECT total + (total/100 * taxrate) INTO total;
	END IF;
	
	-- 5. 最后将总额中间结果赋值给输出形参ototal
	SELECT total INTO ototal;
END;
-- 2.1 调用存储过程
CALL ordertotal(20005, 0, @total);
-- 2.2 检索返回结果
SELECT @total;
-- 3.1 调用存储过程
CALL ordertotal(20005, 1, @total);
-- 3.2 检索返回结果
SELECT @total;

详细的存储过程创建和MySQL编程参考：
https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html
【说明】
1）COMMENT关键字：
可以使用**SHOW PROCEDURE STATUS;**列出所有存储过程的信息；可以通过【LIKE】关键字添加过滤;
如：SHOW PROCEDURE STATUS LIKE ‘ordertotal’;
2）IF语句：IF语法还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）；

2.5 删除存储过程

-- 删除存储过程的基本语法
DROP PROCEDURE 存储过程名;
DROP PROCEDURE IF EXISTS 存储过程名;

【说明】
1）删除存储过程不是调用，存储过程名后面不需要加 ();
2）要删除的存储过程不存在的情况下删除存储过程会报错，尽量使用DROP PROCEDURE IF EXISTS子句删除存储过程，避免报错；
3）存储过程一旦创建就会一直保留在MySQL中，即使重启服务、重启服务器。可以通过如下语句查看现有的存储过程，删除不需要的存储过程：

-- 查看指定数据库的所有存储过程名，决定是否删除
select `name` from mysql.proc where db = '数据库名' and `type` = 'PROCEDURE';

2.6 修改存储过程

修改存储过程可以用下面2.7中的方式查看要修改的存储过程，修改想要修改的地方，然后新建存储过程来代替原有的存储过程。
如果确定，可以删除原有的存储过程。

2.7 检查存储过程

1）如果想知道一个存储过程是如何创建的，使用SHOW CREATE PROCEDURE 语句；
2）如果想要获得 何时、有谁创建等存储过程详细信息列表，可以使用SHOW PROCEDURE STATUS [ LIKE ‘XXXXX’]；

-- 1.查看存储过程是如何创建的基本语法
SHOW CREATE PROCEDURE 存储过程名;
-- 2.查看存储过程详细信息列表的基本语法
SHOW PROCEDURE STATUS [ LIKE 'XXXXX']; -- 其中LIKE可以指定过滤模式，查看指定的存储过程详细信息

2.8 *有关MySQL命令行终端语句分隔符的说明

1）MySQL命令行终端的默认语句分隔符为";"，语句敲完后，需要敲入";“分号才能表示一条命令输完，然后执行命令。
2）在MySQL命令行终端编写存储过程，就会引起存储过程中的分号”;“与MySQL命令行终端语句分隔符分号”;"的歧义；
3）因此，在MySQL命令行终端编写存储过程的时候，需要【临时】修改终端默认的语句分隔符；

-- 1.将终端的默认语句分隔符修改为双斜杠符号"//"
DELIMITER //           

-- 2. 创建存储过程
CREATE PROCEDURE 存储过程名(输入/输出形参列表)
BEGIN
	-- 2.1【由于修改了终端的默认语句分隔符, 因此创建存储过程中使用的";"不会引起歧义】
	存储过程体;
END //  -- 2.2【特别注意】 END后面需要接的是语句分隔符，已经改成//了

-- 3.将默认的语句分隔符改回分号";"
DELIMITER ; 

【说明】除了反斜杠符号’’，任何字符都可以用作语句分隔符。

3.使用游标

【注意】MySQL的游标只能用于存储过程（或函数）中。

3.1 使用游标的步骤

1）声明（定义）游标，指定要使用的SELECT语句，select定义了游标中可以存储哪些列；
2）打开游标，用前面指定的SELECT语句把数据实际地检索出来；
3）对填有数据的游标，根据需要检索出各行；
4）关闭游标

3.2 创建游标

-- 创建游标的基本语法
CREATE PROCEDURE 过程名()
BEGIN
	 -- 1.定义游标，指定SELECT语句
	 DECLARE 游标名 CURSOR
	 FOR 
	 SELECT语句;
	 -- 2.打开游标
	 OPEN 游标名;
	 -- 3.使用游标数据(一般需要循环) 将游标中存储的每列取出来
	 FETCH 游标名 INTO 局部变量1/输出形参1, 局部变量2/输出形参2,...;
	 -- 4.关闭游标
	 CLOSE 游标名;
END;

【说明】
1）游标声明定义之后必须打开才能使用，关闭了的游标也必须再打开才能使用；声明定义之后就不用重复声明定义了；
2）使用完游标后应当尽量关闭游标，释放游标所使用的所有内部内存和资源，如果不明确关闭游标，MySQL会在达到END;语句时自动关闭游标；

3.2 使用游标数据

直接看个例子：计算每个订单的带税合计然后存入新表中

-- 创建存储过程
CREATE PROCEDURE processorder()
BEGIN
	-- 1.声明局部变量    
	DECLARE t DECIMAL(8,2);
	DECLARE o INT;
	DECLARE done BOOLEAN DEFAULT 0;
	
	-- 2.声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 3.声明循环句柄
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

	-- 4.创建表存储结果
	CREATE TABLE IF NOT EXISTS orderstotals
		(order_num INT, total DECIMAL(8,2)) -- 这里的order_num 和 total 是新表的列名 与上面无关
	
	--5. 打开游标
	OPEN ordernumbers;

	--6.开始循环，遍历所有的行
	REPEAT
		-- 6.1 通过游标获得订单编号 存入局部变量o
		FETCH ordernumbers INTO o; 
		--6.2 调用通过订单号计算带税合计的存储过程(方法)
		CALL ordertotal(o,1,t); -- 这里t用来存储计算返回的结果
		--6.3 将结果插入到上面创建的新表里
		INSERT INTO orderstotals(order_num, total) -- 这里是新表的列名
		VALUES(o, t);
	-- 6.4 循环结束
	UNTIL done=1 END REPEAT;

	--7.关闭游标
	CLOSE ordernumbers;
END;

【说明】
1）DECLARE语句的次序：
a.定义局部变量; b.定义游标; c.定义CONTINUE HANDLER等句柄;
声明定义的顺序一定是先a,后b,再c，否则将产生错误信息；
2）声明循环句柄

	-- 这里定义一个CONTINUE HANDLER句柄
	-- FOR 当
	-- SQLSTATE '02000' 出现未找到的代码时
	-- SET done=1; 设置done=1
	-- 合起来就是 定义一个CONTINUE句柄，当出现“未找到”时，将done设置为1
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	...
	REPEAT
		-- 【当遍历完了，这里没有数据可取，这里抛出“未找到” 置done=1】
		FETCH ordernumbers INTO o; 
		CALL ordertotal(o,1,t);
		INSERT INTO orderstotals(order_num, total) VALUES(o, t);
	-- 这里知道done=1停止REPEAT循环
	UNTIL done=1 END REPEAT;

更多关于MySQL的错误代码：
https://dev.mysql.com/doc/refman/8.0/en/error-handling.html
3）另一个使用游标的例子：

	...
    DECLARE  id varchar(64);
    DECLARE  phone1  varchar(16);
    DECLARE  password1  varchar(32);
    DECLARE  name1 varchar(64);
    -- 1.这里定义的游标存储了从表中取出的【多个列】
    DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
    ...
    -- 2.NOT FOUND 可以等效于 SQLSTATE '02000'
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ...
    -- 3.1 这是与上面 REPEAT | UNTIL done END REPEAT对应的另一种循环方式
    -- 循环体名: LOOP 
    --         IF XX THEN 
    --             LEAVE 循环体名;
    --         END IF;
    -- END LOOP;
    read_loop: LOOP -- 定义循环LOOP的名称为read_loop
            -- 4.1 与上面例子中的FETCH cur_account INTO phone1,password1,name1;等效
            -- 4.2 这里把游标中存储的多个列分别存储在多个局部变量中
            FETCH  NEXT from cur_account INTO phone1,password1,name1;
            -- 3.2 这是LOOP循环离开循环的方式，同样是“未找到”时done=1
            IF done THEN
                LEAVE read_loop;
             END IF;
    END LOOP;