作者:阿依古丽丹_736 | 来源:互联网 | 2023-09-16 10:29
createorreplaceprocedurePRO_DelArticles(ArticleIdinvarchar2)isArticleNumvarchar2(20);sqlst
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;