Oracle 테이블 변수/어레이에서 값을 선택하시겠습니까?

Oracle 테이블 변수/어레이에서 값을 선택하시겠습니까?

마지막 질문(Oracle PL/SQL의 표 변수?)에 이어...

어레이/테이블에 값이 있는 경우 값을 다시 가져오려면 어떻게 해야 합니까?선호하는 것은 선택문이나 비슷한 것을 사용하는 것입니까?

제가 지금까지 알아낸 것은 다음과 같습니다.

declare
    type array is table of number index by binary_integer;
    pidms array;
begin
    for i in    (
                select distinct sgbstdn_pidm
                from sgbstdn
                where sgbstdn_majr_code_1 = 'HS04'
                and sgbstdn_program_1 = 'HSCOMPH'
                )
    loop
        pidms(pidms.count+1) := i.sgbstdn_pidm;
    end loop;

    select *
    from pidms; --ORACLE DOESN'T LIKE THIS BIT!!!
end;

dbms_output을 사용하여 출력할 수 있습니다.putline(), 하지만 다른 테이블에서 선택하는 것과 같은 결과 세트를 얻고 싶습니다.

잘 부탁드립니다, 매트

글로벌 임시 테이블이 필요할 수 있습니다.

Oracle에서 이러한 데이터는 한 번 생성된 후 호출되면 세션의 개인 데이터가 됩니다.

오라클 문서 링크

이런 걸 시도해 보세요...

CREATE GLOBAL TEMPORARY TABLE temp_number
   ( number_column   NUMBER( 10, 0 )
   )
   ON COMMIT DELETE ROWS;

BEGIN 
   INSERT INTO temp_number
      ( number_column )
      ( select distinct sgbstdn_pidm 
          from sgbstdn 
         where sgbstdn_majr_code_1 = 'HS04' 
           and sgbstdn_program_1 = 'HSCOMPH' 
      ); 

    FOR pidms_rec IN ( SELECT number_column FROM temp_number )
    LOOP 
        -- Do something here
        NULL; 
    END LOOP; 
END; 
/

Oracle에서 PL/SQL 및 SQL 엔진은 약간의 분리를 유지합니다.PL/SQL 내에서 SQL 문을 실행하면 INDEX BY 테이블과 같은 PL/SQL 관련 구조에 대한 지식이 없는 SQL 엔진으로 전달됩니다.

따라서 PL/SQL 블록에 유형을 선언하는 대신 데이터베이스 스키마 내에 동등한 컬렉션 유형을 만들어야 합니다.

CREATE OR REPLACE TYPE array is table of number;
/

그런 다음 PL/SQL 내에서 다음 두 가지 예제와 같이 사용할 수 있습니다.

SQL> l
  1  declare
  2    p  array := array();
  3  begin
  4    for i in (select level from dual connect by level < 10) loop
  5      p.extend;
  6      p(p.count) := i.level;
  7    end loop;
  8    for x in (select column_value from table(cast(p as array))) loop
  9       dbms_output.put_line(x.column_value);
 10    end loop;
 11* end;
SQL> /
1
2
3
4
5
6
7
8
9

PL/SQL procedure successfully completed.

SQL> l
  1  declare
  2    p  array := array();
  3  begin
  4    select level bulk collect into p from dual connect by level < 10;
  5    for x in (select column_value from table(cast(p as array))) loop
  6       dbms_output.put_line(x.column_value);
  7    end loop;
  8* end;
SQL> /
1
2
3
4
5
6
7
8
9

PL/SQL procedure successfully completed.

주석 기반의 추가 예제

저의 답변에 대한 당신의 의견과 질문 자체로 볼 때, 저는 이렇게 구현할 것이라고 생각합니다.실제 테이블에서 레코드를 한 번 가져와 글로벌 개인 패키지에 저장할 수 있도록 패키지를 사용하고, 열려 있는 참조를 반환하는 기능을 가집니다.

CREATE OR REPLACE PACKAGE p_cache AS
  FUNCTION get_p_cursor RETURN sys_refcursor;
END p_cache;
/

CREATE OR REPLACE PACKAGE BODY p_cache AS

  cache_array  array;

  FUNCTION get_p_cursor RETURN sys_refcursor IS
    pCursor  sys_refcursor;
  BEGIN
    OPEN pCursor FOR SELECT * from TABLE(CAST(cache_array AS array));
    RETURN pCursor;
  END get_p_cursor;

  -- Package initialization runs once in each session that references the package
  BEGIN
    SELECT level BULK COLLECT INTO cache_array FROM dual CONNECT BY LEVEL < 10;
  END p_cache;
/

sql 배열 유형은 필요하지 않습니다.요소 유형이 원시 유형인 경우에는 해당되지 않습니다. (Varchar, 숫자, 날짜...)

매우 기본적인 샘플:

declare
  type TPidmList is table of sgbstdn.sgbstdn_pidm%type;
  pidms TPidmList;
begin
  select distinct sgbstdn_pidm
  bulk collect into pidms
  from sgbstdn
  where sgbstdn_majr_code_1 = 'HS04'
  and sgbstdn_program_1 = 'HSCOMPH';

  -- do something with pidms

  open :someCursor for
    select value(t) pidm
    from table(pidms) t;
end;

재사용을 원할 때는 어떤 모습일지 알면 흥미로울 수 있습니다.여러 개의 명령을 실행하는 경우 이들 명령을 패키지로 그룹화할 수 있습니다.위의 개인 패키지 변수 트릭에는 단점이 있습니다.패키지에 변수를 추가할 때 상태를 지정하면 상태 비저장 함수의 묶음으로 작동하지 않고 대신 이상한 종류의 싱글톤 개체 인스턴스로 작동합니다.

예를 들어 본문을 다시 컴파일하면 이전에 이미 본문을 사용한 세션에서 예외가 발생합니다.(변수 값이 무효화되었기 때문에)

그러나 패키지 또는 sql에서 전역으로 유형을 선언하고 이를 사용해야 하는 메서드에서 매개 변수로 사용할 수 있습니다.

create package Abc as
  type TPidmList is table of sgbstdn.sgbstdn_pidm%type;

  function CreateList(majorCode in Varchar, 
                      program in Varchar) return TPidmList;

  function Test1(list in TPidmList) return PLS_Integer;
  -- "in" to make it immutable so that PL/SQL can pass a pointer instead of a copy
  procedure Test2(list in TPidmList);
end;

create package body Abc as

  function CreateList(majorCode in Varchar, 
                      program in Varchar) return TPidmList is
    result TPidmList;
  begin
    select distinct sgbstdn_pidm
    bulk collect into result
    from sgbstdn
    where sgbstdn_majr_code_1 = majorCode
    and sgbstdn_program_1 = program;

    return result;
  end;

  function Test1(list in TPidmList) return PLS_Integer is
    result PLS_Integer := 0;
  begin
    if list is null or list.Count = 0 then
      return result;
    end if;

    for i in list.First .. list.Last loop
      if ... then
        result := result + list(i);
      end if;
    end loop;
  end;

  procedure Test2(list in TPidmList) as
  begin
    ...
  end;

  return result;
end;

어떻게 부르나요?

declare
  pidms constant Abc.TPidmList := Abc.CreateList('HS04', 'HSCOMPH');
  xyz PLS_Integer;
begin
  Abc.Test2(pidms);
  xyz := Abc.Test1(pidms);
  ...

  open :someCursor for
    select value(t) as Pidm,
           xyz as SomeValue
    from   table(pidms) t;
end;

언급URL : https://stackoverflow.com/questions/1573877/selecting-values-from-oracle-table-variable-array