Oracle Database/SQLを完全に理解する①【基礎編】

# 📖 参考

[Databases for Developers: Foundations](https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-foundations.html)

# テーブルの作成(create table)

## テーブル構成

**テーブル構成**は、`organization`句で定義し、**レコードの格納方法**を指定することができる。

|オプション|レコードの格納方法|
|---|---|
|Heap(既定)|下から順次追加|
|Index|主キーでソートされた状態で追加|
|External|外部ファイル(`.csv`等)の読込|

### ヒープ構成表

```sql:ヒープ構成表の作成
create table toys_heap (
  toy_name varchar2(100)
) organization heap;
```

### インデックス構成表

:::note
インデックス構成表を作成する場合は`主キー`が必要
:::

```sql:インデックス構成表の作成
create table toys_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index;
```

### 外部表

:::note
外部表を作成する場合は**ファイルパスを定義**する`ディレクトリ`の作成と、
`ファイル名`の記述が必要
:::

```sql:外部表の作成
-- 管理者権限(SYSTEMユーザ)でディレクトリを作成
create or replace directory tmp as '/path/to/file';

-- 一般ユーザに読み書き権限を付与
grant read, write on directory tmp to ORCLADM;

-- 一般ユーザで外部表を作成
create table toys_ext (
  toy_name varchar2(100)
) organization external (
  default directory tmp
  location ('toys.csv')
);
```

### 一時表

SQLセッションの期間中のみ実体化される表を`一時表`と呼ぶ。
一時表には`グローバル`・`プライベート`の2種類が存在し、それぞれ以下のように分類される。

|種類|概要|
|---|---|
|グローバル|全ユーザがアクセス可能|
|プライベート|作成者のみアクセス可能|

#### グローバル一時表

```sql:グローバル一時表の作成
create global temporary table toys_gtt (
  toy_name varchar2(100)
);
```

#### プライベート一時表

:::note
プライベート一時表を作成する場合は、テーブル名の**接頭辞**に`ora$ptt_`を付与
:::

```sql:プライベート一時表の作成
create private temporary table ora$ptt_toys (
  toy_name varchar2(100)
);
```

## パーティション表

特定のカラムを**パーティションキー**として同一テーブルを**セグメント**に分割することを`パーティショニング(パーティション化)`と呼ぶ。
パーティショニングはあくまで**論理上の分割**であり、パーティションキーが同じであれば同一領域に格納される。

パーティショニングの分割方式は、以下の3種類に分類される。

|分割方式|条件|
|---|---|
|レンジ|値の範囲|
|リスト|値|
|ハッシュ|要素数(各パーティションの要素数が均等になるように分配)|

:::note
パーティション条件は、`方式`・`パーティションキー`・`パーティション名`の3つが必要
:::

```sql:パーティション表の作成
-- レンジ方式
create table toys_range (
  toy_name varchar2(100)
) partition by range ( toy_name ) (
  partition p0 values less than ('b'),
  partition p1 values less than ('c')
);

-- リスト方式
create table toys_list (
  toy_name varchar2(100)
) partition by list ( toy_name ) (
  partition p0 values ('Sir Stripypants'),
  partition p1 values ('Miss Snuggles')
);

-- ハッシュ方式
create table toys_hash (
  toy_name varchar2(100)
) partition by hash ( toy_name ) partitions 4;

-- ハッシュ方式 × インデックス構成表
create table toys_part_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index 
  partition by hash ( toy_id ) partitions 4;
```

## クラスタ表

複数テーブルの特定カラムを`クラスタキー`として、同一クラスタキーをもつ複数テーブルを一つに結合したテーブルを`クラスタ表`と呼ぶ。
**同一クラスタキー**をもつ複数テーブルのデータは、**同一データブロック**に格納される。

:::note
クラスタ表を作成する場合は、事前に`(表)クラスタ`を作成する必要がある。
:::

```sql:クラスタ表の作成
-- クラスタの作成
create cluster toy_cluster (
  toy_name varchar2(100)
);

-- クラスタキーを指定したクラスタ表の作成
create table toys_cluster_tab (
  toy_name varchar2(100)
) cluster toy_cluster ( toy_name );

create table toy_owners_cluster_tab (
  owner    varchar2(20),
  toy_name varchar2(100)
) cluster toy_cluster ( toy_name );
```

# テーブルの削除(drop table)

```sql:テーブルの削除
drop table toys_heap;
```

# 💡 データ型の定義

|区分|型|概要|
|---|---|---|
|文字列|varchar2|32,767[bytes]以下の可変長|
|^|char|2,000[bytes]以下の固定長|
|^|clob|4$ b $[GB]以下の可変長($ b $はブロックサイズ)|
|^|nvarchar2<br>nchar<br>nclob|Unicode文字列のみ対応|
|数値|number($ p $, $ s $)|小数第$ s $位で丸め込まれた$ p $桁までの数値|
|^|float|小数(非推奨)|
|^|binary_float|32[bit]の単精度浮動小数点数|
|^|binary_double|64[bit]の倍精度浮動小数点数|
|^|integer|= `number(*, 0)`|
|^|real|= `float(63)`|
|日付|date|`yyyy-mm-dd hh24:mi:ss`で表される日時|
|^|timestamp|`yyyy-mm-dd hh24:mi:ss.ff`で表される日時|
|^|time interval|`年→月`または`日→秒`に変換して行う日付の加減算|
|バイナリ|raw|32,767[bytes]以下のバイナリデータ|
|^|blob|4$ b $[GB]以下のバイナリデータ($ b $はブロックサイズ)|

```sql:各データ型の定義
-- 文字列
create table character_data (
  varchar_10_col   varchar2(10),
  varchar_4000_col varchar2(4000),
  char_10_col      char(10),
  clob_col         clob
);

-- 数値
create table numeric_data (
  number_3_sf_2_dp  number(3, 2),
  number_3_sf_2     number(3, -2),
  number_5_sf_0_dp  number(5, 0),
  integer_col       integer,
  float_col         float(10),
  real_col          real,
  binary_float_col  binary_float,
  binary_double_col binary_double
);

-- 日付
create table datetime_data (
  date_col                      date,
  timestamp_with_3_frac_sec_col timestamp(3),
  timestamp_with_tz             timestamp with time zone,
  timestamp_with_local_tz       timestamp with local time zone,
  year_to_month_col             interval year to month,
  day_to_second_col             interval day to second
);

-- バイナリ
create table binary_data (
  raw_col  raw(1000),
  blob_col blob
);
```

