sql - PL/SQL:ORA-00904::无效的标识符

我正在运行以下SP，但获取错误c1.pyid是无效的标识符。我试图从一个游标中使用两个不同的查询结果。如果在游标中有其他使用if-else子句的方法，我也愿意这样做。

 CREATE OR REPLACE
  PROCEDURE FIX_DOCUMENT_RECORDS ( i_flag in varchar)
AS
Op_ID      VARCHAR(8);
Op_Name    VARCHAR(32);
  skill      VARCHAR(32);
temp_count VARCHAR(8);
temp_status VARCHAR(8):='Submitted';
QRYSTR VARCHAR2(400);
TYPE REF_CUR IS REF CURSOR;
 c1 REF_CUR;

BEGIN
 IF (i_flag='1') THEN
 QRYSTR:='SELECT *
FROM dims_doc_master
WHERE concat_prod_id      IS NULL
OR documenttypeid      IS NULL
AND (pystatuswork       = temp_status);';
ELSE
QRYSTR:='SELECT *
FROM dims_doc_master
WHERE (documentimageid IS NULL
AND p8id               IS NULL)
   AND (pystatuswork       = temp_status);';
END IF;

 open c1 FOR QRYSTR;
LOOP
BEGIN

  DBMS_OUTPUT.PUT_LINE('loop begin');
  UPDATE DIMS_DOC_MASTER
  SET pystatuswork  ='Cancelled',
    documentstatus  ='Cancelled',
    cancellationdate='31-JAN-14',
    cancelledbysid  = c1.pxcreateoperator,
    cancelreason    ='Cancelled due to corruption.'
  WHERE pyid        =c1.pyid;

  DBMS_OUTPUT.PUT_LINE('After updation'||c1.pyid );
  --Begin PC_DOCUMENT UPDATION
  UPDATE PC_DOCUMENT
  SET pystatuswork  ='Cancelled',
    cancellationdate='31-JAN-14'
  WHERE pyid        =c1.pyid;
  --Begin insert into History
  --Select Operator name and ID

  SELECT skill
  INTO skill
  FROM operator_map_skill
  WHERE pyuseridentifier=c1.pxcreateoperator
  AND rownum            =1;
  INSERT
  INTO DIMS_DOC_HIST
    (
      DIMS_DOC_ID,
      DOC_CHG_USR,
      DOC_CHG_DT,
      DOC_NEW_STS,
      DOC_CHG_CMNT,
      CRE_TS,
      ROLE,
      RSN_DESC,
      TARGETROLE,
      DOC_CHG_USR_ID,
      DOC_ASG_USR_ID,
      DOC_ASG_USR,
      PREVSTATUS,
      PREVSTATUSDT,
      ASSIGNEDTODT,
      TODISPLAY,
      ACTIVITY_NAME
    )
    VALUES
    (
      c1.pyid,
      'DIMS',
      systimestamp,
      'Cancelled',
      'Cancelled due to corruption',
      '31-JAN-14',
      skill,
      NULL,
      skill,
      c1.pxcreateoperator,
      c1.pxcreateoperator,
      c1.pxcreateopname,
      'Submitted',
      NULL,
      systimestamp,
      'Y',
      'Updation through Script'
    );

  dbms_output.put_line
  (
    'Document ID= '||c1.pyid
  )
  ;
  SELECT COUNT(*)
  INTO temp_count
  FROM PC_ASSIGN_WORKBASKET
  WHERE pxrefobjectinsname=c1.pyid;
  IF(temp_count          IS NOT NULL) THEN
    DELETE FROM PC_ASSIGN_WORKBASKET WHERE pxrefobjectinsname=c1.pyid;
  ELSE
    DELETE FROM PC_ASSIGN_WORKLIST WHERE pxrefobjectinsname=c1.pyid;
  END IF;
  COMMIT;
END;
END LOOP;
 CLOSE c1;

END;

最佳答案

您似乎混淆了游标和获取的行。
在您当前的过程中：打开一个游标，执行一个循环（由于没有EXIT语句，循环看起来是无止境的），然后在循环之后关闭游标（但看起来它永远不会发生）
要从游标获取结果，请执行以下操作：

CREATE OR REPLACE PROCEDURE ...
  ...
  c1 REF_CUR;
  ddm_record dims_doc_master%rowtype;
BEGIN
  ...
  OPEN c1;
  LOOP
     FETCH c1 INTO ddm_record;
     EXIT WHEN c1%NOTFOUND;
     ...
     DBMS_OUTPUT.PUT_LINE('Document ID= ' || ddm_record.pyid); -- not c1.pyid
  END LOOP;
  CLOSE c1;
END;
/

灵感来源于这里的例子：http://plsql-tutorial.com/plsql-explicit-cursors.htm