getFieldInfo

-- create time: 23 Feb 2011/*-- synonym_name 可以不用考虑trim, 换取时间SELECT synonym_name, table_name, table_owner FROM   all_synonyms WHERE  synonym_name = 'PYRD'*/

DROP TYPE FieldInfo_TYPE_TABLE/

DROP TYPE FieldInfo_TYPE/

CREATE OR REPLACE TYPE FieldInfo_TYPE AS OBJECT(        synonname           VARCHAR2(150),       owner_name          VARCHAR2(50),       table_name          VARCHAR2(100), -- table owner & table name       tableComment        VARCHAR2(10240),       column_name         VARCHAR2(100),       columnComment       VARCHAR2(10240),       nullable            CHAR(1),       data_type           VARCHAR2(10),       data_length         NUMBER(9),       data_precision      NUMBER(2),       data_scale          NUMBER(2))/

CREATE OR REPLACE TYPE FieldInfo_TYPE_TABLE AS TABLE OF FieldInfo_TYPE/CREATE OR REPLACE FUNCTION getFieldInfo_Func(inp_owner IN VARCHAR2)

RETURN FieldInfo_TYPE_TABLE

PIPELINED AS        CURSOR getSynName(lv_owner IN VARCHAR2) IS        SELECT DISTINCT synonym_name,               table_name,                owner          FROM all_synonyms         WHERE owner = lv_owner;       

       CURSOR getFieldInfo_Cur( lv_tableSynonymName IN VARCHAR2,                                lv_owner IN VARCHAR2) IS          SELECT  atc.owner,                  atc.table_name,                  atc.column_name,                   atc.nullable,                  atc.data_type,                  atc.data_length,                  atc.data_precision,                  atc.data_scale          FROM    ALL_TAB_COLS atc          WHERE   EXISTS (                  SELECT 1                   FROM   all_synonyms allsyno                  WHERE  allsyno.table_name = atc.table_name                  AND    allsyno.table_owner = atc.owner                  AND    synonym_name = nvl(lv_tableSynonymName,synonym_name)                  --AND    atc.COLUMN_NAME = nvl(lv_fieldName,atc.COLUMN_NAME)                  AND    allsyno.table_owner = lv_owner          );                v_tableComment all_tab_comments.comments%TYPE;        v_columnComment all_col_comments.comments%TYPE;                v_tablename     all_synonyms.table_name%TYPE;        v_synonymname   all_synonyms.synonym_name%TYPE;        v_owner         all_synonyms.owner%TYPE;                    BEGIN

    v_owner := nvl(TRIM(inp_owner),'TBAADM');            IF NOT getSynName%ISOPEN THEN        OPEN getSynName(v_owner);    END IF;

    IF getSynName%ISOPEN THEN        LOOP

            FETCH getSynName            INTO v_synonymname, v_tablename,v_owner;                    IF getSynName%NOTFOUND THEN                CLOSE getSynName;                RETURN;            END IF;

            BEGIN                SELECT comments                  INTO v_tableComment                  FROM all_tab_comments                 WHERE owner = 'TBAADM'                   AND table_name = v_tablename;            EXCEPTION            WHEN no_data_found THEN                v_tableComment := NULL;            END;

            FOR c IN getFieldInfo_Cur(v_synonymname,v_owner) LOOP                BEGIN                SELECT comments                   INTO v_columnComment                  FROM all_col_comments                  WHERE owner = v_owner                   AND table_name = c.table_name                   AND COLUMN_name = c.column_name;                 EXCEPTION                WHEN no_data_found THEN                    v_columnComment := NULL;                END;

                PIPE ROW(                      FieldInfo_TYPE(                                     v_synonymname,                                     v_owner,                                     v_tablename,                                     v_tableComment,                                     c.column_name,                                     v_columnComment,                                     c.nullable,                                     c.data_type,                                     c.data_length,                                     c.data_precision,                                     c.data_scale                      )                 );

                v_tableComment := '';                v_synonymname := '';                v_tablename := '';                v_owner := '';

             END LOOP;                END LOOP;    END IF;

END getFieldInfo_Func;/--select * from table(getFieldInfo_Func) where synonname = 'GAM';

--1496.221 secondsSELECT * FROM TABLE(getFieldInfo_Func('PYRD','')) WHERE ROWNUM<2;--1500.651 secondsSELECT * FROM TABLE(getFieldInfo_Func('PYRD',''))--6390.644 seconds  29443 条记录SELECT * FROM ALL_tab_cols-- 共(148231)SELECT count(*) FROM ALL_tab_cols

SELECT  owner FROM all_tables otrWHERE EXISTS (SELECT 1 FROM all_tables itr WHERE otr.owner = itr.owner)

SELECT DISTINCT atc.owner FROM ALL_TAB_COLS atc

SELECT * FROM all_synonyms WHERE table_owner = 'SVSUSER' --ROWNUM<2 -- synonym_name

SELECT * FROM SVSUSER.APPPARAMETER

SELECT  atc.owner||'.'||atc.table_name tableName,      atc.column_name,       atc.data_type,      atc.data_length,      atc.data_precision,      atc.data_scaleFROM    ALL_TAB_COLS atc,         all_synonyms allsynoWHERE   atc.table_name = allsyno.table_nameAND     atc.owner = allsyno.table_ownerAND     synonym_name = 'PYRD'

SELECT DISTINCT allsyno.table_name,allsyno.table_owner, allsyno.synonym_nameFROM    ALL_TAB_COLS atc,         all_synonyms allsynoWHERE   atc.table_name = allsyno.table_nameAND     atc.owner = allsyno.table_ownerAND     atc.column_name IN ('DEL_FLG','ENTITY_CRE_FLG')

SELECT allsyno.table_name, allsyno.synonym_nameFROM all_synonyms allsynoWHERE  table_name ='CTAIMISACCT'dbscust.CTAIMISACCT

 

SELECT  COUNT(DISTINCT synonym_name)FROM    all_synonyms WHERE owner = 'TBAADM'--3570

SELECT  COUNT(synonym_name)FROM    all_synonyms WHERE owner = 'TBAADM'--3570

-- select distinct data_type from ALL_TAB_COLS where data_type LIKE 'N%';

-- 获得表的注释SELECT * FROM all_tab_comments WHERE TABLE_TYPE <> 'TABLE' AND TABLE_TYPE <> 'VIEW' AND ROWNUM<200;/*   OWNER TABLE_NAME TABLE_TYPE COMMENTS1 sys   ABC TABLE */

-- 获得列的注释SELECT * FROM all_col_comments WHERE ROWNUM<3;/*      OWNER TABLE_NAME COLUMN_NAME COMMENTS1 SYS CON$ OWNER#  */

-- 获得索引的信息SELECT * FROM all_indexes WHERE TABLE_OWNER = 'TBAADM' AND ROWNUM< 3;  

-- 获得列索引信息SELECT * FROM ALL_TAB_COLS WHERE ROWNUM< 3;

select * from table(getFieldInfo_Func) where synonname = 'GAM';

select * from table(getFieldInfo_Func('TBAADM'));