EXCEPTION(Snowflake Scripting)ยถ

Snowflake Scripting ๋ธ”๋ก์—์„œ ๋ฐœ์ƒํ•œ ์˜ˆ์™ธ๋ฅผ ์ฒ˜๋ฆฌํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค.

์˜ˆ์™ธ์— ๋Œ€ํ•œ ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์˜ˆ์™ธ ์ฒ˜๋ฆฌํ•˜๊ธฐ ์„น์…˜์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.

์ฐธ๊ณ  ํ•ญ๋ชฉ:

RAISE

๊ตฌ๋ฌธยถ

EXCEPTION
    WHEN <exception_name> [ OR <exception_name> ... ] THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ WHEN OTHER THEN ]
        <statement>;
        [ <statement>; ... ]
Copy

์—ฌ๊ธฐ์„œ:

exception_name

ํ˜„์žฌ ๋ธ”๋ก์˜ DECLARE ๋ถ€๋ถ„ ๋˜๋Š” ๋‘˜๋Ÿฌ์‹ธ๋Š” ๋ธ”๋ก์— ์ •์˜๋œ ์˜ˆ์™ธ ์ด๋ฆ„์ž…๋‹ˆ๋‹ค.

statement

๋ฌธ์€ ๋‹ค์Œ ์ค‘ ํ•˜๋‚˜์ผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์‚ฌ์šฉ๋ฒ• ๋…ธํŠธยถ

  • ๊ฐ ๋ธ”๋ก ์€ ๊ณ ์œ ํ•œ ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๋ฅผ ๊ฐ€์งˆ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

  • Snowflake๋Š” ๋ธ”๋ก๋‹น ๋‘˜ ์ด์ƒ์˜ ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๋ฅผ ์ง€์›ํ•˜์ง€๋Š” ์•Š์Šต๋‹ˆ๋‹ค. ๊ทธ๋Ÿฌ๋‚˜ ํ•ด๋‹น ์ฒ˜๋ฆฌ๊ธฐ๋Š” ๋‘˜ ์ด์ƒ์˜ WHEN ์ ˆ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋‘˜ ์ด์ƒ์˜ ์˜ˆ์™ธ ํƒ€์ž…์„ ํฌ์ฐฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

  • ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๋Š” ๋ธ”๋ก ๋์— ์žˆ์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ๋ธ”๋ก์— ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ ๋’ค์— ๋ฌธ์ด ํฌํ•จ๋˜์–ด ์žˆ์œผ๋ฉด ํ•ด๋‹น ๋ฌธ์ด ์‹คํ–‰๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

  • WHEN OTHER THEN ์ ˆ์€ ์•„์ง ์ง€์ •๋˜์ง€ ์•Š์€ ์˜ˆ์™ธ๋ฅผ ํฌ์ฐฉํ•ฉ๋‹ˆ๋‹ค.

  • ๋‘˜ ์ด์ƒ์˜ WHEN ์ ˆ์ด ํŠน์ • ์˜ˆ์™ธ์™€ ์ผ์น˜ํ•  ์ˆ˜ ์žˆ๋Š” ๊ฒฝ์šฐ, ์ผ์น˜ํ•˜๋Š” ์ฒซ ๋ฒˆ์งธ WHEN ์ ˆ์ด ์‹คํ–‰๋˜๋Š” ์ ˆ์ž…๋‹ˆ๋‹ค. ๋‹ค๋ฅธ ์ ˆ์€ ์‹คํ–‰๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

  • ์ง€์ •๋œ ์˜ˆ์™ธ๊ฐ€ ๋ฒ”์œ„ ๋‚ด์— ์žˆ๋Š” ๊ฒฝ์šฐ์—๋งŒ ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๋Š” ์ง€์ •๋œ ์˜ˆ์™ธ๋ฅผ ์ฒ˜๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

  • ์ €์žฅ ํ”„๋กœ์‹œ์ €๊ฐ€ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋ ค๋Š” ๊ฒฝ์šฐ, ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ์˜ ๊ฐ WHEN ์ ˆ์„ ํฌํ•จํ•˜์—ฌ ๊ฐ€๋Šฅํ•œ ๊ฐ ๊ฒฝ๋กœ์—์„œ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

  • ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ์—์„œ ๋ณ€์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ ค๋ฉด ํ•ด๋‹น ๋ณ€์ˆ˜๋ฅผ DECLARE ์„น์…˜์— ์„ ์–ธํ•˜๊ฑฐ๋‚˜ ์ €์žฅ ํ”„๋กœ์‹œ์ €์— ์ธ์ž๋กœ ์ „๋‹ฌํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. BEGIN โ€ฆ END ์„น์…˜์—์„œ๋Š” ๋ณ€์ˆ˜๋ฅผ ์„ ์–ธํ•  ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ์— ๋ณ€์ˆ˜ ์ „๋‹ฌํ•˜๊ธฐ ์„น์…˜์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.

