Oracle 如何将记录类型数据分配给ref游标

嗨，汤姆，

这是OBIEE cloud中的一项要求，我们使用匿名块来获得预期的结果。

我们有一个匿名块来捕获查询到记录类型的选择性结果 (选择性行和列)。
现在，要将此记录类型传递到块之外，我们唯一的选择就是使用ref游标。


您能否帮助我，如何将数据从记录类型分配给ref光标。


下面是使用的代码:

DECLARE
TYPE ParticipantRatePayout is record 
(
 p_name  CN_SRP_PARTICIPANT_HDR_RO_V.PARTICIPANT_NAME%TYPE,
 cp_name  CN_COMP_PLANS_ALL_VL.DISPLAY_NAME%TYPE,
 pc_name  CN_PLAN_COMPONENTS_ALL_VL.DISPLAY_NAME%TYPE,
 fo_name  CN_FORMULAS_ALL_VL.FORMULA_NAME%TYPE,
 min_amt  CN_RATE_DIM_TIERS_ALL.MINIMUM_AMOUNT%TYPE,
 iter  CN_RATE_DIMENSIONS_ALL_VL.NUMBER_TIER%TYPE,
 commission  CN_RATE_TABLE_VALUES_ALL.COMMISSION_VALUE%TYPE
);
TYPE par_rate_pay IS TABLE OF ParticipantRatePayout;
rate_pay par_rate_pay;
prp ParticipantRatePayout;
 TYPE irefcursor IS REF CURSOR;
 ixdo_cursor irefcursor;
 TYPE orefcursor IS REF CURSOR return ParticipantRatePayout;
 oxdo_cursor orefcursor; 
  p_name  CN_SRP_PARTICIPANT_HDR_RO_V.PARTICIPANT_NAME%TYPE := NULL;
  cp_name  CN_COMP_PLANS_ALL_VL.DISPLAY_NAME%TYPE   := NULL;
  pc_name  CN_PLAN_COMPONENTS_ALL_VL.DISPLAY_NAME%TYPE  := NULL;
  fo_name  CN_FORMULAS_ALL_VL.FORMULA_NAME%TYPE   := NULL;
  iter  CN_RATE_DIMENSIONS_ALL_VL.NUMBER_TIER%TYPE  := 0;
  commission CN_RATE_TABLE_VALUES_ALL.COMMISSION_VALUE%TYPE  :=  0;
  min_amt  CN_RATE_DIM_TIERS_ALL.MINIMUM_AMOUNT%TYPE  :=  0;
  cnt  NUMBER       :=  0;
 CURSOR RATEPAY IS
SELECT /*+ PARALLEL(5) */
 P.PARTICIPANT_NAME,PR.PERIOD_NAME,CP.DISPLAY_NAME AS COMP_PLAN,PC.DISPLAY_NAME AS PLAN_COMP,
 FV.FORMULA_NAME,RTV.COMMISSION_VALUE,RDT.MINIMUM_AMOUNT,RDT.MAXIMUM_AMOUNT,RDT.TIER_SEQUENCE,RD.NUMBER_TIER
FROM
 CN_SRP_FORM_RATE_TABLES_ALL FR,
 CN_SRP_COMP_PLANS_ALL SCP,
 CN_COMP_PLANS_ALL_VL CP,
 CN_SRP_PLAN_COMPONENTS_ALL SPC,
 CN_PLAN_COMPONENTS_ALL_B PCB,
 CN_PLAN_COMPONENTS_ALL_VL PC,
 CN_COMP_PLAN_COMPONENTS_ALL CPC,
 CN_SRP_FORM_METRICS_ALL SFM,
 CN_FORMULA_RATE_TABLES_ALL FRT,
 CN_RATE_TABLES_ALL_VL RT,
 CN_RATE_TABLE_DIMS_ALL RTD,
 CN_RATE_DIMENSIONS_ALL_VL RD,
 CN_RATE_DIM_TIERS_ALL RDT,
 CN_RATE_TABLE_VALUES_ALL RTV,
 CN_FORMULAS_ALL_VL FV,
 CN_PERIODS_VL PR,
 CN_SRP_PARTICIPANT_DETAILS_ALL PD,
 CN_SRP_PARTICIPANT_HDR_RO_V P,
 CN_REPOSITORIES_ALL_VL R
