Oracle存储过程读文件(二)
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,
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;
commit;
--关闭句柄
utl_file.fclose(filehandle);
v_retvalue :=1;
EXCEPTION
WHEN invalid_data_format THEN
v_retvalue :=0;
dbms_output.put_line('invalid_data_format exception');
when others then
v_retvalue :=0;
dbms_output.put_line('Othre exception,error code='||sqlcode||',error msg='||sqlerrm);
END PR_FINANCE_PRODUCT_PARSE;