PL/SQL Programming-paging functionality (stored procedures)

Sql> Create TableT_book (BookIDvarchar2(3), BookNamevarchar2( -), publishvarchar2( -)); Tablecreated SQL>SQL> Create or Replace procedureSp_add_book (BookIDinch varchar2, BookNameinch varchar2, publishinch varchar2) is  2  begin  3  Insert  intoT_bookValues(Bookid,bookname,publish); 4  End; 5  / Procedurecreated SQL> Select *  fromT_book; BOOKID bookname PUBLISH------ -------------------------------------------------- --------------------------------------------------1Oracletestformine Shenzhenpublish

Sql> Create or Replace procedureSp_get_mytest (id_ininch varchar2, name_out outvarchar2, salary_out out Number, bonus_out out Number, job_out outvarchar2) is  2  begin  3  SelectName,salary,bonus,job intoName_out,salary_out,bonus_out,job_out fromMyTestwhereId=id_in; 4  End; 5  / ProcedureCreated

SQL>Createorreplaceas  2is  cursor ;   3  End sp_pack_mulityqry;   4  /

--Create a cursor classCreate or ReplacePackage Sp_pack_pagebycursor astype P_cursor isRefcursor;EndSp_pack_pagebycursor;/ --using stored procedures for paging functionalityCreate or Replace procedureFenye (tablenameinch varchar2, Curpageinch  Number, Pagerownuminch  Number, ORDERFLGinch varchar2, Totalrownum out Number, Totalpagenum out Number, res_cursor out Sp_pack_pagebycursor.p_cursor) is--defining SQL statement variablesV_sqlvarchar2( +);--define the query start subscriptV_begin_rownum Number:=(Curpage-1)*Pagerownum+1;--The following table defines the end of the queryV_end_rownum Number:=Curpage*Pagerownum;--Executionbegin--define database query SQLV_sql:='SELECT * FROM (select T.*,rownum rn from'||TableName||'ORDER BY Salary'||Orderflg||') T where rownum<='||V_end_rownum||') where rn>='||V_begin_rownum;--associative cursors and SQLOpenRes_cursor forV_sql;--total number of records queriedV_sql:='Select COUNT (*) from'||TableName;ExecuteImmediate V_sql intoTotalrownum;--Calculate Total PagesifMoD (Totalrownum,pagerownum)=0  ThenTotalpagenum:=Totalrownum/Pagerownum;ElseTotalpagenum:=Totalrownum/Pagerownum+1;End if;End;/

PL/SQL Programming-paging functionality (stored procedures)