์˜ˆยถ

์ด ์˜ˆ์—์„œ๋Š” ์˜ˆ์™ธ๋ฅผ ์„ ์–ธํ•˜๊ณ , ๋ฐœ์ƒ์‹œํ‚ค๊ณ , ์ฒ˜๋ฆฌํ•ฉ๋‹ˆ๋‹ค. ์ฐธ๊ณ :

  • ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๋Š” ๋‘˜ ์ด์ƒ์˜ ์˜ˆ์™ธ ํƒ€์ž…์„ ์ฒ˜๋ฆฌํ•˜๋„๋ก ์„ค๊ณ„๋˜์—ˆ์Šต๋‹ˆ๋‹ค.

  • ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ์˜ WHEN ์ ˆ ์ค‘ ํ•˜๋‚˜์—๋Š” ๋‹จ์ผ ๋ฌธ์ด ํฌํ•จ๋˜๋Š” ํ•œํŽธ, ๋‚˜๋จธ์ง€ ์ ˆ์—๋Š” ๋ธ”๋ก ์ด ํฌํ•จ๋ฉ๋‹ˆ๋‹ค.

    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;
    
    Copy

    ์ฐธ๊ณ : Python Connector ์ฝ”๋“œ์—์„œ Snowflake CLI, SnowSQL, Classic Console, ๋˜๋Š” execute_stream ๋˜๋Š” execute_string ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๊ฒฝ์šฐ ์ด ์˜ˆ์ œ๋ฅผ ๋Œ€์‹  ์‚ฌ์šฉํ•˜์‹ญ์‹œ์˜ค(Snowflake CLI, SnowSQL, Classic Console ๋ฐ Python Connector์—์„œ Snowflake Scripting ์‚ฌ์šฉํ•˜๊ธฐ ์ฐธ์กฐ).

    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;
    $$
    ;
    
    Copy

๋‹ค์Œ์€ ์˜ˆ์™ธ๋ฅผ ๋ฐœ์ƒ์‹œํ‚ค๋Š” ์˜ˆ๋ฅผ ์‹คํ–‰ํ•œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์ด๋Š” ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๊ฐ€ ์˜ˆ์™ธ๋ฅผ ํฌ์ฐฉํ–ˆ์Œ์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

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

์ด ๋‹ค์Œ ์˜ˆ๋Š” ์ด์ „ ์˜ˆ์™€ ์œ ์‚ฌํ•˜์ง€๋งŒ, ์ค‘์ฒฉ ๋ธ”๋ก์„ ๋‚˜ํƒ€๋‚ด๋ฉฐ, ๋‚ด๋ถ€ ๋ธ”๋ก์ด ๋‚ด๋ถ€ ๋ธ”๋ก์ด๋‚˜ ์™ธ๋ถ€ ๋ธ”๋ก์—์„œ ์„ ์–ธ๋œ ์˜ˆ์™ธ๋ฅผ ๋ฐœ์ƒ์‹œํ‚ฌ ์ˆ˜ ์žˆ์Œ์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

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;
Copy

์ฐธ๊ณ : Python Connector ์ฝ”๋“œ์—์„œ Snowflake CLI, SnowSQL, Classic Console, ๋˜๋Š” execute_stream ๋˜๋Š” execute_string ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๊ฒฝ์šฐ ์ด ์˜ˆ์ œ๋ฅผ ๋Œ€์‹  ์‚ฌ์šฉํ•˜์‹ญ์‹œ์˜ค(Snowflake CLI, SnowSQL, Classic Console ๋ฐ Python Connector์—์„œ Snowflake Scripting ์‚ฌ์šฉํ•˜๊ธฐ ์ฐธ์กฐ).

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
            BEGIN
                RETURN SQLERRM || ' caught in outer block.';
            END;

    END;
$$
;
Copy

๋‹ค์Œ์€ ์˜ˆ์™ธ๋ฅผ ๋ฐœ์ƒ์‹œํ‚ค๋Š” ์˜ˆ๋ฅผ ์‹คํ–‰ํ•œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์ด๋Š” ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๊ฐ€ ์˜ˆ์™ธ๋ฅผ ํฌ์ฐฉํ–ˆ์Œ์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

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

์ด ๋‹ค์Œ ์˜ˆ๋Š” ์ด์ „ ์˜ˆ์™€ ์œ ์‚ฌํ•˜์ง€๋งŒ, ๊ฐ๊ฐ ๊ณ ์œ ํ•œ ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๊ฐ€ ์žˆ๋Š” ์ค‘์ฒฉ ๋ธ”๋ก์„ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.

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;
Copy

