Snowflakeスクリプトでのストアドプロシージャの記述¶

このトピックでは、Snowflake Scriptingを使用して SQL でストアドプロシージャを記述する方法を紹介します。Snowflake Scriptingの詳細については、 Snowflakeスクリプト開発者ガイドをご参照￥ください。

このトピックの内容:

概要¶

Snowflakeスクリプトを使用するストアドプロシージャを記述するには、

CREATE PROCEDURE または WITH ... CALL ... コマンドを LANGUAGE SQL で使用します。
ストアドプロシージャの本文（AS 句）では、 Snowflakeスクリプトのブロックを使用します。

注釈

SnowSQL または Classic Console でSnowflakeスクリプトプロシージャを作成する場合は、ストアドプロシージャの本文の前後に文字列リテラル区切り文字（' または $$）を使用する必要があります。

詳細については、 Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用するをご参照ください。

Snowflake は、Snowflakeスクリプトストアドプロシージャの本文にあるソースコードの最大サイズを制限します。Snowflakeは、サイズを100 KB に制限することをお勧めします。（コードは圧縮された形式で保存され、正確な制限はコードの圧縮率によって異なります。）

ハンドラーコードの実行時にログをキャプチャし、データをトレースできます。詳細については、ロギング、トレース、メトリクスをご参照ください。

注釈

呼び出し元の権限と所有者の権限に関する同じ規則が、これらのストアドプロシージャに適用されます。
ストアドプロシージャの使用と同じ考慮事項とガイドラインが、Snowflakeスクリプトのストアドプロシージャに適用されます。

以下は、渡された引数の値を返す単純なストアドプロシージャの例です。

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR) RETURNS VARCHAR NOT NULL LANGUAGE SQL AS BEGIN RETURN message; END; 

Copy

注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream、 execute_string メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください（Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用するを参照）。

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR) RETURNS VARCHAR NOT NULL LANGUAGE SQL AS $$ BEGIN RETURN message; END; $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL output_message('Hello World'); 

Copy

以下は、 WITH ... CALL ... コマンドを使用して、匿名ストアドプロシージャを作成し、呼び出す例です。

WITH anonymous_output_message AS PROCEDURE (message VARCHAR) RETURNS VARCHAR NOT NULL LANGUAGE SQL AS $$ BEGIN RETURN message; END;  $$ CALL anonymous_output_message('Hello World'); 

Copy

匿名ストアドプロシージャでは、文字列リテラル区切り文字（' または $$）をプロシージャ本文の周囲に使用しなければならないことに注意してください。

ストアドプロシージャに渡される引数の使用¶

ストアドプロシージャに引数を渡すと、Snowflakeスクリプトの式でそれらの引数を名前で参照できます。Snowflakeスクリプトストアドプロシージャは、入力引数（IN）と出力引数（OUT）をサポートしています。

Snowflakeスクリプトストアドプロシージャの定義で出力引数を指定すると、ストアドプロシージャは、出力引数の現在の値を、匿名ブロックや別のストアドプロシージャなどの呼び出し元プログラムに返すことができます。ストアドプロシージャは、出力引数の初期値を取得し、その値をプロシージャ本文の変数に保存します。オプションで、変数の値を変更する操作を実行してから、更新された値を呼び出し元プログラムに返します。

たとえば、営業担当者のユーザー識別子と四半期の売上を emp_quarter_calling_sp_demo というストアドプロシージャに渡すことができます。このストアドプロシージャは、sales_total_out_sp_demo という別のストアドプロシージャを呼び出します。sales_total_out_sp_demo ストアドプロシージャには、営業担当者の四半期の売上合計を呼び出し元ストアドプロシージャ emp_quarter_calling_sp_demo に返す操作を実行する出力引数があります。このシナリオの例については、出力引数を使用して、四半期における従業員の総売上を返します。をご参照ください。

渡される値のデータ型と出力引数のデータ型が不一致の場合は、サポートされている強制が自動的に実行されます。例については、呼び出し元のプロシージャからの入力値とは異なるデータ型の出力引数の使用をご参照ください。Snowflakeが自動的に実行できる強制の情報については、キャストできるデータ型をご参照ください。

GET_DDL 関数と SHOW PROCEDURES コマンドは、出力にストアドプロシージャの引数の型（IN または OUT）を表示します。ストアドプロシージャに関するメタデータを表示するその他のコマンドとビュー（DESCRIBE PROCEDURE コマンド、情報スキーマ PROCEDURES ビュー、アカウント使用状況 PROCEDURES ビューなど）では、引数の型は表示されません。

署名で異なる引数の型を指定してストアドプロシージャをオーバーロードすることはできません。たとえば、ストアドプロシージャが次の署名を持つとします。

CREATE PROCEDURE test_overloading(a IN NUMBER) 

Copy

次の CREATEPROCEDURE コマンドは、引数の型のみが前の例とは異なる新しいストアドプロシージャを作成しようとするため、プロシージャが既に存在するというエラーで失敗します。

CREATE PROCEDURE test_overloading(a OUT NUMBER) 

Copy

構文¶

Snowflakeスクリプトのストアドプロシージャ定義で引数を指定するには、次の構文を使用します。

<arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type> 

Copy

条件:

arg_name

引数の名前。名前は、オブジェクト識別子の名前付け規則に従う必要があります。

{ IN | INPUT | OUT | OUTPUT }

引数が入力引数か出力引数かを指定するオプションのキーワード。

IN または INPUT - 引数は指定された値で初期化され、この値がストアドプロシージャ変数に割り当てられます。変数はストアドプロシージャ本文で変更できますが、その最終値を呼び出し元プログラムに渡すことはできません。

IN と INPUT は同義語です。
OUT または OUTPUT - 引数は指定された値で初期化され、この値がストアドプロシージャ変数に割り当てられます。変数はストアドプロシージャの本文で変更でき、その最終値は呼び出し元プログラムに渡すことができます。ストアドプロシージャ本文では、出力引数には変数を使用してのみ値を割り当てることができます。

出力引数に初期化されていない変数を渡すこともできます。関連付けられている変数が割り当てられていない場合、出力引数は NULL を返します。

OUT と OUTPUT は同義語です。

