oracle10g根据select语句得到字段名、主键、唯一等

建立package,包含用到的自定义类型和存储过程

create or replace package pkg_getViewFieldList is
--自定义cursor,允许动态游标，否则不允许
type cur_rs_type is ref cursor;

procedure proc_ins_rs(
   sTBTableNames varchar2, --以逗号为分隔符的包含所有表名的表名

   sTBCols varchar2, --存储所有列名的表名
   sTBRs varchar2, --结果集
   sTableView varchar2 --创建的视图名
);

--存储过程
procedure proc_oraGetViewFieldList
(
  sTableView varchar2,
  sOraSelect varchar2,
  sTableNames varchar2,
  cur out cur_rs_type
) ;
end pkg_getViewFieldList;

package body中的内容，即存储过程的实现

create or replace package body pkg_getViewFieldList
as
procedure proc_oraGetViewFieldList
(
  sTableView varchar2,
  sOraSelect varchar2,
  sTableNames varchar2,
  cur out cur_rs_type
)
as
/******************************************************************************
**  Name: [sp_GetViewFieldList]
**  Desc: 查询待添加视图的字段的属性信息（类型 大小等）
**
**
**  Return Values:
**
**  Parameters:
** 临时表名
** select语句
*******************************************************************************/
sTmp varchar2(4000):='';
 begin

--分隔表名字符串，得到所有的表名,并创建表，包含所有的表名
--sTmp := 'drop table tmpTableName'||sTableView;
--execute immediate sTmp;

