Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (日本語) » SQL Anywhere サーバ - SQL リファレンス » SQL の使用 » SQL 文 » SQL 文 (A ~ D)

 

CREATE PROCEDURE 文

この文は、データベースにユーザ定義 SQL プロシージャを作成するために使用します。外部プロシージャ・インタフェースの作成方法については、CREATE PROCEDURE 文 [外部プロシージャ]を参照してください。Web サービス・プロシージャの作成方法については、CREATE PROCEDURE 文 [Web サービス]を参照してください。

構文
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 
parameter :
  parameter-mode parameter-name data-type [ DEFAULT expression ]
| SQLCODE
| SQLSTATE
parameter-mode : IN 
| OUT 
| INOUT
result-column : column-name data-type
パラメータ
  • CREATE PROCEDURE   永続的またはテンポラリ (TEMPORARY) のストアド・プロシージャを作成できます。PROC は PROCEDURE の同義語として使用できます。

    パラメータ名は、カラム名など他のデータベース識別子に対するルールに従って付けてください。これらは有効な SQL データ型にする必要があります。有効なデータ型のリストについては、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 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' );

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

    プロシージャから返される結果セットの詳細については、プロシージャから返される結果を参照してください。

    プロシージャは、その実行方法に応じて、それぞれカラム数が異なる複数の結果セットを生成する場合があります。たとえば次のプロシージャは、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 アプリケーションで使用できます。

    プロシージャが結果セットを 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 では、明示的なエラー処理コードを使用しないでください。

    on_tsql_error オプション [互換性]を参照してください。

  • AT location-string 句   location-string に指定されたリモート・プロシージャのプロキシ・ストアド・プロシージャを現在のデータベース上に作成します。AT 句は、location-string のフィールド・デリミタとしてセミコロン (;) をサポートします。セミコロンがない場合は、ピリオドがフィールド・デリミタです。セミコロンを使用すると、データベースと所有者の各フィールドにファイル名と拡張子を使用できます。

    リモート・プロシージャは、最長 254 バイトの入力パラメータを受け入れ、最大 254 文字の出力変数を返します。

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

    リモート・サーバの詳細については、CREATE SERVER 文を参照してください。リモート・プロシージャの使用については、リモート・プロシージャ・コール (RPC) の使用を参照してください。

備考

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

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

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

パーミッション

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

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

関連する動作

オートコミット。

参照
標準と互換性
  • SQL/2003   永続的ストアド・モジュール機能。Java 結果セットの構文拡張は、オプションの J621 機能に指定されています。

次のプロシージャは、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;