mysql存储过程样例_MySQL存储过程样例

下面是本人写的第一个有点复杂的MySQL存储过程，特此记录：

-- 删除存储过程

DROP PROCEDURE IF EXISTS proc_update_term_group;

-- 创建存储过程

DELIMITER //

CREATE PROCEDURE proc_update_term_group(IN tg_id VARCHAR(40),IN ag_id VARCHAR(20),IN tg_name VARCHAR(40))

BEGIN

-- 当前时间

DECLARE cur_time DATETIME DEFAULT NOW();

-- 终端编号

DECLARE t_id VARCHAR(20);

-- 账户编号

DECLARE a_id VARCHAR(20);

-- 账户密码

DECLARE a_psw VARCHAR(40);

-- 账户有效期

DECLARE a_active_date DATETIME;

-- 带宽编号

DECLARE a_bandwidth_id VARCHAR(10);

-- 计费规则名称

DECLARE a_bill_rule_name VARCHAR(20);

-- 账户余额

DECLARE a_balance NUMERIC(7, 2);

-- 循环结束标志位

DECLARE done INT DEFAULT 0;

-- 定义游标

DECLARE rs_cursor1 CURSOR FOR SELECT t.TERM_ID, a.ID, a.PSW, a.ACTIVE_DATE, a.BANDWIDTH_ID, a.BILL_RULE_NAME, a.BALANCE FROM TERM_ACCOUNT_INFO t, ACCOUNT_INFO a WHERE t.ACCOUNT_ID = a.ID AND t.TERM_GROUP_ID = tg_id;

DECLARE rs_cursor2 CURSOR FOR SELECT ID FROM TERM_BASIC_INFO WHERE TERM_GROUP_ID = tg_id;

DECLARE rs_cursor3 CURSOR FOR SELECT ID, PSW, ACTIVE_DATE, BANDWIDTH_ID, BILL_RULE_NAME, BALANCE FROM ACCOUNT_INFO WHERE ACCOUNT_GROUP_ID = ag_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

START TRANSACTION;

-- 删除终端账户信息

DELETE FROM TERM_ACCOUNT_INFO WHERE TERM_GROUP_ID = tg_id;

-- 删除终端账户下发任务信息

DELETE FROM TERM_DOWN_ACCOUNT WHERE EXISTS(SELECT 1 FROM TERM_BASIC_INFO t WHERE t.ID = TERM_ID AND t.TERM_GROUP_ID = tg_id) AND OPERATE_TYPE = 1;

COMMIT;

START TRANSACTION;

-- 打开游标

OPEN rs_cursor1;

-- 循环

WHILE done=0 DO

-- 游标赋值

FETCH rs_cursor1 INTO t_id, a_id, a_psw, a_active_date, a_bandwidth_id, a_bill_rule_name, a_balance;

IF NOT done THEN

-- 插入数据

INSERT INTO TERM_DOWN_ACCOUNT VALUES(UUID(), t_id, a_id, a_psw, a_active_date, a_bandwidth_id, a_bill_rule_name, a_balance, 0);

END IF;

END WHILE;

SET done=0;

CLOSE rs_cursor1;

COMMIT;

START TRANSACTION;

-- 循环结束标志位2

-- 打开游标

OPEN rs_cursor2;

-- 循环

WHILE done=0 DO

-- 游标赋值

FETCH rs_cursor2 INTO t_id;

IF NOT done THEN

-- 打开游标

OPEN rs_cursor3;

-- 循环

WHILE done=0 DO

-- 游标赋值

FETCH rs_cursor3 INTO a_id, a_psw, a_active_date, a_bandwidth_id, a_bill_rule_name, a_balance;

IF NOT done THEN

-- 插入数据

INSERT INTO TERM_ACCOUNT_INFO VALUES(UUID(), t_id, tg_id, a_id, ag_id, cur_time);

INSERT INTO TERM_DOWN_ACCOUNT VALUES(UUID(), t_id, a_id, a_psw, a_active_date, a_bandwidth_id, a_bill_rule_name, a_balance, 0);

END IF;

END WHILE;

CLOSE rs_cursor3;

set done=0;

END IF;

END WHILE;

CLOSE rs_cursor2;

COMMIT;

START TRANSACTION;

-- 修改终端分组信息

UPDATE TERM_GROUP SET NAME = tg_name WHERE ID = tg_id;

COMMIT;

END

//

DELIMITER ;