Instances that use oracle Stored Procedure Paging

Using oracle stored procedures can also achieve paging. Here is an example of using oracle Stored Procedure paging. If you are interested in oracle stored procedures, take a look.

A small example of using oracle Stored Procedure paging:
1. Create a package. As follows:

 
 

  
  
CREATE OR REPLACE PACKAGE TESTPACKAGE AS   
  
  
 
  
  
TYPE Test_CURSOR IS REF CURSOR;   
  
  
 
  
  
end TESTPACKAGE;   
 
 

2. Create a stored procedure:

 
 

  
  
create or replace procedure TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR,lowerNum in numeric,higherNum in numeric) is   
  
  
begin   
  
  
OPEN p_CURSOR FOR select * from ( select row_.*, rownum rownum_ from (select id,title,status from HYQTEST) row_ where rownum <= higherNum) where rownum_ >lowerNum;   
  
  
end TESTC;  
 
 

Test with plsql:

 
 

  
  
Declare
  
  
LowerNum integer;
  
  
HigherNum integer;
  
  
Id varchar2 (10 );
  
  
Title varchar2 (500 );
  
  
Status numeric;
  
  
C testpackage. Test_CURSOR;
  
  
Rownum _ integer;
  
  
Begin
  
  
LowerNum: = 1;
  
  
HigherNum: = 10;
  
  
TESTC (c, lowerNum, higherNum );
  
  
LOOP
  
  
FETCH c INTO id, title, status, rownum _;
  
  
Exit when c % NOTFOUND;
  
  
DBMS_OUTPUT.PUT_LINE ('= row number =' | rownum _ | '=' | id | '=' | title | '=' | status | '= ');
  
  
End loop;
  
  
CLOSE c;
  
  
 
  
  
End;
 
 

Three common mistakes in Oracle Database Backup

Oracle RMAN backup Optimization

Learn about Oracle OS backup

Oracle EXP/IMP backup Overview

How to Implement ORACLE backup