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;
create or replace procedure IMP_DATA
(
file_n
阅读背景：
[PL/SQL]导入文件内的数据到数据库(针对本博的导出存储过程)
来源：互联网
分享到：
