Oracle 存储过程读文件

create or replace
PROCEDURE PR_FINANCE_PRODUCT_PARSE
(
v_filename IN VARCHAR2, --解析文件名称
v_retvalue OUT NUMBER --1为成功，0为失败
)
AS
--文件句柄
filehandle utl_file.file_type;
--缓冲区
filebuffer varchar2(255);
--文件名
lv_filename varchar2(100);
--文件到表字段的映射
lv_product_number varchar2(255);
lv_product_name varchar2(255);
lv_min_capital varchar2(255);
lv_max_capital varchar2(255);
lv_interest_rate varchar2(255);
lv_begin_time varchar2(255);
lv_end_time varchar2(255);
lv_time_limit varchar2(255);
lv_product_status varchar2(255);
--列指针
lv_i number;
--字符串定位解析指针
lv_sposition1 number;
lv_sposition2 number;
lv_sposition3 number;
lv_sposition4 number;
lv_sposition5 number;
lv_sposition6 number;
lv_sposition7 number;
lv_sposition8 number;

--声明异常
invalid_data_format exception;

BEGIN

if v_filename is null then
lv_filename:='finance_product_'|| to_char(sysdate,'YYYYMMDD') ||'.txt' ;
else
lv_filename := v_filename;
end if;

--打开一个文件句柄 ,同时fopen的第一个参数必须是大写
filehandle := utl_file.fopen('FINANCE_PRODUCT_DIR',lv_filename,'r');

--if UTL_FILE.IS_OPEN(filehandle) then
-- EXECUTE IMMEDIATE 'truncate table T_FINANCING_PRODUCT';
--end if;

Loop
begin
--将文件信息读取到缓冲区filebuffer中,每次读取一行
utl_file.get_line(filehandle,filebuffer);
dbms_output.put_line(filebuffer);
filebuffer:=CONVERT(filebuffer,'UTF8','ZHS16GBK'); --转化汉字，处理乱码

--数据文件一行字符串中数据分割符'|'的位置
lv_sposition1 := instr(filebuffer, '|', 1,1);
lv_sposition2 := instr(filebuffer, '|', 1,2);
lv_sposition3 := instr(filebuffer, '|', 1,3);
lv_sposition4 := instr(filebuffer, '|', 1,4);
lv_sposition5 := instr(filebuffer, '|', 1,5);
lv_sposition6 := instr(filebuffer, '|', 1,6);
lv_sposition7 := instr(filebuffer, '|', 1,7);
lv_sposition8 := instr(filebuffer, '|', 1,8);

--字符串解析正常情况
if lv_sposition1 <>0 then
lv_product_number := substr(filebuffer, 0, lv_sposition1-1); --第一列
else
raise invalid_data_format;
end if;
if lv_sposition2 <> 0 then
lv_product_name := substr(filebuffer, lv_sposition1+1, lv_sposition2-lv_sposition1-1);
--CONVERT(substr(filebuffer, lv_sposition1+1, lv_sposition2-lv_sposition1-1),'AL32UTF8','ZHS16GBK'); --第二列
else
raise invalid_data_format;
end if;
if lv_sposition3 <> 0 then
lv_min_capital := substr(filebuffer, lv_sposition2+1, lv_sposition3-lv_sposition2-1); --第三列
else
raise invalid_data_format;
end if;
if lv_sposition4 <> 0 then
lv_max_capital := substr(filebuffer, lv_sposition3+1, lv_sposition4-lv_sposition3-1); --第四列
else
raise invalid_data_format;
end if;
if lv_sposition5 <> 0 then
lv_interest_rate := substr(filebuffer, lv_sposition4+1, lv_sposition5-lv_sposition4-1); --第五列
else
raise invalid_data_format;
end if;
if lv_sposition6 <> 0 then
lv_begin_time := substr(filebuffer, lv_sposition5+1, lv_sposition6-lv_sposition5-1); --第六列
else
raise invalid_data_format;
end if;
if lv_sposition7 <> 0 then
lv_end_time := substr(filebuffer, lv_sposition6+1, lv_sposition7-lv_sposition6-1); --第七列
else
raise invalid_data_format;
end if;
if lv_sposition8 <> 0 then
lv_time_limit := substr(filebuffer, lv_sposition7+1, lv_sposition8-lv_sposition7-1); --第八列
lv_product_status := substr(filebuffer, lv_sposition8+1); --第九列
else
raise invalid_data_format;
end if;

--每列循环完后将信息保存到表中
dbms_output.put_line('insert data into table');
insert into T_FINANCING_PRODUCT
(
PRODUCT_NUMBER,
PRODUCT_NAME,
MIN_CAPITAL,
MAX_CAPITAL,
INTEREST_RATE,
BEGIN_TIME,
END_TIME,
TIME_LIMIT,
PRODUCT_STATUS
)
values
(
lv_product_number,
lv_product_name,
lv_min_capital,
lv_max_capital,
lv_interest_rate,
lv_begin_time,
lv_end_time,
lv_time_limit,
lv_product_status
);
exception
WHEN no_data_found THEN
exit ;
end;
End Loop;
delete from T_FINANCING_PRODUCT where to_char(OPERATION_DATE,'YYYYMMDD')=to_char(sysdate-1,'YYYYMMDD');
commit;
--关闭句柄
utl_file.fclose(filehandle);
v_retvalue :=1;
EXCEPTION
WHEN invalid_data_format THEN
v_retvalue :=0;
rollback;
dbms_output.put_line('invalid_data_format exception');
when others then
v_retvalue :=0;
rollback;
dbms_output.put_line('Othre exception,error code='||sqlcode||',error msg='||sqlerrm);
END PR_FINANCE_PRODUCT_PARSE;