循环中的Oracle查找函数返回rowtype如何从rowtype获取字段

Question

我有一个运行select的过程(我测试过，返回56条记录)，然后当我运行游标时，我希望将3个字段传递给一个函数(参见上文)，该函数将从包含1500万条记录(价值10年)的表中查找/选择一条记录。

它返回一个rowtype，然后我想要从这个rowtype记录中提取字段，以便使用第一个select中的记录和从lookup函数获取的其他字段来运行插入。

如果我运行这个过程，控制台会打印出我的测试消息，但当我尝试运行

select *从v_processed_claim批量收集到v_tab_proc_claim_recs；

由于错误(97，65)而无法编译: PL/SQL: ORA-00942:表或视图不存在

就好像这两个不是表一样。

我做得对吗..。我该怎么做，为什么它看不到我试图提取到的表？

我是不是应该换一种方式..

感谢您的帮助/建议:)

该函数如下...

create or replace function get_processed_claim_rec(

    p_provider VARCHAR2,
    p_rx VARCHAR2,
    p_record_no  NUMBER 
)


 return i_idb.processed_claim%rowtype
  as
    l_claim_record i_idb.processed_claim%rowtype;
  begin

    select * into l_claim_record from i_idb.processed_claim
      where source_date = p_provider
      AND rx = p_rx
      AND rec_no = p_record_no;


   return(l_claim_record);
  end;

程序是..。

create or replace PROCEDURE import_mailer_data

  AS
    -------------------------------

    /**
        for the lookup table
    **/
    v_processed_claim  i_idb.processed_claim%rowtype;
    TYPE proc_claim_recs IS TABLE OF v_processed_claim%ROWTYPE INDEX BY PLS_INTEGER;
    v_tab_proc_claim_recs proc_claim_recs;
    --------------------------------

    CURSOR myCursor
    IS
    SELECT * 
      from
      (   
      SELECT
        j.create_date as open_date,
        case when (j.create_date < (sysdate - 20) )
            then 'POD'
            else 'REG'
        end as priority,
        c.division,
        c.unit,
        --p.refill as days_supply,
        --p.din_name,
       'CM_JOHN' as log_code,
        c.first_name,
        c.last_name,
        --p.UNLISTED_compound,
        --p.intervention_code,
        --p.substitution,
        --p.confirm,
        c.PROVIDER,
        c.rx,
        c.DISPENSE_DATE,
        c.DIN,
        c.QTY,
        c.DIN_COST_PAID,
        c.DISP_FEE_PAID,
        c.PAID_AMOUNT,
        c.SOURCE_DATE,
        c.RECORD_NO,
        c.RELATIONSHIP,
        c.INSURER_NO,
        c.GROUP_NO,
        c.CERTIFICATE,
        c.BIRTH_DATE,
        c.USER_ID,
        --p.rej_code --v_seq_no
          rank() over
          (
            partition by c.provider, c.rx, c.record_no Order by c.provider desc, c.rx desc

          ) as RNK         
      FROM AUDITCOLLECTIONS.MAILER_CLAIMS c,
        AUDITCOLLECTIONS.MAILER_JOBS j
      WHERE MAILER_JOB_DETAIL_ID IN
        (SELECT MAILER_JOB_DETAIL_ID
        FROM AUDITCOLLECTIONS.MAILER_JOB_DETAILS
        WHERE MAILER_JOB_ID IN
          ( SELECT MAILER_JOB_ID FROM AUDITCOLLECTIONS.MAILER_JOBS
          )
        )

        AND ( c.PROVIDER, c.rx, c.record_no ) NOT IN
          ( SELECT provider, rx, rec_no FROM  AUDITCOLLECTIONS.COLLECTION_AUDIT_STAGING
          )
        AND j.create_date > (sysdate - 30)
        AND c.provider = '2010500042'
      ) A_Latest
      where A_Latest.RNK = 1;

    BEGIN
    v_report_id := audit_load.create_loaded_report(v_report_type_id);

      FOR curRec IN myCursor
      LOOP
        BEGIN
          dbms_output.put_line ('===>>>> PRINTING TEST1 = ');        

        v_processed_claim := get_processed_claim_rec(curRec.PROVIDER, curRec.RX, curRec.RECORD_NO);


        select * bulk collect into v_tab_proc_claim_recs from v_processed_claim; 



      END LOOP;

      audit_load.update_status_to_loaded(v_report_id);
    END import_mailer_data;  

Answer 1

您可以这样做：

 FOR curRec IN myCursor
  LOOP

      v_processed_claim := 
         get_processed_claim_rec(curRec.PROVIDER, curRec.RX, curRec.RECORD_NO);

      v_tab_proc_claim_recs (v_tab_proc_claim_recs.COUNT+1) := v_processed_claim;

  END LOOP;

或者简化为：

 FOR curRec IN myCursor
  LOOP

      v_tab_proc_claim_recs (v_tab_proc_claim_recs.COUNT+1) :=
        get_processed_claim_rec(curRec.PROVIDER, curRec.RX, curRec.RECORD_NO);

  END LOOP;