oracle cursor 动态sql 语句,oracle – 带有动态SQL语句的Cursor For Loop

我认为你可以用DBMS_SQL包做你想做的事.

你也可以看看这些：

例如：

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;