CREATE PROCEDURE
CREATE PROCEDURE ステートメントにより、新規のストアド プロシージャを作成します。ストアド プロシージャは、あらかじめ定義されデータベース辞書に保存されている SQL ステートメントです。
構文
CREATE PROCEDURE プロシージャ名
([パラメーター[, パラメーター]...])
[RETURNS (結果[, 結果]...)] 備考を参照 [WITH DEFAULT HANDLER | WITH EXECUTE AS MASTER' | WITH DEFAULT HANDLER, EXECUTE AS MASTER' | WITH EXECUTE AS MASTER', DEFAULT HANDLER]
as またはセミコロン
プロシージャ ステートメント
パラメーター ::= パラメーター タイプ名 データ型 [DEFAULT プロシージャ式 | = プロシージャ式] |
SQLSTATE
パラメーター タイプ名 ::= パラメーター名
| パラメーター タイプ パラメーター名
| パラメーター名 パラメーター タイプ
パラメーター タイプ ::= IN | OUT | INOUT | IN_OUT
プロシージャ式 ::= 通常の式と同様。ただし、IF 式と ODBC 形式のスカラー関数は使用できない
プロシージャ ステートメント ::= [ラベル名:] BEGIN [ATOMIC] [プロシージャ ステートメント[; プロシージャ ステートメント]...] END [ラベル名] | CALL プロシージャ名 (プロシージャ式[, プロシージャ式]...) | DECLARE カーソル名 CURSOR FOR 選択ステートメント [FOR UPDATE | FOR READ ONLY] | DELETE WHERE CURRENT OF カーソル名
| 削除ステートメント
|
FETCH [
フェッチ方向 [
FROM]]
カーソル名 [INTO 変数名[, 変数名]]| IF プロシージャ検索条件 THEN プロシージャ ステートメント[; プロシージャ ステートメント]...[ ELSE プロシージャ ステートメント[; プロシージャ ステートメント]...] END IF | IF プロシージャ検索条件 プロシージャ ステートメント [ELSE プロシージャ ステートメント] | 挿入ステートメント
| [ラベル名:] LOOP プロシージャ ステートメント[; プロシージャ ステートメント]... END LOOP [ラベル名] | PRINT プロシージャ式[, '文字列'] -- Windows プラットフォームにのみ適用される | RETURN [プロシージャ式]
| トランザクション ステートメント
| into 付き選択ステートメント
| 選択ステートメント
| SET 変数名 = プロシージャ式
| 更新ステートメント
| UPDATE SET 列名 = プロシージャ式[, 列名 = プロシージャ式]... WHERE CURRENT OF カーソル名
| [ラベル名:] WHILE プロシージャ検索条件 DO [プロシージャ ステートメント[; プロシージャ ステートメント]]... END WHILE [ラベル名] | [
ラベル名:]
WHILE プロシージャ検索条件 プロシージャ ステートメント | テーブル変更ステートメント
| インデックス作成ステートメント
| テーブル作成ステートメント
| ビュー作成ステートメント
| インデックス削除ステートメント
| テーブル削除ステートメント
| ビュー削除ステートメント
| 権限付与ステートメント
| 権限取消ステートメント
| 設定ステートメント
トランザクション ステートメント ::= コミット ステートメント
| ロールバック ステートメント
| リリース ステートメント
権限付与ステートメント ::=
GRANT を参照
プロシージャ検索条件 ::= 検索条件と同様。ただし、サブクエリを含む式は使用できない
フェッチ方向 ::= NEXT
sqlstate値 ::= '文字列'
備考
ストアド プロシージャを実行するには、
CALL または
EXECUTE ステートメントを使用します。
なお、プロシージャでは変数名とパラメーター名はコロン（:）で始まる必要があります。これは、変数やパラメーターを定義するときと使用するときの両方に当てはまります。
ストアド プロシージャが結果セットまたはスカラー値を返す場合は、RETURNS 句が必要です。
RETURNS 句が存在する場合は、エラーが発生したときにプロシージャが引き続き実行されます。デフォルトの動作（この句が存在しない場合）では、SQLSTATE がステートメントによって発生したエラー状態になり、プロシージャが中止されます。
IF ステートメントの最初（または最後）に StmtLabel を使用することは、ANSI SQL 3 の拡張機能です。
PRINT ステートメントは Windows ベースのプラットフォームにのみ適用されます。ほかのオペレーティング システムのプラットフォームでは無視されます。
SQL Editor において、変数パラメーターを使ってストアド プロシージャをテストする唯一の方法は、そのストアド プロシージャを別のストアド プロシージャから呼び出すことです。この手法は、pdate の例で示されています（CREATE PROCEDURE pdate();）。
変数は、ストアド プロシージャ内でのみ SELECT 項目として使用することができます。この手法は、varsub1 の例で示されています（CREATE PROCEDURE varsub1 ();）。
ストアド プロシージャ内で CREATE DATABASE または DROP DATABASE ステートメントを使用することはできません。
信頼されるストアド プロシージャと信頼されないストアド プロシージャ
信頼されるストアド プロシージャには WITH EXECUTE AS 'MASTER' 句を含めます。
信頼されるオブジェクトと信頼されないオブジェクトを参照してください。
メモリ キャッシング
デフォルトで、データベース エンジンはメモリ キャッシュを作成し、SQL セッションの継続期間中、複数のストアド プロシージャを格納します。ストアド プロシージャが実行されると、そのコンパイルされたものがメモリ キャッシュに保持されます。一般的に、キャッシングによって、キャッシュされたプロシージャの次回からの呼び出しのパフォーマンスが向上します。ストアド プロシージャが初めて実行されるときにはキャッシュによってパフォーマンスは向上しません。これは、プロシージャがまだメモリに読み込まれていないためです。
2 つの SET ステートメントがメモリ キャッシュに適用されます。
キャッシュの設定やアプリケーションが実行する SQL によっては、過度のメモリ スワッピングやスラッシングが発生するので注意してください。スラッシングはパフォーマンスの低下を招きます。
キャッシングの除外
ストアド プロシージャは、以下の条件に当てはまる場合、キャッシュ設定にかかわらず、キャッシュされません。
•ストアド プロシージャが、ローカルまたはグローバル テンポラリ テーブルを参照する。ローカル テンポラリ テーブルはポンド記号（#）で始まる名前を持ちます。グローバル テンポラリ テーブルは 2 つのポンド記号（##）で始まる名前を持ちます。
CREATE （テンポラリ） TABLE を参照してください。
•ストアド プロシージャにデータ定義言語（DDL）ステートメントが含まれている。
データ定義ステートメントを参照してください。
•ストアド プロシージャに、文字列または文字列を返す式を実行するための EXEC[UTE] ステートメントが含まれている。たとえば、次のような例です。
EXEC ('SELECT Student_ID FROM ' + :myinputvar)
データ型の制限
次のデータ型は、パラメーターとして渡したり、ストアド プロシージャまたはトリガー内で変数として宣言したりすることはできません。
表 21 ストアド プロシージャおよびトリガーで禁止されるデータ型
BFLOAT4 | BFLOAT8 |
MONEY | NUMERICSA |
NUMERICSLB | NUMERICSLS |
NUMERICSTB | NUMERICSTS |
直接対応する ODBC データ型がない PSQL データ型を、プロシージャで使用されるように正しくマップする方法については、
例を参照してください。
制限
ストアド プロシージャを作成する際、次の制限に注意を払う必要があります。
属性 | 制限 |
トリガーまたはストアド プロシージャで使用可能な列数 | 300 |
ストアド プロシージャのパラメーター リスト内の引数の数 | 300 |
ストアド プロシージャのサイズ | 64 KB |
例
このセクションでは、CREATE PROCEDURE のいくつかの例を示します。
次の例では、ストアド プロシージャ Enrollstudent が作成され、Student ID と Class ID が指定されてレコードが Enrolls テーブルに挿入されます。
CREATE PROCEDURE Enrollstudent(in :Stud_id integer, in :Class_Id integer, IN :GPA REAL);
BEGIN
INSERT INTO Enrolls VALUES(:Stud_id, :Class_id, :GPA);
END;
次のステートメントを使用して、ストアド プロシージャを呼び出します。
CALL Enrollstudent(1023456781, 146, 3.2)
次のステートメントを使用して、新しく挿入したレコードを取得します。
SELECT * FROM Enrolls WHERE Student_id = 1023456781
CALL ステートメントは引数を渡してプロシージャを呼び出し、SELECT ステートメントは追加された行を表示します。
============
この例は、パラメーターにデフォルト値を割り当てる方法を示します。
CREATE PROCEDURE ReportTitle1 (:rpttitle1 VARCHAR(20) = 'Finance Department');
BEGIN
PRINT :rpttitle1;
END;
CALL ReportTitle1
CREATE PROCEDURE ReportTitle2 (:rpttitle2 VARCHAR(20) DEFAULT 'Finance Department');
BEGIN
PRINT :rpttitle2;
END;
CALL ReportTitle2
これらのプロシージャは、CALL でパラメーターが提供されなかった場合には、指定したデフォルト値（Finance Department）を使用します。
============
次のプロシージャでは、呼び出し側から渡された classId パラメーターを使用して Class テーブルを読み取り、講座の登録者数がまだ制限数に達していないことを確認します。
CREATE PROCEDURE Checkmax(in :classid integer);
BEGIN
DECLARE :numenrolled integer;
DECLARE :maxenrolled integer;
SELECT COUNT(*) INTO :numenrolled FROM Enrolls WHERE class_ID = :classid;
SELECT Max_size INTO :maxenrolled FROM Class WHERE id = :classid;
IF (:numenrolled >= :maxenrolled) THEN
PRINT '登録は失敗しました。登録された生徒数が、この講座の制限数に達しました。';
ELSE
PRINT '登録可能です。登録された生徒数は、この講座の制限数にまだ達していません。';
END IF;
END;
CALL Checkmax(101)
COUNT(式) によって、述部にある式の非ヌル値がすべてカウントされるということを覚えておいてください。COUNT(*) ではヌル値を含むすべての値がカウントされます。
============
ストアド プロシージャを作成するときに OUT パラメーターを使用する例を以下に示します。このプロシージャを呼び出すと、WHERE 句を満たす生徒数が変数 :outval に返されます。
CREATE PROCEDURE PROCOUT (out :outval INTEGER)
AS BEGIN
SELECT COUNT(*) INTO :outval FROM Enrolls WHERE Class_Id = 101;
END;
============
ストアド プロシージャを作成するときに INOUT パラメーターを使用する例を以下に示します。このプロシージャの呼び出しでは、INPUT パラメーター :IOVAL が要求され、出力の値が変数 :IOVAL に返されます。プロシージャでは、入力と IF 条件に基づいてこの変数の値が設定されます。
CREATE PROCEDURE PROCIODATE (inOUT :IOVAL DATE)
AS BEGIN
IF :IOVAL = '1982-03-03' THEN
SET :IOVAL = '05.05.82';
ELSE
SET :IOVAL = '03.03.82';
END IF;
END;
上記のプロシージャは、call prociodate('1982-03-03') のように、リテラル値を使って呼び出すことはできません。これには OUTPUT パラメーターが必要になります。最初に ODBC 呼び出しを使ってパラメーターをバインドする必要があります。そうでなければ、次に示すように、このプロシージャを呼び出す別のプロシージャを作成することで、プロシージャのテストを行えます。
CREATE PROCEDURE pdate();
BEGIN
DECLARE :a DATE;
CALL prociodate(:a);
PRINT :a;
END
CALL pdate
============
次の例は、プロシージャで RETURNS 句を使用する方法を示します。この例では、Class テーブルの中で、Start Date が、CALL ステートメントで渡される日付と等しいデータがすべて返されます。
CREATE PROCEDURE DATERETURNPROC(IN :PDATE DATE)
RETURNS(
DateProc_ID INTEGER,
DateProc_Name CHAR(7),
DateProc_Section CHAR(3),
DateProc_Max_Size USMALLINT,
DateProc_Start_Date DATE,
DateProc_Start_Time TIME,
DateProc_Finish_Time TIME,
DateProc_Building_Name CHAR(25),
DateProc_Room_Number UINTEGER,
DateProc_Faculty_ID UBIGINT
);
BEGIN
SELECT ID, Name, Section, Max_Size, Start_Date, Start_Time, Finish_Time, Building_Name, Room_Number, Faculty_ID FROM CLASS WHERE START_DATE = :PDATE;
END;
CALL DATERETURNPROC('1995-06-05')
RETURNS 句内のユーザー定義名は、この例で示されているように、選択リストに現れる列名と同じ名前にする必要がないことに注目してください。
============
次の例は、位置付け DELETE に適用される WHERE CURRENT OF 句の使い方を示します。
CREATE PROCEDURE MyProc(IN :CourseName CHAR(7)) AS
BEGIN
DECLARE c1 CURSOR FOR SELECT name FROM course WHERE name = :CourseName FOR UPDATE;
OPEN c1;
FETCH NEXT FROM c1 INTO :CourseName;
DELETE WHERE CURRENT OF c1;
CLOSE c1;
END;
CALL MyProc('HIS 305')
（DELETE の WHERE 句の中で SELECT を使用した場合、それは位置付け DELETE ではなく検索済み DELETE になるので注意してください。）
============
次の例は、変数（:i）を SELECT 項目として使用する方法を示します。例では、table1 はまだ存在しないものとします。person テーブルの、ID が 950000000 より大きいすべてのレコードが選択され、table1 の col2 に挿入されます。col1 には、WHILE ループで定義されるとおり、値 0、1、2、3 または 4 が格納されます。
CREATE TABLE table1 (col1 CHAR(10), col2 BIGINT);
CREATE PROCEDURE varsub1 ();
BEGIN
DECLARE :i INT;
SET :i = 0;
WHILE :i < 5 DO
INSERT INTO table1 (col1, col2) SELECT :i , A.ID FROM PERSON A WHERE A.ID > 950000000;
SET :i = :i + 1;
END WHILE;
END
CALL varsub1
SELECT * FROM table1
-- 110 行を返します
============
一連のステートメントがすべて成功するか失敗するように、ATOMIC を使用してステートメントを 1 つにまとめる例を以下に示します。ATOMIC は、ストアド プロシージャまたはトリガーの本体でのみ使用できます。
最初のプロシージャでは ATOMIC を指定せず、2 番目のプロシージャで指定します。
CREATE TABLE t1 (c1 INTEGER)
CREATE UNIQUE INDEX t1i1 ON t1 (c1)
CREATE PROCEDURE p1 ();
BEGIN
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);
END;
CREATE PROCEDURE p2 ();
BEGIN ATOMIC
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (2);
END;
CALL p1()
CALL p2()
SELECT * FROM t1
どちらのプロシージャも、重複のないインデックスに重複する値を挿入しようとするためにエラーが返されます。
プロシージャ p1 内の最初の INSERT ステートメントは成功しますが、2 番目のステートメントは失敗するので、t1 には 1 レコードのみが格納されるという結果になります。同様に、プロシージャ p2 内の最初の INSERT ステートメントは成功しますが、2 番目のステートメントは失敗します。ただし、プロシージャ p2 には ATOMIC があるため、エラーが発生すると、プロシージャ p2 内で行われた作業はすべてロール バックされます。
============
次の例では、ストアド プロシージャを使用して 2 つのテーブルを作成し、デフォルト値を設定した 1 行をそれぞれのテーブルに挿入します。次に、セキュリティを有効にしてユーザー user1 に権限を与えます。
CREATE PROCEDURE p1 ();
BEGIN
CREATE TABLE t1 (c1 INT DEFAULT 10, c2 INT DEFAULT 100);
CREATE TABLE t2 (c1 INT DEFAULT 1 , c2 INT DEFAULT 2);
INSERT INTO t1 DEFAULT VALUES;
INSERT INTO t2 DEFAULT VALUES;
SET SECURITY = larry;
GRANT LOGIN TO user1 u1pword;
GRANT ALL ON * TO user1;
END;
CALL p1
SELECT * FROM t1
-- 10、100 を返します
SELECT * FROM t2
-- 1、2 を返します
メモ： ストアド プロシージャで GRANT LOGIN ステートメントを使用する場合は、ユーザー名とパスワードをコロンではなく空白文字で区切る必要があります。コロン文字は、ストアド プロシージャ内でローカル変数の識別に使用されます。
============
次の例では、ストアド プロシージャを使用してユーザー user1 の権限を取り消し、前の例で作成した 2 つのテーブルを削除してデータベース セキュリティを無効にします。
CREATE PROCEDURE p3 ();
BEGIN
REVOKE ALL ON t1 FROM user1;
REVOKE ALL ON t2 FROM user1;
DROP TABLE t1;
DROP TABLE t2;
SET SECURITY = NULL;
END;
CALL p3
SELECT * FROM t1 -- テーブルが見つからないというエラーを返します
SELECT * FROM t2 -- テーブルが見つからないというエラーを返します
============
次の例は、カーソル内をループする方法を示します。
CREATE TABLE atable (c1 INT, c2 INT)
INSERT INTO atable VALUES (1,1)
INSERT INTO atable VALUES (1,2)
INSERT INTO atable VALUES (2,2)
INSERT INTO atable VALUES (2,3)
INSERT INTO atable VALUES (3,3)
INSERT INTO atable VALUES (3,4)
CREATE PROCEDURE pp ();
BEGIN
DECLARE :i INTEGER;
DECLARE c1Bulk CURSOR FOR SELECT c1 FROM atable ORDER BY c1 FOR UPDATE;
OPEN c1Bulk;
BulkLinesLoop:
LOOP
FETCH NEXT FROM c1Bulk INTO :i;
IF SQLSTATE = '02000' THEN
LEAVE BulkLinesLoop;
END IF;
UPDATE SET c1 = 10 WHERE CURRENT OF c1Bulk;
END LOOP;
CLOSE c1Bulk;
END
CALL pp
--正常終了
SELECT * FROM atable
--6 行を返します
============
次の例では、InParam という名前の信頼されるストアド プロシージャを作成します。その後、Master ユーザーが User1 に対し、InParam の EXECUTE および ALTER 権限を与えます。この例は、テーブル t99 が存在し、テーブルに INTEGER 型の 2 つの列があることを前提としています。
CREATE PROCEDURE InParam(IN :inparam1 INTEGER, IN :inparam2 INTEGER) WITH DEFAULT HANDLER, EXECUTE AS 'Master' AS
BEGIN
INSERT INTO t99 VALUES(:inparam1 , :inparam2);
END;
GRANT ALL ON PROCEDURE InParam TO User1
Master および User1 は、このプロシージャを呼び出せるようになりました（たとえば、CALL InParam(2,4)）。
============
次の例は、直接対応する ODBC データ型がない PSQL データ型を、プロシージャで使用されるように正しくマップする方法を示します。データ型 NUMERICSA および NUMERICSTS は、直接対応がないデータ型なので、代わりに NUMERIC にマップされます。
CREATE TABLE test1 (id identity, amount1 numeric(5,2), amount2 numericsa(5,2), amount3 numericsts(5,2))
CREATE PROCEDURE ptest2 (IN :numval1 numeric(5,2), IN :numval2 numeric(5,2), IN :numval3 numeric(5,2))
AS
BEGIN
Insert into test1 values(0, :numval1, :numval2, :numval3);
END;
CALL ptest2(100.10, 200.20, 300.30)
SELECT * FROM test1
プロシージャは、amount 値がすべて NUMERIC としてプロシージャに渡されるにもかかわらず、これらを CREATE TABLE ステートメントで定義された PSQL データ型に従って正しく書式指定します。データ型のマップについては、
PSQL トランザクショナルおよびリレーショナル データ型を参照してください。
ストアド プロシージャの使用
一例として、CALL foo(a, b, c) は、パラメーター a、b、および c を持つストアド プロシージャ foo を実行します。パラメーターはいずれも動的パラメーター（'?'）にできます。これは、OUTPUT パラメーターおよび INOUT パラメーターの値を取り出す場合は必須です。たとえば、{CALL foo(?, ?, 'TX')} のように指定します。ソース コードでは、中かっこは省略可能です。
以下は、現バージョンの PSQL におけるストアド プロシージャのしくみです。
•トリガー（CREATE TRIGGER、DROP TRIGGER）が、ストアド プロシージャの形式としてサポートされます。このサポートには、テーブル、プロシージャ、およびデータベースに対するトリガーの依存性の監視が含まれます。CREATE PROCEDURE および CREATE TRIGGER は、ストアド プロシージャまたはトリガーの本体では使用できません。
•CONTAINS、NOT CONTAINS、BEGINS WITH はサポートされていません。
•LOOP：事後条件ループはサポートされていません（REPEAT...UNTIL）。
•ELSEIF：条件形式には IF ... THEN ... ELSE が使用されます。ELSEIF はサポートされていません。
全般的なストアド プロシージャ エンジンの制約
ストアド プロシージャを使用する前に、次に挙げる制約を確認してください。
•修飾子は、CREATE PROCEDURE でも CREATE TRIGGER でもサポートされていません。
•ストアド プロシージャ変数名の最大長は半角 128 文字です。
•ストアド プロシージャ名の最大長については、『
Advanced Operations Guide』の表
1 識別子の種類別の制限を参照してください。
•CREATE PROCEDURE または CREATE TRIGGER のとき、構文の検証は部分的にのみ行われます。列名は実行時まで確認されません。
•現在は、式が使用されているすべての場所でサブクエリを使用できるわけではありません。たとえば、set :arg = SELECT MIN(sal) FROM emp を使用する UPDATE ステートメントはサポートされません。ただし、このサブクエリを SELECT min(sal) INTO :arg FROM emp のように書き直すことができます。
•デフォルトのエラー ハンドラーのみがサポートされています。
SQL 変数とパラメーターの制約
•変数名はコロン（:）で始まる必要があります。これによって、ストアド プロシージャのパーサが変数と列名を区別できるようになります。
•変数名は、大文字と小文字が区別されません。
•セッション変数はサポートされていません。変数はプロシージャに対してローカルです。
カーソルの制約
•位置付け UPDATE はテーブル名を受け入れません。
•グローバル カーソルはサポートされていません。
Long データを使用する際の制約
•Long データを引数として埋め込みプロシージャ（別のプロシージャを呼び出すプロシージャ）に渡すと、データは切り捨てられて 65500 バイトになります。
•プロシージャとの間でやり取りされる Long データの引数は、合計 2 MB に制限されています
Long データは、内部的にはデータ長の制限なしにカーソル間でコピーできます。Long データ列をステートメントからフェッチして別のステートメントに挿入する場合、制約はありません。ただし、1 つの Long データ変数に対して複数の宛先が要求される場合は、最初の宛先テーブルのみが複数の PutData 呼び出しを受け取ります。残りの列は切り捨てられ、最初の 65500 バイトになります。これは ODBC GetData のメカニズムの制約です。
関連項目