カテゴリ:

ユーザー定義関数、外部関数、およびストアドプロシージャ用の DDL

CREATE PROCEDURE

新しい ストアドプロシージャ を作成します。

プロシージャは、次のいずれかの言語で記述できます。

こちらもご参照ください。

ALTER PROCEDUREDROP PROCEDURESHOW PROCEDURESDESCRIBE PROCEDURE

CALL

構文

JavaScript

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

重要

JavaScript は大文字と小文字を区別しますが、 SQL は区別しません。JavaScript コードでストアドプロシージャの引数名を使用する際の重要な情報については、 JavaScript 引数の大文字と小文字の区別 をご参照ください。

Snowflakeスクリプト

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
    [ NOT NULL ]
  LANGUAGE SQL
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS <procedure_definition>

注釈

SnowSQL または従来のウェブインターフェイスでSnowflakeスクリプトプロシージャを作成する場合は、 プロシージャ定義 の前後に 文字列リテラル区切り文字' または $$)を使用する必要があります。 SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 をご参照ください。

Snowpark(ScalaおよびJava)

インラインストアドプロシージャの場合は、次の構文を使用します。

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

Java および Scalaでプリコンパイル済みのストアドプロシージャ の場合は、次の構文を使用します。

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]

必須パラメーター

すべての言語

名前 ( [ 引数名 引数データ型 ] [ , ... ] )

識別子(名前)とストアドプロシージャの入力引数を指定します。

RETURNS 結果データ型 [ NOT NULL ]

ストアドプロシージャによって返される結果の型を指定します。

  • 結果データ型 の場合は、使用している言語の型に対応するSnowflakeデータ型を使用します。

  • RETURNS TABLE ( [ 列名 列データ型 [ , ... ] ] ) で、返されたテーブルの列の Snowflakeデータ型 がわかっている場合は、列名と型を指定します。

    CREATE OR REPLACE PROCEDURE get_top_sales()
    RETURNS TABLE (sales_date DATE, quantity NUMBER)
    ...
    

    それ以外の場合(実行時に列タイプを決定する場合など)は、列名と型を省略できます。

    CREATE OR REPLACE PROCEDURE get_top_sales()
    RETURNS TABLE ()
    

    注釈

    現在、 CREATE PROCEDURERETURNS TABLE(...) 句では、列型として GEOGRAPHY を指定できません。

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
    RETURNS TABLE(g GEOGRAPHY)
    ...
    

    指定した場合にストアドプロシージャを呼び出すと、次のエラーが発生します。

    CALL test_return_geography_table_1();
    
    Stored procedure execution error: data type of returned table does not match expected returned table type
    

    これを回避するには、 RETURNS TABLE() の列の引数と型を省略できます。

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
    RETURNS TABLE()
    ...
    

    RETURNS TABLE(...) は、テーブルを返すSnowflakeスクリプトのストアドプロシージャでのみサポートされます。 RETURN をご参照ください。

実際問題として、呼び出しは式の一部とすることはできないため、戻り値は使用できません。

LANGUAGE 言語

ストアドプロシージャコードの言語を指定します。これは、 Snowflakeスクリプト で記述されたストアドプロシージャでは、オプションであることに注意してください。

現在、 言語 でサポートされている値は次のとおりです。

デフォルト: SQL

AS プロシージャ定義

ストアドプロシージャによって実行されるコードを定義します。定義は、任意の有効なコードで構成できます。

次の点に注意してください。

  • Snowpark Java および Scalaでプリコンパイル済みのストアドプロシージャ の場合は、 AS 句を省略します。

    代わりに IMPORTS 句を使用して、ストアドプロシージャのコードを含む JAR ファイルの場所を指定します。詳細については、以下をご参照ください。

  • 次の場合は、 プロシージャ定義 の前後に 文字列リテラル区切り文字' または $$)を使用する必要があります。

  • JavaScript のストアドプロシージャで、改行を含む文字列を記述している場合は、文字列の前後にバッククォート(別称「バックティック」)を使用できます。

    次の JavaScript ストアドプロシージャの例では、ストアドプロシージャの本文に一重引用符と二重引用符が含まれているため、 $$ とバッククォートを使用しています。

    CREATE OR REPLACE TABLE table1 ("column 1" VARCHAR);
    
    CREATE or replace PROCEDURE proc3()
      RETURNS VARCHAR
      LANGUAGE javascript
      AS
      $$
      var rs = snowflake.execute( { sqlText: 
          `INSERT INTO table1 ("column 1") 
               SELECT 'value 1' AS "column 1" ;`
           } );
      return 'Done.';
      $$;
    
  • Snowflakeスクリプト 以外の言語の場合、 CREATE PROCEDURE コマンドを実行しても、Snowflakeはコードを包括的には検証しません。

    たとえば、Snowpark(Scala)ストアドプロシージャの場合、入力引数の数と型は検証されますが、関数の本文は検証されません。1つまたは複数の型が一致しない場合(例: 引数が非数値型のときにSnowflakeデータ型 NUMBER が使用されている場合)に、 CREATE PROCEDURE コマンドを実行するとエラーが発生します。

    コードが有効でない場合は、 CREATE PROCEDURE コマンドが成功し、ストアドプロシージャが呼び出されたときにエラーが返されます。

