芸人が音声解説 「オレたちの甲子園」

こんばんは。

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

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

このQ&Aに関連する最新のQ&A

A 回答 (4件)

基本的に 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行が選択されました。
    • good
    • 2

回答ではないのですが


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

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

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

#1 diashun です。


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

/*処理したいテーブル名が仮に「顧客マスタ」とし、列名を最初から「会社コード」「会社名」「住所」「電話番号」と仮定します。


次に宣言部に変数を追加します。*/
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;
    • good
    • 2

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qbatファイルからsql文実行

クライアントOS:WIN2000
Oracle:9i(サーバ(UNIX)上にあります)

現在、クライアントからbatを起動し、SQL文を投げ、結果を取得したいと思っております。
(SQL文は単純にTBLをカウントしているだけです)

・batの中身
sqlplus %UID%/%PASS%@%SID% @test.sql > output

結果は取得出来るのですが、余分な情報も結果に出力されてしまいます。結果のみを出力させるにはどうすればよろしいでしょうか?

・余分な情報
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
に接続されました。

Aベストアンサー

sqlplus に -S オプションを追加してみればどうでしょうか。

参考URL:http://biz.rivus.jp/sqlplus_overview.html

Qsqlplusで表示が変なので、出力を整形したい。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------

上記のように意味不明な形式で出てきます。

例えばこんな風に

select カラム1,カラム2,カラム3 from hoge;

カラム1 1の値
---------------------------
カラム2 2の値
---------------------------
カラム3 3の値

等のように分かりやすく表示できないでしょうか?

ちなみにOracle9iR2を使用しています。
sqlに関するツールは使用できないルールでして、あくまでsqlplusのコマンド上でみやすくしなければなりません。

分かりづらくですいませんが、皆さま、ご教授お願いします。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
-----------------------...続きを読む

Aベストアンサー

SQLPLUSを起動して、

SQL>set linesize 列数

でどうだ。

SQL>show linesize

で確認ができる。

QSELECTで1件のみ取得するには?

こんにちわ。
いまORACLE9iを使用している者です。

ACCESSでは
SELECT TOP 1 項目名 FROM テーブル名
ORDER BY 項目名;
で並べ替えたデータ群のうち,先頭の1件だけを
取ることができますが,
ORACLEでそのような機能(SQL)はあるでしょうか?
教えてください。
よろしくお願いします。

Aベストアンサー

order by と rownum を併用する場合は注意が必要です。

[tbl01]
cola | colb
------------
1000 | aaaa
1001 | bbbb

というデータがある場合、
select cola from tbl01 where rownum < 1 order by cola desc;
とすると、「1001」ではなく、「1000」が返されます。
これは、order by の前に rownum < 1 が適用されてしまうからです。

解決するには、
select aaa from (select cola aaa from tbl01 order by cola desc) where rownum = 1;
とすれば良いです。

QOracle(オラクル)で、日付時刻型の検索方法について

質問させていただきます。
データベースはオラクルを使っていて、
SQL文で、抽出するときにエラーが出て困っています。

日付時刻型が「2005/05/26 19:13:00」という感じで入ってます。
2005/05/26 を抽出したいのですが、
BETWEEN '2005/05/26 00:00:00' AND '2005/05/26 23:59:59'

だと、エラーでできません。
どなた様か、ご教授よろしくお願いしますm(_ _)m

Aベストアンサー

日付検索を行う場合は、以下のように書式を含める必要があります。

col BETWEEN TO_DATE('2005/05/26 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2005/05/26 23:59:59','YYYY/MM/DD HH24:MI:SS')

ただ、厳密には

col >= TO_DATE('2005/05/26', 'YYYY/MM/DD')
AND
col < TO_DATE('2005/05/27', 'YYYY/MM/DD')

と書くべきでしょうね。

Q3つの表の外部結合

表A、B、Cの3つがあり、Aのすべての行を出力したいと考えています。
外部結合を用いるのだとは思うのですが、3つの表に対して行う場合の
書き方がわからず困っています。
ご教授いただけないでしょうか?
select * from a,b,c
where a.商品ID =b.商品ID (+) and b.商品ID (+) =c.商品ID (+)
としてみましたが、うまくいきませんでした。

Aベストアンサー

ansi構文の趣旨からいえば、結合条件と絞り込み条件は分けて書くので・・

select *
from a
left join b on (a.商品ID =b.商品ID)
left join c on (b.商品ID =c.商品ID)
where a.年月 = 任意の値

と書くのが一般的でしょうね。

QSQL*LoaderでCSVから指定した列のみインポートしたい。

SQL*Loaderを用いてOracleのテーブルにデータを格納
しようと思っています。
格納するデータはカンマ区切りのCSVファイルです。

入力CSVファイルは他テーブル用のファイルなので、
ローダーで落とし込むテーブルとは列数が異なります。

ですので、「CSVの5列目の値をテーブルのAA_NOというフィールドに
格納する。ということが可能なのでしょうか?」

CSVファイルのある行が
a,b,c,d,e,f
というデータの場合、テーブルには
a,e,f
とインポートしたいのです。

よろしくお願いします。

Aベストアンサー

さきほど回答しましたhamu1です。
訂正します。
a,b,c,d,e,f
というデータの場合、テーブルには
a,e,f
をインポートするときには、

(略)
(
a,
"b" filler,
"c" filler,
"d" filler,
e,
f
)

となります。
間違えちゃって、、すいません。

QCSV形式のファイルを読み込んでテーブルに格納するには?(BULK INSERT、BCP、DTS)

VisualBasicのカテゴリでも質問したのですが、
こちらの方が回答がつく可能性が高いのではと思い、こちらにも書き込みました。

