EXCEPTION (Snowflake Scripting)¶

指定如何处理 Snowflake Scripting 块中引发的异常。

For more information on exceptions, see Handling exceptions.

See also:

RAISE

语法

EXCEPTION
    WHEN <exception_name> [ OR <exception_name> ... ] [ { EXIT | CONTINUE } ] THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ WHEN OTHER [ { EXIT | CONTINUE } ] THEN ]
        <statement>;
        [ <statement>; ... ]

其中：

exception_name

An exception name defined in the DECLARE portion of the current block, or in an enclosing block.

statement

A statement can be any of the following:

• A single SQL statement (including CALL).
• A control-flow statement (for example, a looping or branching statement).
• A nested block.

使用说明

• Each block can have its own exception handler.

• Snowflake supports no more than one exception handler per block. However, that handler can catch more than one type of exception by having more than one WHEN clause.

• The WHEN OTHER [ { EXIT | CONTINUE } ] THEN clause catches any exception not yet specified.

• An exception handler applies to statements between the BEGIN and EXCEPTION sections of the block in which it is declared. It does’t apply to the DECLARE section of the block.

• An exception handler can handle a specified exception only if that specified exception is in scope.

• If a stored procedure is intended to return a value, then it should return a value from each possible exit path, including each WHEN clause of EXIT type in the exception handler.

• To use a variable in an exception handler, the variable must be declared in the DECLARE section or passed as an argument to a stored procedure. It can’t be declared in the BEGIN … END section. For more information, see Passing variables to an exception handler in Snowflake Scripting.

• When an exception occurs, the handler conditions are checked in order and the first WHEN clause that matches is used. The order within a block is top to bottom, and the inner blocks are checked before the outer blocks. There is no preference in matching EXIT or CONTINUE handlers, whichever matches first is used.

• Only one handler can be matched for a statement. However, any exceptions encountered inside of an exception handler body can trigger outer block exception handlers.

• Each WHEN clause in an exception handler can be one of the following types:

  • EXIT - The block runs the statements in the handler and then exits the current block. If the block runs an exception of this type, and the block contains statements after the exception handler, those statements aren’t run.

    If the block is an inner block, and the exception handler doesn’t contain a RETURN statement, then execution exits the inner block and continues with the code in the outer block.

    EXIT is the default.

  • CONTINUE - The block executes the statements in the handler and continues with the statement immediately following the one that caused the error.

  An EXCEPTION clause can have WHEN clauses of both types — EXIT and CONTINUE.

  For a WHEN clause of the CONTINUE type, the following usage notes apply:

  • If an error is raised in a branching construct, then the continuing statement is the statement immediately after the branching construct.
  • If an error is raised in the condition of a loop, then the continuing statement is the statement immediately after the loop.
  • If an error is raised in the body of a loop, then the continuing statement is the statement in the next iteration of the loop. For an example, see 处理异常并继续.
  • If an error is raised in a RETURN statement, then the continuing statement is the statement immediately after the RETURN statement.
  • If an error is raised in a nested stored procedure and the error is handled by the outer scope, then the continuing statement is the statement immediately after the stored procedure call.
  • Avoid including a RETURN statement in a WHEN clause of the CONTINUE type. If you include a RETURN statement, then the stored procedure returns without continuing.

  For a WHEN clause of the CONTINUE type, the following examples show which statement is the statement immediately following the one that caused the error for different scenarios. In these examples, the error_expression is the expression that raised the exception, and the continue_statement is the statement that the code continues with in the block after the CONTINUE handler statements.

  Copy codeExpand code block

  DECLARE
    ...
  BEGIN
    ...
    LET a := <error_expression>;
    <continue_statement>;
    ...
  EXCEPTION
    WHEN <exception_name> CONTINUE THEN
   ...
  END;

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  LET x := <valid_expression>;
  x := <error_expression>;
  <continue_statement>

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  SELECT <statement> INTO <error_expression>;
  <continue_statement>;

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  IF (<error_expression>) THEN
    <statement>
  ELSEIF (<valid_expression>) THEN
    <statement>
  ELSE
    <statement>
  END IF;
  <continue_statement>;

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  CASE (<error_expression>)
    WHEN (<valid_expression>) THEN
   <statement>
    ELSE
   <statement>
  END CASE;
  <continue_statement>

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  CASE (<valid_expression>)
    WHEN (<error_expression>) THEN
   <statement>
    WHEN (<valid_expression>) THEN
   <statement>
    ELSE
   <statement>
  END CASE;
  <continue_statement>

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  FOR i IN <valid_expression> TO <error_expression> DO
    <statement>
  END FOR
  <continue_statement>

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  WHILE <error_expression> DO
    <statement>
  END WHILE;
  <continue_statement>

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  REPEAT
    <statement>
  UNTIL <error_expression>;
  <continue_statement>

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  RETURN <error_expression>;
  <continue_statement>

  Show lessSee more

  Scroll to top
  Copy codeExpand code block

  DECLARE
    x int := 0;
    myproc PROCEDURE()
   RETURNS STRING
   AS BEGIN
     x := <error_expression>;
     <statement>
   END;
  BEGIN
    CALL myproc();
    <continue_statement>
    ...
  END;

  Show lessSee more

  Scroll to top

