如何将值动态传递给 IN 运算符? [英] How to pass values to IN operator dynamically?

问题描述

我想创建一个接受 IN 运算符的数组列表的过程.

I want to create a procedure which accepts an array list for IN operator.

例如:

Create or Replace Procedure Test (a Arraylist)
{
Select * from table1 where col1 IN (Arraylist)
}

如果使用两个参数调用过程，那么查询应该是

If the procedure is called using two parameters then the query should be

Select * from table1 where col1 IN (val1,val2)

如果过程是使用一个参数调用的，那么查询应该是

If the procedure is called using one parameter then the query should be

Select * from table1 where col1 IN (val1)

推荐答案

创建集合类型:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(100);
/

然后您可以将其传递给过程并使用 MEMBER OF 运算符(而不是 IN 运算符):

Then you can pass it to a procedure and use the MEMBER OF operator (rather than the IN operator):

CREATE PROCEDURE Test (
  in_list     IN  stringlist,
  out_results OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN out_results FOR
  SELECT *
  FROM   your_table
  WHERE  your_column MEMBER OF in_list;
END;
/

如果您从外部语言连接，那么您可以很容易地将数组传递给过程(Java 示例)或者您可以在 PL/SQL 中或从分隔列表创建列表.

If you are connecting from an external language then you can quite easily pass an array to the procedure (Java example) or you can create the list in PL/SQL or from a delimited list.

这篇关于如何将值动态传递给 IN 运算符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！