[PL/SQL]使用存储过程实现导入指定文件的数据到数据库(针对本博客的EXP_DATA存储)
create or replace procedure IMP_DATA( file_name in varchar2 --要导入的文件名,包含路径(如:d:\test\exp_0003.txt) ,p_user in varchar2 default SYS_CONTEXT('USERENV', 'CURRENT_USER') --要导入的用户,缺省为当前用户 ,p_sep in varchar2 default ',' --字段分隔符,默认为逗号(需要打开要导入的文件确认导入的字段分隔符)) AS /* 描述:根据EXP_DATA过程导出的数据进行导入 created by cryking 2013.03.07 注意:1.本存储建议由SYS账户或具有SYSDBA权限的账户执行 2.不要在其他事务中运行本存储过程 3. 默认导入的数据(p_user为空,或未指定)全部在当前用户下 */ v_file UTL_FILE.file_type; TYPE t_filed IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER; v_fileds t_filed; TYPE t_data IS TABLE OF varchar2(4000) INDEX BY BINARY_INTEGER; v_data t_data; v_datatype t_data; v_sql varchar2(30000); V_esql varchar2(30000); v_filed VARCHAR2(100) := ''; v_filedstr VARCHAR2(4000) := ''; V_TABLE VARCHAR2(1000); v_user varchar2(20); v_path varchar2(500); v_filename varchar2(50); v_sep varchar2(10); v_text varchar2(32600); v_textTmp varchar2(32600); i_flag integer:=0; I_TABLE INTEGER; exp_sep exception; ex_table exception;BEGIN /*----------输入参数检查部分----------*/ --没有输入用户的情况 if trim(p_user) is null then v_user := SYS_CONTEXT('USERENV', 'CURRENT_USER'); else v_user := upper(p_user); end if; if trim(p_sep) is null then v_sep := ','; else v_sep := p_sep; end if; --获取路径 select replace(file_name, regexp_REPLACE(file_name, '\\*[^\\*]*\\'), '') into v_path from dual; --获取文件名 select regexp_REPLACE(file_name, '\\*[^\\*]*\\') into v_filename from dual; /*------------------------------------*/ --设置日期格式 EXECUTE IMMEDIATE 'ALTER session SET nls_date_format=''yyyy-mm-dd hh24:mi:ss'''; rollback; --防止在其他事务中运行本存储,先回滚之前的事务 execute immediate 'create or replace directory IMPDIR as ''' || v_path || ''' '; --创建目录 v_file := UTL_FILE.fopen('IMPDIR', v_filename, 'r'); --读取文件 --导入所有数据 loop UTL_FILE.get_line(v_file, v_text); if substr(v_text, -1, 1) <> ',' and INSTR(v_text, '[TABLE:]') = 0 then v_textTmp := v_text || chr(10); continue; else v_textTmp := v_textTmp || v_text; end if; --获取表名 IF INSTR(v_text, '[TABLE:]') > 0 THEN v_textTmp := ''; V_TABLE := UPPER(SUBSTR(v_text, INSTR(v_text, ']', 1, 2) + 1)); SELECT COUNT(*) INTO I_TABLE FROM all_TABLES WHERE all_TABLES.TABLE_NAME = V_TABLE AND OWNER = v_user; IF I_TABLE = 0 THEN v_sql := 'create table ' || v_user || '.' || V_TABLE || '('; ELSE v_sql := 'insert into ' || v_user || '.' || V_TABLE || '('; END IF; END IF; --获取字段列表 IF INSTR(v_text, '[filed:]') > 0 THEN v_textTmp := ''; select * bulk collect into v_fileds from table(splitstr(replace(v_text, '[filed:]'), v_sep)); IF INSTR(v_sql, 'create ') > 0 then FOR I IN 1 .. v_fileds.COUNT LOOP V_sql := v_sql || v_fileds(i) || ','; END LOOP; V_sql := v_sql || ') '; execute immediate v_sql; --先创建表 v_sql := 'INSERT INTO ' || v_user || '.' || V_TABLE || ' VALUES('; else v_filedstr := ''; FOR I IN 1 .. v_fileds.COUNT LOOP SELECT COUNT(*) INTO I_TABLE FROM ALL_tab_columns WHERE TABLE_NAME = V_TABLE AND OWNER = v_user and COLUMN_NAME = UPPER(v_fileds(i)); if I_TABLE = 0 then raise ex_table; else v_sql := v_sql || v_fileds(i) || ','; select data_type into v_filed from ALL_tab_columns where TABLE_NAME = V_TABLE AND OWNER = v_user and COLUMN_NAME = UPPER(v_fileds(i)); v_filedstr := v_filedstr || v_filed || ','; end if; end loop; V_sql := substr(v_sql, 1, length(V_sql) - 1) || ') values('; --去掉最后的逗号 end if; END IF; if INSTR(v_text, ',') > 0 and INSTR(v_text, '[filed:]') = 0 then select * bulk collect into v_data from table(splitstr(v_textTmp, v_sep)); select * bulk collect into v_datatype from table(splitstr(v_filedstr, ',')); V_esql := v_sql; FOR I IN 1 .. v_data.COUNT LOOP CASE WHEN INSTR(v_datatype(i), 'CHAR') > 0 THEN if v_data(i) = 'null' then V_esql := v_esql || 'NULL,'; else V_esql := v_esql || ' ''' || v_data(i) || ''','; --处理数据类型 end if; WHEN INSTR(v_datatype(i), 'NUMBER') > 0 THEN if v_data(i) = 'null' then V_esql := v_esql || 'NULL,'; else V_esql := v_esql || ' ' || v_data(i) || ','; --处理数据类型 end if; WHEN INSTR(v_datatype(i), 'DATE') > 0 THEN if v_data(i) = 'null' then V_esql := v_esql || 'NULL,'; else IF length(v_data(i)) - length(replace(v_data(i), ':', '')) = 0 then --日期数据格式确认 V_esql := v_esql || 'TO_DATE(''' || v_data(i) || ''',''YYYY-MM-DD''),'; --处理数据类型 elsif length(v_data(i)) - length(replace(v_data(i), ':', '')) = 1 then V_esql := v_esql || 'TO_DATE(''' || v_data(i) || ''',''YYYY-MM-DD HH24:MI''),'; --处理数据类型 else V_esql := v_esql || 'TO_DATE(''' || v_data(i) || ''',''YYYY-MM-DD HH24:MI:SS''),'; --处理数据类型 end if; end if; END CASE; end loop; V_esql := substr(v_esql, 1, length(V_esql) - 1) || ')'; --dbms_output.put_line('v_sql:' || v_esql); begin execute immediate v_esql; i_flag:=i_flag+1; --控制多少条记录提交一次 if i_flag=5000 then commit; i_flag:=0; end if; EXCEPTION when others then dbms_output.put_line('插入数据失败:[' || SQLCODE || '] ' || SQLERRM); dbms_output.put_line('失败语句:' || v_esql); end; v_textTmp := ''; END IF; end loop; UTL_FILE.fclose(v_file);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('导出数据成功完成!'); commit; UTL_FILE.fclose(v_file); when ex_table then ROLLBACK; raise_application_error(-20001, '导入的表:' || V_TABLE || '已存在,且字段不一致!'); 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表:
16:31:18 SCOTT@orcl> CREATE TABLE EMP_BAK2013 AS SELECT * FROM EMP;表已创建。已用时间: 00: 00: 00.32
然后DROP：
16:49:30 SCOTT@orcl> truncate table emp;表被截断。已用时间: 00: 00: 01.9216:50:13 SCOTT@orcl> select * from emp;未选定行。已用时间: 00: 00: 00.01
先执行导出:
begin
EXP_data('d:\test\exp_0304.txt');
end;
看到输出为:
导入数据成功完成后
进行查询:
16:51:56 SCOTT@orcl> select * from emp; 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 7788 scott ANALYST 7566 1987-04-19 00:00:00 184.5 <null> 20 7839 king PRESIDENT <null> 1981-11-17 00:00:00 7500 <null> 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 <null> 20 7900 james CLERK 7698 1981-12-03 00:00:00 1425 <null> 30 7902 ford ANALYST 7566 1981-12-03 00:00:00 4500 <null> 20 ....
发现数据成功导入.如果存在主键、唯一键约束的，在导入数据的时候，会自动跳过此行，并进行提示,如:
插入数据失败:[-1] ORA-00001: 违反唯一约束条件 (SCOTT.PK_DEPT)
失败语句:insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values( 40, 'OPERATIONS', 'BOSTON')
-------------------------------------------------
如有BUG,欢迎各位指出。