Создание пакета PL/SQL, содержащего функцию, которая возвращает неработающую таблицу

Сначала создайте объект типа...

1. Создать тип(ы)

Create Or Replace Type type_trdelclvr_QADJ  IS
  object( TOTAL_CLAIMS INTEGER,
          CALCULATED_YEAR INTEGER,
          MTH varchar2(500),
          CLAIM  INTEGER,
          CIW INTEGER,
          PAPER INTEGER,
          MBRECLAIM INTEGER,
          PRVRDIALUP INTEGER,
          PRVRIP INTEGER,
          ELECTRONIC INTEGER
        );

--   table type for use with sql select
create or replace TYPE T1_TAB AS TABLE OF type_trdelclvr_QADJ;

2. Создать пакет

Create Or Replace Package P_trdelclvr01 AS 
        --
        FUNCTION fn_trdelclvr_QADJ(p_Param1 VarChar2 := 'A') Return type_trdelclvr_QADJ; 
/* OR ---> Return T1_TAB (if you want to return it as table of ...) */
        --
END P_trdelclvr01;

3. Создать тело пакета

create or replace Package Body
    P_trdelclvr01 AS 
  
        Function fn_trdelclvr_QADJ(p_Param1 VarChar2 := 'A') Return type_trdelclvr_QADJ As
/* OR ---> Return T1_TAB (if you want to return it as table of ...) */
         BEGIN
            Declare 
                
                Cursor CURSEUR_ETAPE Is
                    Select type_trdelclvr_QADJ(tbl.total_claims, tbl.calculated_year, tbl.mth, tbl.claim, tbl.ciw, tbl.paper, tbl.MbrEclaim, tbl.PrvrDialUp, tbl.PrvrIP, tbl.Electronic)
/* This if it is table  (below row)
Select T1_TAB(type_trdelclvr_QADJ(tbl.total_claims, tbl.calculated_year, tbl.mth, tbl.claim, tbl.ciw, tbl.paper, tbl.MbrEclaim, tbl.PrvrDialUp, tbl.PrvrIP, tbl.Electronic))  */
                    From ( Select
                              100 total_claims,
                              '2024' calculated_year,
                              '2024-08' mth,
                              15 claim,
                              10 ciw,
                              5 paper,
                              0 MbrEclaim,
                              7 PrvrDialUp,
                              8 PrvrIP,
                              15 Electronic
                          From Dual
                        ) tbl;
              type_tbl type_trdelclvr_QADJ;
              -- below for table
              /*  type_tbl T1_TAB;  */
            Begin
                Open CURSEUR_ETAPE;
                    Fetch CURSEUR_ETAPE INTO type_tbl;
                Close CURSEUR_ETAPE;
                RETURN type_tbl;
            End;
        END fn_trdelclvr_QADJ;

END P_trdelclvr01;

4. Проверьте это (sql-developer) — для типа объекта:

SET SERVEROUTPUT ON
DECLARE
    v_type_tbl type_trdelclvr_QADJ ;
BEGIN
    v_type_tbl := P_trdelclvr01.fn_trdelclvr_QADJ('B');
    dbms_output.put_line('Year_Month is ' || v_type_tbl.MTH);
END;
/

/*    R e s u l t : 
Year_Month is 2024-08
PL/SQL procedure successfully completed    */

Тот же результат с кодом ниже (T1_TAB для таблицы объекта)

SET SERVEROUTPUT ON
DECLARE
    v_mth  VARCHAR2(12);
BEGIN
    Select MTH Into v_mth From P_trdelclvr01.fn_trdelclvr_QADJ('B');
    dbms_output.put_line('Year_Month is ' || v_mth);
END;
/

... ИЛИ проверка таблицы с помощью sql Select * ...

Select * From P_trdelclvr01.fn_trdelclvr_QADJ('B');

/*      R e s u l t :
TOTAL_CLAIMS  CALCULATED_YEAR  MTH       CLAIM  CIW  PAPER  MBRECLAIM  PRVRDIALUP  PRVRIP  ELECTRONIC
------------  ---------------  --------  -----  ---  -----  ---------  ----------  ------  ----------
100           2024             2024-08      15   10      5          0           7       8          15    */

База данных Oracle содержит два разных «движка»:

• Механизм SQL обрабатывает операторы SQL (т. е. SELECT, INSERT, UPDATE, CREATE TABLE и т. д.) и может использовать типы, объявленные в операторах области видимости SQL (т. е. с CREATE TYPE).

• Механизм PL/SQL обрабатывает операторы PL/SQL и может использовать типы, объявленные как в областях PL/SQL, так и в области SQL (т. е. объявленные локально в блоке PL/SQL, объявленные глобально в пакете PL/SQL или в области SQL с помощью заявление CREATE TYPE).

Если вы хотите использовать тип в инструкции SQL, объявите его в области SQL (а не в области PL/SQL).

Кроме того:

• Тип OBJECT является типом SQL и ДОЛЖЕН быть объявлен в области SQL. (Если вам нужен полуэквивалентный тип PL/SQL, используйте RECORD).
• Когда вы объявляете функцию в пакете, сигнатура функции в спецификации пакета ДОЛЖНА быть идентична сигнатуре функции в теле пакета.
• Вам не нужно использовать CURSOR, можно просто SELECT ... BULK COLLECT INTO ....
• Если вы собираетесь использовать тип PL/SQL (который здесь не подходит) и хотите, чтобы он был доступен вне пакета, объявите его в спецификации пакета, чтобы он был доступен публично (а не в теле пакета, когда он является приватным для пакета).

