CREATE PROCEDURE 文

CREATE [ OR REPLACE | TEMPORARY ] PROCEDURE [ owner.]procedure-name 
( [ parameter, ... ] )
[ RESULT ( result-column, ... ) | NO RESULT SET ]
[ SQL SECURITY { INVOKER | DEFINER } ]
[ ON EXCEPTION RESUME ]
compound-statement | AT location-string | variable-name

parameter :
parameter-mode parameter-name data-type [ DEFAULT expression ]
| SQLCODE
| SQLSTATE

parameter-mode :
IN 
| OUT 
| INOUT

result-column : column-name data-type

外部プロシージャーまたはネイティブプロシージャーを呼び出す永続的なストアドプロシージャーの作成には、さまざまなプログラミング言語を使用できます。PROC は PROCEDURE の同義語として使用できます。

OR REPLACE 句 OR REPLACE を指定すると、新しいプロシージャーが作成されるか、同じ名前の既存のプロシージャーが置き換えられます。この句によって、プロシージャーの定義は変更されますが、既存のパーミッションは保持されます。使用中のプロシージャーを置き換えようとすると、エラーが返されます。

TEMPORARY 句 CREATE TEMPORARY PROCEDURE を指定すると、作成した接続でのみ参照できるストアドプロシージャーになり、接続を削除するとプロシージャーも自動的に削除されます。テンポラリストアドプロシージャーを明示的に削除することもできます。テンポラリストアドプロシージャーに対して ALTER、GRANT、または REVOKE は実行できません。また他の関数とは異なり、テンポラリストアドプロシージャーはカタログやトランザクションログに記録されていません。

テンポラリプロシージャーは、作成者 (現在のユーザー) または指定された所有者のパーミッションで実行されます。テンポラリプロシージャーに所有者を指定できるのは次の場合です。

テンポラリプロシージャーが永続的なストアドプロシージャー内に作成された場合
テンポラリプロシージャーと永続的なプロシージャーとで所有者が同じ場合

テンポラリプロシージャーの所有者を削除するには、テンポラリプロシージャーを先に削除する必要があります。

読み込み専用データベースに接続し、外部プロシージャーにすることができない場合、テンポラリストアドプロシージャーを作成または削除できます。

たとえば、次のテンポラリプロシージャーは CustRank というテーブルがあればそれを削除します。この例では、テーブル名が一意であり、プロシージャーの作成者がテーブルの所有者を指定しなくても参照できると想定しています。

CREATE TEMPORARY PROCEDURE drop_table( IN @TableName char(128) )
BEGIN
    IF EXISTS  ( SELECT * FROM SYS.SYSTAB WHERE table_name = @TableName ) THEN
       EXECUTE IMMEDIATE 'DROP TABLE "' || @TableName || '"';
       MESSAGE 'Table "' || @TableName || '" dropped' to client;
    END IF;
END;
CALL drop_table( 'CustRank' );

パラメーター パラメーター名は、カラム名など他のデータベース識別子に対するルールに従って付けてください。これらは有効な SQL データ型にする必要があります。
パラメーターには、IN、OUT、INOUT のいずれかのキーワードをプレフィクスとして付けることができます。これらの値のいずれも指定しない場合、パラメーターはデフォルトで INOUT になります。キーワードには次の意味があります。
- IN このパラメーターは、プロシージャーに値を与える式です。
- OUT このパラメーターは、プロシージャーから値を受け取ることがある変数です。
- INOUT このパラメーターはプロシージャーに値を与え、プロシージャーから新しい値を受け取ることがある変数です。
CALL 文を使ってプロシージャーを実行する場合、必ずしもすべてのパラメーターを指定する必要はありません。CREATE PROCEDURE 文の中にデフォルト値がある場合、不明のパラメーターにデフォルト値を割り当てます。CALL に引数が指定されておらず、デフォルトも設定されていない場合には、エラーが発生します。

SQLSTATE と SQLCODE は、プロシージャーが終了するときに、SQLSTATE または SQLCODE 値を出力する、特別な OUT パラメーターです。SQLSTATE と SQLCODE の特別値は、プロシージャーのリターンステータスのテストを目的として、プロシージャー呼び出しの直後にチェックできます。

SQLSTATE と SQLCODE 特別値は、その次の SQL 文によって修正されます。SQLSTATE と SQLCODE をプロシージャー引数として与えると、リターンコードは変数の中に格納されます。