ストアドプロシージャの詳細については、 ストアドプロシージャの使用 をご参照ください。

Snowpark(ScalaおよびJava)

RUNTIME_VERSION = 'ScalaまたはJavaのランタイムバージョン'

使用するScalaまたはJavaのランタイムバージョン。現在、サポートされている唯一のバージョンは次のとおりです。

  • 2.12 (Scalaの場合)

  • 11 (Javaの場合)。

PACKAGES = ( '完全修飾パッケージ名' )

Snowparkライブラリの完全修飾パッケージ名。

Snowparkライブラリの完全修飾パッケージ名を次の形式で指定します。

com.snowflake:snowpark:<version>

ここで、 バージョン はバージョン番号、または最新バージョンの場合は latest です。

注釈

少なくともサポートされている最小バージョン以上のバージョンを指定してください。

  • Scalaの場合は、バージョン1.1.0以降を指定します。

    Snowflakeは、ストアドプロシージャでSnowparkバージョン0.9.0以降の使用をサポートしています。ただし、これらのバージョンには制限があることに注意してください。たとえば、1.1.0より前のバージョンは、ストアドプロシージャでトランザクションの使用をサポートしていません。

  • Javaの場合は、バージョン1.3.0以降を指定します。

サポートされているパッケージとバージョンのリストについては、 LANGUAGE = 'scala' または LANGUAGE = 'java' で、行の INFORMATION_SCHEMA.PACKAGES ビュー をクエリします。例:

select * from information_schema.packages where language = 'scala';
select * from information_schema.packages where language = 'java';
HANDLER = '完全修飾メソッド名'

ストアドプロシージャのメソッドまたは関数の完全修飾名。これは通常、次の形式です。

com.my_company.my_package.MyClass.myMethod

条件:

com.my_company.my_package

オブジェクトまたはクラスを含むパッケージに対応します。

package com.my_company.my_package;

オプションのパラメーター

すべての言語

[ [ NOT ] NULL ]

ストアドプロシージャにより NULL 値を返すことができるか、 NON-NULL 値のみを返す必要があるかを指定します。

デフォルトは NULL です(つまり、ストアドプロシージャは NULL を返すことができます)。

CALLED ON NULL INPUT または . RETURNS NULL ON NULL INPUT | STRICT

null入力で呼び出されたときのストアドプロシージャの動作を指定します。入力がnullの場合は常にnullを返すシステム定義関数とは対照的に、ストアドプロシージャはnull入力を処理でき、入力がnullの場合でもnull以外の値を返します。

  • CALLED ON NULL INPUT 常にnull入力でストアドプロシージャを呼び出します。そのような値を適切に処理するのは手順次第です。

  • RETURNS NULL ON NULL INPUT (またはその同義語 STRICT)は、入力がnullの場合にストアドプロシージャを呼び出さないため、ストアドプロシージャ内のステートメントは実行されません。代わりに、null値が常に返されます。プロシージャは、null以外の入力に対してもnullを返す場合があります。

デフォルト: CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

結果を返すときのストアドプロシージャの動作を指定します。

  • VOLATILE: 同じ入力で異なる時間に呼び出された場合、プロシージャは異なる値を返す可能性があります(例: 非決定性およびステートフル性のため)。

  • IMMUTABLE: プロシージャが同じ入力値で呼び出されると、常に同じ結果が返されます。この保証は確認されていません。同じ入力に対して異なる値を返すプロシージャに IMMUTABLE を指定すると、未定義の動作が発生します。

デフォルト: VOLATILE

COMMENT = '文字列リテラル'

SHOW PROCEDURES 出力の DESCRIPTION 列に表示されるストアドプロシージャのコメントを指定します。

デフォルト: stored procedure

EXECUTE AS CALLER または . EXECUTE AS OWNER

ストアドプロシージャが、所有者の権限(「所有者の権利」ストアドプロシージャ)または呼び出し元の権限(「呼び出し元の権利」ストアドプロシージャ)で実行するかどうかを指定します。

  • CREATE PROCEDURE ... EXECUTE AS CALLER ステートメントを実行すると、その後、プロシージャは呼び出し元の権限プロシージャとして実行されます。

  • CREATE PROCEDURE ... EXECUTE AS OWNER を実行すると、その後、プロシージャは所有者の権利プロシージャとして実行されます。

