如何从Oracle sqlplus获取PL / sql中EXECUTE的动态选择结果?
我正在编写一个简单的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,length(sql_query)-2) || -- remove trailing ',' ' from &scheme_name' || '.&table_name'; EXECUTE IMMEDIATE sql_query; END; /
SUM(X) | SUM(Y) | SUM(Z) | -------------------------- 111 | 222 | 333 |
但是,即使使用SET SERVEROUTPUT ON,运行sqlplus脚本仅提供:
PL/sql procedure successfully completed.
您将需要从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