CREATE OR REPLACE PROCEDURE を指定すると、新しいプロシージャーが作成されるか、同じ名前の既存のプロシージャーが置き換えられます。この句によって、プロシージャーの定義は変更されますが、既存のパーミッションは保持されます。OR REPLACE をテンポラリプロシージャーで使用することはできません。置き換え対象のプロシージャーが使用中の場合は、エラーが返されます。CREATE OR REPLACE PROCEDURE 文が実行されると、接続のためのオープンカーソルが閉じられます。

RESULT 句 RESULT 句は結果セットのカラムの数と型を宣言します。RESULT キーワードに続くカッコで囲まれたリストは、結果カラムの名前と型を定義します。CALL 文が記述されていると、この情報を Embedded SQL DESCRIBE または ODBC SQLDescribeCol が返します。

プロシージャーは、その実行方法に応じて、それぞれカラム数が異なる複数の結果セットを生成する場合があります。たとえば次のプロシージャーは、2 カラムを返す場合も、1 カラムを返す場合もあります。

CREATE PROCEDURE names( IN formal char(1))
BEGIN
    IF formal = 'n' THEN
        SELECT GivenName
        FROM Employees
    ELSE
        SELECT Surname, GivenName
        FROM Employees
    END IF
END;

これらの結果セットプロシージャーは RESULT 句を指定しないで記述するか、Transact-SQL で記述します。これらの使用には、次の制約があります。

Embedded SQL 正しい形式の結果セットを取得するには、結果セットのカーソルが開かれてからローが返されるまでの間に、プロシージャーコールを記述 (DESCRIBE) します。DESCRIBE 文の CURSOR cursor-name 句は必須です。
ODBC、OLE DB、ADO.NET 変数結果セットプロシージャーは、これらのインターフェイスを使用するアプリケーションで使用できます。結果セットの記述は、ドライバーまたはプロバイダーによって実行されます。
Open Client アプリケーション 変数結果セットプロシージャーは Open Client アプリケーションで使用できます。
Web サービス Web サービスはストアドプロシージャーの RESULTS 句を使用して、結果セットのカラムの数と型を指定します。Web サービスは、複数の結果セットを返すプロシージャーや EXECUTE IMMEDIATE を使用する変数結果セットには対応していません。

注意

WITH RESULT SET ON 句を含む EXECUTE IMMEDIATE 文をプロシージャーで使用しており、文から返される結果セットがプロシージャーから返される結果セットと同じである場合には、EXECUTE IMMEDIATE 文の結果セットの最初のカラムのみが返されます。

プロシージャーが結果セットを 1 つしか返さない場合、RESULT 句を使用してください。この句を使用すると、カーソルがオープンした後で ODBC と Open Client のアプリケーションが結果セットを記述し直すのを防ぐことができます。

複数の結果セットを処理するために ODBC は、プロシージャーが定義した結果セットではなく、現在実行中のカーソルを記述します。したがって、ODBC はいつもプロシージャー定義の RESULT 句内で定義されているカラム名を記述するわけではありません。この問題を回避するには、結果セットを生成する SELECT 文でカラムエイリアスを使用します。

NO RESULT SET 句 このプロシージャーによって結果セットが返されないことを宣言します。この句は、プロシージャーが結果セットを返さないことを外部環境から知る必要がある場合に役立ちます。

SQL SECURITY 句 SQL SECURITY 句は、INVOKER (プロシージャーを呼び出すユーザー) または DEFINER (プロシージャーを所有するユーザー) としてプロシージャーが実行されるかどうかを定義します。デフォルトは DEFINER です。

SQL SECURITY INVOKER が指定されている場合は、プロシージャーを呼び出すユーザーごとに注釈を行う必要があるためメモリ使用量が増えます。SQL SECURITY INVOKER が指定されている場合は、呼び出し側としても名前の決定が行われます。このため、すべてのオブジェクト名 (テーブル、プロシージャーなど) を該当する所有者で修飾する場合は注意が必要です。たとえば、user1 が次のプロシージャーを作成するとします。

CREATE PROCEDURE user1.myProcedure()
    RESULT( columnA INT )
    SQL SECURITY INVOKER
BEGIN
    SELECT columnA FROM table1;
