SELECT CONCAT('CREATE TABLE ',T.TABLE_NAME,' (') cre_sql FROM TABLES T WHERE T.TABLE_NAME = 'basic_ent'
UNION ALL
#字段信息
SELECT CONCAT(T.COLUMN_NAME,' ',
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T.COLUMN_TYPE,'varchar','varchar2'),'datetime','date'),
'decimal','number'),'text','clob'),'bigint','NUMBER'),'tinyint','NUMBER'),'int','NUMBER'),'double','NUMBER') ,
' ',(CASE WHEN T.IS_NULLABLE = 'NO' THEN 'NOT NULL ' ELSE 'NULL' END), ' ,') ZD
FROM COLUMNS T WHERE T.TABLE_NAME = 'basic_ent'
UNION ALL
#主键信息
SELECT CONCAT('CONSTRAINT PK_',T.TABLE_NAME,' PRIMARY KEY (',GROUP_CONCAT(T.COLUMN_NAME SEPARATOR ','),')')
FROM COLUMNS T WHERE T.TABLE_NAME = 'basic_ent' AND T.COLUMN_KEY = 'PRI'
UNION ALL
SELECT ') ;'
UNION ALL
#表注释
SELECT CONCAT('COMMENT ON table',' ',T.TABLE_NAME,' IS ''',T.TABLE_COMMENT,''' ;') FROM TABLES T WHERE T.TABLE_NAME = 'basic_ent'
UNION ALL
#字段注释
SELECT CONCAT('comment on column ',T.TABLE_NAME,'.',T.COLUMN_NAME,' IS ''',T.COLUMN_COMMENT,''' ;')
FROM COLUMNS T WHERE T.TABLE_NAME = 'basic_ent' ;
mysql创建表语句转换为oracle创建表语句
最新推荐文章于 2024-04-28 18:48:43 发布