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;