独り言

プログラミングの講師をしています。新人研修で扱う技術の解説と個人の技術メモ、技術書の紹介など

【SQL】SQL入門（DBの様々な機能編）

SQL

ここでは、テーブル以外のDBで使用されるオブジェクトについて解説します。
RDBでは、テーブルと最低限のインデックスがあればシステムとして成り立ちますが、DBの機能を最大限活用することで、よりプログラミングを効率化したり、メンテナンス性を高めたり、処理を高速化することができるようになります。
ここではSQLの基本は知っている前提とします。
SQLの基本については下記の記事を参照ください。

case10.hateblo.jp

case10.hateblo.jp

目次

  • 準備
  • インデックス
  • ビュー
  • マテリアライズド・ビュー
  • ストアドプログラム
    • ファンクション
    • プロシージャ
    • パッケージ
  • トリガー
  • シーケンス
  • シノニム
  • データベースリンク

準備

DBの様々な機能を学習するために、最低限のテーブルとデータを用意しておきます。

下記のSQLを実行して、itemsテーブルとサンプルデータを作成しておいてください。

create table items as 
select 1 id, 'リンゴ' item_name, 120 price
union
select 2, 'みかん', 150
union
select 3, 'バナナ', 300
union
select 4, 'ボールペン', 300
union
select 5, 'ティッシュ', 250
union
select 6, '電池', 400
union
select 7, 'コップ', 700
union
select 8, '椅子', 2000
union
select 9, '地球儀', 5000
;

インデックス

インデックスは日本語で索引という意味で、レコードの検索（SELECT文）を速くするための機能です。
辞書や専門書には、後ろのページに索引がついています。
調べたい用語があった時、索引から用語を探し、索引から該当のページを探すことで、知りたい情報に素早くアクセスすることができます。
DBのインデックスもこれと同じ仕組みです。
SELECT文でレコードを検索するときに、インデックスからレコードを検索することができれば、高速にデータを取得することができます。

インデックスは1つのテーブルに対して複数作成することができます。
テーブル作成の際にPRIMARY KEY 及びUNIQUE に設定したカラムについては自動でインデックスが作成されます。
それ以外のカラムでインデックスを作成したい場合は、手動でCREATE INDEX を使ってインデックスを作成します。

CREATE INDEX <インデックス名> ON <テーブル名> (column1, …)

例えば、テーブルを以下のようなSQLで作成したとします。

CREATE TABLE items (
    id int PRIMARY KEY
    , item_name vaarchar(50)
    , price real
);

この場合、PRIMARY KEYであるidについては自動的にインデックスが作成されます。
このとき、レコードの件数が増えても、idを使って検索は高速にデータを取得できます。

-- インデックスが使用されるため高速
SELECT * FROM items WHERE id = 100;

一方、item_nameにはインデックスがないので、レコード件数が増えた時、item_nameを使用した検索は遅くなります。

-- インデックスが使用されないので低速
SELECT * FROM items WHERE item_name = 'えんぴつ';

item_nameに対してインデックスを作成すれば、高速になります。

CREATE INDEX item_name_idx ON items (item_name);

インデックスを作成するとSELECT文の高速化が期待できますが、作り過ぎには注意です。
インデックスを作成するとその分ディスク容量を圧迫します。
また、インデックスが多いと、INSERTやUPDATEなど、更新処理を実行したときの負荷は増えます。
なので適切に設計して作成することが大切です。
また、インデックスを作成しても、SELECT文の書き方によってはインデックスが適切に使用されず、高速化されない場合があります。
詳しくはここでは割愛します。

ビュー

ビューとは、仮想的な表のことです。 結合やgroup byなどを使用する複雑なSELECT文をビューとして定義しておくことで、SELECT文を簡潔に書くことができます。 また、テーブルの一部のカラムだけに絞り込んでビューとして定義しておくことで、一般のユーザーに不必要なデータを見せないようにすることもできます。

構文

CREATE VIEW <VIEW名> AS SELECT文

サンプル

CREATE VIEW high_price_items AS
SELECT * FROM items
WHERE price >= 1000;

確認

SELECT * FROM high_price_items;

結果

