JavaScript でのストアドプロシージャの記述¶

このトピックでは、ストアドプロシージャの JavaScript コードを作成する方法について説明します。

注釈

匿名プロシージャの作成と呼び出しの両方を実行するには、 CALL （匿名プロシージャの場合）を使用します。匿名プロシージャの作成と呼び出しには、 CREATE PROCEDURE スキーマ権限を持つロールは必要ありません。

このトピックの内容:

JavaScript API を理解する
SQL および JavaScript データ型マッピング
一般的なヒント
- 行の継続
JavaScript ストアドプロシージャの考慮事項
例
トラブルシューティング

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

JavaScript API を理解する¶

ストアドプロシージャの JavaScriptAPI は、Snowflakeコネクタおよびドライバー（Node.js、 JDBC、Pythonなど）の APIs と似ていますが、同一ではありません。

API を使用すると、次のような操作を実行できます。

SQL ステートメントを実行します。
クエリの結果（つまり、結果セット）の取得。
結果セットに関するメタデータ（列の数、列のデータ型など）の取得。

これらの操作は、次のオブジェクトのメソッドを呼び出すことにより実行されます。

snowflake、 Statement オブジェクトを作成する、 SQL コマンドを実行するメソッド。
Statement、準備済みステートメントを実行し、それらの準備済みステートメントのメタデータにアクセスし、 ResultSet オブジェクトを取得できるようにする。
ResultSet、クエリの結果を保持する（例： SELECT ステートメントに対して取得されたデータの行）。
SfDate、JavaScript 日付の拡張（追加のメソッドを含む）であり、Snowflakeの SQL データ型 TIMESTAMP_LTZ、 TIMESTAMP_NTZ、 TIMESTAMP_TZの戻り型として機能する。

これらのオブジェクトの詳細については、 JavaScript ストアドプロシージャ API をご参照ください。

典型的なストアドプロシージャには、次の擬似コードに類似するコードが含まれています。

var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();

var my_sql_command2 = "delete from log_table where event_year < 2016";
var statement2 = snowflake.createStatement(my_sql_command2);
statement2.execute();

Copy

このコードは、 snowflake という名前のオブジェクトを使用します。これは、宣言なしで存在する特別なオブジェクトです。オブジェクトは各ストアドプロシージャのコンテキスト内で提供され、 API を公開して、サーバーと対話できるようにします。

他の変数（ statement1 など）は、 JavaScript var ステートメントで作成されます。例：

var statement1 = ...;

Copy

上記のコードサンプルに示すように、 snowflake オブジェクトを使用すると、 APIのメソッドの1つを呼び出すことで Statement オブジェクトを作成できます。

ResultSet を取得してそれを反復処理する例を次に示します。

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Copy

例セクション（このトピックの最後）には、ストアドプロシージャ JavaScript APIの各オブジェクトと多くのメソッドを実行する追加の例が記載されています。

SQL および JavaScript データ型マッピング¶

ストアドプロシージャの呼び出し、使用、値の取得をする場合、多くの場合においてSnowflake SQL データ型から JavaScript データ型、またはその逆に変換する必要があります。

SQL JavaScript への変換は、次の場合に発生する可能性があります。

引数を指定してストアドプロシージャを呼び出す場合。引数は SQL データ型です。ストアドプロシージャ内の JavaScript 変数内に保存されている場合は、変換する必要があります。
ResultSet オブジェクトから JavaScript 変数に値を取得する場合。 ResultSet は値を SQL データ型として保持し、 JavaScript 変数は値を JavaScript データ型の1つとして保存する必要があります。

JavaScript SQL への変換は、次の場合に発生する可能性があります。

ストアドプロシージャから値を返す場合。通常、 return ステートメントには JavaScript 変数が含まれており、 SQL データ型に変換する必要があります。
JavaScript 変数の値を使用する SQL ステートメントを動的に構築する場合。
JavaScript 変数の値を準備されたステートメントにバインドする場合。

Snowflake が JavaScript および SQL データ型をマッピングする方法の詳細については、 SQL-JavaScript データ型マッピングをご参照ください。

一般的なヒント¶

行の継続¶

SQL ステートメントは非常に長くなる可能性があり、それらを1行に収めることが常に実用的ではありません。JavaScript は、改行をステートメントの終わりとして扱います。長い SQL ステートメントを複数の行に分割する場合、次のような通常の JavaScript テクニックを使用して長い文字列を処理できます。

行の終わりの直前にバックスラッシュ（行継続文字）を置きます。例：
```
var sql_command = "SELECT * \
                       FROM table1;";
```
Copy
文字列を二重引用符で囲むのではなく、逆引用符（一重の逆引用符）を使用します。例：
```
var sql_command = `SELECT *
                       FROM table1;`;
```
Copy

文字列を累積します。例:

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

Copy

JavaScript ストアドプロシージャの考慮事項¶

JavaScript 数値範囲¶

精度がそのままの数値の範囲は

-(2^53 -1)

から

(2^53 -1)

Snowflake NUMBER（p、s）および DOUBLE データ型の有効な値の範囲が大きくなりました。Snowflakeから値を取得して JavaScript 数値変数に格納すると、精度が失われる可能性があります。例：

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;

Copy

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Copy

最初の2つの列は一致し、3番目の列は0.0を含む必要があります。

この問題は、 JavaScript ユーザー定義関数（UDFs）およびストアドプロシージャに適用されます。

getColumnValue() を使用しているときにストアドプロシージャで問題が発生した場合、次のように値を文字列として取得することで問題を回避できる可能性があります。

getColumnValueAsString()

Copy

その後、ストアドプロシージャから文字列を返し、文字列を SQLの数値データ型にキャストできます。

JavaScript のエラー処理¶

ストアドプロシージャは JavaScriptで記述されているため、 JavaScriptのtry/catch構文を使用できます。

ストアドプロシージャは、事前定義の例外またはカスタム例外をスローできます。カスタム例外をスローする簡単な例は、こちらです。

SQL ステートメントは、tryブロック内で実行できます。エラーが発生した場合は、catchブロックにより、すべてのステートメントをロールバックできます（ステートメントをトランザクションに配置した場合）。例セクションには、ストアドプロシージャでトランザクションをロールバックする例が含まれています。

ストアドプロシージャの制限¶

ストアドプロシージャには次の制限があります。

JavaScript コードは、 JavaScript eval() 関数を呼び出すことができません。
JavaScript ストアドプロシージャは、標準 JavaScript ライブラリへのアクセスをサポートします。これにより、ブラウザーが通常提供する多くのオブジェクトとメソッドが除外されます。追加のライブラリをインポートする、含める、または呼び出すメカニズムはありません。サードパーティライブラリを許可すると、セキュリティホールが生じる可能性があります。
JavaScript コードは制限されたエンジン内で実行され、 JavaScript コンテキストからのシステムコール（例: ネットワークおよびディスクアクセスなし）を防ぎ、エンジンで使用できるシステムリソース、特にメモリを制限します。

JavaScript 引数の大文字と小文字の区別¶

引数名は、ストアドプロシージャコードの SQL 部分では大文字と小文字を区別しませんが、JavaScript部分では大文字と小文字を区別します。

JavaScriptを使用するストアドプロシージャ（および UDFs）の場合、ステートメントの SQL 部分の識別子（引数名など）は自動的に大文字に変換されます（二重引用符で識別子を区切らない限り） JavaScript 部分は元のケースのままになります。これにより、引数が表示されないため、明示的なエラーメッセージを返さずにストアドプロシージャが失敗する可能性があります。

以下は、 JavaScript コードの引数の名前が SQL コードの引数の名前と一致しないストアドプロシージャの例です。大文字と小文字が異なるだけです。

次の例では、名前 argument1 が小文字であるため、最初の割り当てステートメントは正しくありません。

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

Copy

SQL ステートメントと JavaScript コードで一貫して大文字の識別子（特に引数名）を使用すると、サイレントエラーが減少する傾向があります。

JavaScript 引数の大文字と小文字の区別¶

引数名は、ストアドプロシージャコードの SQL 部分では大文字と小文字を区別しませんが、JavaScript部分では大文字と小文字を区別します。

JavaScript 区切り記号¶

ストアドプロシージャコードの JavaScript 部分は、一重引用符 ' または二重ドル記号 $$ で囲む必要があります。

$$ を使用すると、一重引用符を含む JavaScript コードを、それらの引用符を「エスケープ」せずに処理しやすくなります。

ストアドプロシージャ名のオーバーロード¶

オーバーロードと命名規則については、プロシージャおよび UDFs の命名とオーバーロードをご参照ください。

変数のバインド¶

変数を SQL ステートメントにバインドすると、ステートメントで変数の値を使用できます。

NULL の値とNULL の値をバインドできます。

変数のデータ型は、 SQL ステートメントの値の使用に適している必要があります。現在、型番号、文字列、および SfDate の JavaScript 変数のみをバインドできます。（ SQL データ型と JavaScriptデータ型間のマッピングの詳細については、 SQL および JavaScript データ型マッピングを参照。）

バインドの簡単な例を次に示します。

var stmt = snowflake.createStatement(
   {
   sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
   binds:["LiteralValue1", variable2]
   }
);

Copy

以下に、より完全な例を示します。この例では、 TIMESTAMP 情報をバインドします。 SQL TIMESTAMP データの直接バインドはサポートされていないため、この例ではタイムスタンプを VARCHARとして渡し、それをステートメントにバインドします。 SQL ステートメント自体は、 TO_TIMESTAMP（）関数を呼び出して VARCHAR を TIMESTAMP に変換することに注意してください。

この単純な関数は、指定されたタイムスタンプが現在より前であれば TRUE を返し、そうでなければ FALSE を返します。

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

Copy

CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+

Copy

これは、 VARCHAR、 TIMESTAMP_LTZ、およびその他のデータ型を INSERT ステートメントにバインドする方法を示しています。 TIMESTAMP_LTZ は、ストアドプロシージャ内で作成された SfDate 変数をバインドします。

テーブルを作成します。

CREATE TABLE table1 (v VARCHAR,
                     ts1 TIMESTAMP_LTZ(9), 
                     int1 INTEGER,
                     float1 FLOAT,
                     numeric1 NUMERIC(10,9),
                     ts_ntz1 TIMESTAMP_NTZ,
                     date1 DATE,
                     time1 TIME
                     );

Copy

ストアドプロシージャを作成します。このプロシージャは VARCHAR を受け入れ、 SQLを使用して VARCHAR を TIMESTAMP_LTZ に変換します。その後、プロシージャは ResultSetから変換された値を取得します。値は、タイプ SfDate の JavaScript 変数に保存されます。ストアドプロシージャは、元の VARCHAR と TIMESTAMP_LTZ の両方を INSERT ステートメントにバインドします。これは、 JavaScript 数値データのバインドも示しています。

CREATE OR REPLACE PROCEDURE string_to_timestamp_ltz(TSV VARCHAR) 
RETURNS TIMESTAMP_LTZ 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    // Convert the input varchar to a TIMESTAMP_LTZ.
    var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_LTZ;"; 
    var stmt = snowflake.createStatement( {sqlText: sql_command} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    // Retrieve the TIMESTAMP_LTZ and store it in an SfDate variable.
    var my_sfDate = resultSet.getColumnValue(1); 

    f = 3.1415926;

    // Specify that we'd like position-based binding.
    sql_command = `INSERT INTO table1 VALUES(:1, :2, :3, :4, :5, :6, :7, :8);` 
    // Bind a VARCHAR, a TIMESTAMP_LTZ, a numeric to our INSERT statement.
    result = snowflake.execute(
        { 
        sqlText: sql_command, 
        binds: [TSV, my_sfDate, f, f, f, my_sfDate, my_sfDate, '12:30:00.123' ] 
        }
        ); 

    return my_sfDate; 
$$ ; 

Copy

プロシージャを呼び出します。

CALL string_to_timestamp_ltz('2008-11-18 16:00:00');
+-------------------------------+
| STRING_TO_TIMESTAMP_LTZ       |
|-------------------------------|
| 2008-11-18 16:00:00.000 -0800 |
+-------------------------------+

Copy

行が挿入されたことを確認します。

SELECT * FROM table1;
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
| V                   | TS1                           | INT1 |   FLOAT1 |    NUMERIC1 | TS_NTZ1                 | DATE1      | TIME1    |
|---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------|
| 2008-11-18 16:00:00 | 2008-11-18 16:00:00.000 -0800 |    3 | 3.141593 | 3.141593000 | 2008-11-18 16:00:00.000 | 2008-11-18 | 12:30:00 |
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+

Copy

JavaScriptのデータバインドのその他の例については、ステートメントパラメーターのバインドをご参照ください。

コード要件¶

ストアドプロシージャを有効にするには、 JavaScript コードで単一のリテラル JavaScript オブジェクトを定義する必要があります。

JavaScript コードがこの要件を満たさない場合、ストアドプロシージャが作成されます。ただし、呼び出されると失敗します。

コードサイズ¶

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

ランタイムエラー¶

ストアドプロシージャのほとんどのエラーは、実行時に表示されます。これは、 JavaScript コードが、ストアドプロシージャの作成時ではなく、ストアドプロシージャの実行時に解釈されるためです。

動的な SQLのサポート¶

ストアドプロシージャを使用して、 SQL ステートメントを動的に構築できます。例えば、事前に構成された SQL とユーザー入力（ユーザーのアカウント番号など）が混在する SQL コマンド文字列を作成できます。

例については、 SQL ステートメントの動的な作成および例セクションをご参照ください。

同期 API¶

Snowflakeストアドプロシージャの API は同期的です。ストアドプロシージャ内では、一度に1つのスレッドのみを実行できます。

これは、非同期スレッドを実行できるNode.jsコネクタで実行する JavaScript の規則とは異なります。

例¶

基本的な例¶

次の例は、ストアドプロシージャを作成して呼び出す基本的な構文を示しています。SQL または手続き型コードは実行されません。ただし、後でより現実的な例の開始点を提供します。

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;
Copy
$$ 区切り文字は JavaScript コードの開始と終了を示します。

次に、作成したばかりのプロシージャを呼び出します。
CALL sp_pi();
+-----------+
|     SP_PI |
|-----------|
| 3.1415926 |
+-----------+
Copy

次の例は、ストアドプロシージャ内で SQL ステートメントを実行する方法を示しています。

テーブルを作成します。

CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));

Copy

ストアドプロシージャを作成します。これにより、 stproc_test_table1 という名前の既存テーブルに行が挿入され、「成功」の値が返されます。返される値は SQL の観点からは特に有用ではありませんが、ユーザーにステータス情報を返すことができます（「成功」または「失敗」など）。

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.
        }
    $$
    ;

Copy

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

call stproc1(5.14::FLOAT);
+------------+
| STPROC1    |
|------------|
| Succeeded. |
+------------+

Copy

ストアドプロシージャによって行が挿入されたことを確認します。

select * from stproc_test_table1;
+-----------+
|  NUM_COL1 |
|-----------|
| 5.1400000 |
+-----------+

Copy

次の例では、結果を取得します。

テーブル内の行数をカウントするプロシージャを作成します（select count(*) from table と同等）:

create or replace procedure get_row_count(table_name VARCHAR)
  returns float not null
  language javascript
  as
  $$
  var row_count = 0;
  // Dynamically compose the SQL statement to execute.
  var sql_command = "select count(*) from " + TABLE_NAME;
  // Run the statement.
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();
  // Get back the row count. Specifically, ...
  // ... get the first (and in this case only) row from the result set ...
  res.next();
  // ... and then get the returned value, which in this case is the number of
  // rows in the table.
  row_count = res.getColumnValue(1);
  return row_count;
  $$
  ;

Copy

テーブル内の行数をストアドプロシージャに問い合わせます。

call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+

Copy

正しい番号を取得していることを個別に確認します。

select count(*) from stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Copy

再帰ストアドプロシージャの例¶

次の例は、基本的ですが、それほど現実的ではない再帰ストアドプロシージャを示しています。

create or replace table stproc_test_table2 (col1 FLOAT);

Copy

create or replace procedure recursive_stproc(counter FLOAT)
    returns varchar not null
    language javascript
    as
    -- "$$" is the delimiter that shows the beginning and end of the stored proc.
    $$
    var counter1 = COUNTER;
    var returned_value = "";
    var accumulator = "";
    var stmt = snowflake.createStatement(
        {
        sqlText: "INSERT INTO stproc_test_table2 (col1) VALUES (?);",
        binds:[counter1]
        }
        );
    var res = stmt.execute();
    if (COUNTER > 0)
        {
        stmt = snowflake.createStatement(
            {
            sqlText: "call recursive_stproc (?);",
            binds:[counter1 - 1]
            }
            );
        res = stmt.execute();
        res.next();
        returned_value = res.getColumnValue(1);
        }
    accumulator = accumulator + counter1 + ":" + returned_value;
    return accumulator;
    $$
    ;

Copy

call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+

Copy

SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+

Copy

SQL ステートメントの動的な作成¶

次の例は、 SQL ステートメントを動的に作成する方法を示しています。

注釈

このトピック内の SQL インジェクションで述べたように、動的な SQLを使用するときは攻撃に注意してください。

ストアドプロシージャを作成します。このプロシージャを使用すると、テーブルの名前を渡し、そのテーブルの行数を取得できます（select count(*) from table_name と同等）。

create or replace procedure get_row_count(table_name VARCHAR)
    returns float 
    not null
    language javascript
    as
    $$
    var row_count = 0;
    // Dynamically compose the SQL statement to execute.
    // Note that we uppercased the input parameter name.
    var sql_command = "select count(*) from " + TABLE_NAME;
    // Run the statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );
    var res = stmt.execute();
    // Get back the row count. Specifically, ...
    // ... first, get the first (and in this case only) row from the
    //  result set ...
    res.next();
    // ... then extract the returned value (which in this case is the
    // number of rows in the table).
    row_count = res.getColumnValue(1);
    return row_count;
    $$
    ;

Copy

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

call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+

Copy

同じテーブルの select count(*) からの結果を表示します。

SELECT COUNT(*) FROM stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Copy

結果セットのメタデータの取得¶

この例は、結果セットから少量のメタデータを取得する方法を示しています。

create or replace table stproc_test_table3 (
    n10 numeric(10,0),     /* precision = 10, scale = 0 */
    n12 numeric(12,4),     /* precision = 12, scale = 4 */
    v1 varchar(19)         /* scale = 0 */
    );

Copy

create or replace procedure get_column_scale(column_index float)
    returns float not null
    language javascript
    as
    $$
    var stmt = snowflake.createStatement(
        {sqlText: "select n10, n12, v1 from stproc_test_table3;"}
        );
    stmt.execute();  // ignore the result set; we just want the scale.
    return stmt.getColumnScale(COLUMN_INDEX); // Get by column index (1-based)
    $$
    ;

Copy

call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Copy

call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+

Copy

call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Copy

Try/Catchを使用したエラーのキャッチ¶

