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屋！