저장 프로시저에서 여러 행을 반환하는 방법(Oracle PL/SQL)

저장 프로시저에서 여러 행을 반환하는 방법(Oracle PL/SQL)

인수에 따라 다른 레코드 세트를 반환하는 하나의 인수로 저장 프로시저를 만들고 싶습니다.어떻게 하면 좋을까요?일반 SQL에서 호출할 수 있습니까?

다음은 테이블인 것처럼 쿼리할 수 있는 결과 세트를 반환하는 함수를 구축하는 방법입니다.

SQL> create type emp_obj is object (empno number, ename varchar2(10));
  2  /

Type created.

SQL> create type emp_tab is table of emp_obj;
  2  /

Type created.

SQL> create or replace function all_emps return emp_tab
  2  is
  3     l_emp_tab emp_tab := emp_tab();
  4     n integer := 0;
  5  begin
  6     for r in (select empno, ename from emp)
  7     loop
  8        l_emp_tab.extend;
  9        n := n + 1;
 10       l_emp_tab(n) := emp_obj(r.empno, r.ename);
 11     end loop;
 12     return l_emp_tab;
 13  end;
 14  /

Function created.

SQL> select * from table (all_emps);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7902 FORD
      7934 MILLER

REFCURSOR를 반환하고 싶은 것 같습니다.

create function test_cursor 
            return sys_refcursor
            is
                    c_result sys_refcursor;
            begin
                    open c_result for
                    select * from dual;
                    return c_result;
            end;

업데이트: SQL에서 호출할 필요가 있는 경우 @Tony Andrews가 제안하는 테이블 함수를 사용하십시오.

Oracle 파이프라인 함수를 사용할 수 있습니다.

기본적으로 PLSQL(또는 Java 또는 c) 루틴을 테이블이 아닌 데이터의 '소스'로 하는 경우 파이프라인 함수를 사용합니다.

단순 예제 - 일부 랜덤 데이터 생성
입력 인수에 따라 N개의 고유 난수를 작성하려면 어떻게 해야 합니까?

create type array
as table of number;


create function  gen_numbers(n in number default null)
return array
PIPELINED
as
begin
  for i in 1 .. nvl(n,999999999)
  loop
     pipe row(i);
 end loop;
 return;
end;

어떤 일에 세 줄의 줄이 필요하다고 가정해 봅시다.다음 두 가지 방법 중 하나를 사용할 수 있습니다.

select * from TABLE(gen_numbers(3));

열_값

---

       1
       2
       3

또는

select * from TABLE(gen_numbers)
 where rownum <= 3;

열_값

---

       1
       2
       3

파이프에 연결된 함수1 파이프 연결 함수2

일반 SQL에서 사용하고 싶다면 스토어 프로시저에서 테이블 또는 임시 테이블을 결과 행으로 채우도록 하겠습니다(또는 @Tony Andrew 접근법).
@Thilo의 솔루션을 사용하려면 PL/SQL을 사용하여 커서를 루프해야 합니다.예를 들어 (@Thilo가 했던 것과 같은 함수 대신 프로시저를 사용했습니다.)

create or replace procedure myprocedure(retval in out sys_refcursor) is
begin
  open retval for
    select TABLE_NAME from user_tables;
end myprocedure;

 declare 
   myrefcur sys_refcursor;
   tablename user_tables.TABLE_NAME%type;
 begin
   myprocedure(myrefcur);
   loop
     fetch myrefcur into tablename;
     exit when myrefcur%notfound;
     dbms_output.put_line(tablename);
   end loop;
   close myrefcur;
 end;

create procedure <procedure_name>(p_cur out sys_refcursor) as begin open p_cur for select * from <table_name> end;

언급URL : https://stackoverflow.com/questions/101033/how-to-return-multiple-rows-from-the-stored-procedure-oracle-pl-sql