存储过程使用
存储过程
近期为了刷数据库数据,由于所需数据涉及多张表,又懒得弄一个新的需求,且要求具有重复使用性,无奈之下只能学习一波被人遗忘的存储过程
- 代码流程
通过特定条件查出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
- 循环中当不满足某些条件要跳出本次循环时,游标的位置要后移,否则会死循环
- 注意循环条件的设置
- 参数的定义只能放在一开始