id item_name price
8 椅子 2000
9 地球儀 5000

priceが1000以上のレコードのみが取得できることが確認できます。

削除

-- 削除する場合にはDROPを使用する
DROP VIEW high_price_items;

ビューは主にSELECT文を簡略化するために使用しますが、つくり方によってはビューに対して更新も可能です。
例えば、1つのテーブルから一部のカラムを抜き出しただけのビューの場合は更新が可能です。
複数のテーブルを結合したり、GROUP BY を使って加工したビューの場合は更新は不可です。

マテリアライズド・ビュー

一般に、SELECT文を実行するとき、結合やGROUP BY などが多くなると速度が低下します。
ビューを作成すると、SQL文は簡潔に書くことができますが、SQL文が簡潔になったからといって速度が向上するわけではありません。
ビューはあくまで仮想的なテーブルで、SQLでビューを参照した場合には、ビューの定義のSQL文が実行されるため、速度は速くなりません。

そこで、実体を持たせることで速度の問題を解決することができるビューがあります。
それがマテリアライズド・ビューです。

構文

MATERIALIZED VIEW <VIEW名> AS SELECT文

構文はVIEWの作成の構文とほとんど変わりません。
「MATERIALIZED」というキーワードを追加したものになります。

サンプル

-- ビューで定義した内容と同じものをマテリアライズド・ビューで定義する
CREATE MATERIALIZED VIEW mv_items AS
SELECT * FROM items
WHERE price >= 1000;
-- 確認
SELECT * FROM mv_items ORDER BY id;

結果

id item_name price
8 椅子 2000
9 地球儀 5000

先ほどと同じ結果になります。
ここで、レコードをアップデートしてみます。

-- 価格を2倍に設定する
UPDATE items SET price = price * 2;
-- 確認
SELECT * FROM mv_items ORDER BY id;

結果

id item_name price
8 椅子 2000
9 地球儀 5000

itemsのpriceを更新しましたが、マテリアライズド・ビューを参照した結果は、更新前のものになっています。
PostgreSQLの場合、実テーブルに更新を行っても、マテリアライズド・ビューにはリアルタイムでは反映されません。
反映させるためには、リフレッシュと呼ばれる操作が必要です。

-- mv_items をリフレッシュ
REFRESH MATERIALIZED VIEW mv_items;
-- 再度中身の確認
SELECT * FROM mv_items ORDER BY id;

結果

id item_name price
7 コップ 1400
8 椅子 4000
9 地球儀 10000

更新結果が反映されています。

この例はPostgreSQLの場合です。
PostgreSQLの場合、バージョン9.3以降であればマテリアライズド・ビューがサポートされています。
他、DBMS製品によってサポートの有無が異なります。
また、製品によってはリフレッシュのタイミングを自動で行ってくれるものもあります。
詳しくはそれぞれのDBMS製品のマニュアルで確認してください。

ストアドプログラム

ストアドプログラムは、データベース内で動作するプログラムのことです。
一般にストアドファンクション(ファンクション)とストアドプロシージャ(プロシージャ)に分かれます。

ストアドファンクションは一般にはSQL(SELECT文など)内で使用できるものです。
ストアドプロシージャはSQL内では使用できず、単体で呼び出しす処理になります。
他にも、一般にファンクションではトランザクション制御ができず、プロシージャはトランザクション制御ができるなどの違いがあったりしますが、細かくはDBMS製品によっても異なります。
以下、ファンクションとプロシージャについて解説します。

ファンクション

ファンクションは関数のことです。
各DBMS製品で、SQL内で使用できる関数が用意されていますが、関数を自分で作成することもできます。

ファンクションはほとんどのDBMS製品でサポートされていますが、文法は製品によって大きく異なります。
ここではPostgreSQLでのファンクションのサンプルを紹介します。
他の製品の場合はそれぞれのマニュアル等を参照して確認してください。

構文

CREATE OR REPLACE FUNCTION 
 <ファンクション名>(<引数リスト>) RETURNS <戻り値のデータ型>
 LANGUAGE plpgsql
AS $function$
DECLARE
    <変数宣言>
BEGIN
    <処理>
END;
$function$

サンプル