デフォルト: IN

arg_data_type

SQL データ型。

制限事項¶

出力引数は、ストアドプロシージャの定義で指定する必要があります。
出力引数は :ref:` オプション引数 <label-procedure_function_arguments_optional>` として指定できません。つまり、出力引数は DEFAULT キーワードを使用して指定できません。
ストアドプロシージャ本文では、出力引数に値を割り当てるために変数を使用する必要があります。
同じ変数を複数の出力引数に使用することはできません。
セッション変数を出力引数に渡すことはできません。
ユーザー定義関数（UDFs）は出力引数をサポートしていません。
SQL 以外の言語で記述されたストアドプロシージャは出力引数をサポートしていません。
出力引数は :doc:` 非同期子ジョブ </developer-guide/snowflake-scripting/asynchronous-child-jobs>` では使用できません。
ストアドプロシージャの引数は、入力引数と出力引数を含めて500個までに制限されています。

例¶

ストアドプロシージャに渡される引数を使用する単純な例¶

次のストアドプロシージャは、 IF および RETURN ステートメントの引数の値を使用します。

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER) RETURNS INTEGER NOT NULL LANGUAGE SQL AS BEGIN IF (number_1 > number_2) THEN RETURN number_1; ELSE RETURN number_2; END IF; END; 

Copy

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER) RETURNS INTEGER NOT NULL LANGUAGE SQL AS $$ BEGIN IF (number_1 > number_2) THEN RETURN number_1; ELSE RETURN number_2; END IF; END; $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL return_greater(2, 3); 

Copy

SQL ステートメントでの引数の使用（バインド）¶

Snowflakeスクリプト変数の場合と同様に、 SQL ステートメントで引数を使用する必要がある場合は、引数名の前にコロン（:）を付けます。詳細については、 SQL ステートメントでの変数の使用（バインド）をご参照ください。

以下の節では、ストアドプロシージャでバインド変数を使用する例を示します。

WHERE 句でバインド変数を使う例¶

次のストアドプロシージャは、 SELECT ステートメントの WHERE 句で id 引数を使用します。WHERE 句では、引数は :id として指定されます。

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR) RETURNS TABLE (id INTEGER, price NUMBER(12,2)) LANGUAGE SQL AS DECLARE res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id); BEGIN RETURN TABLE(res); END; 

Copy

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR) RETURNS TABLE (id INTEGER, price NUMBER(12,2)) LANGUAGE SQL AS $$ DECLARE res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id); BEGIN RETURN TABLE(res); END; $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL find_invoice_by_id('2'); 

Copy

さらに、 TO_QUERY 関数は、 SELECT ステートメントの FROM 句で直接 SQL 文字列を受け入れるための簡単な構文を提供します。TO_QUERY 関数とダイナミック SQL の比較については実行時に SQL を構築するをご参照ください。

バインド変数を使ってプロパティの値をセットする例¶

以下のストアドプロシージャは、 comment 引数を使用して、 CREATE TABLE ステートメント内のテーブルにコメントを追加します。ステートメントでは、引数は :comment として指定されます。

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR) RETURNS STRING LANGUAGE SQL AS BEGIN CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment; END; 

Copy

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR) RETURNS STRING LANGUAGE SQL AS $$ BEGIN CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment; END; $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL test_bind_comment('My Test Table'); 

Copy

INFORMATION_SCHEMA の TABLES ビューをクエリして、テーブルのコメントを表示します。

SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT'; 

Copy

+---------------+ | COMMENT | |---------------| | My Test Table | +---------------+ 

コメントを表示するには、 SHOW TABLES コマンドを実行します。

バインド変数を使ってコマンドのパラメーターをセットする例¶

CSV ファイルを持つ st というステージがあるとします。

CREATE OR REPLACE STAGE st; PUT file://good_data.csv @st; PUT file://errors_data.csv @st; 

Copy

CSV ファイルのデータを test_bind_stage_and_load という名前のテーブルに読み込みたいのです。

CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR); 

Copy

次のストアドプロシージャは、 COPY INTO <テーブル> ステートメントで FROM、 ON_ERROR、 VALIDATION_MODE パラメーターを使用します。ステートメントでは、パラメーター値をそれぞれ :my_stage_name、 :on_error、 :valid_mode と指定します。

CREATE OR REPLACE PROCEDURE test_copy_files_validate( my_stage_name VARCHAR, on_error VARCHAR, valid_mode VARCHAR) RETURNS STRING LANGUAGE SQL AS BEGIN COPY INTO test_bind_stage_and_load FROM :my_stage_name ON_ERROR=:on_error FILE_FORMAT=(type='csv') VALIDATION_MODE=:valid_mode; END; 

Copy

CREATE OR REPLACE PROCEDURE test_copy_files_validate( my_stage_name VARCHAR, on_error VARCHAR, valid_mode VARCHAR) RETURNS STRING LANGUAGE SQL AS $$ BEGIN COPY INTO test_bind_stage_and_load FROM :my_stage_name ON_ERROR=:on_error FILE_FORMAT=(type='csv') VALIDATION_MODE=:valid_mode; END; $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors'); 

Copy

配列にバインド変数を使う例¶

配列を表すバインド変数を個々の値のリストに展開するには、スプレッド演算子（**）を使います。詳細情報と例については、展開演算子をご参照ください。

オブジェクト識別子としての引数の使用¶

オブジェクトを参照するために引数を使用する必要がある場合（例: SELECT ステートメントの FROM 句のテーブル名）は、 IDENTIFIER キーワードを使用して、引数がオブジェクト識別子を表すことを示します。例:

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR) RETURNS INTEGER NOT NULL LANGUAGE SQL AS DECLARE row_count INTEGER DEFAULT 0; res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name)); c1 CURSOR FOR res; BEGIN FOR row_variable IN c1 DO row_count := row_variable.count; END FOR; RETURN row_count; END; 

Copy

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR) RETURNS INTEGER NOT NULL LANGUAGE SQL AS $$ DECLARE row_count INTEGER DEFAULT 0; res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name)); c1 CURSOR FOR res; BEGIN FOR row_variable IN c1 DO row_count := row_variable.count; END FOR; RETURN row_count; END; $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL get_row_count('invoices'); 

