如何在SELECT FROM语句中使用表类型？

Question

这个问题基本上与这个问题相同。

在包头中:
声明了以下行类型：

  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)
)

然后在我的存储过程中使用这个表变量。

- Stef Heyenrath

---

正如其他人所说，您无法使用包中声明的类型来完成此操作。不清楚您这样做的原因，而不是使用真实的表来保存汇率，或者您想要对所选数据进行什么操作。这是在游标中吗？您是否真的正在寻找特定货币的数据？您是否根据汇率来操作项目值？更多上下文可能会有助于激发替代方法。 - Alex Poole

Answer 1

在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;

然后你可以使用 TABLE 操作符在 SQL 中使用它，例如：

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;
/

- Marcin Wroblewski

---

在函数中是否有一种使用表类型的方法？ - sabertooth1990

是的，您可以在函数中完全按照此答案中匿名PL/SQL块中的方式使用表类型。 - Marcin Wroblewski

实际上，您可以在包规范/主体中使用几乎所有描述的对象。唯一的限制是，您不能将本地类型创建为对象。只允许将其作为记录用于包。 - t v

Answer 2

在Oracle 12C之前，您无法从PL / SQL定义的表中进行选择，只能从基于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 表中进行选择。

- Tony Andrews

---

如果我没有使用现有表的所有列，我是否仍然需要声明类型？ - zygimantus

@zygimantus 抱歉，我不理解你的问题，请你能详细解释一下吗？ - Tony Andrews

例如，我能否声明这种类型 TYPE my_type IS TABLE OF my_table%ROWTYPE 而不是使用您描述的自定义列？ - zygimantus

在PL/SQL中，是可以的 - 如果使用12C，还可以从该类型的变量中进行选择。 - Tony Andrews

好的。谢谢信息。 - zygimantus

Answer 3

在包中无法使用单个查询完成此操作 - 你不能混合使用SQL和PL/SQL类型，需要像Tony、Marcin和Thio所说的那样在SQL层中定义类型。

如果你真的想要在本地完成这个操作，并且可以按VARCHAR而不是BINARY_INTEGER对表类型进行索引，那么你可以这样做:

-- 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_eur，USD同理。下面是一个匿名块的测试：

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英镑和25.00澳元的物品时，您将获得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;

- Alex Poole

---

感谢提供详细信息，请查看我在问题中更新的“笔记”。 - Stef Heyenrath

@Stef：我认为这是最接近的等价物，但并不完全相同。我仍然不确定为什么您不会使用常规表格，但如果您想要不同会话的不同值，您也可以查看全局临时表。我认为管道函数在类型方面也会有相同的问题。 - Alex Poole

这个早期的问题上有一些链接（https://dev59.com/yHRB5IYBdhLWcg3wSVe1），但并没有真正解决你想做的事情。 - Alex Poole

Answer 4

感谢在此问题上提供的所有帮助。我将在此发布我的解决方案： 包头

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;

请审核。

- Stef Heyenrath

---

我不确定这是一个解决方案，因为它无法编译：在 AS net_value_in_euro 后面有一个额外的逗号，而且 batchsize 没有定义。而且 doit 没有在包头中声明，所以无法运行。我认为这应该是对问题的编辑，而不是答案。但是为什么你要在 PL/SQL 中做这个呢？这可以在单个 update 语句中完成。 - Alex Poole

更新了我的答案，加入了一个insert语句，用于创建与doit过程中相同的detail_items记录。 - Alex Poole

@Alex，它无法编译，因为它只是一个如何创建它的提示。也许这很令人困惑。我之所以这样做有以下原因：1）没有视图：我不想用那些小的辅助视图“污染”数据库。2）我想使用PL/SQL来记录批处理错误和处理大量数据。 - Stef Heyenrath

好的，如果你坚持使用PL/SQL，那么这个方法可以实现。你仍然可以使用我插入版本中的选择部分（从with rt as开始）作为游标，避免使用PL/SQL表和函数，因为它们似乎增加了不必要的复杂性。但是我已经偏离了原始问题的范畴。 - Alex Poole

Answer 5

在包规范中，你可以做到所有你提到的，但不确定关于 INDEX BY BINARY_INTEGER;。

在包体中：

在声明中初始化表：

exch_rt exch_tbl := exch_tbl();

为了向本地集合添加记录，您可以在begin-end块中执行以下操作：

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)

享受吧！

P.S. 抱歉回复晚了 :D

- t v