日期:2014-05-17 浏览次数:20812 次
DECLARE
V_TABLENAME VARCHAR2(100):='EMP';
V_SQL VARCHAR2(4000);
BEGIN
DBMS_OUTPUT.PUT_LINE('建表语句如下:'
------解决方案--------------------
'==>');
SELECT DBMS_METADATA.GET_DDL('TABLE',V_TABLENAME) INTO V_SQL FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_SQL);
DBMS_OUTPUT.PUT_LINE('表的字段信息如下:'
------解决方案--------------------
'==>');
DBMS_OUTPUT.PUT_LINE('表名称'
------解决方案--------------------
' '
------解决方案--------------------
'表注释'
------解决方案--------------------
' '
------解决方案--------------------
'字段名称'
------解决方案--------------------
' '
------解决方案--------------------
'字段注释'
------解决方案--------------------
' '
------解决方案--------------------
'字段类型'
------解决方案--------------------
' '
------解决方案--------------------
'字段长度'
------解决方案--------------------
' '
------解决方案--------------------
'是否为空');
FOR I IN (
SELECT a.TABLE_NAME,c.COMMENTS AS TCOMMENTS,a.COLUMN_NAME,b.commentS AS CCOMMNETS,a.DATA_TYPE, DECODE(a.DATA_TYPE,'CHAR',a.CHAR_LENGTH,'VARCHAR2',a.CHAR_LENGTH,'NUMBER',a.DATA_PRECISION)
------解决方案--------------------
DECODE(a.DATA_TYPE,'NUMBER',','
------解决方案--------------------
a.DATA_SCALE) as length,
NULLABLE
FROM (SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,COLUMN_ID,NULLABLE,CHAR_LENGTH FROM USER_TAB_COLUMNS WHERE 1 = 1 ORDER BY TABLE_NAME) a,
(SELECT * FROM USER_COL_COMMENTS) b,
(SELECT * FROM USER_tab_COMMENTS) c
WHERE a.COLUMN_NAME = b.COLUMN_NAME
AND a.TABLE_NAME = b.TABLE_NAME
AND a.TABLE_NAME = c.TABLE_NAME
AND A.TABLE_NAME = V_TABLENAME
ORDER BY a.TABLE_NAME,a.COLUMN_ID
) LOOP
DBMS_OUTPUT.PUT_LINE(I.TABLE_NAME
------解决方案--------------------