table type을 return하는 패키지 function

1. 스펙 부분

create or replace package xx_table_test is

  //1. recordd type
  TYPE T_MESSAGE_REC is record(
    APPLICATION_ID    NUMBER,
    LANGUAGE_CODE     VARCHAR2(4),
    MESSAGE_NUMBER    NUMBER(9),
    MESSAGE_NAME      VARCHAR2(30),
    MESSAGE_TEXT      VARCHAR2(2000),
    CREATION_DATE     DATE,
    CREATED_BY        NUMBER(15),
    LAST_UPDATE_DATE  DATE,
    LAST_UPDATED_BY   NUMBER(15),
    LAST_UPDATE_LOGIN NUMBER(15),
    DESCRIPTION       VARCHAR2(240),
    TYPE              VARCHAR2(30),
    MAX_LENGTH        NUMBER,
    CATEGORY          VARCHAR2(10),
    SEVERITY          VARCHAR2(10),
    FND_LOG_SEVERITY  NUMBER);

//2. record type을 가지고 테이블 type 을 만들고
  TYPE T_MESSAGE_TBL is table of T_MESSAGE_REC;

//3. 테이블 type을 리턴하는 펑션 선언
  function message_f(p_message_name varchar2) return T_MESSAGE_TBL
    pipelined;

end xx_table_test;

2. BODY 부분

create or replace package body xx_table_test is
//1.   데이터를 가져올 커서 선언 만만한걸로..
  function message_f(p_message_name varchar2) return T_MESSAGE_TBL
    pipelined as
    cursor c_msg is
      select APPLICATION_ID,
             LANGUAGE_CODE,
             MESSAGE_NUMBER,
             MESSAGE_NAME,
             MESSAGE_TEXT,
             CREATION_DATE,
             CREATED_BY,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             DESCRIPTION,
             TYPE,
             MAX_LENGTH,
             CATEGORY,
             SEVERITY,
             FND_LOG_SEVERITY
     
        from fnd_new_messages a
       where a.message_name like p_message_name;
 
    pragma autonomous_transaction;

//2. 커서를 데이터를 담을 Row Type 선언
    x_msg_rec      c_msg%rowtype;

//3. 리턴할 값을 저장시킬 테이블 Type 선언
    x_tbl          T_MESSAGE_TBL := T_MESSAGE_TBL();
  
  begin

  //4. 커서 열고..
    open c_msg;
    loop
      fetch c_msg
        into x_msg_rec;
      exit when c_msg%notfound;
    //5 커서의 쿼리 데이터를  테이블 에 담는다.
      x_tbl.extend;
      x_tbl(x_tbl.count).language_code := x_msg_rec.language_code;
      x_tbl(x_tbl.count).message_name := x_msg_rec.message_name;
      x_tbl(x_tbl.count).message_text := x_msg_rec.message_text;

//6. 여기까지..
      pipe row(x_tbl(x_tbl.count));
    end loop;

    close c_msg;
    return;
  end;
 
end xx_table_test;

3. 쿼리로 펑션 결과 가져오기

select * from table(xx_table_test.message_f('AP%'));

위에처럼 사용 하면 끝