[PL/SQL]使用存储过程实现导入指定文件的数据到数据库(针对本博客的EXP_DATA存储)
[sql] www.zzzyk.com
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过程导出的数据进行导入 www.zzzyk.com
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) || ',';
