存储过程分页

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;