-- 税抜きの金額を受け取って税込みの金額を返すファンクション
CREATE OR REPLACE FUNCTION 
 tax_include(price integer) RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
    RETURN trunc(price * 1.1, 0);
END;
$function$

サンプルは非常にシンプルなファンクションですが、処理の部分では、IF文を使用して条件分岐したり、LOOP文を使って繰り返し処理を行うこともできます。

ファンクションの確認

-- 確認
SELECT tax_include(100) price;
-- 確認
SELECT 
id, item_name, price, tax_include(price) 税込金額
FROM items;

プロシージャ

プロシージャはファンクションに似ていますが、戻りがない処理をまとめる場合に使用されるのがプロシージャです。
ファンクションはSQL文の中で使用しますが、プロシージャはSQL(SELECT文など)からは呼び出しません。
プロシージャ単体で実行します。

プロシージャもファンクション同様、DBMS製品によって定義の仕方が大きく異なります。
ここではPostgreSQLでの場合を紹介しますが、PostgreSQLの場合バージョン11から使用できます。

CREATE OR REPLACE PROCEDURE <プロシージャ名>(<引数リスト>)
LANGUAGE plpgsql
AS $$
DECLARE
    <変数宣言>
BEGIN
    <処理>
END;
$$;

ここでは、実行したらitemsのデータのバックアップを作成するプロシージャを考えます。
そのためにまずバックアップ用のテーブルを作成しておきます。
準備

CREATE TABLE items_bk(
    id serial primary key
    , item_id int
    , item_name varchar(200)
    , price real
    , update_dt timestamp
);

プロシージャの作成

CREATE OR REPLACE PROCEDURE create_items_bk()
LANGUAGE plpgsql
AS $$
DECLARE
    item RECORD;  -- 商品レコード用変数
BEGIN
    DELETE FROM items_bk;  -- バックアップ用レコードの削除
    -- INSERT SELECTで一発で作成できるが、サンプルのためあえてループ処理を行う
    FOR item IN SELECT * FROM items ORDER BY id LOOP
        -- バックアップ用テーブルにインサート
        INSERT INTO items_bk(item_id, item_name, price, update_dt)
            VALUES(item.id, item.item_name, item.price, now());
    END LOOP;
END;
$$;

-- プロシージャの実行
CALL create_items_bk();
-- バックアップの確認
SELECT * FROM items_bk;

結果

id item_id item_name price update_dt
1 1 リンゴ 240 2020-03-07 18:17:07.847694
2 2 みかん 300 2020-03-07 18:17:07.847694
3 3 バナナ 600 2020-03-07 18:17:07.847694
4 4 ボールペン 600 2020-03-07 18:17:07.847694
5 5 ティッシュ 500 2020-03-07 18:17:07.847694
6 6 電池 800 2020-03-07 18:17:07.847694
7 7 コップ 1400 2020-03-07 18:17:07.847694
8 8 椅子 4000 2020-03-07 18:17:07.847694
9 9 地球儀 10000 2020-03-07 18:17:07.847694

バックアップが作成されていることが分かります。

パッケージ

パッケージはOracleのみの機能になります。
複数のファンクションやプロシージャをパッケージという単位で一つにまとめることができる機能です。

トリガー

トリガーは引き金を意味する英語です。
DBMSにおけるトリガーとは、特定のテーブルに対して更新（insert, update, delete）が引き金になって実行されるプログラム(SQL文)のことです。

トリガーもファンクションやプロシージャと同様、DBMSによって定義方法が異なります。
ここではPostgreSQLでの定義方法を紹介します。

PostgreSQLでトリガーを定義する場合、まずはトリガーで実行したい処理をファンクションとして定義し、そのファンクションをトリガーに設定する手順を踏みます。

トリガー用のファンクションの作成

CREATE OR REPLACE FUNCTION <ファンクション名>()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    <変数宣言>
BEGIN
    <処理>
    RETURN NULL;
END;
$$;

トリガーの定義

CREATE TRIGGER <トリガー名> (BEFORE|AFTER) (INSERT|UPDATE|DELETE) ON <テーブル名>
FOR EACH ROW
EXECUTE PROCEDURE <ファンクション>();

