oracle - 在oracle中执行select * from中的立即使用

SELECT owner OWNER, table_name, column_name, 'select max(length('||column_name||')) from '||table_name||';' max_data_length FROM dba_tab_columns WHERE AND ( data_type = 'NUMBER' OR data_type = 'INTEGER' )

这里有一些值得深思的地方。请注意，我只查找目录中尚未指定精度的数字列。（如果您愿意，您可以审核所有数字列并将声明的精度与数据使用的实际精度进行比较。）

我也只在特定的模式中寻找。相反，您可以给出要忽略的模式列表；我希望您没有认真考虑对进行任何更改SYS，例如，即使它确实（而且确实！）具有没有指定精度的数字列。

目录不存储INTEGER在数据类型中；相反，它将它存储为NUMBER(38)所以我没有INTEGER在DBA_TAB_COLUMNS. 但这提出了一个有趣的问题——也许你应该搜索所有列 where DATA_PRECISIONis null（如下面的代码），但也应该搜索DATA_PRECISION = 38.

在下面的代码中，我使用DBMS_OUTPUT将结果直接显示到屏幕上。你可能想用这个做一些更聪明的事情；要么创建一个表函数，要么创建一个表并将结果插入其中，或者甚至可能已经发出 DDL（请注意，这些也需要动态 SQL）。

这仍然让您处理规模问题。也许你可以通过一个规范来解决这个问题NUMBER(prec, *)——不确定这是否能满足你的需求。但是这个想法是相似的；您只需要仔细编写代码，就像我为精度所做的那样（例如考虑小数点和减号）。

长话短说，这是我在系统上运行的内容，以及它产生的输出。

declare
  prec number;
begin
  for rec in (
    select owner, table_name, column_name
    from   all_tab_columns
    where  owner in ('SCOTT', 'HR')
      and  data_type = 'NUMBER'
      and  data_precision is null
  )
  loop
    execute immediate
      'select max(length(translate(to_char(' || rec.column_name ||
              '), ''0-.'', ''0'')))
              from ' || rec.owner || '.' || rec.table_name
       into   prec;
    dbms_output.put_line('owner: '        || lpad(rec.owner, 12, ' ')       ||
                         ' table name: '  || lpad(rec.table_name, 12, ' ')  ||
                         ' column_name: ' || lpad(rec.column_name, 12, ' ') ||
                         ' precision: '   || prec);
  end loop;
end;
/

PL/SQL procedure successfully completed.

owner:           HR table name:      REGIONS column_name:    REGION_ID precision: 1
owner:           HR table name:    COUNTRIES column_name:    REGION_ID precision: 1
owner:        SCOTT table name:     SALGRADE column_name:        GRADE precision: 1
owner:        SCOTT table name:     SALGRADE column_name:        LOSAL precision: 4
owner:        SCOTT table name:     SALGRADE column_name:        HISAL precision: 4


PL/SQL procedure successfully completed.

编辑

以下是基于与 Sayan Malakshinov 在对我的回答和他的回答的评论中的扩展对话的几个附加点（主要是更正）。

最重要的是，即使我们可以计算出数字列的最大精度，这似乎与整个事情的最终目标没有直接关系，即确定现有 Oracle 列的正确 Postgre 数据类型。例如在 Postgre 中，与 Oracle 不同，区分整数和非整数很重要。除非在 Oracle 中 scale 明确为 0，否则我们不知道该列是“仅整数”；我们可以通过类似的动态 SQL 方法找到这一点，但我们将检查非整数值，而不是精度。

各种更正：我的查询对引用的标识符（模式名、表名、列名）很粗心。在 Sayan 的回答中查看动态查询中双引号的正确使用；我的动态查询应该被修改为像他一样使用双引号。

在我的方法中，我传递数字TO_CHAR，然后删除减号和小数点。当然，一个系统可以使用逗号或其他符号作为小数分隔符；更安全的方法是删除所有不是数字的东西。这可以做到

translate(col_name, '0123456789' || col_name, '0123456789')

该查询也不处理非常大或非常小的数字，可以存储在Oracle数据库中，但通过时只能用科学计数法表示TO_CHAR()。

无论如何，由于“最大精度”似乎与在 Postgre 中映射到正确数据类型的最终目标没有直接关系，所以我不会更改代码 - 将其保留为原始形式。

感谢Sayan 指出所有这些问题。

还有一件事——*_TAB_COLUMNS也包含有关视图列的信息；对于手头的任务，很可能应该忽略这些。很容易做到，只要我们意识到需要去做。

oracle - 在oracle中执行select * from中的立即使用

问题描述

解决方案

推荐阅读