使用动态SQL检索表结构

问题描述

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

I am migrating a database and am trying to retrieve table structure information into a single line item for machine processing. For technical reasons, existing migration tools can not be used and it must be processed in this manner.

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

I have run the following query with success on multiple tables:

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

对于许多表来说，它都能正常工作并返回我所期望的结果:

For many of the tables it works fine and returns exactly what I would expect:

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

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

However, sometimes it is not quite right. For example, I know from the DB design document that ColumnName2 is supposed to be a number of length 2, not 22. Why does that return incorrectly?

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

And even more puzzling, sometimes it does not work at all and returns nothing. I was thinking it was the CHAR data type that was causing problems, but some of my tables that have CHAR worked alright. It does seem to pretty consistently give me problems if they are INTEGER, SHORTINT, or DATE types though. What is the best way to fix this problem?

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

I also know the table exists, because when I run a simple

SELECT * FROM MyTableName

它返回表中的所有记录.

it returns all of the records in the table.

更新

我尝试用data_precision替换data_length，对于数字它返回了正确的答案，但是现在我对VARCHAR2没有任何帮助.我该如何重组查询以获取data_precision(如果它是数字)，而给我data_length(如果它是其他东西)?

I tried replacing the data_length with data_precision and for numbers it returned the correct answer, but now I have nothing for VARCHAR2. How do I need to restructure my query to get me data_precision if it is a number and instead give me data_length if it is anything else?

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

Also, I still have several tables that will not let me view their structures. I am still not very familiar with schemas, but I understand sometimes a table may exist under a different one. But why would the data be returned with a SELECT *, but the structure information here would not when I am looking at all_tab_col?

推荐答案

您需要根据data_type决定使用data_length还是data_precision，可以使用大小写表达式来完成此操作:

You need to decide whether to use data_length or data_precision based on the data_type, which you can do with a case expression:

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.

In this example I've represented a number as precision.scale, but you may not have scales to worry about, or may want to handle them differently - depends how the result will be used. And I've included an empty field for the data types with no size, e.g. CLOB and DATE.

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

Also note that timestamps (and intervals) include the precision in the data type itself, so the timestamp(3) is coming directly from that column's data_type. Timestamps with time zones and intervals also include spaces in the data type name.

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

So this is a starting point, and you can extend it to other data types you need to handle in specific ways, or (say) split the timestamp precision out into a separate comma-separated field.

这篇关于使用动态SQL检索表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！