END;

user2 がこのプロシージャーを実行しようとし、テーブル user2.table1 が存在しない場合、テーブルルックアップエラーが生じます。さらに、user2.table1 が存在する場合は、意図する user1.table1 の代わりにこのテーブルが使用されます。このような状況を防ぐには、文においてテーブル参照を修飾します (単なる table1 ではなく、user1.table1 とします)。

ON EXCEPTION RESUME 句 この句は、Transact-SQL のようなエラー処理を Watcom SQL 構文のプロシージャーで使用可能にします。
ON EXCEPTION RESUME を使用すると、プロシージャーは on_tsql_error オプションの設定に応じたアクションを実行します。on_tsql_error を Conditional (デフォルト) に設定すると、次の文がエラーを処理する場合は実行が継続され、そうでない場合は終了します。

エラー処理文には、次のようなものがあります。
- IF
- SELECT @variable =
- CASE
- LOOP
- LEAVE
- CONTINUE
- CALL
- EXECUTE
- SIGNAL
- RESIGNAL
- DECLARE
- SET VARIABLE
ON EXCEPTION RESUME では、明示的なエラー処理コードを使用しないでください。
AT location-string 句 location-string に指定されたリモートプロシージャーのプロキシストアドプロシージャーを現在のデータベース上に作成します。AT 句は、location-string のフィールドデリミターとしてセミコロン (;) をサポートします。セミコロンがない場合は、ピリオドがフィールドデリミターです。セミコロンを使用すると、データベースと所有者の各フィールドにファイル名と拡張子を使用できます。
リモートプロシージャーを使用する場合、variable-name は SQL 変数 variable-name の内容に置き換えられます。これは、CHAR、VARCHAR、LONG VARCHAR 型でなければなりません。AT 句の変数の使用の詳細については、ディレクトリアクセスサーバーの作成 (Sybase Central の場合)の例 2 を参照してください。

リモートプロシージャーが結果セットを返すことができる場合は、たとえすべてのケースで結果セットを返せるわけではなくても、ローカルプロシージャー定義には RESULT 句を含めてください。

CREATE PROCEDURE 文はデータベースにプロシージャーを作成します。DBA 権限があるユーザーは、所有者を指定することによって他のユーザーのプロシージャーを作成できます。プロシージャーは CALL 文で呼び出します。

ストアドプロシージャーが結果セットを返す場合、出力パラメーターを設定したり戻り値を返したりすることはできません。

複数のプロシージャーからテンポラリテーブルを参照する場合、テンポラリテーブル定義が矛盾していたり、テーブルを参照する文がキャッシュされていたりすると、問題が発生する可能性があります。

テンポラリプロシージャーを作成するのでないかぎり、RESOURCE 権限が必要です。

外部プロシージャーを作成する場合、および別のユーザーのプロシージャーを作成する場合、DBA 権限が必要です。

オートコミット、テンポラリプロシージャーにも適用。

SQL/2008 CREATE PROCEDURE は SQL/2008 標準のコア機能ですが、SQL Anywhere でサポートされている一部のコンポーネントはオプションの SQL 言語機能です。これらの機能のサブセットを次に示します。
- SQL SECURITY 句は、オプションの SQL/2008 言語機能 T324 です。
- SQL プロシージャーに LONG VARCHAR、LONG NVARCHAR、または LONG BINARY 値を渡す機能は、SQL/2008 言語機能 T041 です。
- CREATE TABLE または DROP TRIGGER などの文を使用して SQL プロシージャー内でスキーマオブジェクトを作成または変更する機能は、SQL/2008 言語機能 T651 です。
- EXECUTE IMMEDIATE、PREPARE、および DESCRIBE などの文を使用して SQL プロシージャー内で動的 SQL 文を使用する機能は、SQL/2008 言語機能 T652 です。
CREATE PROCEDURE 文のいくつかの句は、ベンダー拡張です。これらを以下に示します。
- TEMPORARY 句。
- ON EXCEPTION RESUME 句。
- AT 句。
- 特定のルーチンパラメーター用のオプションの DEFAULT 句。
- RESULT 句と NO RESULT SET 句。SQL/2008 標準では、RETURNS キーワードが使用されます。
- オプションの OR REPLACE 句。
Transact-SQL CREATE PROCEDURE は、Adaptive Server Enterprise でサポートされています。