この例では、 JavaScript try/catchブロックを使用して、ストアドプロシージャ内のエラーをキャッチします。

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

    create procedure broken()
      returns varchar not null
      language javascript
      as
      $$
      var result = "";
      try {
          snowflake.execute( {sqlText: "Invalid Command!;"} );
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
      $$
      ;

Copy

ストアドプロシージャを呼び出します。これにより、エラー番号とその他の情報を示すエラーが返されます。

    -- This is expected to fail.
    call broken();
+---------------------------------------------------------+
| BROKEN                                                  |
|---------------------------------------------------------|
| Failed: Code: 1003                                      |
|   State: 42000                                          |
|   Message: SQL compilation error:                       |
| syntax error line 1 at position 0 unexpected 'Invalid'. |
| Stack Trace:                                            |
| Snowflake.execute, line 4 position 20                   |
+---------------------------------------------------------+

Copy

次の例は、カスタム例外のスローを示しています。

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

CREATE OR REPLACE PROCEDURE validate_age (age float)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    try {
        if (AGE < 0) {
            throw "Age cannot be negative!";
        } else {
            return "Age validated.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;

Copy

有効な値と無効な値を使用してストアドプロシージャを呼び出します。

CALL validate_age(50);
+----------------+
| VALIDATE_AGE   |
|----------------|
| Age validated. |
+----------------+
CALL validate_age(-2);
+--------------------------------+
| VALIDATE_AGE                   |
|--------------------------------|
| Error: Age cannot be negative! |
+--------------------------------+

Copy

ストアドプロシージャでのトランザクションの使用¶

次の例では、トランザクション内の複数の関連ステートメントをラップし、try/catchを使用してコミットまたはロールバックします。パラメーター force_failure を使用すると、呼び出し元は実行の成功と意図的なエラーのどちらかを選択できます。

-- Create the procedure
create or replace procedure cleanup(force_failure varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "BEGIN WORK;"} );
  try {
      snowflake.execute( {sqlText: "DELETE FROM child;"} );
      snowflake.execute( {sqlText: "DELETE FROM parent;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "DELETE FROM no_such_table;"} );
          }
      snowflake.execute( {sqlText: "COMMIT WORK;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "ROLLBACK WORK;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

call cleanup('fail');

call cleanup('do not fail');

Copy

エラーのログ¶

JavaScript API の snowflake オブジェクトを使用すると、 JavaScript ハンドラーコードからログとトレースデータをキャプチャできます。これを実行すると、ログメッセージとトレースデータがイベントテーブルに格納され、クエリで分析できます。

詳細については、以下をご参照ください。

RESULT_SCAN を使用したストアドプロシージャからの結果の取得¶

この例は、 RESULT_SCAN 関数を使用して CALL ステートメントから結果を取得する方法を示しています。

テーブルを作成してロードします。

CREATE TABLE western_provinces(ID INT, province VARCHAR);

Copy

INSERT INTO western_provinces(ID, province) VALUES
    (1, 'Alberta'),
    (2, 'British Columbia'),
    (3, 'Manitoba')
    ;

Copy

ストアドプロシージャを作成します。このプロシージャは、3行の結果セットのように見える適切にフォーマットされた文字列を返しますが、実際は単一の文字列です。

CREATE OR REPLACE PROCEDURE read_western_provinces()
  RETURNS VARCHAR NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var return_value = "";
  try {
      var command = "SELECT * FROM western_provinces ORDER BY province;"
      var stmt = snowflake.createStatement( {sqlText: command } );
      var rs = stmt.execute();
      if (rs.next())  {
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      while (rs.next())  {
          return_value += "\n";
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      }
  catch (err)  {
      result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt;
      }
  return return_value;
  $$
  ;

Copy

ストアドプロシージャを呼び出してから、 RESULT_SCAN を使用して結果を取得します。

CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+

Copy

RESULT_SCAN 関数によって返された値に対して、より複雑な操作を実行できます。この場合、返される値は単一の文字列であるため、その文字列に含まれているように見える個々の「行」を抽出し、それらの行を別のテーブルに保存することができます。

前の例の続きである次の例は、これを行う1つの方法を示しています。

長期保存用のテーブルを作成します。このテーブルには、 CALL コマンドによって返された文字列から抽出した後の州名と州 ID が含まれます。
CREATE TABLE all_provinces(ID INT, province VARCHAR);
Copy

ストアドプロシージャを呼び出し、 RESULT_SCAN を使用して結果を取得し、文字列から3行を抽出して、それらの行をテーブルに配置します。

INSERT INTO all_provinces
  WITH 
    one_string (string_col) AS
      (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))),
    three_strings (one_row) AS
      (SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n'))
  SELECT
         STRTOK(one_row, ',', 1) AS ID,
         STRTOK(one_row, ',', 2) AS province
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

Copy

テーブルに行を表示して、これが機能したことを確認します。

SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+

Copy

これはほぼ同じコードですが、手順は次のとおりです。

one_string という名前のテーブルを作成します。このテーブルは、 CALL コマンドの結果を一時的に保存します。 CALL の結果は単一の文字列なので、このテーブルには単一の VARCHAR 値のみが保存されます。
CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
Copy

ストアドプロシージャを呼び出し、 RESULT_SCANを使用して結果（文字列）を取得し、それを one_string という名前の中間テーブルに保存します。

CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
INSERT INTO one_string
    SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

Copy

これにより、 one_string テーブルの新しい行が表示されます。これは3行のようにフォーマットされていますが、実際には単一の文字列です。

SELECT string_col FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
-- Show that it's one string, not three rows:
SELECT '>>>' || string_col || '<<<' AS string_col 
    FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| >>>1, Alberta       |
| 2, British Columbia |
| 3, Manitoba<<<      |
+---------------------+
SELECT COUNT(*) FROM one_string;
+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

Copy

次のコマンドは、文字列から複数の行を抽出する方法を示しています。

SELECT * FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+-----+-------+---------------------+
| STRING_COL          | SEQ | INDEX | VALUE               |
|---------------------+-----+-------+---------------------|
| 1, Alberta          |   1 |     1 | 1, Alberta          |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     2 | 2, British Columbia |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     3 | 3, Manitoba         |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
+---------------------+-----+-------+---------------------+
SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+
| VALUE               |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+

Copy

次に、 three_strings という名前のテーブルを作成します。このテーブルは、個々の行/文字列に分割した後、結果を保持します。
CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
Copy

one_string テーブルの1つの文字列を3つの個別の文字列に変換し、実際に3つの文字列であることを示します。

INSERT INTO three_strings
  SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT string_col 
    FROM three_strings;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
SELECT COUNT(*) 
    FROM three_strings;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Copy

ここで、 all_provinces という名前の長期テーブルで3つの文字列を3つの行に変換します。

INSERT INTO all_provinces
  SELECT 
         STRTOK(string_col, ',', 1) AS ID, 
         STRTOK(string_col, ',', 2) AS province 
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

Copy

長期テーブルの3行を表示します。

SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+
SELECT COUNT(*) 
    FROM all_provinces;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Copy

エラーメッセージの配列を返す¶

ストアドプロシージャは複数の SQL ステートメントを実行する可能性があり、各 SQL ステートメントに対してステータス/エラーメッセージを返すことができます。ただし、ストアドプロシージャは単一の行を返します。複数の行を返すようには設計されていません。

すべてのメッセージが ARRAY型の単一の値に収まる場合、追加の労力でストアドプロシージャからすべてのメッセージを取得できます。

次の例は、これを行う1つの方法を示しています（表示されるエラーメッセージは実際のものではありませんが、実際の SQL ステートメントで動作するようにこのコードを拡張できます）。

CREATE OR REPLACE PROCEDURE sp_return_array()
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This array will contain one error message (or an empty string) 
      // for each SQL command that we executed.
      var array_of_rows = [];

      // Artificially fake the error messages.
      array_of_rows.push("ERROR: The foo was barred.")
      array_of_rows.push("WARNING: A Carrington Event is predicted.")

      return array_of_rows;
      $$
      ;

Copy

CALL sp_return_array();
+-----------------------------------------------+
| SP_RETURN_ARRAY                               |
|-----------------------------------------------|
| [                                             |
|   "ERROR: The foo was barred.",               |
|   "WARNING: A Carrington Event is predicted." |
| ]                                             |
+-----------------------------------------------+
-- Now get the individual error messages, in order.
SELECT INDEX, VALUE 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(INPUT => res.$1)
    ORDER BY index
    ;
+-------+---------------------------------------------+
| INDEX | VALUE                                       |
|-------+---------------------------------------------|
|     0 | "ERROR: The foo was barred."                |
|     1 | "WARNING: A Carrington Event is predicted." |
+-------+---------------------------------------------+

Copy

これは、汎用ソリューションではありません。ARRAY データ型の最大サイズには制限があり、結果セット全体が単一の ARRAY に収まる必要があります。

結果セットを返す¶

このセクションでは、エラーメッセージの配列を返すで説明した例について詳しく説明します。この例はより一般的であり、クエリから結果セットを返すことができます。

ストアドプロシージャは、単一の列を含む単一の行を返します。結果セットを返すようには設計されていません。ただし、結果セットが VARIANT 型または ARRAY の単一の値に収まるほど小さい場合は、追加のコードを使用してストアドプロシージャから結果セットを返すことができます。

CREATE TABLE return_to_me(col_i INT, col_v VARCHAR);
INSERT INTO return_to_me (col_i, col_v) VALUES
    (1, 'Ariel'),
    (2, 'October'),
    (3, NULL),
    (NULL, 'Project');

Copy

-- Create the stored procedure that retrieves a result set and returns it.
CREATE OR REPLACE PROCEDURE sp_return_table(TABLE_NAME VARCHAR, COL_NAMES ARRAY)
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This variable will hold a JSON data structure that holds ONE row.
      var row_as_json = {};
      // This array will contain all the rows.
      var array_of_rows = [];
      // This variable will hold a JSON data structure that we can return as
      // a VARIANT.
      // This will contain ALL the rows in a single "value".
      var table_as_json = {};

      // Run SQL statement(s) and get a resultSet.
      var command = "SELECT * FROM " + TABLE_NAME;
      var cmd1_dict = {sqlText: command};
      var stmt = snowflake.createStatement(cmd1_dict);
      var rs = stmt.execute();

      // Read each row and add it to the array we will return.
      var row_num = 1;
      while (rs.next())  {
        // Put each row in a variable of type JSON.
        row_as_json = {};
        // For each column in the row...
        for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
          var col_name = COL_NAMES[col_num];
          row_as_json[col_name] = rs.getColumnValue(col_num + 1);
          }
        // Add the row to the array of rows.
        array_of_rows.push(row_as_json);
        ++row_num;
        }
      // Put the array in a JSON variable (so it looks like a VARIANT to
      // Snowflake).  The key is "key1", and the value is the array that has
      // the rows we want.
      table_as_json = { "key1" : array_of_rows };

      // Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
      return table_as_json;
      $$
      ;

Copy

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
-- Use "ResultScan" to get the data from the stored procedure that
-- "did not return a result set".
-- Use "$1:key1" to get the value corresponding to the JSON key named "key1".
SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| $1:KEY1                |
|------------------------|
| [                      |
|   {                    |
|     "COL_I": 1,        |
|     "COL_V": "Ariel"   |
|   },                   |
|   {                    |
|     "COL_I": 2,        |
|     "COL_V": "October" |
|   },                   |
|   {                    |
|     "COL_I": 3,        |
|     "COL_V": null      |
|   },                   |
|   {                    |
|     "COL_I": null,     |
|     "COL_V": "Project" |
|   }                    |
| ]                      |
+------------------------+
-- Now get what we really want.
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(input => res.$1)
  ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

Copy

これは、前の2行を1行に結合する方法を示しています。

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
       FROM (SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res,
            LATERAL FLATTEN(input => res.$1)
       ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

Copy

便宜上、前の行をビューで折り返すことができます。このビューは、文字列「null」を真の NULL に変換します。ビューを作成する必要があるのは一度だけです。ただし、ビューを使用する際は毎回、このビューから選択する直前にストアドプロシージャを呼び出す必要があります。ビュー内の RESULT_SCAN の呼び出しは、最新のステートメントから取得しています。これは CALL でなければなりません。

CREATE VIEW stproc_view (col_i, col_v) AS 
  SELECT NULLIF(VALUE:COL_I::VARCHAR, 'null'::VARCHAR), 
         NULLIF(value:COL_V::VARCHAR, 'null'::VARCHAR)
    FROM (SELECT $1:key1 AS tbl FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res, 
         LATERAL FLATTEN(input => res.tbl);

Copy

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT * 
    FROM stproc_view
    ORDER BY COL_I;
+-------+---------+
| COL_I | COL_V   |
|-------+---------|
| 1     | Ariel   |
| 2     | October |
| 3     | NULL    |
| NULL  | Project |
+-------+---------+

Copy

真のビューとして使用することもできます（そのサブセットを選択）。

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT COL_V 
    FROM stproc_view
    WHERE COL_V IS NOT NULL
    ORDER BY COL_V;
+---------+
| COL_V   |
|---------|
| Ariel   |
| October |
| Project |
+---------+

Copy

これは、汎用ソリューションではありません。VARIANT および ARRAY データ型の最大サイズには制限があり、結果セット全体が単一の VARIANT または ARRAY に収まる必要があります。

プライバシーの保護¶

この例は、オンライン小売業者に役立つストアドプロシージャを示しています。このストアドプロシージャは、小売業者と顧客の両方の正当な利益を保護しながら、顧客のプライバシーを尊重します。顧客が小売業者にプライバシー上の理由で顧客データを削除するように依頼した場合、このストアドプロシージャは顧客データのほとんどを削除しますが、次のいずれかが当てはまる場合は顧客の購入履歴を残します。

購入したアイテムに、まだ期限が切れていない保証が付いている場合。
顧客が未払いの（または顧客に払い戻しをしている）場合。

これのより現実的なバージョンでは、支払いが行われ保証が期限切れになった個別の行を削除します。

最初に、テーブルを作成してロードします。

create table reviews (customer_ID VARCHAR, review VARCHAR);
create table purchase_history (customer_ID VARCHAR, price FLOAT, paid FLOAT,
                               product_ID VARCHAR, purchase_date DATE);

Copy

insert into purchase_history (customer_ID, price, paid, product_ID, purchase_date) values 
    (1, 19.99, 19.99, 'chocolate', '2018-06-17'::DATE),
    (2, 19.99,  0.00, 'chocolate', '2017-02-14'::DATE),
    (3, 19.99,  19.99, 'chocolate', '2017-03-19'::DATE);

insert into reviews (customer_ID, review) values (1, 'Loved the milk chocolate!');
insert into reviews (customer_ID, review) values (2, 'Loved the dark chocolate!');

Copy

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

create or replace procedure delete_nonessential_customer_data(customer_ID varchar)
    returns varchar not null
    language javascript
    as
    $$

    // If the customer posted reviews of products, delete those reviews.
    var sql_cmd = "DELETE FROM reviews WHERE customer_ID = " + CUSTOMER_ID;
    snowflake.execute( {sqlText: sql_cmd} );

    // Delete any other records not needed for warranty or payment info.
    // ...

    var result = "Deleted non-financial, non-warranty data for customer " + CUSTOMER_ID;

    // Find out if the customer has any net unpaid balance (or surplus/prepayment).
    sql_cmd = "SELECT SUM(price) - SUM(paid) FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    var net_amount_owed = rs.getColumnValue(1);

    // Look up the number of purchases still under warranty...
    var number_purchases_under_warranty = 0;
    // Assuming a 1-year warranty...
    sql_cmd = "SELECT COUNT(*) FROM purchase_history ";
    sql_cmd += "WHERE customer_ID = " + CUSTOMER_ID;
    // Can't use CURRENT_DATE() because that changes. So assume that today is 
    // always June 15, 2019.
    sql_cmd += "AND PURCHASE_DATE > dateadd(year, -1, '2019-06-15'::DATE)";
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    number_purchases_under_warranty = rs.getColumnValue(1);

    // Check whether need to keep some purchase history data; if not, then delete the data.
    if (net_amount_owed == 0.0 && number_purchases_under_warranty == 0)  {
        // Delete the purchase history of this customer ...
        sql_cmd = "DELETE FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
        snowflake.execute( {sqlText: sql_cmd} );
        // ... and delete anything else that that should be deleted.
        // ...
        result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
        }
    return result;
    $$
    ;

Copy

そのデータを削除する前に、テーブル内のデータを表示します。

SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 1           | Loved the milk chocolate! |
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

Copy

顧客1には、まだ有効な保証があります。ストアドプロシージャは、投稿したレビューコメントを削除しますが、保証の購入記録を保持します。

call delete_nonessential_customer_data(1);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 1 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

Copy

顧客2には、未払い金があります。ストアドプロシージャはレビューコメントを削除しますが、購入記録を保持します。

call delete_nonessential_customer_data(2);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 2 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

Copy

顧客3には未払金がありません。（払い戻しもありません）。保証の期限が切れたため、ストアドプロシージャはレビューコメントと購入記録の両方を削除します。

call delete_nonessential_customer_data(3);
+-------------------------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                                       |
|-------------------------------------------------------------------------|
| Deleted all data, including financial and warranty data, for customer 3 |
+-------------------------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
+-------------+-------+-------+------------+---------------+

Copy

呼び出し元権限と所有者権限のストアドプロシージャでのセッション変数の使用¶

これらの例は、呼び出し元権限と所有者権限のストアドプロシージャの主な違いの1つを示しています。例では、セッション変数を次の2つの方法で使用しようとします。

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

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

呼び出し元権限ストアドプロシージャ¶

次の例は、呼び出し元権限ストアドプロシージャを示しています。

テーブルを作成してロードします。

create table sv_table (f float);
insert into sv_table (f) values (49), (51);

Copy

セッション変数を設定します。
set SESSION_VAR1 = 50;
Copy

1つのセッション変数を使用し、別のセッション変数を設定する呼び出し元権限ストアドプロシージャを作成します。

create procedure session_var_user()
  returns float
  language javascript
  EXECUTE AS CALLER
  as
  $$
  // Set the second session variable
  var stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  var rs = stmt.execute();  // we ignore the result in this case
  // Run a query using the first session variable
  stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  rs = stmt.execute();
  rs.next();
  var output = rs.getColumnValue(1);
  return output;
  $$
  ;

Copy

プロシージャを呼び出します。

CALL session_var_user();
+------------------+
| SESSION_VAR_USER |
|------------------|
|               51 |
+------------------+

Copy

ストアドプロシージャ内で設定されたセッション変数の値を表示します。

SELECT $SESSION_VAR2;
+------------------------------+
| $SESSION_VAR2                |
|------------------------------|
| I was set inside the StProc. |
+------------------------------+

Copy

注釈

ストアドプロシージャ内でセッション変数を設定し、プロシージャの終了後に設定したままにしておくことはできますが、Snowflakeはそれは推奨しません。

所有者権限ストアドプロシージャ¶

次の例は、所有者権限ストアドプロシージャを示しています。

セッション変数を使用する所有者権限ストアドプロシージャを作成します。

create procedure cannot_use_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Run a query using the first session variable
  var stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  var rs = stmt.execute();
  rs.next();
  var output = rs.getColumnValue(1);
  return output;
  $$
  ;

Copy

プロシージャを呼び出します（失敗するはずです）。
CALL cannot_use_session_vars();
Copy

セッション変数を設定しようとする所有者権限ストアドプロシージャを作成します。

create procedure cannot_set_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Set the second session variable
  var stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  var rs = stmt.execute();  // we ignore the result in this case
  return 3.0;   // dummy value.
  $$
  ;

Copy

プロシージャを呼び出します（失敗するはずです）。
CALL cannot_set_session_vars();
Copy

トラブルシューティング¶

一般的なトラブルシューティングの手法は、JavaScript try/catchブロックを使用してエラーをキャッチし、エラー情報を表示することです。エラーオブジェクトには次が含まれます。

エラーコード。
エラーメッセージ。
エラーの状態。
障害点でのスタックトレース。

この情報の使用方法の例を含む詳細については、このトピック内の Try/Catchを使用したエラーのキャッチをご参照ください。

次のセクションでは、特定の問題のデバッグに役立つ追加の提案について説明します。

ストアドプロシージャまたは UDF が予期しない NULL を返す¶

原因

ストアドプロシージャ/UDF にはパラメーターがあり、プロシージャ/UDF内では、パラメーターは小文字の名前で参照されますが、Snowflakeは名前を自動的に大文字に変換しました。

解決策

次のいずれかを実行します。

JavaScript コード内の変数名に大文字を使用します。
SQL コードで変数名を二重引用符で囲みます。

詳細については、 JavaScript 引数と戻り値をご参照ください。

ストアドプロシージャの実行が終了しない¶

原因: JavaScript コードに無限ループがある可能性があります。
解決策: 無限ループを確認して修正します。

エラー: `Failed: empty argument passed`¶

原因: ストアドプロシージャに含まれるべき「sqlText」が「sqltext」になっている可能性があります（1番目は大文字と小文字が混在、2番目はすべて小文字）。
解決策: 「sqlText」を使用します。

エラー： `JavaScript out of memory error: UDF thread memory limit exceeded`¶

原因: JavaScript コードに無限ループがある可能性があります。
解決策: 無限ループを確認して修正します。特に、結果セットがなくなったら（ resultSet.next() が false を返すと）次の行の呼び出しを停止してください。

JavaScript でのストアドプロシージャの記述¶

JavaScript API を理解する¶

SQL および JavaScript データ型マッピング¶

一般的なヒント¶

行の継続¶

JavaScript ストアドプロシージャの考慮事項¶

JavaScript 数値範囲¶

JavaScript のエラー処理¶

ストアドプロシージャの制限¶

JavaScript 引数の大文字と小文字の区別¶

JavaScript 引数の大文字と小文字の区別¶

JavaScript 区切り記号¶

ストアドプロシージャ名のオーバーロード¶

変数のバインド¶

コード要件¶

コードサイズ¶

ランタイムエラー¶

動的な SQLのサポート¶

同期 API¶

例¶

基本的な例¶

再帰ストアドプロシージャの例¶

SQL ステートメントの動的な作成¶

結果セットのメタデータの取得¶

Try/Catchを使用したエラーのキャッチ¶

ストアドプロシージャでのトランザクションの使用¶

エラーのログ¶

RESULT_SCAN を使用したストアドプロシージャからの結果の取得¶

エラーメッセージの配列を返す¶

結果セットを返す¶

プライバシーの保護¶

呼び出し元権限と所有者権限のストアドプロシージャでのセッション変数の使用¶

呼び出し元権限ストアドプロシージャ¶

所有者権限ストアドプロシージャ¶

トラブルシューティング¶

ストアドプロシージャまたは UDF が予期しない NULL を返す¶

ストアドプロシージャの実行が終了しない¶

エラー: Failed: empty argument passed¶

エラー： JavaScript out of memory error: UDF thread memory limit exceeded¶

エラー: `Failed: empty argument passed`¶

エラー： `JavaScript out of memory error: UDF thread memory limit exceeded`¶