使用动态SQL检索表结构

小编典典

使用动态SQL检索表结构

sql

我正在迁移数据库，并试图将表结构信息检索到单个行项目中以进行机器处理。由于技术原因，无法使用现有的迁移工具，因此必须以这种方式进行处理。

我在多个表上成功运行了以下查询：

SELECT LISTAGG(column_name || ',' || data_type || ',' || data_length, ',')
WITHIN GROUP (ORDER BY column_id)
FROM all_tab_cols
WHERE table_name = 'MyTableName'

对于许多表，它都可以正常工作并返回我所期望的：

ColumnName1, VARCHAR2, 20, ColumnName2, NUMBER, 22, ColumnName3, CHAR, 3, ...

但是，有时它不是很正确。例如，从数据库设计文档中我知道ColumnName2应该是长度为2的数字，而不是22。为什么返回的值不正确？

甚至更令人困惑的是，有时它根本不起作用并且什么也不返回。我当时以为是CHAR数据类型引起了问题，但是我的一些具有CHAR的表工作正常。如果它们是INTEGER，SHORTINT或DATE类型，似乎确实给我带来了问题。解决此问题的最佳方法是什么？

我也知道该表存在，因为当我运行一个简单的表时

SELECT * FROM MyTableName

它返回表中的所有记录。

更新

我尝试用data_precision替换data_length，对于数字它返回了正确的答案，但是现在我对VARCHAR2没有任何帮助。如果我是一个数字，我该如何重组我的查询以获得data_precision；如果还有其他什么，我该如何给我data_length？

另外，我还有几个表将不允许我查看它们的结构。我对模式还不是很熟悉，但是我知道有时在不同的表下可能存在一个表。但是为什么用SELECT
*返回数据，但是当我查看all_tab_col时，这里的结构信息却不显示？

阅读 254

2021-04-15

共1个答案

小编典典

您需要决定使用data_length还是data_precision基于data_type，您可以使用case表达式来做到这一点：

select listagg(column_name ||','|| data_type ||','||
  case 
    when data_type in ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'RAW')
      then to_char(data_length)
    when data_type = 'NUMBER'
        and (data_precision is not null or data_scale is not null)
      then data_precision || case
        when data_scale > 0 then '.' || data_scale
      end
    end, ',') within group (order by column_id)
from all_tab_columns
where table_name = 'MYTABLENAME'
and owner = user -- if it is always current user, use user_tab_columns instead
/

如果我将该表创建为：

create table mytablename (col1 varchar2(20), col2 number(2), col3 char(3), col4 date,
  col5 timestamp(3), col6 clob, col7 number(5,2));

然后该查询产生：

COL1,VARCHAR2,20,COL2,NUMBER,2,COL3,CHAR,3,COL4,DATE,,COL5,TIMESTAMP(3),,COL6,CLOB,,COL7,NUMBER,5.2

在此示例中，我将数字表示为 precision 。 scale ，但是您可能不必担心秤，或者可能想要以不同的方式处理它们-
取决于结果的使用方式。并且我为没有大小的数据类型包括了一个空字段，例如CLOB和DATE。

另请注意，时间戳记（和间隔）包括数据类型本身的精度，因此时间戳记timestamp(3)直接来自该列的data_type。具有时区和间隔的时间戳记在数据类型名称中也包含空格。

因此，这是一个起点，您可以将其扩展为需要以特定方式处理的其他数据类型，或者（例如）将时间戳精度拆分为单独的逗号分隔字段。

2021-04-15