存储过程

create or replace procedure getrenewbooks(rids in varchar2,uid in varchar2,rtnrids out varchar2) as
rtnbookids varchar2(500); --返回更新的图书
tmprid int; --临时变量 图书
tmpchid int; --临时变量 章节
tmporder int;
chapterid int;
corder int;
sqlstr varchar2(500);
type mycursor is ref cursor;
r_words mycursor;
begin
-- sqlstr := 'select r.resource_id, max(nvl(chapter_id, 0)) as chid from mass_resource_tmp r, mass_book_chapter c where r.resource_id in ('||rids|| ') and r.resource_id = c.resource_id(+) group by r.resource_id';
sqlstr := 'select o.resource_id,c.chapter_id as chid,c.chapter_order from (select r.resource_id, max(nvl(c.chapter_order, 0)) as chorder
from mass_resource_tmp r, mass_book_chapter c where r.resource_id in ('||rids|| ') and r.resource_id = c.resource_id(+) and chapter_isdelete=0
group by r.resource_id order by chorder desc) o,mass_book_chapter c where o.resource_id = c.resource_id(+) and o.chorder=c.chapter_order'; -- and chapter_isdelete=0
open r_words for sqlstr;
loop
fetch r_words into tmprid, tmpchid,tmporder;
--判断用户是否阅读到最后章节
select count(*) into chapterid from mass_app_afterbook where resource_id=tmprid and user_id=uid and ROWNUM=1 order by cdate desc;
EXIT WHEN r_words%NOTFOUND;
if chapterid>0 then
select b.cid,b.orderid into chapterid,corder from (select a.chapter_id as cid ,c.chapter_order as orderid from mass_app_afterbook a,mass_book_chapter c where a.resource_id=tmprid and a.user_id=uid
and a.chapter_id = c.chapter_id order by a.cdate desc) b where ROWNUM=1;
--阅读记录章节和这本书最大章节比较 order
if corder1 then
rtnbookids := substr(rtnbookids,0,length(rtnbookids)-1);
end if;
rtnrids:=rtnbookids;
--DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
end;