示例

以下示例展示了如何声明和触发异常，以及如何使用异常处理程序处理这些异常：

• 处理多种类型的异常
• 处理异常并继续
• 在嵌套块中处理异常
• 处理同一子句中的多种异常及未指定异常
• 使用内置变量处理异常

处理多种类型的异常

以下示例展示了一个设计用于处理多种类型异常的异常处理程序：

DECLARE
  result VARCHAR;
  exception_1 EXCEPTION (-20001, 'I caught the expected exception.');
  exception_2 EXCEPTION (-20002, 'Not the expected exception!');
BEGIN
  result := 'If you see this, I did not catch any exception.';
  IF (TRUE) THEN
    RAISE exception_1;
  END IF;
  RETURN result;
EXCEPTION
  WHEN exception_2 THEN
    RETURN SQLERRM;
  WHEN exception_1 THEN
    RETURN SQLERRM;
END;

EXECUTE IMMEDIATE $$
DECLARE
  result VARCHAR;
  exception_1 EXCEPTION (-20001, 'I caught the expected exception.');
  exception_2 EXCEPTION (-20002, 'Not the expected exception!');
BEGIN
  result := 'If you see this, I did not catch any exception.';
  IF (TRUE) THEN
    RAISE exception_1;
  END IF;
  RETURN result;
EXCEPTION
  WHEN exception_2 THEN
    RETURN SQLERRM;
  WHEN exception_1 THEN
    RETURN SQLERRM;
END;
$$;

该输出表明异常处理程序已捕获异常：

+----------------------------------+
| anonymous block                  |
%----------------------------------%
| I caught the expected exception. |
+----------------------------------+

处理异常并继续

The following example shows an exception handler with a WHEN clause of the CONTINUE type:

DECLARE
  exception_1 EXCEPTION (-20001, 'Catch and continue');
BEGIN
  LET counter := 0;
  IF (TRUE) THEN
    RAISE exception_1;
  END IF;
  counter := counter + 10;
  RETURN 'Counter value: ' || counter;
EXCEPTION
  WHEN exception_1 CONTINUE THEN
    counter := counter +1;
END;

EXECUTE IMMEDIATE $$
DECLARE
  exception_1 EXCEPTION (-20001, 'Catch and continue');
BEGIN
  LET counter := 0;
  IF (TRUE) THEN
    RAISE exception_1;
  END IF;
  counter := counter + 10;
  RETURN 'Counter value: ' || counter;
EXCEPTION
  WHEN exception_1 CONTINUE THEN
    counter := counter +1;
END;
$$;

The output shows that the exception handler caught the exception, executed a statement that added 1 to the counter, and then executed the next statement after the exception was caught, which added 10 to the counter:

+-------------------+
| anonymous block   |
%-------------------%
| Counter value: 11 |
+-------------------+

The following example shows how an exception handler with a WHEN clause of the CONTINUE type works when an error is raised in a loop. The example raises an error on the first iteration because it tries to divide the value 10 by zero. The CONTINUE handler logs the error in the error_log_table, and the block continues with the next iteration of the loop, which divides 10 by 1. The loop continues to iterate until 10 is divided by 5 and the loop ends. The output is 2:

CREATE TABLE error_log_table (handler_type VARCHAR, error_message VARCHAR);

DECLARE
  x INT := 0;
BEGIN
  FOR i IN 0 TO 5 DO
    x := 10/i;
  END FOR;
  RETURN x;
EXCEPTION
  WHEN EXPRESSION_ERROR CONTINUE THEN
    INSERT INTO error_log_table SELECT 'continue_type', :SQLERRM;
END;

CREATE TABLE error_log_table (handler_type VARCHAR, error_message VARCHAR);

EXECUTE IMMEDIATE $$
DECLARE
  x INT := 0;
BEGIN
  FOR i IN 0 TO 5 DO
    x := 10/i;
  END FOR;
  RETURN x;
EXCEPTION
  WHEN EXPRESSION_ERROR CONTINUE THEN
    INSERT INTO error_log_table SELECT 'continue_type', :SQLERRM;
END;
$$;

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

在嵌套块中处理异常

