oracle procedure存储过程(pl/sql)_使用declare cursor_begin end嵌套

最新推荐文章于 2023-03-09 14:22:37 发布

转载最新推荐文章于 2023-03-09 14:22:37 发布 · 215 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xieon1986/p/3364487.html

文章标签：

#数据库

本文介绍了一个用于删除特定文章及其附件的存储过程。包括文章的直接删除、相关明细的清除、以及与文章关联的附件表数据的遍历删除。

create or replace procedure PRO_DelArticles
(
       ArticleId     in varchar2 
) 
is

     
       ArticleNum       varchar2(20);
       sqlstr1          varchar2(2000);
       sqlstr2          varchar2(2000);
       sqlstr3          varchar2(2000);
       sqlstr4          varchar2(2000);
       sqlstr5          varchar2(2000);
       glcount         number;/*获取文章附件关联表数据行数*/
       num             varchar2(20);
/*       TYPE c1 IS REF CURSOR;
       temp_cursor c1;*/
/*       actmfile_rec temp_cursor%ROWTYPE;*/

begin
  
       /*del文章*/
       sqlstr1:='delete from zs_articles where articleid='''||ArticleId||'''';
       EXECUTE IMMEDIATE sqlstr1 into ArticleNum;
       /*del文章明细*/
       sqlstr2:='delete from zs_articlesdetail where articlenum='''||ArticleNum||'''';
       EXECUTE IMMEDIATE sqlstr2;
       /*获取文章附件关联表数据行数*/
       sqlstr4:='select count(*) from zs_articles_and_enclosure where articlenum='''||ArticleNum||'''';
       EXECUTE IMMEDIATE sqlstr4 into glcount;
       
       num:=ArticleNum;
       /*游标遍历删除附件表数据*/
       declare
       cursor file_cursor is 
              select FILENUM from zs_articles_and_enclosure 
              where articlenum=num; 
       file_rec file_cursor%ROWTYPE;
       begin    /*嵌套开始*/
                if not file_cursor%isopen then
                open file_cursor;
                end if;
                while file_cursor%found 
                loop
                      sqlstr5:='delete from zs_attachment where filenum='''||file_rec.filenum||'''';
                      EXECUTE IMMEDIATE sqlstr5;
                 /*fetch file_cursor into file_rec;*/
                end loop;
       end;    /*嵌套结束*/
         
       /*文章附件关联表存在相关数据则删除数据*/
       if(glcount>0) then
         sqlstr3:='delete from zs_articles_and_enclosure where articlenum='''||ArticleNum||'''';
         EXECUTE IMMEDIATE sqlstr3;
       end if;
end PRO_DelArticles;

转载于:https://www.cnblogs.com/xieon1986/p/3364487.html