SELECT FROM 문에서 테이블 유형을 사용하는 방법은 무엇입니까?

SELECT FROM 문에서 테이블 유형을 사용하는 방법은 무엇입니까?

이 질문은 이것과 거의 같습니다.

패키지 헤더에서:
다음 행 유형을 선언했습니다.

  TYPE exch_row IS RECORD(
    currency_cd VARCHAR2(9),
    exch_rt_eur NUMBER,
    exch_rt_usd NUMBER);

다음 테이블 유형:

  TYPE exch_tbl IS TABLE OF exch_row INDEX BY BINARY_INTEGER;

변수가 추가되었습니다.

exch_rt exch_tbl;

패키지 본문:
이 테이블 변수를 데이터로 채웁니다.

패키지 본문의 절차에서 다음을 수행합니다.
다음 문장을 사용합니다.

CURSOR c0 IS
  SELECT i.*, rt.exch_rt_eur, rt.exch_rt_usd
  FROM item i, exch_rt rt
  WHERE i.currency = rt.exchange_cd

Oracle에서 이 작업을 수행하는 방법은 무엇입니까?

---

메모들

실제로 MSSQL에서 '테이블 변수' 솔루션을 찾고 있습니다.

DECLARE @exch_tbl TABLE
(
  currency_cd VARCHAR(9),
  exch_rt_eur NUMBER,
  exch_rt_usd NUMBER)
)

저장 프로시저 내에서 이 표 변수를 사용합니다.

SQL에서는 스키마 수준(패키지 또는 프로시저 수준이 아님)에서 정의된 테이블 유형만 사용할 수 있으며, 스키마 수준에서는 인덱스별 테이블(연관 배열)을 정의할 수 없습니다.따라서 - 다음과 같이 중첩 테이블을 정의해야 합니다.

create type exch_row as object (
    currency_cd VARCHAR2(9),
    exch_rt_eur NUMBER,
    exch_rt_usd NUMBER);

create type exch_tbl as table of exch_row;

그런 다음 SQL에서 TABLE 연산자를 사용하여 다음과 같이 사용할 수 있습니다.

declare
   l_row     exch_row;
   exch_rt   exch_tbl;
begin
   l_row := exch_row('PLN', 100, 100);
   exch_rt  := exch_tbl(l_row);

   for r in (select i.*
               from item i, TABLE(exch_rt) rt
              where i.currency = rt.currency_cd) loop
      -- your code here
   end loop;
end;
/

Oracle 12C 이전 버전에서는 다음과 같은 SQL 유형을 기반으로 하는 테이블에서만 PL/SQL 정의 테이블을 선택할 수 없습니다.