以下示例展示了嵌套块，并说明内部块可以引发在内部块或外部块中声明的异常：

DECLARE
  e1 EXCEPTION (-20001, 'Exception e1');
BEGIN
  -- Inner block.
  DECLARE
    e2 EXCEPTION (-20002, 'Exception e2');
    selector BOOLEAN DEFAULT TRUE;
  BEGIN
    IF (selector) THEN
      RAISE e1;
    ELSE
      RAISE e2;
    END IF;
  END;
EXCEPTION
  WHEN e1 THEN
    RETURN SQLERRM || ' caught in outer block.';
END;

EXECUTE IMMEDIATE $$
DECLARE
  e1 EXCEPTION (-20001, 'Exception e1');
BEGIN
  -- Inner block.
  DECLARE
    e2 EXCEPTION (-20002, 'Exception e2');
    selector BOOLEAN DEFAULT TRUE;
  BEGIN
    IF (selector) THEN
      RAISE e1;
    ELSE
      RAISE e2;
    END IF;
  END;
EXCEPTION
  WHEN e1 THEN
    RETURN SQLERRM || ' caught in outer block.';
END;
$$;

该输出表明异常处理程序已捕获异常：

+-------------------------------------+
| anonymous block                     |
%-------------------------------------%
| Exception e1 caught in outer block. |
+-------------------------------------+

以下示例与上一个示例类似，但演示了嵌套块，每个块都有自己的异常处理程序。

DECLARE
  result VARCHAR;
  e1 EXCEPTION (-20001, 'Outer exception e1');
BEGIN
  result := 'No error so far (but there will be).';
  DECLARE
    e1 EXCEPTION (-20101, 'Inner exception e1');
  BEGIN
    RAISE e1;
  EXCEPTION
    WHEN e1 THEN
      result := 'Inner exception raised.';
      RETURN result;
  END;
  RETURN result;
EXCEPTION
  WHEN e1 THEN
    result := 'Outer exception raised.';
    RETURN result;
END;

EXECUTE IMMEDIATE $$
DECLARE
  result VARCHAR;
  e1 EXCEPTION (-20001, 'Outer exception e1');
BEGIN
  result := 'No error so far (but there will be).';
  DECLARE
    e1 EXCEPTION (-20101, 'Inner exception e1');
  BEGIN
    RAISE e1;
  EXCEPTION
    WHEN e1 THEN
      result := 'Inner exception raised.';
      RETURN result;
  END;
  RETURN result;
EXCEPTION
  WHEN e1 THEN
    result := 'Outer exception raised.';
    RETURN result;
END;
$$;

该输出表明内部异常处理程序已运行：

+-------------------------+
| anonymous block         |
%-------------------------%
| Inner exception raised. |
+-------------------------+

处理同一子句中的多种异常及未指定异常

以下示例片段展示了如何执行两项任务：

• Catch more than one exception in the same clause by using OR.
• Catch unspecified exceptions by using WHEN OTHER THEN.

EXCEPTION
  WHEN MY_FIRST_EXCEPTION OR MY_SECOND_EXCEPTION OR MY_THIRD_EXCEPTION THEN
    RETURN 123;
  WHEN MY_FOURTH_EXCEPTION THEN
    RETURN 4;
  WHEN OTHER THEN
    RETURN 99;

使用内置变量处理异常

The following example shows how to return SQLCODE, SQLERRM (SQL error message), and SQLSTATE built-in variable values when catching an exception:

DECLARE
  MY_EXCEPTION EXCEPTION (-20001, 'Sample message');
BEGIN
  RAISE MY_EXCEPTION;
EXCEPTION
  WHEN STATEMENT_ERROR THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
  WHEN EXPRESSION_ERROR THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'EXPRESSION_ERROR',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
END;

EXECUTE IMMEDIATE $$
DECLARE
  MY_EXCEPTION EXCEPTION (-20001, 'Sample message');
BEGIN
  RAISE MY_EXCEPTION;
EXCEPTION
  WHEN STATEMENT_ERROR THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
  WHEN EXPRESSION_ERROR THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'EXPRESSION_ERROR',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
  WHEN OTHER THEN
    RETURN OBJECT_CONSTRUCT('Error type', 'Other error',
                            'SQLCODE', SQLCODE,
                            'SQLERRM', SQLERRM,
                            'SQLSTATE', SQLSTATE);
END;
$$;

运行此示例将生成以下输出：

+--------------------------------+
| anonymous block                |
%--------------------------------%
| {                              |
|   "Error type": "Other error", |
|   "SQLCODE": -20001,           |
|   "SQLERRM": "Sample message", |
|   "SQLSTATE": "P0001"          |
| }                              |
+--------------------------------+