oracle中查看注释,ORACLE 查询表相关字段注释

--最近整理数据库表，Name，Type，Nullable，Default，Comments整理到WORD中，类似于命令行下使用desc table_name

由于使用DESC命令不知道如何把对应的字段放入到word表格中，也不想一个个拷贝，因为采用以表的形式查询出来，然后在WORD中建立一个相同行和列的表格放入。

方法如下：

一、创建函数，查询LONG列使用

CREATE OR REPLACE FUNCTION LONGSEARCH(V_TABLENAME VARCHAR2,

V_COLNAME   VARCHAR2) RETURN VARCHAR2 IS

VARC    LONG;

VRSTR   VARCHAR2(32767);

LN      NUMBER(6);

STRT    NUMBER(6);

V_COUNT NUMBER;

BEGIN

V_COUNT := 0;

SELECT COUNT(*)

INTO V_COUNT

FROM USER_TAB_COLS T

WHERE T.TABLE_NAME = V_TABLENAME

AND T.COLUMN_NAME = V_COLNAME;

IF V_COUNT = 0 THEN

RETURN '';

ELSE

SELECT DATA_DEFAULT

INTO VARC

FROM USER_TAB_COLS T

WHERE T.TABLE_NAME = V_TABLENAME

AND T.COLUMN_NAME = V_COLNAME;

END IF;

IF VARC IS NULL THEN

RETURN '';

END IF;

--LN := DBMS_LOB.GetLength (varC);

LN   := 100;

STRT := 1;

DBMS_LOB.READ(VARC, LN, STRT, VRSTR);

--      DBMS_OUTPUT.put_line ('Return:  ' || vRStr);

IF LENGTH(VRSTR) > 32767 THEN

VRSTR := SUBSTR(VRSTR, 0, 32767);

END IF;

VRSTR := TRIM(REPLACE(TRIM(VRSTR), CHR(39)));

VRSTR := TRIM(REPLACE(TRIM(VRSTR), CHR(10)));

VRSTR := TRIM(REPLACE(TRIM(VRSTR), CHR(13)));

RETURN VRSTR;

END;

RETURN VRSTR;

END;

二、使用以下查询SQL

SELECT T.COLUMN_NAME,

CASE

WHEN (T.DATA_TYPE IN ('VARCHAR2', 'NUMBER')) THEN

T.DATA_TYPE || '(' || T.DATA_LENGTH || ')'

ELSE

T.DATA_TYPE

END AS TYPE,

T.NULLABLE,

TRIM(LONGSEARCH(T.TABLE_NAME, T.COLUMN_NAME)) DEFAULTVALUE,

C.COMMENTS

FROM USER_TAB_COLS T

JOIN USER_COL_COMMENTS C ON (T.TABLE_NAME = C.TABLE_NAME AND

T.COLUMN_NAME = C.COLUMN_NAME)

WHERE T.TABLE_NAME = 'BIZ_CON_PAYMENT'

ORDER BY T.COLUMN_NAME;

阅读(905) | 评论(0) | 转发(0) |