BEFOREかAFTERによって、処理の実行がレコードが更新される前か更新された後かを指定します。

ここでは、itemsテーブルにUPDATEの処理が実行された場合に、更新前のデータをitems_bkテーブルにINSERTするようなトリガーを考えます。

-- ファンクションの定義
CREATE OR REPLACE FUNCTION items_bk_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
    -- トリガーのファンクションでは、OLD, またはNEWのキーワードが使用できる
    -- トリガーの実行タイミングをBEFOREにしたときはOLD, AFTERにしたときはNEWが使用できる
    INSERT INTO item_bk(item_id, item_name, price, update_dt) 
        VALUES (OLD.item_id, OLD.item_name, OLD.price, now());
    RETURN NULL;
END;
$$;

-- トリガーの定義
-- itemsテーブルにUPDATEが実行されたとき、直前に処理が走る
CREATE TRIGGER items_bk_trigger BEFORE UPDATE ON item
FOR EACH ROW
EXECUTE PROCEDURE  items_bk_update();

-- 更新
UPDATE items SET price = price + 100;
-- 確認
SELECT * FROM items_bk;

シーケンス

シーケンスは連番を扱うためのオブジェクトです。
テーブルを作成する際、idのカラムを作成してPRIMARY KEYに設定することがよくあります。
idには意味を持たない連番で値を設定することが多く、その際によく使用されるのがシーケンスのオブジェクトです。

-- 単純なシーケンス作成の構文
CREATE SEQENCE <シーケンス名>;

シーケンスは様々なオプションを指定することができます。
初期値や最大値、増分の値、最大値に達した後にループするかどうか、等の指定ができます。
シーケンスは大抵のDBMSで実装されていますが、オプションの指定の仕方や、どんなオプションが指定できるかは製品に依存するので、詳細はマニュアルを参照してください。

シーケンスの使い方もDBMSによって異なります。
ここではPostgreSQLでのシーケンスの扱い方のサンプルを紹介します。

CREATE SEQUENCE seq_sample;
-- 連番取得
SELECT nextval('seq_sample');
-- 次の連番取得
SELECT nextval('seq_sample');
-- 次の連番取得
SELECT nextval('seq_sample');

ちなみに、PostgreSQLでは、テーブルを作成する際に、カラムのデータ型でserial型を指定することができます。
serial型にすると、INSERTするときにそのカラムに自動的に連番がセットされますが、これはテーブルを作成した際に、自動的にシーケンスも同時に作成され、その連番がセットされる仕組みになっています。

MySQLの場合、シーケンスのオブジェクトはサポートされていません。
ただし、カラムのオプションで AUTO INCREMENTを指定することで、自動的に連番をセットすることが可能です。

シノニム

シノニムとは呼び方が違うが同じ意味を持つ言葉のことです。
テーブルに対してシノニムを定義することで、別の名前でも参照することができるようになります。

シノニムはPostgreSQL, MySQL にはない機能です。
ここでは基本的にPostgreSQLの環境を前提としていますが、ここのサンプルはPostgreSQLでは使用できないので注意していください。
Oracle, SQL Server, DB2 で使用可能です。

-- シノニムの定義
CREATE SYNONYM <別名> FOR <対象のオブジェクト>;
-- 例
-- itemsテーブルに商品という別名を付ける
CREATE SYNONYM 商品 FOR items;

-- どちらで実行しても同じ結果になる
SELECT * FROM items;
SELECT * FROM 商品;

データベースリンク

データベースリンクはその名の通り、異なるサーバーにインストールされたDBに対するリンクです。
通常、DBにログインしたときには、ログインしているDBしか操作できません。
環境の異なるDBサーバーを使用する際には、データベースリンクを使用します。

以下はOracleのデータベースリンクの例です。

CREATE  DATABASE LINK <データベースリンク名>
  CONNECT TO <ユーザ名> IDENTIFIED BY <パスワード>
  USING '<DB接続名>'

-- DBリンクの使用例
-- テーブル名の後に@接続名で、リンク先のテーブルのデータが取得できる
SELECT * FROM tableA
UNION
SELECT * FROM tableB@DB接続名;