pl/sql - 集合可以循环遍历列名吗？

以下代码的输出是：

         |LAT|MISC|SID|NO
MIN_LENGTH|1|2|1|1
MAX_LENGTH|6|6|4|2

输出正如我所期望的那样，但是无论如何都可以使用索引（即j）循环遍历列而不是执行RESULTS（I）。最长长度，结果（一）。最长长度ETC ？值得关注的是，当向“R_RESULT_REC”记录添加额外的列时，需要另一个循环。

set serveroutput on
DECLARE     
  TYPE R_RESULT_REC IS RECORD
    (COL_NAME        VARCHAR2(100),
      MIN_LENGTH         NUMBER,
      MAX_LENGTH         NUMBER
      );       
  TYPE tr_RESULT IS TABLE OF R_RESULT_REC;
  RESULTS   TR_RESULT := TR_RESULT();
  v_counter NUMBER := 1;
BEGIN
  FOR J IN (SELECT DISTINCT COLUMN_NAME FROM ALL_TAB_COLUMNS 
            WHERE OWNER = 'SYSTEM'
            and TABLE_NAME = 'SPECCHAR')
  LOOP
      RESULTS.EXTEND;
      RESULTS(V_COUNTER).COL_NAME := J.COLUMN_NAME;
      EXECUTE IMMEDIATE 'SELECT MIN(LENGTH('||J.COLUMN_NAME||')),
      MAX(LENGTH('||J.COLUMN_NAME||'))
      FROM '||'SYSTEM'||'.'||'SPECCHAR' INTO 
      RESULTS(V_COUNTER).MIN_LENGTH,
      RESULTS(V_COUNTER).MAX_LENGTH; 
      V_COUNTER := V_COUNTER + 1;
  END LOOP;
     FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
       IF I = RESULTS.LAST THEN
         DBMS_OUTPUT.PUT_LINE(RESULTS(I).COL_NAME);
       ELSIF I = RESULTS.FIRST THEN
         DBMS_OUTPUT.PUT('         |'||RESULTS(I).COL_NAME||'|');
        ELSE
         DBMS_OUTPUT.PUT(RESULTS(I).COL_NAME||'|');
       END IF ;
    END LOOP;
    FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
       IF I = RESULTS.LAST THEN
         DBMS_OUTPUT.PUT_LINE(RESULTS(I).MIN_LENGTH);
        ELSIF I = RESULTS.FIRST THEN
         DBMS_OUTPUT.PUT('MIN_LENGTH|'||RESULTS(I).MIN_LENGTH||'|');
        ELSE
         DBMS_OUTPUT.PUT(RESULTS(I).MIN_LENGTH||'|');
       END IF ;
    END LOOP;
     FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
       IF I = RESULTS.LAST THEN
         DBMS_OUTPUT.PUT_LINE(RESULTS(I).MAX_LENGTH);
        ELSIF I = RESULTS.FIRST THEN
         DBMS_OUTPUT.PUT('MAX_LENGTH|'||RESULTS(I).MAX_LENGTH||'|');
        ELSE
         DBMS_OUTPUT.PUT(RESULTS(I).MAX_LENGTH||'|');
       END IF ;
    END LOOP;
end;

它使用了 DBMS_SQL，所以读起来相当困难。我看到使用它的主要原因是我可以获得 SQL 语句的列式描述以及基于缓冲区而不是基于对象的获取。

它不是在处理过程中调用 DBMS_OUTPUT，而是构建一个用于输出的记录表，为了简单起见，使用关联数组。

可以进一步细化为每个函数应用一个数组或可解析的函数列表，但这似乎超出了当前的要求。如果添加新的聚合函数，则代码的性质将需要编辑。

调用概述（2c + a + s）：

• 3 loops;
  • 对列列表 (c) 进行 2 次循环，
  • 1 次循环分析函数 (a) 的数量。
• 1 个针对表数据的 SQL 语句。

OP的调用概述（c*s + a + 1）：

• 1个循环，针对表数据执行一条sql语句每列 (c*s)
• a+1 循环，其中 a 是解析函数的数量

测试数据：

  1  select  min(length(GP_ID)),  max(length(GP_ID)),
  2          min(length(GGP_ID)),  max(length(GGP_ID)),
  3          min(length(OBJECT_NAME)),  max(length(OBJECT_NAME))
  4*   from AMUSCH.GP
