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;