--源地址不详
--创建目录SQL> create directory dir_xls as '/home/oracle';Directory created.--给用户授权SQL> grant all on directory dir_xls to public;Grant succeeded.
CREATE OR REPLACE PROCEDURE p_xlstooracle IS v_file utl_file.file_type; out_v VARCHAR2(4000); v_id varchar2(20); v_service_competition varchar2(20); v_cu_market_share varchar2(20); v_ct_market_share varchar2(20); v_cm_market_share varchar2(20); v_other_market_share varchar2(20);BEGIN--判断文件是否被打开 IF utl_file.is_open(v_file) THEN utl_file.fclose(v_file); END IF; v_file := utl_file.fopen('DIR_XLS', 'xls_to_oracle.txt', 'r');--循环读取文件内容，赋值 out_v LOOP BEGIN utl_file.get_line(v_file, out_v); EXCEPTION WHEN no_data_found THEN EXIT; END;--依次读取空格的位置 赋值 v_id v_id := substr(out_v, 1, instr(out_v, ' ', 1, 1) - 1);--读取第二个字段的内容 v_service_competition := substr(out_v, instr(out_v, ' ', 1, 1) + 1, instr(out_v, ' ', 1, 2) - instr(out_v, ' ', 1, 1)-1); --读取第三个字段的内容 v_cu_market_share := substr(out_v, instr(out_v, ' ', 1, 2) + 1, instr(out_v, ' ', 1, 3) - instr(out_v, ' ', 1, 2)-1); --读取第四个字段的内容 v_ct_market_share := substr(out_v, instr(out_v, ' ', 1, 3) + 1, instr(out_v, ' ', 1, 4) - instr(out_v, ' ', 1, 3)-1); --读取第五个字段的内容 v_cm_market_share := substr(out_v, instr(out_v, ' ', 1, 4) + 1, instr(out_v, ' ', 1, 5) - instr(out_v, ' ', 1, 4)-1); --读取地六个字段的内容 v_other_market_share := substr(out_v, instr(out_v, ' ', 1, 5) + 1, length(out_v) - instr(out_v, ' ', 1, 5)); --插入 表 i_exch_info insert into i_exch_info values(v_id,v_service_competition,v_cu_market_share,v_ct_market_share,v_cm_market_share,v_other_market_share); dbms_output.put_line(out_v); END LOOP; --关闭文件 utl_file.fclose(v_file); END p_xlstooracle;
转载于:https://www.cnblogs.com/iyoume2008/p/5629287.html