今、
"aaa","bbb","ccc" 
のような形で1レコードのCSVファイルがあります。
これをSQLServer2000上のテーブルに読込みたいのですが、
10件ほどの少ないレコードなら
各フィールドごとを変数に入れて読込み、
それをループさせてINSERT INTO すればよいのですが・・・。

大量の件数の場合時間がかかりすぎてしまいます。
それで、BULK INSERT、BCP、DTS などの利用を考えているのですが、

まず、何よりこの質問です。

(1)このようなファイル形式のものを上記の方法で読込めるのか?

実現可能なのかをまずお聞きしたいです。
どなたか、成功されている方はいらっしゃいますか?

(2)BCPでファイルのフォーマットを指定したファイルを用意して試みたところ、
""で区切られているため、その部分もフィールドに取り込まれてしまい、
binaryデータが切り詰められましたとなってしまいます。
この回避方法はないでしょうか?

(3)""が余計なので、これを変換して読込むことは可能ですか?
DTSはVisualBasic上からコマンドとして利用したいです。
batファイルを用意してもいいです。

困っています。どなたか助けてください。

VisualBasicのカテゴリでも質問したのですが、
こちらの方が回答がつく可能性が高いのではと思い、こちらにも書き込みました。

今、
"aaa","bbb","ccc" 
のような形で1レコードのCSVファイルがあります。
これをSQLServer2000上のテーブルに読込みたいのですが、
10件ほどの少ないレコードなら
各フィールドごとを変数に入れて読込み、
それをループさせてINSERT INTO すればよいのですが・・・。

大量の件数の場合時間がかかりすぎてしまいます。
それで、BULK INSERT、BCP、DTS などの利用を...続きを読む

Aベストアンサー

DTSインポートエクスポートウィザードを使用すれば、”を含むCSVファイルでもよ見込むことが出来ますよ。
[スタート]-[プログラム]-[Microsoft SQL Server]-[データのインポートとエクスポート]から呼び出すことも出来ます。

変換元(データのコピー元)にCSVファイル(データソース:Text File)を選択して、変換先に接続するSQLServerとデータベース名を指定して・・・
順次設定していけば、設定できますよ。
#最後にちゃんと保存してくださいね。

話は変わりますが、最初の質問から派生して関連する質問をするのであれば、ちゃんと最初の質問を終了してから新規の質問を作成してください。感じ悪いです。回答している人の気持ちを考えてください。

Qupdate文で改行を入れる

こんにちは。
いつもお世話になっています。

update文を使用して、データに改行を入れたいのですが、どうすればよいでしょうか。

対象列のデータ型はVARCHAR2です。

例えばSQLPLUSから、
SPL>update 「テーブル名」set 「対象列」='あ改行い改行う改行'
を投入し、そのあと
select 「対象列」 from 「テーブル名」
とすると
SQL>あ
SQL>い
SQL>う
と出てくればよいんですが、
SQL>update 「テーブル名」set 「対象列」='あ\nい\nう\n'
としても
SQL>あ\nい\nう\n
と、「\n」が文字列としてでてきてしまいます。

SPL>update 「テーブル名」set 「対象列」='あ
2い
3う'
と投入すると、期待通りになるのですが、
そうではなく一行にまとめたいのですがどうすれば良いでしょうか。

Oracle9iを使用しています。
宜しくお願いいたします。

Aベストアンサー

こんにちわyukio200263さん
以下のSQL文でどうでしょうか?

UPDATE 「テーブル名」
SET 「対象列」= 'あ' || CHR(13) || CHR(10) ||'い'

ちょっと長くなってしまいますが、一行で可能です。

Q日付型カラムへのデータINSERT

お世話になります。ひとつご教授ください。

オラクル10g, ojdbc14でjdk1.4.2から接続です。

INSERT命令を使ってテーブルにレコードを追加する際に、DATE型のカラムに対して

INSERT TABLE_A (DATE1, ...) VALUES ('2006-4-27 12:00:00', ...);

を実行するとエラーORA-01861が出ます。そこで

INSERT TABLE_A (DATE1, ...) VALUES ('2006-4-27', ...);

このように記述して再度実行すると通ります。

このようなとき、時間の部分まで設定した値をカラムに格納したいときはどうしたらよろしいでしょうか?

よろしくお願いします。

Aベストアンサー

ごめんなさい。
説明と文例が違ってしまいました。
正しくはこちらです。

INSERT TABLE_A (DATE1, ...) VALUES (to_date('2006-4-27 12:00:00','yyyy/mm/dd hh24:mi:ss'), ...);
というように、to_date関数を使うのが一般的かと思います。

QPL/SQLをWindowsのBATファイルで実行するには

いつも参考にさせていただいております。
表題のとおり、WindowsServer2003上でBATファイルを起動したいのですが、うまくいきません。

BATファイルの中身
sqlplus USER_A/USER_A @DB_A @SQLPAT
sqlplus USER_B/USER_B @DB_B @SQLPAT

SQLPATファイルは、大体以下の感じで、2つのスキーマーで同じPL/SQLを実行します。

Declare

カーソルループ
更新


END;

タスクにBATファイルを登録し実行すると
最初のPL/SQLは実行されますが、次のUSER_Bが実行されません。

どなたか詳しい方ご教授をお願いできませんでしょうか?
オラクルのVerは9iです。よろしくお願い致します。

Aベストアンサー

pl_sqlは予めDBに登録しておく。
CREATE OR REPLACE PROCEDURE pl_hoge()
IS
・・・
BEGIN
・・・
END;
/

■hoge.bat
sqlplus user/pass@dbname @hoge.sql

■hoge.sql
SET serveroutput ON;
BEGIN
pl_hoge();
END;
/
exit;

1さんはhoge.sql最後のexit;を指摘してます。
私もそれと疑いますが。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング