如何使包装器返回除ref游标以外的东西 [英] How to make a wrapper to return something other than ref cursor

问题描述

我有以下PL SQL函数返回一个ref游标，但我使用的应用程序不支持ref游标。如何使此代码返回非ref游标

I've got the following PL SQL function that returns a ref cursor but the application i am using does not support ref cursors. How can i make this code return something other than ref cursor

FUNCTION getADedIcWarningsProv(p_hos_id IN work_entity_data.hos_id%TYPE
                                ,p_date        IN DATE
                                )


RETURN eOdatatypes_package.eOrefcur
  IS

v_refcur eOdatatypes_package.eOrefcur;

BEGIN

OPEN v_refcur FOR
SELECT IF_type IF_type
      ,COUNT(*)       number_infected
FROM (SELECT DISTINCT bd.PT_id             PT_id
                     ,CASE WHEN NVL(O_package.get_O_code_property(pw.warning_code,'Setl'),'N') = 'Y'
                      THEN cd.description
                      ELSE 'Other'
                      END                       IF_type
      FROM PT_ad       pad
          ,BD_details      bd
          ,PT_warnings pw
          ,codes            cd
      WHERE bd.current_record = 'Y'
      AND   bd.BD_location IS NOT NULL
      AND   bd.BD_status IN (SELECT code
                                FROM codes
                               WHERE prog_code IN (1, 1, 2) 
                                 AND code_type = 4)
      AND   bd.AD_no = pad.AD_no
      AND   pad.hos_id = p_hos_id
      AND   pw.PT_id   = bd.PT_id
      AND   pw.warning_to IN ('D','Q')
      AND   p_date BETWEEN pw.applies_start
                       AND NVL(pw.applies_end,p_date)
      AND   NVL(O_package.get_O_code_property(pw.warning_code,'INFT'),'Y') = 'N'
      AND   pw.warning_code = cd.code)
GROUP BY IF_type
ORDER BY IF_type;
RETURN v_refcur;
END getADedIcWarningsProv;

OUTPUT：

IF_TYPE                           NUMBER_IF
---------------------------------------- ---------------
C                                                 2
M                                                 6
Other                                             4
3 rows selected

推荐答案

您可以使用管道函数一次返回一个记录，

You can use a pipeline function to return a result set one record at a time, but in a way that the SQL engine can understand.

create or replace package WrapperSample is

  type TResultRow is record(
     if_type         codes.cd%type
    ,number_infected Integer);

  type TResultRowList is table of TResultRow;

  function GetADedIcWarningsProv
  (
    p_hos_id in work_entity_data.hos_id%type
   ,p_date   in date
  ) return TResultRowList
    pipelined;

end WrapperSample;
/

create or replace package body WrapperSample is

  function GetADedIcWarningsProv
  (
    p_hos_id in work_entity_data.hos_id%type
   ,p_date   in date
  ) return TResultRowList
    pipelined is
    v_refcur   eOdatatypes_package.eOrefcur;
    currentRow TResultRow;
  begin
    v_refcur := YourSchema.getADedIcWarningsProv(p_hos_id, p_date);

    loop
      fetch v_refcur
        INTO currentRow;
      exit when v_refcur%NotFound;
      pipe row(currentRow);
    end loop;

    close v_refcur;

    return;
  end;

end WrapperSample;
/

使用此软件包，您可以选择您的引用游标：

Using this package, you can select your ref cursor:

SELECT if_type
      ,number_infected
FROM table(WrapperSample.getADedIcWarningsProv(1, 2))

这篇关于如何使包装器返回除ref游标以外的东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！