์ฐธ๊ณ : Python Connector ์ฝ”๋“œ์—์„œ Snowflake CLI, SnowSQL, Classic Console, ๋˜๋Š” execute_stream ๋˜๋Š” execute_string ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๊ฒฝ์šฐ ์ด ์˜ˆ์ œ๋ฅผ ๋Œ€์‹  ์‚ฌ์šฉํ•˜์‹ญ์‹œ์˜ค(Snowflake CLI, SnowSQL, Classic Console ๋ฐ Python Connector์—์„œ Snowflake Scripting ์‚ฌ์šฉํ•˜๊ธฐ ์ฐธ์กฐ).

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;
$$
;
Copy

์ฐธ๊ณ 

์ด ์˜ˆ์—์„œ๋Š” ์™ธ๋ถ€ ๋ฐ ๋‚ด๋ถ€ ๋ธ”๋ก์—์„œ ๊ฐ™์€ ์˜ˆ์™ธ ์ด๋ฆ„(e1)์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.

์ด๋Š” ๊ถŒ์žฅ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

์ด ์˜ˆ์—์„œ๋Š” ์˜ˆ์™ธ ์ด๋ฆ„์˜ ๋ฒ”์œ„ ๋ฅผ ์„ค๋ช…ํ•˜๊ธฐ ์œ„ํ•ด ์ด ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค. e1 ์ด๋ฆ„์ด ์žˆ๋Š” ๋‘ ์˜ˆ์™ธ๋Š” ๋‹ค๋ฅธ ์˜ˆ์™ธ์ž…๋‹ˆ๋‹ค.

์™ธ๋ถ€ ๋ธ”๋ก์˜ e1 ํ•ธ๋“ค๋Ÿฌ๋Š” ๋‚ด๋ถ€ ๋ธ”๋ก์—์„œ ์„ ์–ธ๋˜์–ด ๋ฐœ์ƒํ•œ ์˜ˆ์™ธ e1์„ ์ฒ˜๋ฆฌํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

๋‹ค์Œ์€ ์˜ˆ์™ธ๋ฅผ ๋ฐœ์ƒ์‹œํ‚ค๋Š” ์˜ˆ๋ฅผ ์‹คํ–‰ํ•œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์ด๋Š” ๋‚ด๋ถ€ ์˜ˆ์™ธ ์ฒ˜๋ฆฌ๊ธฐ๊ฐ€ ์‹คํ–‰๋˜์—ˆ์Œ์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

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

์ด ์˜ˆ ์กฐ๊ฐ์€ ๋‹ค์Œ์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

  • OR ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋™์ผ ์ ˆ์—์„œ ๋‘˜ ์ด์ƒ์˜ ์˜ˆ์™ธ๋ฅผ ํฌ์ฐฉํ•˜๋Š” ๋ฐฉ๋ฒ•.

  • 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;
    
    Copy

๋‹ค์Œ ์˜ˆ์—์„œ๋Š” ์˜ˆ์™ธ๋ฅผ ํฌ์ฐฉํ•  ๋•Œ SQLCODE, SQLERRM(SQL ์˜ค๋ฅ˜ ๋ฉ”์‹œ์ง€), SQLSTATE๋ฅผ ๋ฐ˜ํ™˜ํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

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;
Copy

์ฐธ๊ณ : Python Connector ์ฝ”๋“œ์—์„œ Snowflake CLI, SnowSQL, Classic Console, ๋˜๋Š” execute_stream ๋˜๋Š” execute_string ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๊ฒฝ์šฐ ์ด ์˜ˆ์ œ๋ฅผ ๋Œ€์‹  ์‚ฌ์šฉํ•˜์‹ญ์‹œ์˜ค(Snowflake CLI, SnowSQL, Classic Console ๋ฐ Python Connector์—์„œ Snowflake Scripting ์‚ฌ์šฉํ•˜๊ธฐ ์ฐธ์กฐ).

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;
$$
;
Copy

์ด ์˜ˆ๋ฅผ ์‹คํ–‰ํ•˜๋ฉด ๋‹ค์Œ ์ถœ๋ ฅ์ด ์ƒ์„ฑ๋ฉ๋‹ˆ๋‹ค.

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

์ด ์˜ˆ์—์„œ๋Š” ๊ฐ€๋Šฅํ•œ ๊ฐ ๊ฒฝ๋กœ์—์„œ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

declare
    e1 exception;
    e2 exception;
begin
    statement_1;
    ...
    RETURN x;
exception
    when e1 then begin
        ...
        RETURN y;
        end;
    when e2 then begin
        ...
        RETURN z;
        end;
end;
Copy