本文介绍了如何将值动态传递给 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.
这篇关于如何将值动态传递给 IN 运算符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！