清楚很简单,truncate table test 或者 delete table test 但你怎么导入数据呀,既然是批处理,那就是说数据以文件的形式保存在外部?
--这是tom写的,为表的每一行数据生成一条insert sql --已知问题:她不区分数据类型,插入时由oracle来自动转换 --更好的工具:pl/sql developercreate or replace procedure dump_tab_to_sql ( p_tname in varchar2, p_nls_date_format varchar2 := 'yyyy-mm-dd hh24:mi:ss' ) authid current_user /* <<<== if you want... runs as "invoker" and runs with ROLES */ is
l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2(4000); l_status integer; l_query varchar2(1000) default 'select * from ' || p_tname; l_colCnt number := 0; l_separator varchar2(2); l_descTbl dbms_sql.desc_tab; l_text long; begin execute immediate 'alter session set nls_date_format=''' || p_nls_date_format || ''''; dbms_output.put_line( 'alter session set nls_date_format=''' || p_nls_date_format || ''';' ); dbms_output.put_line( 'alter session set cursor_sharing=force;' ); dbms_sql.parse( l_theCursor, l_query, dbms_sql.native ); dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); l_text := 'insert into ' || p_tname || ' ('; for i in 1 .. l_colCnt loop l_text := l_text || l_separator || '"' || l_descTbl(i).col_name || '"'; dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 ); l_separator := ', '; end loop; l_text := l_text || ') values ('; l_status := dbms_sql.execute(l_theCursor); while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop l_separator := ''; dbms_output.put( l_text ); for i in 1 .. l_colCnt loop dbms_sql.column_value( l_theCursor, i, l_columnValue ); if ( l_columnValue is not null ) then dbms_output.put( l_separator || '''' || replace(l_columnValue, '''', '''''' ) || '''' ); else dbms_output.put( l_separator || 'NULL' ); end if; l_separator := ', '; end loop; dbms_output.put_line( ');' ); end loop; dbms_sql.close_cursor(l_theCursor);
dbms_output.put_line('commit;'); exception when others then raise; end; /
但你怎么导入数据呀,既然是批处理,那就是说数据以文件的形式保存在外部?
--已知问题:她不区分数据类型,插入时由oracle来自动转换
--更好的工具:pl/sql developercreate or replace procedure dump_tab_to_sql
( p_tname in varchar2,
p_nls_date_format varchar2 := 'yyyy-mm-dd hh24:mi:ss'
)
authid current_user /* <<<== if you want... runs as "invoker" and runs with ROLES */
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(2);
l_descTbl dbms_sql.desc_tab;
l_text long;
begin
execute immediate
'alter session set nls_date_format=''' || p_nls_date_format || '''';
dbms_output.put_line( 'alter session set nls_date_format=''' || p_nls_date_format || ''';' );
dbms_output.put_line( 'alter session set cursor_sharing=force;' ); dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); l_text := 'insert into ' || p_tname || ' (';
for i in 1 .. l_colCnt
loop
l_text := l_text || l_separator || '"' || l_descTbl(i).col_name || '"';
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ', ';
end loop;
l_text := l_text || ') values ('; l_status := dbms_sql.execute(l_theCursor); while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
l_separator := '';
dbms_output.put( l_text );
for i in 1 .. l_colCnt
loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
if ( l_columnValue is not null )
then
dbms_output.put( l_separator || '''' || replace(l_columnValue, '''', ''''''
) || '''' );
else
dbms_output.put( l_separator || 'NULL' );
end if;
l_separator := ', ';
end loop;
dbms_output.put_line( ');' );
end loop;
dbms_sql.close_cursor(l_theCursor);
dbms_output.put_line('commit;');
exception
when others then
raise;
end;
/
---------------------
truncate table a;
truncate table b;
@@a.sql;
commit;
@@b.sql;
commit;再写一run.bat
----------------
sqlplus 用户名/密码@xxxx @run.sql用户只需点run.bat即可
conn yourUser/pw
truncate table yourtable1;
truncate table yourtable2;
--其它表的truncate
exitimp user/pw file=xxx.dmp fromuser=user1 touser=user2