Enter the sqeuence of the order by, which should be "desc" or "ASC"
If you enter two order by, v_order_field= "a[sequence" and ORDER by B "
CREATE OR REPLACE PROCEDURE tablepage_select (v_page_size int,--the size a page of list
v_current_page int,--the current page of list
V_table_name varchar2,--the talbe name
V_order_field VARCHAR2,--the Order field
V_order_sequence VARCHAR2,--The order sequence should by "_desc" or "_ASC" _is blank.
--v_sql_select varchar2,--the Select SQL for procedure
--v_sql_count varchar2,--the count SQL for procedure
--v_out_recordcount out int,--the num ' return rows
P_cursor out Refcursor_pkg.return_cursor) as
V_sql VARCHAR2 (3000); --the SQL for Select all rows of list
V_sql_count VARCHAR2 (3000); --the Count SQL for procedure
V_sql_order VARCHAR2 (2000); --the Order of the list
V_count int; --The Amount rows fo original list
V_endrownum int; --the end Row num by the current page
V_startrownum int; --the Start Row num of the current page
BEGIN
----Set the Order of list
If v_order_field!= ' NO ' then
V_sql_order: = ' ORDER BY ' | | V_order_field | | ' '|| V_order_sequence;
Else
V_sql_order: = ';
End If;
----Catch the amount rows of the list
V_sql_count:= ' SELECT count (rownum) from ' | | V_table_name;
Execute immediate v_sql_count into V_count;
-V_out_recordcount: = V_count;
----Set the value of the start and end row
If v_order_sequence= ' desc ' then
v_endrownum:=v_count-(v_current_page-1) *v_page_size;
V_startrownum:=v_endrownum-v_page_size + 1;
Else
v_endrownum:= v_current_page * v_page_size;
V_startrownum: = v_endrownum-v_page_size + 1;
End If;
----The SQL for page slide
V_sql: = ' select * FROM (SELECT ' | | v_table_name| | '. *, rownum rn from ' | | v_table_name| | ' WHERE rownum <= ' | |
To_char (v_endrownum) | | '|| v_sql_order| | ') WHERE rn >= ' | |
To_char (v_startrownum) | | '|| V_sql_order;
Open p_cursor for V_sql;
End Tablepage_select;