取出字段的类型与长度

最新推荐文章于 2021-04-04 09:59:23 发布

物润声无

最新推荐文章于 2021-04-04 09:59:23 发布

阅读量856

点赞数

分类专栏： oracle 文章标签： table function object c

本文链接：https://blog.csdn.net/gjswxhb/article/details/6226012

版权

oracle 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

-- 有待修改
DROP TYPE FieldInfo_TYPE_TABLE
/

DROP TYPE FieldInfo_TYPE
/

CREATE OR REPLACE TYPE FieldInfo_TYPE AS OBJECT(
       table_name          VARCHAR2(150), -- table owner & table name
       column_name         VARCHAR2(100),
       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_tableSynonymName IN VARCHAR2,
inp_fieldName IN VARCHAR2
)
RETURN FieldInfo_TYPE_TABLE

PIPELINED AS

       CURSOR getFieldInfo_Cur( lv_tableSynonymName IN VARCHAR2,
                                lv_fieldName        IN VARCHAR2) IS
          SELECT atc.owner||'.'||atc.table_name tableName,
                  atc.column_name,
                  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)
          );


          v_tableSynonymName VARCHAR2(100);
          v_fieldName        VARCHAR2(100);

BEGIN
     v_tableSynonymName := TRIM(inp_tableSynonymName);
     v_fieldName        := TRIM(inp_fieldName);

     FOR c IN getFieldInfo_Cur(v_tableSynonymName,v_fieldName) LOOP

         PIPE ROW(
              FieldInfo_TYPE(
                             c.tableName,
                             c.column_name,
                             c.data_type,
                             c.data_length,
                             c.data_precision,
                             c.data_scale
              )
         );

END LOOP;

END getFieldInfo_Func;
/