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 ;