【Oracle DB基本3】表の作成とデータのインサート

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

|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.
```

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

https://docs.oracle.com/cd/F19136_01/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6

- 書式
    - `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を使って一括でインサートします。
他にもいくつかツールがあるようです。

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader.html#GUID-8D037494-07FA-4226-B507-E1B2ED10C144

使用する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;

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