PLS-00402:游标的SELECT列表中需要别名，以避免重复的列名 [英] PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names

问题描述

我编写了一个存储过程以使用join从三个不同的表中提取数据，但是我无法获得结果.我也试图传递动态表，但是发生了错误.

I have written a stored procedure to pull the data from three different table using join but I'm not able get the result.I'm also trying to pass dynamic table but there is error occurred.

CREATE OR REPLACE Procedure DE_DUP_PRO1 (Dy_File_Name in varchar2) 
   --RETURN NUMBER
AS
       v_hol varchar2(300);

  CURSOR De_DUB_CUR IS
  SELECT S.TRANS_GUID AS OLD_TRANS_GUID,
  H.TRANS_GUID    AS NEW_TRANS_GUID,
  CASE
    WHEN H.TRANS_GUID IS NULL
    THEN 0
    ELSE 1
  END as TRN_STAT,
        P.INTR_PHARMACY_ID, S.EXTRNL_PHARMACY_ID,  S.PHARMACY_NM,  S.PHARMACY_ADDR,  S.SUPPLIERS_PSCR_DRUG_CD,  S.PSCR_DRUG_IPU_CD,
        'IPU',  S.PSCR_DRUG_DESC,  S.DSPNSD_DRUG_PACK_SIZE,  S.RX_ID,  S.RX_ITEM_SEQ,  S.RX_REPEAT_STATUS,  S.RX_TYP,  S.EXMT_STATUS,
        S.PSCR_QTY,  S.NRSG_HM_IND,  S.RX_DSPNSD_DT,  S.RX_DSPNSD_TM,  S.SUPPLIERS_DSPNSD_DRUG_CD,  S.DSPNSD_DRUG_IPU_CD, 'IPU',
        S.DSPNSD_DRUG_DESC,  S.GENERIC_USE_MARKER,  S.DSPNSD_UNIT_OF_QTY,  S.DSPNSD_QTY, 'EUR',  S.COST_OF_DSPNSD_QTY,  S.VERBOSE_DOSAGE

  FROM (SELECT stg.*, row_number() over ( partition BY key_clmns_hash ORDER BY 1 ) AS RN FROM  T_MCL_30404_20150317_020 stg ) s


  LEFT JOIN ps_pharmacy p ON s.extrnl_pharmacy_id = p.extrnl_pharmacy_id LEFT JOIN ps_rx_hist H ON h.key_clmns_hash = s.key_clmnS_hash
  AND h.rx_dspnsd_dt = s.rx_dspnsd_dt AND s.supplier_pharmacy_cd = h.SUPPLIER_PHARMACY_CD AND s.detl_clmns_hash <> h.detl_clmns_hash WHERE S.RN = 1;

BEGIN


      FOR De_Dub_rec IN  De_DUB_CUR
      LOOP

        DBMS_OUTPUT.PUT_LINE ( De_Dub_rec.OLD_TRANS_GUID || '|' || De_Dub_rec.NEW_TRANS_GUID || '|' || De_Dub_rec.TRN_STAT || '|' || De_Dub_rec.P.INTR_PHARMACY_ID || '|' || De_Dub_rec.S.EXTRNL_PHARMACY_ID
 || '|' || De_Dub_rec.S.PHARMACY_NM || '|' || De_Dub_rec.S.PHARMACY_ADDR || '|' || De_Dub_rec.S.SUPPLIERS_PSCR_DRUG_CD|| '|' || De_Dub_rec.S.PSCR_DRUG_IPU_CD || '|' || 'IPU' 
 || '|' || De_Dub_rec.S.PSCR_DRUG_DESC || '|' || De_Dub_rec.S.DSPNSD_DRUG_PACK_SIZE || '|' || De_Dub_rec.S.RX_ID || '|' || De_Dub_rec.S.RX_ITEM_SEQ || '|' || De_Dub_rec.S.RX_REPEAT_STATUS 
 || '|' || De_Dub_rec.S.RX_TYP || '|' || De_Dub_rec.S.EXMT_STATUS || '|' || De_Dub_rec.S.PSCR_QTY || '|' || De_Dub_rec.S.NRSG_HM_IND || '|' || De_Dub_rec.S.RX_DSPNSD_DT 
 || '|' || De_Dub_rec.S.RX_DSPNSD_TM || '|' || De_Dub_rec.S.SUPPLIERS_DSPNSD_DRUG_CD || '|' || De_Dub_rec.S.DSPNSD_DRUG_IPU_CD || '|' || 'IPU' || '|' || De_Dub_rec.S.DSPNSD_DRUG_DESC 
 || '|' || De_Dub_rec.S.GENERIC_USE_MARKER || '|' || De_Dub_rec.S.DSPNSD_UNIT_OF_QTY  || '|' || De_Dub_rec.S.DSPNSD_QTY || '|' || 'EUR' || '|' || De_Dub_rec.S.COST_OF_DSPNSD_QTY 
 || '|'|| De_Dub_rec.S.VERBOSE_DOSAGE );

      END LOOP;