SQL> /

MIN(LENGTH(GP_ID)) MAX(LENGTH(GP_ID)) MIN(LENGTH(GGP_ID)) 
MAX(LENGTH(GGP_ID)) MIN(LENGTH(OBJECT_NAME)) MAX(LENGTH(OBJECT_NAME))
                  1                  7                   1                              
                  4                        9                       41

Code:

declare
  p_owner         varchar2(30);
  p_table_name    varchar2(30);

  TYPE OUTPUT_TAB_TYPE IS TABLE OF VARCHAR2(32767) index by binary_integer;
  OUTPUT_TAB OUTPUT_TAB_TYPE;

  l_columns_tab   dbms_sql.desc_tab;
  l_columns_cur   integer;
  l_columns_sql   varchar2(32767);
  l_columns_cnt   number;

  l_minmax_sql    varchar2(32767);
  l_minmax_cur    integer;
  l_minmax_tab    dbms_sql.desc_tab;
  l_minmax_cnt    number;

  l_fetch_ok      number;
  l_fetch_value   number;
begin

  p_owner := 'AMUSCH';
  p_table_name := 'GP';

  output_tab(1) := lpad(' ', 20, ' ');
  output_tab(2) := lpad('MIN_LENGTH', 20, ' ');
  output_tab(3) := lpad('MAX_LENGTH', 20, ' ');

  l_columns_sql := 'select * from ' || p_owner || '.' || p_table_name || 
     ' where 1 = 0';
  l_columns_cur := dbms_sql.open_cursor;
  dbms_sql.parse (l_columns_cur, l_columns_sql, dbms_sql.native);
  dbms_sql.describe_columns (l_columns_cur, l_columns_cnt, l_columns_tab);

  -- build the min/max sql statement
  l_minmax_sql := 'select ' ;
  for i in 1..l_columns_cnt
  loop
    l_minmax_sql := l_minmax_sql || 
          ' min(length(' || l_columns_tab(i).col_name || ')), ';
    l_minmax_sql := l_minmax_sql || 
          ' max(length(' || l_columns_tab(i).col_name || ')), ';
  end loop;
  l_minmax_sql := substr(l_minmax_sql, 1, 
                         length(l_minmax_sql) - 2); -- trim trailing comma
  l_minmax_sql := l_minmax_sql || ' from ' || p_owner || '.' || p_table_name;

  l_minmax_cur := dbms_sql.open_cursor;
  dbms_sql.parse (l_minmax_cur, l_minmax_sql, dbms_sql.native);
  dbms_sql.describe_columns (l_minmax_cur, l_minmax_cnt, l_minmax_tab);

  for i in 1..l_minmax_cnt
  loop
    dbms_sql.define_column(l_minmax_cur, i, l_fetch_value);
  end loop;

  l_fetch_ok := dbms_sql.execute(l_minmax_cur);

  loop
    l_fetch_ok := dbms_sql.fetch_rows(l_minmax_cur);
    exit when l_fetch_ok = 0;

    -- loop over the columns selected over
    for i in 1..l_columns_cnt
    loop
      output_tab(1) := output_tab(1) || '|' || l_columns_tab(i).col_name;

      dbms_sql.column_value(l_minmax_cur, (2*i-1), l_fetch_value);
      output_tab(2) := output_tab(2) || '|' || 
        lpad(l_fetch_value, length(l_columns_tab(i).col_name), ' ');
      dbms_sql.column_value(l_minmax_cur, (2*i), l_fetch_value);
      output_tab(3) := output_tab(3) || '|' || 
        lpad(l_fetch_value, length(l_columns_tab(i).col_name), ' ');

    end loop;
  end loop;

  if dbms_sql.is_open(l_minmax_cur) then
    dbms_sql.close_cursor (l_minmax_cur);
  end if;

  if dbms_sql.is_open (l_columns_cur) then
    dbms_sql.close_cursor (l_columns_cur);
  end if;

  for i in output_tab.first..output_tab.last
  loop
    dbms_output.put_line(output_tab(i));
  end loop;
end;
/

Results:

                |GP_ID|GGP_ID|OBJECT_NAME
      MIN_LENGTH|    1|     1|          9
      MAX_LENGTH|    7|     4|         41