# テーブルの変更

## カラムの追加(add)

:::note
1テーブル内のカラム数は最大で**1000列**
:::

```sql
alter table this_table_has_three_columns add (
  this_is_a_timestamp_column    timestamp, 
  this_is_a_binary_large_object blob
);
```

## カラムの削除(drop)

```sql
alter table this_table_has_three_columns drop (
  this_is_a_timestamp_column, 
  this_is_a_binary_large_object
);
```

# テーブル設計

## 概念モデル

`概念モデル(Conceptual Model)`では、データベース上に登録する**実体(ヒト・モノ)** を定義する。

|実体|概要|
|---|---|
|患者|治療を受けるヒト|
|医師|診断・処方を行うヒト|
|予約|予約日時・場所|

## 論理モデル

`論理モデル(Logical Model)`では、概念モデルで定義した**実体の属性**(=データ型・制約)を定義する。

:::note
**太字**は正規化(Normalization)によって論理モデルで追加された項目
:::

:::note
正規化(Normalization) ・・・ データ間の依存関係を見つけ、テーブルを分割すること。
:::

|実体|属性|
|---|---|
|患者|氏名|
|医師|氏名|
|予約|日時|
|^|病院名・住所|
|^|患者・担当医|
|**病院**|病院名|
|^|住所|

## 物理モデル

`物理モデル(Physical Model)`では、システム上の**非機能要件**(パフォーマンス・テーブル構成等)を考慮したテーブルを作成する。

```sql
create table consultants (
  consultant_id   integer,
  consultant_name varchar2(100)
);

create table patients (
  patient_id   integer,
  patient_name varchar2(100)
);

create table clinics (
  clinic_name varchar2(10),
  address     varchar2(1000)
);

create table appointments (
  appointment_id       integer,
  appointment_datetime date,
  clinic_name          varchar2(30),
  consultant_id        integer,
  patient_id           integer
);
```

## スーパータイプ/サブタイプ

物理モデルで定義した`consultants`・`patients`テーブルには担当医と患者の氏名をもつカラムがあるものの、**同一人物が医師でもあり患者でもある**場合は、`consultants`⇄`patients`テーブル間での整合性が取りづらくなる。
そのため、人物は`person`テーブルを**スーパータイプとして一括管理**し、医師や患者の詳細情報を別途`consultants`・`patients`テーブルで管理するのが望ましい。

:::note
**複数テーブルに同じ情報をもつカラムが存在**する場合、**スーパータイプ**として汎化(generalization)させることを検討する
:::

:::note
**同一テーブルに特定条件においてのみ適用されるカラムが存在**する場合、**サブタイプ**として特化(specialization)させることを検討する
:::

```sql
drop table consultants;
drop table patients;

create table people (
  person_id integer,
  full_name varchar2(100)
);

create table consultants (
  consultant_id  integer,
  salary         number(10,2),
  speciality     varchar2(30)
);

create table patients (
  patient_id      integer,
  hopsital_number integer
);
```

# レコードの取得

## 全件取得

`*(アスタリスク, スター)`は全ての可視カラムを取得することができる。
不可視カラムを取得する場合は、カラム名で指定する必要がある。

:::note
必要なカラムだけを取得することで、データ転送量を抑え、アプリケーションの高速化を図る
:::

```sql
select * from toys;

select toy_name, price from toys;
```

## フィルタリングを用いたレコード取得

:::note
`and`条件は`or`条件よりフィルタリング条件における優先順位が高いため、`or`条件を適用したい場合は**括弧(parenthese)** を用いるか、場合によっては`in`を用いることが可能
:::

```sql:リスト指定
-- (toy_name = 'Mr Bunnykins') or
-- (toy_name = 'Baby Turtle' and colour = 'green')
select * from toys
where  toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle'
and    colour = 'green';

-- (toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle') and
-- (colour = 'green')
-- 括弧を用いる場合
select * from toys
where  ( toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' )
and    colour = 'green';
-- inを用いる場合
select * from toys
where  toy_name in ( 'Mr Bunnykins', 'Baby Turtle' )
and    colour = 'green';
```

:::note
フィルタリング条件が**閉区間**の場合は`between`を用いることも可能
:::

```sql:範囲指定
-- フィルタリング条件は閉区間[6, 20]
select * from toys
where  price >= 6
and    price <= 20;
-- betweenを用いる場合
select * from toys
where  price between 6 and 20;
```

:::note
`like`条件とワイルドカード(`_`・`%`)を用いてワイルドカードによる条件指定も可能
`_` ・・・ **1文字**のワイルドカード
`%` ・・・ **0文字以上**のワイルドカード
:::

:::note warn
エスケープ文字は`escape`句で宣言する
エスケープ文字は任意の文字だが、慣習的に`\(バックスラッシュ)`や`#(ハッシュ)`が用いられる
:::

```sql:ワイルドカード指定
select * from toys
where  colour like '%_e_%';

-- エスケープ文字の指定
select * from toys
where  toy_name like '%#_%' escape '#';

select * from toys
where  toy_name like '%\_%' escape '\';
```

### Nullの扱い

Nullはあらゆる値に対して等価であるとも等価でないともいえない値であり、nullとの比較結果は`unknown`となるため、値がnullかどうかを調べる場合は`is null`条件を用いる。

```sql:is null条件
-- 取得結果が0件になってしまう
select * from toys
where price = null;

-- is null条件
select * from toys
where price is null;
```

### 否定条件

:::note
**Aでない**要素を満たすレコードを取得する場合は、`not`を条件式の前に付与するか、比較演算子`!=`/`<>`のどちらかを用いる
:::

:::note
**nullでない**レコードを取得する場合のみ`is not null`条件を用いる
:::

```sql:not, !=, <>
-- notを用いる場合
select * from toys 
where not colour = 'green';

-- 比較演算子を用いる場合
select * from toys 
where colour != 'green';
select * from toys 
where colour <> 'green';

-- nullでないレコード
select * from toys
where colour is not null;
```

