存储过程这个东西,一言难尽, 业内基本淘汰了,基本都快不会写了。今天有个特殊场景, 批量插入数据, 居然还用起来了。
简单描述业务: A表有字段dept_id,tenant_id, 新建B表, 然后根据A表的dept_id,每个dept_id对应在B表里生成50个递增数据,同时B表数据具有A表对应的dept_id,tenant_id
CREATE PROCEDURE init_hand_card_procedure () BEGIN
--
DECLARE
flag INT DEFAULT 0; -- 定义一个 flag 变量,用来判断记录是否全部取出,我这里设置,1代表没有记录,0代表还有记录。
DECLARE
p_dept_id bigint(64) DEFAULT NULL;
DECLARE
p_uid bigint(64) DEFAULT NULL;
DECLARE
p_tenant_id VARCHAR ( 50 ) DEFAULT '';
DECLARE i INT;
-- 定义游标,游标用于存放查询出的临时数据
DECLARE cur CURSOR FOR
SELECT DISTINCT t.id as dept_id, t.tenant_id,u.id as u_id FROM blade_dept t LEFT JOIN blade_user u on t.tenant_id = u.tenant_id
WHERE t.dept_category = 4 and u.account = 'admin';
DECLARE EXIT HANDLER FOR not found SET flag = 1; -- EXIT/CONTINUE 表明了没有记录退出还是继续, 默认是CONTINUE
OPEN cur; -- 打开游标
WHILE flag != 1 DO
FETCH cur INTO p_dept_id,p_tenant_id, p_uid; -- 抓取一条数据
SET i = 1;
WHILE
i < 51 DO
INSERT INTO hd_vip_hand_card ( id,no,online_status,use_status,tenant_id,shop_dept_id,
to_remove_status,status,create_time,create_user,create_dept,update_user,update_time,is_deleted) VALUES
( CONCAT( '130', right(p_dept_id,13), LPAD(i,3,0) ), i ,1,0, p_tenant_id,p_dept_id,0,1,now(), p_uid,p_dept_id, p_uid, NOW(),0);
SET i = i + 1;
END WHILE;
END WHILE;
CLOSE cur;
END;
调用命令就是 call init_hand_card_procedure()
call init_hand_card_procedure()