WHERE 
 P.PARTICIPANT_ID  = FR.PARTICIPANT_ID
 AND (P.PARTICIPANT_NAME IN (:Participant) OR LEAST(:Participant) IS NULL)
 AND P.PARTICIPANT_ID  = PD.PARTICIPANT_ID
 AND CP.DISPLAY_NAME  = 'NA_SPECIALTY_IC11'
 AND PC.DISPLAY_NAME  = 'Order 1'
 AND FV.FORMULA_NAME  = 'ORD 1 Annual PM'
 AND SCP.PARTICIPANT_ID   = FR.PARTICIPANT_ID
 AND SCP.SRP_COMP_PLAN_ID  = FR.SRP_COMP_PLAN_ID 
 AND SPC.SRP_COMP_PLAN_ID = FR.SRP_COMP_PLAN_ID 
 AND CP.COMP_PLAN_ID  = SCP.COMP_PLAN_ID
 AND SPC.COMP_PLAN_ID  = SCP.COMP_PLAN_ID
 AND SPC.COMP_PLAN_COMPONENT_ID = CPC.COMP_PLAN_COMPONENT_ID
 AND SPC.SRP_PLAN_COMPONENT_ID  = FR.SRP_PLAN_COMPONENT_ID 
 AND SPC.PARTICIPANT_ID   = FR.PARTICIPANT_ID
 AND SPC.SRP_COMP_PLAN_ID  = SCP.SRP_COMP_PLAN_ID 
 AND PC.PLAN_COMPONENT_ID  = SPC.PLAN_COMPONENT_ID
 AND PCB.PLAN_COMPONENT_ID  = SPC.PLAN_COMPONENT_ID
 AND PC.PLAN_COMPONENT_ID  = CPC.PLAN_COMPONENT_ID
        AND CP.COMP_PLAN_ID   = CPC.COMP_PLAN_ID
 AND SPC.ORG_ID   = CPC.ORG_ID
 AND PCB.ORG_ID   = CPC.ORG_ID
 AND SFM.PARTICIPANT_ID   = FR.PARTICIPANT_ID
 AND SFM.SRP_COMP_PLAN_ID  = FR.SRP_COMP_PLAN_ID 
 AND SFM.SRP_PLAN_COMPONENT_ID  = FR.SRP_PLAN_COMPONENT_ID
 AND SFM.SRP_FORM_METRIC_ID  = FR.SRP_FORM_METRIC_ID
 AND FR.RATE_TABLE_ID  = FRT.RATE_TABLE_ID
 AND FR.FORMULA_ID  = FRT.FORMULA_ID 
 AND FRT.FORMULA_ID  = SFM.FORMULA_ID 
 AND FRT.RATE_TABLE_ID  = RT.RATE_TABLE_ID
 AND FRT.ORG_ID   = CPC.ORG_ID
 AND RT.ORG_ID    = FRT.ORG_ID
 AND RT.RATE_TABLE_ID  = RTD.RATE_TABLE_ID
 AND RT.ORG_ID    = RTD.ORG_ID
 AND RTD.RATE_DIMENSION_ID  = RD.RATE_DIMENSION_ID
 AND RD.RATE_DIMENSION_ID = RDT.RATE_DIMENSION_ID
 AND RTD.ORG_ID   = RDT.ORG_ID  
 AND RD.DIM_UNIT_CODE  = 'AMOUNT'
 AND RTV.RATE_TABLE_ID  = RTD.RATE_TABLE_ID
 AND RTV.ORG_ID   = RDT.ORG_ID
 AND RTV.SRP_FORM_RATE_TABLE_ID = FR.SRP_FORM_RATE_TABLE_ID
 AND RTV.RATE_SEQUENCE  = RDT.TIER_SEQUENCE
 AND FR.FORMULA_RATE_TABLE_ID    = FRT.FORMULA_RATE_TABLE_ID
 AND FV.FORMULA_ID  = FRT.FORMULA_ID
 AND FV.ORG_ID    = RDT.ORG_ID
 --AND PR.PERIOD_YEAR  = '2019'
 AND (PR.PERIOD_YEAR in (:Year) OR LEAST(:Year) IS NULL)
 --AND PR.PERIOD_ID  = '2019001'
 AND (PR.PERIOD_NAME IN (:PERIOD) OR LEAST(:PERIOD) IS NULL)
 AND P.ORG_ID    = PD.ORG_ID
 AND PD.ORG_ID   = RDT.ORG_ID 
 AND P.ORG_ID = R.ORG_ID
 AND R.ORG_NAME IN (:BU)
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= TO_DATE(TO_CHAR (PD.START_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY'))
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') <= TO_DATE(TO_CHAR (PD.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') OR PD.END_DATE IS NULL)
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') <= TO_DATE(TO_CHAR (SCP.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') OR SCP.END_DATE IS NULL) 
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= TO_DATE(TO_CHAR (SCP.START_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY')) 
ORDER BY P.PARTICIPANT_NAME,PR.PERIOD_NAME,CP.DISPLAY_NAME,PC.DISPLAY_NAME,FV.FORMULA_NAME,RDT.TIER_SEQUENCE;

BEGIN 

 --OPEN :xdo_cursor FOR
 --SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;
 FOR X IN RATEPAY
 LOOP
  IF(p_name <> X.PARTICIPANT_NAME OR cp_name <> X.COMP_PLAN OR pc_name <> X.PLAN_COMP OR fo_name <> X.FORMULA_NAME) THEN
   rate_pay.extend;
   cnt  := cnt + 1;
   p_name  := X.PARTICIPANT_NAME;
   cp_name  := X.COMP_PLAN;
   pc_name  := X.PLAN_COMP;
   fo_name  := X.FORMULA_NAME;
   iter  := X.NUMBER_TIER;
   commission  := X.COMMISSION_VALUE;
   min_amt  := X.MINIMUM_AMOUNT;
   
   rate_pay(cnt).p_name := X.PARTICIPANT_NAME;
   rate_pay(cnt).cp_name := X.COMP_PLAN;
   rate_pay(cnt).pc_name := X.PLAN_COMP;
   rate_pay(cnt).fo_name := X.FORMULA_NAME;   
   rate_pay(cnt).iter := X.NUMBER_TIER;   
   rate_pay(cnt).commission := X.COMMISSION_VALUE;
   rate_pay(cnt).min_amt := X.MINIMUM_AMOUNT;
   
  END IF;
   
  IF (rate_pay(cnt).commission <> X.COMMISSION_VALUE) THEN
    rate_pay.extend;
    cnt  := cnt + 1;
    rate_pay(cnt).p_name := X.PARTICIPANT_NAME;
    rate_pay(cnt).cp_name := X.COMP_PLAN;
    rate_pay(cnt).pc_name := X.PLAN_COMP;
    rate_pay(cnt).fo_name := X.FORMULA_NAME;   
    rate_pay(cnt).iter := X.NUMBER_TIER;   
    rate_pay(cnt).commission := X.COMMISSION_VALUE;
    rate_pay(cnt).min_amt := X.MINIMUM_AMOUNT;    
  END IF;
  IF (X.NUMBER_TIER = X.TIER_SEQUENCE) THEN
   p_name  := NULL;
   cp_name  := NULL;
   pc_name  := NULL;
   fo_name  := NULL;
   iter  := 0;
   commission  := 0;
   min_amt  := 0;   
  END IF;
 END LOOP;
 --OPEN :xdo_cursor FOR SELECT * FROM TABLE(CAST(rate_pay as ParticipantRatePayout));

END;

谢谢

听起来像你想要一个流水线表功能。这些返回您的记录类型的表。您可以使用表运算符进行查询:

create or replace package pkg as 

  type rec is record (
    c1 int, c2 int
  );
  
  type rec_tab is table of rec;

  function f ( num_rows int )
    return rec_tab
    pipelined;
end;
/

create or replace package body pkg as 

  function f ( num_rows int )
    return rec_tab
    pipelined as
    rw rec;
  begin
    
    for i in 1 .. num_rows loop
      rw.c1 := i;
      rw.c2 := i * 2 ;
      pipe row ( rw );
    end loop;
    
    return;
  end f;
end;
/

select * from table ( pkg.f ( 5 ) );

C1    C2   
    1     2 
    2     4 
    3     6 
    4     8 
    5    10 

所以这只是运行查询和处理结果的问题。

如果由于某种原因您需要参考光标，只需打开一个运行该功能的光标:

var cur refcursor;

begin
  open :cur for
    select * from table ( pkg.f ( 5 ) );
end;
/

print :cur;

        C1         C2
---------- ----------
         1          2
         2          4
         3          6
         4          8
         5         10

如果您想了解更多有关这些的信息，请参阅:
https://blogs.oracle.com/oraclemagazine/pipelined-table-functions