PL/SQL 使用表变量作为查询表

Question

在PL/SQL中，可以将变量用作查询表的查询条件吗？

尝试过：

declare
   TYPE t_name IS TABLE OF varchar(50) INDEX BY PLS_INTEGER;
   v_names t_name;
begin
select name bulk collect into v_names from my_table;

select name from v_names where name = 'Max';
end;

- user5507535

Answer 1

是的...但你的做法有两个问题：

• 首先，你没有一个集合（你所称的表变量），因为你使用了INDEX BY PLS_INTEGER，所以你拥有的是一个关联数组。
• 其次，在 SQL 查询中只能使用在 SQL 范围内声明数据类型的集合（而你的是在 PL/SQL 中声明的）。

所以，首先你需要创建该类型：

CREATE TYPE t_name IS TABLE OF VARCHAR2(50);

然后您可以运行PL/SQL块：

DECLARE
  v_names t_name;
  v_name  VARCHAR2(50);
BEGIN
  SELECT name
  BULK COLLECT INTO v_names
  FROM my_table;

  SELECT COLUMN_VALUE
  INTO   v_name
  FROM   TABLE(v_names)
  WHERE  COLUMN_VALUE = 'Max';
  
  DBMS_OUTPUT.PUT_LINE( v_name );
END;
/

（注意：第二个查询中的表集合表达式使用伪列COLUMN_VALUE，而不是来自表的任何特定标识符。）

db<>fiddle 在这里

- MT0

---

谢谢！我不明白为什么他们把这个弄得这么复杂，我不想在数据库里到处乱搞类型 :/ 我只是想让它工作！如果有人删除了我的类型，系统就会崩溃，该死的。 - user5507535

如果您想过滤集合以查找单个值，则可以在初始的 SELECT 中执行此操作，以仅获取单个值，或者您可以使用 PL/SQL 中的 FOR 循环来迭代集合，然后您不需要尝试将其放回到 SQL 范围内。 - MT0

@user5507535。这并不复杂，只是对你来说不熟悉而已。至于破坏数据库的问题，那并不是问题。在20年的时间里，我从未遇到过类型被删除的情况。毕竟，在包STANDARD中，varchar2、integer和date都被定义为TYPE（大多数是子类型）。因此，这样做是一致的。只是有所不同而已。 - Belayer

@Belayer 我理解你的想法，但这个设计决策没有意义，就像让你创建一个全局变量一样，而实际上你并不需要。我只会在那个特定的文件和范围中使用该类型，所以为什么需要在整个系统中共享呢？在PL/SQL和SQL中有很多类似这样的东西，我认为这是糟糕的设计，对程序员来说也不符合人体工程学。 - user5507535

如果你考虑到它们是两种不同的语言，这就很有道理了。这就像编写一个Java程序来包装一些低级C代码，然后问为什么当我在Java中声明数据类型时，不能在C代码中使用它？因为它们是两种不同的语言，而且包装是从Java到C而不是反过来。对于PL/SQL和SQL也是如此；你可以在PL/SQL中使用SQL类型，但你不能在SQL中使用PL/SQL类型。 - MT0

如果有人删除我的类型，系统就会崩溃。如果有人删除CUSTOMERS表，系统也会崩溃。你希望在发布前进行充分的测试并提出问题。 - William Robertson