はじめに
前回までに表領域とスキーマーを作成しましたので、今回は表を作成しデータをインサートしたいと思います。
以下のような社員表を作ります。
| 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
- 日付
- number
- 制約
- primary key
- テーブル内で一意となる値
- not null
- 空白を許可しない
- primary key
設定した内容を確認します。
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;
レコードが選択されませんでした。