Copy

次の例では、引数で指定されたテーブル名に基づいて、ストアドプロシージャの CREATE TABLE ... AS SELECT（CTAS）ステートメントを実行します。

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR) RETURNS TEXT LANGUAGE SQL AS BEGIN CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS SELECT * FROM IDENTIFIER(:existing_table); RETURN 'Table created'; END; 

Copy

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR) RETURNS TEXT LANGUAGE SQL AS $$ BEGIN CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS SELECT * FROM IDENTIFIER(:existing_table); RETURN 'Table created'; END; $$ ; 

Copy

プロシージャを呼び出す前に、簡単なテーブルを作成してデータを挿入します。

CREATE OR REPLACE TABLE test_table_for_ctas_sp ( id NUMBER(2), v VARCHAR(2)) AS SELECT column1, column2, FROM VALUES (1, 'a'), (2, 'b'), (3, 'c'); 

Copy

ストアドプロシージャを呼び出して、このテーブルをベースとした新しいテーブルを作成します。

CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup'); 

Copy

SQL ステートメントの文字列作成時における引数の使用¶

EXECUTE IMMEDIATE に渡される文字列として SQL ステートメントを作成する場合（宣言された RESULTSET へのクエリの割り当てを参照）は、引数の前にコロンを付けないでください。例:

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR) RETURNS TABLE (id INTEGER, price NUMBER(12,2)) LANGUAGE SQL AS DECLARE select_statement VARCHAR; res RESULTSET; BEGIN select_statement := 'SELECT * FROM invoices WHERE id = ' || id; res := (EXECUTE IMMEDIATE :select_statement); RETURN TABLE(res); END; 

Copy

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR) RETURNS TABLE (id INTEGER, price NUMBER(12,2)) LANGUAGE SQL AS $$ DECLARE select_statement VARCHAR; res RESULTSET; BEGIN select_statement := 'SELECT * FROM invoices WHERE id = ' || id; res := (EXECUTE IMMEDIATE :select_statement); RETURN TABLE(res); END; $$ ; 

Copy

出力引数を使用して単一の値を返す¶

次の例では、定義内に出力引数 xout を使用してストアドプロシージャ simple_out_sp_demo を作成します。このストアドプロシージャは、xout の値を 2 に設定します。

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER) RETURNS STRING LANGUAGE SQL AS BEGIN xout := 2; RETURN 'Done'; END; 

Copy

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER) RETURNS STRING LANGUAGE SQL AS $$ BEGIN xout := 2; RETURN 'Done'; END; $$ ; 

Copy

次の匿名ブロックは、x 変数の値を 1 に設定します。次に、simple_out_sp_demo ストアドプロシージャを呼び出し、その変数を引数として指定します。

BEGIN LET x := 1; CALL simple_out_sp_demo(:x); RETURN x; END; 

Copy

EXECUTE IMMEDIATE $$ BEGIN LET x := 1; CALL simple_out_sp_demo(:x); RETURN x; END; $$ ; 

Copy

出力から、simple_out_sp_demo ストアドプロシージャが出力引数の値を 2 に設定する操作を実行し、その値を匿名ブロックに返したことがわかります。

+-----------------+ | anonymous block | |-----------------| | 2 | +-----------------+ 

次の匿名ブロックは simple_out_sp_demo ストアドプロシージャを呼び出し、変数ではなく式を使用して出力引数に値を割り当てようとしているため、エラーを返します。

BEGIN LET x := 1; CALL simple_out_sp_demo(:x + 2); RETURN x; END; 

Copy

EXECUTE IMMEDIATE $$ BEGIN LET x := 1; CALL simple_out_sp_demo(:x + 2); RETURN x; END; $$ ; 

Copy

出力引数を使用して、ストアドプロシージャへの複数の呼び出しで複数の値を返す¶

次の例は、ストアドプロシージャと入力引数および出力引数に関連する次の動作を示しています。

ストアドプロシージャは、その定義内に複数の入力引数と出力引数を持つことができます。
プログラムは出力引数を持つストアドプロシージャを複数回呼び出すことができ、出力引数の値は各呼び出し後に保持されます。
入力引数は、呼び出し元のプログラムに値を返しません。

定義内に複数の入力引数と出力引数を持つストアドプロシージャ multiple_out_sp_demo を作成します。ストアドプロシージャは、同等の入力引数と出力引数に対して同じ操作を実行します。たとえば、ストアドプロシージャは 1 を p1_in 入力引数と p1_out 出力引数に追加します。

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo( p1_in NUMBER, p1_out OUT NUMBER, p2_in VARCHAR(100), p2_out OUT VARCHAR(100), p3_in BOOLEAN, p3_out OUT BOOLEAN) RETURNS NUMBER LANGUAGE SQL AS BEGIN p1_in := p1_in + 1; p1_out := p1_out + 1; p2_in := p2_in || ' hi '; p2_out := p2_out || ' hi '; p3_in := (NOT p3_in); p3_out := (NOT p3_out); RETURN 1; END; 

Copy

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo( p1_in NUMBER, p1_out OUT NUMBER, p2_in VARCHAR(100), p2_out OUT VARCHAR(100), p3_in BOOLEAN, p3_out OUT BOOLEAN) RETURNS NUMBER LANGUAGE SQL AS $$ BEGIN p1_in := p1_in + 1; p1_out := p1_out + 1; p2_in := p2_in || ' hi '; p2_out := p2_out || ' hi '; p3_in := (NOT p3_in); p3_out := (NOT p3_out); RETURN 1; END; $$ ; 

Copy

以下の匿名ブロックは、multiple_out_sp_demo ストアドプロシージャの引数に対応する変数に値を割り当て、その後、ストアドプロシージャを複数回呼び出します。最初の呼び出しは匿名ブロックで指定された変数値を使用しますが、後続の各呼び出しは multiple_out_sp_demo ストアドプロシージャの出力引数によって返された値を使用します。

BEGIN LET x_in INT := 1; LET x_out INT := 1; LET y_in VARCHAR(100) := 'hello'; LET y_out VARCHAR(100) := 'hello'; LET z_in BOOLEAN := true; LET z_out BOOLEAN := true; CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out); CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out); CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out); RETURN [x_in, x_out, y_in, y_out, z_in, z_out]; END; 

