はじめに

前回までに表領域とスキーマーを作成しましたので、今回は表を作成しデータをインサートしたいと思います。
以下のような社員表を作ります。

EMP_ID	NAME	HIRE_DATE	SALARY	DEPT_ID
100	佐藤	00-04-01	500000	50
101	鈴木	00-10-01	480000	10
102	高橋	02-01-01	450000	40
103	田中	03-12-01	400000	30
104	渡辺	07-04-01	300000	40
105	伊藤	04-12-01	350000	30
106	山本	01-04-01	450000	20
107	中村	06-11-01	300000	10
108	小林	05-04-01	360000	20
109	吉田	08-04-01	280000

表（Table）の作成

OSにログインし、oracleユーザでスキーマーに接続します。

$ sqlplus ken/<パスワード>@PDB1

スキーマーに表がないことを確認します。

SQL> select table_name from user_tables;

no rows selected

EMPという名称の表を作成し、各カラム（列）を設定します。

SQL> create table emp (
  2  emp_id number(3) primary key,
  3  name varchar2(10 char) not null,
  4  hire_date date not null,
  5  salary number(10) not null,
  6  dept_id number(2)
  7  );

Table created.

マニュアルを見るとたくさん書かれていますが、今回指定した内容だけ簡単にメモします。

書式
- CREATE TABLE 表 ( 列1 データ型制約 ,列2 データ型制約 ,・・・) TABLESPACE 表領域名 ;
  - TABLESPACEを指定しない場合、スキーマーのデフォルト表領域に作成される。
データ型
- number
  - 数値（整数）
  - 括弧内で桁数の上限を指定
- varchar2
  - 可変長の文字列
  - 括弧内に(数字 char)で文字数の上限を指定
- data
  - 日付
制約
- primary key
  - テーブル内で一意となる値
- not null
  - 空白を許可しない

設定した内容を確認します。

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
EMP

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                    NOT NULL NUMBER(3)
 NAME                                      NOT NULL VARCHAR2(10 CHAR)
 HIRE_DATE                                 NOT NULL DATE
 SALARY                                    NOT NULL NUMBER(10)
 DEPT_ID                                            NUMBER(2)

データのインサート

oracleユーザの環境変数を日本語に設定します。これをしないと、日本語をインサートしたときに文字化けしました。

$ export NLS_LANG=japanese_japan.UTF8

日付の形式を確認します。
これも形式に合わせてインサートしないとエラーになります。

SQL> col parameter format a25
SQL> col value format a35
SQL> select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER                 VALUE                                   CON_ID
------------------------- ----------------------------------- ----------
NLS_DATE_FORMAT           RR-MM-DD                                     3

データをインサートします。

SQL> insert into emp
  2  values(
  3  '100',
  4  '佐藤',
  5  '00-04-01',
  6  '500000',
  7  '50'
  8  );

1行が作成されました。

インサートしたらCommitして確認します。
Commitしない状態でSQL*Plusから切断すると、インサートされずに元に戻ります。

SQL> commit;

コミットが完了しました。

SQL> select * from emp;

    EMP_ID NAME                           HIRE_DAT     SARARY    DEPT_IT
---------- ------------------------------ -------- ---------- ----------
       100 佐藤                           00-04-01     500000         50

削除

削除する際は、Primary keyを指定して削除します。

SQL> delete from emp where emp_id = 100;

1行が削除されました。

SQL> select * from emp;

レコードが選択されませんでした。

複数行のインサート

データの量が少ない場合には1行ずつでもいいですが、多い場合にはCSVファイルなどから一括でインサートしたいですよね。そのためのツールとしてSQL Loaderを使って一括でインサートします。
他にもいくつかツールがあるようです。

使用するCSVファイルです。

emp.csv

