MySQL存储过程

存储过程

近期为了刷数据库数据，由于所需数据涉及多张表，又懒得弄一个新的需求，且要求具有重复使用性，无奈之下只能学习一波被人遗忘的存储过程

• 代码流程
  通过特定条件查出A表中ID，再从B表中过滤关联了A表/不关联A表具体ID的记录；
  获取B表记录ID集合，循环ID集合获取对应关联C表的编码；
  判断D表中是否有 B.ID，C.CODE，特定值VALUE 组合而成的记录，有则跳过，无则进入下一步；
  查不到特定组合记录，说明还没有进行这种类型的组合，删除除该类型以外的其他组合（B.ID，C.CODE，其他值VALUE），然后将该记录插入 D 表（此处业务为一对一，所以要删除）
• 以上流程使用代码实现极其简单
• 存储过程
  格式问题，，，

DROP PROCEDURE IF EXISTS _set_product_category;
CREATE PROCEDURE _set_product_category(IN directory_key varchar(255),IN product_type tinyint(4),IN category_id bigint(20),IN product_df int(2),IN table_name varchar(255))
BEGIN
– 从id集合中拿到的单个记录
DECLARE _productid bigint(64);

– 判断是否已有相同关联的记录
DECLARE p_count int;

– id对应记录的C.CODE
DECLARE _nircode varchar(200);

– 判断id集合是否遍历结束
DECLARE s int DEFAULT 0;

– 获取属于某个目录下的产品ID集合-使用视图动态获取某个产品表
DECLARE _IDLIST CURSOR FOR SELECT id FROM view_myproc;
– 循环标识，集合元素遍历完找不到 s=1 跳出 while
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

SET @sql = concat('create view view_myproc as 
		select tf.id from ',table_name,' tf where df = ',product_df,' and catid = 
		(select id from A where `key` = "',directory_key,'" and enable = 1 )');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;



OPEN _IDLIST;

– 循环赋值
FETCH _IDLIST INTO _productid;

– 循环条件，集合有值就循环
lab:WHILE s <> 1 DO

– 根据id获取对应的编号
set _nircode = (
select sp.code from C sp
where s_id = _productid and status = 1 limit 1
);

– 如果当前id找不到对应的编号，跳过本次循环
IF _nircode IS NULL THEN
– 游标要移动，否则死循环
FETCH _IDLIST INTO _productid;
ITERATE lab;
END IF;

– 默认没有重复记录
set p_count = 0;

select count(id) INTO p_count from D where 
a_id = _productid and _id = category_id 
and `status` = 0 and a_code = _nircode;

– 没有重复记录就新增
IF p_count = 0 THEN

– 一对一关联的，所以在新增前还要删除旧记录
DELETE FROM D WHERE g_type = product_type AND a_id = _productid
AND status = 0 AND a_code = _nircode;

– 插入
INSERT INTO D ()
VALUES( );

END IF;

– 循环结束，进入下一次循环赋值
FETCH _IDLIST INTO _productid;

– 集合中没有值了，结束循环
END WHILE lab;
CLOSE _IDLIST;
END;

• 主要注意几个点
  • 存储过程是无法直接获取 SELECT 语句的集合结果的，需要借助视图来保存结果
  • 表名是无法作为参数直接写入 SQL 中的，要用拼接的方式来动态生成 SQL
  • 循环中当不满足某些条件要跳出本次循环时，游标的位置要后移，否则会死循环
  • 注意循环条件的设置
  • 参数的定义只能放在一开始