oracle – 显示动态EXECUTE输出pl / sql从sqlplus

发布时间：2020-12-12 15:13:50 所属栏目：百科来源：网络整理

导读：如何从Oracle sqlplus获取PL / SQL中EXECUTE的动态选择结果？我正在编写一个简单的sqlplus脚本来收集给定表的所有NUMBER列的总和： SET SERVEROUTPUT ONDECLARE CURSOR column_cur IS SELECT column_name FROM ALL_TAB_COLS WHERE owner = 'scheme_name' AN

如何从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;
/

动态生成的SQL语句在执行时会产生如下结果：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!