读取远程表并逆向生成建表语句

create or replace procedure tab_reverse_create(v_tab_name varchar2,v_data_link varchar2 default null)
as
	type cur_type is ref cursor;
	cur_policy cur_type;
	
	rec_utcl user_tab_columns%rowtype;
	rec_utcm user_tab_comments%rowtype;
	rec_uccm user_col_comments%rowtype;
	rec_ucns user_constraints%rowtype;
	rec_uccl user_cons_columns%rowtype;
	rec_uind user_indexes%rowtype;
	rec_uicl user_ind_columns%rowtype;
	
	n_count number default 0;--计数器
	v_at_affix varchar2(50) default null;--数据链词缀
	v_type_affix varchar2(500) default null;--字段类型词缀
	v_cons_p_name varchar2(500) default null;--主键约束名称
	v_cons_p_list varchar2(500) default null;--主键约束列表
	v_column_list varchar2(4000) default null;--字段声明列表
	
begin
	--检查表是否存在
	n_count := 0;
	select count(1) into n_count from user_tables where table_name=upper(v_tab_name);
	if n_count > 0 then
		execute immediate 'drop table '||v_tab_name||' purge';--删除已存在表
	end if;
	
	--处理数据链词缀
	if v_data_link is not null then
		v_at_affix := '@'||v_data_link;
	end if;
	
	/* ---------- 生成建表语句 ---------- */
	/* 备注: chr(9) 制表符,chr(10) 回车符,chr(13) 换行符,chr(32) 空格符 */
	open cur_policy for 'select 
							table_name,
							column_name,
							data_type,
							char_length,
							data_length,
							data_precision,
							data_scale,
							nullable 
						from user_tab_columns'||v_at_affix||' 
						where table_name=upper('''||v_tab_name||''') 
						order by column_id';
	--重置相关变量
	v_type_affix := null;
	v_column_list := null;
	n_count := 0;
	loop fetch cur_policy into 
							rec_utcl.table_name,
							rec_utcl.column_name,
							rec_utcl.data_type,
							rec_utcl.char_length,
							rec_utcl.data_length,
							rec_utcl.data_precision,
							rec_utcl.data_scale,
							rec_utcl.nullable;
		exit when cur_policy%notfound;
		--根据字段类型生成相应字段词缀
		if rec_utcl.data_type='NVARCHAR2' then
			v_type_affix := 'NVARCHAR2('||rec_utcl.char_length||')';
		elsif rec_utcl.data_type='VARCHAR2' then/* 注意是 elsif 不是 elseif */
			v_type_affix := 'VARCHAR2('||rec_utcl.data_length||')';
		elsif rec_utcl.data_type='CHAR' then
			v_type_affix := 'CHAR('||rec_utcl.data_length||')';
		elsif rec_utcl.data_type='NUMBER' then
			if rec_utcl.data_precision is null then
				--先判断 data_precision 为空
				v_type_affix := 'NUMBER';
			elsif rec_utcl.data_scale=0 then
				--再判断 data_scale 为0
				v_type_affix := 'NUMBER('||rec_utcl.data_precision||')';
			else
				--最后处理一般情况
				v_type_affix := 'NUMBER('||rec_utcl.data_precision||','||rec_utcl.data_scale||')';
			end if;
		elsif rec_utcl.data_type='FLOAT' then
			v_type_affix := 'FLOAT';
		elsif rec_utcl.data_type='DATE' then
			v_type_affix := 'DATE';
		end if;
		--拼接上一条记录逗号
		v_column_list := case when n_count!=0 then v_column_list||','||chr(13) else null end;
		--拼接字段声明
		v_column_list := v_column_list||chr(9)||rec_utcl.column_name||chr(32)||v_type_affix||case when rec_utcl.nullable='Y' then null else chr(32)||'not null' end;
		--记录执行成功数
		n_count := n_count+1;
	end loop;
	--拼接建表语句
	if v_column_list is not null then
		dbms_output.put_line('create table '||v_tab_name||' ('||chr(13)||v_column_list||chr(13)||')');--输出建表语句
		execute immediate 'create table '||v_tab_name||' ('||chr(13)||v_column_list||chr(13)||')';--执行建表语句
	end if;
	close cur_policy;
	
	/* ---------- 生成表备注 ---------- */
	open cur_policy for 'select 
							table_name,
							comments 
						from user_tab_comments'||v_at_affix||' 
						where table_name=upper('''||v_tab_name||''')';
	loop fetch cur_policy into 
							rec_utcm.table_name,
							rec_utcm.comments;
		exit when cur_policy%notfound;
		if rec_utcm.comments is not null then
			dbms_output.put_line('comment on table '||v_tab_name||' is '''||rec_utcm.comments||'''');--输出表备注
			execute immediate 'comment on table '||v_tab_name||' is '''||rec_utcm.comments||'''';--创建表备注
		end if;
	end loop;
	close cur_policy;
	
	/* ---------- 生成列备注 ---------- */
	open cur_policy for '
						select 
							utc.table_name,
							utc.column_name,
							ucc.comments 
						from user_tab_columns'||v_at_affix||' utc 
						left join user_col_comments'||v_at_affix||' ucc 
						on utc.table_name=ucc.table_name and utc.column_name=ucc.column_name 
						where utc.table_name=upper('''||v_tab_name||''') 
						order by utc.column_id';
	loop fetch cur_policy into 
							rec_utcl.table_name,
							rec_utcl.column_name,
							rec_uccm.comments;
		exit when cur_policy%notfound;
		if rec_uccm.comments is not null then
			dbms_output.put_line('comment on column '||v_tab_name||'.'||rec_utcl.column_name||' is '''||rec_uccm.comments||'''');--输出列备注
			execute immediate 'comment on column '||v_tab_name||'.'||rec_utcl.column_name||' is '''||rec_uccm.comments||'''';--创建列备注
		end if;
	end loop;
	close cur_policy;
	
	/* ---------- 生成非外键约束 ---------- */
	open cur_policy for 'select 
							uc.index_name,
							uc.constraint_name,
							uc.constraint_type,
							uc.search_condition,
							ucc.column_name 
						from user_constraints'||v_at_affix||' uc 
						join user_cons_columns'||v_at_affix||' ucc 
						on uc.constraint_name=ucc.constraint_name 
						where uc.table_name=upper('''||v_tab_name||''') and uc.constraint_name not like ''%SYS_%'' 
						order by uc.constraint_type,uc.constraint_name';
	--重置相关变量
	v_cons_p_list := null;
	n_count := 0;
	loop fetch cur_policy into 
							rec_ucns.index_name,
							rec_ucns.constraint_name,
							rec_ucns.constraint_type,
							rec_ucns.search_condition,
							rec_uccl.column_name;
		exit when cur_policy%notfound;
		--根据约束类型生成相应语句
		if rec_ucns.constraint_type='C' then--条件约束
			dbms_output.put_line('alter table '||v_tab_name||' add constraint '||rec_ucns.constraint_name||' check ('||rec_ucns.search_condition||')');--输出条件约束语句
			execute immediate 'alter table '||v_tab_name||' add constraint '||rec_ucns.constraint_name||' check ('||rec_ucns.search_condition||')';--创建条件约束
		elsif rec_ucns.constraint_type='P' then--主键约束(带索引)
			--存储主键约束名
			v_cons_p_name := rec_ucns.constraint_name;
			--拼接主键约束列列名,同时拼接上一条记录逗号
			v_cons_p_list := case when n_count!=0 then v_cons_p_list||', '||rec_uccl.column_name else rec_uccl.column_name end;
			--记录执行成功数
			n_count := n_count+1;
		elsif rec_ucns.constraint_type='U' then--唯一约束(带索引)
			dbms_output.put_line('alter table '||v_tab_name||' add constraint '||rec_ucns.constraint_name||' unique ('||rec_uccl.column_name||') using index');--输出唯一约束语句
			execute immediate 'alter table '||v_tab_name||' add constraint '||rec_ucns.constraint_name||' unique ('||rec_uccl.column_name||') using index';--创建唯一约束
		end if;
	end loop;
	--拼接主键约束语句
	if v_cons_p_list is not null then
		dbms_output.put_line('alter table '||v_tab_name||' add constraint '||v_cons_p_name||' primary key ('||v_cons_p_list||') using index');--输出主键约束语句
		execute immediate 'alter table '||v_tab_name||' add constraint '||v_cons_p_name||' primary key ('||v_cons_p_list||') using index';--创建主键约束
	end if;
	close cur_policy;
	
	/* ---------- 生成外键约束 ---------- */
	open cur_policy for 'select 
							uc.constraint_name,
							ucc.column_name,
							ui.table_name,
							uic.column_name 
						from user_constraints'||v_at_affix||' uc 
						join user_cons_columns'||v_at_affix||' ucc 
						on uc.constraint_name=ucc.constraint_name 
						join user_indexes'||v_at_affix||' ui 
						on uc.r_constraint_name=ui.index_name 
						join user_ind_columns'||v_at_affix||' uic 
						on ui.index_name = uic.index_name 
						where uc.table_name=upper('''||v_tab_name||''') and uc.constraint_type=''R'' 
						order by uc.constraint_name';
	loop fetch cur_policy into 
							rec_ucns.constraint_name,
							rec_uccl.column_name,
							rec_uind.table_name,
							rec_uicl.column_name;
		exit when cur_policy%notfound;
		--外键约束,每条外键约束都要单独拼接
		dbms_output.put_line('alter table '||v_tab_name||' add constraint '||rec_ucns.constraint_name||' foreign key ('||rec_uccl.column_name||') references '||rec_uind.table_name||' ('||rec_uicl.column_name||')');--输出外键约束语句
		execute immediate 'alter table '||v_tab_name||' add constraint '||rec_ucns.constraint_name||' foreign key ('||rec_uccl.column_name||') references '||rec_uind.table_name||' ('||rec_uicl.column_name||')';--创建外键约束
	end loop;
	close cur_policy;
	
end tab_reverse_create;