• PL/SQL编程—分页功能(存储过程)


    SQL> create table t_book(bookid varchar2(3),bookname varchar2(50),publish varchar2(50));
     
    Table created
     
    SQL>
    SQL> create or replace procedure sp_add_book(bookid in varchar2, bookname in varchar2,publish in varchar2) is
      2  begin
      3  insert into t_book values(bookid,bookname,publish);
      4  end;
      5  /
     
    Procedure created
     
    SQL> select * from t_book;
     
    BOOKID BOOKNAME                                           PUBLISH
    ------ -------------------------------------------------- --------------------------------------------------
    1      OracleTestForMine                                  ShenZhenPublish

    SQL> create or replace procedure sp_get_mytest(id_in in varchar2,name_out out varchar2,salary_out out number,bonus_out out number,job_out out varchar2) is
      2  begin
      3  select name,salary,bonus,job into name_out,salary_out,bonus_out,job_out from mytest where id=id_in;
      4  end;
      5  /
     
    Procedure created

    SQL> create or replace package sp_pack_mulityqry as
      2  type p_cursor is ref cursor;
      3  end sp_pack_mulityqry;
      4  /
     
    Package created
     

    --创建一个游标类
    create or replace package sp_pack_pagebycursor as
    type p_cursor is ref cursor;
    end sp_pack_pagebycursor;
    /
     
    --利用存储过程来实现分页功能
    create or replace procedure fenye(
    tablename in varchar2,
    curpage in number,
    pagerownum in number,
    orderflg in varchar2,
    totalrownum out number,
    totalpagenum out number,
    res_cursor out sp_pack_pagebycursor.p_cursor
    ) is
    --定义sql语句变量
    v_sql varchar2(1000);
    --定义查询起始下标
    v_begin_rownum number:=(curpage-1)*pagerownum+1;
    --定义查询结束下表
    v_end_rownum number:=curpage*pagerownum;
    --执行
    begin
    --定义数据库查询sql
    v_sql:='select * from (select t.*,rownum rn from (select * from '||tablename||' order by salary '||orderflg||') t where rownum<='||v_end_rownum||') where rn>='||v_begin_rownum;
    --关联游标和sql
    open res_cursor for v_sql;
    --查询总记录数
    v_sql:='select count(*) from '||tablename;
    execute immediate v_sql into totalrownum;
    --计算总页数
    if mod(totalrownum,pagerownum)=0 then
    totalpagenum:=totalrownum/pagerownum;
    else
    totalpagenum:=totalrownum/pagerownum+1;
    end if;
    end;
    /

     
  • 相关阅读:
    使用pjsip传输已经编码的视频
    xubuntu14.04下编译pjsip及pjsua2 java
    解决 Python.h:没有那个文件或目录 错误的方法
    HIbernate與不支持boolean的數據庫之間的映射
    js数组的操作
    jQuery中ajax的使用与缓存问题的解决方法
    网络游戏中应用可插拔工厂处理消息
    How to Train Triplet Networks with 100K Identities?
    (转)如何用TensorLayer做目标检测的数据增强
    图像超分辨-IDN
  • 原文地址:https://www.cnblogs.com/lingyejun/p/7096698.html
Copyright © 2020-2023  润新知