如何动态地将值传递给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屋！