-----------------------------------------------
-- 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
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;
function func_getleft(
p_str varchar2,
p_len number
)return varchar2;
function func_ngetleft(
p_str nvarchar2,
p_len number
)return nvarchar2;
function func_getright(
p_str varchar2,
p_len number
)return varchar2;
function func_ngetright(
p_str nvarchar2,
p_len number
)return nvarchar2;
function func_getlastpart(
p_str varchar2, --字元
p_dot varchar2-- 分隔符
) return varchar2;
function func_ngetlastpart(
p_str nvarchar2, --字元
p_dot nvarchar2-- 分隔符
) return nvarchar2;
function func_getpriopart(
p_str varchar2, --字元
p_dot varchar2-- 分隔符
)return varchar2;
function func_ngetpriopart(
p_str nvarchar2, --字元
p_dot nvarchar2-- 分隔符
)return nvarchar2;
function func_getvalue(
p_str varchar2, --字元串
p_sign varchar2 --分隔符
) return t_getvalue;
function func_ngetvalue(
p_str nvarchar2, --字元串
p_sign nvarchar2 --分隔符
) return t_ngetvalue;
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;
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;
function func_getvalues(
p_tablename varchar2,
p_where varchar2
)return varchar2;
function func_ngetvalues(
p_tablename nvarchar2,
p_where nvarchar2
)return nvarchar2;
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
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;
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;
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;
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;
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;
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;
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;
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表中，也可以放到檔案中，具體調用請自己參照具體過程
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)