oracle 如何在 SELECT FROM 语句中使用表类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5165580/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a table type in a SELECT FROM statement?
提问by Stef Heyenrath
This question is more or less the same as this
这个问题或多或少与此相同
In the package header :
Declared the following row type:
在包头中：
声明了以下行类型：
TYPE exch_row IS RECORD(
currency_cd VARCHAR2(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER);
And this table type:
这个表类型：
TYPE exch_tbl IS TABLE OF exch_row INDEX BY BINARY_INTEGER;
Added a variable:
添加了一个变量：
exch_rt exch_tbl;
In the package body:
Fill this table variable with some data.
在包体中：
用一些数据填充这个表变量。
In a procedure in the package body:
I want to use the following statement:
在包体中的一个过程中：
我想使用以下语句：
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
How to do this in Oracle ?
如何在 Oracle 中执行此操作？
Notes
笔记
Actually I'm looking for the 'Table Variable' solution in MSSQL:
实际上，我正在 MSSQL 中寻找“表变量”解决方案：
DECLARE @exch_tbl TABLE
(
currency_cd VARCHAR(9),
exch_rt_eur NUMBER,
exch_rt_usd NUMBER)
)
And use this Table Variable inside my StoredProcedure.
并在我的 StoredProcedure 中使用这个表变量。
回答by Marcin Wroblewski
In SQL you may only use table type which is defined at schema level (not at package or procedure level), and index-by table (associative array) cannot be defined at schema level. So - you have to define nested table like this
在 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;
And then you can use it in SQL with TABLE operator, for example:
然后您可以在 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;
/
回答by Tony Andrews
Prior to Oracle 12C you cannot select from PL/SQL-defined tables, only from tables based on SQL types like this:
在 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;
In Oracle 12C it is now possible to select from PL/SQL tables that are defined in a package spec.
在 Oracle 12C 中，现在可以从包规范中定义的 PL/SQL 表中进行选择。
回答by Alex Poole
You can't do it in a single query inside the package - you can't mix the SQL and PL/SQL types, and would need to define the types in the SQL layer as Tony, Marcin and Thio have said.
您不能在包内的单个查询中执行此操作 - 您不能混合 SQL 和 PL/SQL 类型，并且需要在 SQL 层中定义类型，如 Tony、Marcin 和 Thio 所说。
If you really want this done locally, and you can index the table type by VARCHAR instead of BINARY_INTEGER, you can do something like this:
如果您真的希望在本地完成此操作，并且您可以通过 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;
/
So inside your loop, wherever you would have expected to use r0.exch_rt_euryou instead use exch_rt(r0.currency).exch_rt_eur, and the same for USD. Testing from an anonymous block:
因此，在您的循环中，您希望使用的任何地方都r0.exch_rt_eur使用exch_rt(r0.currency).exch_rt_eur，而美元也是如此。从匿名块测试：
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 **
Based on the answer Stef posted, this doesn't need to be in a package at all; the same results could be achieved with an insertstatement. Assuming EXCHholds exchange rates of other currencies against the Euro, including USD with currency_key=1:
根据 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;
With items valued at 19.99 GBP and 25.00 AUD, you get detail_items:
对于价值 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
If you want the currency stuff to be more re-usable you could create a view:
如果您希望货币内容更可重用，您可以创建一个视图：
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;
And then insert using values from that:
然后使用其中的值插入：
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;
回答by Stef Heyenrath
Thanks for all help at this issue. I'll post here my solution:
感谢您在此问题上的所有帮助。我会在这里发布我的解决方案：
Package Header
包头
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;
Package Body
包体
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;
Please review.
请查阅。
回答by t v
In package specsyou can do all you mentioned but not sure about INDEX BY BINARY_INTEGER;
在包装规格中，您可以执行您提到的所有操作，但不确定INDEX BY BINARY_INTEGER;
In package body:
在包体中：
initialize the table in declarations:
在声明中初始化表：
exch_rt exch_tbl := exch_tbl();
in order to add record to the local collection, in begin - end block you can do:
为了将记录添加到本地集合，在开始 - 结束块中，您可以执行以下操作：
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
in order to get data from this table , inside package body you can use:
为了从此表中获取数据，您可以在包体内使用：
select exch_rt_usd, exch_rt_eur, currency_cd from table(exch_rt)
enjoy!
请享用！
P.S. sorry for a late answer :D
PS抱歉回复晚了：D
