游标中嵌套游标 oracle,oracle – PL / SQL中的嵌套游标

静态游标只能访问静态对象.换句话说,只有在编译时知道所有表和列时,静态游标才起作用.

如果您需要访问其名称仅在执行期间已知的表,则必须使用dynamic SQL.例如,您可以在您的情况下使用REF CURSOR：

DECLARE

var_table_name VARCHAR2(30);

var_dimension_key VARCHAR2(30);

cur_dimension_key SYS_REFCURSOR;

BEGIN

FOR cur_all_dim IN (SELECT table_name

FROM dba_tables

WHERE dba_tables.tablespace_name = 'USERS'

AND dba_tables.owner = 'DWH_CORE'

AND UPPER(dba_tables.table_name) LIKE ('%DIM%%')

AND UPPER(dba_tables.table_name) NOT LIKE ('%TEMP%')

AND UPPER(dba_tables.table_name) NOT LIKE ('%DEBUG%')

AND UPPER(dba_tables.table_name) NOT LIKE ('%LOG%'))

LOOP

OPEN cur_dimension_key

FOR 'SELECT dimention_key

FROM ' || cur_all_dim.table_name;

LOOP

FETCH cur_dimensions_key INTO var_dimension_key;

EXIT WHEN cur_dimensions_key%NOTFOUND;

dbms_output.put_line(cur_all_dim.table_name);

dbms_output.put_line(var_dimension_key);

END LOOP;

CLOSE cur_dimension_key;

END LOOP;

END;