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

发布于 2024-12-23 15:42:41 字数 2095 浏览 1 评论 0原文

下面代码的输出是：

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

输出如我所料，但是无论如何都可以使用索引（即 j）循环遍历列，而不是执行 RESULTS(I).MAX_LENGTH , RESULTS (I).MAX_LENGTH 等？值得关注的是，当向“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;

原文

The output from the below code is:

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

The output is as I expect, but is there anyway to loop through the columns using an index (ie. j) instead of doing RESULTS(I).MAX_LENGTH , RESULTS(I).MAX_LENGTH etc ? The concern is that when adding extra columns to the 'R_RESULT_REC' record, another loop is required.

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

固执像三岁 2024-12-30 15:42:41

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

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

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

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

3 个循环；
- 对列列表 (c) 进行 2 次循环，
- 对多个分析函数 (a) 进行 1 次循环。
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

代码：

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;
/

结果：

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

This uses DBMS_SQL, so it's pretty snarly to read. The main reason I saw to use it was that I could get columnar descriptions of a SQL statement and to a buffer-based, not object-based fetch.

Rather than making calls to DBMS_OUTPUT during the processing, it builds a table of records for output, using associative arrays for simplicity.

It could further be refined to have an array or parsable list of functions to apply to each function, but that seems excess to current requirements. The nature of the code would require editing if new aggregation functions are being added.

Call overview (2c + a + s):

3 loops;
- 2 loops over column list (c),
- 1 loop over number of analytic functions (a).
1 SQL statement against table data (s).

OP's call overview (c*s + a + 1):

1 loop, executing a sql statement against table data per column (c*s)
a+1 loops, where a is the number of analytic functions

Test data:

  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

回复收藏 0 原文

谈场末日恋爱 2024-12-30 15:42:41

如果您想使用 DBMS_SQL 包（有时非常复杂），那么 DBMS_SQL.COLUMN_VALUE 函数可能适合您。

更新：
或者更好：DBMS_SQL.DESC_REC
你可以参考：
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i996963
注意示例 8

我还没有测试过它

更新：
也许您真正想要的是循环对象类型属性而不是表列，所以也许您应该尝试不同的方法：
使您的类型 R_RESULT_REC 成为数据库中的对象类型，然后您可以循环查询结果：

SELECT attr_name
FROM user_type_attrs
WHERE type_name = 'R_RESULT_REC'

这与使用索引不同，但您仍然不需要对列名称/类型属性进行硬编码，

这里是代码（基于您的)：

CREATE OR REPLACE TYPE R_RESULT_REC AS OBJECT
(
  COL_NAME        VARCHAR2(100),
  MIN_LENGTH         NUMBER,
  MAX_LENGTH         NUMBER
);
/

然后：

DECLARE

  TYPE tr_RESULT IS TABLE OF R_RESULT_REC;
  RESULTS   TR_RESULT := TR_RESULT();
  v_counter NUMBER := 1;
  v_max     number;
  v_min     number;

BEGIN
  FOR J IN (SELECT DISTINCT COLUMN_NAME
              FROM ALL_TAB_COLUMNS
             WHERE OWNER = 'SYSTEM'
               and TABLE_NAME = 'SPECCHAR') LOOP

    EXECUTE IMMEDIATE 'SELECT MIN(LENGTH(' || J.COLUMN_NAME || ')),
      MAX(LENGTH(' || J.COLUMN_NAME || '))      FROM ' ||
                      'SPECCHAR'
      INTO v_min, v_max;

    RESULTS.EXTEND;
    RESULTS(V_COUNTER) := new R_RESULT_REC(J.COLUMN_NAME, v_min, v_max);
    V_COUNTER := V_COUNTER + 1;
  END LOOP;

  for r in (select attr_name
              from all_type_attrs t
             where t.owner = 'SYSTEM'
               and t.type_name = 'R_RESULT_REC') loop

    FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
      IF I = RESULTS.LAST THEN
        execute immediate 'declare rec R_RESULT_REC := :0; begin' ||
                          ' DBMS_OUTPUT.PUT_LINE(rec.' || r.attr_name || ');' ||
                          'end;'
          using RESULTS(I);
      ELSIF I = RESULTS.FIRST THEN
        execute immediate 'declare rec R_RESULT_REC := :0; begin' ||
                          ' DBMS_OUTPUT.PUT(''' || r.attr_name ||
                          '|'' || rec.' || r.attr_name || ' || ''|'');' ||
                          'end;'
          using RESULTS(I);
      ELSE
        execute immediate 'declare rec R_RESULT_REC := :0; begin' ||
                          ' DBMS_OUTPUT.PUT(rec.' || r.attr_name ||
                          ' || ''|''); ' || 'end;'
          using RESULTS(I);
      END IF;
    END LOOP;

  end loop;

end;

如果您向记录添加另一个属性（并使用值启动它），它将自动显示它。

If you want to use the DBMS_SQL package (which is sometimes very complex), then there is a DBMS_SQL.COLUMN_VALUE function that may work for you.

update:
Or even better: DBMS_SQL.DESC_REC
you can refer to:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i996963
notice example 8

I haven't tested it

update:
Perhaps what you really want is to loop on an Object type attributes and not a table column, so maybe you should try a different approach:
Make your type R_RESULT_REC an Object type in the DB and then you can loop on the query results:

SELECT attr_name
FROM user_type_attrs
WHERE type_name = 'R_RESULT_REC'

