存储过程分页

PROCEDURE GET_PAYMENT_INVOICE(P_VENDOR_ID NUMBER, --普通输入参数
P_PAYMENT_METHOD VARCHAR2,
......
P_ORDERCOLUMN VARCHAR2,--排序的行
P_ORDERTYPE VARCHAR2,--升序OR降序
P_START_POS NUMBER, --split page start row
P_END_POS NUMBER, --split page end row
P_PAYMENT_CURSOR OUT SYS_REFCURSOR) IS
V_SQL VARCHAR2(4000);
BEGIN
V_SQL := 'select aca.CHECK_DATE as paymentDate,
......
aca.CHECK_ID as checkId
from ap_checks_all aca,
...
where ...
and aca.ORG_ID=hoa.ORG_ID(+)';
IF P_VENDOR_ID IS NOT NULL THEN
--p_org_ID
V_SQL := V_SQL || ' and aca.VENDOR_ID = ''' || P_VENDOR_ID || '''';
END IF;
......
V_SQL := 'select rownum rn, temp.* from(' || V_SQL || ') temp';
--
V_SQL := 'select * from (' || V_SQL || ') t
where t.rn >=' || P_START_POS || '
and t.rn<' || P_END_POS;
IF P_ORDERCOLUMN IS NOT NULL THEN
V_SQL := V_SQL || ' order by ' || P_ORDERCOLUMN;
END IF;
IF P_ORDERTYPE IS NOT NULL THEN
V_SQL := V_SQL || ' ' || P_ORDERTYPE;
END IF;
--raise_application_error(-20700,v_sql);

BEGIN
OPEN P_PAYMENT_CURSOR FOR V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred:get_payment_invoice');
CLOSE P_PAYMENT_CURSOR;
END;
END;