如何将值动态传递给 IN 运算符?
我想创建一个接受 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.
相关文章