将字符串数组传递给过程，并在带有IN的WHERE子句中使用它 [英] Passing an array of strings to a procedure and using it in the WHERE clause with IN

问题描述

我想将字符串列表传递给过程，并在select语句的WHERE子句中使用它，但是我不知道该怎么做.

I want to pass a list of strings to a procedure and use it in the WHERE clause of a select statement, but I can't figure out how to do this.

我声明了以下嵌套表:

TYPE t_strarray IS TABLE OF VARCHAR2(30);

过程如下:

PROCEDURE getstuff(p_list IN t_strarray, io_cursor OUT t_cursor)
   IS    
   BEGIN
        OPEN io_cursor FOR
            SELECT * FROM mytable 
            WHERE mytable.field1 in (select * from table(p_list)); 
   END;

这怎么办?

推荐答案

您的数组必须是直接在SQL中创建的SQL对象类型，而不是在包中声明的PLSQL类型:

Your array needs to be a SQL object type, created directly in SQL, not a PLSQL type declared in a package:

SQL> CREATE OR REPLACE TYPE t_strarray IS TABLE OF VARCHAR2(30);
  2  /
Type created.

SQL> CREATE TABLE mytable (field1 VARCHAR2(30));
Table created.

SQL> INSERT INTO mytable VALUES ('A');
1 row created.

SQL> INSERT INTO mytable VALUES ('D');
1 row created.

SQL> CREATE OR REPLACE PROCEDURE getstuff(p_list IN t_strarray,
  2                                       io_cursor OUT SYS_REFCURSOR) IS
  3  BEGIN
  4     OPEN io_cursor FOR
  5        SELECT *
  6          FROM mytable
  7         WHERE mytable.field1 IN (SELECT COLUMN_VALUE FROM TABLE(p_list));
  8  END;
  9  /
Procedure created.

SQL> VARIABLE cc REFCURSOR;
SQL> EXEC getstuff (t_strarray('A', 'B', 'C'), :cc);    
PL/SQL procedure successfully completed.

SQL> print cc

FIELD1
------------------------------
A

这篇关于将字符串数组传递给过程，并在带有IN的WHERE子句中使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！