Copy

EXECUTE IMMEDIATE $$ BEGIN LET x_in INT := 1; LET x_out INT := 1; LET y_in VARCHAR(100) := 'hello'; LET y_out VARCHAR(100) := 'hello'; LET z_in BOOLEAN := true; LET z_out BOOLEAN := true; CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out); CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out); CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out); RETURN [x_in, x_out, y_in, y_out, z_in, z_out]; END; $$ ; 

Copy

+------------------------+ | anonymous block | |------------------------| | [ | | 1, | | 4, | | "hello", | | "hello hi hi hi ", | | true, | | false | | ] | +------------------------+ 

呼び出し元のプロシージャからの入力値とは異なるデータ型の出力引数の使用¶

ユースケースによっては、ストアドプロシージャに渡される値のデータ型とプロシージャの出力引数のデータ型が不一致になる可能性があります。このような場合サポートされている強制が自動的に実行されます。

注釈

強制はサポートされている場合もありますが、推奨されていません。

この例では、NUMBER データ型の出力引数に渡される FLOAT 値の自動変換を示します。その FLOAT 値は自動的に NUMBER 値に変換され、呼び出し元の匿名ブロックに返されます。

NUMBER 型の出力引数を取る sp_out_coercion ストアドプロシージャを作成します。

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER) RETURNS STRING LANGUAGE SQL AS BEGIN x := x * 2; RETURN 'Done'; END; 

Copy

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER) RETURNS STRING LANGUAGE SQL AS $$ BEGIN x := x * 2; RETURN 'Done'; END; $$ ; 

Copy

FLOAT 値を sp_out_coercion ストアドプロシージャに渡す匿名ブロックを実行します。

BEGIN LET a FLOAT := 500.662; CALL sp_out_coercion(:a); RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')'; END; 

Copy

EXECUTE IMMEDIATE $$ BEGIN LET a FLOAT := 500.662; CALL sp_out_coercion(:a); RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')'; END; $$ ; 

Copy

SYSTEM$TYPEOF 関数を呼び出すことにより、出力には返される値と返される値のデータ型の両方が表示されます。ストアドプロシージャから値が返された後、NUMBER 値から FLOAT 値に強制されることに注意してください。

+---------------------------+ | anonymous block | |---------------------------| | 1002 (Type FLOAT[DOUBLE]) | +---------------------------+ 

出力引数を使用して、四半期における従業員の総売上を返します。¶

この例では、以下の quarterly_sales テーブルを使用します。

CREATE OR REPLACE TABLE quarterly_sales( empid INT, amount INT, quarter TEXT) AS SELECT * FROM VALUES (1, 10000, '2023_Q1'), (1, 400, '2023_Q1'), (2, 4500, '2023_Q1'), (2, 35000, '2023_Q1'), (1, 5000, '2023_Q2'), (1, 3000, '2023_Q2'), (2, 200, '2023_Q2'), (2, 90500, '2023_Q2'), (1, 6000, '2023_Q3'), (1, 5000, '2023_Q3'), (2, 2500, '2023_Q3'), (2, 9500, '2023_Q3'), (3, 2700, '2023_Q3'), (1, 8000, '2023_Q4'), (1, 10000, '2023_Q4'), (2, 800, '2023_Q4'), (2, 4500, '2023_Q4'), (3, 2700, '2023_Q4'), (3, 16000, '2023_Q4'), (3, 10200, '2023_Q4'); 

Copy

従業員識別子と四半期の2つの入力引数と、指定された従業員と四半期の売上合計を計算する1つの出力引数を取るストアドプロシージャ sales_total_out_sp_demo を作成します。

CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo( id INT, quarter VARCHAR(20), total_sales OUT NUMBER(38,0)) RETURNS STRING LANGUAGE SQL AS $$ BEGIN SELECT SUM(amount) INTO total_sales FROM quarterly_sales WHERE empid = :id AND quarter = :quarter; RETURN 'Done'; END; $$ ; 

Copy

sales_total_out_sp_demo ストアドプロシージャを呼び出すストアドプロシージャ emp_quarter_calling_sp_demo を作成します。このストアドプロシージャも、従業員識別子と四半期の2つの入力引数を取ります。

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo( id INT, quarter VARCHAR(20)) RETURNS STRING LANGUAGE SQL AS BEGIN LET x NUMBER(38,0); CALL sales_total_out_sp_demo(:id, :quarter, :x); RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x; END; 

Copy

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo( id INT, quarter VARCHAR(20)) RETURNS STRING LANGUAGE SQL AS $$ BEGIN LET x NUMBER(38,0); CALL sales_total_out_sp_demo(:id, :quarter, :x); RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x; END; $$ ; 

Copy

引数 2``（従業員識別子）と ``'2023_Q4'``（四半期）を指定して ``emp_quarter_calling_sp_demo を呼び出します。

CALL emp_quarter_calling_sp_demo(2, '2023_Q4'); 

Copy

+-----------------------------------------------------+ | emp_quarter_calling_sp_demo | |-----------------------------------------------------| | Total sales for employee 2 in quarter 2023_Q4: 5300 | +-----------------------------------------------------+ 

表形式のデータを返す¶

ストアドプロシージャから表形式のデータ（例: RESULTSET からのデータ）を返す必要がある場合は、 CREATE PROCEDURE ステートメントで RETURNS TABLE(...)を指定します。

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

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

Copy

それ以外の場合（例: 実行時に列型を決定する場合）は、列名と型を省略できます。

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

Copy

注釈

現在、 RETURNS TABLE(...) 句では、 GEOGRAPHY を列タイプとして指定することはできません。これは、ストアドプロシージャまたは匿名プロシージャを作成する場合に適用されます。

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

Copy

WITH test_return_geography_table_1() AS PROCEDURE RETURNS TABLE(g GEOGRAPHY) ... CALL test_return_geography_table_1(); 

Copy

列タイプとして GEOGRAPHY を指定しようとすると、ストアドプロシージャの呼び出しはエラーになります。

Stored procedure execution error: data type of returned table does not match expected returned table type 

Copy

