--导出表的ddl
CREATE OR REPLACE PROCEDURE show_create_table( db IN varchar(255), tb IN varchar(255)) ASsql1 text;ret text := '';cmt text :='';sql2 text :='';
BEGINFOR WSX IN (select TABLEDEF(db,tb) as ddl from dual) LOOPret:= ret||WSX.DDL;END LOOP;ret := ret||chr(10);-- print ret;FOR ZXCV IN (select INDEX_NAME from ALL_INDEXES where TABLE_OWNER=db and TABLE_NAME=tb ) LOOPsql1 := 'select dbms_metadata.get_ddl(''INDEX'','''||ZXCV.INDEX_NAME||''','''||db||''') from dual';for QAZ IN (select dbms_metadata.get_ddl('INDEX',ZXCV.INDEX_NAME,DB)as DDL from dual ) LOOPret:= ret||QAZ.DDL;ret := ret||chr(10);END LOOP;END LOOP;-- print ret;ret := ret||chr(10);ret := ret||chr(13);FOR TAB_CMT IN (select * from DBA_TAB_COMMENTS where OWNER=db AND TABLE_NAME=tb) LOOPcmt:= cmt||'COMMENT ON TABLE '||TAB_CMT.OWNER||'.'||TAB_CMT.TABLE_NAME||' IS '''''||TAB_CMT.COMMENTS||''''';'||char(10);END LOOP;FOR COL_CMT IN (select * from DBA_COL_COMMENTS where OWNER=db AND TABLE_NAME=tb) LOOPcmt:= cmt||'COMMENT ON COLUMN '||COL_CMT.OWNER||'.'||COL_CMT.TABLE_NAME||'('||COL_CMT.COLUMN_NAME||') IS '''''||COL_CMT.COMMENTS||''''';'||char(10);END LOOP;--print cmt;print '-----------------------------';--sql2 := 'select '''|| ret ||''' as ddl' ; sql2:= 'select '||'''' ||ret ||char(10)||cmt ||''''|| ' as ddl';
--print sql2;
--print cmt;
EXECUTE IMMEDIATE sql2;
END;-- 使用示例
CALL show_create_table('SCHEMA_NAME','TABLE_NAME');此内容引用自此处：dbms_metadata.get_ddl生成建表语句时没有字段描述 | 达梦技术社区
)
