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

2021-12-19 00:00:00 select row oracle

这个问题或多或少与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


如何在 Oracle 中执行此操作?


How to do this in Oracle ?

注意事项

实际上我正在寻找 MSSQL 中的表变量"解决方案:

Actually I'm looking for the 'Table Variable' solution in MSSQL:

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

并在我的 StoredProcedure 中使用这个表变量.

And use this Table Variable inside my StoredProcedure.

推荐答案

在 SQL 中，您只能使用在模式级别(而不是在包或过程级别)定义的表类型，不能使用按表索引(关联数组)在模式级别定义.所以 - 你必须像这样定义嵌套表

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

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 操作符，例如:

And then you can use it in SQL with TABLE operator, for example:

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

相关文章