当前位置:主页 > IT技术分享 >

Oracle存储过程中多层嵌套游标的用法

作者:AG88发表时间:2018-11-12

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;  

http://www.bkjia.com/oracle/481570.htmlwww.bkjia.comtruehttp://www.bkjia.com/oracle/481570.htmlTechArticleOracle存储过程中多层嵌套游标的用法 Oracle sql脚本代码 CREATE OR REPLACE PROCEDURE P_DELETE_QK (pId in NUMBER, deep in NUMBER) AS -- pId = 分类ID deep = 深度,层...

本文源自: 环亚娱乐

下一篇:没有了 上一篇:rowid和rownum的区别
IT技术分享
联系我们