oracle批量获取建表语句

问题起因

昨天同事来问我，是否能批量获取oracle的建表语句，需要提供给客户，
我们知道，oracle的内部视图中并没有直接记录建表语句，但是User_Tab_Columns这个视图中记录了字段，字段类型，于是想到用语句拼接满足这个需求。

脚本实现

在实现过程中需要考虑到

（1）字段除了有字段类型，字段长度等普通属性外，还有是否非空，是否有默认值这些特殊属性，对于特殊属性要加以判断；
（2）记录默认值属性的字段DATA_DEFAULT为long型，无法直接转换成char型，需要自定义函数来解决。

实现脚本如下

long类型转换成char类型函数

Create Or Replace Function f_Long2char(v_Tab_Name In Varchar2,
                                       v_Col_Id   In Number)
  Return Varchar2 As
  v_Char Varchar2(255);
Begin
  Select Data_Default
    Into v_Char
    From User_Tab_Cols
   Where Table_Name = Upper(v_Tab_Name)
     And Column_Id = v_Col_Id;
  v_Char := Substr(v_Char, 1, 50);
  Return v_Char;
End;

获取建表语句sql

Select t.Table_Name || '(' || To_Char(Wm_Concat(t.Column_Name)) || ');'
  From (Select 'create table ' || t.Table_Name As Table_Name,
               t.Column_Name || ' ' || t.Data_Type || Case
                 When t.Data_Type In ('DATE', 'CLOB') Then
                  Null
                 Else
                  '(' || t.Data_Length || ')'
               End || Case
                 When t.Nullable = 'Y' Then
                  Null
                 Else
                  ' NOT NULL'
               End || Case
                 When t.Data_Default Is Not Null Then
                  ' DEFAULT ' || Case
                    When t.Data_Type Like '%VARCHAR2%' Then
                     '' || f_Long2char(t.Table_Name, t.Column_Id) || ''
                    Else
                     f_Long2char(t.Table_Name, t.Column_Id)
                  End
                 Else
                  Null
               End As Column_Name
          From User_Tab_Columns t
         Where t.Table_Name Like '%T1%'
         Order By t.Column_Id) t
 Group By t.Table_Name;
 

亲测好用，满足需求啦！

转载请注明原地址~