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

Question

我有一个表变量传递给一个过程。我想在下面的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; 

Answer 1

是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"

所以CatalogNos不是SQL类型，即它是在一个封装规格或机构宣称PL/SQL类型。错误消息非常明确：我们不能在SQL语句中使用PL/SQL类型。就是那样子。

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

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

Type created. 

SQL> 

如果您真的不想创建自己的类型（为什么不？），您可以使用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; 
/

Answer 2

使用||对于与可变

像拼接查询：

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; 

Answer 3

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; 

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

---

基础上的评论，看来我需要再次重申：为了引用SQL用户定义的数据类型，你必须定义包之外的类型作为一个单独的对象。

---

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