MariaDB、MySQL存储过程、游标基础应用举例说明
测试环境：
MariaDB-10.0.19-centos7-x86_64
实践操作：
#创建测试数据库
DROP DATABASE IF EXISTS testdb1;
CREATE DATABASE testdb1;
#创建测试表-余额表
DROP TABLE IF EXISTS account;
CREATE TABLE IF NOT EXISTS account
(user_id BIGINT PRIMARY KEY, #用户id
account BIGINT #账户余额
);
#创建测试表-创建利润分成表
CREATE TABLE IF NOT EXISTS profit_sharing
(order_id BIGINT PRIMARY KEY, #订单ID
user_id BIGINT, #用户ID
total_price DECIMAL(9,4), #交易价格
share_price DECIMAL(9,4), #消费返利
FOREIGN KEY(user_id) REFERENCES account(user_id)
);
#插入测试数据
INSERT INTO account VALUES(1001, 10000);
INSERT INTO account VALUES(1002, 9000);
INSERT INTO profit_sharing VALUES(100001, 1001, 99, 10);
INSERT INTO profit_sharing VALUES(100002, 1002, 90, 5);
#场景1：存储过程中的每个查询语句都只返回一条记录
#创建存储过程
DROP PROCEDURE IF EXISTSproc_varify_profit_sharing; #如果存在proc_varify_profit_sharing则先删除
DELIMITER // #使用DELIMITER命令来把语句定界符从;变为// #注意，定界符可以是其它符号，比如$，但是不能为\，因为它是转义字符
#说明，通常情况下，mysql遇到分号;则判断语句结束，并执行该语句，这里DELIMITER //告诉mysql，遇到//则表示语句结束，执行语句
CREATE PROCEDURE proc_varify_profit_sharing(
user_id BIGINT,
account_init BIGINT,
order_id BIGINT
)
BEGIN
#声明局部变量
DECLARE account_final BIGINT; #用于存储最终余额
DECLARE total_price DECIMAL(9, 4); #用于存储交易总金额
DECLARE share_price DECIMAL(9, 4) DEFAULT 0.00; #用于存储消费返利金额
#说明：BEGIN END;之间定义的变量为局部变量,基础形式：DECLARE变量名数据类型默认值
#定义游标
DECLARE cur_get_account_final CURSOR FOR SELECT t1.account FROM account t1 WHERE t1.user_id=user_id;
DECLARE cur_varify_account CURSOR FOR SELECT t2.share_price, t2.total_price FROM profit_sharing t2
WHERE t2.user_id=user_id AND t2.order_id = order_id;
#基础语法说明:DECLARE cursor_name CURSOR FOR select_statement;
#打开游标
OPEN cur_get_account_final;
OPEN cur_varify_account;
#基础语法说明:OPEN cursor_name;
#获取数据
FETCH cur_get_account_final INTO account_final;
FETCH cur_varify_account INTO share_price, total_price;
#基础语法说明:FETCH cursor_name INTO variable1[, variable2,...]; #读取一行记录(如果有的话)，
#并将记录中各项的值赋值给变量variable1，variable2,...
#注意，变量个数要和游标指定查询生成记录的记录项的数目要相同，通俗的说，查询生成记录，包含多少列，就指定多少个变量，一一对应
#验证金额增减的准确性
SET @tmp = account_final + total_price - share_price; #使用用户变量@tmp,存放中间计算结果
SELECT IF(@tmp=account_init, 'right', 'wrong') AS 'result';
#说明：用户变量(用户变量也归属局部变量)的定义：@变量名，，
#注意：
#1.mysql中用户变量可不用事前声明，在用的时候直接用“@变量名”使用就可以了，
#2.用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
#说明：用SET给变量赋值：SET变量=value;或者SET变量:=value;如上语句所示
小知识：可以用SELECT给变量赋值，类似的有，SELECT变量:=value;或SELECT变量:=colunm_name FROM table_name WHERE…;
注意:使用SELECT时必须用:=
#关闭游标
CLOSE cur_get_account_final;
CLOSE cur_varify_account;
#基础语法说明:CLOSE cursor_name;
END;
//
DELIMITER ; #还原定界符为;
#调用存储过程
CALL proc_varify_profit_sharing(1001, 10089, 100001);
#调用形式说明：CALL procedure_name()(无参数的话)、CALL procedure_name(arg1[, arg2, ...])(带参数)
# "场景1"不用游标的等价实现
#创建存储过程
DROP PROCEDURE IF EXISTS proc_varify_profit_sharing;
DELIMITER //
CREATE PROCEDURE proc_varify_profit_sharing(
userID BIGINT, #注意，当存储过程参数如果用于存储过程中，表查询语句的WHERE子句，作为筛选条件值时，如果未指定要查询表的表别名，则不能和表的列名相同,参见如下
account_init BIGINT,
orderID BIGINT
)
BEGIN
#定义局部变量
DECLARE account_final BIGINT; #用于存储最终余额
DECLARE total_price1 DECIMAL(9, 4); #用于存储交易总金额
DECLARE share_price1 DECIMAL(9, 4); #用于存储消费返利金额
#获取数据
SELECT account INTO account_final FROM account WHERE user_id = userID LIMIT 0,1; # LIMIT 0, 1确保只返回一条记录
#把share_price, total_price分别的查询结果分别赋值给局部变量share_price, total_price
SELECT share_price, total_price INTO share_price1, total_price1 FROM profit_sharing
WHERE user_id=userID AND order_id = orderID LIMIT 0,1;
#注意：
#1、未使用游标的情况下，查询语句使用表别名需要添加 AS，如SELECT t1.account INTO account_final FROM account t1;报错,提示t1为Undeclared variable ，SELECT t1.account INTO account_final FROM account AS t1则不会报错
#2、SELECT column1, ... INTO variable1, ...;如果column1和variable1名称不可以相同，否则取不到值，variable1最终的值为NULL（MariaDB下测试
#验证金额增减的准确性
SET @tmp = account_final + total_price1 - share_price1; #注意：NULL + value1 + ... = NULL
SELECT IF(@tmp=account_init, 'right', 'wrong') AS 'result';
END;
//
DELIMITER ;
CALL proc_varify_profit_sharing(1001, 10089, 100001);
#场景2：返回一个结果集,多条记录(循环读取
#创建存储过程
DROP PROCEDURE IF EXISTS proc_test;
DELIMITER //
CREATE PROCEDURE proc_test()
BEGIN
#声明局部变量
DECLARE order_id BIGINT; #用于存储订单号
DECLARE total_price DECIMAL(9, 4); #用于存储交易总金额
DECLARE share_price DECIMAL(9, 4) DEFAULT 0.00; #用于存储消费返利金额
#DECLARE no_more_record INT DEFAULT 0; #是否还有记录标识
DECLARE no_more_record INT; #是否还有记录标识
#定义游标
DECLARE cur_get_order_info CURSOR FOR SELECT share_price, total_price FROM profit_sharing;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; #当游标或者SQL选择语句没有返回值时，设置no_more_record=1后继续执行
#打开游标
OPEN cur_get_order_info;
#读取数据======行1
FETCH cur_get_order_info INTO total_price, share_price;
SET no_more_record = 0;
WHILE no_more_record <> 1 DO #如果存在下一跳记录，则继续读取
FETCH cur_get_order_info INTO total_price, share_price;
END WHILE;
# ===============行2
#注意：如果把行1到行2之间的语句改成如下
#WHILE no_more_record <> 1 DO #如果存在下一跳记录，则继续读取
# FETCH cur_get_order_info INTO total_price, share_price;
#END WHILE;
#那么，整个while循环体将执行3次(但是表里仅2条记录，为何这样呢？个人想法是这样的：
#第一次循环，no_more_record=0,执行FETCH语句，且游标指针停留在一条记录
#第二次循环，no_more_record=0,执行FETCH语句，且游标指针停留在二条记录
#第三次循环，no_more_record=0,执行FETCH语句，且游标指针移动到下一条，结果无记录，no_more_record=1，结束下次循环
#所以，while循环之前要先执行一次
#关闭游标
CLOSE cur_get_order_info;
END;
//
DELIMITER ;
CALL proc_test();
