Create an Oracle paging storage process

1. Create an Oracle package definition. Use the ref cursor data type to process the Oracle result set. Ref cursor is a pointer to the result set returned by PL/SQL queries. Unlike a normal CURSOR, ref cursor is a variable that references the CURSOR. 1. Create an Oracle package. Use the ref cursor data type to process the Oracle result set. Ref cursor is a pointer to the result set returned by PL/SQL queries. Unlike a normal CURSOR, ref cursor is a variable that references the CURSOR and can be set to point to different result sets during execution. You can use the ref cursor output parameter to pass the result set of the Oracle structured program back to the calling application. You can access the result set pointed to by REF Cursor by defining the output parameters of 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 a package. Implement the specific paging stored procedure in the package body.

 
 

  
  
Create or replace package body MF_PAK_001 is
  
  
Procedure GetDataByPage (
  
  
P_tableName varchar2, -- Name of the table to be queried
  
  
P_fields varchar2, -- the field to be queried
  
  
P_filter varchar2, -- filter Condition
  
  
P_sort varchar2, -- sorting field and direction
  
  
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
  
  

 
 
-- Obtain 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 a query statement in Oracle to execute the paging stored procedure in the package body to check whether the query statement can be correctly executed.

 
 

  
  
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 number of records: '| 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();