游标For循环与动态SQL语句 [英] Cursor For Loop with dynamic SQL-Statement

问题描述

有一种方法可以用动态SQL语句执行Cursor For Loop？

Is there a way to perform a Cursor For Loop with an dynamic SQL-statement?

如果我不想声明一个记录，我可以做（仅当我声明游标..）：

If I don't want to declare a record I can do something like this (only if I declared the cursor..):

For I in cuSelect Loop
  dbms_output.put_line(I.NAME);
End Loop;

我可以打开一个动态SQL语句的游标：

And I can open a cursor for a dynamic SQL-statement:

Open cuSelect For 'Select * From TAB_X';
Fetch ceSelect Into recSelect;
Close cuSelect;

但是，我必须先声明记录。

But to do that I have to first declare the Record.

现在我的问题是，我必须打开一个非常大和复杂的动态SQL语句的Cursor。记录的结构未知。有没有办法打开一个变量游标并用未声明记录迭代它？

Now my problem is that I have to open the Cursor for a very big and complicated dynamic SQL-statement. The structure of the record is unknown. Is there a way to open a variable cursor and iterate through it with an "undeclared" record?

感谢您的帮助。

推荐答案

我想你可以做你想要的与DBMS_SQL包。您还可以查看以下内容：

I think you can do what you want with DBMS_SQL package. You can also check these:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i1025449

例如：

declare
  TYPE curtype IS REF CURSOR;
  src_cur      curtype;
  curid        NUMBER;
  namevar  VARCHAR2(50);
  numvar   NUMBER;
  datevar  DATE;
  desctab  DBMS_SQL.DESC_TAB;
  colcnt   NUMBER;
  dsql varchar2(1000) := 'select card_no from card_table where rownum = 1';
begin
  OPEN src_cur FOR dsql;

  -- Switch from native dynamic SQL to DBMS_SQL package.
  curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);

  -- Define columns.
  FOR i IN 1 .. colcnt LOOP
    IF desctab(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
    ELSIF desctab(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
    END IF;
  END LOOP;

  -- Fetch rows with DBMS_SQL package.
  WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    FOR i IN 1 .. colcnt LOOP
      IF (desctab(i).col_type = 1) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
        dbms_output.put_line(namevar);
      ELSIF (desctab(i).col_type = 2) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
        dbms_output.put_line(numvar);
      ELSIF (desctab(i).col_type = 12) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
        dbms_output.put_line(datevar);
      END IF;
    END LOOP;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(curid);

end;

这篇关于游标For循环与动态SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！