この問題を回避するには、 RETURNS TABLE() の列の引数と型を省略します。

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

Copy

WITH test_return_geography_table_1() AS PROCEDURE RETURNS TABLE() ... CALL test_return_geography_table_1(); 

Copy

RESULTSET でデータを返す必要がある場合は、 RETURN ステートメントで TABLE()を使用します。

例:

CREATE OR REPLACE PROCEDURE get_top_sales() RETURNS TABLE (sales_date DATE, quantity NUMBER) LANGUAGE SQL AS DECLARE res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10); BEGIN RETURN TABLE(res); END; 

Copy

CREATE OR REPLACE PROCEDURE get_top_sales() RETURNS TABLE (sales_date DATE, quantity NUMBER) LANGUAGE SQL AS $$ DECLARE res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10); BEGIN RETURN TABLE(res); END; $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL get_top_sales(); 

Copy

別のストアドプロシージャからのストアドプロシージャ呼び出し¶

ストアドプロシージャで、別のストアドプロシージャを呼び出す必要がある場合は、次のいずれかの方法を使用します。

戻り値を使用しないストアドプロシージャ呼び出し¶

CALL ステートメントを使用して、ストアドプロシージャを呼び出します（通常どおり）。

CALL ステートメントで入力引数として変数または引数を渡す必要がある場合は、変数名の前にコロン（:）を使用することを忘れないでください。（SQL ステートメントでの変数の使用（バインド）を参照。）

以下は、別のストアドプロシージャを呼び出すが、戻り値に依存しないストアドプロシージャの例です。

まず、例で使用するテーブルを作成します。

-- Create a table for use in the example. CREATE OR REPLACE TABLE int_table (value INTEGER); 

Copy

次に、別のストアドプロシージャから呼び出すストアドプロシージャを作成します。

-- Create a stored procedure to be called from another stored procedure. CREATE OR REPLACE PROCEDURE insert_value(value INTEGER) RETURNS VARCHAR NOT NULL LANGUAGE SQL AS BEGIN INSERT INTO int_table VALUES (:value); RETURN 'Rows inserted: ' || SQLROWCOUNT; END; 

Copy

-- Create a stored procedure to be called from another stored procedure. CREATE OR REPLACE PROCEDURE insert_value(value INTEGER) RETURNS VARCHAR NOT NULL LANGUAGE SQL AS $$ BEGIN INSERT INTO int_table VALUES (:value); RETURN 'Rows inserted: ' || SQLROWCOUNT; END; $$ ; 

Copy

次に、最初のストアドプロシージャを呼び出す2番目のストアドプロシージャを作成します。

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER) RETURNS VARCHAR NOT NULL LANGUAGE SQL AS BEGIN CALL insert_value(:value1); CALL insert_value(:value2); RETURN 'Finished calling stored procedures'; END; 

Copy

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER) RETURNS VARCHAR NOT NULL LANGUAGE SQL AS $$ BEGIN CALL insert_value(:value1); CALL insert_value(:value2); RETURN 'Finished calling stored procedures'; END; $$ ; 

Copy

最後に、2番目のストアドプロシージャを呼び出します。

CALL insert_two_values(4, 5); 

Copy

ストアドプロシージャ呼び出しからの戻り値の使用¶

スカラー値を返すストアドプロシージャを呼び出していて、その値にアクセスする必要がある場合は、 CALL ステートメントで INTO :snowflake_scripting_variable 句を使用して、 Snowflakeスクリプト変数の値をキャプチャします。

次の例では、オブジェクト識別子としての引数の使用で定義された get_row_count ストアドプロシージャを呼び出します。

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER) RETURNS BOOLEAN NOT NULL LANGUAGE SQL AS DECLARE count1 NUMBER; BEGIN CALL get_row_count(:table_name) INTO :count1; IF (:count1 > maximum_count) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; 

Copy

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER) RETURNS BOOLEAN NOT NULL LANGUAGE SQL AS $$ DECLARE count1 NUMBER; BEGIN CALL get_row_count(:table_name) INTO :count1; IF (:count1 > maximum_count) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END;  $$ ; 

Copy

以下は、ストアドプロシージャを呼び出す例です。

CALL count_greater_than('invoices', 3); 

Copy

ストアドプロシージャがテーブルを返す場合は、 RESULTSET を CALL ステートメントを含む文字列に設定することで、戻り値をキャプチャできます。（宣言された RESULTSET へのクエリの割り当てを参照。）

呼び出しから戻り値を取得するには、 RESULTSET の CURSOR を使用できます。例:

DECLARE res1 RESULTSET; BEGIN res1 := (CALL my_procedure()); LET c1 CURSOR FOR res1; FOR row_variable IN c1 DO IF (row_variable.col1 > 0) THEN ...; ELSE ...; END IF; END FOR; ... 

Copy

ストアドプロシージャから呼び出しストアドプロシージャへの出力引数値の引き渡し¶

Snowflakeスクリプトストアドプロシージャの定義で出力引数が指定されている場合、ストアドプロシージャは出力引数の現在の値を呼び出し元のストアドプロシージャに返すことができます。ストアドプロシージャは、出力引数の初期値を取得し、その値をプロシージャ本文の変数に保存し、オプションで変数の値を変更する操作を実行します。次に、ストアドプロシージャは更新された値を呼び出し元のストアドプロシージャに返します。

例については、出力引数を使用して、四半期における従業員の総売上を返します。をご参照ください。

ネストされたストアドプロシージャの使用¶

ネストされたストアドプロシージャ*とは、匿名ブロック内、または別のストアドプロシージャ（*親ストアドプロシージャ）内のブロック内で定義されるストアドプロシージャのことです。

ネストされたストアドプロシージャは、CREATE PROCEDURE ステートメントの一部となるブロックの DECLARE セクションで宣言します。次の例は、ネストされたストアドプロシージャの宣言を示しています。

DECLARE <nested_stored_procedure_name> PROCEDURE (<arguments>) RETURNS <data_type> AS BEGIN <nested_procedure_procedure_statements> END; BEGIN <statements> END; 

Copy

ネストされたストアドプロシージャの宣言構文については、ネストされたストアドプロシージャ宣言構文をご参照ください。

ネストされたストアドプロシージャは、その :doc:` ブロック </developer-guide/snowflake-scripting/blocks>` のスコープ内にのみ存在します。ネストされたストアドプロシージャは、そのブロックのどのセクションからでも呼び出すことができます（DECLARE、BEGIN ... END、EXCEPTION）。1つのブロックには複数のネストされたストアドプロシージャを含めることができ、1つのネストされたストアドプロシージャは、同じブロックで別のネストされたストアドプロシージャを呼び出すことができます。ネストされたプロシージャは、そのブロックの外部から呼び出すことやアクセスすることはできません。

ネストされたストアドプロシージャは、それを定義するブロックと同じセキュリティコンテキストで動作します。ネストされたストアドプロシージャが親ストアドプロシージャで定義されると、親ストアドプロシージャと同じ権限で自動的に実行されます。

注釈

ネストされたストアドプロシージャの宣言と CALL WITH コマンドはどちらも、スコープが制限された一時ストアドプロシージャを作成します。それぞれの違いは以下のとおりです。

CALL WITH ステートメントは、ストアドプロシージャ内を含め、SQL ステートメントが使用できる場所であればどこでも使用できますが、ネストされたストアドプロシージャの宣言はSnowflakeスクリプトのブロック内である必要があります。
CALLWITH ストアドプロシージャはそのステートメントのスコープ内にのみ存在しますが、ネストされたストアドプロシージャはそのSnowflakeスクリプトブロックのスコープ内に存在します。

ネストされたストアドプロシージャの利点¶

ネストされたストアドプロシージャには、次の利点があります。

ロジックを匿名ブロックや親ストアドプロシージャ内にカプセル化することで、ブロックまたは親の外部からのアクセスを防ぎ、セキュリティを強化、簡素化できます。
コードを論理的に小さなチャンクに分割することでコードのモジュール性を維持し、メンテナンスとデバッグを容易にします。
ネストされたストアドプロシージャはそのブロックのローカル変数に直接アクセスできるため、グローバル変数や追加の引数の必要性が減り、メンテナンス性が改善します。

ネストされたストアドプロシージャを呼び出すための使用上の注意¶

ネストされたストアドプロシージャの呼び出しには、次の使用上の注意が適用されます。

ネストされたストアドプロシージャに引数を渡すために、ブロックでは定数値、Snowflakeスクリプト変数、バインド変数、SQL（セッション）変数、ユーザー定義関数の呼び出しを使用できます。
渡される値のデータ型と引数のデータ型が不一致の場合、Snowflakeはサポートされている強制を自動的に実行します。Snowflakeが自動的に実行できる強制の情報については、データ型の変換をご参照ください。

ネストされたストアドプロシージャ内の変数の使用上の注意¶

ネストされたストアドプロシージャ内の変数には、次の使用上の注意が適用されます。

ネストされたストアドプロシージャは、そのブロックの DECLARE セクション内のネストされたストアドプロシージャの宣言より前に宣言された変数をそのブロックから参照できます。DECLARE セクションで自身の宣言より後に定義された変数は参照できません。
ネストされたストアドプロシージャは、ブロックの BEGIN ... END セクションの LET ステートメントで宣言された変数にアクセスできません。
参照される変数の値は、ネストされたストアドプロシージャが呼び出された時点の値を反映します。
ネストされたストアドプロシージャは、参照している変数の値を変更でき、その変更された値は、匿名ブロックの1回の実行または親ストアドプロシージャへの1回の呼び出しで、ブロック内および同じネストされたプロシージャの複数の呼び出しにわたって保持されます。
ネストされたストアドプロシージャの呼び出しの前に宣言された変数の値は、ネストされたストアドプロシージャに引数として渡すことができます。変数の値は、その変数がネストされたストアドプロシージャの宣言後や LET ステートメント内で宣言されたものであっても、呼び出し時の引数として渡すことができます。

たとえば、次のストアドプロシージャでは複数の変数が宣言されています。

CREATE OR REPLACE PROCEDURE outer_sp () RETURNS NUMBER LANGUAGE SQL AS $$ DECLARE var_before_nested_proc NUMBER DEFAULT 1; test_nested_variables PROCEDURE(arg1 NUMBER) -- <nested_sp_logic> var_after_nested_proc NUMBER DEFAULT 2; BEGIN LET var_let_before_call NUMBER DEFAULT 3; LET result := CALL nested_proc(:<var_name>); LET var_let_after_call NUMBER DEFAULT 3; RETURN result; END; $$; 

Copy

この例では、nested_sp_logic で参照できるのは var_before_nested_proc のみです。

ネストされたストアドプロシージャの呼び出しでは、以下の変数のいずれの値も、var_name の引数としてネストされたストアドプロシージャに渡すことができます。

var_before_nested_proc
var_after_nested_proc
var_let_before_call

var_let_after_call の値は、ネストされたストアドプロシージャに引数として渡すことはできません。

ネストされたストアドプロシージャの制限¶

ネストされたストアドプロシージャの定義には、次の制限が適用されます。

他のネストされたストアドプロシージャ内、または FOR ループや WHILE ループなどの制御構造内では定義できません。
ネストされた各ストアドプロシージャは、そのブロック内で一意の名前を持つ必要があります。つまり、ネストされたストアドプロシージャはオーバーロードできません。
出力（OUT）引数はサポートされていません。
デフォルト値を持つオプションの引数はサポートされていません。

ネストされたストアドプロシージャの呼び出しには、次の制限が適用されます。

EXECUTE IMMEDIATE ステートメントでは呼び出せません。
:doc:` 非同期子ジョブ </developer-guide/snowflake-scripting/asynchronous-child-jobs>` では呼び出せません。
名前付き入力引数（arg_name => arg）はサポートされていません。引数は位置で指定する必要があります。詳細については、 CALL をご参照ください。

ネストされたストアドプロシージャの例¶

次の例では、ネストされたストアドプロシージャを使用しています。

表形式データを返すネストされたストアドプロシージャを定義する¶

次の例では、表形式データを返すネストされたストアドプロシージャを定義します。この例では、nested_procedure_example_table と呼ばれる親ストアドプロシージャと、nested_return_table と呼ばれるネストされたストアドプロシージャが作成されます。コードには、次のロジックが含まれています。

RESULTSET 型の res という変数を宣言します。
ネストされたストアドプロシージャに次のロジックが含まれます。
- res2 という変数を宣言します。
- nested_table というテーブルに値を挿入します。
- res2 変数を、テーブルに対する SELECT の結果に設定します。
- 結果セットの表形式のデータを返します。
親ストアドプロシージャにテーブル nested_table を作成します。
ネストされたストアドプロシージャ nested_return_table を呼び出し、res 変数をネストされたストアドプロシージャへの呼び出しの結果に設定します。
表形式の結果を res 変数に返します。

CREATE OR REPLACE PROCEDURE nested_procedure_example_table() RETURNS TABLE() LANGUAGE SQL AS $$ DECLARE res RESULTSET; nested_return_table PROCEDURE() RETURNS TABLE() AS DECLARE res2 RESULTSET; BEGIN INSERT INTO nested_table VALUES(1); INSERT INTO nested_table VALUES(2); res2 := (SELECT * FROM nested_table); RETURN TABLE(res2); END; BEGIN CREATE OR REPLACE TABLE nested_table(col1 INT); res := (CALL nested_return_table()); RETURN TABLE(res); END; $$; 

Copy

ストアドプロシージャを呼び出します。

CALL nested_procedure_example_table(); 

Copy

+------+ | COL1 | |------| | 1 | | 2 | +------+ 

スカラー値を返すネストされたストアドプロシージャを定義する¶

次の例では、スカラー値を返すネストされたストアドプロシージャを定義します。この例では、nested_procedure_example_scalar と呼ばれる親ストアドプロシージャと、simple_counter と呼ばれるネストされたストアドプロシージャが作成されます。コードには、次のロジックが含まれています。

NUMBER 型の変数 counter を宣言し、この変数の値を 0 に設定します。
ネストされたストアドプロシージャが、counter 型の変数の現在の値に 1 を加えるよう指定します。
親ストアドプロシージャで、ネストされたストアドプロシージャを3回呼び出します。counter 変数の値は、ネストされたストアドプロシージャの呼び出し間で引き継がれます。
counter 変数の値（3）を返します。

CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar() RETURNS VARCHAR LANGUAGE SQL AS $$ DECLARE counter NUMBER := 0; simple_counter PROCEDURE() RETURNS VARCHAR AS BEGIN counter := counter + 1; RETURN counter; END; BEGIN CALL simple_counter(); CALL simple_counter(); CALL simple_counter(); RETURN counter; END; $$; 

Copy

ストアドプロシージャを呼び出します。

CALL nested_procedure_example_scalar(); 

Copy

+---------------------------------+ | NESTED_PROCEDURE_EXAMPLE_SCALAR | |---------------------------------| | 3 | +---------------------------------+ 

匿名ブロックでネストされたストアドプロシージャを定義します¶

次の例は、ストアドプロシージャではなく、匿名ブロック内でネストされたストアドプロシージャを定義していることを除き、スカラー値を返すネストされたストアドプロシージャを定義するの例と同じです。

EXECUTE IMMEDIATE $$ DECLARE counter NUMBER := 0; simple_counter PROCEDURE() RETURNS VARCHAR AS BEGIN counter := counter + 1; RETURN counter; END; BEGIN CALL simple_counter(); CALL simple_counter(); CALL simple_counter(); RETURN counter; END; $$; 

Copy

+-----------------+ | anonymous block | |-----------------| | 3 | +-----------------+ 

引数を渡されるネストされたストアドプロシージャを定義する¶

次の例では、引数を渡されるネストされたストアドプロシージャを定義します。この例では、ネストされたストアドプロシージャは、次のテーブルに値を挿入します。

CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT); 

Copy

この例では、nested_procedure_example_arguments と呼ばれる親ストアドプロシージャと、log_and_multiply_numbers と呼ばれるネストされたストアドプロシージャが作成されます。ネストされたストアドプロシージャは、NUMBER 型の引数を2つ取ります。コードには、次のロジックが含まれています。

NUMBER 型の変数 a、b、x を宣言します。
次のアクションを実行するネストされたストアドプロシージャが含まれます。
- バインド変数を使用して、親ストアドプロシージャから渡された2つの数値を log_nested_values テーブルに挿入します。
- 変数 x の値を、2つの引数値を乗算した結果に設定します。
- x の値を親ストアドプロシージャに返します。
変数 a の値を 5 に、変数 b の値を 10 に設定します。
ネストされたストアドプロシージャを呼び出します。
ネストされたストアドプロシージャで設定された x 変数の値を返します。

CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments() RETURNS NUMBER LANGUAGE SQL AS $$ DECLARE a NUMBER; b NUMBER; x NUMBER; log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER) RETURNS NUMBER AS BEGIN INSERT INTO log_nested_values VALUES(:num1, :num2); x := :num1 * :num2; RETURN x; END; BEGIN a := 5; b := 10; CALL log_and_multiply_numbers(:a, :b); RETURN x; END; $$; 

Copy

ストアドプロシージャを呼び出します。

CALL nested_procedure_example_arguments(); 

Copy

+------------------------------------+ | NESTED_PROCEDURE_EXAMPLE_ARGUMENTS | |------------------------------------| | 50 | +------------------------------------+ 

log_nested_values テーブルをクエリして、ネストされたストアドプロシージャが渡された値を挿入したことを確認します。

SELECT * FROM log_nested_values; 

Copy

+------+------+ | COL1 | COL2 | |------+------| | 5 | 10 | +------+------+ 

別のネストされたストアドプロシージャを呼び出すネストされたストアドプロシージャを定義する¶

次の例では、別のネストされたストアドプロシージャを呼び出すネストされたストアドプロシージャを定義します。この例では、counter_nested_proc と call_counter_nested_proc と呼ばれる2つのネストされたストアドプロシージャを持つ、nested_procedure_example_call_from_nested と呼ばれる親ストアドプロシージャを作成します。コードには、次のロジックが含まれています。