It's not like working with indexes but you still don't need to hard code the column names / type attributes

here is the code (based on yours):

CREATE OR REPLACE TYPE R_RESULT_REC AS OBJECT
(
  COL_NAME        VARCHAR2(100),
  MIN_LENGTH         NUMBER,
  MAX_LENGTH         NUMBER
);
/

and then:

DECLARE

  TYPE tr_RESULT IS TABLE OF R_RESULT_REC;
  RESULTS   TR_RESULT := TR_RESULT();
  v_counter NUMBER := 1;
  v_max     number;
  v_min     number;

BEGIN
  FOR J IN (SELECT DISTINCT COLUMN_NAME
              FROM ALL_TAB_COLUMNS
             WHERE OWNER = 'SYSTEM'
               and TABLE_NAME = 'SPECCHAR') LOOP

    EXECUTE IMMEDIATE 'SELECT MIN(LENGTH(' || J.COLUMN_NAME || ')),
      MAX(LENGTH(' || J.COLUMN_NAME || '))      FROM ' ||
                      'SPECCHAR'
      INTO v_min, v_max;

    RESULTS.EXTEND;
    RESULTS(V_COUNTER) := new R_RESULT_REC(J.COLUMN_NAME, v_min, v_max);
    V_COUNTER := V_COUNTER + 1;
  END LOOP;

  for r in (select attr_name
              from all_type_attrs t
             where t.owner = 'SYSTEM'
               and t.type_name = 'R_RESULT_REC') loop

    FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
      IF I = RESULTS.LAST THEN
        execute immediate 'declare rec R_RESULT_REC := :0; begin' ||
                          ' DBMS_OUTPUT.PUT_LINE(rec.' || r.attr_name || ');' ||
                          'end;'
          using RESULTS(I);
      ELSIF I = RESULTS.FIRST THEN
        execute immediate 'declare rec R_RESULT_REC := :0; begin' ||
                          ' DBMS_OUTPUT.PUT(''' || r.attr_name ||
                          '|'' || rec.' || r.attr_name || ' || ''|'');' ||
                          'end;'
          using RESULTS(I);
      ELSE
        execute immediate 'declare rec R_RESULT_REC := :0; begin' ||
                          ' DBMS_OUTPUT.PUT(rec.' || r.attr_name ||
                          ' || ''|''); ' || 'end;'
          using RESULTS(I);
      END IF;
    END LOOP;

  end loop;

end;

If you'll add another attribute to the Record (and initiate it with values) , it will automatic display it.

回复收藏 0 原文

梦途 2024-12-30 15:42:41

为此，请利用 Oracle 的统计数据。

首先，使用 dbms_stats.gather_table_stats 在表上完全构建统计信息

然后，创建以下函数来帮助转换 Oracle 存储在 all_tab_columns 中的原始低/高值

create or replace function show_raw(i_raw raw, i_type varchar2)
return varchar2 is
  l_varchar2 varchar2(32);
  l_number number;
  l_date date;
  l_nvarchar2 nvarchar2(32);
  l_rowid rowid;
  l_char char;
begin

  if (i_type = 'VARCHAR2') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_varchar2);
    return to_char(l_varchar2);
  elsif(i_type = 'NUMBER') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_number);
    return to_char(l_number);
  elsif(i_type = 'DATE') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_date);
    return to_char(l_date);
  elsif(i_type = 'NVARCHAR2') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_nvarchar2);
    return to_char(l_nvarchar2);
  elsif(i_type = 'ROWID') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_rowid);
    return to_char(l_rowid);
  elsif(i_type = 'CHAR') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_char);
    return l_char;
  else return 'Unknown type value';
  end if;
end;

然后，只需选择每列的低/高值：

select column_id, 
  column_name, 
  data_type, 
  show_raw(low_value, data_type) as min_val, 
  show_raw(high_value, data_type) as max_val
from all_tab_columns
where table_name = 'SOME_TABLE'
and owner = 'SOME_OWNER'
;

Take advantage of Oracle's stats for this.

First, fully build stats on table using dbms_stats.gather_table_stats

Then, create the following function to help translate the raw low/high values that Oracle stores in all_tab_columns

create or replace function show_raw(i_raw raw, i_type varchar2)
return varchar2 is
  l_varchar2 varchar2(32);
  l_number number;
  l_date date;
  l_nvarchar2 nvarchar2(32);
  l_rowid rowid;
  l_char char;
begin

  if (i_type = 'VARCHAR2') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_varchar2);
    return to_char(l_varchar2);
  elsif(i_type = 'NUMBER') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_number);
    return to_char(l_number);
  elsif(i_type = 'DATE') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_date);
    return to_char(l_date);
  elsif(i_type = 'NVARCHAR2') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_nvarchar2);
    return to_char(l_nvarchar2);
  elsif(i_type = 'ROWID') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_rowid);
    return to_char(l_rowid);
  elsif(i_type = 'CHAR') then
    DBMS_STATS.CONVERT_RAW_VALUE(i_raw, l_char);
    return l_char;
  else return 'Unknown type value';
  end if;
end;

Then, just select the low/high values for each column:

select column_id, 
  column_name, 
  data_type, 
  show_raw(low_value, data_type) as min_val, 
  show_raw(high_value, data_type) as max_val
from all_tab_columns
where table_name = 'SOME_TABLE'
and owner = 'SOME_OWNER'
;

回复收藏 0 原文

~没有更多了~