CREATE OR REPLACE TYPE exch_row AS OBJECT(
currency_cd VARCHAR2(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER);


CREATE OR REPLACE TYPE exch_tbl AS TABLE OF exch_row;

이제 Oracle 12C에서는 패키지 사양에 정의된 PL/SQL 테이블에서 선택할 수 있습니다.

패키지 내부의 단일 쿼리에서 이 작업을 수행할 수 없습니다. SQL과 PL/SQL 유형을 혼합할 수 없으며 Tony, Marcin 및 Thio가 말한 대로 SQL 계층에서 유형을 정의해야 합니다.

이 작업을 로컬에서 수행하고 테이블 유형을 BINITAR_INTEGER 대신 VARCHAR로 인덱싱할 수 있다면 다음과 같은 작업을 수행할 수 있습니다.

-- dummy ITEM table as we don't know what the real ones looks like
create table item(
    item_num number,
    currency varchar2(9)
)
/   

insert into item values(1,'GBP');
insert into item values(2,'AUD');
insert into item values(3,'GBP');
insert into item values(4,'AUD');
insert into item values(5,'CDN');

create package so_5165580 as
    type exch_row is record(
        exch_rt_eur number,
        exch_rt_usd number);
    type exch_tbl is table of exch_row index by varchar2(9);
    exch_rt exch_tbl;
    procedure show_items;
end so_5165580;
/

create package body so_5165580 as
    procedure populate_rates is
        rate exch_row;
    begin
        rate.exch_rt_eur := 0.614394;
        rate.exch_rt_usd := 0.8494;
        exch_rt('GBP') := rate;
        rate.exch_rt_eur := 0.9817;
        rate.exch_rt_usd := 1.3572;
        exch_rt('AUD') := rate;
    end;

    procedure show_items is
        cursor c0 is
            select i.*
            from item i;
    begin
        for r0 in c0 loop
            if exch_rt.exists(r0.currency) then
                dbms_output.put_line('Item ' || r0.item_num
                    || ' Currency ' || r0.currency
                    || ' EUR ' || exch_rt(r0.currency).exch_rt_eur
                    || ' USD ' || exch_rt(r0.currency).exch_rt_usd);
            else
                dbms_output.put_line('Item ' || r0.item_num
                    || ' Currency ' || r0.currency
                    || ' ** no rates defined **');
            end if;
        end loop;
    end;
begin
    populate_rates;
end so_5165580;
/

루프 내에서, 사용할 것으로 예상되는 모든 곳에서r0.exch_rt_eur대신에 당신은 사용합니다.exch_rt(r0.currency).exch_rt_eurUSD도 마찬가지입니다.익명 블록에서 테스트:

begin
    so_5165580.show_items;
end;
/

Item 1 Currency GBP EUR .614394 USD .8494
Item 2 Currency AUD EUR .9817 USD 1.3572
Item 3 Currency GBP EUR .614394 USD .8494
Item 4 Currency AUD EUR .9817 USD 1.3572
Item 5 Currency CDN ** no rates defined **

---

Stef가 게시한 답변에 따르면, 이것은 패키지에 포함될 필요가 전혀 없습니다. 동일한 결과를 얻을 수 있습니다.insert진술.가정하면EXCH유로에 대한 다른 통화의 환율을 보유하고 있습니다. 달러를 포함하여.currency_key=1:

insert into detail_items
with rt as (select c.currency_cd as currency_cd,
        e.exch_rt as exch_rt_eur,
        (e.exch_rt / usd.exch_rt) as exch_rt_usd
    from exch e,
        currency c,
        (select exch_rt from exch where currency_key = 1) usd
    where c.currency_key = e.currency_key)
select i.doc,
    i.doc_currency,
    i.net_value,
    i.net_value / rt.exch_rt_usd AS net_value_in_usd,
    i.net_value / rt.exch_rt_eur as net_value_in_euro
from item i
join rt on i.doc_currency = rt.currency_cd;

19.99 GBP 및 25.00 AUD의 가치를 지닌 품목은 다음과 같습니다.detail_items:

DOC DOC_CURRENCY NET_VALUE         NET_VALUE_IN_USD  NET_VALUE_IN_EURO
--- ------------ ----------------- ----------------- -----------------
1   GBP          19.99             32.53611          23.53426
2   AUD          25                25.46041          18.41621

통화 항목을 더 재사용할 수 있도록 하려면 다음 보기를 만들 수 있습니다.

create view rt as
select c.currency_cd as currency_cd,
    e.exch_rt as exch_rt_eur,
    (e.exch_rt / usd.exch_rt) as exch_rt_usd
from exch e,
    currency c,
    (select exch_rt from exch where currency_key = 1) usd
where c.currency_key = e.currency_key;

그런 다음 다음 값을 사용하여 삽입합니다.

insert into detail_items
select i.doc,
    i.doc_currency,
    i.net_value,
    i.net_value / rt.exch_rt_usd AS net_value_in_usd,
    i.net_value / rt.exch_rt_eur as net_value_in_euro
from item i
join rt on i.doc_currency = rt.currency_cd;

이 문제에 대한 모든 도움에 감사드립니다.여기에 제 솔루션을 게시하겠습니다.

패키지 머리글

CREATE OR REPLACE PACKAGE X IS
  TYPE exch_row IS RECORD(
    currency_cd VARCHAR2(9),
    exch_rt_eur NUMBER,
    exch_rt_usd NUMBER);
  TYPE exch_tbl IS TABLE OF X.exch_row;

  FUNCTION GetExchangeRate RETURN X.exch_tbl PIPELINED;
END X;

패키지 본문

CREATE OR REPLACE PACKAGE BODY X IS
  FUNCTION GetExchangeRate RETURN X.exch_tbl
    PIPELINED AS
    exch_rt_usd NUMBER := 1.0; --todo
    rw exch_row;
  BEGIN

    FOR rw IN (SELECT c.currency_cd AS currency_cd, e.exch_rt AS exch_rt_eur, (e.exch_rt / exch_rt_usd) AS exch_rt_usd
                 FROM exch e, currency c
                WHERE c.currency_key = e.currency_key
                  ) LOOP
      PIPE ROW(rw);
    END LOOP;
  END;


  PROCEDURE DoIt IS
  BEGIN
    DECLARE
      CURSOR c0 IS
        SELECT i.DOC,
               i.doc_currency,
               i.net_value,
               i.net_value / rt.exch_rt_usd AS net_value_in_usd,
               i.net_value / rt.exch_rt_eur AS net_value_in_euro,
          FROM item i, (SELECT * FROM TABLE(X.GetExchangeRate())) rt
         WHERE i.doc_currency = rt.currency_cd;

      TYPE c0_type IS TABLE OF c0%ROWTYPE;

      items c0_type;
    BEGIN
      OPEN c0;

      LOOP
        FETCH c0 BULK COLLECT
          INTO items LIMIT batchsize;

        EXIT WHEN items.COUNT = 0;
        FORALL i IN items.FIRST .. items.LAST SAVE EXCEPTIONS
          INSERT INTO detail_items VALUES items (i);

      END LOOP;

      CLOSE c0;

      COMMIT;

    EXCEPTION
      WHEN OTHERS THEN
        RAISE;
    END;
  END;

END X;

검토 부탁드립니다.

패키지 사양에서 당신은 당신이 언급했지만 확실하지 않은 모든 것을 할 수 있습니다.INDEX BY BINARY_INTEGER;

패키지 본문:

선언으로 테이블을 초기화합니다.

exch_rt exch_tbl := exch_tbl();

레코드를 로컬 컬렉션에 추가하기 위해 시작-끝 블록에서 다음 작업을 수행할 수 있습니다.

exch_rt.extend;
                                one_row.exch_rt_usd := 2;
                                one_row.exch_rt_eur := 1;
                                one_row.currency_cd := 'dollar';
                                exch_rt(1) := one_row; -- 1 - number of row in the table - you can put a variable which will be incremented inside a loop 

이 테이블에서 데이터를 가져오려면 패키지 본문 내부에서 다음을 사용할 수 있습니다.

select exch_rt_usd, exch_rt_eur, currency_cd from table(exch_rt)

즐기세요!

추신: 답변이 늦어서 죄송합니다 :D

언급URL : https://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement