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

このトピックでは、Snowflakeスクリプトを使用して、 SQL でストアドプロシージャを記述する方法について説明します。

このトピックの内容:

紹介¶

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

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

注釈

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

詳細については、 SnowSQL および Classic Console でのSnowflakeスクリプトの使用をご参照ください。

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

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

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

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

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

Copy

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します（SnowSQL および Classic Console でのSnowflakeスクリプトの使用を参照）。

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スクリプトの式でそれらの引数を名前で参照できます。詳細については、次のセクションをご参照ください。

ストアドプロシージャに渡される引数を使用する単純な例
SQL ステートメントでの引数の使用（バインド）
オブジェクト識別子としての引数の使用
SQL ステートメントの文字列作成時における引数の使用

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

次のストアドプロシージャは、 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 ステートメントでの変数の使用（バインド）を参照。）

たとえば、次のストアドプロシージャは、 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

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

オブジェクトを参照するために引数を使用する必要がある場合（例: 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

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

表形式のデータを返す¶

ストアドプロシージャから表形式のデータ（例: 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