Oracle 存储过程

CREATE OR REPLACE PROCEDURE PROC_DATAFILTER_NEW IS
  TYPE type_cur IS REF CURSOR;
  cur_temp   type_cur;
  v_count    NUMBER;
  v_len      NUMBER;
  v_upnum    VARCHAR(32);
  v_number   VARCHAR(32);
  v_sql      VARCHAR(512);
  V_ERR_CODE NUMBER;
  V_ERR_MSG  VARCHAR2(1024);
BEGIN
  OPEN cur_temp FOR
    SELECT DISTINCT(RTRIM(a.CALLEDPARTYNUMBER))
  FROM SER_BILLS_TEMP a
 WHERE ( a.CALLEDPARTYNUMBER LIKE '4008%'
  OR a.CALLEDPARTYNUMBER LIKE '95%')
   AND NOT EXISTS (SELECT B.UPNUMBER
          FROM BASETAB_810 B
         WHERE B.UPNUMBER = a.CALLEDPARTYNUMBER);
  LOOP
    FETCH cur_temp
      INTO v_upnum; -- CALLEDPARTYNUMBER 集合
    EXIT WHEN cur_temp%NOTFOUND;
    v_len := length(v_upnum);
    FOR i IN 1 .. v_len
    LOOP
      dbms_output.put_line(i||' : v_len = '||v_len);
      IF v_len - i >= 5
      THEN
        SELECT COUNT(*)
          INTO v_count
          FROM basetab_810 b
         WHERE b.groupuserflag = 1
               AND rtrim(b.upnumber) = SUBSTR(v_upnum, 1, v_len - i);
        IF v_count > 0
        THEN
          v_number := SUBSTR(v_upnum, 1, v_len - i);
          v_sql    := '
            UPDATE ser_bills_temp
                   SET calledpartynumber = :a
            WHERE rtrim(calledpartynumber) = :b
          ';
          COMMIT;
          BEGIN
            EXECUTE IMMEDIATE v_sql
              USING v_number, v_upnum;
            COMMIT;
          EXCEPTION
            WHEN OTHERS THEN
              V_ERR_CODE := SQLCODE;
              V_ERR_MSG  := SUBSTR(SQLERRM, 1, 512);
              INSERT INTO T_ERROR_DEBUG
              VALUES
                (V_ERR_CODE,
                 V_ERR_MSG,
                 SYSDATE,
                 v_sql || '_' || v_number || '_' || v_upnum);
              COMMIT;
          END;
        END IF;
      ELSE
        EXIT;
      END IF;
    END LOOP;
  END LOOP;
  CLOSE cur_temp;
END PROC_DATAFILTER_NEW;