# テーブルの結合

ここでは、以下の`toys`・`bricks`テーブルを用いて説明する。

- `toys`テーブル

|TOY_ID|TOY_NAME|TOY_COLOUR|
|---|---|---|
|1|Miss Snuggles|pink|
|2|Cuteasaurus|blue|
|3|Baby Turtle|green|

- `bricks`テーブル

|BRICK_ID|BRICK_COLOUR|BRICK_SHAPE|
|---|---|---|
|2|blue|cube|
|3|green|cube|
|4|blue|pyramid|

なお、**結合構文(join syntax)** には`Oracle方式`と`ANSI方式`の2種類が存在するが、**基本的にANSI方式を用いる**のが望ましい。

## クロス結合(cross join)

`クロス結合(cross join)`は、2つのテーブルの**直積(direct product, Cartesian product)集合**を表す。
直積(デカルト積)の定義は以下の通り。

```math
A \times B = \{ (a,b)\ |\ a \in A, b \in B \}
```

:::note warn
クロス結合を多用せざるをえない場合は、データ構造を見直す必要がある。
:::

```sql:クロス結合
-- Oracle構文
select * 
from   toys, bricks;

-- ANSI構文
select * 
from   toys
cross join bricks;
```

上記のSELECT文による取得結果は、いずれも以下の通りとなる。

|TOY_ID|TOY_NAME|TOY_COLOUR|BRICK_ID|BRICK_COLOUR|BRICK_SHAPE|
|---|---|---|---|---|---|
|1|Miss Snuggles|pink|2|blue|cube|
|1|Miss Snuggles|pink|3|green|cube|
|1|Miss Snuggles|pink|4|blue|pyramid|
|2|Cuteasaurus|blue|2|blue|cube|
|2|Cuteasaurus|blue|3|green|cube|
|2|Cuteasaurus|blue|4|blue|pyramid|
|3|Baby Turtle|green|2|blue|cube|
|3|Baby Turtle|green|3|green|cube|
|3|Baby Turtle|green|4|blue|pyramid|

## 内部結合(inner join)

`内部結合(inner join)`は、2つのテーブルの**直積集合のうち、`where`・`on`句で指定された条件を満たすレコードの部分集合(subset)** を表す。

:::note
**ANSI構文**における`inner`キーワードは省略可能
:::

```sql:内部結合
-- Oracle構文
select * 
from   toys, bricks
where  toy_id = brick_id;

-- ANSI構文
select * 
from   toys
inner  join bricks
on     toy_id = brick_id;

-- ANSI構文(innerキーワードの省略) ・・・ A
select * 
from   toys
join   bricks
on     toy_colour <> brick_colour;
```

`A`のSELECT文による取得結果は、以下の通りとなる。

|TOY_ID|TOY_NAME|TOY_COLOUR|BRICK_ID|BRICK_COLOUR|BRICK_SHAPE|
|---|---|---|---|---|---|
|1|Miss Snuggles|pink|2|blue|cube|
|1|Miss Snuggles|pink|3|green|cube|
|1|Miss Snuggles|pink|4|blue|pyramid|
|2|Cuteasaurus|blue|3|green|cube|
|3|Baby Turtle|green|2|blue|cube|
|3|Baby Turtle|green|4|blue|pyramid|

## 外部結合(outer join)

`外部結合(outer join)`は、**基準となるテーブルに対して可能な限り、`on`・`where`句で指定された条件を満たす外部テーブルのレコードを結合した集合**を表す。
条件を満たさない場合、**外部テーブルの各カラムの値が`null`のレコード**が結合される。

:::note
**ANSI構文**の場合、`from`句で指定したテーブルのカラムが**左側**に来る
**Oracle構文**の場合、`from`句で指定した**順番通り**に左側から並べられる
:::

:::note
**ANSI構文**における`outer`キーワードは省略可能
**ANSI構文**の場合、**結合の方向**を`left`・`right`キーワードで表す

※ $ A \leftarrow B $ の方向に結合される場合、$ A $ が基準テーブルとなる
:::

:::note
**Oracle構文**の場合、**結合対象テーブルのカラム**を`(+)`キーワードで表す
:::

```sql:外部結合
-- ANSI構文(基準テーブル: toys)
select * 
from   toys
left   outer join bricks
on     toy_id = brick_id;

-- Oracle構文(基準テーブル: toys)
select * 
from   toys, bricks
where  toy_id = brick_id (+);

-- ANSI構文(outerキーワードの省略, 基準テーブル: bricks) ・・・ B
-- toysテーブルのカラムが左側
select * 
from   toys
right  join bricks
on     toy_id = brick_id;

-- ANSI構文(outerキーワードの省略, 基準テーブル: bricks)
-- bricksテーブルのカラムが左側
select * 
from   bricks
left   join toys
on     toy_id = brick_id;
```

`B`のSELECT文による取得結果は、以下の通りとなる。

|TOY_ID|TOY_NAME|TOY_COLOUR|BRICK_ID|BRICK_COLOUR|BRICK_SHAPE|
|---|---|---|---|---|---|
|2|Cuteasaurus|blue|2|blue|cube|
|3|Baby Turtle|green|3|green|cube|
|(null)|(null)|(null)|4|blue|pyramid|

### 複数の外部結合条件の定義

:::note
複数の外部結合条件を定義したい場合、
**ANSI構文**では、`on`句に`and`・`or`条件を用いる
**Oracle構文**では、`where`句の`and`・`or`条件に対しても`(+)`キーワードを用いる
:::

```sql:外部結合におけるフィルタリング
-- ANSI構文(取得結果: 1件)
select * 
from   toys
join   bricks
on     toy_id = brick_id
where  brick_colour = 'green';

-- ANSI構文(取得結果: 3件)
select * 
from   toys
left   join bricks
on     toy_id = brick_id
and    brick_colour = 'green';

-- Oracle構文(取得結果: 1件)
select * 
from   toys, bricks
where  toy_id (+) = brick_id
and    brick_colour = 'green';

-- Oracle構文(取得結果: 3件) ・・・ C
select * 
from   toys, bricks
where  toy_id = brick_id (+)
and    brick_colour (+) = 'green';
```