NUMBER 型の変数 counter を宣言し、この変数の値を 0 に設定します。
counter の値に 10 を加算するネストされたストアドプロシージャ counter_nested_proc が含まれます。
counter の値に 15 を加えるネストされたストアドプロシージャ call_counter_nested_proc を含み、counter_nested_proc も呼び出します（これは、counter の値にさらに 10 を加えます）。
親ストアドプロシージャでネストされた両方のストアドプロシージャを呼び出します。
counter 変数の値（35）を返します。

CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested() RETURNS NUMBER LANGUAGE SQL AS $$ DECLARE counter NUMBER := 0; counter_nested_proc PROCEDURE() RETURNS NUMBER AS DECLARE var1 NUMBER := 10; BEGIN counter := counter + var1; END; call_counter_nested_proc PROCEDURE() RETURNS NUMBER AS DECLARE var2 NUMBER := 15; BEGIN counter := counter + var2; CALL counter_nested_proc(); END; BEGIN counter := 0; CALL counter_nested_proc(); CALL call_counter_nested_proc(); RETURN counter; END; $$; 

Copy

ストアドプロシージャを呼び出します。

CALL nested_procedure_example_call_from_nested(); 

Copy

+-------------------------------------------+ | NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED | |-------------------------------------------| | 35 | +-------------------------------------------+ 

ストアドプロシージャでの SQL 変数の使用とセット¶

デフォルトでは、Snowflake Scriptingストアドプロシージャはオーナー権限で実行されます。ストアドプロシージャがオーナー権限で実行されると、 SQL (またはセッション) 変数にアクセスできなくなります。

しかし、呼び出し元の権限ストアドプロシージャは、呼び出し元のセッション変数を読み取り、ストアドプロシージャのロジックで使用することができます。例えば、呼び出し元のライツストアドプロシージャは、 SQL 変数の値をクエリで使用することができます。呼び出し元の権限で実行されるストアドプロシージャを作成するには、 CREATE PROCEDURE ステートメントで EXECUTE AS CALLER パラメーターを指定します。

これらの例は、この呼び出し元権限と所有者権限のストアドプロシージャのキーの違いを示しています。これらは以下の2つの方法で SQL 変数の利用を試みています。

ストアドプロシージャを呼び出す前に SQL 変数をセットし、ストアドプロシージャ内で SQL 変数を使用します。
ストアドプロシージャの内部で SQL 変数をセットし、ストアドプロシージャから戻った後に SQL 変数を使用します。

SQL 変数の使用も SQL 変数のセットも、呼び出し元権利ストアドプロシージャで正しく機能します。呼び出し元が所有者であっても、所有者権限ストアドプロシージャを使用すると、両方とも失敗します。

所有者の権利および発信者の権利に関する情報については、呼び出し元権限と所有者権限のストアドプロシージャについてをご覧ください。

ストアドプロシージャ内での SQL 変数の使用¶

次の例では、ストアドプロシージャで SQL 変数を使用します。

まず、セッションに SQL 変数をセットします。

SET example_use_variable = 2; 

Copy

呼び出し元権限で実行され、この SQL 変数を使用する単純なストアドプロシージャを作成します。

CREATE OR REPLACE PROCEDURE use_sql_variable_proc() RETURNS NUMBER LANGUAGE SQL EXECUTE AS CALLER AS DECLARE sess_var_x_2 NUMBER; BEGIN sess_var_x_2 := 2 * $example_use_variable; RETURN sess_var_x_2; END; 

Copy

CREATE OR REPLACE PROCEDURE use_sql_variable_proc() RETURNS NUMBER LANGUAGE SQL EXECUTE AS CALLER AS $$ DECLARE sess_var_x_2 NUMBER; BEGIN sess_var_x_2 := 2 * $example_use_variable; RETURN sess_var_x_2; END; $$ ; 

Copy

ストアドプロシージャを呼び出します。

CALL use_sql_variable_proc(); 

Copy

+-----------------------+ | USE_SQL_VARIABLE_PROC | |-----------------------| | 4 | +-----------------------+ 

SQL 変数を別の値にセットします。

SET example_use_variable = 9; 

Copy

プロシージャを再度呼び出して、返される値が変わったことを確認します。

CALL use_sql_variable_proc(); 

Copy

+-----------------------+ | USE_SQL_VARIABLE_PROC | |-----------------------| | 18 | +-----------------------+ 

ストアドプロシージャの SQL 変数のセット¶

呼び出し元権限で実行されているストアドプロシージャで、 SQL 変数をセットすることができます。ストアドプロシージャで SQL 変数を使用する際のガイドラインなど、詳細情報については呼び出し元権限ストアドプロシージャを参照してください。

注釈

ストアドプロシージャの内部で SQL 変数をセットし、プロシージャの終了後もセットしたままにすることはできますが、Snowflake ではこの方法を推奨していません。

次の例では、ストアドプロシージャで SQL 変数を設定します。

まず、セッションに SQL 変数をセットします。

SET example_set_variable = 55; 

Copy

SQL 変数の値を確認します。

SHOW VARIABLES LIKE 'example_set_variable'; 

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+ | session_id | created_on | updated_on | name | value | type | comment | |----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------| | 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55 | fixed | | +----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+ 

例えば、次のストアドプロシージャは、 SQL 変数 example_set_variable を新しい値にセットし、新しい値を返します。

CREATE OR REPLACE PROCEDURE set_sql_variable_proc() RETURNS NUMBER LANGUAGE SQL EXECUTE AS CALLER AS BEGIN SET example_set_variable = $example_set_variable - 3; RETURN $example_set_variable; END; 

Copy

CREATE OR REPLACE PROCEDURE set_sql_variable_proc() RETURNS NUMBER LANGUAGE SQL EXECUTE AS CALLER AS $$ BEGIN SET example_set_variable = $example_set_variable - 3; RETURN $example_set_variable; END; $$ ; 

Copy

ストアドプロシージャを呼び出します。

CALL set_sql_variable_proc(); 

Copy

+-----------------------+ | SET_SQL_VARIABLE_PROC | |-----------------------| | 52 | +-----------------------+ 

SQL 変数の新しい値を確認します。

SHOW VARIABLES LIKE 'example_set_variable'; 

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+ | session_id | created_on | updated_on | name | value | type | comment | |----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------| | 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52 | fixed | | +----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+ 