CREATE PROCEDURE 文 [T-SQL]

次のプロシージャーは、Employees テーブルに問い合わせ、指定の給与 (sal) の指定の割合 (percentage) 内にある給与を返します。

CREATE OR REPLACE PROCEDURE AverageEmployees( IN percentage NUMERIC( 5,3), IN sal NUMERIC( 20, 3 ) )
RESULT( Department CHAR(40), GivenName person_name_t, Surname person_name_t, Salary NUMERIC( 20, 3) )
BEGIN
    DECLARE maxS NUMERIC( 20, 3 );
    DECLARE minS NUMERIC( 20, 3 );

    IF percentage >= 1 THEN
        SET percentage = percentage / 100;
    ELSEIF percentage < 0 THEN
        SELECT 'Percentage error', 'Err','Err', -1;
        RETURN;
    END IF;

    SELECT MIN( E.Salary ), MAX( E.Salary ) INTO minS, maxS
    FROM Employees E;

    IF sal < minS OR sal > maxS THEN
        SELECT 'Salary out of bounds', 'Err', 'Err', -2;
        RETURN;
    END IF;

    SELECT D.DepartmentName, E.GivenName, E.Surname, E.Salary
    FROM Employees E JOIN Departments D ON E.DepartmentID = D.DepartmentID
    WHERE E.Salary BETWEEN sal *( 1 - percentage ) AND sal * ( 1 + percentage );
END;

次のプロシージャーは、CASE 文を使用してクエリの結果を分類します。

CREATE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
    DECLARE prod_name CHAR(20);
    SELECT name INTO prod_name FROM Products
    WHERE ID = product_ID;
    CASE prod_name
    WHEN 'Tee Shirt' THEN
        SET type = 'Shirt'
    WHEN 'Sweatshirt' THEN
        SET type = 'Shirt'
    WHEN 'Baseball Cap' THEN
        SET type = 'Hat'
    WHEN 'Visor' THEN
        SET type = 'Hat'
    WHEN 'Shorts' THEN
        SET type = 'Shorts'
    ELSE
        SET type = 'UNKNOWN'
    END CASE;
END;

次の例は、前の例で作成された ProductType プロシージャーを置き換えます。プロシージャーが置き換えられた後、Tee Shirt と Sweatshirt のパラメーターが更新されます。

CREATE OR REPLACE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
    DECLARE prod_name CHAR(20);
    SELECT name INTO prod_name FROM Products
    WHERE ID = product_ID;
    CASE prod_name
    WHEN 'Tee Shirt' THEN
        SET type = 'T Shirt'
    WHEN 'Sweatshirt' THEN
        SET type = 'Long Sleeve Shirt'
    WHEN 'Baseball Cap' THEN
        SET type = 'Hat'
    WHEN 'Visor' THEN
        SET type = 'Hat'
    WHEN 'Shorts' THEN
        SET type = 'Shorts'
    ELSE
        SET type = 'UNKNOWN'
    END CASE;
END;

次のプロシージャーはカーソルのロー上でカーソルとループを使用して、単一の値を返します。

CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
    DECLARE err_notfound EXCEPTION
    FOR SQLSTATE '02000';
    DECLARE curThisCust CURSOR FOR
        SELECT CompanyName,
             CAST(SUM(SalesOrderItems.Quantity *
             Products.UnitPrice) AS INTEGER) VALUE
        FROM Customers
        LEFT OUTER JOIN SalesOrders
        LEFT OUTER JOIN SalesOrderItems
        LEFT OUTER JOIN Products
        GROUP BY CompanyName;
    DECLARE ThisValue INT;
    DECLARE ThisCompany CHAR(35);
    SET TopValue = 0;
    OPEN curThisCust;
    CustomerLoop:
    LOOP
        FETCH NEXT curThisCust
        INTO ThisCompany, ThisValue;
        IF SQLSTATE = err_notfound THEN
            LEAVE CustomerLoop;
        END IF;
        IF ThisValue > TopValue THEN
            SET TopValue = ThisValue;
            SET TopCompany = ThisCompany;
            END IF;
    END LOOP CustomerLoop;
    CLOSE curThisCust;
END;

CREATE PROCEDURE 文

構文

パラメーター

注意

備考

パーミッション

関連する動作

参照

標準と互換性

参照

例