sTmp :='create table tmpTableName'||sTableView||'
 as SELECT REGEXP_SUBSTR ('''||sTableNames||''', ''[^,]+'', 1,rownum) tableName
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH ('''||sTableNames||''') - LENGTH (REPLACE ('''||sTableNames||''', '','', ''''))+1';

dbms_output.put_line('split by , ');
--执行
execute immediate sTmp;

--创建一个结果集，包括列名、类型、主键、唯一等
--sTmp := 'drop table rsTable'||sTableView;
--execute immediate sTmp;

sTmp :='create table rsTable'||sTableView||'
      (FieldName varchar2(100),FieldIsIdentity varchar2(10),
      FieldIsPrimary varchar2(10), FieldType varchar2(100),
      FieldLength varchar2(100), FieldPrecision varchar2(100),
      FieldScale varchar2(100)) ';
dbms_output.put_line('exec rsTable');
--执行创建结果集
execute immediate sTmp;

--创建一个视图，为后面的获取sSQLSelect中的列作准备
--sTmp  :=  'drop view tmpView'||sTableView;
--execute immediate sTmp;

sTmp :='create view ' || sTableView || ' as ' || sOraSelect;
dbms_output.put_line('exec tmpView');
--执行创建视图
execute immediate sTmp;

--获取sSQLSelect中包含的所有列表，创建临时表
--sTmp := 'drop table tmpCols'||sTableView;
--execute immediate sTmp;

sTmp :='create table tmpCols'||sTableView||' as
     select a.column_name from user_tab_cols a, user_objects b
      where a.table_name=b.object_name and b.object_type = ''VIEW''
      and b.object_name=upper('''||sTableView||''')';
dbms_output.put_line('exec tmpCols');
execute immediate sTmp;
--执行函数，获得结果集
--遍历列，判断每一列是否在各个表中
--如果存在，进行下一次循环，否则逐个判断每一个表

proc_ins_rs('tmpTableName'||sTableView,
                           'tmpCols'||sTableView,
                           'rsTable'||sTableView,
                           sTableView);
                          
 open cur for 'select * from rsTable'||sTableView;
                          
--dbms_output.put_line('cur rows = '||cur%rowcount);

--删除所有临时表
dbms_output.put_line('drop tables');
sTmp := 'drop view '||  sTableView;
--execute immediate sTmp;
sTmp := 'drop table tmpTableName'||  sTableView;
--execute immediate sTmp;
sTmp := 'drop table tmpCols'||  sTableView;
--execute immediate sTmp;
sTmp := 'drop table rsTable'||  sTableView;
--execute immediate sTmp;

end proc_oragetviewfieldlist;

procedure proc_ins_rs
(
   sTBTableNames varchar2,
   sTBCols varchar2,
   sTBRs varchar2,
   sTableView varchar2 --创建的视图名
)

is
   tmpColsCur cur_rs_type;
   tmpTableCur cur_rs_type;
   tmpCol varchar2(100):='';
   tmpTable varchar2(100):='';
   tbCount int := ''; --表名数
   sTmp varchar2(4000):='';
   sTmpCol varchar2(100):='';
   sTmpTable varchar2(100):='';
   colExist int :=0; --列是否存在的标示，0不存在，1存在
   nCol int :=0;
   nTable int :=0;
   rsColExist int :=0; --结果集中是否有此字段的信息，0不存在，1存在
begin
--声明包含所有列和所有表的游标
--sTmpCol := ||'';
dbms_output.put_line('stbCols = '||sTBCols);
dbms_output.put_line('sTBTableNames = '||sTBTableNames);

open tmpColsCur for 'select * from '||sTBCols;
sTmpTable := 'select * from '||sTBTableNames||'';
--open tmpTableCur for 'select * from '||sTBTableNames;

--打开游标
--open tmpColsCur;
dbms_output.put_line('tmpColsCur open ');

--开始循环
loop
  <<nextCol>>
  --从游标中取记录,放入临时列中
  fetch tmpColsCur into tmpCol;
  --针对每一列，遍历所有表，判断是否在表中
  exit when tmpColsCur%notfound;
 
  dbms_output.put_line('tmpCol = '||tmpCol);
  --打开表游标
   open tmpTableCur for sTmpTable;
  loop
    --从table游标中取记录
     <<nextTable>>
      fetch tmpTableCur into tmpTable;
      exit when tmpTableCur%notfound;
     
       select count(column_name) into colExist from user_tab_cols
           where table_name=upper(''||tmpTable||'')
           and column_name=upper(''||tmpCol||'');
          
           dbms_output.put_line('colExist = '||colExist);
           --如果列存在本表中，则colExist为1，将列的信息插入到结果集中
        if colExist > 0
          then
           -- colExist := 1;
            --检查rs结果集中是否有该字段的信息，若有，则rsColExist=1
           -- select count(FieldName) into rsColExist
           --  from '||sTBRs||'
           --  where FieldName = upper(''||tmpCol||'');
            -- execute immediate sTmp;
            
             --if rsColExist = 0
            --   then
            sTmp := 'insert into '||sTBRs||'
 select distinct a.COLUMN_NAME FieldName,
case when
  exists(select h.COLUMN_NAME from all_indexes g
  inner join all_ind_columns h on g.INDEX_NAME = h.INDEX_NAME
  where a.COLUMN_NAME = h.COLUMN_NAME and g.UNIQUENESS = ''UNIQUE''
   and h.column_name = upper('''||tmpCol||''')
    and h.TABLE_NAME = a.TABLE_NAME )
  then ''1''
    else ''0''
     end FieldIsIdentity,
case when
  exists(select e.COLUMN_NAME from user_cons_columns e
  inner join user_constraints f on e.TABLE_NAME = f.TABLE_NAME
  where e.TABLE_NAME=a.TABLE_NAME 
   and f.CONSTRAINT_TYPE = ''P''
    and e.COLUMN_NAME = upper('''||tmpCol||'''))
  then ''1''
    else ''0''
     end FieldIsPrimary,
a.DATA_TYPE FieldType,
a.DATA_LENGTH FieldLength,
  a.DATA_PRECISION FieldPrecision,
  a.DATA_SCALE FieldScale
 from user_tab_columns a, user_constraints b, user_cons_columns c,user_tab_comments d
 where b.CONSTRAINT_NAME = c.CONSTRAINT_NAME
 and d.TABLE_NAME = a.TABLE_NAME
 and a.column_name = upper('''||tmpCol||''')
 and a.table_name = upper('''||sTableView||''')' ;
 
                  execute immediate sTmp;
                  close tmpTableCur;     
            goto nextCol;
          --  end if;
            else --当前列不在当前表中，查看下一张表
              colExist := 0;
              goto nexttable;
          end if;
      end loop;
     
end loop;

--关闭游标
if tmpColsCur%isOpen
  then
    close tmpColsCur;
    end if;

if tmpTableCur%isOpen
  then
    close tmpTableCur;
    end if;  
   
--sTmp :='select * from '||sTBRs||'';
--open rsCur for sTmp;
--dbms_output.put_line('rsCur rows = '||rsCur%rowcount);
--return rsCur;
end proc_ins_rs;
end pkg_getViewFieldList;