Oracle - PLS-00222

Question

我有下面这段返回PLS-00222的代码。因此，我想将“旧”id的日期与从cur_c1检索到的“新”id的日期进行比较。

以下是cur_table记录所在的光标：

    CURSOR table_cur IS
        SELECT
            NEW_ID,
            OLD_ID    
        FROM
            TABLE_C
        WHERE
            C_ID = in_parameter_id; --This is input for the procedure

    CURSOR cur_c1 (c_in_id NUMBER) IS
        SELECT 
               FIELD_DATE
          FROM
               TABLE_D
          WHERE
               FIELD_ID = c_in_id;

  FOR cur_table IN table_cur LOOP
   ...stuff...;

    FOR c_cur IN cur_c1(cur_table.NEW_ID) LOOP
        IF c_cur.field_date > cur_c1(cur_table.OLD_ID).field_date
        THEN
            v_exist := 'Y';
        END IF;
    END LOOP;
   END LOOP;

怎样才能达到我想要的效果？

错误：

2593/56 PLS-00222: no function with name 'cur_c1' exists in this scope
2593/17 PL/SQL: Statement ignored

Answer 1

我建议在这里使用游标是低效的。相反，我的建议如下：

FOR cur_table IN table_cur LOOP
  ...stuff...;


  SELECT d_old.FIELD_DATE,
         d_new.FIELD_DATE
    INTO dtOld_field_date,
         dtNew_field_date
    FROM DUAL
    LEFT OUTER JOIN TABLE_D d_old
      ON d_old.FIELD_ID = cur_table.OLD_ID
    LEFT OUTER JOIN TABLE_D d_new
      ON d_new.FIELD_ID = cur_table.NEW_ID;

  IF dtNew_field_date > dtOld_field_date THEN
    v_exist := 'Y';
  END IF;
END LOOP;

祝你好运。