for循环里面的游标oracle [英] for loop inside a cursor oracle
问题描述
我已创建了一个TYPE
I have created a TYPE
TYPE t_array IS TABLE OF VARCHAR2(15);
一个函数,它接受一个包含','作为分隔符的字符串,函数返回t_array分割字符串并返回值列表。
A function which takes a string which contains ',' as delimiter and the function returns t_array which is basically splitting the string and returning list of values.
FUNCTION split_string(id IN VARCHAR2)
...
...
....
RETURN t_array;
END split_string;
现在我的存储过程接受long字符串作为输入,调用函数来拆分字符串和循环
Now my stored procedure takes in the long string as input, calls the function to split the string and loops through the t_array and returns a CURSOR.
PROCEDURE p_get_xxx(p_id IN VARCHAR2,
p_cur_result OUT SYSREFCURSOR)
AS
l_array schema_name.t_array;
BEGIN
l_array := split_string(p_id);
OPEN p_cur_result FOR
FOR i IN l_array.first .. l_array.last
LOOP
SELECT * FROM ........
WHERE ID = l_array(i);
END LOOP;
END p_get_xxx;
我遇到以下行的编译错误:
I get a compilation error along the lines of :
在'('
Unexpected 'FOR' in the place of '('
的位置出现意外的FOR
Is there a better way of handling this scenario or am I missing some thing here?
推荐答案
游标总是在SELECT语句中打开,没有一种方式,据我所知,在 FOR
循环打开一个游标。
A cursor is always opened on a SELECT statement. There is no a way, as far as I am aware, to open a cursor on a FOR
loop.
我喜欢你真的想动态创建一个SELECT语句我建议如下:
It looks to me like you really want to create a SELECT statement dynamically. I suggest something like the following:
PROCEDURE p_get_xxx(p_id IN VARCHAR2, p_cur_result OUT SYSREFCURSOR)
AS
l_array schema_name.t_array;
strSelect_statement VARCHAR2(4000);
BEGIN
l_array := split_string(p_id);
-- Set up the basics of the SELECT statement
strSelect_statement := 'SELECT * FROM SOME_TABLE WHERE ID IN (';
FOR i IN l_array.FIRST..l_array.LAST LOOP
strSelect_statement := strSelect_statement ||
'''' || l_array(i) || ''',';
END LOOP;
-- Get rid of the unwanted trailing comma
strSelect_statement := SUBSTR(strSelect_statement, 1,
LENGTH(strSelect_statement)-1);
-- Add a right parentheses to close the IN list
strSelect_statement := strSelect_statement || ')';
-- Open the cursor
OPEN p_cur_result FOR strSelect_statement;
END p_get_xxx;
运气最好。
这篇关于for循环里面的游标oracle的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!