ストアドプロシージャとは？利点や書き方を解説

2021.09.03 株式会社システムインテグレータ

データベースと言えばデータを格納する箱というイメージですが、.NETやJavaなどのようにプログラムを格納することもできるのをご存知でしょうか？今回はデータベース内で管理し実行できる「ストアドプロシージャ」機能の作り方をサンプルを交えてご紹介します。

ストアドプロシージャとは

ストアドプロシージャとは、データベースに対する複数の命令をまとめて、RDBMS に保存する「ストアドプログラム」という機能の一種です。ストアドプログラムにはもうひとつ「ストアドファンクション」があります。両者の違いは、ストアドプロシージャが実行結果を返すのみで戻り値を返さないのに対してストアドファンクションは戻り値を返すことです。

ストアドプロシージャのメリット

ストアドプロシージャの主な利点は以下です。

・複数のSQLをまとめることで、一回の呼び出しで処理が実行できるようになり、サーバーの負荷の軽減、ネットワークの帯域の節約が期待できる

・繰り返し使用されるSQLをストアドプロシージャにしておくことで、SQLを実行するコードが冗長になることを防ぎ、アプリケーションの開発を効率化することができる

・すでに構文解析が完了した状態で保存するため、アプリケーションから実行する場合よりも高速にSQLを実行することができる

・アプリケーションに影響を与えずにSQLを変更することができる

・アプリケーションからデータベースへのアクセスを制限する（アプリケーションに直接データを操作させるのではなく、ストアドプロシージャを呼び出しさせる）ことによってデータベースのセキュリティが強化できる

ストアドプロシージャのデメリット

一方で以下のようなデメリットもあります。

・ストアドプロシージャの記述に使われる言語は、それぞれのデータベース製品によって異なっているため、使用するデータベース製品が異なるシステム間ではプログラムの再利用がしにくい

・アプリケーションの変更によってストアドプロシージャの変更も必要になり、開発工数が増大する場合がある

・ストアドプロシージャの数が増えすぎると、データベースサーバーに負荷がかかる

ストアドプロシージャの使いどころ

では、上のメリット・デメリットをふまえたうえでどんなときにストアドプロシージャを使うと良いのでしょうか。通常のSQLではパフォーマンスが悪い場合、ストアドプロシージャを使うことでレスポンスの向上が期待できます。
また個人情報など情報漏えい被害があった際のインパクトが大きいデータを保持している場合、SQLインジェクション対策の一つとしてストアドプロシージャが有効です。さらに複数のアプリケーションでデータベースに対して同じ処理を実行する場合は、開発の効率化のためストアドプロシージャで共通化することを検討してもよいでしょう。

ストアドプロシージャの言語

以下では、OracleのPL/SQLを例に、ストアドプロシージャの記述方法を簡単に解説いたします。
以下はストアドプロシージャのサンプルプログラムです。

CREATE OR REPLACE PROCEDURE UPDATE_SALARY(P_GROUP_ID NUMBER, P_ POSITION_ID NUMBER, SALARY_INCREASE NUMBER)
/******************************** 宣言部 ********************************/
IS
--カーソルの宣言
CURSOL EMPROYEES_CUR IS SELECT EMPROYEE_NUMBER FROM EMPROYEES_TABLE WHERE GROUP_ID = P_GROUP_ID AND POSITION_ID = P_ POSITION_ID;
--カーソル変数の宣言
EMPLOYEES_REC EMPLOYEES_CUR%ROWTYPE;
/******************************** 処理部 ********************************/
BEGIN
--対象レコードがなくなるまで繰り返す
FOR EMPLOYEES_REC IN EMPLOYEES_CUR LOOP
UPDATE SALARY_TABLE SET SALARY + SALARY_INCREASE WHERE EMPROYEE_NUMBER = EMPLOYEES_REC.EMPROYEE_NUMBER;
END LOOP;
CLOSE EMPROYEES_CUR;
COMMIT;
/****************************** 例外処理部 ******************************/
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line('SQLCODE = ' || SQLCODE);
dbms_output.put_line('SQLERRM = ' || SQLERRM);
END; /

プログラムは大きく定義部・宣言部・処理部・例外処理部に分けることができます。以下ではそれぞれのブロックの説明と、サンプルプログラムの記述内容を解説します。

定義部

冒頭の定義部には、ストアドプロシージャの作成コマンド CREATE OR REPLACE PROCEDURE以降にプログラムの名前と引数の定義を記述します。サンプルではプログラム名はUPDATE_SALARYです。また引数はP_GROUP_ID、P_ POSITION_ID、SALARY_INCREASEの3つで、すべてNUMBER形式です。

宣言部

IS以降の宣言部ではプログラム内で利用する変数・定数・カーソル（複数行の検索結果を一件ずつ処理したい場合に利用する）を記述します。サンプルではカーソルEMPROYEES_CURを定義し、さらにSELECT文の結果を格納しています。またカーソル変数を定義しています。

処理部

BEGIN以降の処理部では、ストアドプロシージャ内で実行したいSQLを記述します。サンプルではカーソル内（SELECT結果）のEMPLOYEES_REC.EMPROYEE_NUMBERとEMPROYEE_NUMBERが一致するSALARY_TABLEのレコードに対して、SALARYにSALARY_INCREASEを足しています。これはカーソルEMPROYEES_CURの対象レコードがなくなるまで繰り返します。

例外処理部

EXCEPTION以降の例外処理部にはストアドプロシージャの実行部でエラーが発生した場合の処理を記載します。例外処理部の記述は必須ではありませんが、もし処理部でIINSERT、UPDATEといった処理を行っている場合は、トランザクションのACID性を保つために例外発生時にはROLLBACKを行うとよいでしょう。サンプルではエラーが発生した場合ロールバックを行い、エラーメッセージを画面に表示しています。

作成したストアドプロシージャは、Oracleの標準ツールSQL*PlusのEXECUTEコマンドで実行できます。

上記のプログラムを実行するSQLは以下です。
EXECUTE UPDATE_SALARY (11, 30, 5000);

また、PL/SQLプログラムからストアドプロシージャの呼び出しをすることも可能です。
その場合は以下のように記述します。

DECLARE
X NUMBER
BEGIN
UPDATE_SALARY (11, 30, 5000);
dbms_output.put_line(X);
END

上記ではDECLAREの後に結果の戻りを受け取る変数を宣言し、
BEGINの後で引数を渡してUPDATE_SALARYを実行しています。
その後、実行結果を画面に表示しています。

データーベースサーバーに現在登録されているストアドプロシージャの一覧を取得するには、以下SQLを実行します。

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'PUROCEDURE';

他のユーザーのストアドプロシージャの一覧を取得したい場合のSQLは以下です。

SELECT * FROM DBA_OBJECTS WHERE OWNER = 'ユーザー名' AND OBJECT_TYPE = 'オブジェクトタイプ名';

また、ストアドプロシージャのソースを取得したい場合は、以下SQLを実行します。

SELECT TEXT FROM USER_SOURCE WHERE NAME = 'ストアドプロシージャ名' ORDER BY LINE;

こちらも、他のユーザーのストアドプロシージャのソースを取得する場合は以下です。
SELECT TEXT FROM DBA_OBJECTS WHERE OWNER = 'ユーザー名' AND NAME = 'ストアドプロシージャ名' ORDER BY LINE;