`C`のSELECT文による取得結果は、以下の通りとなる。

|TOY_ID|TOY_NAME|TOY_COLOUR|BRICK_ID|BRICK_COLOUR|BRICK_SHAPE|
|---|---|---|---|---|---|
|3|Baby Turtle|green|3|green|cube|
|1|Miss Snuggles|pink|(null)|(null)|(null)|
|2|Cuteasaurus|blue|(null)|(null)|(null)|

### 完全外部結合(full outer join)

`完全外部結合(full outer join)`は、**2つのテーブルに対して相互に外部結合を行った集合**を表す。

:::note
**ANSI構文**では、`full join`句を用いて完全外部結合を定義できる
**Oracle構文**では、完全外部結合のキーワードが用意されていないため、**双方向の外部結合を定義**し、`union all`を用いて2つの取得結果を結合する。
:::

```sql:完全外部結合
-- ANSI構文
select * 
from   toys
full   join bricks
on     toy_id = brick_id;

-- Oracle構文 ・・・ D
select * 
from   toys, bricks
where  toy_id = brick_id (+)
-- 上部は toys ← bricks の外部結合
union all
-- 下部は toys → bricks の外部結合のうち、
-- toysテーブルの各カラムの値がnullであるレコード
select * 
from   toys, bricks
where  toy_id (+) = brick_id
and    toy_id is null;
```

`D`のSELECT文による取得結果は、以下の通りとなる。
この場合、上3行が**toys ← bricksの外部結合**を表し、下1行が**toys → bricksの外部結合**のうち、`toys`テーブルの各カラムの値が`null`であるレコードを表す。

|TOY_ID|TOY_NAME|TOY_COLOUR|BRICK_ID|BRICK_COLOUR|BRICK_SHAPE|
|---|---|---|---|---|---|
|2|Cuteasaurus|blue|2|blue|cube|
|3|Baby Turtle|green|3|green|cube|
|1|Miss Snuggles|pink|(null)|(null)|(null)|
|(null)|(null)|(null)|4|blue|pyramid|

# 集計関数

`集計関数(aggregate function)`は、**レコードを集計**する関数を表す。

|関数|概要|
|---|---|
|count|行数|
|sum|総和|
|min/max|最小値/最大値|
|avg|平均値|
|stddev|標準偏差(standard deviation)|
|median|中央値|
|variance|分散|
|stats_mode|最頻値(the most common value)|

ここでは、以下の`bricks`テーブルを用いて説明する。

- `bricks`テーブル

|COLOUR|SHAPE|WEIGHT|
|---|---|---|
|red|cube|1|
|red|pyramid|2|
|red|cuboid|1|
|blue|cube|1|
|blue|pyramid|2|
|green|cube|3|

## 射影条件の指定(distinct, unique, all)

テーブルを射影(project)する際に`distinct(unique)`・`all`キーワードを付与することで、射影条件を指定することができる。
※射影(projection) ・・・ テーブルから**一部のカラムを抽出**すること

|キーワード|射影対象|
|---|---|
|`distinct(unique)`|一意なレコード|
|`all`|全レコード|

:::note warn
一つの`DML(Data Manipulation Language)`文中で指定できる射影条件(`distinct`・`unique`・`all`)は**一つ**まで
:::

```sql:キーワードによる射影条件の比較
-- allキーワード(E-1)
select all colour from bricks;

-- distinct, uniqueキーワード(E-2)
select distinct colour from bricks;
select unique colour from bricks;
```

`E-1`・`E-2`のSELECT文による取得結果は、それぞれ以下の通りとなる。

- `bricks`テーブル(all, E-1)

|COLOUR|
|---|
|red|
|red|
|red|
|blue|
|blue|
|green|

- `bricks`テーブル(distinct & unique, E-2)

|COLOUR|
|---|
|red|
|blue|
|green|

:::note
取得結果を別名で表示する場合、テーブル・カラム名の直後に`as <別名>`を付与
※`as`キーワードは省略可能
:::

```sql:集計対象の限定
-- all vs. distinct ・・・ F
select sum ( weight ) total_weight, 
       sum ( distinct weight ) sum_of_unique_weights, 
       avg ( weight ) overall_mean, 
       avg ( distinct weight ) mean_of_unique_weights 
from   bricks;
```

`F`のSELECT文による取得結果は、以下の通りとなる。

|TOTAL_WEIGHT|SUM_OF_UNIQUE_WEIGHTS|OVERALL_MEAN|MEAN_OF_UNIQUE_WEIGHTS|
|---|---|---|---|
|10|6|1.66666666666666666666666666666666666667|2|

## グループ化(group by)

特定のカラムの値をもとに、**一つのテーブルを複数のテーブルに分割**することを**グループ化(grouping)** と呼ぶ。

:::note
`group by`句を用いることで、**グループ化条件**を指定する
:::

:::note
グループ化を行う場合、集計関数の集計対象は**各グループ**になる
:::

```sql:グループ化
-- shape, weightカラムの値をもとにグループ化 ・・・ G
select shape, weight, count (*) 
from   bricks
group  by shape, weight;
```

ここで、`G`のグループ化の過程に着目するため、`ID`カラムを以下のように一番左に追加した`bricks`テーブルを再掲する。

- `bricks`テーブル(`ID`カラムを追加して再掲)

|ID|COLOUR|SHAPE|WEIGHT|
|---|---|---|---|
|A|red|cube|1|
|B|red|pyramid|2|
|C|red|cuboid|1|
|D|blue|cube|1|
|E|blue|pyramid|2|
|F|green|cube|3|

`SHAPE`・`WEIGHT`カラムの値によって、以下のようにグループ化される。

- `SHAPE = cube` & `WEIGHT = 1` のグループ

|ID|COLOUR|SHAPE|WEIGHT|
|---|---|---|---|
|A|red|cube|1|
|D|blue|cube|1|

- `SHAPE = pyramid` & `WEIGHT = 2` のグループ

|ID|COLOUR|SHAPE|WEIGHT|
|---|---|---|---|
|B|red|pyramid|2|
|E|blue|pyramid|2|

- `SHAPE = cuboid` & `WEIGHT = 1` のグループ

