MariaDB MariaDB、MySQL存储过程、游标基础应用举例说明

MariaDBMySQL存储过程、游标基础应用举例说明

by:授客 QQ1033553122

测试环境:

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 EXISTS proc_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();

上一篇:bzoj 4559 [JLoi2016]成绩比较——拉格朗日插值


下一篇:bzoj千题计划281:bzoj4558: [JLoi2016]方