ストアドプロシージャの作成

SQLコンソールは、視覚的な方法でストアドプロシージャを作成することをサポートしています。この記事では、SQLウィンドウを使用してストアドプロシージャを作成する方法について説明します。

背景

ストアドプロシージャは、1つまたは複数のステートメントの集合であり、データベースに対して一連の複雑な操作を行う際に、そのような操作をデータベース内でコードブロックとしてカプセル化し、再利用することができます。これにより、データベース開発者の作業負荷を軽減できます。

ストアドプロシージャは、特定の機能を実現するためのSQLステートメントの集合であり、コンパイルされてデータベースに保存されます。ユーザーはストアドプロシージャ名と必要なパラメータを指定することで呼び出し実行でき、ストアドプロシージャを利用することでSQLステートメントの実行を高速化できます。

新規ストアドプロシージャとは、ユーザーが作成し、特定の機能を実現できるストアドプロシージャのことです。ストアドプロシージャにはパラメータと戻り値を含めることができます。

ストアドプロシージャと関数の違い

• 関数の戻り値は具体的な結果値を示しますが、ストアドプロシージャの戻り値は実行が成功したかどうかのみを示します。

• 関数は SELECT ステートメントで直接呼び出すことができますが、ストアドプロシージャの呼び出しには CALL ステートメントを使用する必要があります。

ストアドプロシージャの役割

• アプリケーションの汎用性と移植性の向上：新規ストアドプロシージャを作成後、プログラム内で何度も呼び出すことができます。ストアドプロシージャのSQLステートメントを書き直す必要はなく、ストアドプロシージャを変更してもプログラムのソースコードに影響を与えません。これにより、プログラムの移植性が向上します。

• SQL実行速度の向上：ストアドプロシージャはコンパイル済みであるため、ある操作に大量のSQLコードが含まれる場合や、複数回に分けて実行される場合、ストアドプロシージャを使用する方が、単一のSQLステートメントを直接使用するよりも実行速度が速くなります。

• サーバー負荷の軽減：データベースオブジェクトを操作する際、単一のステートメントを呼び出す方法を使用すると、ネットワーク上で大量のSQLステートメントを転送する必要があります。ストアドプロシージャを使用すると、プロシージャの呼び出しコマンドを直接送信できるため、ネットワーク負荷を軽減できます。

上図のように、ストアドプロシージャの作成には以下の5つのステップが含まれます：

1. ストアドプロシージャ名を入力します。

2. パラメータを追加します。

3. 新規ストアドプロシージャ情報を確定します。

4. ストアドプロシージャ情報を編集します。

5. 新規ストアドプロシージャの作成を完了します。

本記事では、SQLウィンドウで部門予算ストアドプロシージャ（proc_total）を作成する例を挙げます。ストアドプロシージャproc_totalには、budget_rとbudget_sという2つのINT型の入力パラメータが含まれ、2つのパラメータの合計を計算します。

前提条件

SQLコンソールにログインするための、現在のテナントのデータベースアカウントとパスワードを保有していること。

操作手順

ステップ１：ストアドプロシージャ名の指定

1. SQLコンソールにログインしSQLウィンドウに入ったら、左側のデータベースリストで ストアドプロシージャ タブをクリックしてストアドプロシージャのリストを表示します。ストアドプロシージャのリストの右上隅で、+ をクリックしてストアドプロシージャを作成し、必要なオブジェクトを作成します。

2. ストアドプロシージャの作成 に、ストアドプロシージャ名を入力します。

   

ステップ2：パラメータの追加

1. パラメータとは関数を呼び出す際に渡される情報です。パラメータ情報を指定する必要があります：

   • Oracleモード：パラメータの 名前、モード、タイプ、および デフォルト値 を指定する必要があります。

   • MySQLモード：パラメータの 名前、モード、タイプ、および 長さ を指定する必要があります。

2. 関数の追加には、以下の3つの操作機能が提供されています：

   機能	説明
   ポップアップガイド補助メニュー	ポップアップガイド補助メニューを通じて、パラメータの追加、削除、上下移動を行えます。
   表番号をクリック	表番号をクリックして行全体を選択すると、補助メニュー（削除、上/下移動）が表示されます。 表番号をクリックして選択すると、パラメータ行全体をドラッグして順序を調整できます。
   マウス右クリック操作	右クリックして行全体を選択し、行のコピー / 1行下への移動を行います。

   説明

   行をコピーした後、任意の行を選択し、ショートカットキーCommand + V / Ctrl + Vで貼り付けることができます。

3. パラメータ で指定する必要がある モード はパラメータタイプを指します。

   MySQLモードではパラメータモードの設定はサポートされていませんが、Oracleモードでは IN (入力)、OUT (出力)、INOUT (入出力) の3種類のパラメータモードをサポートしています：

   CREATE PROCEDURE proc_name ([[IN |OUT |INOUT ] parameter_name parameter_type...])
   

   • パラメータの説明

     パラメータ	タイプ
     IN	入力パラメータ。 ストアドプロシージャを呼び出す際に、パラメータの値をストアドプロシージャに渡して実行時に使用します。INタイプのパラメータは通常、渡すためだけに使用され、ストアドプロシージャ内での変更や返却は一般的ではありません。
     OUT	出力パラメータ。 ストアドプロシージャを呼び出す際、ストアドプロシージャは出力パラメータ自体の値を無視し、空値を渡します。実行終了後、出力パラメータにはストアドプロシージャが変更した値が代入されます。一般的に出力パラメータを使用してストアドプロシージャの実行結果データを取得します。OUTタイプのパラメータは、ストアドプロシージャ内での変更や値の返却が必要な場合に使用できます。
     INOUT	入出力パラメータ。 入力パラメータと出力パラメータの機能を兼ね備えています。

   • パラメータの設定

     プロパティ	必須	デフォルト	モード
     名前	必須	空	Oracle/MySQL
     モード	必須	IN	Oracle/MySQL
     タイプ	必須	VARCHAR	Oracle/MySQL
     長さ	必須	45	MySQL
     デフォルト値	任意	空	Oracle

ステップ3：新規ストアドプロシージャを確定する

OK をクリックし、SQL作成確認ページに進みます。

ステップ4：ストアドプロシージャを編集する

SQL作成確認ページで、ストアドプロシージャのステートメントを編集します。

ユーザーはSQL作成確認ページで作成するストアドプロシージャのステートメントを編集できます。構文は以下のとおりです：

CREATE PROCEDURE proc_name (
  [proc_parameter[,...]]) 
BEGIN
proc_body:
　　Valid SQL routine statement
END [end_label]

パラメータの説明：

ステップ5：ストアドプロシージャの作成を完了する

作成 をクリックしてストアドプロシージャの作成を完了すると、システムの組み込み関数を呼び出すように、CALL ステートメントを使用してユーザー定義のストアドプロシージャを呼び出すことができます。

構文：

CALL proc_name ([proc_parameter [,...]]);

例：

CALL proc_total (30000, 20000);

左側のデータベースリストで、テーブル名の右側にあるメニューアイコンをクリックし、表示される管理操作リスト（表示、編集、実行、ダウンロード、削除、および 更新など）を使用して、ターゲットオブジェクトを簡単に管理および操作できます。ストアドプロシージャの機能については、SQLコンソールの機能キー一覧をご参照ください。