oracle数据库存储过程的创建,oracle数据库创建存储过程

create or replace procedure proc_note(

res out sys_refcursor,

countPage out number,

currentPage in number,

pageSize in number,

nid in number

)

as

begin

declare

str varchar(500);

str1 varchar(200);

begin

str :='select * from (select rownum r,e.* from (select nts.noteid,nts.notetitle,nts.userid,nts.notedate '

||' ,nts.notectx,nts.searchnum,us.levelid,us.realyname, '

||' us.loginname,us.photo,us.sex,us.userscore,func_level(us.userscore) levelname from notes nts inner join userinfos us '

||' on nts.userid = us.userid where nts.noteid='

||nid||' or nts.subnote='

||nid||') e where rownum<='

||(currentPage*pageSize)||') k where k.r>'

||((currentPage-1)*pageSize);

str1 := 'select ceil(count(noteid)/'||pageSize||') from notes where noteid=:ntd or subnote=:ntd';

open res for str;

execute immediate str1 into countPage using nid,nid;

end;

end;