oracle 从 sqlplus 显示 pl/sql 中的动态 EXECUTE 输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1366426/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display Dynamic EXECUTE Output Within pl/sql From sqlplus
提问by Jerry
How to get the dynamic select results of EXECUTEwithin PL/SQL from Oracle sqlplus?
如何EXECUTE从 PL/SQL 中获取动态选择结果Oracle sqlplus？
I'm writing a simple sqlplus script to collect the sum of all NUMBERcolumns of a given table:
我正在编写一个简单的 sqlplus 脚本来收集NUMBER给定表的所有列的总和：
SET SERVEROUTPUT ON
DECLARE
CURSOR column_cur IS
SELECT column_name FROM ALL_TAB_COLS
WHERE owner = '&scheme_name' AND table_name = '&table_name'
AND data_type = 'NUMBER';
sql_query VARCHAR2(32767);
BEGIN
sql_query := 'select ';
FOR column_rec IN column_cur LOOP
sql_query := sql_query || 'SUM(' || column_rec.column_name ||
') "SUM(' || column_rec.column_name || ')", ';
END LOOP;
sql_query := substr(sql_query, 0, length(sql_query)-2) || -- remove trailing ', '
' from &scheme_name' || '.&table_name';
EXECUTE IMMEDIATE sql_query;
END;
/
The dynamically generated SQL statement, when executed, gives something like:
动态生成的 SQL 语句在执行时给出如下内容：
SUM(X) | SUM(Y) | SUM(Z) |
--------------------------
111 | 222 | 333 |
However, even with SET SERVEROUTPUT ON, running the sqlplus script gives only:
但是，即使使用SET SERVEROUTPUT ON，运行 sqlplus 脚本也仅提供：
PL/SQL procedure successfully completed.
采纳答案by Vincent Malgrat
you will need to retrieve the result from your SELECT in order to display it. You would use the synthax EXECUTE IMMEDIATE sql_query INTO var1, var2.. varn. However in your case the number of columns is unknown at compile time.
您需要从 SELECT 检索结果才能显示它。你会使用 synthax EXECUTE IMMEDIATE sql_query INTO var1, var2.. varn。但是，在您的情况下，列数在编译时未知。
There are a number of ways you could deal with this:
有多种方法可以解决这个问题：
- you could use DBMS_SQL and loop on the columns of the output.
- you could build a column with all the results with a readable format like CSV of XML
- 您可以使用 DBMS_SQL 并在输出列上循环。
- 您可以使用可读格式（如 XML 的 CSV）构建包含所有结果的列
I will demo 1:
我将演示 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, 0, 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, i, 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