--   RETURN 0;

END DE_DUP_PRO1;
/

每当我执行存储过程时，都会遇到错误

whenever I'm execute stored procedure I get below error

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3      PL/SQL: SQL Statement ignored
19/96    PL/SQL: ORA-00942: table or view does not exist
31/9     PL/SQL: Statement ignored
31/32    PLS-00364: loop index variable 'DE_DUB_REC' use is invalid

LINE/COL ERROR(Now it is resolved)
-------- -----------------------------------------------------------------
28/7     PL/SQL: Statement ignored
28/7     PLS-00402: alias required in SELECT list of cursor to avoid
         duplicate column names

推荐答案

您的问题是您的查询正在选择一些文字字符串值而未设置任何别名:

Your problem is that your query is selecting a few literal string values without setting any aliases:

select ..., 'IPU', ... , 'IPU', ..., 'EUR', ...

在上述情况下，Oracle将自动生成看起来像这样的丑陋别名:

In the above case, Oracle will auto-generate ugly aliases that look something like this:

select ..., 'IPU' AS "'IPU'", ..., 'IPU' AS "'IPU'", ..., 'EUR' AS "'EUR'", ...

如您所见，您现在拥有3个非常丑陋的列名，很难使用，其中2个重复，导致出现错误.

So as you can see, you now have 3 very ugly column names that are very awkward to work with, and 2 of them are duplicate, resulting in the error you are getting.

请考虑给他们适当的别名，以避免歧义.这只是一个示例，但是您应该根据值的含义提供一个更有意义的别名:

Consider giving them proper distinct aliases to avoid the ambiguity. This is just an example, but you should give a more meaningful alias according to the meaning of the value:

select ..., 'IPU' AS some_col_1, ..., 'IPU' AS some_col_2, ..., 'EUR' AS some_col_3, ...

---

有趣的是，当您在游标循环中读取查询时，您目前 不是 .当通过游标读取/循环时，您无需尝试从游标中读取3个值，而只是在打印出这些值时再次对其进行硬编码.

---

The funny thing is that you are not currently using those 3 values when reading the query in your cursor for loop. When reading/looping through your cursor, instead of trying to read the 3 values from the cursor, you simply hard-code the values again as you are printing them out.

因此，实际上，如果您真的不关心从游标中读取3个值，只需将它们从查询中完全删除.否则，将DBMS_OUTPUT.PUT_LINE(...)中的硬编码值替换为您设置的别名.

So in fact, if you really don't care about reading the 3 values from the cursor, just remove them from the query altogether. Otherwise, replace your hard-coded values from your DBMS_OUTPUT.PUT_LINE(...) with the aliases that you set.

因此，一旦您的查询固定，就不再是:

So once your query is fixed, instead of:

DBMS_OUTPUT.PUT_LINE(... || 'IPU' || ... || 'IPU' || ... || 'EUR' || ...);

您可能应该像这样使用光标:

You should probably use the cursor like this:

DBMS_OUTPUT.PUT_LINE(... || De_Dub_rec.some_col_1 || ... || De_Dub_rec.some_col_2 || ... || De_Dub_rec.some_col_3 || ...);

这篇关于PLS-00402:游标的SELECT列表中需要别名，以避免重复的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！