[PL/SQL]使用存储过程实现导入指定文件的数据到数据库(针对本博客的EXP_DATA存储)(四)
UTL_FILE.fclose(v_file);
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line('导出数据' || file_name || '失败');
UTL_FILE.fclose(v_file);
raise;
END IMP_DATA;
简约地写了个IMP,实现了导入文件中所有数据到指定用户.(固定5000记录提交一次)
注:暂时还未实现没有表存在时,自动创建表并导入数据
现在我们来综合执行测试一下:
先执行导出:
begin
EXP_data('d:\test\exp_0304.txt');
end;
默认导出当前用户所有表的数据,内容为(部分内容):
[USER:]SCOTT[TABLE:]EMP
[filed:]EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,
1214,null,null,null,null,1212,null,null,
1212,g001,null,null,null,4500,null,null,
1213,gwmk,null,null,null,null,null,null,
1235,w002,null,null,null,null,null,null,
1234,w001,null,null,null,null,null,null,
2012,g002 ,null,null,null,null,null,null,
2013,1002 ,null,null,null,null,null,null,
2014,1002,null,null,null,null,null,null,
7369,smith,CLERK,7902,1980-12-17 00:00:00,1200,null,20,
7499,allen,SALESMAN,7698,1981-02-20 00:00:00,2400,300,30,
7521,ward,SALESMAN,7698,1981-02-22 00:00:00,1875,500,30,
7566,jones,MANAGER,7839,1981-04-02 00:00:00,4462.5,null,20,
7654,martin,SALESMAN,7698,1981-09-28 00:00:00,1875,1400,30,
7698,blake,MANAGER,7839,1981-05-01 00:00:00,4275,null,30,
7782,clark,MANAGER,7839,1981-06-09 00:00:00,3675,null,10,
...
...
再执行导入:
为了方便测试,我先备份EMP表:
[sql]
16:31:18 SCOTT@orcl> CREATE TABLE EMP_BAK2013 AS SELECT * FROM EMP;
表已创建。
已用时间: 00: 00: 00.32
然后DROP：
[sql]
16:49:30 SCOTT@orcl> truncate table emp;
表被截断。
已用时间: 00: 00: 01.92
16:50:13 SCOTT@orcl> select * from emp;
未选定行。
已用时间: 00: 00: 00.01
先执行导出:
begin
EXP_data('d:\test\exp_0304.txt');
end;
看到输出为:
导入数据成功完成后
进行查询:
[sql]
16:51:56 SCOTT@orcl> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
1214 1212
1212 g001 4500
1213 gwmk
1235 w002
1234 w001
2012 g002
2013 1002
2014 1002
7369 smith CLERK 7902 1980-12-17 00:00:00 1200 20
7499 allen SALESMAN 7698 1981-02-20 00:00:00 2400 300 30
7521 ward SALESMAN 7698 1981-02-22 00:00:00 1875 500 30
7566 jones MANAGER 7839 1981-04-02 00:00:00 4462.5 20
7654 martin SALESMAN 7698 1981-09-28 00:00:00 1875 1400 30
7698 blake MANAGER 7839 1981-05-01 00:00:00 4275 30
7782 clark MANAGER 7839 1981-06-09 00:00:00 3675 10
7788 scott ANALYST 7566 1987-04-19 00:00:00 184.5 20
7839 king PRESIDENT 1981-11-17 00:00:00 7500 10
7844 turner SALESMAN 7698 1981-09-08 00:00:00 2250 0 30
7876 adams CLERK 7788 1987-05-23 00:00:00 1650 20
7900 james CLERK 7698 1981-12-