デフォルトでは(プロシージャの作成時に OWNER も CALLER も明示的に指定されていない場合)、プロシージャは所有者の権限のストアドプロシージャとして実行されます。

所有者の権利のストアドプロシージャは、呼び出し側の環境(呼び出し側のセッション変数など)へのアクセスが少なく、Snowflakeはデフォルトでこの高いレベルのプライバシーとセキュリティを使用します。

詳細については、 呼び出し元権限と所有者権限のストアドプロシージャの理解 をご参照ください。

デフォルト: OWNER

Snowpark(ScalaおよびJava)

IMPORTS = ( '読み込むステージパスとファイル名' [, '読み込みステージパスとファイル名' ...] )

インポートするファイルの場所(ステージ)、パス、および名前。ストアドプロシージャが依存するファイルを含めるには、 IMPORTS 句を設定する必要があります。

  • インラインストアドプロシージャを作成している場合は、コードがストアドプロシージャまたはリソースファイルの外部で定義されたクラスに依存している場合を除き、この句を省略できます。

  • プリコンパイル済みストアドプロシージャを作成する場合は、ストアドプロシージャの定義を含む JAR ファイルも含める必要があります。

IMPORTS 句の各ファイルは、ファイルが異なるサブディレクトリまたは異なるステージにある場合でも、一意の名前を持っている必要があります。

TARGET_PATH = '書き込むためのステージパスおよびファイル名'

TARGET_PATH 句は、 プロシージャ定義 で指定されたソースコードをコンパイルした後、Snowflakeがコンパイルされたコード(JAR ファイル)を書き込む場所を指定します。

この句を省略すると、Snowflakeはコードが必要になるたびにソースコードを再コンパイルします。

この句を指定した場合、

  • これを既存のファイルに設定することはできません。 TARGET_PATH が既存のファイルをポイントしている場合、Snowflakeはエラーを返します。

  • IMPORTS 句と TARGET_PATH 句の両方を指定する場合は、ファイルが異なるサブディレクトリまたは異なるステージにあっても、 TARGET_PATH 句にあるファイル名と IMPORTS 句にある各ファイル名は異なる必要があります。

  • ストアドプロシージャを使用する必要がなくなった場合(例: ストアドプロシージャを削除した場合)は、この JAR ファイルを手動で削除する必要があります。

使用上の注意

  • すべてのストアドプロシージャでは、

    • ストアドプロシージャは オーバーロード をサポートします。2つのプロシージャは、パラメーターの数またはパラメーターのデータ型が異なる場合、同じ名前を持つことができます。

    • ストアドプロシージャはアトミックではありません。ストアドプロシージャ内の1つのステートメントが失敗した場合、ストアドプロシージャ内の他のステートメントは必ずしもロールバックされるとは限りません。ストアドプロシージャとトランザクションについては、 トランザクション管理 をご参照ください。

    • メタデータについて。

      注意

      Snowflakeサービスを使用する場合、お客様は、個人データ(ユーザーオブジェクト向け以外)、機密データ、輸出管理データ、またはその他の規制されたデータがメタデータとして入力されていないことを確認する必要があります。詳細については、 Snowflakeのメタデータフィールド をご参照ください。

    ちなみに

    組織が呼び出し元の権利と所有者の権利のストアドプロシージャを組み合わせて使用している場合は、ストアドプロシージャの命名規則を使用して、個々のストアドプロシージャが呼び出し元の権利のストアドプロシージャか、所有者の権利のストアドプロシージャかを示すことができます。

  • JavaScript ストアドプロシージャの場合、

    • JavaScript ストアドプロシージャは、文字列(例: 成功/失敗インジケーター)や数値(例: エラーコード)などの単一の値のみを返すことができます。より広範な情報を返す必要がある場合は、区切り文字(コンマなど)で区切られた値を含む VARCHAR、または VARIANT などの半構造化データ型を返すことができます。

  • Snowpark(Scala)ストアドプロシージャについては、 既知の制限 をご参照ください。

  • Snowpark(Java)ストアドプロシージャについては、 既知の制限 をご参照ください。

これにより、ハードコードされた値を返す簡単なストアドプロシージャが作成されます。これは非現実的ですが、最小限の JavaScript コードで基本的な SQL 構文を示しています。

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;

これは、 JavaScript API への呼び出しを含むより現実的な例を示しています。このプロシージャのより広範なバージョンでは、ユーザーが直接挿入する権限を持っていないテーブルにデータを挿入できます。JavaScript ステートメントは、特定の要件が満たされた場合にのみ、入力パラメーターをチェックし、 SQL INSERT を実行できます。

create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var sql_command = 
     "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$
    ;

Snowpark(Scala)ストアドプロシージャの例については、 Snowpark(Scala)でのストアドプロシージャの記述 をご参照ください。

その他の例については、 ストアドプロシージャの使用 をご参照ください。