时间戳

首页 > 解决方案 > Oracle 视图：如何获取列定义

oracle - Oracle 视图：如何获取列定义

问题描述

我有一个 Oracle 视图：

create view schemaName.viewName as
select case when 1=1 then 1 else 2 end as col1, decode('A','A','B','C') as col2 from dual

有没有办法获得包含这些信息的输出或表格：

Column_Name: Col1
Column_Definition: case when 1=1 then 1 else 2 end

Column_Name: Col2
Column_Definition: decode('A','A','B','C')

非常感谢

标签： oracleviewdefinition

解决方案

Thanks for clarifying ... just try below block and see if this helps ..Also let me know for any column definition which is incorrect

prerequiste for ananymous block:

create view TEST_VW as
select case when 1=1 then 1 else 2 end as col1, decode('A','A','B','C') as col2, 
null as col3 , sysdate as col4 , 'var1' as col5  
from dual;

-- 在视图定义中，我希望每列的格式和每列的分隔符是分隔该块的列名和定义的关键。

 create sequence seq
      start with 1;

create table col_nm_def 
      (column_nm varchar2(1000),
      column_def varchar2(1000),
      id number);


Ananymous block;

DECLARE 

      a varchar2(1000); 
      b varchar2(1000); 
      c varchar2(1000);
      d number;

      BEGIN 

      EXECUTE  IMMEDIATE 'SELECT TEXT FROM user_views 
      WHERE VIEW_NAME=''TEST_VW'' ' INTO a;

       EXECUTE  IMMEDIATE 'delete from col_nm_def';

       EXECUTE  IMMEDIATE  'drop sequence seq';


       EXECUTE  IMMEDIATE     'create sequence seq
      start with 1';


      select regexp_replace(regexp_replace(a,'select|from|dual',''),'as\W|, ' , '|') into b from dual; -- replacing as and (, ) to | symbol (try to change symbol if view uses this pipe in transformation)

      select regexp_count(b,'[|]') into d from dual; 

      d:=d+1;

      For i in 1..d

      LOOP

      insert into col_nm_def  values (REGEXP_SUBSTR ( b , '[^|]+' , i+1 , i+1 ),REGEXP_SUBSTR ( b , '[^|]+' , i , i ),seq.nextval);
      COMMIT;

    END LOOP;
    END;


Final result : select only odd rows 

select * from col_nm_def where mod(id,2)=1 order by ID;

推荐阅读