当前位置:数据库 > Oracle >>

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

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;  
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,