Так:

CREATE TYPE type_trdelclvr_QADJ as object(
  TOTAL_CLAIMS    INTEGER,
  calculated_year INTEGER,
  MTH             varchar2(500),
  CLAIM           INTEGER,
  CIW             INTEGER,
  PAPER           INTEGER,
  MBRECLAIM       INTEGER,
  PRVRDIALUP      INTEGER,
  PRVRIP          INTEGER,
  ELECTRONIC      INTEGER
);

CREATE TYPE table_trdelclvr_QADJ is table of type_trdelclvr_QADJ;   

Затем вы можете объявить подпись пакета:

CREATE OR REPLACE PACKAGE P_trdelclvr01 AS    
  FUNCTION fn_trdelclvr_QADJ(
    P_CLAIM varchar2,
    P_CIW varchar2,
    P_PP varchar2,
    P_PAPER varchar2,
    P_MBR varchar2,
    P_EDI varchar2,
    P_MBR_NatApp varchar2,
    P_CDAnet_DialUp varchar2,
    P_CDAnet_IP varchar2,
    P_CDAnet_ICA varchar2,
    P_START_DATE date,
    P_END_DATE date,
    P_DENTAL varchar2,
    P_IVR varchar2
  ) return table_trdelclvr_QADJ;
END P_trdelclvr01;
/

и тело пакета:

CREATE OR REPLACE PACKAGE BODY P_trdelclvr01 AS 
  FUNCTION fn_trdelclvr_QADJ(
    P_CLAIM         varchar2,
    P_CIW           varchar2,
    P_PP            varchar2,
    P_PAPER         varchar2,
    P_MBR           varchar2,
    P_EDI           varchar2,
    P_MBR_NatApp    varchar2,
    P_CDAnet_DialUp varchar2,
    P_CDAnet_IP     varchar2,
    P_CDAnet_ICA    varchar2,
    P_START_DATE    date,
    P_END_DATE      date,
    P_DENTAL        varchar2,
    P_IVR           varchar2
  ) RETURN table_trdelclvr_QADJ
  AS
    test_type table_trdelclvr_QADJ;
  BEGIN
    SELECT type_trdelclvr_QADJ( 
             count(1),
             TO_CHAR(TRUNC(system_date, 'MM'),'YYYY'),
             TO_CHAR(TRUNC(system_date, 'MM'),'YYYY-MM'),
             sum(decode(document_category, P_CLAIM ,1,0)),
             sum(decode(document_category, P_CIW ,1, P_PP ,1,0)),
             sum(decode(document_source,P_PAPER,1,0)),
             sum(decode(document_source, P_MBR,decode(edi_app_source,P_MBR,1,0),0)
               + decode(document_source, P_EDI, decode(edi_app_source, P_MBR_NatApp, 1,0),0)),
             sum(decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_DialUp, 1,0),0)),
             sum(decode(document_source, P_EDI,decode(edi_app_source, P_CDAnet_IP, 1,0),0)
               + decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_ICA, 1,0),0)),
             sum(decode(document_source, P_EDI, 1,0))
           )
    BULK COLLECT INTO test_type
    FROM   blu_adj_statistics adj
    WHERE  system_date >= P_START_DATE
    AND    system_date < P_END_DATE + 1
    AND    claim_type = P_DENTAL
    AND    claim_iteration = 1
    AND    document_source <> P_IVR
    AND    hsa_indicator is null
    GROUP BY TRUNC(system_date, 'MM')
    ORDER BY TRUNC(system_date, 'MM');
     
    RETURN test_type;
  END;
END P_trdelclvr01;
/

Учитывая примерные данные:

CREATE TABLE blu_adj_statistics (
  system_date       DATE,
  document_category VARCHAR2(50),
  document_source   VARCHAR2(50),
  claim_type        VARCHAR2(50),
  claim_iteration   NUMBER,
  hsa_indicator     NUMBER,
  edi_app_source    VARCHAR2(50)
)

INSERT INTO blu_adj_statistics VALUES (SYSDATE, 'A', 'B', 'C', 1, NULL, 'D');

Затем:

DECLARE
  v_data table_trdelclvr_QADJ;
BEGIN
  v_data := P_trdelclvr01.fn_trdelclvr_QADJ(
    P_CLAIM         => 'A',
    P_CIW           => 'A',
    P_PP            => 'X',
    P_PAPER         => 'B',
    P_MBR           => 'B',
    P_EDI           => 'D',
    P_MBR_NatApp    => 'D',
    P_CDAnet_DialUp => 'D',
    P_CDAnet_IP     => 'D',
    P_CDAnet_ICA    => 'D',
    P_START_DATE    => TRUNC(SYSDATE),
    P_END_DATE      => TRUNC(SYSDATE),
    P_DENTAL        => 'C',
    P_IVR           => 'A'
  );

  FOR i IN 1 .. v_data.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      v_data(i).TOTAL_CLAIMS
      ||', '||v_data(i).calculated_year
      ||', '||v_data(i).MTH
      ||', '||v_data(i).CLAIM
      ||', '||v_data(i).CIW
      ||', '||v_data(i).PAPER
      ||', '||v_data(i).MBRECLAIM
      ||', '||v_data(i).PRVRDIALUP
      ||', '||v_data(i).PRVRIP
      ||', '||v_data(i).ELECTRONIC
    );
  END LOOP;
END;
/

Выходы:

1, 2024, 2024-08, 1, 1, 1, 0, 0, 0, 0

рабочий пример