|ID|COLOUR|SHAPE|WEIGHT|
|---|---|---|---|
|C|red|cuboid|1|

- `SHAPE = cube` & `WEIGHT = 3` のグループ

|ID|COLOUR|SHAPE|WEIGHT|
|---|---|---|---|
|F|green|cube|3|

以上から、`G`のSELECT文による取得結果は以下の通りとなる。

|SHAPE|WEIGHT|COUNT(*)|
|---|---|---|
|cube|1|2|
|pyramid|2|2|
|cuboid|1|1|
|cube|3|1|

### グループ化後の値でフィルタリング(having)

`having`句を用いて、**各グループに対する集計関数の結果でフィルタリング**することができる。

:::note
`where`句は**グループ化前**のフィルタリング条件を表し、
`having`句は**グループ化後**のフィルタリング条件を表す
:::

```sql:グループ化後のフィルタリング
-- フィルタリング条件がグループ化"前"の値であればwhere句を用いる
select colour, count (*) 
from   bricks
where  weight > 1
group  by colour;

-- フィルタリング条件がグループ化"後"の値であればhaving句を用いる ・・・ H
select colour, sum( weight )
from   bricks
having sum ( weight ) > 1
group  by colour;
```

`H`のSELECT文による取得結果は、以下の通りとなる。

|COLOUR|SUM(WEIGHT)|
|---|---|
|red|4|
|blue|3|
|green|3|

### 小計・総計の算出(rollup, cube)

任意のグループに対して、`rollup`・`cube`を用いて**小計(subtotal)や総計(total)** を求めることができる。
なお、**小計・総計を表すカラムの値には`null`が挿入**される。

:::note
`rollup`は、引数の**右のカラムから順に**小計を求める
・・・ **左のカラムから順にグループ化条件の優先度が高い**とも言える
:::

:::note
`cube`は、引数の**クロス集計**で小計を求める
:::

```sql:小計・総計の算出
-- rollup  ・・・ I
select colour, shape, count (*)
from   bricks
group  by rollup ( colour, shape );

-- cube  ・・・ J
select colour, shape, count (*)
from   bricks
group  by cube ( colour, shape );
```

`shape` → `colour` の順に小計を行う`I`のSELECT文による取得結果は、以下の通りとなる。

|COLOUR|SHAPE|COUNT(*)|グループ化条件|
|---|---|---|---|
|red|cube|1|`COLOUR = red` & `SHAPE = cube`|
|red|pyramid|1|`COLOUR = red` & `SHAPE = pyramid`|
|red|cuboid|1|`COLOUR = red` & `SHAPE = cuboid`|
|blue|cube|1|`COLOUR = blue` & `SHAPE = cube`|
|blue|pyramid|1|`COLOUR = blue` & `SHAPE = pyramid`|
|green|cube|1|`COLOUR = green` & `SHAPE = cube`|
|red|(null)|3|`COLOUR = red`(を満たす`SHAPE`の**小計**)|
|blue|(null)|2|`COLOUR = blue`(を満たす`SHAPE`の**小計**)|
|green|(null)|1|`COLOUR = green`(を満たす`SHAPE`の**小計**)|
|(null)|(null)|6|-(**総計**; `COLOUR`(と`SHAPE`)の**小計**)|

一方で、`colour`×`shape`のクロス集計を行う`J`のSELECT文による取得結果は、以下の通りとなる。

|COLOUR|SHAPE|COUNT(*)|グループ化条件|
|---|---|---|---|
|(null)|(null)|6|-(**総計**; `COLOUR`(と`SHAPE`)の**小計**)|
|(null)|cube|3|`SHAPE = cube`(を満たす`COLOUR`の**小計**)|
|(null)|cuboid|1|`SHAPE = cuboid`(を満たす`COLOUR`の**小計**)|
|(null)|pyramid|2|`SHAPE = pyramid`(を満たす`COLOUR`の**小計**)|
|red|(null)|3|`COLOUR = red`(を満たす`SHAPE`の**小計**)|
|red|cube|1|`COLOUR = red` & `SHAPE = cube`|
|red|cuboid|1|`COLOUR = red` & `SHAPE = cuboid`|
|red|pyramid|1|`COLOUR = red` & `SHAPE = pyramid`|
|blue|(null)|2|`COLOUR = blue`(を満たす`SHAPE`の**小計**)|
|blue|cube|1|`COLOUR = blue` & `SHAPE = cube`|
|blue|pyramid|1|`COLOUR = blue` & `SHAPE = pyramid`|
|green|(null)|1|`COLOUR = green`(を満たす`SHAPE`の**小計**)|
|green|cube|1|`COLOUR = green` & `SHAPE = cube`|

# レコードの追加(insert)

レコードを追加する場合は、`insert`文を用いる。

以下の説明では、次のようなテーブルを用いる。

- `toys`テーブル

|COLUMN_NAME|DATA_TYPE|NULLABLE|DATA_DEFAULT|
|---|---|---|---|
|TOY_ID|NUMBER(38,0)|Yes|(null)|
|TOY_NAME|VARCHAR2(100 BYTE)|Yes|(null)|
|COLOUR|VARCHAR2(10 BYTE)|Yes|(null)|

- `bricks`テーブル

|COLUMN_NAME|DATA_TYPE|NULLABLE|DATA_DEFAULT|
|---|---|---|---|
|BRICK_ID|NUMBER(38,0)|Yes|(null)|
|COLOUR|VARCHAR2(10 BYTE)|Yes|(null)|
|SHAPE|VARCHAR2(10 BYTE)|Yes|(null)|

:::note
カラム名は省略可能だが、**データを追加するカラム名を記述**することで保守性を高める
※カラム名を省略する場合は、テーブルで定義された**全てのカラムに対してデータを指定**する必要がある
:::

:::note
**データを指定しないカラム**に対しては`null`が挿入される
:::

```sql:レコードの追加
-- カラム名を省略(非推奨)した単一行の挿入
insert into toys values ( 1, 'Miss Snuggles', 'pink' );

-- カラム名を指定(推奨)した単一行の挿入
insert into toys ( toy_id, toy_name ) values ( 2, 'Baby Turtle' );

-- 複数行の挿入 ・・・ K
insert into bricks ( brick_id )
  select toy_id
  from   toys;
```

