如下所示：
CREATE OR REPLACE PROCEDURE p_xlstooracle IS
v_file utl_file.file_type;
out_v VARCHAR2(4000);
v_id NUMBER;
v_service_competition NUMBER;
v_cu_market_share NUMBER(6, 4);
v_ct_market_share NUMBER(6, 4);
v_cm_market_share NUMBER(6, 4);
v_other_market_share NUMBER(6, 4);
BEGIN
IF utl_file.is_open(v_file) THEN
utl_file.fclose(v_file);
END IF;
v_file := utl_file.fopen('UTL_FILE_DIR', 'i_exch_info2.xls', 'r');
LOOP
BEGIN
utl_file.get_line(v_file, out_v);
EXCEPTION
WHEN no_data_found THEN
EXIT;
END;
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));
UPDATE i_exch_info
SET service_competition = v_service_competition,
cu_market_share = v_cu_market_share,
ct_market_share = v_ct_market_share,
cm_market_share = v_cm_market_share,
other_market_share = v_other_market_share
WHERE gwm_fid = v_id;
dbms_output.put_line(out_v);
END LOOP;
utl_file.fclose(v_file);
END p_xlstooracle;
註：分隔字元不是空格，我是直接拷貝的excel輸出文本的那個佔空間字元才行。
