Create or replace procedure tablepage_select (v_page_size int, -- the size of 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 shocould 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 of return rows
P_cursor out refcursor_pkg.return_cursor)
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 list
V_count int; -- The amount rows fo original list
V_endrownum int; -- the end row num of 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 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 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;
Note: The sqeuence input for order by is "DESC" or "ASC"
If two order by statements are input, v_order_field = "A [sequence], order by B"