Oracle拼出建表语句

目标：自动拼出建表语句，并且把字段注释自动放在字段定义后，方便脚本沟通。

方法：运用系统表来拼出SQL

-- 涉及系统表：
-- DBA_COL_COMMENTS 字段注释
-- DBA_TAB_COLUMNS  字段定义

-- 拼字段定义
SELECT  A.COLUMN_NAME||'  '||A.DATA_TYPE||'  '||A.NULLABLE||COL_SEP||'  '||A.COMMENTS AS COL_DEFINE
FROM(
  select A.COLUMN_NAME,
        A.DATA_TYPE||CASE WHEN DATA_TYPE='NUMBER' THEN '('||A.DATA_PRECISION||','||A.DATA_SCALE||')' 
         WHEN A.DATA_TYPE IN ('VARCHAR2','VARCHAR') THEN '('||A.DATA_LENGTH||')' 
         ELSE '' END AS DATA_TYPE,
        CASE WHEN A.NULLABLE='N' THEN 'NOT NULL' ELSE '' END  AS NULLABLE,
        '--'||B.comments AS comments,
        CASE WHEN A.COLUMN_ID =max(column_id) over(PARTITION BY A.TABLE_NAME) THEN '' ELSE ',' END AS COL_SEP
  from DBA_TAB_COLUMNS A
  INNER JOIN DBA_COL_COMMENTS B ON A.OWNER=B.OWNER AND B.TABLE_NAME=A.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME
  where A.Table_Name='TABLE_NAME' --TABLE_NAME
  ORDER BY A.column_id
) A;

-- 输出：
 BAL_DATE  NUMBER(8,0)  ,  --余额日期
 CUR_TYPE  VARCHAR2(3)  NOT NULL,  --币种
 BANK_ACC_ID  NUMBER(10,0)  ,  --银行账户ID

-- 复制出来，前后加上CREATE TABLE 'TABLE_NAME'( ) 即可