oracle oracle过程中使用游标的动态查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15184015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
dynamic query in oracle procedure using cursor
提问by Matiar Rahman
I am using a stored procedure to select records using a cursor. This procedure gets record id as input.
我正在使用存储过程使用游标选择记录。此过程获取记录 ID 作为输入。
Here is the code:
这是代码：
create or replace
procedure GET_ITEM_DETAILS_ALL
(
cur_out out sys_refcursor,
PSTRING VARCHAR2 DEFAULT NULL
)
is
query_string VARCHAR2(1000);
Begin
query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (:sl) order by IT.SL_NO';
OPEN cur_out FOR query_string USING PSTRING;
End;
And for calling / testing the procedure I use the below code:
对于调用/测试程序，我使用以下代码：
VAR R REFCURSOR
EXEC GET_ITEM_DETAILS_ALL (:R, '4')
PRINT R
The problem is when I pass a single integer number such as '4' or '2', the procedure returns data, but when I pass '1,2' or '3,4', it shows error.
问题是当我传递一个整数（例如“4”或“2”）时，该过程返回数据，但当我传递“1,2”或“3,4”时，它显示错误。
采纳答案by APC
The problem is PSTRING is a single variable not an array. So your statement actually equates to
问题是 PSTRING 是单个变量而不是数组。所以你的陈述实际上相当于
.... where IT.SL_NO = PSTRING
That's why it works when you pass 4and fails where you pass 1,2.
这就是为什么它在您通过时有效4而在您通过时失败的原因1,2。
You get no value in using dynamic SQL the way you do (we can open ref cursor without using strings). However, taking advantage of dynamic SQL is one way of solving your problem:
以您的方式使用动态 SQL 没有任何价值（我们可以在不使用字符串的情况下打开引用游标）。但是，利用动态 SQL 是解决问题的一种方法：
query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('
||PSTRING||
') order by IT.SL_NO';
OPEN cur_out FOR query_string;
Alternatively you can use a string tokenizer to turn the string into, well, into tokens. Unfortunately Oracle doesn't have a standard built-in but there are various workarounds for different versions of the database. Adrian Billington has a good round-up on his site. Using one of those approaches allows you to ditch the dynamic SQL:
或者，您可以使用字符串标记器将字符串转换为标记。不幸的是，Oracle 没有内置的标准，但是对于不同版本的数据库有各种解决方法。Adrian Billington在他的网站上有一个很好的综述。使用其中一种方法可以让您放弃动态 SQL：
OPEN cur_out FOR select IT.SL_NO from ITEM_DETAILS IT
where IT.SL_NO in ( select * from table ( your_string_tokenizer( PSTRING ) ) )
order by IT.SL_NO;
回答by Rajesh D
OPEN cur_out FOR Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (SELECT REGEXP_SUBSTR(sl,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(sl, '[^,]+', 1, LEVEL) IS NOT NULL ) order by IT.SL_NO
回答by Egor Skriptunoff
You can't pass a list of values to a query using one bind variable.
After passing a string with comma-separated list your query becomesSelect IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('1,2') order by IT.SL_NO
which is incorrect.
You should manually convert input string to a list of values inside your query.
您不能使用一个绑定变量将值列表传递给查询。
传递带有逗号分隔列表的字符串后，您的查询变得Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('1,2') order by IT.SL_NO
不正确。
您应该手动将输入字符串转换为查询中的值列表。
回答by Matiar Rahman
The solution is given below:
解决方法如下：
create or replace procedure GETITEM_DETAILS_ALL
(
cur_out out sys_refcursor,
PSTRING VARCHAR2 DEFAULT NULL
)
is
query_string VARCHAR2(1000);
Begin
query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (' || PSTRING || ') order by IT.SL_NO';
OPEN cur_out FOR query_string;
End;
