p_gettabsql

create or replace procedure p_gettabsql(
   avc_user    varchar2,
   avc_table   varchar2)
is
  lvc_user      varchar2(30);
  lvc_table     varchar2(30);


  fhandle utl_file.file_type;


  li_count       number(10);
  li_i           number(10):=0;
  lvc_line       varchar2(4000);


  lvc_colname    varchar2(40);
  lvc_coltype    varchar2(20);
  lvc_colnull    varchar2(500);
  --修改日期：2015-07-09 修改人:zhangruiu
  lvc_data_default varchar2(500);
  --修改完成


  lvc_tab_comment varchar2(500);
  lvc_col_comment varchar2(500);


  lvc_pkname      varchar2(30);
  lvc_pk_column   varchar2(500):='';


  lvc_tablespace_name varchar2(30);
  lvc_index_name  varchar2(30);
  lvc_index_type  varchar2(50);
  lvc_index_prior varchar2(30);
  lvc_index_column varchar2(50);


  lc_pkflag       char(1):='N';


  lvc_temptab     char(1):='N';
  lvc_duration    varchar2(50);
  e_tableerror   exception;


  --修改时间：2015-06-12 修改人：zhangruiu
  lvc_lon varchar2(4000);
  --修改完成


  cursor cur_coltype is
    select a.COLUMN_NAME,
       case when a.data_type ='VARCHAR2' and CHAR_USED = 'C' then 'VARCHAR2('||TO_CHAR(CHAR_LENGTH)||' CHAR)'
            when a.data_type ='VARCHAR2' and CHAR_USED = 'B' then 'VARCHAR2('||TO_CHAR(CHAR_LENGTH)||')'
            when a.data_type = 'CHAR' then 'CHAR('||TO_CHAR(DATA_LENGTH)||')'
            when a.data_type = 'NUMBER' and DATA_SCALE = 0 and DATA_PRECISION is not null then 'NUMBER('||TO_CHAR(DATA_PRECISION)||')'
            when a.data_type = 'NUMBER' and DATA_SCALE <> 0 then 'NUMBER('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'
            --修改时间：2015-06-05 修改人：zhangruiu
            when a.data_type = 'NUMBER' and DATA_SCALE is null then 'NUMBER'
            when a.data_type = 'CLOB' then 'CLOB'
            when a.data_type = 'BLOB' then 'BLOB'
            when a.data_type = 'NUMBER' and DATA_SCALE = 0 and DATA_PRECISION is null then 'INTEGER'
            when a.data_type = 'NVARCHAR2' then 'NVARCHAR2('||TO_CHAR(CHAR_LENGTH)||')'
            --修改完成
            when a.data_type = 'DATE' then 'DATE'
            --修改时间：2015-06-05 修改人：zhangruiu
            else a.data_type
            --修改完成
            end col_type,
       case when a.NULLABLE = 'N' then 'NOT NULL'
            end col_null,
--修改时间：2015-07-09 修改人：zhangruiu
       a.DATA_DEFAULT
--修改完成
  from dba_tab_columns a
 where a.owner = lvc_user
   and a.table_name = lvc_table
 order by a.column_id;


 cursor cur_colcomment is
