oracle execute 结果,oracle – 显示动态EXECUTE输出pl / sql从sqlplus

您将需要从SELECT中检索结果以显示它.您将使用synthax EXECUTE IMMEDIATE sql_query INTO var1,var2 .. varn.但是在你的情况下,编译时列的数量是未知的.

有很多方法可以处理这个：

>您可以使用DBMS_sql并循环输出的列.

>您可以使用可读格式(如CSV的CSV)构建具有所有结果的列

我会演示1：

sql> DEFINE scheme_name=SYS

sql> DEFINE table_name=ALL_OBJECTS

sql> DECLARE

2 sql_query VARCHAR2(32767);

3 l_cursor NUMBER := dbms_sql.open_cursor;

4 l_dummy NUMBER;

5 l_columns dbms_sql.desc_tab;

6 l_value NUMBER;

7 BEGIN

8 sql_query := 'select ';

9 FOR column_rec IN (SELECT column_name

10 FROM ALL_TAB_COLS

11 WHERE owner = '&scheme_name'

12 AND table_name = '&table_name'

13 AND data_type = 'NUMBER') LOOP

14 sql_query := sql_query || 'SUM(' || column_rec.column_name

15 || ') "SUM(' || column_rec.column_name || ')",';

16 END LOOP;

17 sql_query := substr(sql_query,length(sql_query) - 2)

18 || ' from &scheme_name' || '.&table_name';

19 dbms_sql.parse(l_cursor,sql_query,dbms_sql.NATIVE);

20 dbms_sql.describe_columns(l_cursor,l_dummy,l_columns);

21 FOR i IN 1..l_columns.count LOOP

22 dbms_sql.define_column(l_cursor,i,l_columns(i).col_type);

23 END LOOP;

24 l_dummy := dbms_sql.execute_and_fetch(l_cursor,TRUE);

25 FOR i IN 1..l_columns.count LOOP

26 dbms_sql.column_value(l_cursor,l_value);

27 dbms_output.put_line(l_columns(i).col_name ||' = '||l_value);

28 END LOOP;

29 END;

30 /

SUM(DATA_OBJECT_ID) = 260692975

SUM(OBJECT_ID) = 15242783244