Oracle 存储过程优化 的 问题 很有难度的呵呵

Oracle 转为DB2 SQL语句的 问题CREATE OR REPLACE PACKAGE BODY Pack_9001 AS
   PROCEDURE proc_9001(account_num_in  IN VARCHAR2,
area_code_in    IN VARCHAR2,
begindate       IN VARCHAR2,
enddate         IN VARCHAR2,
max_amount      IN VARCHAR2,
min_amount      IN VARCHAR2,
start_id        IN VARCHAR2,    --查询开始行代码
end_id          IN VARCHAR2,    --查询结束行代码
out_val         OUT VARCHAR2,
m_list          OUT mingxi_list
-- out_mess        out varchar2,
-- out_rownum      out varchar2  --时间段交易总条数 )
IS
BEGIN
    out_val:='00';
--out_mess:='no';
--out_rownum:='0';
     BEGIN
      IF TO_NUMBER(NVL(min_amount,'0'))=0
      AND TO_NUMBER(NVL(max_amount,'0'))=0
      THEN
      /**SELECT COUNT(*) INTO out_rownum FROM CORPORACCT_JOURNAL
      WHERE  area_code=area_code_in  AND (account_num=account_num_in or account_num=SUBSTR(account_num_in,1,17))
      AND trans_date>=begindate AND trans_date<=enddate;**/      OPEN m_list FOR SELECT  area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT ROWNUM rown,area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT * FROM CORPORACCT_JOURNAL
        WHERE
          area_code=area_code_in  AND
          (account_num=account_num_in OR account_num=SUBSTR(account_num_in,1,17))
          AND  trans_date>=begindate AND trans_date<=enddate
        ORDER BY trans_date,trade_time DESC))
        WHERE  rown BETWEEN start_id AND end_id;
-- out_mess:='all null' || account_num_in || begindate || enddate || area_code_in;      ELSIF TO_NUMBER(NVL(min_amount,'0'))=0 THEN      /**SELECT COUNT(*) INTO out_rownum FROM CORPORACCT_JOURNAL
      WHERE  area_code=area_code_in  AND (account_num=account_num_in or account_num=SUBSTR(account_num_in,1,17))
      AND trans_date>=begindate AND trans_date<=enddate
      AND
 ((ABS(TO_NUMBER(debit_amount))<>0 AND ABS(TO_NUMBER(debit_amount))<=TO_NUMBER(max_amount))
  OR
 (ABS(TO_NUMBER(credit_amount))<>0 AND ABS(TO_NUMBER(credit_amount))<=TO_NUMBER(max_amount)));**/      OPEN m_list FOR SELECT  area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT ROWNUM rown,area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT * FROM CORPORACCT_JOURNAL WHERE
 area_code=area_code_in  AND (account_num=account_num_in OR account_num=SUBSTR(account_num_in,1,17))
AND trans_date>=begindate AND trans_date<=enddate
AND
 ((ABS(TO_NUMBER(debit_amount))<>0 AND ABS(TO_NUMBER(debit_amount))<=TO_NUMBER(max_amount))
  OR
 (ABS(TO_NUMBER(credit_amount))<>0 AND ABS(TO_NUMBER(credit_amount))<=TO_NUMBER(max_amount)))
        ORDER BY trans_date,trade_time DESC))
      WHERE  rown BETWEEN start_id AND end_id; -- out_mess:='min null';
      ELSIF TO_NUMBER(NVL(max_amount,'0'))=0 THEN      /**SELECT COUNT(*) INTO out_rownum FROM CORPORACCT_JOURNAL
      WHERE  area_code=area_code_in  AND (account_num=account_num_in or account_num=SUBSTR(account_num_in,1,17))
      AND trans_date>=begindate AND trans_date<=enddate
      AND
     ((ABS(TO_NUMBER(debit_amount))<>0 AND ABS(TO_NUMBER(debit_amount))>=TO_NUMBER(min_amount))
OR
     (ABS(TO_NUMBER(credit_amount))<>0 AND ABS(TO_NUMBER(credit_amount))>=TO_NUMBER(min_amount)));**/      OPEN m_list FOR SELECT  area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT ROWNUM rown,area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT * FROM CORPORACCT_JOURNAL WHERE
     area_code=area_code_in  AND (account_num=account_num_in OR account_num=SUBSTR(account_num_in,1,17))
     AND trans_date>=begindate AND trans_date<=enddate AND
     ((ABS(TO_NUMBER(debit_amount))<>0 AND ABS(TO_NUMBER(debit_amount))>=TO_NUMBER(min_amount))
OR
     (ABS(TO_NUMBER(credit_amount))<>0 AND ABS(TO_NUMBER(credit_amount))>=TO_NUMBER(min_amount)))
        ORDER BY trans_date,trade_time DESC))
      WHERE  rown BETWEEN start_id AND end_id;
-- out_mess:='max null';
      ELSE      /**SELECT COUNT(*) INTO out_rownum FROM CORPORACCT_JOURNAL WHERE
 area_code=area_code_in  AND (account_num=account_num_in or account_num=SUBSTR(account_num_in,1,17))
AND trans_date>=begindate AND trans_date<=enddate AND
 ((ABS(TO_NUMBER(debit_amount))>=TO_NUMBER(min_amount) AND
   ABS(TO_NUMBER(debit_amount))<=TO_NUMBER(max_amount)
AND ABS(TO_NUMBER(debit_amount))<>0) OR
   (ABS(TO_NUMBER(credit_amount))>=TO_NUMBER(min_amount) AND
   ABS(TO_NUMBER(credit_amount))<=TO_NUMBER(max_amount) AND
   ABS(TO_NUMBER(credit_amount))<>0));**/         OPEN m_list FOR SELECT  area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT ROWNUM rown,area_code,account_num,
       trans_date,check_num,trans_abstr,debit_amount,credit_amount,
        balance,account_num1,bank_name,bank1_name,acc1_name,trans_type,
        yt,trade_time,SIGN,timestmp,notes,TRXSERIAL,TELLERNO,TPHYBRNO
        FROM
        (SELECT * FROM CORPORACCT_JOURNAL WHERE
 area_code=area_code_in  AND (account_num=account_num_in OR account_num=SUBSTR(account_num_in,1,17))
AND trans_date>=begindate AND trans_date<=enddate AND
 ((ABS(TO_NUMBER(debit_amount))>=TO_NUMBER(min_amount) AND
   ABS(TO_NUMBER(debit_amount))<=TO_NUMBER(max_amount)
AND ABS(TO_NUMBER(debit_amount))<>0) OR
   (ABS(TO_NUMBER(credit_amount))>=TO_NUMBER(min_amount) AND
   ABS(TO_NUMBER(credit_amount))<=TO_NUMBER(max_amount) AND
   ABS(TO_NUMBER(credit_amount))<>0))
                ORDER BY trans_date,trade_time DESC))
      WHERE  rown BETWEEN start_id AND end_id;
-- out_mess:='all exist' || TO_CHAR(TO_NUMBER(NVL(max_amount,'0'))) || min_amount;      END IF;
      EXCEPTION
WHEN OTHERS THEN
out_val:=TO_CHAR(SQLCODE);
--out_mess:=TO_CHAR(SQLCODE);
END;
   END;