oracle PL/SQL 在 Where 子句中使用表变量

PL/SQL Use Table Variable in Where Clause

提问by Adam

I have a table variable that is being passed into a procedure. I would like to use the values in a where clause as below, how do I do this. The first line below is declared in the package definition. The procedure below is in the package body.

我有一个正在传递给过程的表变量。我想使用如下 where 子句中的值，我该怎么做。下面的第一行在包定义中声明。下面的过程在包体中。

type CatalogNos is table of VARCHAR2(100);
PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                    Parts_Char_Cursor out sys_refcursor) AS
BEGIN
    OPEN Parts_Char_Cursor FOR
    SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
    WHERE CATALOG_NO IN (select values from v_catalog_nos_);
END GET_PART_CHARACTERISTICS;

回答by APC

Is CatalogNosa SQL type (i.e. not declared in a package spec)? If so:

是否CatalogNos为 SQL 类型（即未在包规范中声明）？如果是这样的话：

PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                    Parts_Char_Cursor out sys_refcursor) 
AS
BEGIN
    OPEN Parts_Char_Cursor FOR
    SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
    WHERE CATALOG_NO IN (select * from table(v_catalog_nos_));
END GET_PART_CHARACTERISTICS;

---

---

"This SQL gives an error: PLS-00642: local collection types not allowed in SQL statements"

“此 SQL 给出错误：PLS-00642：SQL 语句中不允许使用本地集合类型”

So CatalogNosis not a SQL type i.e. it is a PL/SQL type declared in a package spec or body. The error message is quite clear: we cannot use PL/SQL types in SQL statements. That's just the way it is.

所以CatalogNos不是 SQL 类型，即它是在包规范或主体中声明的 PL/SQL 类型。错误信息很清楚：我们不能在 SQL 语句中使用 PL/SQL 类型。就是那样子。

The simplest solution is to use a SQL type.

最简单的解决方案是使用 SQL 类型。

SQL> create or replace type CatalogNos is table of VARCHAR2(100);    
  2  /

Type created.

SQL> 

If you really don't want to create your own type (why not?) you can use one of the Oracle built-ins. Like this:

如果您真的不想创建自己的类型（为什么不呢？），您可以使用 Oracle 内置程序之一。像这样：

create or replace PROCEDURE GET_PART_CHARACTERISTICS
      (v_catalog_nos_  IN sys.dbms_debug_vc2coll,
       Parts_Char_Cursor out sys_refcursor)
AS
BEGIN
    OPEN Parts_Char_Cursor FOR
    SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
    WHERE CATALOG_NO IN (select * from table(v_catalog_nos_));
END GET_PART_CHARACTERISTICS;
/

回答by Allan

create type CatalogNos is table of VARCHAR2(100);

CREATE OR REPLACE PACKAGE your_package as
   PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                       Parts_Char_Cursor out sys_refcursor);
END your_package;

CREATE OR REPLACE PACKAGE BODY your_package as
   PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                       Parts_Char_Cursor out sys_refcursor) AS
   BEGIN
       OPEN Parts_Char_Cursor FOR
       SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
       WHERE CATALOG_NO IN (select column_value from table(v_catalog_nos_));
   END GET_PART_CHARACTERISTICS;
END your_package;

The type definition must be a database object, not part of a package, in order to be used in SQL. Once that's true, you can use the tablefunction to reference a variable of that type in the fromclause.

类型定义必须是数据库对象，而不是包的一部分，才能在 SQL 中使用。一旦这是真的，您就可以使用该table函数在from子句中引用该类型的变量。

---

---

Based on the comment, it seems that I need to re-iterate: in order to reference a user-defined datatype in SQL, you must define the type as a separate object outside of the package.

根据评论，我似乎需要重新迭代：为了在 SQL 中引用用户定义的数据类型，您必须将该类型定义为包外的单独对象。

---

---

For a user-defined table that contains a single unnamed field, you can use either select *or select column_value.

对于包含单个未命名字段的用户定义表，您可以使用select *或select column_value。

回答by Emmanuel N

Use || for to concatenate your query with a variable

使用 || 用于将您的查询与变量连接起来

something like:

就像是：

type CatalogNos is table of VARCHAR2(100);
PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                Parts_Char_Cursor out sys_refcursor) AS
BEGIN
   OPEN Parts_Char_Cursor FOR
   'SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
   WHERE CATALOG_NO IN (select values from' || v_catalog_nos_ || ')';
END GET_PART_CHARACTERISTICS;