`K`のINSERT文では、まず`toys`テーブルに対してクエリを実行し、その取得結果を`bricks`テーブルに追加している。

`K`のINSERT文を実行した後の`bricks`テーブル情報は、以下の通り。

|BRICK_ID|COLOUR|SHAPE|
|---|---|---|
|1|(null)|(null)|
|2|(null)|(null)|

## 単一行/複数行の挿入パフォーマンス比較

:::note
同じ数のレコードをテーブルに追加する場合、**DML文の実行回数が少ない**複数行の挿入の方が高速
:::

`bricks`テーブルに50,000行を追加する以下の`PL/SQL`を実行する。

:::note warn
`DBMS_OUTPUT`パッケージの出力を表示する場合、以下のPL/SQLを実行する。

```sql
-- `DBMS_OUTPUT`パッケージの出力を表示
SET SERVEROUTPUT ON;
```
:::

```sql:単一行と複数行のパフォーマンス比較(PL/SQL)
declare
  start_time   pls_integer;
  insert_count pls_integer := 50000;
begin
  start_time := dbms_utility.get_time ();
  
  for i in 1 .. insert_count loop
    insert into bricks 
    values ( i, 'red', 'cube' );
  end loop;
  
  dbms_output.put_line ( 'Single-row duration = ' || ( dbms_utility.get_time () - start_time) );
  
  rollback;
  
  start_time := dbms_utility.get_time ();
  
  insert into bricks
    select level, 'red', 'cube' from dual
    connect by level <= insert_count;
  
  dbms_output.put_line ( 'Multi-row duration = ' || ( dbms_utility.get_time () - start_time) );
  
  rollback;
end;
/
```

```txt:出力結果
Single-row duration = 34
Multi-row duration = 2
```

## DML文による変更の保存(commit)

`データ操作言語(Data Manipulation Language; DML)`による**データの変更(=トランザクション)を確定**する場合は`commit`文を実行する。

:::note
`commit`文が送信されるまで、更新後のデータについて**他ユーザは参照不可**であり、**自ユーザのセッション内でのみ参照可能**
※多くの開発ツールでオートコミット機能が利用可能
:::

```sql:DML文による変更の保存
-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 6, 'Green Rabbit', 'green' );

-- トランザクションの確定(コミット)
commit;
```

## DML文による変更の取消(rollback)

`データ操作言語`による**トランザクションを取り消す**場合は`rollback`文を実行する。

:::note
`rollback`は**最後に`commit`文が送信された状態**までしかロールバックできないため、複数のDML文を実行する場合は**全ての変更が完了するまでコミットしない**のが望ましい
:::

```sql:DML文による変更の取消
-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 7, 'Pink Rabbit', 'pink' );

-- トランザクションの取消(ロールバック)
rollback;
```

## セーブポイントの作成(savepoint)

トランザクションの途中でロールバック可能なセーブポイントを作成する場合は`savepoint`文を実行する。

:::note
**セーブポイント ≠ コミット** であるため、全てのセーブポイントでの変更を`rollback`文で取り消すことができる
:::

:::note warn
`sp1` → `sp2` という流れでセーブポイントを作成し`sp1`までロールバックした場合、`sp2`へのロールバックはできない
:::

```sql:セーブポイントの作成
-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 8, 'Pink Rabbit', 'pink' );

-- セーブポイントの作成
savepoint after_six;

-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 9, 'Purple Ninja', 'purple' );

-- セーブポイントへのロールバック
rollback to savepoint after_six;

-- 最後にコミットされた状態へのロールバック
rollback;
```

## マルチテーブルインサート(insert all)

`insert all`文を用いることで、**複数レコードを複数テーブルに対して挿入**することができる。

:::note
`dual`テーブルはOracle Database上で事前定義されたテーブルであり、マルチテーブルインサートで必要な**SELECT文のスタブ**として、**実テーブル上に存在しないデータ**(= 値, 関数)を追加する場合に慣習的に用いられる
:::

```sql:マルチテーブルインサート
-- マルチデーブルインサート
insert all
  into toys ( toy_id ) values ( id )
  into bricks ( brick_id ) values ( id )
  into bricks ( brick_id ) values ( id )
select 0 id from dual;
```

### 条件付きマルチテーブルインサート(all, first)

マルチテーブルインサートにおいて、**条件式に基づいてレコードを追加するテーブルを指定**する場合は`when`句を用いて条件式を定義する。

:::note warn
`else`節は他言語における条件文とは異なり、**直前の条件式だけでなく他の条件全てに該当しない**場合に`true`として適用される
:::

また、`all`・`first`キーワードを用いて**各条件式の評価方法**を指定することができる。

|キーワード|評価方法|
|---|---|
|`all`|**全ての条件式**を評価|
|`first`|**最初に条件式が`true`となった時点**で、他の条件式は評価しない|

以下の説明では、次のようなテーブルを用いる。

- `toys`テーブル

|TOY_ID|TOY_NAME|COLOUR|
|---|---|---|
|11|Cuteasaurus|blue|
|12|Sir Stripypants|blue|
|13|White Rabbit|white|

- `bricks`テーブル(中身は空の状態)

|BRICK_ID|COLOUR|SHAPE|
|---|---|---|

```sql:条件付きマルチテーブルインサート
-- allキーワード ・・・ L
insert all
  when colour = 'blue' then  -- 条件式①
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  when toy_name = 'Cuteasaurus' then  -- 条件式②
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  else  -- 条件式③
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  select * from toys;

-- firstキーワード ・・・ M
insert first
  when colour = 'blue' then
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  when toy_name = 'Cuteasaurus' then
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  else
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  select * from toys;
```

`L`のINSERT文を実行した後の`bricks`テーブル情報は、以下の通り。

|BRICK_ID|COLOUR|SHAPE|
|---|---|---|
|11|blue|(null)|
|12|blue|(null)|
|11|blue|(null)|
|13|white|(null)|

上記の結果から、`all`キーワードを用いた`L`のINSERT文では、`toys`テーブルの各レコードが次のように評価されたことが分かる。

