To create an Oracle paging stored procedure

, which can be set to point to a different result set at execution time. Using the REF CURSOR output parameter, you can pass the result set of an Oracle formatter back to the calling application. You can access the result set that REF Cursor points to by defining an output parameter for the OracleType.Cursor data type in the calling application.

   
    
   
 
     
    
Create or Replace package mf_pak_001 is
  Type t_cursor is REF CURSOR;
  Procedure Getdatabypage (
     P_tablename VARCHAR2,
     P_fields VARCHAR2,
     P_filter VARCHAR2,
     P_sort VARCHAR2,
     P_curpage number,
     P_pagesize number,
     P_cursor out T_cursor,
     P_totalrecords out number         
);
End mf_pak_001;
 
    
   
2. Create the package body. Implement specific paging stored procedures in the package body.

   
  

    
   
 Create or Replace package body mf_pak_001 is
    
Procedure Getdatabypage (
    
P_tablename varchar2,--The name of the table to query
    
P_fields varchar2,--the field to query
    
P_filter VARCHAR2,--Filtration Conditions
    
P_sort varchar2,--sort fields and orientation
    
P_curpage number,
    
P_pagesize number,
    
P_cursor out T_cursor,
    
P_totalrecords out number
    
)
    
Is
    
V_sql varchar2 (1000): = ';
    
V_startrecord number (4);
    
V_endrecord number (4);
    
Begin
    
 
   
   
  
--Get the total number of records
 
   
  

    
   
 v_sql:= ' Select To_number (COUNT (*)) from ' | | P_tablename;
    
If P_filter is not NULL then
    
v_sql:=v_sql| | ' where 1=1 and ' | | P_filter;
    
End If;
    
Execute immediate v_sql into p_totalrecords;
    

    
v_startrecord:= (p_curpage-1) *p_pagesize;
    
V_endrecord:=p_curpage*p_pagesize;
    

    
v_sql:= ' SELECT ' | | p_fields| | ' from (SELECT ' | | p_fields| | ', RowNum r from ' | |
    
' (SELECT ' | | p_fields| | ' From ' | | P_tablename;
    
If P_filter is not NULL then
    
v_sql:=v_sql| | ' where 1=1 and ' | | P_filter;
    
End If;
    
If P_sort is not NULL then
    
v_sql:=v_sql| | ' ORDER BY ' | | P_sort;
    
End If;
    
v_sql:=v_sql| | ') A where rownum<= ' | | To_char (V_endrecord) | | B where r>= ' | | To_char (V_startrecord);
    
Open p_cursor for V_sql;
    

    
End Getdatabypage;
    
End mf_pak_001; 
   
   
  
3. Write query statements in Oracle and execute the paging stored procedures in the package body to see if they can be executed correctly.

   
    
   
 
     
    
 
     
Declare
V_cur Mf_pak_001.t_cursor;
V_job Jobs%rowtype;
V_totalrecords number;
Begin
  Mf_pak_001.getdatabypage (' Jobs ', ' job_id,job_title,min_salary,max_salary ', ' min_salary>0 ', ' job_id ASC ',
 
     
1,10,v_cur,v_totalrecords);
  Fetch v_cur into v_job;
  While V_cur%found loop
    Dbms_output.put_line (v_job.job_id| | ', ' | | V_job.job_title);
    Fetch v_cur into v_job;
  End Loop;
  Dbms_output.put_line (' Total record number is: ' | | V_totalrecords);
End
 
    
 
    
   
4. Call the paging stored procedure in. Net.

   
  

    
   
String connstring = "Data source=orcl;" User id=hr; password=pwd123456 ";
OracleConnection conn = new OracleConnection (connstring);

OracleCommand cmd = new OracleCommand ();
Cmd. Connection = conn;
Cmd.commandtext = "Mf_pak_001.getdatabypage";
Cmd.commandtype = CommandType.StoredProcedure;

Cmd. Parameters.Add ("P_tablename", Oracletype.varchar). Value = "Jobs";
Cmd. Parameters.Add ("P_fields", Oracletype.varchar). Value = "Job_id,job_title,min_salary,max_salary";
Cmd. Parameters.Add ("P_filter", Oracletype.varchar). Value = "";
Cmd. Parameters.Add ("P_sort", Oracletype.varchar). Value = "job_id ASC";
Cmd. Parameters.Add ("P_curpage", Oracletype.number). Value = 1;
Cmd. Parameters.Add ("P_pagesize", Oracletype.number). Value = 10;
Cmd. Parameters.Add ("P_cursor", OracleType.Cursor). Direction =parameterdirection.output;
Cmd. Parameters.Add ("P_totalrecords", Oracletype.number). Direction = ParameterDirection.Output;

Conn. Open ();
OracleDataReader dr = cmd. ExecuteReader ();

while (Dr. Read ())
{
for (int i = 0; i < Dr. FieldCount; i++)
Response.Write (Dr[i]. ToString () + ";");
Response.Write ("<br/>");
}
Conn. Close ();