CSVファイルを読み込んでテーブルの更新

質問

こんばんは。

oracle9iでPL/SQLを使用して、CSVファイルを
読み込んで、テーブルの更新を行いたいと
思っています。
CSVファイル、テーブル共に最初の２つがキー項目です。

■CSVファイル
001, 111, 10000, 20000
002, 222, 30000, 40000
・・・・・
・・・・・
009, 999, 55555, 55555

■テーブル
001, 111, 99999, 99999
002, 222, 99999, 99999
・・・・・
・・・・・
009, 999, 99999, 99999

■テーブル（処理後）
001, 111, 10000, 20000
002, 222, 30000, 40000
・・・・・
・・・・・
009, 999, 55555, 55555

わかったのは、CSVファイルを行単位で読み込む所までです。

DECLARE
　　File_Handle UTL_FILE.FILETYPE;
Read_Line VARCHAR2(1023);
BEGIN
File_Handle := UTL.FILE.FOPEN('dir', 'file', 'r');
BEGIN
LOOP
UTL.FILE.GETLINE(File_Handle, Read_line);
END LOOP;
END;
UTL.FILE.FCLOSE(File_Handle);
END;

1行を読み込んだのはいいけど、この後がよくわかりません。テーブルを更新する所とあわせて、ご教示ください。

回答No.4

ryotana01

基本的に GoF さんの意見に賛成です。
方法がスマートなのはそちらかなというだけなのですが・・

PL*SQL でやろうとするのであれば、以下のようにすることで
出来るのではないかと思います。

プログラマではないので、コーディングが下手な部分は許してください。
あくまで参考として捕らえてください。

-- test.csv
001, 111, 10000, 20000
002, 222, 30000, 40000
003, 333, 50000, 60000
004, 444, 70000, 80000
005, 555, 90000, 55555
006, 666, 55555, 55555
007, 777, 55555, 55555
008, 888, 55555, 55555
009, 999, 55555, 55555

--定義
create table test (col1 varchar2(3),
col2 varchar2(3),
col3 varchar2(5),
col4 varchar2(5));
--初期データ
insert into test values ('001','111','99999','99999');
insert into test values ('002','222','99999','99999');
insert into test values ('003','333','99999','99999');
insert into test values ('004','444','99999','99999');
insert into test values ('005','555','99999','99999');
insert into test values ('006','666','99999','99999');
insert into test values ('007','777','99999','99999');
insert into test values ('008','888','99999','99999');
insert into test values ('009','999','99999','99999');
**********************************************************

SQL> select * from test;

COL COL COL3 COL4
--- --- ----- -----
001 111 99999 99999
002 222 99999 99999
003 333 99999 99999
004 444 99999 99999
005 555 99999 99999
006 666 99999 99999
007 777 99999 99999
008 888 99999 99999
009 999 99999 99999

9行が選択されました。

SQL> DECLARE
2 c1 number;
3 c2 number;
4 c3 number;
5 c4 number;
6 data1 varchar2(100) := null;
7 data2 varchar2(100) := null;
8 data3 varchar2(100) := null;
9 data4 varchar2(100) := null;
10 File_Handle UTL_FILE.FILE_TYPE;
11 Read_Line VARCHAR2(1023);
12 BEGIN
13 File_Handle := utl_file.fopen('c:\temp', 'test.csv', 'r');
14 LOOP
15 BEGIN
16 UTL_FILE.GET_LINE(File_Handle, Read_line);
17 -- カンマ位置
18 c1 := instr(Read_line,',',1,1);
19 c2 := instr(Read_line,',',1,2);
20 c3 := instr(Read_line,',',1,3);
21 c4 := length(Read_line);
22 -- 列データ
23 data1 := ltrim(substr(Read_line,1 ,c1-1));
24 data2 := ltrim(substr(Read_line,c1+1,c2-c1-1));
25 data3 := ltrim(substr(Read_line,c2+1,c3-c2-1));
26 data4 := ltrim(substr(Read_line,c3+1,c4));
27 -- update
28 update test set col3 = data3
29 where col1=data1 and col2=data2;
30 update test set col4 = data4
31 where col1=data1 and col2=data2;
32 commit;
33 exception
34 when no_data_found then exit;
35 END;
36 END LOOP;
37 UTL_FILE.FCLOSE(File_Handle);
38 END;
39 /

PL/SQLプロシージャが正常に完了しました。

SQL> select * from test;

COL COL COL3 COL4
--- --- ----- -----
001 111 10000 20000
002 222 30000 40000
003 333 50000 60000
004 444 70000 80000
005 555 90000 55555
006 666 55555 55555
007 777 55555 55555
008 888 55555 55555
009 999 55555 55555

9行が選択されました。

回答No.3

GoF

回答ではないのですが
ファイルをアクセスして取り込むのも一つの方法ですが
別のアプローチでのやり方をお勧めします。

・CSVファイルを外部表の読み取り表として定義する。
・MERGE文でINSERT/UPDATEする。

これでやるメリットは、仕様変更に柔軟に対応でき、
普通のSQLだけなのでファイルIOエラーを意識する必要がありません。

回答No.2

diashun

＃1 diashun です。
訂正です。
CustCode := SUBSTRB(line,1,3);　の「line」は
「Read_line」に修正してください。
（他の箇所も）・・・お手数です。

回答No.1

diashun

/*処理したいテーブル名が仮に「顧客マスタ」とし、列名を最初から「会社コード」「会社名」「住所」「電話番号」と仮定します。
次に宣言部に変数を追加します。*/
CustCode 顧客マスタ.会社コード%TYPE;
CustName 顧客マスタ.会社名%TYPE;
Address 顧客マスタ.住所%TYPE;
TelNo 顧客マスタ.電話番号%TYPE;

/*次に
loop
UTL_FILE.GET_LINE(File_Handle, Read_line);の
後に以下を記述します。（桁数はmkimさんの参考例）*/
CustCode := SUBSTRB(line,1,3);
CustName := SUBSTRB(line,4,3);
Address := SUBSTRB(line,8,5);
TelNo := SUBSTRB(line,14,5);

INSERT INTO 顧客マスタ (会社コード,会社名, 住所
,電話番号)
VALUES (CustCode, CustName, Address,
　　　　TelNo);
end loop;

/*念のため例外処理も・・・*/
EXCEPTION
WHEN NO_DATA_FOUND then
dbms_output.put_line('正常終了');
commit;
utl_file.fclose(File_Handle);
WHEN OTHERS then
dbms_output.put_line('エラー発生');
rollback;
utl_file.fclose(File_Handle);

/* おしまい (頑張って下さい）-----------*/
end;