Machiken’sAdvent Calendar 2024

SQLでできることについて

Posted at 2024-12-25

はじめに

こんにちは。今日はSQLでできることについてまとめます。
SQLでは、問合せへの応答のほかにいろいろなことが行えます。

本文

ビュー

ビュー（導出表）とは、仮想的な表です。SQL文で参照されるたびに問合せを行い、見せるためのビューを作ります。そのため問合せに時間がかかりますが、その時間を短縮するための体現ビューを利用する方法もあります。

ビューの目的

ビューは仮想的な表で、データベースに作成された実際の表（実表）とは異なり、SQL文から問合せがあるたびに、実表または別のビューに問合せを行い、その返答結果を基にビューを作成します。

ビューを作成する目的は2つです。

複雑なSQL文を何度も書く必要がない

複数の表を結合する処理や集約関数などで複雑なSQL文を書く必要がある場合には、ビューを作成しておくと便利です。何度も同じ処理を繰り返すときには、1度ビューを登録しておけば簡単なSELECT文で呼び出せるからです。

ビューは毎回演算を行うので、最新の情報を反映させることができ、データの整合性も取りやすくなります。正規化を行い、導出属性は毎回演算するようにしておけば、データの更新時異状を減らすことが可能です。

セキュリティを確保する

表の内容には顧客の個人情報など、公開を限定したい部分がある場合があります。こういった場合に、公開しても差し支えのない、必要な情報だけを取り出し、それ以外は隠しておくという目的でビューを使用することができます。
表とビューにアクセス制御をかけることによって、参照できるユーザを特定し、情報の漏洩を防ぎます。

更新可能なビュー

ビューは、実際の表と同じように表示させることが可能です。しかし、ビューを更新しようとすると、時と場合によって不具合が生じることがあります。特に、元の表で行が特定できない場合には更新時異状が起こってしまいます。それを避けるために、ビューの更新には制限があり、更新可能なビューは限られています。
更新可能なビューでは、次の機能は使ってはいけません。

GROUP BY句
HAVING句
計算列（演算で求められる結果）
集約関数（集計関数）
DISTINCT句

これらを使って演算を行うと複数の行が1行にまとめられてしまい、元の行を特定できないため、更新が不可能になります。また、ビューに行を挿入するときには、ビューに含まれていない列は全てNULLを許可しているか、または、デフォルト値の設定が可能である必要があります。これは、ビューからの挿入の場合は、ビューに含まれない列の値を設定することができないからです。
なお、結合演算（JOIN句）や和演算（UNION句）では、更新可能なビューを作成することは可能ですが、元の行が特定できない結合や演算を行うと作成できない場合もあるので、注意が必要です

体現ビュー

ビューは基本的に毎回、実表に問い合わせを行います。そのため、実際のデータベースには格納されません。しかし、頻繁にビューが参照される場合、その都度問い合わせを行なっていると処理速度が遅くなってしまいます。
そうした問題を回避するため、実表のようにデータベースにデータを格納したビューを作成することができます。このビューを体現ビューと言います。
体現ビューでは、ビューの問い合わせの前に、あらかじめSELECT文を実行してデータを格納しておきます。そのためビューへのアクセスは速くなりますが、データが更新される場合などにデータの不整合が起こる可能性があります。また、データベースにデータを格納するため、実表とは別に新たな格納領域が必要になるので、必要な記憶領域が増加します。
そのため、体現ビューを作成する場合には、どのビューを体現化し、どのビューを体現かせずにおくかをしっかり考えて決める必要があります。

カーソル

カーソルは、プログラム言語からSQL文を呼び出すときに使用します。実行結果を1行ずつ返すことで、プログラムでの1つずつの処理に対応できます。

カーソルの目的

カーソルの目的は、プログラムとデータベースを結びつけることです。SELECT文での問合せを実行すると、通常は複数行の結果が返ってきます。昔のプログラム言語では複数行を一度に処理することができなかったので、1行ずつ順に渡す方法としてカーソルが考えられました。

カーソルの使用例

カーソルは、プログラム言語で書かれたプログラムやストアドプロシージャなど、データベースとは別の順次実行するプログラムから実行されます。例えば、ストアドプロシージャでは次のように書かれます。

CREATE PROCEDURE sp_sample AS
    DECLARE @FirstName nvarchar(50)
    DECLARE @LastName nvarchar(50)
    -- カーソルの宣言
    DECLARE EmpCur CURSOR FOR
            SELECT FirstName, LastName
            FROM Employee
    -- カーソルを開く
    OPEN EmpCur
    -- Fetch
    FETCH NEXT FROM EmpCur INTO @FirstName, @LastName
    WHILE (@@fetch_status = 0)
    BEGIN
        PRINT @FirstName + ' ' + @LastName
        FETCH NEXT FROM EmpCur INTO @FirstName, @LastName
    END
    -- カーソルを閉じる
    CLOSE EmpCur
RETURN

プログラム内で変数を用意し、FETCH文で行を1行ずつ取得して、変数に値を格納します。その変数に対する処理を全ての行に関してループさせて行います。

カーソルやストアドプロシージャなどは、標準のSQL以外を使用することが多く、データベースの種類によって文法が異なります。

カーソルの問題点

カーソルはプログラムからSQLを呼び出すものです。そのため、カーソルの実装方法は、プログラム言語やストアドプロシージャの種類などによってかなり異なります。特に、カーソルから1行ずつ取り出す際の処理の順番などは、使用するDBMSによって異なります。そのため、実装により記述の仕方が変わることになり、移植やバージョンアップで不都合が起こりやすくなります。
また、カーソルは元々、昔ながらの手続き型言語（PL/IやCOBOLなど）でDBMSのデータを扱うために作られたものです。そのため、現在のプログラム言語では使う必要がなくなってきています。カーソルを極力使わずにプログラムを作成することも重要です。

ストアドプロシージャ

ストアドプロシージャは、SQLを用いた一連の処理をデータベースで行うプログラムです。ストアドプロシージャを用いることによって、ひとまとめの処理を実行できます。

ストアドプロシージャの利用目的

ストアドプロシージャは、条件分岐や順次処理など、SQLでの処理を実行するプログラム機能を持ちます。CREATE PROCEDURE文でストアドプロシージャを定義してそれを呼び出すことで、一連の処理を実行できます。
ストアドプロシージャを利用する目的の1つに、処理の高速化があります。
ストアドプロシージャは、作成されたときにプリコンパイルされるので、SQL文を1つずつ呼び出す場合に比べて処理が高速になります。
また、一連の処理があらかじめデータベースサーバに登録されており、クライアントはそれを呼び出すだけでいいので、通信量の削減が実現できます。

トリガー

トリガーは、あるイベントがデータベースで起きたときに実行されるストアドプロシージャです。トリガーとなるイベントは、テーブルに対する追加、削除、更新などです。
あるテーブルに変更があったときに別のテーブルに連動して変更を行いたい場合などに設定します。
トリガーは、１つずつ順番にしか処理できないので、トリガーの実行数が増えると処理が遅くなるという欠点がなります。そのため、トリガーを利用する場合は注意深く設計を行う必要があります。

まとめ

いかがでしたか？

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up