Oracle存储过程中多层嵌套游标的用法
Oracle sql脚本代码
CREATE OR REPLACE
PROCEDURE P_DELETE_QK (pId in NUMBER, deep in NUMBER) AS
-- pId = 分类ID deep = 深度，层级
one_val NUMBER;
two_val NUMBER;
three_val NUMBER;
cursor var_one is select "ID" from T_QK where PARENTID = pId;
cursor var_two is select "ID" from T_QK where PARENTID = one_val;
cursor var_three is select "ID" from T_WZ where QKID = two_val;
BEGIN
IF deep = 0 THEN
for oneID in var_one LOOP
one_val:= oneID."ID";
for twoID in var_two LOOP
two_val:= twoID."ID";
--删除与文章关联表
for threeID in var_three LOOP
three_val:= threeID."ID";
delete from Q_BROWSE where WZID = three_val;--浏览量
delete from Q_ATTENTION where WZID = three_val;--关注量
delete from T_COMMENT where WZID = three_val;--评论
END LOOP;
--删除这个分类下的所有文章
delete from T_WZ where QKID = two_val;
--删除易做图分类
delete from T_QK where "ID" = two_val;
END LOOP;
--删除二级分类
delete from T_QK where "ID" = one_val;
END LOOP;
--删除期刊期数
delete from T_QKQS where QKID = pId;
--删除下载量
delete from Q_DOWNLOAD where QKID = pId;
--删除本身
delete from T_QK where "ID" = pId;
END IF;
IF deep = 1 THEN
for twoID in var_one LOOP
two_val:= twoID."ID";
for threeID in var_three LOOP
three_val:= threeID."ID";
delete from Q_BROWSE where WZID = three_val;--浏览量
delete from Q_ATTENTION where WZID = three_val;--关注量
delete from T_COMMENT where WZID = three_val;--评论
END LOOP;
--删除这个分类下的所有文章
delete from T_WZ where QKID = two_val;
--删除易做图分类
delete from T_QK where "ID" = two_val;
END LOOP;
--删除二级分类
delete from T_QK where "ID" = pId;
END IF;
IF deep = 2 THEN
two_val:= pId;
--删除与文章关联表
for threeID in var_three LOOP
three_val:= threeID."ID";
delete from Q_BROWSE where WZID = three_val;--浏览量
delete from Q_ATTENTION where WZID = three_val;--关注量
delete from T_COMMENT where WZID = three_val;--评论
END LOOP;
--删除这个分类下的所有文章
delete from T_WZ where QKID = pId;
--删除易做图分类
delete from T_QK where "ID" = pId;
END IF;
END;
