从EXECUTE IMMEDIATE PLSQL输出结果

Question

我有一个脚本，它查询ALL_TAB_COLUMNS进行一些聚合，然后通过执行

EXECUTE IMMEDIATE vSelect into v_output;

起初，iv_output是一个整数，vSelect进行了计数(*(并返回行计数。我想将vSelect更改为实际的

SELECT * FROM ALL_TAB_COLUMNS <MY_WHERE_CLAUSE> 

并使用DBMS_output.PUT_LINE输出结果。我将设置它，以便在输出后尽快写入日志文件，但尽量先输出。Oracle 11G

Answer 1

一种方法是使用all_tab_columns%rowtype记录变量的嵌套表，然后循环显示。

SET SERVEROUTPUT ON
DECLARE
TYPE tab_all_tab_rec IS
TABLE OF all_tab_columns%rowtype;
trec             tab_all_tab_rec;
v_where_clause   CLOB := 'WHERE OWNER = ''HR''';
BEGIN
EXECUTE IMMEDIATE 'SELECT * 
FROM all_tab_columns ' || v_where_clause BULK COLLECT
INTO trec;
FOR i IN trec.first..trec.last LOOP
dbms_output.put_line(trec(i).owner || ',' || trec(i).table_name || ',' || trec(i).column_name
); --Other columns
END LOOP;
END;
/

O/p

HR,COUNTRIES,COUNTRY_NAME
HR,COUNTRIES,COUNTRY_ID
HR,COUNTRIES,REGION_ID
HR,DEPARTMENTS,LOCATION_ID
HR,DEPARTMENTS,MANAGER_ID
HR,DEPARTMENTS,DEPARTMENT_NAME
HR,DEPARTMENTS,DEPARTMENT_ID
HR,EMPLOYEES,DEPARTMENT_ID
HR,EMPLOYEES,MANAGER_ID
HR,EMPLOYEES,COMMISSION_PCT
HR,EMPLOYEES,SALARY
HR,EMPLOYEES,JOB_ID
HR,EMPLOYEES,HIRE_DATE
HR,EMPLOYEES,PHONE_NUMBER
HR,EMPLOYEES,EMAIL
HR,EMPLOYEES,LAST_NAME
HR,EMPLOYEES,FIRST_NAME
HR,EMPLOYEES,EMPLOYEE_ID
..
..
PL/SQL procedure successfully completed.

Answer 2

这是一个类似于@Kaushik的方法，但使用游标循环而不是集合：

set serveroutput on
declare
l_cursor sys_refcursor;
l_row all_tab_columns%rowtype;
l_where_clause varchar2(50) := q'[where owner = 'HR']';
begin
open l_cursor for 'SELECT * FROM all_tab_columns ' || l_where_clause;
loop
fetch l_cursor into l_row;
exit when l_cursor%notfound;
dbms_output.put_line(l_row.owner ||','|| l_row.table_name ||','|| l_row.column_name);
end loop;
end;
/

它还得到：

HR,REGIONS,REGION_NAME
HR,REGIONS,REGION_ID
HR,LOCATIONS,COUNTRY_ID
HR,LOCATIONS,STATE_PROVINCE
HR,LOCATIONS,CITY
...

尽管这是在进行单独的单行提取，但在最近的版本中，Oracle优化为在后台使用批量操作，因此性能应该类似于显式bulk collect，而不需要集合类型。

这假设您在查询中实际使用select *，因此有一个表可用于%rowtype。如果查询更具选择性(这通常是一件好事(或组合了多个表，则需要声明匹配的记录类型。