|TOY_ID|TOY_NAME|COLOUR|`true`と評価された条件式|
|---|---|---|---|
|11|Cuteasaurus|blue|①, ②|
|12|Sir Stripypants|blue|①|
|13|White Rabbit|white|③|

一方で、`M`のINSERT文を実行した後の`bricks`テーブル情報は、以下の通り。

|BRICK_ID|COLOUR|SHAPE|
|---|---|---|
|11|blue|(null)|
|12|blue|(null)|
|13|white|(null)|

上記の結果から、`first`キーワードを用いた`M`のINSERT文では、`toys`テーブルの各レコードが次のように評価されたことが分かる。

|TOY_ID|TOY_NAME|COLOUR|`true`と評価された条件式|
|---|---|---|---|
|11|Cuteasaurus|blue|①|
|12|Sir Stripypants|blue|①|
|13|White Rabbit|white|③|

# データの更新(update)

レコードのデータを更新する場合は、`update`文を用いる。

:::note
データが更新されたレコードは、更新したユーザが**コミットまたはロールバックを行うまでデータベースによってロック**される
:::

:::note
`where`句で**レコードを指定しない**場合は、**全レコード**のデータが更新される
:::

```sql:データの更新
-- データの更新
update bricks
set    quantity = quantity + 10
where  colour = 'blue'
and    shape = 'cube';
```

## デッドロック

同じレコードを操作する複数のトランザクションが**互いにロック解除待ち状態となり、処理が進まなくなる**ことを**デッドロック(deadlock)** と呼ぶ。

以下のPL/SQLでは、**自律型トランザクション(Autonomous Transaction)** を用いてデッドロックを引き起こしている。

```sql:デッドロック(PL/SQL)
update bricks
set    quantity = 60;

declare
  pragma autonomous_transaction;
begin
  update bricks
  set    unit_weight = 55;
  commit;
end;
/
```

```txt:出力結果
エラー・レポート -
ORA-00060: リソース待機の間にデッドロックが検出されました。
ORA-06512: 行4
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.
```

### 悲観的ロック(select for update)

:::note
デッドロックを未然に防ぐには、**トランザクションの冒頭**で予め`select .. for update`文を用いて、**更新対象のレコードをロック**しておく
:::

:::note warn
悲観的ロックを利用するには、アプリケーションが**ステートフル(stateful)** である必要があるため、**ステートレス(stateless)** なアプリケーションと連携する場合は後述する**楽観的ロック**を用いる。
※**ステートフル**ではデータの取得と更新が**同じトランザクション**で行われ、**ステートレス**ではそれぞれ**異なるトランザクション**で行われる
:::

```sql:for update
-- for updateキーワードによる悲観的ロック
select * from bricks
where  colour = 'red'
for    update;

-- DML文の送信
update bricks
set    quantity = 1001
where  colour = 'red';

-- トランザクションの確定(コミット)
commit;
```

## ロストアップデート

同一レコードに対する変更タイミングが重なり、**最終更新によって他の更新が上書きされてしまう現象**を**ロストアップデート(lost update)** と呼ぶ。

```sql:ロストアップデート
-- unit_weightの更新
update bricks
set    quantity = 60,
       unit_weight = 8
where  colour = 'red'
and    shape  = 'cylinder';

-- quantityの更新
-- → unit_weightが更新前の値で上書きされる
update bricks
set    quantity = 1001,
       unit_weight = 13
where  colour = 'red'
and    shape  = 'cylinder';
```

### 楽観的ロック

ロストアップデートへの対処法として、**悲観的ロック(pessimistic locking)** と**楽観的ロック(optimistic locking)** が挙げられる。

悲観的ロックと楽観的ロックの説明は、それぞれ以下の通りである。

|手法|概要|
|---|---|
|悲観的ロック|トランザクションの**冒頭**でレコードを事前にロック|
|楽観的ロック|トランザクションの**途中(=更新時)** で他カラムの値の整合性を確認した上で値を更新|

楽観的ロックにおける整合性確認の手法としては、次のような例が挙げられる。

- **バージョン番号**や**最終更新日時**を表すカラムを追加
- **全カラムの更新前の値**を`where`句で指定し抽出

:::note warn
**カラム数が多いテーブル**の場合に後者の手法を用いると`where`句が肥大化するため、**バージョン番号や最終更新日時をレコードに追加**して整合性を確認するのが望ましい
:::

```sql:楽観的ロック
-- 全カラムの値で確認する場合
update bricks
set    quantity = 60,
       unit_weight = 8
where  colour = 'red'
and    shape  = 'cylinder'
and    quantity = 60
and    unit_weight = 13;

-- バージョン番号を表すカラムを追加(既定値: 1)
alter table bricks add ( version_number integer default 1 );

-- バージョン番号で確認する場合
update bricks
set    quantity = 1001,
       unit_weight = 13,
       version_number = version_number + 1
where  colour = 'red'
and    shape  = 'cylinder'
and    version_number = 1;
```

# レコードの削除(delete, trancate)

レコードを削除する場合は、`delete`または`trancate`文を用いる。

`delete`と`trancate`の違いは、以下の通り。

|文|区分|削除対象|テーブルへの表領域の割り当て|膨大な量のレコード削除|
|---|---|---|---|---|
|`delete`|**DML**|**一部または全て**のレコード|解除しない|低速|
|`trancate`|**DDL**|**全て**のレコード|解除する(※)|高速|

:::note
`delete文`における`from`キーワードは省略可能
**表領域の割り当てを解除しない**ため、同程度の**レコード再挿入が高速**
:::

:::note
`trancate`文は**データ定義言語(DDL)** であるため、コミットが不要
既定では**表領域の割り当てを解除**するが、`reuse storage`キーワードを付与することで解除させないことも可能
:::

```sql:レコードの削除
-- delete文を用いた全レコードの削除
delete from toys;

-- 一部レコードの削除
delete toys where price < 1;

-- trancate文を用いた全レコードの削除(表領域の割り当てを解除する)
truncate table toys;

-- trancate文を用いた全レコードの削除(表領域の割り当てを解除しない)
truncate table toys reuse storage;
```

## 物理削除/論理削除

「削除」には**物理削除(hard delete)** と**論理削除(soft delete)** の2種類が存在し、それぞれ以下のような違いをもつ。

