ÈçºÎ²âÊÔoracleͨÓô洢¹ý³Ì - Oracle / ¿ª·¢

SQL code:


CREATE OR REPLACE PROCEDURE p_page (
p_sqlcount IN VARCHAR2, -- ¼Ç¼ÊýsqlÓï¾ä
p_sql IN VARCHAR2, -- ½á¹û¼¯sqlÓï¾ä
p_startpage IN INT, --ÆðʼҳºÅ
p_pagenum IN INT, --ÿҳ¼Ç¼Êý
r_count OUT INT, --×ܼǼÊý
r_cursor OUT sys_refcursor -- ½á¹û¼¯
)
IS
v_start INT;
v_end INT;
v_sql VARCHAR2 (2000);
BEGIN
v_start := (p_startpage - 1) * p_pagenum + 1;
v_end := v_start + p_pagenum - 1;

EXECUTE IMMEDIATE p_sqlcount
INTO r_count;

v_sql :=
'SELECT * '
|| ' from (select t1.*, ROWNUM r '
|| ' from ('
|| p_sql
|| ' ) t1 where rownum <='
|| TO_CHAR (v_end)
|| ') t2 where r >='
|| TO_CHAR (v_start);

OPEN r_cursor FOR v_sql;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END p_page;
/

±¾ÎÄÀ´×ÔCSDN²©¿Í£¬×ªÔØÇë±êÃ÷³ö´¦£ºhttp://blog.csdn.net/wzy0623/archive/2007/08/14/1742788.aspx



ÔÚpl/sqlÖвâÊԸô洢¹ý³Ì£º


²ÎÊýÊäÈëÈçÏ£º

p_sqlcount select * from jtglpt_t_dm_b

p_sql select * from jtglpt_t_dm_b