EMP_ID,NAME,HIRE_DATE,SALARY,DEPT_ID
100,佐藤,2000/4/1,500000,50
101,鈴木,2000/10/1,480000,10
102,高橋,2002/1/1,450000,40
103,田中,2003/12/1,400000,30
104,渡辺,2007/4/1,300000,40
105,伊藤,2004/12/1,350000,30
106,山本,2001/4/1,450000,20
107,中村,2006/11/1,300000,10
108,小林,2005/4/1,360000,20
109,吉田,2008/4/1,280000,

制御ファイルの作成

以下の制御ファイルを作成しました。

load.ctl

options(
        skip=1 # 1行目をスキップ（カラム名なので）
)

load data
infile 'emp.csv' # 取り込むファイル
into table emp # インサートするテーブル名
fields 
        terminated by "," # 区切り文字
        optionally enclosed by '"' # オプションとしての区切り文字
trailing nullcols # データがない場合はNULLにする
( emp_id, name, hire_date, salary, dept_id ) # カラム

CSVファイルのロード

以下のコマンドで作成した制御ファイルを指定してCSVファイルをロードします。

$ sqlldr ken/<パスワード>@PDB1 CONTROL=load.ctl 

SQL*Loader: Release 19.0.0.0.0 - Production on 木 7月 20 10:51:30 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

使用パス:      従来型
コミット・ポイントに達しました - 論理レコード件数10

表EMP:
  10 行は正常にロードされました。

確認するログ・ファイル:
  load.log
ロードの詳細を参照してください。

実行するとログファイルが作成されます。
インサートできなかった行は<テーブル名>.badファイルに記録されます。

SQL*Loader: Release 19.0.0.0.0 - Production on 木 7月 20 10:51:30 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

制御ファイル:    load.ctl
データファイルemp.csv
  不良ファイル:  emp.bad
  廃棄ファイル:  指定なし

 (すべて廃棄できます)

ロード数: ALL
スキップ数: 1
許容エラー数: 50
バインド配列:    250行、最大1048576バイト
継続文字:    指定なし
使用パス:      従来型

表EMP、 ロード済 すべての論理レコードから
この表に対する有効な挿入オプション: INSERT
TRAILING NULLCOLSオプションは有効です。

   列名                  位置   長さ  用語暗号化データ型
------------------------------ ---------- ----- ---- ---- ---------------------
EMP_ID                             FIRST    *  , O(")CHARACTER            
NAME                                NEXT    *  , O(")CHARACTER            
HIRE_DATE                           NEXT    *  , O(")CHARACTER            
SALARY                              NEXT    *  , O(")CHARACTER            
DEPT_ID                             NEXT    *  , O(")CHARACTER            


表EMP:
  10 行は正常にロードされました。
  0 行はデータ・エラーのためロードされませんでした。
  0 行は、すべてのWHEN句が失敗したためロードされませんでした。
  0 行はすべてのフィールドがNULLであったためロードされませんでした。


バインド配列に割り当てられた領域:   322500バイト(250行)
読取りバッファのバイト数:  1048576

スキップされた論理レコードの合計:          1
読み込まれた論理レコードの合計:            10
拒否された論理レコードの合計:               0
廃棄された論理レコードの合計:        0

実行開始木 7月  20 10:51:30 2023
実行終了木 7月  20 10:51:31 2023

実行時間:        00: 00: 00.34
CPU時間 :        00: 00: 00.16

確認

SQL*Plusに接続して確認します。

SQL> select * from emp;

    EMP_ID NAME                           HIRE_DAT     SALARY    DEPT_ID
---------- ------------------------------ -------- ---------- ----------
       100 佐藤                           00-04-01     500000         50
       101 鈴木                           00-10-01     480000         10
       102 高橋                           02-01-01     450000         40
       103 田中                           03-12-01     400000         30
       104 渡辺                           07-04-01     300000         40
       105 伊藤                           04-12-01     350000         30
       106 山本                           01-04-01     450000         20
       107 中村                           06-11-01     300000         10
       108 小林                           05-04-01     360000         20
       109 吉田                           08-04-01     280000

10行が選択されました。

（参考）表の削除

SQL> drop table emp;

表が削除されました。

SQL> select table_name from user_tables;

レコードが選択されませんでした。