|手法|概要|膨大な量のレコード削除|システム全体のレスポンス|
|---|---|---|---|
|物理削除|テーブルからレコードを削除|レコードを削除<br>→ **低速**|データ量 少<br>→ **高速**|
|論理削除|フラグで削除済かどうかを識別|フラグを更新<br>→ **高速**|データ量 多<br>→ **低速**|

```sql:物理削除/論理削除
-- 物理削除(delete文)
delete toys;

-- 物理削除(trancate文)
truncate table toys;

-- 削除フラグの追加
alter table toys add is_deleted varchar2(1) default 'N';

-- 論理削除(削除フラグを'N'→'Y'に更新) ・・・ N
update toys
set    is_deleted = 'Y'
where  toy_name = 'Cuteasaurus';
```

`N`のUPDATE文を実行した後の`toys`テーブル情報は次の通りであり、以降このテーブル情報をもとに説明する。

|TOY_NAME|PRICE|IS_DELETED|
|---|---|---|
|Baby Turtle|0.01|N|
|Miss Snuggles|0.51|N|
|Cuteasaurus|10.01|Y|
|Sir Stripypants|14.03|N|
|Purple Ninja|14.22|N|

論理削除は**不要レコードを高速に扱える**一方で、`where`句に**フラグの条件式を挿れる**必要があるという点で少し複雑になる。

そこで、Oracle Databaseでは**現存レコードの抽出**を簡易化するために、以下のような仕組みが用意されている。

- ビュー(View) ・・・ **一部のレコードを抽出**して作成した仮想テーブル
- 仮想プライベートデータベース(Virtual Private Database; VPD) ・・・ 閲覧可能なレコードを**ユーザ単位で制御**する機能
- データベース内アーカイブ(In-Database Archiving) ・・・ `ora_archive_state`という**不可視カラム**が`0(=現存)`か`0以外(=削除済)`かで削除済レコードを識別する機能

### ビューを用いた現存レコードの抽出

```sql:ビューを用いた現存レコードの抽出
-- ビューの作成
create or replace view active_toys as
  select * from toys
  where is_deleted = 'N';
```

### VPDを用いた現存レコードの抽出

:::note warn
**VPDポリシーが適用されたテーブルはカラム・レコード単位で保護**されるため、テーブルに変更を加える際は**VPDポリシーを削除**する必要がある
:::

```sql:VPDを用いた現存レコードの抽出
-- 以下、DBAロールが付与されたユーザで実行 --
-- ユーザ(visible_only)の作成
create user visible_only identified by visib;

-- データベースへの接続を許可
grant connect to visible_only;

-- 他ユーザのテーブルへの接続を許可
grant select on ORCLADM.toys to visible_only;

-- VPDファンクションの作成
create or replace function vpdfunc
  (schema_var varchar2, table_var varchar2)
  return varchar2
is
  cond varchar2(400);
  username varchar2(20);
begin
  select sys_context('USERENV', 'SESSION_USER') into username from dual;
  if (username = 'VISIBLE_ONLY') then
    cond := 'is_deleted = ''N''';
  else
    cond := 'is_deleted = ''Y''';
  end if;
  return cond;
end vpdfunc;
/

-- VPDファンクションをVPDポリシーとして適用
begin
  dbms_rls.add_policy(
    object_schema => 'ORCLADM',  -- VPDによって保護するスキーマ
    object_name => 'toys',  -- VPDによって保護するスキーマ内オブジェクト
    policy_name => 'VPDPOL',  -- VPDポリシー名
    function_schema => 'ORCLADM',  -- VPDポリシーを保持するスキーマ
    policy_function => 'vpdfunc');  -- VPDポリシーとして規定するVPDファンクション
end;
/

-- VPDポリシーの削除
begin
  dbms_rls.drop_policy(
    object_schema => 'ORCLADM',  -- VPDファンクションの第一引数
    object_name => 'toys',  -- VPDファンクションの第二引数
    policy_name => 'VPDPOL');  -- VPDファンクションが適用されたVPDポリシー
end;
/

-- 以下、作成したユーザ(visible_only)で実行 --
-- ORCLADM.toysテーブルの参照 ・・・ O
select * from ORCLADM.toys;
```

`O`のSELECT文による取得結果は、以下の通りとなる。

|TOY_NAME|PRICE|IS_DELETED|
|---|---|---|
|Baby Turtle|0.01|N|
|Miss Snuggles|0.51|N|
|Sir Stripypants|14.03|N|
|Purple Ninja|14.22|N|

### データベース内アーカイブを用いた現存レコードの抽出

:::note
`row archival`キーワードを用いて**データベース内アーカイブを有効化**すると、システムによって`ora_archive_state`という**不可視カラム**がテーブルに追加される
:::

```sql:データベース内アーカイブを用いた現存レコードの抽出
-- データベース内アーカイブの有効化(= カラムora_archive_stateの追加)
alter table toys row archival;

-- toy_name = 'Baby Turtle'のレコードを論理削除 ・・・ P
update toys 
set    ora_archive_state = '1'
where  toy_name = 'Baby Turtle';

-- セッション内で全レコードを可視化する ・・・ Q
alter session set row archival visibility = all;

-- セッション内でアーカイブ済レコードのみを可視化する ・・・ P
alter session set row archival visibility = active;
```

データベース内アーカイブが有効化され、**アーカイブ済レコードのみが可視化**された`P`の状態における`toys`テーブル情報は以下の通り。

- `toys`テーブル(状態P)

|TOY_NAME|PRICE|IS_DELETED|
|---|---|---|
|Miss Snuggles|0.51|N|
|Cuteasaurus|10.01|Y|
|Sir Stripypants|14.03|N|
|Purple Ninja|14.22|N|

一方で、**全レコードが可視化**された`Q`の状態における`toys`テーブル情報は以下の通り。

- `toys`テーブル(状態P)

|TOY_NAME|PRICE|IS_DELETED|
|---|---|---|
|Baby Turtle|0.01|N|
|Miss Snuggles|0.51|N|
|Cuteasaurus|10.01|Y|
|Sir Stripypants|14.03|N|
|Purple Ninja|14.22|N|