--修改时间2015-06-11 修改人：zhangruiu
   select column_name,
   case when comments like '%''%' then replace(comments,'''','') else comments end comments
--修改完成
     from dba_col_comments
    where table_name = lvc_table
      and owner = lvc_user;


--修改时间：2015-06-11 修改人：zhangruiu
/*
  cursor cur_pk is
   select C.CONSTRAINT_NAME,D.COLUMN_NAME
     from dba_constraints c,dba_cons_columns d
    where D.table_name = c.table_name
      and d.owner = lvc_user
      and c.owner = lvc_user
      and c.CONSTRAINT_NAME = d.constraint_name
      and c.CONSTRAINT_TYPE = 'P'
      and C.TABLE_NAME = lvc_table;
*/
/*
cursor cur_pk is
SELECT DC_DCC.CONSTRAINT_NAME,DC_DCC.COLUMN_NAME,DT.TABLESPACE_NAME FROM
(
   select C.CONSTRAINT_NAME CONSTRAINT_NAME,D.COLUMN_NAME COLUMN_NAME,C.TABLE_NAME TABLE_NAME
     from dba_constraints c,dba_cons_columns d
    where D.table_name = c.table_name
      and d.owner = lvc_user
      and c.owner = lvc_user
      and c.CONSTRAINT_NAME = d.constraint_name
      and c.CONSTRAINT_TYPE = 'P'
      and C.TABLE_NAME = lvc_table
) DC_DCC LEFT OUTER JOIN DBA_TABLES DT ON DC_DCC.TABLE_NAME=DT.TABLE_NAME AND DT.OWNER=lvc_user;
*/
cursor cur_pk is
SELECT DC_DCC.CONSTRAINT_NAME,DC_DCC.COLUMN_NAME,DC_DCC.Position,DT.TABLESPACE_NAME FROM
(
   select C.CONSTRAINT_NAME CONSTRAINT_NAME,D.COLUMN_NAME COLUMN_NAME,D.Position Position,C.TABLE_NAME TABLE_NAME
     from dba_constraints c,dba_cons_columns d
    where D.table_name = c.table_name
      and d.owner = lvc_user
      and c.owner = lvc_user
      and c.CONSTRAINT_NAME = d.constraint_name
      and c.CONSTRAINT_TYPE = 'P'
      and C.TABLE_NAME = lvc_table
) DC_DCC LEFT OUTER JOIN DBA_TABLES DT ON DC_DCC.TABLE_NAME=DT.TABLE_NAME AND DT.OWNER=lvc_user ORDER BY DC_DCC.Position;


--修改完成


  cursor cur_index is
  --修改时间：2015-06-11 修改人：zhangruiu
  --select A.INDEX_TYPE,A.INDEX_NAME
  select A.tablespace_name,A.INDEX_TYPE,A.INDEX_NAME
  --修改完成
    from dba_indexes A
   where A.OWNER = lvc_user
     and A.TABLE_NAME = lvc_table
--修改时间：2015-06-10 修改人：zhangruiu
     and A.INDEX_TYPE <>'LOB';
--修改完成
  cursor cur_index_column is
  select B.COLUMN_NAME
    from DBA_IND_COLUMNS B
   where B.INDEX_NAME = lvc_index_name
     and B.TABLE_OWNER = lvc_user
     and B.TABLE_NAME = lvc_table;


  cursor cur_tmptab is
  select TEMPORARY,DURATION
    from dba_tables
   where owner = lvc_user
     and table_name = lvc_table;


begin


  lvc_user := upper(trim(avc_user));
  lvc_table := upper(trim(avc_table));


  --dbms_output.put_line(lvc_user||'_FILE_DIR');




  --0、输入合法性判断
  select count(*) into li_count
    from dba_tab_columns
   where table_name = lvc_table
     and owner = lvc_user;


  if li_count = 0 then
     raise e_tableerror;
  end if;


  --1、打开文件
  fhandle := utl_file.fopen(lvc_user||'_FILE_DIR',avc_table||'.sql', 'w');
  --fhandle := utl_file.fopen('J1_LDM_FILE_DIR',avc_table||'.sql', 'w');
  --dbms_output.put_line(lvc_user||'_FILE_DIR');


  --2、生成 建表脚本 设置表空间 信息
/*
set feedback off
define TABLESPACE_NAME=&1
define INDEX_TABLESPACE_NAME=&2
*/


  lvc_line := 'set feedback off';
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := 'define TABLESPACE_NAME='|| chr(38) ||'1;';
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := 'define INDEX_TABLESPACE_NAME='|| chr(38) ||'2;';
  utl_file.put_line(fhandle ,lvc_line);


  utl_file.put_line(fhandle ,'');


  --3、生成以下表头信息


/*
-- ============================================================
--   Table: INFT02_JBXX_ZFJG
--   汉字名称: INFT02_基本信息_总分机构
--   创建时间:
--   创建人:
--   修改日期:
--   修改人：
--   修改内容：
-- ============================================================
*/
  select comments into lvc_tab_comment
    from dba_tab_comments
   where table_name = lvc_table
     and owner = lvc_user;
  lvc_line := '-- ============================================================';
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := '--   Table:  '|| lvc_table;
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := '--   汉字名称:  '|| lvc_tab_comment;
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := '--   创建时间:';
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := '--   创建人:';
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := '--   修改日期:';
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := '--   修改人：';
  utl_file.put_line(fhandle ,lvc_line);


  lvc_line := '--   修改内容：';
  utl_file.put_line(fhandle ,lvc_line);


  utl_file.put_line(fhandle ,'');


  lvc_line := '-- ============================================================';
  utl_file.put_line(fhandle ,lvc_line);


  --3、生成 :create table 表名(


  select TEMPORARY,DURATION into lvc_temptab,lvc_duration
    from dba_tables
   where owner = lvc_user
     and table_name = lvc_table;
  --dbms_output.put_line('aaa'||','||lvc_user||','||lvc_user||','|| lvc_temptab );
  if lvc_temptab = 'N' then
     lvc_line := 'create table '||avc_table||'(';
  else
     lvc_line := 'create global temporary table '||avc_table||'(';
  end if;


  utl_file.put_line(fhandle ,lvc_line);




  --4、生成列信息：字段类型定义、字段长度、非空判断；
  li_i := 0;
  for cur in cur_coltype loop


     lvc_colname := rpad('    '||cur.COLUMN_NAME,40,' ');
     lvc_coltype := cur.col_type;
     lvc_colnull := cur.col_null;
     --修改人:zhangruiu 修改日期:2015-07-09
     if cur.data_default is not null then
     lvc_data_default := ' DEFAULT '||cur.data_default;
     ELSE
     lvc_data_default := '';     
     end if;
     --修改完成
     if lvc_colnull = 'NOT NULL' then
         lvc_colnull := ' '||lvc_data_default||lvc_colnull || ',';
     else
       --修改人：zhangruiu 修改时间:2015-07-13
         lvc_colnull := replace(lvc_data_default,chr(10),'')||',';
       --修改完成 
     end if;
     lvc_line := lvc_colname || lvc_coltype || lvc_colnull;
     li_i := li_i + 1;
     if li_i = li_count then
         lvc_line := substr(lvc_line,1,length(lvc_line) - 1);
     end if;
     utl_file.put_line(fhandle ,lvc_line);
  end loop;


  if lvc_temptab = 'N' then
      lvc_line := ') TABLESPACE '|| chr(38) || 'TABLESPACE_NAME;';
  else
      if lvc_duration = 'SYS$TRANSACTION' then
           lvc_line := ')   on commit delete rows;';
    elsif lvc_duration = 'SYS$SESSION' then
           lvc_line := ')   on commit preserve rows;';
    end if;
  end if;
  --lvc_line := ');';
  utl_file.put_line(fhandle ,lvc_line);


  utl_file.put_line(fhandle ,'');


  --5、生成创建表注释脚本；


  lvc_line := 'comment on table '||lvc_table||' is '''||lvc_tab_comment||''';';
  utl_file.put_line(fhandle ,lvc_line);




  --6、生成创建列注释脚本；
  utl_file.put_line(fhandle ,'');


  for cur in cur_colcomment loop
    lvc_line := 'comment on column ' || lvc_table || '.' || cur.column_name || ' is ''' || cur.comments ||''';';
    utl_file.put_line(fhandle ,lvc_line);
  end loop;


  --7、生成创建主键脚本；
  utl_file.put_line(fhandle ,'');


  lvc_pkname := '0';
  for cur in cur_pk loop
    lc_pkflag := 'Y';
    lvc_pkname := cur.CONSTRAINT_NAME;
    --修改时间：2015-06-11 修改人:zhangruiu
    lvc_tablespace_name :=cur.tablespace_name;
    --修改完成
    lvc_pk_column := lvc_pk_column ||cur.COLUMN_NAME||',';
  end loop;
--修  
  --8、生成创建索引脚本；
/*  脚本示例


create bitmap index IDX_LDMT02_JBXX_NSZHDJ_NSRDAH on LDMT02_JBXX_NSZHDJXX(NSRDAH)
 tablespace &TBS_IDX;


*/
  utl_file.put_line(fhandle ,'');


  lvc_index_column := '';
  for cur in cur_index loop
      lvc_tablespace_name :=cur.tablespace_name;
      lvc_index_name := cur.INDEX_NAME;
      lvc_index_type := cur.INDEX_type;
      lvc_index_column := '';
      --dbms_output.put_line(lvc_pkname||','||lvc_index_name);
      if lvc_index_name <> lvc_pkname  then
         --非主键索引，需要创建
           for cur_indcol in cur_index_column loop
               lvc_index_column := lvc_index_column || cur_indcol.COLUMN_NAME ||',';
           end loop;
           lvc_index_column := substr(lvc_index_column ,1,length(lvc_index_column)-1);
           lvc_line := 'create '||case when lvc_index_type = 'BITMAP' then 'BITMAP' end || ' index '|| lvc_index_name;
           --修改时间：2015-06-11 修改人：zhangruiu
           if lvc_tablespace_name is not null then
           --修改时间：2015-06-12 修改人：zhangruiu
           if lvc_index_type <>'FUNCTION-BASED NORMAL' then
             lvc_line := lvc_line ||' on '|| lvc_table ||'(' || lvc_index_column ||') tablespace '|| chr(38)||'INDEX_TABLESPACE_NAME;';
           else
           select die.COLUMN_EXPRESSION into lvc_lon from dba_ind_expressions die where die.INDEX_NAME = lvc_index_name;
             lvc_line := lvc_line ||' on '|| lvc_table ||'(' || replace(lvc_lon,chr(34),'') ||') tablespace '|| chr(38)||'INDEX_TABLESPACE_NAME;';
           end if ;
           --修改完成
           else
             lvc_line := lvc_line ||' on '|| lvc_table ||'(' || lvc_index_column ||');';
           end if;
           --修改完成
           utl_file.put_line(fhandle ,lvc_line);
      end if;
  end loop;  
--修  
  lvc_pk_column := substr(lvc_pk_column,1,length(lvc_pk_column)-1);


/* 脚本示例
  alter table LDMT04_SB_SDSNDNSSBBAL
  add constraint PK_LDMT04_SB_SDSNDNSSBBAL primary key (NSRZHDAH,PZXH,BDXH,SBXH) USING INDEX TABLESPACE &TBS_IDX;
*/


  if lc_pkflag = 'Y' then
      lvc_line := 'alter table '||lvc_table ;
      utl_file.put_line(fhandle ,lvc_line);
      --修改时间：2015-06-11 修改人:zhangruiu
      if lvc_tablespace_name is not null then
      lvc_line := 'add constraint '|| lvc_pkname || ' primary key ('|| lvc_pk_column ||') using INDEX TABLESPACE '|| chr(38) ||'INDEX_TABLESPACE_NAME;';
      else
      lvc_line := 'add constraint '|| lvc_pkname || ' primary key ('|| lvc_pk_column ||');';
      end if;
      utl_file.put_line(fhandle ,lvc_line);
  end if;


  --9、关闭文件。
  utl_file.fclose(fhandle);


exception
  when e_tableerror then
     raise_application_error(-20001,'用户名或表明错误，系统中不存在对应物理表');
  when others then
     utl_file.fclose(fhandle);
     raise_application_error(-20001,to_char(sqlcode) || sqlerrm);
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29802484/viewspace-1705450/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29802484/viewspace-1705450/