如何在SELECT FROM语句中使用表类型? [英] How to use a table type in a SELECT FROM statement?

问题描述

此问题与此

在包标题中:
声明以下行类型:

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

这篇关于如何在SELECT FROM语句中使用表类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！