/*某些原因，删了一些自己用的函数，此包是我每次导数据老是用工具，如果要导特定几张表的话，如果用工具导入每次都找的够呛，所以花了一点时间写了此过程，如果发现问题或好的建议可以给我留言或者邮箱myrocket_2003@163.com
使用方法分为写文件和写表的方式，写文件需要先创建directory并需要相应的权限，写表则需要首先创建basedata表和seq_basedata序列
过程里面支持类型有number ,char,varchar2,date，如果有其他类型，请自己在过程里面添加，不支持大字段，对象类型，呵呵
调用过程也可以写在包里面，然后可以直接调用
*/
-----------------------------------------------
-- Export file for user SCOTT --
-- Created by lihong on 2009-09-13, 21:47:44 --
-----------------------------------------------
spool pkg_comm.log
prompt
prompt Creating package PKG_COMM
prompt =========================
prompt
create or replace package pkg_comm
as
/*
需要显式的给此用户赋予 权限
如grant create table to adc;
*/
e_error Exception;
type t_getvalue is table of varchar2(5000)
index by binary_integer;
type t_getinserts is table of varchar2(30000)
index by binary_integer;
type t_ngetvalue is table of varchar2(5000)
index by binary_integer;
type t_ngetinserts is table of varchar2(10000)
index by binary_integer;
/*
获得字符串的分隔符的前部分
select func_getleft('abcdefg',3) from dual
返回abc
*/
function func_getleft(
p_str varchar2,
p_len number
)return varchar2;
function func_ngetleft(
p_str nvarchar2,
p_len number
)return nvarchar2;
/*
获得字符的从右边开始的部分
select func_getright('abcde',3) from dual
返回 cde
*/
function func_getright(
p_str varchar2,
p_len number
)return varchar2;
function func_ngetright(
p_str nvarchar2,
p_len number
)return nvarchar2;
/*
获得最后一部分字符串
select func_getlastpart('/abc/def/ghi','/') from dual;
得到 ghi
*/
function func_getlastpart(
p_str varchar2, --字符
p_dot varchar2-- 分隔符
) return varchar2;
function func_ngetlastpart(
p_str nvarchar2, --字符
p_dot nvarchar2-- 分隔符
) return nvarchar2;
/*
获得字符串分隔符前面的字符串
如： abc/de 得到 abc
*/
function func_getpriopart(
p_str varchar2, --字符
p_dot varchar2-- 分隔符
)return varchar2;
function func_ngetpriopart(
p_str nvarchar2, --字符
p_dot nvarchar2-- 分隔符
)return nvarchar2;
/*
分离字符串 如'ab,cde,fgh,i' 分割为'ab' 'cde' 'fgh' 'i'
*/
function func_getvalue(
p_str varchar2, --字符串
p_sign varchar2 --分隔符
) return t_getvalue;
function func_ngetvalue(
p_str nvarchar2, --字符串
p_sign nvarchar2 --分隔符
) return t_ngetvalue;
/*
根据表名获得insert语句
*/
function func_getinserts(
p_tablename varchar2,
p_where varchar2:=' where 1=1'
)return t_getinserts;
function func_ngetinserts(
p_tablename nvarchar2,
p_where nvarchar2:=' where 1=1'
)return t_ngetinserts;
/*
将表中的数据打印成文本
调用前须确保以 sys登陆，create or replace directory aim_dir as '/home/oracle';
grant write on directory aim_dir to adc
做完后才可以调用此过程,调用的时候应该注意路径为大写
现在只提供每次一张表，如果打印多张表的请用p_writetext过程
将表中的数据打印成文本 p_type in ('append','write')
*/
procedure p_writetext(
p_path varchar2,
p_filename varchar2,
p_tablename varchar2,
p_where varchar2:=' where 1=1',
p_type varchar2,
p_errid out int
);
/*
将多个表一起打印成文本
*/
procedure p_writetext(
p_path varchar2,
p_filename varchar2,
p_tablename varchar2,
p_dot varchar2,
p_errid out int
);
--取得序列
function func_getnumber(
p_name varchar2
) return int;
/*根据表名取得表的所有字段*/
function func_getFields(
p_tablename varchar2
)return varchar2;
/*根据表名取得表的select语句，根据此语句可以取得表的值*/
function func_getvalues(
p_tablename varchar2,
p_where varchar2
)return varchar2;
function func_ngetvalues(
p_tablename nvarchar2,
p_where nvarchar2
)return nvarchar2;
/*
备份本地所有表到文件
注意运行此过程应该假设已经有read directory,write directory的权限
具体权限设置请参照p_writetext里面的说明
P_condition 含义是每张表里面的小于等于此数据的记录数才备份，如果为0 则备份所有的记录
*/
procedure p_exporttotext(
p_path varchar2,
P_condition int,
p_errid out int
);
/*
备份自己手工加的表，表名组成为：用户名_数据库名_日期
*/
procedure p_exportself(
p_path varchar2,
p_tablename varchar2,
p_dot varchar2,
p_errid out int
);
procedure p_writesql(
p_tablename nvarchar2,
p_where nvarchar2:=' where 1=1',
p_errid out int
);
procedure p_writesqls(
p_tablename nvarchar2,
p_dot nvarchar2,
p_errid out int
);
end pkg_comm;
/
prompt
prompt Creating package body PKG_COMM
prompt ==============================
prompt
create or replace package body pkg_comm
as
/*
获得字符串的分隔符的前部分
select pkg_comm.func_getleft('abcdefg',3) from dual
返回abc
*/
function func_getleft(
p_str varchar2,
p_len number
)
return varchar2
as
begin
if p_len>length(p_str) then
return p_str;
end if;
if p_len<=0 then
return '';
end if;
return substr(p_str,1,p_len);
exception
when others then
return p_str;
end;
function func_ngetleft(
p_str nvarchar2,
p_len number
)
return nvarchar2
as
begin
if p_len>length(p_str) then
return p_str;
end if;
if p_len<=0 then
return '';
end if;
return substr(p_str,1,p_len);
exception
when others then
return p_str;
end;
/*
获得字符的从右边开始的部分
select pkg_comm.func_getright('abcde',3) from dual
返回 cde
*/
function func_getright(
p_str varchar2,
p_len number
)
return varchar2
as
l_tmp varchar2(30000);
begin
if p_len>length(p_str) then
return p_str;
end if;
if p_len<=0 then
return '';
end if;
select reverse(substr(reverse(p_str),1,p_len)) into l_tmp from dual;
return l_tmp;
exception
when others then
return p_str;
end;
function func_ngetright(
p_str nvarchar2,
p_len number
)
return nvarchar2
as
l_tmp nvarchar2(10000);
begin
if p_len>length(p_str) then
return p_str;
end if;
if p_len<=0 then
return '';
end if;
select reverse(substr(reverse(p_str),1,p_len)) into l_tmp from dual;
return l_tmp;
exception
when others then
return p_str;
end;
/*
获得最后一部分字符串
select pkg_comm.func_getlastpart('/abc/def/ghi','/') from dual;
得到 ghi
*/
function func_getlastpart(
p_str varchar2, --字符
p_dot varchar2-- 分隔符
)
return varchar2
as
l_tmp varchar2(300);
l_dot varchar2(10);
begin
l_dot:=trim(p_dot);
if l_dot is null then
return p_str;
end if;
if instr(p_str,l_dot)=0 then
return p_str;
end if;
select reverse(pkg_comm.func_getleft(reverse(p_str),instr(reverse(p_str),reverse(l_dot))-1)) into l_tmp from dual;
return l_tmp;
exception
when others then
return p_str;
end;
function func_ngetlastpart(
p_str nvarchar2, --字符
p_dot nvarchar2-- 分隔符
)
return nvarchar2
as
l_tmp nvarchar2(300);
l_dot nvarchar2(10);
begin
l_dot:=trim(p_dot);
if l_dot is null then
return p_str;
end if;
if instr(p_str,l_dot)=0 then
return p_str;
end if;
select reverse(pkg_comm.func_ngetleft(reverse(p_str),instr(reverse(p_str),reverse(l_dot))-1)) into l_tmp from dual;
return l_tmp;
exception
when others then
return p_str;
end;
/*
获得字符串分隔符前面的字符串
如： abc/de 得到 abc
select pkg_comm.func_getlastpart('abc/de','/') from dual;
*/
function func_getpriopart(
p_str varchar2, --字符
p_dot varchar2-- 分隔符
)
return varchar2
as
l_tmp varchar2(300);
l_dot varchar2(10);
begin
l_dot:=trim(p_dot);
if l_dot is null then
return p_str;
end if;
if instr(p_str,l_dot)=0 then
return p_str;
end if;
select pkg_comm.func_getleft( p_str,instr(p_str,l_dot)-1) into l_tmp from dual;
return l_tmp;
exception
when others then
return p_str;
end;
function func_ngetpriopart(
p_str nvarchar2, --字符
p_dot nvarchar2-- 分隔符
)
return nvarchar2
as
l_tmp nvarchar2(300);
l_dot nvarchar2(10);
begin
l_dot:=trim(p_dot);
if l_dot is null then
return p_str;
end if;
if instr(p_str,l_dot)=0 then
return p_str;
end if;
select pkg_comm.func_ngetleft( p_str,instr(p_str,l_dot)-1) into l_tmp from dual;
return l_tmp;
exception
when others then
return p_str;
end;
/*
分离字符串 如'ab,cde,fgh,i' 分割为'ab' 'cde' 'fgh' 'i'
declare
l_facttable pkg_comm.t_getvalue;
l_index int;
begin
l_facttable:=pkg_comm.func_getvalue('a->b->c->d,e','->');
l_index:=l_facttable.first;
loop
exit when l_index is null;
if l_facttable(l_index)is not null then
dbms_output.put_line(l_facttable(l_index));
end if;
exit when l_index=l_facttable.last;
l_index:=l_facttable.next(l_index);
end loop;
end;
*/
function func_getvalue(
p_str varchar2, --字符串
p_sign varchar2 --分隔符
)
return t_getvalue
as
l_facttable t_getvalue;
l_pos number;
l_value varchar(30000);
l_low number;
l_str varchar2(30000);
begin
l_low:=1;
l_str:=p_str;
l_pos:=instr(l_str,p_sign);
while l_pos<>0 loop
l_value:=func_getleft(l_str,l_pos-1);
l_str:=substr(l_str,l_pos+length(p_sign),length(l_str)-l_pos);
l_pos:=instr(l_str,p_sign);
l_facttable(l_low):=l_value;
l_low:=l_low+1;
end loop;
l_facttable(l_low):=l_str;
return l_facttable;
end;
function func_ngetvalue(
p_str nvarchar2, --字符串
p_sign nvarchar2 --分隔符
)
return t_ngetvalue
as
l_facttable t_ngetvalue;
l_pos number;
l_value nvarchar2(10000);
l_low number;
l_str nvarchar2(10000);
begin
l_low:=1;
l_str:=p_str;
l_pos:=instr(l_str,p_sign);
while l_pos<>0 loop
l_value:=func_ngetleft(l_str,l_pos-1);
l_str:=substr(l_str,l_pos+length(p_sign),length(l_str)-l_pos);
l_pos:=instr(l_str,p_sign);
l_facttable(l_low):=l_value;
l_low:=l_low+1;
end loop;
l_facttable(l_low):=l_str;
return l_facttable;
end;
/*
根据表名获得insert语句，
暂时支持number,int,varchar2,char,date类型，其他类型暂时不支持
特别要注意字符串里面不能有引号等特殊字符
不支持含有大对象的表的导出
*/
function func_getinserts(
p_tablename varchar2,
p_where varchar2:=' where 1=1'
)
return t_getinserts
as
l_count int:=1;
l_str varchar2(10000);
type l_ref_cur is ref cursor;
l_cur l_ref_cur;
l_result varchar2(30000);
l_getinsert t_getinserts;
l_facttable t_getvalue;
l_index int;
l_fieldvalue varchar2(5000);
l_flag boolean;
l_values varchar2(30000);
begin
select count(*) into l_count from user_tab_columns
where table_name=p_tablename and DATA_TYPE in ('CLOB','BLOB','LONG','LONG RAW','RAW','ROWID','UROWID');
if l_count>0 then
l_getinsert(1):='';
return l_getinsert;
end if;
l_count:=1;
l_str:=func_getvalues(p_tablename,p_where );
open l_cur for l_str;
loop
fetch l_cur into l_result;
exit when l_cur%notfound;
/**************添加了对特殊字符的处理******************/
l_values:='';
l_facttable:=func_getvalue(l_result,chr(8));
l_index:=l_facttable.first;
loop
exit when l_index is null;
l_fieldvalue:=l_facttable(l_index);
if l_fieldvalue is not null then
l_flag:=false;
if func_getleft(l_fieldvalue,1)='''' then
l_fieldvalue:=func_getright(l_fieldvalue,length(l_fieldvalue)-1);
l_flag:=true;
end if;
if func_getright(l_fieldvalue,1)='''' then
l_fieldvalue:=func_getleft(l_fieldvalue,length(l_fieldvalue)-1);
l_flag:=true;
end if;
if l_flag then
l_fieldvalue:=replace(l_fieldvalue,'''','''''');
l_fieldvalue:=''''||l_fieldvalue||'''';
end if;
l_values:=l_values||','||l_fieldvalue;
end if;
exit when l_index=l_facttable.last;
l_index:=l_facttable.next(l_index);
end loop;
/********************************/
if l_values is not null then
l_values:=func_getright(l_values,length(l_values)-1);
end if;
l_str:='INSERT INTO '||p_tablename||' ('||func_getFields(p_tablename)
||') VALUES('||l_values||')';
l_getinsert(l_count):=l_str;
l_count:=l_count+1;
end loop;
close l_cur;
-- dbms_output.put_line(l_count);
return l_getinsert;
exception
when others then
if l_cur%isopen then
close l_cur;
end if;
raise;
l_getinsert(1):='';
return l_getinsert;
end;
function func_ngetinserts(
p_tablename nvarchar2,
p_where nvarchar2:=' where 1=1'
)
return t_ngetinserts
as
l_count int:=1;
l_str varchar2(10000);
type l_ref_cur is ref cursor;
l_cur l_ref_cur;
l_result nvarchar2(10000);
l_getinsert t_ngetinserts;
l_facttable t_ngetvalue;
l_index int;
l_fieldvalue nvarchar2(5000);
l_flag boolean;
l_values nvarchar2(10000);
begin
select count(*) into l_count from user_tab_columns
where table_name=p_tablename and DATA_TYPE in ('CLOB','BLOB','LONG','LONG RAW','RAW','ROWID','UROWID');
if l_count>0 then
l_getinsert(1):='';
return l_getinsert;
end if;
l_count:=1;
l_str:=func_ngetvalues(p_tablename,p_where );
open l_cur for l_str;
loop
fetch l_cur into l_result;
exit when l_cur%notfound;
/**************添加了对特殊字符的处理******************/
l_values:='';
l_facttable:=func_ngetvalue(l_result,chr(8));
l_index:=l_facttable.first;
loop
exit when l_index is null;
l_fieldvalue:=l_facttable(l_index);
if l_fieldvalue is not null then
l_flag:=false;
if func_ngetleft(l_fieldvalue,1)='''' then
l_fieldvalue:=func_ngetright(l_fieldvalue,length(l_fieldvalue)-1);
l_flag:=true;
end if;
if func_ngetright(l_fieldvalue,1)='''' then
l_fieldvalue:=func_ngetleft(l_fieldvalue,length(l_fieldvalue)-1);
l_flag:=true;
end if;
if l_flag then
l_fieldvalue:=replace(l_fieldvalue,'''','''''');
l_fieldvalue:=''''||l_fieldvalue||'''';
end if;
l_values:=l_values||','||l_fieldvalue;
end if;
exit when l_index=l_facttable.last;
l_index:=l_facttable.next(l_index);
end loop;
/********************************/
if l_values is not null then
l_values:=func_ngetright(l_values,length(l_values)-1);
end if;
l_str:='INSERT INTO '||p_tablename||' ('||func_getFields(p_tablename)
||') VALUES('||l_values||')';
l_getinsert(l_count):=l_str;
-- dbms_output.put_line(substr(l_str,1,200));
l_count:=l_count+1;
end loop;
close l_cur;
-- dbms_output.put_line(l_count);
return l_getinsert;
exception
when others then
if l_cur%isopen then
close l_cur;
end if;
raise;
l_getinsert(1):='';
return l_getinsert;
end;
/*
将表中的数据打印成文本
调用前须确保以 sys登陆，create or replace directory eidc_dir as '/home/oracle/ora_data_backup'
grant read on directory eidc_dir to adc
grant write on directory eidc_dir to adc
做完后才可以调用此过程,调用的时候应该注意路径为大写
现在只提供每次一张表，如果打印多张表的请用p_writetexts过程
将表中的数据打印成文本 p_type in ('append','write')
如果是append 的时候必须保证有此文件才能够append否则报错
*/
procedure p_writetext(
p_path varchar2,
p_filename varchar2,
p_tablename varchar2,
p_where varchar2:=' where 1=1',
p_type varchar2,
p_errid out int
)
as
l_fileid utl_file.file_type;
l_index int;
l_getinserts t_getinserts;
l_type char(1);
l_count int;
begin
p_errid:=0;
if upper(p_type)='WRITE' then
l_type:='w';
else
l_type:='a';
end if;
l_count:=1;
l_getinserts:=func_getinserts(p_tablename,p_where);
l_fileid:=utl_file.fopen(p_path,p_filename,l_type,30000);
l_index:=l_getinserts.first;
loop
exit when l_index is null;
if l_count=1 then
utl_file.put_line(l_fileid,'prompt 正在装载表'||p_tablename||'数据......');
end if;
if l_getinserts(l_index) is not null then
utl_file.put_line(l_fileid,l_getinserts(l_index)||';');
end if;
exit when l_index=l_getinserts.last;
l_count:=l_count+1;
l_index:=l_getinserts.next(l_index);
end loop;
utl_file.put_line(l_fileid,'commit;');
utl_file.put_line(l_fileid,'prompt 共装载'||to_char(l_count)||'条记录');
utl_file.fflush(l_fileid);
utl_file.fclose(l_fileid);
exception
when others then
if utl_file.is_open(l_fileid) then
utl_file.fclose(l_fileid);
end if;
raise;
p_errid:=sqlcode;
raise;
return;
end;
/*
将多个表一起打印成文本,如果是多个表，请用割开符号隔开单个表直接写表名，
此时打印的表为所有记录，如果有条件的记录，请到其他的过程去打印
*/
procedure p_writetext(
p_path varchar2,
p_filename varchar2,
p_tablename varchar2,
p_dot varchar2,
p_errid out int
)
as
l_facttable pkg_comm.t_getvalue;
l_index int;
l_fileid utl_file.file_type;
l_osuser varchar2(30);
l_dbname varchar2(30);
l_charset varchar2(60);
l_tblname varchar2(30);
l_client_charset varchar2(60);
begin
p_errid:=0;
begin
select sys_context('USERENV','os_user'),sys_context('USERENV','db_name'),sys_context('USERENV','LANGUAGE')
into l_osuser,l_dbname,l_client_charset from dual;
exception
when no_data_found then
l_osuser:='';
l_dbname:='';
l_client_charset:='';
end;
select a.value||'_'||b.value||'.'||c.value charset
into l_charset
from nls_database_parameters a,
nls_database_parameters b,
nls_database_parameters c
where a.parameter='NLS_LANGUAGE'
and b.parameter='NLS_TERRITORY'
and c.parameter='NLS_CHARACTERSET';
l_facttable:=func_getvalue(p_tablename,p_dot);
l_fileid:=utl_file.fopen(p_path,p_filename,'w');
utl_file.put_line(l_fileid,'prompt 创建时间:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
utl_file.put_line(l_fileid,'prompt 脚本创建人:'||l_osuser);
utl_file.put_line(l_fileid,'prompt 数据库名:'||l_dbname);
utl_file.put_line(l_fileid,'prompt 数据库字符集:'||l_charset);
utl_file.put_line(l_fileid,'prompt 客户端字符集:'||l_client_charset);
utl_file.put_line(l_fileid,'set feedback off;');
utl_file.put_line(l_fileid,'set define off;');
utl_file.put_line(l_fileid,'');
l_index:=l_facttable.first;
loop
exit when l_index is null;
l_tblname:=trim(replace(l_facttable(l_index),chr(10),' '));
if l_tblname is not null then
utl_file.put_line(l_fileid,'prompt 删除表:'||l_tblname||'的数据');
utl_file.put_line(l_fileid,'truncate table '||l_tblname||'; ');
-- dbms_output.put_line(l_facttable(l_index));
end if;
exit when l_index=l_facttable.last;
l_index:=l_facttable.next(l_index);
end loop;
utl_file.fflush(l_fileid);
utl_file.fclose(l_fileid);
l_index:=l_facttable.first;
loop
exit when l_index is null;
l_tblname:=trim(replace(l_facttable(l_index),chr(10),' '));
if l_tblname is not null then
dbms_output.put_line(l_facttable(l_index));
p_writetext(p_path,p_filename,l_tblname,' where 1=1 ','append',p_errid);
end if;
exit when l_index=l_facttable.last;
l_index:=l_facttable.next(l_index);
end loop;
l_fileid:=utl_file.fopen(p_path,p_filename,'a');
utl_file.put_line(l_fileid,'set feedback on;');
utl_file.put_line(l_fileid,'set define on;');
utl_file.put_line(l_fileid,'prompt 装载数据完毕;');
utl_file.fflush(l_fileid);
utl_file.fclose(l_fileid);
exception
when others then
if utl_file.is_open(l_fileid) then
utl_file.fclose(l_fileid);
end if;
raise;
p_errid:=sqlcode;
end;
--取得序列
function func_getnumber(
p_name varchar2 --序列名称
)
return int
as
l_str varchar2(1000);
l_seqname varchar2(100);
l_count int;
l_result int;
begin
l_seqname:='pseq_'||p_name;
l_str:='select object_name from user_objects '
||' where object_type=''SEQUENCE'' and status=''VALID'''
||' and object_name = upper('''||l_seqname||''')';
select count(SEQUENCE_NAME) into l_count from user_sequences a
where a.sequence_name =upper(l_seqname);
if l_count=0 then
l_str:='create sequence '||l_seqname||' increment by 1
start with 1 nomaxvalue nocycle cache 10';
execute immediate l_str;
end if;
l_str:='select '||rtrim(l_seqname)||'.nextval from dual';
execute immediate l_str into l_result;
return l_result;
exception
when others then
return 0;
end;
--取得表的字段
function func_getFields(
p_tablename varchar2
)
return varchar2
as
l_result varchar2(1000);
begin
for l_field in (select COLUMN_NAME from user_tab_columns
where TABLE_NAME=upper(p_tablename) order by COLUMN_ID) loop
l_result:=l_result||','||l_field.COLUMN_NAME;
end loop;
if l_result is not null then
return func_getright(l_result,length(l_result)-1);
else
return '';
end if;
exception
when others then
return '';
end;
--取得表的值
function func_getvalues(
p_tablename varchar2,
p_where varchar2
)
return varchar2
as
l_result varchar2(30000);
l_tmp varchar2(30000);
begin
for l_field in (
select 'decode( '||column_name ||' , null , ''NULL'' , '
||decode(DATA_TYPE,'NUMBER',column_name,
'INT',column_name,
'CHAR',chr(39)||chr(39)||chr(39)||chr(39)||'||'||column_name||'||'||chr(39)||chr(39)||chr(39)||chr(39),
'VARCHAR2',chr(39)||chr(39)||chr(39)||chr(39)||'||'||column_name||'||'||chr(39)||chr(39)||chr(39)||chr(39),
'DATE','''to_date(''''''||to_char('||column_name||',''yyyy-mm-dd hh24:mi:ss'')||'||''''''',''''yyyy-mm-dd hh24:mi:ss'''')'''
)||' )' col_name
from user_tab_columns
where table_name=upper(p_tablename)
order by column_id) loop
l_result:=l_result||'||'||'chr(8)'||'||'||l_field.col_name;
end loop;
if l_result is not null then
l_tmp:= func_getright(l_result,length(l_result)-10);
l_result:='select '||l_tmp
||' from '||p_tablename||' '||p_where;
return l_result;
else
return '';
end if;
exception
when others then
return '';
end;
function func_ngetvalues(
p_tablename nvarchar2,
p_where nvarchar2
)
return nvarchar2
as
l_result nvarchar2(10000);
l_tmp nvarchar2(10000);
begin
for l_field in (
select 'decode( '||column_name ||' , null , ''NULL'' , '
||decode(DATA_TYPE,'NUMBER',column_name,
'INT',column_name,
'CHAR',chr(39)||chr(39)||chr(39)||chr(39)||'||'||column_name||'||'||chr(39)||chr(39)||chr(39)||chr(39),
'VARCHAR2',chr(39)||chr(39)||chr(39)||chr(39)||'||'||column_name||'||'||chr(39)||chr(39)||chr(39)||chr(39),
'DATE','''to_date(''''''||to_char('||column_name||',''yyyy-mm-dd hh24:mi:ss'')||'||''''''',''''yyyy-mm-dd hh24:mi:ss'''')'''
)||' )' col_name
from user_tab_columns
where table_name=upper(p_tablename)
order by column_id) loop
l_result:=l_result||'||'||'chr(8)'||'||'||l_field.col_name;
end loop;
if l_result is not null then
l_tmp:= func_ngetright(l_result,length(l_result)-10);
l_result:='select '||l_tmp
||' from '||p_tablename||' '||p_where
||' order by 1';
return l_result;
else
return '';
end if;
exception
when others then
return '';
end;
/*
备份本地所有表到文件
注意运行此过程应该假设已经有read directory,write directory的权限
具体权限设置请参照p_writetext里面的说明
如果有大对象的表备份不了,会跳过含有大对象的表
P_condition 含义是每张表里面的小于等于此数据的记录数才备份，如果为0 则备份所有的记录
*/
procedure p_exporttotext(
p_path varchar2,
P_condition int,
p_errid out int
)
as
l_count int;
l_str varchar2(1000);
l_tmpname varchar2(1000);
l_tmpfilename varchar2(100);
begin
p_errid:=0;
l_tmpfilename:='eidc'||to_char(sysdate,'yyyymmddhh24miss')||'.txt';
for l_table in (select table_name from user_tables
where table_name not in
(select table_name from user_tab_columns
where DATA_TYPE in ('BLOB','CLOB','LONG','LONG RAW'))) loop
l_str:='select count(1) from '||l_table.table_name||' ';
execute immediate l_str into l_count;
if P_condition<=0 then
if l_count>0 then
l_tmpname:=l_tmpname||','||l_table.table_name;
end if;
else
if l_count>0 and l_count<=P_condition then
l_tmpname:=l_tmpname||','||l_table.table_name;
end if;
end if;
end loop;
if l_tmpname is not null then
l_tmpname:=pkg_comm.func_getright(l_tmpname,length(l_tmpname)-1);
pkg_comm.p_writetext(p_path,l_tmpfilename,l_tmpname,',',P_ERRID);
if p_errid<>0 then
dbms_output.put_line('error');
end if;
end if;
exception
when others then
p_errid:=sqlcode;
raise;
return;
end;
/*
备份自己手工加的表，表名组成为：用户名_数据库名_日期
*/
procedure p_exportself(
p_path varchar2,
p_tablename varchar2,
p_dot varchar2,
p_errid out int
)
as
l_osuser varchar2(30);
l_tmpfilename varchar2(100);
begin
p_errid:=0;
begin
select sys_context('USERENV','os_user')
into l_osuser from dual;
exception
when no_data_found then
l_osuser:='';
end;
l_tmpfilename:=l_osuser||'_N3'||'_eidc'||to_char(sysdate,'yyyymmddhh24miss')||'.txt';
p_writetext(p_path,l_tmpfilename,p_tablename,p_dot,p_errid);
if p_errid<>0 then
dbms_output.put_line('error');
end if;
exception
when others then
p_errid:=sqlcode;
raise;
return;
end;
procedure p_writesql(
p_tablename nvarchar2,
p_where nvarchar2:=' where 1=1',
p_errid out int
)
as
l_index int;
l_getinserts t_ngetinserts;
l_count int;
begin
p_errid:=0;
l_count:=1;
l_getinserts:=func_ngetinserts(p_tablename,p_where);
l_index:=l_getinserts.first;
loop
exit when l_index is null;
if l_count=1 then
insert into basedata values(seq_basedata.nextval,'prompt 正在装载表'||p_tablename||'数据......');
end if;
if l_getinserts(l_index) is not null then
insert into basedata values(seq_basedata.nextval,l_getinserts(l_index)||';');
end if;
exit when l_index=l_getinserts.last;
l_count:=l_count+1;
l_index:=l_getinserts.next(l_index);
end loop;
insert into basedata values(seq_basedata.nextval,'commit;');
insert into basedata values(seq_basedata.nextval,'prompt 共装载'||to_char(l_count)||'条记录');
commit;
exception
when others then
raise;
p_errid:=sqlcode;
raise;
return;
end;
procedure p_writesqls(
p_tablename nvarchar2,
p_dot nvarchar2,
p_errid out int
)
as
l_facttable pkg_comm.t_ngetvalue;
l_index int;
l_osuser nvarchar2(30);
l_dbname nvarchar2(30);
l_charset nvarchar2(60);
l_tblname nvarchar2(30);
l_str varchar2(100);
begin
p_errid:=0;
begin
select sys_context('USERENV','os_user'),sys_context('USERENV','db_name')
into l_osuser,l_dbname from dual;
exception
when no_data_found then
l_osuser:='';
l_dbname:='';
end;
select a.value||'_'||b.value||'.'||c.value charset
into l_charset
from nls_database_parameters a,
nls_database_parameters b,
nls_database_parameters c
where a.parameter='NLS_LANGUAGE'
and b.parameter='NLS_TERRITORY'
and c.parameter='NLS_CHARACTERSET';
l_str:='truncate table basedata';
execute immediate l_str;
l_facttable:=func_ngetvalue(p_tablename,p_dot);
insert into basedata values(seq_basedata.nextval,'prompt 创建时间:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
insert into basedata values(seq_basedata.nextval,'prompt 脚本创建人:'||l_osuser);
insert into basedata values(seq_basedata.nextval,'prompt 数据库名:'||l_dbname);
insert into basedata values(seq_basedata.nextval,'prompt 数据库字符集:'||l_charset);
insert into basedata values(seq_basedata.nextval,'set feedback off;');
insert into basedata values(seq_basedata.nextval,'set define off;');
insert into basedata values(seq_basedata.nextval,'');
l_index:=l_facttable.first;
loop
exit when l_index is null;
l_tblname:=trim(replace(l_facttable(l_index),chr(10),' '));
if l_tblname is not null then
insert into basedata values(seq_basedata.nextval,'prompt 删除表:'||l_tblname||'的数据');
insert into basedata values(seq_basedata.nextval,'truncate table '||l_tblname||'; ');
end if;
exit when l_index=l_facttable.last;
l_index:=l_facttable.next(l_index);
end loop;
l_index:=l_facttable.first;
loop
exit when l_index is null;
l_tblname:=trim(replace(l_facttable(l_index),chr(10),' '));
if l_tblname is not null then
p_writesql(l_tblname,' where 1=1 ',p_errid);
end if;
exit when l_index=l_facttable.last;
l_index:=l_facttable.next(l_index);
end loop;
insert into basedata values(seq_basedata.nextval,'set feedback on;');
insert into basedata values(seq_basedata.nextval,'set define on;');
insert into basedata values(seq_basedata.nextval,'prompt 装载数据完毕;');
commit;
exception
when others then
rollback;
p_errid:=sqlcode;
raise;
return;
end;
end pkg_comm;
/
spool off
执行方法如：
declare
l_errid int;
l_tbname varchar2(500);
begin
l_tbname:='aaa';
pkg_comm.p_writesqls(l_tbname,',',l_errid);
if l_errid<>0 then
dbms_output.put_line('请检查错误');
end if;
end;
此时是把数据写入到basedata表中，也可以放到文件中，具体调用请自己参照具体过程
1010
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
