创建存储过程代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】创建存储过程

作者:首君 / 发布于2016/5/10/ 1274

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;

试试其它关键字

　存储过程　

同语言下

可能有用的

首君贡献的其它代码(39)