SnowConvert AI - Oracle - PL/SQL을 Snowflake Scripting으로 변환¶

ASSIGNMENT STATEMENT¶

설명¶

할당 문은 데이터 항목의 값을 유효한 값으로 설정합니다.\ (Oracle PL/SQL 언어 참조 ASSIGNMENT 문)

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

Oracle 할당 구문¶

assignment_statement_target := expression ;

assignment_statement_target = 
{ collection_variable [ ( index ) ]
| cursor_variable
| :host_cursor_variable
| object[.attribute]
| out_parameter
| placeholder
| record_variable[.field]
| scalar_variable
}

Snowflake Scripting 할당 구문¶

LET <variable_name> <type> { DEFAULT | := } <expression> ;

LET <variable_name> { DEFAULT | := } <expression> ;

참고

LET 키워드는 변수가 이전에 선언된 경우 할당 문에 필요하지 않습니다. 자세한 내용은 Snowflake 할당 설명서 를 참조하십시오.

샘플 소스 패턴¶

1. Scalar Variables¶

Oracle¶

CREATE TABLE TASSIGN (
    COL1 NUMBER,
    COL2 NUMBER,
    COL3 VARCHAR(20),
    COL4 VARCHAR(20)
);

CREATE OR REPLACE PROCEDURE PSCALAR
AS
   var1  NUMBER := 40;
   var2  NUMBER := 22.50;
   var3  VARCHAR(20);
   var4  BOOLEAN;
   var5  NUMBER;
BEGIN
   var1 := 1;
   var2 := 2.1;
   var2 := var2 + var2;
   var3 := 'Hello World';
   var4 := true;
   var4 := var1 > 500;
   IF var4 THEN
      var5 := 0;
   ELSE
      var5 := 1;
   END IF;
  INSERT INTO TASSIGN VALUES(var1, var2, var3, var5);
END;

CALL PSCALAR();

SELECT * FROM TASSIGN;

결과¶


COL1	COL2	COL3	COL4
1	4.2	Hello World	1

Snowflake Scripting¶

CREATE OR REPLACE TABLE TASSIGN (
     COL1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
     COL2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
     COL3 VARCHAR(20),
     COL4 VARCHAR(20)
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

 CREATE OR REPLACE PROCEDURE PSCALAR ()
 RETURNS VARCHAR
 LANGUAGE SQL
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 EXECUTE AS CALLER
 AS
 $$
     DECLARE
     var1 NUMBER(38, 18) := 40;
     var2 NUMBER(38, 18) := 22.50;
     var3  VARCHAR(20);
     var4  BOOLEAN;
     var5 NUMBER(38, 18);
     BEGIN
     var1 := 1;
     var2 := 2.1;
     var2 := :var2 + :var2;
     var3 := 'Hello World';
     var4 := true;
     var4 := :var1 > 500;
     IF (:var4) THEN
       var5 := 0;
       ELSE
       var5 := 1;
       END IF;
       INSERT INTO TASSIGN
       VALUES(:var1, :var2, :var3, :var5);
     END;
 $$;

 CALL PSCALAR();
 
SELECT * FROM
     TASSIGN;

결과¶


COL1	COL2	COL3	COL4
1.000000000000000000	4.000000000000000000	Hello World	1

경고

일부 데이터 타입에 대한 변환을 업데이트해야 하며, 이로 인해 다른 결과가 발생할 수 있습니다. 예를 들어, NUMBER 에서 NUMBER 로 변경하면 값이 반올림되고 소수점이 손실됩니다. 이 문제에 대한 작업 항목이 이미 있습니다.

2. Out Parameter Assignment¶

출력 매개 변수가 변환되는 방식에 대한 자세한 내용은 다음 문서 출력 매개 변수를 참조하십시오.

3. Not Supported Assignments¶

Oracle¶

CREATE OR REPLACE PROCEDURE pinvalid(out_parameter   IN OUT NUMBER)
AS
record_variable       employees%ROWTYPE;

TYPE cursor_type IS REF CURSOR;
cursor1   cursor_type;
cursor2   SYS_REFCURSOR;

TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
collection_variable     collection_type;

BEGIN
--Record Example
  record_variable.last_name := 'Ortiz';

--Cursor Example
  cursor1 := cursor2;
  
--Collection
  collection_variable('Test') := 5;

--Out Parameter
  out_parameter := 123;
END;

Snowflake Scripting¶

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employees" **
CREATE OR REPLACE PROCEDURE pinvalid (out_parameter OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL REF CURSOR TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE cursor_type IS REF CURSOR;
    cursor1_res RESULTSET;
    cursor2_res RESULTSET;
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
    collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collection_type' USAGE CHANGED TO VARIANT ***/!!!;
  BEGIN
    --Record Example
    record_variable := OBJECT_INSERT(record_variable, 'LAST_NAME', 'Ortiz', true);

    --Cursor Example
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      cursor1 := :cursor2;

    --Collection
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      collection_variable('Test') := 5;
    --Out Parameter
    out_parameter := 123;
  END;
$$;

Known Issues¶

1. Several Unsupported Assignment Statements¶

현재 커서, 컬렉션, 레코드 및 사용자 정의 유형 변수에 대한 변환은 Snowflake Scripting에서 지원되지 않습니다. 따라서 이러한 변수를 사용하는 할당 문은 설명이 추가되고 지원되지 않는 것으로 표시됩니다. 이러한 변수를 Snowflake 반정형 데이터 타입 으로 변경하면 일부 시나리오에서 해결 방법으로 도움이 될 수 있습니다.

CALL¶

설명¶

Oracle에는 두 가지 유형의 호출 문이 있습니다.

1-CALL 문:¶

CALL 문을 사용하여 SQL 에서 루틴(독립 실행형 프로시저 또는 함수 또는 유형 또는 패키지 내에 정의된 프로시저 또는 함수)을 실행합니다. (Oracle SQL Language Reference CALL)

2 호출 사양:¶

호출 사양은 Java 메서드 또는 C 언어 하위 프로그램을 선언하여 PL/SQL 에서 호출할 수 있도록 합니다. (Oracle SQL Language Reference 호출 사양)

The CALL Specification is not supported in Snowflake Scripting since this is part of the development libraries for C and JAVA, not a SQL statement, therefore this statement is not transformed.

Known Issues¶

문제가 발견되지 않았습니다.

CASE¶

CASE 문에 대한 변환 참조

설명¶

CASE 문은 조건 시퀀스 중에서 선택하여 해당 문을 실행합니다. Oracle CASE 에 대한 자세한 내용은 여기 에서 확인할 수 있습니다.

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

단순 케이스¶

Oracle CASE 구문¶

[ <<label>> ] CASE case_operand
  WHEN boolean_expression THEN statement ;
  [ WHEN boolean_expression THEN statement ; ]...
  [ ELSE statement [ statement ]... ;
END CASE [ label ] ;

Snowflake Scripting CASE 구문¶

CASE ( <expression_to_match> )
    WHEN <expression> THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ ELSE
        <statement>;
        [ <statement>; ... ]
    ]
END [ CASE ] ;

검색된 케이스¶

Oracle CASE 구문¶

[ <<label>> ] CASE
  WHEN boolean_expression THEN statement ;
  [ WHEN boolean_expression THEN statement ; ]...
  [ ELSE statement [ statement ]... ;
END CASE [ label ];

Snowflake Scripting CASE 구문¶

CASE
    WHEN <boolean_expression> THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ ELSE
        <statement>;
        [ <statement>; ... ]
    ]
END [ CASE ] ;

샘플 소스 패턴¶

Sample auxiliary table¶

Oracle¶

CREATE TABLE case_table(col varchar(30));

Snowflake¶

CREATE OR REPLACE TABLE case_table (col varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

단순 케이스¶

Oracle¶

CREATE OR REPLACE PROCEDURE caseExample1 ( grade NUMBER )
IS
RESULT VARCHAR(20);
BEGIN
   <<CASE1>>
   CASE grade
    WHEN 10 THEN RESULT:='Excellent';
    WHEN 9 THEN RESULT:='Very Good';
    WHEN 8 THEN RESULT:='Good';
    WHEN 7 THEN RESULT:='Fair';
    WHEN 6 THEN RESULT:='Poor';
    ELSE RESULT:='No such grade';
  END CASE CASE1;
  INSERT INTO CASE_TABLE(COL) VALUES (RESULT);
END;

CALL caseExample1(6);

CALL caseExample1(4);

CALL caseExample1(10);

SELECT * FROM CASE_TABLE;

결과¶


COL
불량
해당 등급 없음
우수

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE caseExample1 (grade NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT VARCHAR(20);
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<CASE1>> ***/!!!
    CASE :grade
      WHEN 10 THEN
        RESULT := 'Excellent';
      WHEN 9 THEN
        RESULT := 'Very Good';
      WHEN 8 THEN
        RESULT := 'Good';
      WHEN 7 THEN
        RESULT := 'Fair';
      WHEN 6 THEN
        RESULT := 'Poor';
        ELSE
        RESULT := 'No such grade';
    END CASE;
    INSERT INTO CASE_TABLE(COL) VALUES (:RESULT);
  END;
$$;

CALL caseExample1(6);

CALL caseExample1(4);

CALL caseExample1(10);

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "CASE_TABLE" **

SELECT * FROM
  CASE_TABLE;

결과¶


COL
불량
해당 등급 없음
우수

검색된 케이스¶

Oracle¶

CREATE OR REPLACE PROCEDURE caseExample2 ( grade NUMBER )
IS
RESULT VARCHAR(20);
BEGIN
    <<CASE1>>
    CASE
    	WHEN grade = 10 THEN RESULT:='Excellent';
    	WHEN grade = 9 THEN RESULT:='Very Good';
    	WHEN grade = 8 THEN RESULT:='Good';
    	WHEN grade = 7 THEN RESULT:='Fair';
    	WHEN grade = 6 THEN RESULT:='Poor';
    	ELSE RESULT:='No such grade';
  END CASE CASE1;
  INSERT INTO CASE_TABLE(COL) VALUES (RESULT);
END;

CALL caseExample2(6);
CALL caseExample2(4);
CALL caseExample2(10);
SELECT * FROM CASE_TABLE;

결과¶


COL
불량
해당 등급 없음
우수

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE caseExample2 (grade NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT VARCHAR(20);
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<CASE1>> ***/!!!
    CASE
      WHEN :grade = 10 THEN
        RESULT := 'Excellent';
      WHEN :grade = 9 THEN
        RESULT := 'Very Good';
      WHEN :grade = 8 THEN
        RESULT := 'Good';
      WHEN :grade = 7 THEN
        RESULT := 'Fair';
      WHEN :grade = 6 THEN
        RESULT := 'Poor';
        ELSE
        RESULT := 'No such grade';
    END CASE;
    INSERT INTO CASE_TABLE(COL) VALUES (:RESULT);
  END;
$$;

CALL caseExample2(6);

CALL caseExample2(4);

CALL caseExample2(10);

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "CASE_TABLE" **
SELECT * FROM
  CASE_TABLE;

결과¶


COL
불량
해당 등급 없음
우수

알려진 문제¶

1. Labels are not supported in Snowflake Scripting CASE syntax¶

레이블은 위치에 따라 설명이 추가되거나 제거됩니다.

COMPOUND STATEMENTS¶

이 섹션은 복합 문에 대한 변환 사양입니다.

경고

이 섹션은 진행 중인 작업으로 향후 정보가 변경될 수 있습니다.

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

일반 설명¶

PL/SQL소스 프로그램의 기본 단위는 블록으로, 관련 선언과 문을 그룹화합니다.

PL/SQL 블록은 DECLARE, BEGIN, EXCEPTION, END 라는 키워드로 정의됩니다. 이러한 키워드는 블록을 선언적 부분, 실행 가능한 부분, 예외 처리 부분으로 나눕니다. 실행 가능한 부분만 필수입니다. (PL/SQL 익명 블록)

Oracle의 BEGIN...END 블록은 다음과 같은 특성을 가질 수 있습니다.

중첩됩니다.
변수에 DECLARE 문을 포함합니다.
여러 개의 SQL 또는 PL/SQL 문을 그룹화합니다.

Oracle 구문¶

[DECLARE <Variable declaration>]
BEGIN
  <Executable statements>
[EXCEPTION <Exception handler>]
END

Snowflake 구문¶

BEGIN
    <statement>;
    [ <statement>; ... ]
[ EXCEPTION <exception_handler> ]
END;

참고

Snowflake에서 BEGIN/END 블록은 익명 블록 내부의 최상위 구조가 될 수 있습니다(Snowflake 설명서) 를 참조하십시오.

샘플 소스 패턴¶

1. IF-ELSE block¶

자세한 내용은 IF 문에 대한 SnowConvert AI IF 문 변환 및 Snowflake IF 문 설명서를 검토하세요.

Oracle¶

DECLARE
    age NUMBER := 18;
BEGIN
    IF age >= 18 THEN 
        DBMS_OUTPUT.PUT_LINE('You are an adult.');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('You are a minor.');
    END IF;
END;

결과¶

Statement processed.
You are an adult.

Snowflake¶

경고

프로시저 또는 사용자 정의 함수(UDF)를 호출할 때 call_results 변수와 동등성을 지원하기 위해 코드를 생성해야 합니다. 이 경우 정보를 인쇄하는 데 사용됩니다.

[여기](../ built-in-packages.md#put_line-procedure)에서 사용된 사용자 정의 함수(UDF)를 검토하세요.

DECLARE
    age NUMBER(38, 18) := 18;
    call_results VARIANT;
BEGIN
    IF (:age >= 18) THEN
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        call_results := (
            CALL DBMS_OUTPUT.PUT_LINE_UDF('You are an adult.')
        );
    ELSE
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        call_results := (
            CALL DBMS_OUTPUT.PUT_LINE_UDF('You are a minor.')
        );
    END IF;
    RETURN call_results;
END;

결과¶

anonymous block
You are an adult.

2. CASE statement¶

자세한 내용은 SnowConvert AI CASE 문 설명서 및 Snowflake CASE 설명서를 검토하세요.

Oracle¶

BEGIN
   DECLARE
      day_of_week NUMBER := 3;
   BEGIN
      CASE day_of_week
         WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Sunday');
         WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Monday');
         WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Tuesday');
         WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Wednesday');
         WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('Thursday');
         WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('Friday');
         WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('Saturday');
         ELSE DBMS_OUTPUT.PUT_LINE('Invalid day');
      END CASE;
   END;
END;

결과¶

Statement processed.
Tuesday

Snowflake¶

경고

[여기](../ built-in-packages.md#put_line-procedure)에서 사용된 사용자 정의 함수(UDF)를 검토하세요.

DECLARE
   call_results VARIANT;
BEGIN
   DECLARE
      day_of_week NUMBER(38, 18) := 3;
   BEGIN
      CASE :day_of_week
         WHEN 1 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Sunday')
            );
         WHEN 2 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Monday')
            );
         WHEN 3 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Tuesday')
            );
         WHEN 4 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Wednesday')
            );
         WHEN 5 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Thursday')
            );
         WHEN 6 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Friday')
            );
         WHEN 7 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Saturday')
            );
         ELSE
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Invalid day')
            );
      END CASE;
   END;
   RETURN call_results;
END;

결과¶

anonymous block
Tuesday

3. LOOP statements¶

자세한 내용은 SnowConvert AI FOR LOOP와 Snowflake LOOP 설명서 및 FOR 설명서를 검토하세요.

Oracle¶

BEGIN
    FOR i IN 1..10 LOOP
        NULL;
    END LOOP;
END;

결과¶

Statement processed.

Snowflake¶

첫 번째 탭¶

BEGIN
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        FOR i IN 1 TO 10
                         --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                         LOOP
                                    NULL;
                                END LOOP;
END;

결과¶

anonymous block

4. Procedure call and OUTPUT parameters¶

Oracle의 익명 블록에는 프로시저에 대한 호출이 있을 수 있습니다. 또한 SnowConvert AI 프로시저 설명서가 유용할 수 있습니다.

다음 예에서는 OUT 매개 변수를 사용합니다. 현재 변환에 대한 정보는 여기(SnowConvert AI OUTPUT 매개 변수)에서 확인할 수 있습니다.

Oracle¶

-- Procedure declaration
CREATE OR REPLACE PROCEDURE calculate_sum(
    p_num1 IN NUMBER,
    p_num2 IN NUMBER,
    p_result OUT NUMBER
)
IS
BEGIN
    -- Calculate the sum of the two numbers
    p_result := p_num1 + p_num2;
END;
/

-- Anonymous block with a procedure call
DECLARE
    -- Declare variables to hold the input and output values
    v_num1 NUMBER := 10;
    v_num2 NUMBER := 20;
    v_result NUMBER;
BEGIN
    -- Call the procedure with the input values and get the result
    calculate_sum(v_num1, v_num2, v_result);
    
    -- Display the result
    DBMS_OUTPUT.PUT_LINE('The sum of ' || v_num1 || ' and ' || v_num2 || ' is ' || v_result);
END;
/

결과¶

Statement processed.
The sum of 10 and 20 is 30

Snowflake¶

-- Procedure declaration
CREATE OR REPLACE PROCEDURE calculate_sum (p_num1 NUMBER(38, 18), p_num2 NUMBER(38, 18), p_result OUT NUMBER(38, 18)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
    -- Calculate the sum of the two numbers
        p_result := :p_num1 + :p_num2;
    END;
$$;

-- Anonymous block with a procedure call
DECLARE
    -- Declare variables to hold the input and output values
    v_num1 NUMBER(38, 18) := 10;
    v_num2 NUMBER(38, 18) := 20;
    v_result NUMBER(38, 18);
    call_results VARIANT;
BEGIN
    CALL
    -- Call the procedure with the input values and get the result
    calculate_sum(:v_num1, :v_num2, :v_result);

    -- Display the result
    --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
    call_results := (
        CALL DBMS_OUTPUT.PUT_LINE_UDF('The sum of ' || NVL(:v_num1 :: STRING, '') || ' and ' || NVL(:v_num2 :: STRING, '') || ' is ' || NVL(:v_result :: STRING, ''))
    );
    RETURN call_results;
END;

결과¶

anonymous block
The sum of 10 and 20 is 30

5. Alter session¶

자세한 내용은 세션 변경 설명서를 검토하세요.

Oracle에서 BEGIN...END 블록은 EXECUTE IMMEDIATE 문을 사용하여 alter session 문을 실행해야 합니다.

Oracle¶

DECLARE
     lv_sql_txt VARCHAR2(200);
BEGIN
     lv_sql_txt := 'ALTER SESSION SET nls_date_format = ''DD-MM-YYYY''';
     EXECUTE IMMEDIATE lv_sql_txt;
END;

결과¶

Statement processed.
Done

Snowflake¶

DECLARE
     lv_sql_txt VARCHAR(200);
BEGIN
     lv_sql_txt := 'ALTER SESSION SET nls_date_format = ''DD-MM-YYYY''';
     !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!! 
     EXECUTE IMMEDIATE :lv_sql_txt;
END;

결과¶

anonymous block
Done

6. Cursors¶

다음 예는 BEGIN...END 블록 내부의 cursor 사용법을 보여줍니다. 자세한 내용은 커서 설명서를 검토하세요.

Oracle¶

CREATE TABLE employee (
    ID_Number	NUMBER,
    emp_Name	VARCHAR(200),
    emp_Phone	NUMBER
);

INSERT INTO employee VALUES (1, 'NameA NameZ', 1234567890);
INSERT INTO employee VALUES (2, 'NameB NameY', 1234567890);

DECLARE
    var1 VARCHAR(20);
    CURSOR cursor1 IS SELECT emp_Name FROM employee ORDER BY ID_Number;
BEGIN
    OPEN cursor1;
    FETCH cursor1 INTO var1;
    CLOSE cursor1;
	DBMS_OUTPUT.PUT_LINE(var1);
END;

결과¶

Statement processed.
NameA NameZ

Snowflake¶

경고

[여기](../ built-in-packages.md#put_line-procedure)에서 사용된 사용자 정의 함수(UDF)를 검토하세요.

CREATE OR REPLACE TABLE employee (
	   ID_Number NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
	   emp_Name	VARCHAR(200),
	   emp_Phone NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO employee
VALUES (1, 'NameA NameZ', 1234567890);

INSERT INTO employee
VALUES (2, 'NameB NameY', 1234567890);

DECLARE
    var1 VARCHAR(20);
	   --** SSC-PRF-0009 - PERFORMANCE REVIEW - CURSOR USAGE **
	   cursor1 CURSOR
	   FOR
		SELECT emp_Name FROM
			employee
		ORDER BY ID_Number;
	   call_results VARIANT;
BEGIN
	   OPEN cursor1;
	   FETCH cursor1 INTO
		:var1;
	   CLOSE cursor1;
	   --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
	   call_results := (
		CALL DBMS_OUTPUT.PUT_LINE_UDF(:var1)
	   );
	   RETURN call_results;
END;

결과¶

anonymous block
NameA NameZ

7. Select statements¶

자세한 내용은 Select 설명서를 검토하세요.

Oracle¶

CREATE TABLE employee (
    ID_Number NUMBER,
    emp_Name VARCHAR(200),
    emp_Phone NUMBER
);

INSERT INTO employee VALUES (1, 'NameA NameZ', 1234567890);
INSERT INTO employee VALUES (2, 'NameB NameY', 1234567890);

DECLARE
    var_Result NUMBER;
BEGIN
    SELECT COUNT(*) INTO var_Result FROM employee;
    DBMS_OUTPUT.PUT_LINE(var_Result);
END;

결과¶

Statement processed.
2

Snowflake¶

경고

[여기](../ built-in-packages.md#put_line-procedure)에서 사용된 사용자 정의 함수(UDF)를 검토하세요.

CREATE OR REPLACE TABLE employee (
       ID_Number NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
       emp_Name VARCHAR(200),
       emp_Phone NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
   ;

   INSERT INTO employee
   VALUES (1, 'NameA NameZ', 1234567890);

   INSERT INTO employee
   VALUES (2, 'NameB NameY', 1234567890);

   DECLARE
    var_Result NUMBER(38, 18);
       call_results VARIANT;
   BEGIN
       SELECT COUNT(*) INTO
           :var_Result
       FROM
           employee;
       --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
       call_results := (
           CALL DBMS_OUTPUT.PUT_LINE_UDF(:var_Result)
       );
       RETURN call_results;
   END;

결과¶

anonymous block
2

8. Join Statements¶

자세한 내용은 조인 설명서를 검토하세요.

Oracle¶

CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES (2);
INSERT INTO t1 (col1) VALUES (3);
INSERT INTO t1 (col1) VALUES (4);

INSERT INTO t2 (col1) VALUES (1);
INSERT INTO t2 (col1) VALUES (2);
INSERT INTO t2 (col1) VALUES (2);
INSERT INTO t2 (col1) VALUES (3);


DECLARE
    total_price FLOAT;
    CURSOR cursor1 IS SELECT t1.col1 as FirstTable, t2.col1 as SecondTable
    FROM t1 INNER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
BEGIN
    total_price := 0.0;
    FOR rec IN cursor1 LOOP
      total_price := total_price + rec.FirstTable;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(total_price);
END;

결과¶

Statement processed.
7

Snowflake¶

경고

[여기](../ built-in-packages.md#put_line-procedure)에서 사용된 사용자 정의 함수(UDF)를 검토하세요.

CREATE OR REPLACE TABLE t1 (col1 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE TABLE t2 (col1 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO t1(col1) VALUES (2);

INSERT INTO t1(col1) VALUES (3);

INSERT INTO t1(col1) VALUES (4);

INSERT INTO t2(col1) VALUES (1);

INSERT INTO t2(col1) VALUES (2);

INSERT INTO t2(col1) VALUES (2);

INSERT INTO t2(col1) VALUES (3);

DECLARE
    total_price FLOAT;
    --** SSC-PRF-0009 - PERFORMANCE REVIEW - CURSOR USAGE **
    cursor1 CURSOR
    FOR
        SELECT t1.col1 as FIRSTTABLE, t2.col1 as SECONDTABLE
           FROM
            t1
            INNER JOIN
                t2
               ON t2.col1 = t1.col1
           ORDER BY 1,2;
    call_results VARIANT;
BEGIN
    total_price := 0.0;
    OPEN cursor1;
    --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
    FOR rec IN cursor1 DO
        total_price :=
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN FLOAT AND unknown ***/!!!
        :total_price + rec.FIRSTTABLE;
    END FOR;
    CLOSE cursor1;
    --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
    call_results := (
        CALL DBMS_OUTPUT.PUT_LINE_UDF(:total_price)
    );
    RETURN call_results;
END;

9. Exception handling¶

Oracle¶

DECLARE
      v_result NUMBER;
BEGIN
   v_result := 1 / 0;
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         DBMS_OUTPUT.PUT_LINE( SQLERRM );
END;

결과¶

Statement processed.
ORA-01476: divisor is equal to zero

Snowflake¶

경고

ZERO_DIVIDE 의 예외는 지원되지 않습니다.

DECLARE
      v_result NUMBER(38, 18);
      error_results VARIANT;
BEGIN
      v_result := 1 / 0;
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
      --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
      error_results := (
         CALL DBMS_OUTPUT.PUT_LINE_UDF( SQLERRM )
      );
      RETURN error_results;
END;

결과¶

anonymous block
Division by zero

알려진 문제¶

Oracle에서 지원되지 않는 GOTO 문.
GOTO 문을 사용하는 예외도 영향을 받을 수 있습니다.
커서 기능은 현재 변환에 대한 제한 사항에 따라 조정될 수 있습니다.

CONTINUE¶

Oracle CONTINUE 문을 Snowflake Scripting으로 변환하기 위한 변환 참조

설명¶

CONTINUE 문은 조건부 또는 무조건적으로 루프의 현재 반복을 종료하고 현재 루프 또는 레이블이 지정된 루프의 다음 반복으로 제어를 이전합니다.\ (Oracle PL/SQL 언어 참조 CONTINUE 문)

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

Oracle CONTINUE 구문¶

CONTINUE [ label ] [ WHEN boolean_expression ] ;

Snowflake Scripting CONTINUE 구문¶

{ CONTINUE | ITERATE } [ <label> ] ;

샘플 소스 패턴¶

1. Simple Continue¶

코드에서는 CONTINUE 를 사용하여 INSERT 문을 건너뜁니다.

참고

이 경우는 기능적으로 동일합니다.

Oracle¶

CREATE TABLE continue_testing_table_1 (iterator VARCHAR2(5));

CREATE OR REPLACE PROCEDURE continue_procedure_1 
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP 
        I := I + 1;
        CONTINUE;
        INSERT INTO continue_testing_table_1
        VALUES (TO_CHAR(I));
    END LOOP;
END;

CALL continue_procedure_1();
SELECT * FROM continue_testing_table_1;

결과¶


ITERATOR

Snowflake Scripting¶

CREATE OR REPLACE TABLE continue_testing_table_1 (iterator VARCHAR(5))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE continue_procedure_1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I NUMBER(38, 18) := 0;
        J NUMBER(38, 18) := 20;
    BEGIN
        WHILE (:I <= :J)
                         --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                         LOOP
                             I := :I + 1;
                             CONTINUE;
                                    INSERT INTO continue_testing_table_1
                                    VALUES (TO_CHAR(:I));
                                END LOOP;
    END;
$$;

CALL continue_procedure_1();

SELECT * FROM
    continue_testing_table_1;

결과¶


ITERATOR

2. Continue with condition¶

CONTINUE 를 사용하여 짝수 삽입을 건너뛰는 코드입니다.

참고

이 경우는 기능적으로 동일하지는 않지만 조건을 IF 문으로 바꿀 수 있습니다.

Oracle¶

CREATE TABLE continue_testing_table_2 (iterator VARCHAR2(5));

CREATE OR REPLACE PROCEDURE continue_procedure_2
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP
        I := I + 1;
        CONTINUE WHEN MOD(I,2) = 0;
        INSERT INTO continue_testing_table_2 VALUES(TO_CHAR(I));
    END LOOP;  
END;

CALL continue_procedure_2();
SELECT * FROM continue_testing_table_2;

결과¶


ITERATOR
1
3
5
7
9
11
13
15
17
19
21

Snowflake Scripting¶

CREATE OR REPLACE TABLE continue_testing_table_2 (iterator VARCHAR(5))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE continue_procedure_2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I NUMBER(38, 18) := 0;
        J NUMBER(38, 18) := 20;
    BEGIN
        WHILE (:I <= :J)
                         --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                         LOOP
                             I := :I + 1;
                             IF (MOD(:I,2) = 0) THEN
                                 CONTINUE;
                             END IF;
                                    INSERT INTO continue_testing_table_2
                             VALUES(TO_CHAR(:I));
                                END LOOP;
    END;
$$;

CALL continue_procedure_2();

SELECT * FROM
    continue_testing_table_2;

결과¶


ITERATOR
1
3
5
7
9
11
13
15
17
19
21

3. Continue with label and condition¶

코드는 19번째 라인을 건너뛰고, CONTINUE 는 레이블을 사용하여 항상 외부 루프로 이동하기 때문에 내부 루프는 한 번만 실행됩니다.

참고

이 케이스는 이전 샘플과 동일한 프로세스를 적용하여 기능적으로 동일합니다.

참고

레이블은 설명이 추가됩니다.

Oracle¶

CREATE OR REPLACE PROCEDURE continue_procedure_3
IS
I NUMBER := 0;
J NUMBER := 10;
K NUMBER := 0;
BEGIN
    <<out_loop>>
    WHILE I <= J LOOP
        I := I + 1;
        INSERT INTO continue_testing_table_3 VALUES('I' || TO_CHAR(I));

        <<in_loop>>
        WHILE K <= J * 2 LOOP
            K := K + 1;
            CONTINUE out_loop WHEN K > J / 2;
            INSERT INTO continue_testing_table_3 VALUES('K' || TO_CHAR(K));
        END LOOP in_loop;

        K := 0;
    END LOOP out_loop; 
END;

CALL continue_procedure_3();
SELECT * FROM continue_testing_table_3;

결과¶


ITERATOR
I1
K1
K2
K3
K4
K5
I2
I3
I4
I5
I6
I7
I8
I9
I10
I11

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE continue_procedure_3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I NUMBER(38, 18) := 0;
        J NUMBER(38, 18) := 10;
        K NUMBER(38, 18) := 0;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<out_loop>> ***/!!!
        WHILE (:I <= :J)
                         --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                         LOOP
                             I := :I + 1;
                                    INSERT INTO continue_testing_table_3
                             VALUES('I' || NVL(TO_CHAR(:I) :: STRING, ''));
                             !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<in_loop>> ***/!!!
                             WHILE (:K <= :J * 2)
                                                  --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                                                  LOOP
                                                      K := :K + 1;
                                                      IF (:K > :J / 2) THEN
                                                          CONTINUE out_loop;
                                                      END IF;
                                        INSERT INTO continue_testing_table_3
                                                      VALUES('K' || NVL(TO_CHAR(:K) :: STRING, ''));
                                    END LOOP in_loop;
                             K := 0;
                                END LOOP out_loop;
    END;
$$;

CALL continue_procedure_3();

SELECT * FROM
    continue_testing_table_3;

결과¶


ITERATOR
I1
K1
K2
K3
K4
K5
I2
I3
I4
I5
I6
I7
I8
I9
I10
I11

Known Issues¶

문제가 발견되지 않았습니다.

DECLARE¶

Oracle DECLARE 문을 Snowflake Scripting으로 변환하기 위한 변환 참조

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명¶

Oracle DECLARE 문은 PL/SQL 블록 문의 선택적 부분입니다. 변수, 상수, 프로시저 선언 및 정의, 함수 선언 및 정의, 예외, 커서, 유형 및 기타 여러 문을 생성할 수 있습니다. Oracle DECLARE 에 대한 자세한 내용은 여기 에서 확인할 수 있습니다.

Oracle DECLARE 구문¶

declare_section body

declare_section::= { item_list_1 [ item_list_2 ] | item_list_2 }

item_list_1::= 
{ type_definition
| cursor_declaration
| item_declaration
| function_declaration
| procedure_declaration
}
 ...
 
item_list_2::=
{ cursor_declaration
| cursor_definition
| function_declaration
| function_definition
| procedure_declaration
| procedure_definition
}
 ...

item_declaration::=
{ collection_variable_decl
| constant_declaration
| cursor_variable_declaration
| exception_declaration
| record_variable_declaration
| variable_declaration
}

body::= BEGIN statement ...
  [ EXCEPTION exception_handler [ exception_handler ]... ] END [ name ] ;

Snowflake Scripting DECLARE 구문¶

[ DECLARE
  { <variable_declaration> | <cursor_declaration> | <exception_declaration> | <resultset_declaration> }
  [, { <variable_declaration> | <cursor_declaration> | <exception_declaration> | <resultset_declaration> } ... ]
]
BEGIN
    <statement>;
    [ <statement>; ... ]
[ EXCEPTION <exception_handler> ]
END [ <label> ] ;

샘플 소스 패턴¶

변수 선언¶

Oracle 변수 선언 구문¶

variable_declaration::= 
variable datatype [ [ NOT NULL] {:= | DEFAULT} expression ] ;

Snowflake Scripting 변수 선언 구문¶

<variable_name> <type>;

<variable_name> DEFAULT <expression> ;

<variable_name> <type> DEFAULT <expression> ;

Oracle¶

CREATE OR REPLACE PROCEDURE var_decl_proc
IS
var1 NUMBER; 
var2 NUMBER := 1;
var3 NUMBER NOT NULL := 1;
var4 NUMBER DEFAULT 1;
var5 NUMBER NOT NULL DEFAULT 1;
BEGIN
    NULL; 
END;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE var_decl_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
        var2 NUMBER(38, 18) := 1;
        var3 NUMBER(38, 18) := 1 /*** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/;
        var4 NUMBER(38, 18) DEFAULT 1;
        var5 NUMBER(38, 18) DEFAULT 1 /*** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/;
    BEGIN
        NULL;
    END;
$$;

상수 선언¶

경고

상수는 Snowflake Scripting에서 지원되지 않지만, 동작을 시뮬레이션하기 위해 변수로 변환되고 있습니다.

Oracle 상수 선언 구문¶

constant_declaration::=
constant CONSTANT datatype [NOT NULL] { := | DEFAULT } expression ;

Snowflake Scripting 변수 선언 구문¶

<variable_name> <type>;

<variable_name> DEFAULT <expression> ;

<variable_name> <type> DEFAULT <expression> ; 

Oracle¶

CREATE OR REPLACE PROCEDURE const_decl_proc
IS
my_const1 CONSTANT NUMBER := 40;
my_const2 CONSTANT NUMBER NOT NULL := 40;
my_const2 CONSTANT NUMBER DEFAULT 40;
my_const2 CONSTANT NUMBER NOT NULL DEFAULT 40;
BEGIN
    NULL; 
END;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE const_decl_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        --** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
        my_const1 NUMBER(38, 18) := 40;
        --** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
        --** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE **
        my_const2 NUMBER(38, 18) := 40;
        --** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
        my_const2 NUMBER(38, 18) DEFAULT 40;
        --** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
        --** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE **
        my_const2 NUMBER(38, 18) DEFAULT 40;
    BEGIN
        NULL;
    END;
$$;

커서 선언¶

Oracle 커서 선언 구문¶

cursor_declaration::= CURSOR cursor
  [( cursor_parameter_dec [, cursor_parameter_dec ]... )]
    RETURN rowtype;

cursor_parameter_dec::= parameter [IN] datatype [ { := | DEFAULT } expression ]

rowtype::= 
{ {db_table_or_view | cursor | cursor_variable}%ROWTYPE
  | record%TYPE
  | record_type
  }

Snowflake Scripting 커서 선언 구문¶

<cursor_name> CURSOR [ ( <argument> [, <argument> ... ] ) ]
        FOR <query> ;

위험

Oracle _ 커서 선언 _은 필수가 아니므로 출력 코드에서 설명이 생략될 수 있습니다. 대신 _ 커서 정의 _가 사용되며, Snowflake Scripting _ 커서 선언 _으로 변환됩니다. 섹션으로 이동하여 CURSOR 섹션을 참조하여 커서 정의에 대한 자세한 정보를 확인하십시오.

예외 선언¶

예외 선언 뒤에 예외 초기화가 뒤따르는 경우가 있는데, 현재 변환은 이 두 가지를 모두 가져와서 Snowflake Scripting 예외 선언으로 병합합니다. 원본 PRAGMA EXCEPTION_INIT 은 설명이 생략됩니다.

Oracle 예외 선언 구문¶

exception_declaration::= exception EXCEPTION;

PRAGMA EXCEPTION_INIT ( exception, error_code ) ;

Snowflake Scripting 예외 선언 구문¶

<exception_name> EXCEPTION [ ( <exception_number> , '<exception_message>' ) ] ;

Oracle¶

CREATE OR REPLACE PROCEDURE procedure_exception
IS
my_exception EXCEPTION;
my_exception2 EXCEPTION;
PRAGMA EXCEPTION_INIT ( my_exception2, -20100 );
my_exception3 EXCEPTION;
PRAGMA EXCEPTION_INIT ( my_exception3, -19000 );
BEGIN
    NULL; 
END;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE procedure_exception ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        my_exception EXCEPTION;
        my_exception2 EXCEPTION (-20100, '');
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!
        PRAGMA EXCEPTION_INIT ( my_exception2, -20100 );
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/!!!
        my_exception3 EXCEPTION;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!
PRAGMA EXCEPTION_INIT ( my_exception3, -19000 );
    BEGIN
        NULL;
    END;
$$;

지원되지 않는 경우¶

다음 Oracle 선언 문은 Snowflake Scripting 선언 블록에서 지원되지 않습니다.

커서 변수 선언.
컬렉션 변수 선언.
레코드 변수 선언.
유형 정의(모든 베리언트).
함수 선언 및 정의.
프로시저 선언 및 정의.

알려진 문제¶

1. The variable declarations with NOT NULL constraints are not supported by Snow Scripting.¶

NOT NULL 제약 조건이 있는 변수를 생성하면 Snowflake Scripting에서 오류를 throw합니다.

2. The cursor declaration has no equivalent to Snowflake Scripting.¶

Oracle 커서 선언은 쓸모가 없으므로 출력 코드에서 설명이 생략될 수 있습니다. 커서 정의가 대신 사용되며 Snowflake Scripting 커서 선언으로 변환됩니다.

3. The exception code exceeds Snowflake Scripting limits.¶

Oracle 예외 코드가 Snowflake Scripting 코드 제한을 초과하는 경우 제거되고 있습니다. 예외 코드는 -20000~-20999 사이의 정수여야 합니다.

3. The not supported cases.¶

일부 Oracle 선언 문은 Snowflake Scripting 선언 블록에서 지원되지 않으므로 설명이 생략되고 경고가 추가될 수 있습니다.

DEFAULT PARAMETERS¶

이 문서에서는 기본 매개 변수의 현재 변환과 해당 기능이 에뮬레이션되는 방식에 대해 설명합니다.

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명¶

기본 매개 변수는 프로시저 또는 함수 호출에서 인자가 전달되지 않는 경우 값이 있는 매개 변수입니다. Snowflake는 기본 매개 변수를 지원하지 않으므로 SnowConvert AI는 프로시저 또는 함수 호출에 기본값을 삽입합니다.

선언에서 매개 변수의 DEFAULT VALUE 절이 제거됩니다. := 기호 및 DEFAULT 절 구문 모두 지원됩니다.

샘플 소스 패턴¶

Sample auxiliaryy code¶

Oracle¶

CREATE TABLE TABLE1(COL1 NUMBER, COL2 NUMBER);
CREATE TABLE TABLE2(COL1 NUMBER, COL2 NUMBER, COL2 NUMBER);0016

Snowflake¶

CREATE OR REPLACE TABLE TABLE1 (COL1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
COL2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE TABLE TABLE2 (COL1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
COL2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
COL2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

기본 매개 변수 선언¶

Oracle¶

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1 (
    param1 NUMBER,
    param2 NUMBER default TO_NUMBER(1)
)
AS
BEGIN 
	INSERT INTO TABLE1 (COL1, COL2)
    VALUES(param1, param2);
END;
CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS2 (
    param1 NUMBER default 1,
    param2 NUMBER default 2
)
AS
BEGIN 
	INSERT INTO TABLE1 (COL1, COL2)
    VALUES(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_DEAFAULT_PARAMS3 (
    param1 NUMBER DEFAULT 100,
    param2 NUMBER,
    param3 NUMBER DEFAULT 1000
)
IS
BEGIN
	INSERT INTO TABLE2(COL1, COL2, COL3)
    VALUES (param1, param2, param3);
END;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1 (param1 NUMBER(38, 18),
   param2 NUMBER(38, 18) DEFAULT TO_NUMBER(1)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE1(COL1, COL2)
		   VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS2 (
   param1 NUMBER(38, 18) DEFAULT 1,
   param2 NUMBER(38, 18) DEFAULT 2
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE1(COL1, COL2)
		   VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_DEAFAULT_PARAMS3 (
   param1 NUMBER(38, 18) DEFAULT 100, param2 NUMBER(38, 18),
   param3 NUMBER(38, 18) DEFAULT 1000
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE2(COL1, COL2, COL3)
		   VALUES (:param1, :param2, :param3);
	END;
$$;

기본 매개 변수를 사용하여 프로시저 호출하기¶

Oracle¶

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS
AS
BEGIN 
    PROC_WITH_DEFAULT_PARAMS1(10, 15);
    PROC_WITH_DEFAULT_PARAMS1(10);
    PROC_WITH_DEFAULT_PARAMS2(10, 15);
    PROC_WITH_DEFAULT_PARAMS2(10);
    PROC_WITH_DEFAULT_PARAMS2();
END;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        PROC_WITH_DEFAULT_PARAMS1(10, 15);
        CALL
        PROC_WITH_DEFAULT_PARAMS1(10);
        CALL
        PROC_WITH_DEFAULT_PARAMS2(10, 15);
        CALL
        PROC_WITH_DEFAULT_PARAMS2(10);
        CALL
        PROC_WITH_DEFAULT_PARAMS2();
    END;
$$;

기능이 올바르게 에뮬레이션되고 있는지 확인하기 위해 다음 쿼리는 앞서 언급한 테이블의 프로시저와 SELECT 를 실행합니다.

Oracle¶

CALL PROC_WITH_DEFAULT_CALLS();

SELECT * FROM TABLE1;

결과¶


COL1	COL2
10	15
10	1
10	15
10	2
1	2

Snowflake Scripting¶

CALL PROC_WITH_DEFAULT_CALLS();

SELECT * FROM TABLE1;

결과¶


COL1	COL2
10	15
10	1
10	15
10	2
1	2

명명된 인자와 기본 매개 변수로 프로시저 호출하기¶

Oracle¶

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS2
AS
BEGIN 
    PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param1 => 10, param2 => 20, param3 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param3 => 10, param1 => 20, param2 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param3 => 10, param2 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param2 => 10, param3 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param2 => 10);
END;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10);
    END;
$$;

기능이 올바르게 에뮬레이션되고 있는지 확인하기 위해 다음 쿼리는 앞서 언급한 테이블의 프로시저와 SELECT 를 실행합니다.

Oracle¶

CALL PROC_WITH_DEFAULT_CALLS2();

SELECT * FROM TABLE2;

결과¶


COL1	COL2	COL3
10	20	30
10	20	30
20	30	10
100	30	10
100	10	30
100	10	1000

Snowflake Scripting¶

CALL PROC_WITH_DEFAULT_CALLS2();

SELECT * FROM TABLE2;

결과¶


COL1	COL2	COL3
10	20	30
10	20	30
20	30	10
100	30	10
100	10	30
100	10	1000

Known Issues¶

1. 발견된 문제 없음

EXECUTE IMMEDIATE¶

Oracle EXECUTE IMMEDIATE 문을 Snowflake Scripting으로 변환하기 위한 변환 참조

설명¶

EXECUTE IMMEDIATE 문은 단일 작업으로 동적 SQL 문을 빌드하고 실행합니다.

기본 동적 SQL 은 EXECUTE IMMEDIATE 문을 사용하여 대부분의 동적 SQL 문을 처리합니다. (Oracle PL/SQL Language Reference EXECUTE IMMEDIATE 문)

Oracle EXECUTE IMMEDIATE 구문¶

EXECUTE IMMEDIATE <dynamic statement> [<additional clause> , ...];

dynamic statement::= { '<string literal>' | <variable> }

additional clauses::=
{ <into clause> [<using clause>]
| <bulk collect into clause> [<using clause>]
| <using clause> [<dynamic return clause>]
| <dynamic return clasue> }

일부 함수상의 차이는 있지만 Snowflake Scripting은 이 문을 지원합니다. Snowflake에 대한 자세한 내용은 Snowflake의 EXECUTE IMMEDIATE 설명서 를 참조하십시오.

Snow Scripting EXECUTE IMMEDIATE 구문¶

EXECUTE IMMEDIATE <dynamic statement> ;

dynamic statement::= {'<string literal>' | <variable> | $<session variable>}

샘플 소스 패턴¶

다음 샘플은 테이블을 생성하고 즉시 실행을 사용하여 테이블을 드롭하려고 시도합니다.

하드 코딩된 문자열 사용¶

Oracle¶

CREATE TABLE immediate_dropped_table(
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure
AS BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE immediate_dropped_table PURGE';
END;

CALL dropping_procedure();
SELECT * FROM immediate_dropped_table;

Snowflake Scripting¶

CREATE OR REPLACE TABLE immediate_dropped_table (
    col1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE dropping_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE 'DROP TABLE immediate_dropped_table';
    END;
$$;

CALL dropping_procedure();

SELECT * FROM
    immediate_dropped_table;

문자열을 변수에 저장하기¶

Oracle¶

CREATE TABLE immediate_dropped_table(
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure
AS
BEGIN
    DECLARE
        statement_variable VARCHAR2(500) := 'DROP TABLE immediate_dropped_table PURGE';
    BEGIN
        EXECUTE IMMEDIATE statement_variable;
    END;
END;

CALL dropping_procedure();
SELECT * FROM immediate_dropped_table;

Snowflake Scripting¶

CREATE OR REPLACE TABLE immediate_dropped_table (
    col1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE dropping_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DECLARE
            statement_variable VARCHAR(500) := 'DROP TABLE immediate_dropped_table';
        BEGIN
            !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
            EXECUTE IMMEDIATE :statement_variable;
        END;
    END;
$$;

CALL dropping_procedure();

SELECT * FROM
    immediate_dropped_table;

동적 문의 매개 변수 연결¶

Oracle¶

CREATE TABLE immediate_dropped_table(
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure(param1 VARCHAR2)
AS
BEGIN
    DECLARE
        statement_variable VARCHAR2(500) := 'DROP TABLE ' || param1 || ' PURGE';
    BEGIN
        EXECUTE IMMEDIATE statement_variable;
    END;
END;

CALL dropping_procedure();
SELECT * FROM immediate_dropped_table;

Snowflake Scripting¶

CREATE OR REPLACE TABLE immediate_dropped_table (
    col1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE dropping_procedure (param1 VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DECLARE
            statement_variable VARCHAR(500) := 'DROP TABLE ' || NVL(:param1 :: STRING, '');
        BEGIN
            !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
            EXECUTE IMMEDIATE :statement_variable;
        END;
    END;
$$;

CALL dropping_procedure();

SELECT * FROM
    immediate_dropped_table;

USING 절 변환¶

Oracle¶

CREATE TABLE immediate_inserted_table(COL1 INTEGER);

CREATE OR REPLACE PROCEDURE inserting_procedure_using(param1 INTEGER)
AS
BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO immediate_inserted_table VALUES (:1)' USING param1;
END;

CALL inserting_procedure_using(1);

SELECT * FROM immediate_inserted_table;

결과¶


COL1
1

Snowflake Scripting¶

참고

Snowflake Scripting의 USING 절에 있는 매개 변수에는 괄호가 필요합니다.

CREATE OR REPLACE TABLE immediate_inserted_table (COL1 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE inserting_procedure_using (param1 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE 'INSERT INTO immediate_inserted_table
VALUES (?)' USING ( param1);
    END;
$$;

CALL inserting_procedure_using(1);

SELECT * FROM
    immediate_inserted_table;

결과¶


COL1
1

Known Issues¶

1. Immediate Execution results cannot be stored in variables.¶

SnowScripting 은 INTO 또는 BULK COLLECT INTO 절을 지원하지 않습니다. 따라서 다른 수단을 통해 결과를 전달해야 합니다.

2. Numeric Placeholders¶

자리 표시자의 숫자 이름은 현재 SnowConvert AI에서 인식되지 않지만, 이 문제를 해결하기 위한 작업 항목이 있습니다.

3. Argument Expressions are not supported by Snowflake Scripting¶

Oracle에서는 식을 Using 절의 인자로 사용할 수 있지만, Snowflake Scripting에서는 지원되지 않으며 주석 처리됩니다.

4. Dynamic SQL Execution queries may be marked incorrectly as non-runnable.¶

일부 시나리오에서는 실행이 안전하거나 안전하지 않은 것과 관계없이 실행 문에 설명이 붙을 수 있으므로 이를 고려하시기 바랍니다.

Oracle¶

CREATE OR REPLACE PROCEDURE inserting_procedure_variable_execute_concatenation_parameter(param1 INTEGER)
IS
    query VARCHAR2(500) := 'INSERT INTO immediate_inserted_table VALUES (';
BEGIN
    EXECUTE IMMEDIATE query || param1 || ')';
END;

Snowflake Scripting¶

참고

Snowflake Scripting의 USING 절에 있는 매개 변수에는 괄호가 필요합니다.

CREATE OR REPLACE PROCEDURE inserting_procedure_variable_execute_concatenation_parameter (param1 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        query VARCHAR(500) := 'INSERT INTO immediate_inserted_table VALUES (';
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        !!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!
        EXECUTE IMMEDIATE NVL(:query :: STRING, '') || NVL(:param1 :: STRING, '') || ')';
    END;
$$;

EXIT¶

Oracle EXIT 문을 Snowflake Scripting으로 변환하기 위한 변환 참조

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명¶

EXIT 문은 조건부 또는 무조건적으로 루프의 현재 반복을 종료하고 현재 루프 또는 레이블이 지정된 루프의 끝으로 제어를 이전합니다.\ (Oracle PL/SQL 언어 참조 EXIT 문)

Oracle EXIT 구문¶

EXIT [ label ] [ WHEN boolean_expression ] ;

Snowflake Scripting EXIT 구문¶

{ BREAK | EXIT } [ <label> ] ;

샘플 소스 패턴¶

참고

EXIT 를 BREAK 로 변경하면 모든 것이 동일하게 작동합니다.

1. Simple Exit¶

코드에서는 EXIT 를 사용하여 INSERT 문을 건너뜁니다.

참고

이 경우는 기능적으로 동일합니다.

Oracle¶

CREATE TABLE exit_testing_table_1 (
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE exit_procedure_1
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP
        I := I + 1;
        EXIT;
        INSERT INTO exit_testing_table_1 VALUES(TO_CHAR(I));
    END LOOP;  
END;

CALL exit_procedure_1();
SELECT * FROM exit_testing_table_1;

결과¶


ITERATOR

Snowflake Scripting¶

CREATE OR REPLACE TABLE exit_testing_table_1 (
       iterator VARCHAR(5)
   )
   COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
   ;

   CREATE OR REPLACE PROCEDURE exit_procedure_1 ()
   RETURNS VARCHAR
   LANGUAGE SQL
   COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
   EXECUTE AS CALLER
   AS
   $$
       DECLARE
           I NUMBER(38, 18) := 0;
           J NUMBER(38, 18) := 20;
       BEGIN
           WHILE (:I <= :J)
                            --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                            LOOP
                                I := :I + 1;
                                EXIT;
                                       INSERT INTO exit_testing_table_1
                                VALUES(TO_CHAR(:I));
                                   END LOOP;
       END;
   $$;

   CALL exit_procedure_1();

   SELECT * FROM
       exit_testing_table_1;

결과¶


ITERATOR

2. Exit with condition¶

반복자가 5보다 크면 코드가 루프를 종료합니다.

참고

이 경우는 조건을 IF 문으로 바꾸면 기능적으로 동일합니다.

Oracle¶

CREATE TABLE exit_testing_table_2 (
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE exit_procedure_2
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP
        EXIT WHEN I > 5;
        I := I + 1;
        INSERT INTO exit_testing_table_2 VALUES(TO_CHAR(I)); 
    END LOOP;  
END;

CALL exit_procedure_2();
SELECT * FROM exit_testing_table_2;

결과¶


ITERATOR
1
2
3
4
5
6

Snowflake Scripting¶

CREATE OR REPLACE TABLE exit_testing_table_2 (
       iterator VARCHAR(5)
   )
   COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
   ;

   CREATE OR REPLACE PROCEDURE exit_procedure_2 ()
   RETURNS VARCHAR
   LANGUAGE SQL
   COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
   EXECUTE AS CALLER
   AS
   $$
       DECLARE
           I NUMBER(38, 18) := 0;
           J NUMBER(38, 18) := 20;
       BEGIN
           WHILE (:I <= :J)
                            --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                            LOOP
                                IF (:I > 5) THEN
                                    EXIT;
                                END IF;
                                I := :I + 1;
                                       INSERT INTO exit_testing_table_2
                                VALUES(TO_CHAR(:I));
                                   END LOOP;
       END;
   $$;

   CALL exit_procedure_2();

   SELECT * FROM
       exit_testing_table_2;

결과¶


ITERATOR
1
2
3
4
5
6

3. Exit with label and condition¶

코드는 외부 루프를 가리키는 EXIT 문을 사용하여 두 루프를 모두 끊습니다.

참고

이 케이스는 이전 샘플과 동일한 프로세스를 적용하여 기능적으로 동일합니다.

참고

레이블은 설명이 추가됩니다.

Oracle¶

CREATE TABLE exit_testing_table_3 (
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE exit_procedure_3
IS
I NUMBER := 0;
J NUMBER := 10;
K NUMBER := 0;
BEGIN
    <<out_loop>>
    WHILE I <= J LOOP
        I := I + 1;
        INSERT INTO exit_testing_table_3 VALUES('I' || TO_CHAR(I));

        <<in_loop>>
        WHILE K <= J * 2 LOOP
            K := K + 1;    
                EXIT out_loop WHEN K > J / 2;
            INSERT INTO exit_testing_table_3 VALUES('K' || TO_CHAR(K));
        END LOOP in_loop; 

        K := 0;
    END LOOP out_loop; 
END;

CALL exit_procedure_3();
SELECT * FROM exit_testing_table_3;

결과¶


ITERATOR
I1
K1
K2
K3
K4
K5

Snowflake Scripting¶

CREATE OR REPLACE TABLE exit_testing_table_3 (
       iterator VARCHAR(5)
   )
   COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
   ;

   CREATE OR REPLACE PROCEDURE exit_procedure_3 ()
   RETURNS VARCHAR
   LANGUAGE SQL
   COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
   EXECUTE AS CALLER
   AS
   $$
       DECLARE
           I NUMBER(38, 18) := 0;
           J NUMBER(38, 18) := 10;
           K NUMBER(38, 18) := 0;
       BEGIN
           !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<out_loop>> ***/!!!
           WHILE (:I <= :J)
                            --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                            LOOP
                                I := :I + 1;
                                       INSERT INTO exit_testing_table_3
                                VALUES('I' || NVL(TO_CHAR(:I) :: STRING, ''));
                                !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<in_loop>> ***/!!!
                                WHILE (:K <= :J * 2)
                                                     --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                                                     LOOP
                                                         K := :K + 1;
                                                         IF (:K > :J / 2) THEN
                                                             EXIT out_loop;
                                                         END IF;
                                           INSERT INTO exit_testing_table_3
                                                         VALUES('K' || NVL(TO_CHAR(:K) :: STRING, ''));
                                       END LOOP in_loop;
                                K := 0;
                                   END LOOP out_loop;
       END;
   $$;

   CALL exit_procedure_3();

   SELECT * FROM
       exit_testing_table_3;

결과¶


ITERATOR
I1
K1
K2
K3
K4
K5

Known Issues¶

문제가 발견되지 않았습니다.

EXPRESSIONS¶

Oracle 식을 Snow Scripting으로 변환하기 위한 참조

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명¶

다음 테이블에는 다양한 Oracle 식 종류 를 Snowflake Scripting로 변환하는 방법이 요약되어 있습니다.


구문	변환 상태	참고
문자 식	부분	부분적으로 지원되는 공통 시나리오
숫자 식	부분	부분적으로 지원되는 공통 시나리오
날짜 식	부분	부분적으로 지원되는 일반적인 시나리오
부울 식	부분	지원되지 않는 부울 식
단순 대/소문자 식	전체	N/A
검색된 대/소문자 식	전체	N/A
컬렉션 생성자	변환되지 않음	Snowflake에는 Oracle 컬렉션에 해당하는 네이티브가 없습니다. 컬렉션 및 레코드를 참조하세요.
적격 식	변환되지 않음	Snowflake에는 Oracle 레코드 유형에 해당하는 네이티브가 없습니다. 컬렉션 및 레코드를 참조하세요.

부분적으로 지원되는 일반적인 시나리오¶

Oracle 상수¶

Oracle¶

CREATE TABLE EXPRESSIONS_TABLE(col VARCHAR(30));
CREATE OR REPLACE PROCEDURE EXPRESSIONS_SAMPLE
IS
RESULT VARCHAR(50);
CONST CONSTANT VARCHAR(20) := 'CONSTANT TEXT';
BEGIN
	-- CONSTANT EXPRESSIONS
	RESULT := CONST;
	INSERT INTO EXPRESSIONS_TABLE(COL) VALUES (RESULT);
END;

CALL EXPRESSIONS_SAMPLE();
SELECT * FROM EXPRESSIONS_TABLE;

결과¶


COL
CONSTANT TEXT

Snowflake¶

CREATE OR REPLACE TABLE EXPRESSIONS_TABLE (col VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE EXPRESSIONS_SAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		RESULT VARCHAR(50);
		--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
		CONST VARCHAR(20) := 'CONSTANT TEXT';
	BEGIN
		-- CONSTANT EXPRESSIONS
		RESULT := :CONST;
		INSERT INTO EXPRESSIONS_TABLE(COL) VALUES (:RESULT);
	END;
$$;

CALL EXPRESSIONS_SAMPLE();

SELECT * FROM
	EXPRESSIONS_TABLE;

결과¶


COL
CONSTANT TEXT

지원되지 않는 숫자 식¶

Oracle¶

CREATE TABLE NUMERIC_EXPRESSIONS_TABLE(col number);

CREATE OR REPLACE PROCEDURE NUMERIC_EXPRESSIONS
IS
RESULT NUMBER;
CURSOR C1 IS SELECT * FROM NUMERIC_EXPRESSIONS_TABLE;
TYPE NUMERIC_TABLE IS TABLE OF NUMBER(10);
COLLECTION NUMERIC_TABLE; 
BEGIN
	-- CURSOR EXPRESSIONS
	OPEN C1;
	RESULT := C1%ROWCOUNT;
	CLOSE C1;
	INSERT INTO NUMERIC_EXPRESSIONS_TABLE(COL) VALUES (RESULT);
	
	-- ** OPERATOR
	RESULT := 10 ** 2;
	INSERT INTO NUMERIC_EXPRESSIONS_TABLE(COL) VALUES (RESULT);
	
	-- COLLECTION EXPRESSIONS
	COLLECTION := NUMERIC_TABLE(1, 2, 3, 4, 5, 6); 
	RESULT := COLLECTION.COUNT + COLLECTION.FIRST;
	INSERT INTO NUMERIC_EXPRESSIONS_TABLE(COL) VALUES (RESULT);

	-- IMPLICIT CURSOR EXPRESSIONS
	UPDATE NUMERIC_EXPRESSIONS_TABLE SET COL = COL + 4;
	RESULT := SQL%ROWCOUNT;
	INSERT INTO NUMERIC_EXPRESSIONS_TABLE(COL) VALUES (RESULT);
END;

CALL NUMERIC_EXPRESSIONS();
SELECT * FROM NUMERIC_EXPRESSIONS_TABLE;

결과¶


COL
4
104
11
3

지원되지 않는 부울 식¶

Oracle¶

--Aux function to convert BOOLEAN to VARCHAR
CREATE OR REPLACE FUNCTION convert_bool(p1 in BOOLEAN)
RETURN VARCHAR
AS
var1 VARCHAR(20) := 'FALSE';
BEGIN
IF p1 THEN
var1 := 'TRUE';
END IF;
RETURN var1;
END;

--Table
CREATE TABLE t_boolean_table
(
conditional_predicate VARCHAR(20),
collection_variable VARCHAR(20),
sql_variable VARCHAR(20)
)

--Main Procedure
CREATE OR REPLACE PROCEDURE p_boolean_limitations
AS

TYPE varray_example IS VARRAY(4) OF VARCHAR(15);
colection_example varray_example := varray_example('John', 'Mary', 'Alberto', 'Juanita');
collection_variable BOOLEAN;
conditional_predicate BOOLEAN;
sql_variable BOOLEAN;

--Result variables
col1 VARCHAR(20);
col2 VARCHAR(20);
col3 VARCHAR(20);
BEGIN

--Conditional predicate
conditional_predicate := INSERTING;

--Collection.EXISTS(index)
collection_variable := colection_example.EXISTS(2);

--Cursor FOUND / NOTFOUND / ISOPEN
sql_variable:= SQL%FOUND OR SQL%NOTFOUND OR SQL%ISOPEN;

--Convert BOOLEAN to VARCHAR to insert
col1 := convert_bool(conditional_predicate);
col2 := convert_bool(collection_variable);
col3 := convert_bool(sql_variable);

INSERT INTO t_boolean_table VALUES (col1, col2, col3);

END;

CALL p_boolean_limitations();

SELECT * FROM t_boolean_table;

FOR LOOP¶

설명¶

FOR LOOP 문이 반복될 때마다 해당 문이 실행되고 인덱스가 증가하거나 감소하며 제어가 루프의 맨 위로 돌아갑니다. (Oracle PL/SQL Language Reference FOR LOOP 문).

Oracle 구문¶

FOR
pls_identifier [ MUTABLE | IMMUTABLE ] [ constrained_type ]
[ , iterand_decl ]

IN

[ REVERSE ] iteration_control pred_clause_seq
[, qual_iteration_ctl]...

LOOP
statement... 
END LOOP [ label ] ;

Snowflake Scripting 구문¶

FOR <counter_variable> IN [ REVERSE ] <start> TO <end> { DO | LOOP }
    statement;
    [ statement; ... ]
END { FOR | LOOP } [ <label> ] ;

지정된 횟수만큼 반복하는 Snowflake Scripting(FOR LOOP)을 지원합니다. 상한과 하한은 INTEGER 여야 합니다. 자세한 내용은 Snowflake Scripting 설명서 를 참조하십시오.

Oracle FOR LOOP 동작은 문을 사용하여 수정할 수도 있습니다.

CONTINUE
EXIT
GOTO
RAISE

샘플 소스 패턴¶

1. FOR LOOP¶

참고

이 경우는 기능적으로 동일합니다.

Oracle FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P1
AS
BEGIN
    FOR i IN 1..10
    LOOP
        NULL;
    END LOOP;

    FOR i IN VAR1..VAR2
    LOOP
        NULL;
    END LOOP; 

    FOR i IN REVERSE 1+2..10+5
    LOOP
        NULL;
    END LOOP; 
END;

Snowflake Scripting FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        FOR i IN 1 TO 10
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        LOOP
            NULL;
        END LOOP;
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        FOR i IN VAR1 TO VAR2
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        LOOP
            NULL;
        END LOOP;
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        FOR i IN REVERSE 1+2 TO 10+5
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        LOOP
            NULL;
        END LOOP;
    END;
$$;

2. FOR LOOP with additional clauses¶

Oracle FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P2
AS
BEGIN
    FOR i IN 1..10 WHILE i <= 5 LOOP
        NULL;
    END LOOP;

    FOR i IN 5..15 BY 5 LOOP
        NULL;
    END LOOP;
END;

Snowflake Scripting FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR i IN 1 TO 10
                         --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                         LOOP
                                    NULL;
                                END LOOP;
                         !!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "BY" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
                         --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                         FOR i IN 5 TO 15
                                          --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                                          LOOP
                                           NULL;
                                       END LOOP;
    END;
$$;

3. FOR LOOP with multiple conditions¶

Oracle FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P3
AS
BEGIN
    FOR i IN REVERSE 1..3,
    REVERSE i+5..i+7
    LOOP
        NULL;
    END LOOP; 
END;

Snowflake Scripting FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0100 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR i IN REVERSE 1 TO 3
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        LOOP
            NULL;
        END LOOP;
    END;
$$;

4. FOR LOOP with unsupported format¶

Oracle FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P3
AS
TYPE values_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
l_employee_values   values_aat;
BEGIN
    FOR power IN REPEAT power*2 WHILE power <= 64 LOOP
        NULL;
    END LOOP;

    FOR i IN VALUES OF l_employee_values LOOP
        NULL;
    END LOOP; 
END;

Snowflake Scripting FOR LOOP 예¶

CREATE OR REPLACE PROCEDURE P3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE values_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
        l_employee_values VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'values_aat' USAGE CHANGED TO VARIANT ***/!!!;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR power IN REPEAT power*2 WHILE power <= 64
                                                      --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                                                      LOOP
            NULL;
        END LOOP;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **

        FOR i IN VALUES OF :l_employee_values
                                              --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                                              LOOP
            NULL;
        END LOOP;
    END;
$$;

경고

사용자 정의 유형에 대한 변환은 현재 Snowflake Scripting에서 지원되지 않습니다.

Known Issues¶

1. For With Multiple Conditions¶

Oracle은 단일 FOR LOOP 에 여러 조건을 허용하지만, Snowflake Scripting은 FOR LOOP 당 1개의 조건만 허용합니다. 변환 중에는 첫 번째 조건만 마이그레이션되고 다른 조건은 무시됩니다. SSC-FDM-OR0022 를 확인하십시오.

Oracle¶

FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
    NULL;
END LOOP;

Snowflake Scripting FOR LOOP 예¶

--** SSC-FDM-OR0022 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
FOR i IN REVERSE 1 TO 3 LOOP
    NULL;
END LOOP;

2. 변경 가능과 불변 카운터 변수 비교

Oracle에서는 루프 내에서 FOR LOOP 변수의 값을 수정할 수 있습니다. 현재 설명서 에는 이 기능이 포함되어 있지만 Snowflake에서는 이 기능을 사용하지 않는 것이 좋습니다. 이 변수의 값을 수정하면 Snowflake Scripting에서 올바르게 작동하지 않을 수 있습니다.

3. 상한 또는 하한에 대한 정수 대 부동 소수점 수

Snowflake Scripting은 FORLOOP 조건의 한계값으로 INTEGER 또는 INTEGER로 평가되는 식만 허용합니다. 부동 소수점 숫자는 반올림 또는 내림되어 원래 한계값을 변경합니다.

4. Oracle 미지원 절

Oracle은 FOR LOOP 조건에 추가 절을 허용합니다. 조건의 단계별 증분에 대한 BY 절 및 부울 식에 대한 WHILE과 **WHEN 절을 예로 들 수 있습니다. 이러한 추가 절은 Snowflake Scripting에서 지원되지 않으며 변환 중에 무시됩니다. SSC-EWI-OR0101을 확인하세요.

Oracle¶

FOR i IN 5..15 BY 5 LOOP
    NULL;
END LOOP;

Snowflake Scripting¶

!!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "BY" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
FOR i IN 5 TO 15 LOOP
    NULL;
END LOOP;

5. 지원되지 않는 형식

Oracle은 FOR LOOP에 대해 다양한 유형의 조건을 허용합니다. 부울 식, 컬렉션, 레코드 등을 지원합니다. 그러나 Snowflake Scripting은 정의된 정수를 한계값으로 하는 FOR LOOP만 지원합니다. 다른 모든 형식은 지원되지 않는 것으로 표시되며 변환하려면 추가적인 수동 작업이 필요합니다. SSC-EWI-OR0103을 확인하세요.

FORALL¶

설명¶

FORALL 문은 VALUES 및 WHERE 절에 다른 값을 사용하여 1개의 DML 문을 여러 번 실행합니다. (Oracle PL/SQL Language Reference FORALL 문).

Oracle 구문¶

FORALL index IN bounds_clause [ SAVE ] [ EXCEPTIONS ] dml_statement ;

경고

Snowflake Scripting은 FORALL 문과 직접적으로 동등하지는 않지만 다른 해결 방법을 사용하여 에뮬레이션하여 기능적 동등성을 얻을 수 있습니다.

샘플 소스 패턴¶

설정 데이터¶

Oracle¶

테이블 1¶

CREATE TABLE table1 (
    column1 NUMBER,
    column2 NUMBER
);

INSERT INTO table1 (column1, column2) VALUES (1, 2);
INSERT INTO table1 (column1, column2) VALUES (2, 3);
INSERT INTO table1 (column1, column2) VALUES (3, 4);
INSERT INTO table1 (column1, column2) VALUES (4, 5);
INSERT INTO table1 (column1, column2) VALUES (5, 6);

CREATE TABLE table2 (
    column1 NUMBER,
    column2 NUMBER
);

INSERT INTO table2 (column1, column2) VALUES (1, 2);

테이블 2¶

CREATE TABLE error_table (
    ORA_ERR_NUMBER$ NUMBER,
    ORA_ERR_MESG$ VARCHAR2(2000),
    ORA_ERR_ROWID$ ROWID,
    ORA_ERR_OPTYP$ VARCHAR2(2),
    ORA_ERR_TAG$ VARCHAR2(2000)
);

--departments
CREATE TABLE parent_table( 
    Id   INT PRIMARY KEY, 
    Name VARCHAR2(10) 
);
INSERT INTO parent_table VALUES (10, 'IT');
INSERT INTO parent_table VALUES (20, 'HR');
INSERT INTO parent_table VALUES (30, 'INFRA');

--employees
CREATE TABLE source_table(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  DepartmentID INT REFERENCES parent_table(Id)
);
INSERT INTO source_table VALUES (101, 'Anurag111111111', 10); 
INSERT INTO source_table VALUES (102, 'Pranaya11111111', 20); 
INSERT INTO source_table VALUES (103, 'Hina11111111111', 30);

--a copy of source
CREATE TABLE target_table(
  Id INT PRIMARY KEY,
  Name VARCHAR2(10) NOT NULL,
  DepartmentID INT REFERENCES parent_table(Id)
);

INSERT INTO target_table VALUES (101, 'Anurag', 10);

Snowflake¶

테이블 1¶

CREATE OR REPLACE TABLE table1 (
    column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
    column2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO table1(column1, column2) VALUES (1, 2);

INSERT INTO table1(column1, column2) VALUES (2, 3);

INSERT INTO table1(column1, column2) VALUES (3, 4);

INSERT INTO table1(column1, column2) VALUES (4, 5);

INSERT INTO table1(column1, column2) VALUES (5, 6);

CREATE OR REPLACE TABLE table2 (
    column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
    column2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO table2(column1, column2) VALUES (1, 2);

테이블 2¶

CREATE OR REPLACE TABLE error_table (
  "ORA_ERR_NUMBER$" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
  "ORA_ERR_MESG$" VARCHAR(2000),
  "ORA_ERR_ROWID$" VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!,
  "ORA_ERR_OPTYP$" VARCHAR(2),
  "ORA_ERR_TAG$" VARCHAR(2000)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--departments
CREATE OR REPLACE TABLE parent_table (
      Id   INT PRIMARY KEY,
      Name VARCHAR(10)
  )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO parent_table
VALUES (10, 'IT');

INSERT INTO parent_table
VALUES (20, 'HR');

INSERT INTO parent_table
VALUES (30, 'INFRA');

--employees
CREATE OR REPLACE TABLE source_table (
  Id INT PRIMARY KEY,
  Name VARCHAR(20) NOT NULL,
  DepartmentID INT REFERENCES parent_table (Id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO source_table
VALUES (101, 'Anurag111111111', 10);

INSERT INTO source_table
VALUES (102, 'Pranaya11111111', 20);

INSERT INTO source_table
VALUES (103, 'Hina11111111111', 30);

--a copy of source
CREATE OR REPLACE TABLE target_table (
  Id INT PRIMARY KEY,
  Name VARCHAR(10) NOT NULL,
  DepartmentID INT REFERENCES parent_table (Id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO target_table
VALUES (101, 'Anurag', 10);

1. FORALL With Collection of Records¶

Oracle¶

참고

아래 세 가지 케이스는 Snowflake Scripting으로 변환하는 방식이 동일하며 기능적으로도 동일합니다.

원본¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS SELECT * FROM table1;
    TYPE tableType IS TABLE OF cursorVariable%ROWTYPE;
    tableVariable tableType;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO tableVariable LIMIT 100;
        EXIT WHEN tableVariable.COUNT = 0;

        FORALL forIndex IN 1..tableVariable.COUNT
            INSERT INTO table2 (column1, column2)
            VALUES (tableVariable(forIndex).column1, tableVariable(forIndex).column2);
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2

Snowflake¶

레코드 컬렉션이 있는 FORALL¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2(column1, column2)
        (
            SELECT
                column1,
                column2
            FROM
                table1
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

참고

EWIs SSC-PRF-0001 및 SSC-PRF-0003은 FORALL 문에 FETCH BULK COLLECT 가 나타날 때마다 추가됩니다.

2. FORALL With INSERT INTO¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            INSERT INTO table2 VALUES collectionVariable(forIndex);
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                * FROM
                table1
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

3. FORALL With Multiple Fetched Collections¶

Oracle¶

INSERT INTO 포함¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    column1Collection dbms_sql.NUMBER_table;
    column2Collection dbms_sql.NUMBER_table;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO column1Collection, column2Collection limit 20;
        EXIT WHEN column1Collection.COUNT = 0;
        FORALL forIndex IN 1..column1Collection.COUNT
            INSERT INTO table2 VALUES (
                column1Collection(forIndex),
                column2Collection(forIndex)
            );
    END LOOP;
    CLOSE cursorVariable;
END;

UPDATE 포함¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    column1Collection dbms_sql.NUMBER_table;
    column2Collection dbms_sql.NUMBER_table;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO column1Collection, column2Collection limit 2;
        EXIT WHEN column1Collection.COUNT = 0;
        FORALL forIndex IN 1..column1Collection.COUNT
            UPDATE table2 SET column2 = column2Collection(forIndex)
            WHERE column1 = column1Collection(forIndex);
    END LOOP;
    CLOSE cursorVariable;
END;

결과 INSERT INTO¶


COLUMN1	COLUMN2
1	2
2	3
3	4
4	5
5	6
1	2

결과 UPDATE¶


COLUMN1	COLUMN2
1	2.

Snowflake¶

INSERT INTO 포함¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                $1,
                $2
            FROM
                table1
        );
    END;
$$;

UPDATE 포함¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        UPDATE table2
            SET column2 = column1Collection.$2
            FROM
                (
                    SELECT
                        * FROM
                        table1) AS column1Collection
            WHERE
                column1 = column1Collection.$1;
    END;
$$;

결과 INSERT INTO¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

결과 UPDATE¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000

4. FORALL With Record of Collections¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE recordType IS RECORD(
        column1Collection dbms_sql.NUMBER_table,
        column2Collection dbms_sql.NUMBER_table
    );
    columnRecord recordType;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO columnRecord.column1Collection, columnRecord.column2Collection limit 20;
        FORALL forIndex IN 1..columnRecord.column1Collection.COUNT
            INSERT INTO table2 VALUES (
                columnRecord.column1Collection(forIndex),
                columnRecord.column2Collection(forIndex)
            );
        EXIT WHEN cursorVariable%NOTFOUND;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

Scripting FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                $1,
                $2
            FROM
                table1
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

5. FORALL With Dynamic SQL¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    cursorVariable SYS_REFCURSOR;
    TYPE collectionTypeDefinition IS
        TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
    query VARCHAR(200) := 'SELECT * FROM table1';
BEGIN
    OPEN cursorVariable FOR query;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            INSERT INTO table2 VALUES collectionVariable(forIndex);
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        query VARCHAR(200) := 'SELECT * FROM
   table1';
    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TEMPORARY TABLE query AS ' || :query;
        INSERT INTO table2
        (
            SELECT
                *
            FROM
                query
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

6. FORALL With Literal SQL¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE SampleProcedure
IS
TYPE TabRecType IS RECORD (
    column1 NUMBER,
    column2 NUMBER
);
TYPE tabType IS TABLE OF TabRecType;
cursorRef SYS_REFCURSOR;
tab tabType;
BEGIN
    OPEN cursorRef FOR 'SELECT src.column1, src.column2 FROM ' || 'table1' || ' src';

    LOOP
        BEGIN
            FETCH cursorRef BULK COLLECT INTO tab LIMIT 1000;
            FORALL i IN 1..tab.COUNT
                INSERT INTO table2 (column1, column2)
                VALUES (tab(i).column1, tab(i).column2);

            EXIT WHEN cursorRef%NOTFOUND;
        END;
    END LOOP;

    CLOSE cursorRef;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE SampleProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TEMPORARY TABLE cursorRef_TEMP_TABLE AS ' || 'SELECT src.column1, src.column2 FROM ' || 'table1' || ' src';
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2(column1, column2)
        (
            SELECT
                *
            FROM
                cursorRef_TEMP_TABLE
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

7. FORALL With Parametrized Cursors¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    intVariable INTEGER := 7;
    CURSOR cursorVariable(param1 INTEGER, param2 INTEGER default 5) IS
        SELECT * FROM table1
        WHERE
            column2 = intVariable OR
            column1 BETWEEN param1 AND param2;
    TYPE collectionTypeDefinition IS
        TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable(1);
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 20;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            INSERT INTO table2 VALUES collectionVariable(forIndex);
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        intVariable INTEGER := 7;
    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                * FROM
                table1
                   WHERE
                       column2 = :intVariable
                OR
                       column1 BETWEEN 1 AND 5
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

8. FORALL Without LOOPS¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE  myProcedure IS
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    SELECT * BULK COLLECT INTO collectionVariable FROM table1;
        FORALL forIndex IN 1..collectionVariable.COUNT
            INSERT INTO table2 VALUES (
                collectionVariable (forIndex).column1,
                collectionVariable (forIndex).column2
            );
        collectionVariable.DELETE;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                column1,
                column2
            FROM
                table1
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

9. FORALL With UPDATE Statements¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            UPDATE table2 SET column1 = '54321' WHERE column2 = collectionVariable(forIndex).column2;
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
54321	2

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        UPDATE table2
            SET column1 = '54321'
            FROM
                (
                    SELECT
                        * FROM
                        table1) AS collectionVariable
            WHERE
                column2 = collectionVariable.column2;
    END;
$$;

결과¶


COLUMN1	COLUMN2
54321	2

10. FORALL With DELETE Statements¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            DELETE FROM table2 WHERE column2 = collectionVariable(forIndex).column2;
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶

no data found

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        DELETE FROM
            table2
        USING (
            SELECT
                * FROM
                table1) collectionVariable
                WHERE
            table2.column2 = collectionVariable.column2;
    END;
$$;

결과¶

Query produced no results

11. FORALL With PACKAGE References¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PACKAGE MyPackage AS
    TYPE collectionTypeDefinition IS
        TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
END;
/
 
CREATE OR REPLACE PROCEDURE InsertIntoPackage(param integer) IS
BEGIN
    SELECT
        param,
        param BULK COLLECT INTO MyPackage.collectionVariable
    FROM
        DUAL;
END;
/
 
CREATE OR REPLACE PROCEDURE InsertUsingPackage IS
BEGIN
        FORALL forIndex IN MyPackage.collectionVariable.FIRST..MyPackage.collectionVariable.LAST
            INSERT INTO table2 VALUES MyPackage.collectionVariable(forIndex);
        MyPackage.collectionVariable.DELETE;
END;
/

DECLARE
    param_value INTEGER := 10;
BEGIN
    InsertIntoPackage(param_value);
    InsertUsingPackage;
END;

select * from table2;

결과¶


COLUMN1	COLUMN2
1	2
10	10

Snowflake¶

FORALL의 해당 항목¶

CREATE SCHEMA IF NOT EXISTS MyPackage
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

!!!RESOLVE EWI!!! /*** SSC-EWI-OR0049 - PACKAGE TYPE DEFINITIONS in stateful package MyPackage are not supported yet ***/!!!
TYPE collectionTypeDefinition IS
    TABLE OF table1%ROWTYPE;

CREATE OR REPLACE TEMPORARY TABLE MYPACKAGE_COLLECTIONVARIABLE (
);

CREATE OR REPLACE PROCEDURE InsertIntoPackage (param integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        DELETE FROM
            MYPACKAGE_COLLECTIONVARIABLE;
        INSERT INTO MYPACKAGE_COLLECTIONVARIABLE
        (
            SELECT
                :param,
                :param
            FROM
        DUAL
        );
    END;
$$;

CREATE OR REPLACE PROCEDURE InsertUsingPackage ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                *
            FROM
                MYPACKAGE_COLLECTIONVARIABLE
        );
    END;
$$;

DECLARE
    param_value INTEGER := 10;
    call_results VARIANT;
BEGIN
    CALL
    InsertIntoPackage(:param_value);
    CALL
    InsertUsingPackage();
    RETURN call_results;
END;

select * from
    table2;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
10.000000000000000000	10.000000000000000000

경고

위의 변환은 패키지에 정의된 변수가 컬렉션의 레코드인 경우에만 작동합니다.

12. FORALL With MERGE Statements¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE collectionTypeDefinition IS
        TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
        MERGE INTO table2 tgt
            USING (
                SELECT
                    collectionVariable(forIndex).column1 column1,
                    collectionVariable(forIndex).column2 column2
                FROM DUAL
            ) src
           ON (tgt.column1 = src.column1)
        WHEN MATCHED THEN
            UPDATE SET
               tgt.column2 = src.column2 * 2
        WHEN NOT MATCHED THEN
            INSERT (column1, column2)
            VALUES (src.column1, src.column2);
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	4
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        MERGE INTO table2 tgt
            USING (
                SELECT
                    collectionVariable.column1 column1,
                    collectionVariable.column2 column2
                FROM
                    (
                        SELECT
                            * FROM
                            table1
                    ) collectionVariable
            ) src
           ON (tgt.column1 = src.column1)
        WHEN MATCHED THEN
            UPDATE SET
               tgt.column2 = src.column2 * 2
        WHEN NOT MATCHED THEN
            INSERT (column1, column2)
            VALUES (src.column1, src.column2);
    END;
$$;

결과¶


COLUMN1	COLUMN2
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000
1.000000000000000000	4.000000000000000000

경고

위의 변환은 MERGE 내부의 SELECT 문이 DUAL 테이블에서 선택되는 경우에만 작동합니다.

13. Default FORALL transformation¶

참고

대량 커서 헬퍼에 관심이 있을 수도 있습니다.

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS SELECT * FROM table1;
    TYPE columnsRecordType IS RECORD (column1 dbms_sql.NUMBER_table, column2 dbms_sql.NUMBER_table);
    recordVariable columnsRecordType;
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
    col1 dbms_sql.NUMBER_table;
    col2 dbms_sql.NUMBER_table;
BEGIN
    OPEN cursorVariable;
    FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
    FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
        INSERT INTO table2 (column1, column2)
        VALUES (collectionVariable(forIndex).column1, collectionVariable(forIndex).column2);

    FETCH cursorVariable BULK COLLECT INTO col1, col2 limit 2;
    FORALL forIndex IN col1.FIRST..col1.LAST
        INSERT INTO table2 (column1, column2)
        VALUES (col1(forIndex), col2(forIndex));

    LOOP
        FETCH cursorVariable BULK COLLECT INTO recordVariable limit 2;
        EXIT WHEN recordVariable.column1.COUNT = 0;
        FORALL forIndex IN recordVariable.column1.FIRST..recordVariable.column1.LAST
            INSERT INTO table2 (column1, column2)
            VALUES (recordVariable.column1(forIndex), recordVariable.column2(forIndex));
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "table1", "table2" **
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        cursorVariable OBJECT := INIT_CURSOR_UDF('cursorVariable', '   SELECT * FROM
      table1');
        !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
           TYPE columnsRecordType IS RECORD (column1 dbms_sql.NUMBER_table, column2 dbms_sql.NUMBER_table);
           recordVariable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - columnsRecordType DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--           TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
           collectionVariable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collectionTypeDefinition' USAGE CHANGED TO VARIANT ***/!!!;
           col1 VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'dbms_sql.NUMBER_table' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/;
           col2 VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'dbms_sql.NUMBER_table' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/;
        FORALL INTEGER;
    BEGIN
        cursorVariable := (
            CALL OPEN_BULK_CURSOR_UDF(:cursorVariable)
        );
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        cursorVariable := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:cursorVariable, 2)
        );
        collectionVariable := :cursorVariable:RESULT;
        FORALL := ARRAY_SIZE(:collectionVariable);
        INSERT INTO table2(column1, column2)
        (
            SELECT
                :collectionVariable[forIndex]:column1,
                : collectionVariable[forIndex]:column2
            FROM
                (
                    SELECT
                        seq4() AS forIndex
                    FROM
                        TABLE(GENERATOR(ROWCOUNT => :FORALL))
                )
        );
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        cursorVariable := (
            CALL FETCH_BULK_COLLECTIONS_UDF(:cursorVariable, 2)
        );
        col1 := :cursorVariable:RESULT[0];
        col2 := :cursorVariable:RESULT[1];
        FORALL := ARRAY_SIZE(:col1);
        INSERT INTO table2(column1, column2)
        (
            SELECT
                :col1[forIndex],
                : col2[forIndex]
            FROM
                (
                    SELECT
                        seq4() AS forIndex
                    FROM
                        TABLE(GENERATOR(ROWCOUNT => :FORALL))
                )
        );
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **

        LOOP
            --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
            --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
            cursorVariable := (
                CALL FETCH_BULK_RECORD_COLLECTIONS_UDF(:cursorVariable, 2)
            );
            recordVariable := :cursorVariable:RESULT;
            IF (ARRAY_SIZE(:recordVariable:column1) = 0) THEN
                EXIT;
            END IF;
            FORALL := ARRAY_SIZE(:recordVariable:column1);
            INSERT INTO table2(column1, column2)
            (
                SELECT
                    :recordVariable:column1[forIndex],
                    : recordVariable:column2[forIndex]
                FROM
                    (
                        SELECT
                            seq4() AS forIndex
                        FROM
                            TABLE(GENERATOR(ROWCOUNT => :FORALL))
                    )
            );
        END LOOP;
        cursorVariable := (
            CALL CLOSE_BULK_CURSOR_UDF(:cursorVariable)
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

참고

이 변환은 앞서 언급한 변환을 수행할 수 없는 경우에만 수행됩니다.

14. Multiple FORALL inside a LOOP clause¶

참고

이 패턴은 동일한 프로시저에 FORALL 이 2개 이상 있고 다음 구조를 충족하는 경우에 적용됩니다.

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;

    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 20;
        EXIT WHEN collectionVariable.COUNT = 0;

        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            INSERT INTO table2 VALUES collectionVariable(forIndex);
        
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            UPDATE table2 SET column1 = '54321' WHERE column2 = collectionVariable(forIndex).column2;

    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
54321	2
54321	2
54321	3
54321	4
54321	5
54321	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                * FROM
                table1
        );
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        UPDATE table2
            SET column1 = '54321'
            FROM
                (
                    SELECT
                        * FROM
                        table1) AS collectionVariable
            WHERE
                column2 = collectionVariable.column2;
    END;
$$;

결과¶


COLUMN1	COLUMN2
54321	2
54321	2
54321	3
54321	4
54321	5
54321	6

15. Multiple FORALL inside different LOOP clauses¶

참고

이 패턴은 동일한 프로시저에 FORALL 이 2개 이상 있고 다음 구조를 충족하는 경우에 적용됩니다.

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;

    CURSOR cursorVariable2 IS
        SELECT * FROM table1;

    TYPE collectionTypeDefinition IS
        TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;

    TYPE collectionTypeDefinition2 IS
        TABLE OF table1%ROWTYPE;
    collectionVariable2 collectionTypeDefinition2;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            INSERT INTO table2 VALUES collectionVariable(forIndex);
    END LOOP;
    CLOSE cursorVariable;

    OPEN cursorVariable2;
    LOOP
        FETCH cursorVariable2 BULK COLLECT INTO collectionVariable2 limit 2;
        EXIT WHEN collectionVariable2.COUNT = 0;
        FORALL forIndex IN collectionVariable2.FIRST..collectionVariable2.LAST
            UPDATE table2 SET column1 = '54321' WHERE column2 = collectionVariable2(forIndex).column2;
    END LOOP;
    CLOSE cursorVariable2;
END;

결과¶


COLUMN1	COLUMN2
54321	2
54321	2
54321	3
54321	4
54321	5
54321	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                * FROM
                table1
        );
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        UPDATE table2
            SET column1 = '54321'
            FROM
                (
                    SELECT
                        * FROM
                        table1) AS collectionVariable2
            WHERE
                column2 = collectionVariable2.column2;
    END;
$$;

결과¶


COLUMN1	COLUMN2
54321	2
54321	2
54321	3
54321	4
54321	5
54321	6

16. FORALL with MERGE INTO with LOG ERRORS¶

경고

이 패턴은 아직 구현되지 않았습니다

Oracle¶

LOG ERRORS¶

CREATE OR REPLACE PROCEDURE procedure_example (
    department_id_in   IN source_table.DepartmentID%TYPE)
IS
    TYPE employee_ids_t IS TABLE OF source_table%ROWTYPE
    INDEX BY PLS_INTEGER; 
    employee_list   employee_ids_t;
BEGIN
    SELECT *
        BULK COLLECT INTO employee_list
        FROM source_table
        WHERE DepartmentID = procedure_example.department_id_in;
    
    FORALL indx IN 1 .. employee_list.COUNT
      MERGE INTO target_table 
      USING (SELECT * FROM DUAL) src
      ON (id = employee_list(indx).id)
      WHEN MATCHED THEN
        UPDATE SET
          name = employee_list(indx).Name
      WHEN NOT MATCHED THEN
        INSERT (Id, Name, DepartmentID)
        VALUES (employee_list(indx).Id, employee_list(indx).Name, employee_list(indx).DepartmentID)
      LOG ERRORS INTO error_table('MERGE INTO ERROR') 
      REJECT LIMIT UNLIMITED;
        
END;

CALL procedure_example(10);

select * from target_table;
select * from error_table;

Snowflake¶

LOG ERRORS¶

--Generated by SnowConvert---------------
CREATE OR REPLACE TRANSIENT TABLE target_staging_table(
  Id INT PRIMARY KEY,
  Name VARCHAR2(10) NOT NULL,
  DepartmentID INT REFERENCES parent_table(Id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--Generated by SnowConvert---------------

CREATE OR REPLACE PROCEDURE procedure_example (DEPARTMENT_ID_IN INT !!!RESOLVE EWI!!! /*** SSC-EWI-OR0129 - TYPE ATTRIBUTE 'source_table.DepartmentID%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/!!!)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$  
    BEGIN
        CREATE OR REPLACE TEMP TABLE SOURCE_TEMPORAL AS
        WITH source_data as (
            SELECT *
            FROM source_table
            WHERE DEPARTMENTID =: DEPARTMENT_ID_IN
        )
        SELECT source_data.*, parent_table.id as PARENT_KEY 
        FROM source_data 
        left join parent_table on source_data.DepartmentID = parent_table.id;
        
        --All records violating foreign key integrity
        INSERT INTO error_table (ERROR, COLUMN_NAME, REJECTED_RECORD)
        SELECT 
            'Foreign Key Constraint Violated' ERROR,'KEY_COL' COLUMN_NAME, id
        FROM SOURCE_TEMPORAL 
        WHERE PARENT_KEY IS NULL;


        DELETE FROM SOURCE_TEMPORAL 
        WHERE PARENT_KEY IS NULL;

        BEGIN
            MERGE INTO target_table
            USING SOURCE_TEMPORAL SRC
            ON SRC.id = target_table.id
            WHEN MATCHED THEN
                UPDATE SET 
                    name = SRC.name
            WHEN NOT MATCHED THEN
               INSERT (Id, Name, DepartmentID)
               VALUES (SRC.Id, SRC.Name, SRC.DepartmentID);
        EXCEPTION
            WHEN OTHER THEN
                CREATE OR REPLACE TEMPORARY STAGE my_int_stage
                  COPY_OPTIONS = (ON_ERROR='continue');
                
                --Create my file and populate with data
                COPY INTO @my_int_stage/my_file FROM (
                SELECT  * exclude(PARENT_KEY) FROM SOURCE_TEMPORAL
                ) OVERWRITE = TRUE ;

                COPY INTO target_staging_table(id, name, DepartmentID) 
                FROM (
                  SELECT 
                    -- distinct
                    t.$1, t.$2, t.$3 
                  FROM @my_int_stage/my_file t
                  ) ON_ERROR = CONTINUE;

                INSERT INTO ERROR_TABLE (ERROR, FILE, LINE, CHARACTER, CATEGORY, CODE, SQL_STATE, COLUMN_NAME, ROW_NUMBER, REJECTED_RECORD)
                SELECT 
                    ERROR, FILE,LINE, CHARACTER, CATEGORY, CODE, SQL_STATE, COLUMN_NAME, ROW_NUMBER, REJECTED_RECORD
                FROM TABLE(VALIDATE(target_staging_table, JOB_ID => '_last')) order by line; --The last charge on the current session

                MERGE INTO target_table
                USING target_staging_table staging
                ON staging.id = target_table.id
                WHEN MATCHED THEN
                    UPDATE SET 
                        name = staging.name
                WHEN NOT MATCHED THEN
                INSERT (Id, Name, DepartmentID)
                VALUES (staging.Id, staging.Name, staging.DepartmentID);
        END;

        return 'Awesome!';
    END;
$$;

CALL procedure_example(10);

SELECT * FROM target_table;
SELECT * FROM error_table;

17. FORALL with INSERT with LOG ERRORS¶

경고

이 패턴은 아직 구현되지 않았습니다

Oracle¶

LOG ERRORS¶

CREATE OR REPLACE PROCEDURE procedure_example (
    department_id_in   IN source_table.DepartmentID%TYPE)
IS
    TYPE employee_ids_t IS TABLE OF source_table%ROWTYPE
    INDEX BY PLS_INTEGER; 
    employee_list   employee_ids_t;
BEGIN
    SELECT *
        BULK COLLECT INTO employee_list
        FROM source_table
        WHERE DepartmentID = procedure_example.department_id_in;
    
    FORALL indx IN 1 .. employee_list.COUNT
        INSERT INTO target_table(Id, Name, DepartmentID)
        VALUES (employee_list(indx).Id, employee_list(indx).Name, employee_list(indx).DepartmentID)
        LOG ERRORS INTO error_table('MERGE INTO ERROR') 
        REJECT LIMIT UNLIMITED;
END;

Snowflake¶

LOG ERRORS¶

--Generated by SnowConvert---------------
CREATE OR REPLACE TRANSIENT TABLE target_staging_table(
  Id INT PRIMARY KEY,
  Name VARCHAR2(10) NOT NULL,
  DepartmentID INT REFERENCES parent_table(Id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--Generated by SnowConvert---------------

CREATE OR REPLACE PROCEDURE procedure_example (DEPARTMENT_ID_IN INT !!!RESOLVE EWI!!! /*** SSC-EWI-OR0129 - TYPE ATTRIBUTE 'employees.DepartmentID%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/!!!)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$  
    BEGIN
        CREATE OR REPLACE TEMP TABLE SOURCE_TEMPORAL AS
        WITH source_data as (
            SELECT *
            FROM source_table
            WHERE DEPARTMENTID =: DEPARTMENT_ID_IN
        )
        SELECT source_data.*, parent_table.id as PARENT_KEY 
        FROM source_data 
        left join parent_table on source_data.DepartmentID = parent_table.id;
        
        --All records violating foreign key integrity
        INSERT INTO error_table (ERROR, COLUMN_NAME, REJECTED_RECORD)
        SELECT 
            'Foreign Key Constraint Violated' ERROR,'KEY_COL' COLUMN_NAME, id
        FROM SOURCE_TEMPORAL 
        WHERE PARENT_KEY IS NULL;


        DELETE FROM SOURCE_TEMPORAL 
        WHERE PARENT_KEY IS NULL;

        BEGIN
            INSERT INTO target_table (Id, Name, DepartmentID)
            SELECT SRC.Id, SRC.Name, SRC.DepartmentID FROM SOURCE_TEMPORAL SRC;
        EXCEPTION
            WHEN OTHER THEN
                CREATE OR REPLACE TEMPORARY STAGE my_int_stage
                  COPY_OPTIONS = (ON_ERROR='continue');
                
                --Create my file and populate with data
                COPY INTO @my_int_stage/my_file FROM (
                SELECT  * exclude(PARENT_KEY) FROM SOURCE_TEMPORAL
                ) OVERWRITE = TRUE ;

                COPY INTO target_staging_table(id, name, DepartmentID) 
                FROM (
                  SELECT 
                    -- distinct
                    t.$1, t.$2, t.$3 
                  FROM @my_int_stage/my_file t
                  ) ON_ERROR = CONTINUE;

                INSERT INTO ERROR_TABLE (ERROR, FILE, LINE, CHARACTER, CATEGORY, CODE, SQL_STATE, COLUMN_NAME, ROW_NUMBER, REJECTED_RECORD)
                SELECT 
                    ERROR, FILE,LINE, CHARACTER, CATEGORY, CODE, SQL_STATE, COLUMN_NAME, ROW_NUMBER, REJECTED_RECORD
                FROM TABLE(VALIDATE(target_staging_table, JOB_ID => '_last')) order by line; --The last charge on the current session

                INSERT INTO target_table (Id, Name, DepartmentID)
                SELECT staging.Id, staging.Name, staging.DepartmentID FROM target_staging_table staging;
        END;
    END;
$$;

CALL procedure_example(10);

SELECT * FROM target_table;
SELECT * FROM error_table;

Known Issues¶

문제가 발견되지 않았습니다.

IF¶

설명¶

IF 문은 BOOLEAN 식의 값에 따라 1개 이상의 문으로 구성된 시퀀스를 실행하거나 건너뜁니다. Oracle IF 에 대한 자세한 내용은 여기 에서 확인할 수 있습니다.

IF boolean_expression THEN 
    statement 
    [ statement ]...
[ 
ELSIF boolean_expression THEN 
    statement 
    [ statement ]... ]...
   [ 
ELSE 
statement [ statement ]... ] END IF ;

IF ( <condition> ) THEN
    <statement>;
    [ <statement>; ... ]
[
ELSEIF ( <condition> ) THEN
    <statement>;
    [ <statement>; ... ]
]
[
ELSE
    <statement>;
    [ <statement>; ... ]
]
END IF;

샘플 소스 패턴¶

Sample auxiliary table¶

CREATE TABLE if_table(col1 varchar(30));

CREATE OR REPLACE TABLE PUBLIC.if_table (col1 varchar(30));

가능한 IF 변형¶

Oracle¶

코드 1¶

CREATE OR REPLACE PROCEDURE ifExample1 ( flag NUMBER )
IS
BEGIN
    IF flag = 1 THEN
        INSERT INTO if_table(col1) VALUES ('one');
    END IF;
END;

CALL ifExample1(1);
SELECT * FROM if_table;

코드 2¶

CREATE OR REPLACE PROCEDURE ifExample2 ( flag NUMBER )
IS
BEGIN
    IF flag = 1 THEN
        INSERT INTO if_table(col1) VALUES ('one');
    ELSE
        INSERT INTO if_table(col1) VALUES ('Unexpected input.');
    END IF;
END;

CALL ifExample2(2);
SELECT * FROM if_table;

코드 3¶

CREATE OR REPLACE PROCEDURE ifExample3 ( flag NUMBER )
IS
BEGIN
    IF flag = 1 THEN
        INSERT INTO if_table(col1) VALUES ('one');
    ELSIF flag = 2 THEN
        INSERT INTO if_table(col1) VALUES ('two');
    ELSIF flag = 3 THEN
        INSERT INTO if_table(col1) VALUES ('three');
    END IF;
END;

CALL ifExample3(3);
SELECT * FROM if_table;

코드 4¶

CREATE OR REPLACE PROCEDURE ifExample4 ( flag NUMBER )
IS
BEGIN
    IF flag = 1 THEN
        INSERT INTO if_table(col1) VALUES ('one');
    ELSIF flag = 2 THEN
        INSERT INTO if_table(col1) VALUES ('two');
    ELSIF flag = 3 THEN
        INSERT INTO if_table(col1) VALUES ('three');
    ELSE
        INSERT INTO if_table(col1) VALUES ('Unexpected input.');  
    END IF;
END;

CALL ifExample4(4);
SELECT * FROM if_table;

결과 1¶


COL1
하나

결과 2¶


COL1
예기치 않은 입력입니다.

결과 3¶


COL1
3개

결과 4¶


COL1
예기치 않은 입력입니다.

Snowflake Scripting¶

코드 1¶

CREATE OR REPLACE PROCEDURE ifExample1 (flag NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        IF (:flag = 1) THEN
            INSERT INTO if_table(col1) VALUES ('one');
        END IF;
    END;
$$;

CALL ifExample1(1);

SELECT * FROM
    if_table;

코드 2¶

CREATE OR REPLACE PROCEDURE ifExample2 (flag NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        IF (:flag = 1) THEN
            INSERT INTO if_table(col1) VALUES ('one');
        ELSE
            INSERT INTO if_table(col1) VALUES ('Unexpected input.');
        END IF;
    END;
$$;

CALL ifExample2(2);

SELECT * FROM
    if_table;

코드 3¶

CREATE OR REPLACE PROCEDURE ifExample3 (flag NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        IF (:flag = 1) THEN
            INSERT INTO if_table(col1) VALUES ('one');
        ELSEIF (:flag = 2) THEN
            INSERT INTO if_table(col1) VALUES ('two');
        ELSEIF (:flag = 3) THEN
            INSERT INTO if_table(col1) VALUES ('three');
        END IF;
    END;
$$;

CALL ifExample3(3);

SELECT * FROM
    if_table;

코드 4¶

CREATE OR REPLACE PROCEDURE ifExample4 (flag NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        IF (:flag = 1) THEN
            INSERT INTO if_table(col1) VALUES ('one');
        ELSEIF (:flag = 2) THEN
            INSERT INTO if_table(col1) VALUES ('two');
        ELSEIF (:flag = 3) THEN
            INSERT INTO if_table(col1) VALUES ('three');
        ELSE
            INSERT INTO if_table(col1) VALUES ('Unexpected input.');
        END IF;
    END;
$$;

CALL ifExample4(4);

SELECT * FROM if_table;

결과 1¶


COL1
하나

결과 2¶


COL1
예기치 않은 입력입니다.

결과 3¶


COL1
3개

결과 4¶


COL1
예기치 않은 입력입니다.

알려진 문제¶

문제가 발견되지 않았습니다.

IS EMPTY¶

Oracle IS EMPTY 문을 Snowflake로 변환하기 위한 변환 참조

경고

이 섹션은 진행 중인 작업으로 향후 정보가 변경될 수 있습니다.

설명¶

컬렉션의 요소가 NULL 인지 여부에 관계없이 지정된 중첩된 테이블이 비어 있는지 테스트하려면 IS [NOT] EMPTY 조건을 사용합니다. (설명서).

Oracle 구문¶

nested_table IS [ NOT ] EMPTY

샘플 소스 패턴¶

Oracle¶

다음 예제는 IS EMPTY 문의 사용법을 보여줍니다. 이 문은 UDT 를 정의 유형으로 사용하는 중첩된 테이블 위에 적용됩니다. 출력에는 전화번호가 없는 직원의 이름이 표시됩니다.

CREATE TYPE phone_number_type AS OBJECT (phone_number VARCHAR2(30));
/

CREATE TYPE phone_number_list AS TABLE OF phone_number_type;

CREATE TABLE employee (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    phone_numbers_col phone_number_list
) NESTED TABLE phone_numbers_col STORE AS nested_tab return as value;

INSERT INTO employee VALUES (
    1,
    'John Doe',
    phone_number_list(phone_number_type('1234567890'))
);
/

INSERT INTO employee VALUES (
    2,
    'Jane Smith',
    phone_number_list()
);

SELECT emp_name
FROM employee
WHERE phone_numbers_col IS EMPTY;

출력¶


EMP_NAME
제인 스미스

Snowflake¶

아래 표시된 Snowflake 쿼리는 IS EMPTY 문의 기능과 동일합니다. 특히 IS EMPTY 문은 NULL 오브젝트와 EMPTY 오브젝트 사이에 차이가 있습니다.

사용자 정의 유형이 VARIANT 로 변환된 것을 확인할 수 있습니다. Snowflake의 VARIANT 유형은 오브젝트와 배열을 저장할 수 있습니다. 중첩된 테이블은 정보의 시퀀스이므로 ARRAY 유형이 이를 재정의하고 ARRAY 오브젝트가 비어 있는지 확인하는 데 가장 적합한 유형입니다.

ARRAY_SIZE 동등 솔루션은 중첩된 테이블의 무효성을 요청할 수도 있습니다(VARIANT 로 변환됨). 즉, VARIANT 유형은 NULLs 을 저장하고 ARRAYs 를 비워둘 수도 있습니다.

!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE phone_number_type AS OBJECT (phone_number VARCHAR2(30))
;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE' NODE ***/!!!

CREATE TYPE phone_number_list AS TABLE OF phone_number_type;

CREATE OR REPLACE TABLE employee (
    emp_id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
    emp_name VARCHAR(50),
    phone_numbers_col VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'phone_number_list' USAGE CHANGED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE VIEW PUBLIC.employee_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
AS
SELECT
    emp_id,
    emp_name,
    phone_numbers_col
FROM
    employee;

INSERT INTO employee
VALUES (
    1,
    'John Doe',
    phone_number_list(phone_number_type('1234567890') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_type' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_list' NODE ***/!!!
);

INSERT INTO employee
VALUES (
    2,
    'Jane Smith',
    phone_number_list() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_list' NODE ***/!!!
);

SELECT emp_name
FROM
    employee
WHERE
    ARRAY_SIZE( phone_numbers_col) = 0;

출력¶


EMP_NAME
제인 스미스

기타 가능한 조합¶

Description	Oracle	Snowflake
Ask for a IS NOT EMPTY	`(...) WHERE phone_numbers_col IS NOT EMPTY;`	`(...) WHERE ARRAY_SIZE(phone_numbers_col) != 0;`
Ask for NULL instead of EMPTY	`(...) WHERE phone_numbers_col IS NULL;`	`(...) WHERE ARRAY_SIZE(phone_numbers_col) IS NULL;`

Known Issues¶

1. 사용자 정의 유형은 베리언트로 변환됩니다.¶

사용자 정의 유형은 지원되지 않으므로 일부 함수를 보장하기 위해 수작업이 필요할 수 있는 베리언트 유형으로 변환됩니다.

자세한 내용은 다음 페이지를 참조하십시오.

create-type-statement

2. 중첩 테이블은 지원되지 않습니다.¶

중첩된 테이블은 현재 지원되지 않습니다. 이 동등성에 기반한 가장 좋은 접근법은 중첩된 테이블을 베리언트로 처리하되 내부에 JSON 데이터가 있는 배열을 선언하고 PARSE_JSON Snowflake 함수를 실행하여 중첩된 정보를 채우는 것입니다.

자세한 내용은 다음 페이지를 참조하십시오.

nested-table-array-type-definition.md

nested-table-type-definition.md

3. Insert 문은 사용자 정의 유형에 지원되지 않습니다.¶

사용자 정의 유형은 지원되지 않으므로 결과적으로 이러한 유형에 대한 삽입 문은 지원되지 않습니다. 특히 중첩된 테이블의 경우 해당 패턴에서 ARRAY_CONSTRUCT 함수가 사용될 것으로 예상되므로 INSERTINTO ... VALUES 문을 INSERT INTO ...SELECT 로 변경해야 합니다.

자세한 내용은 다음 페이지를 참조하십시오.

object-type-definition.md

4. 논리는 `ARRAY` 유형에 맞게 조정되어야 합니다.¶

중첩된 테이블은 VARIANT 로 동등하게 변환되고 ARRAYs, 처럼 동작해야 하므로 프로시저 구현의 기능과 로직 및 데이터와의 상호 작용을 조정해야 합니다.

다음 예제를 검토하십시오.

4.1 프로시저 동등성¶

Oracle¶

create or replace procedure proc1
as
    col1 phone_number_list:= phone_number_list();
begin
   IF col1 IS EMPTY
   THEN
    dbms_output.put_line('IS EMPTY');
   END IF;
end;

Snowflake¶

CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      col1 VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'phone_number_list' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := phone_number_list() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_list' NODE ***/!!!;
   BEGIN
      IF (ARRAY_SIZE(:col1) = 0) THEN
         --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
         CALL DBMS_OUTPUT.PUT_LINE_UDF('IS EMPTY');
      END IF;
   END;
$$;

출력¶


PROC1
IS EMPTY

4.2 문 선택¶

출력은 ARRAYs 에 따라 테이블과 다를 수 있습니다.

Oracle¶

SELECT
    t.*
FROM
    employee e,
    table(e.phone_numbers_col) t
WHERE
    emp_id = 1;

출력¶


PHONE_NUMBER
1234567890

Snowflake¶

SELECT
    t.*
FROM
    employee e,
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0035 - TABLE FUNCTION IS NOT SUPPORTED WHEN IT IS USED AS A COLLECTION OF EXPRESSIONS ***/!!!
    table(e.phone_numbers_col) t
WHERE
    emp_id = 1;

출력¶


PHONE_NUMBERS_COL
[ 1234567890 ]

LOCK TABLE¶

참고

설명¶

Oracle에서는 LOCK TABLE 문을 사용하여 지정된 테이블에 대한 공유 또는 독점 테이블 잠금을 명시적으로 획득할 수 있습니다. 테이블 잠금은 현재 트랜잭션이 끝날 때까지 지속됩니다. 자세한 내용은 여기 에서 확인할 수 있습니다.

구문

LOCK TABLE tableName IN { SHARE | EXCLUSIVE } MODE

샘플 소스 패턴¶

잠금 테이블¶

이 예제에서는 LOCK TABLE 문이 삭제된 것을 확인할 수 있습니다. 이는 Snowflake가 트랜잭션을 통해 다른 방식으로 잠금을 처리하기 때문입니다.

Oracle¶

LOCK TABLE table1 IN EXCLUSIVE MODE;

Snowflake¶

[Empty output]

LOG ERROR¶

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명¶

FORALL 문은 VALUES 및 WHERE 절에 다른 값을 사용하여 1개의 DML 문을 여러 번 실행합니다. (Oracle PL/SQL Language Reference FORALL 문).

Oracle 구문¶

FORALL index IN bounds_clause [ SAVE ] [ EXCEPTIONS ] dml_statement ;

경고

Snowflake Scripting은 FORALL 문과 직접적으로 동등하지는 않지만 다른 해결 방법을 사용하여 에뮬레이션하여 기능적 동등성을 얻을 수 있습니다.

샘플 소스 패턴¶

설정 데이터¶

Oracle¶

테이블¶

CREATE TABLE error_table (
    ORA_ERR_NUMBER$ NUMBER,
    ORA_ERR_MESG$ VARCHAR2(2000),
    ORA_ERR_ROWID$ ROWID,
    ORA_ERR_OPTYP$ VARCHAR2(2),
    ORA_ERR_TAG$ VARCHAR2(2000)
);

--departments
CREATE TABLE parent_table(
    Id INT PRIMARY KEY,
    Name VARCHAR2(10)
);

INSERT INTO parent_table VALUES (10, 'IT');
INSERT INTO parent_table VALUES (20, 'HR');
INSERT INTO parent_table VALUES (30, 'INFRA');

--employees
CREATE TABLE source_table(
    Id INT PRIMARY KEY,
    Name VARCHAR2(20) NOT NULL,
    DepartmentID INT REFERENCES parent_table(Id)
);

INSERT INTO source_table VALUES (101, 'Anurag111111111', 10); 
INSERT INTO source_table VALUES (102, 'Pranaya11111111', 20); 
INSERT INTO source_table VALUES (103, 'Hina11111111111', 30);

--a copy of source
CREATE TABLE target_table(
    Id INT PRIMARY KEY,
    Name VARCHAR2(10) NOT NULL,
    DepartmentID INT REFERENCES parent_table(Id)
);

INSERT INTO target_table VALUES (101, 'Anurag', 10);

Snowflake¶

테이블¶

CREATE OR REPLACE TABLE error_table (
    "ORA_ERR_NUMBER$" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
    "ORA_ERR_MESG$" VARCHAR(2000),
    "ORA_ERR_ROWID$" VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!,
    "ORA_ERR_OPTYP$" VARCHAR(2),
    "ORA_ERR_TAG$" VARCHAR(2000)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--departments
CREATE OR REPLACE TABLE parent_table (
        Id INT PRIMARY KEY,
        Name VARCHAR(10)
    )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO parent_table
VALUES (10, 'IT');

INSERT INTO parent_table
VALUES (20, 'HR');

INSERT INTO parent_table
VALUES (30, 'INFRA');

--employees
CREATE OR REPLACE TABLE source_table (
    Id INT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    DepartmentID INT REFERENCES parent_table (Id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO source_table
VALUES (101, 'Anurag111111111', 10);

INSERT INTO source_table
VALUES (102, 'Pranaya11111111', 20);

INSERT INTO source_table
VALUES (103, 'Hina11111111111', 30);

--a copy of source
CREATE OR REPLACE TABLE target_table (
    Id INT PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    DepartmentID INT REFERENCES parent_table (Id)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO target_table
VALUES (101, 'Anurag', 10);

1. MERGE INTO Inside a FORALL¶

Oracle¶

참고

아래 세 가지 케이스는 Snowflake Scripting으로 변환하는 방식이 동일하며 기능적으로도 동일합니다.

사례 1¶

CREATE OR REPLACE PROCEDURE procedure_example (
    department_id_in   IN source_table.DepartmentID%TYPE)
IS
    TYPE employee_ids_t IS TABLE OF source_table%ROWTYPE
    INDEX BY PLS_INTEGER; 
    employee_list   employee_ids_t;
BEGIN
    SELECT *
        BULK COLLECT INTO employee_list
        FROM source_table
        WHERE DepartmentID = procedure_example.department_id_in;
    
    FORALL indx IN 1 .. employee_list.COUNT
      MERGE INTO target_table 
      USING (SELECT * FROM DUAL) src
      ON (id = employee_list(indx).id)
      WHEN MATCHED THEN
        UPDATE SET
          name = employee_list(indx).Name
      WHEN NOT MATCHED THEN
        INSERT (Id, Name, DepartmentID)
        VALUES (employee_list(indx).Id, employee_list(indx).Name, employee_list(indx).DepartmentID)
      LOG ERRORS INTO error_table('MERGE INTO ERROR') 
      REJECT LIMIT UNLIMITED;
        
END;

CALL procedure_example(10);

select * from target_table;
select * from error_table;

Snowflake¶

레코드 컬렉션이 있는 FORALL¶

CREATE OR REPLACE PROCEDURE procedure_example (department_id_in VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-OR0129 - TYPE ATTRIBUTE 'source_table.DepartmentID%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/!!!)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE employee_ids_t IS TABLE OF source_table%ROWTYPE
--        INDEX BY PLS_INTEGER;
        employee_list VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'employee_ids_t' USAGE CHANGED TO VARIANT ***/!!!;
        FORALL INTEGER;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'RECORDS AND COLLECTIONS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        SELECT *
            BULK COLLECT INTO employee_list
            FROM source_table
            WHERE DepartmentID = procedure_example.department_id_in;
        FORALL := ARRAY_SIZE(:employee_list);
          MERGE INTO target_table
          USING (SELECT * FROM
                (
                    SELECT
                        seq4() AS indx
                    FROM
                        TABLE(GENERATOR(ROWCOUNT => :FORALL))
                )) src
          ON (id = : employee_list[indx]:id)
        WHEN MATCHED THEN
        UPDATE SET
          name = : employee_list[indx]:Name
        WHEN NOT MATCHED THEN
        INSERT (Id, Name, DepartmentID)
        VALUES (:employee_list[indx]:Id, : employee_list[indx]:Name, : employee_list[indx]:DepartmentID)
--        --** SSC-FDM-OR0031 - THE ERROR LOGGING CLAUSE IN DML STATEMENTS IS NOT SUPPORTED BY SNOWFLAKE **
--          LOG ERRORS INTO error_table('MERGE INTO ERROR')
--          REJECT LIMIT UNLIMITED
                                ;
    END;
$$;


CALL procedure_example(10);


select * from
    target_table;

select * from
    error_table;

2. FORALL With INSERT INTO¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            INSERT INTO table2 VALUES collectionVariable(forIndex);
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                * FROM
                table1
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

3. FORALL With Multiple Fetched Collections¶

Oracle¶

INSERT INTO 포함¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    column1Collection dbms_sql.NUMBER_table;
    column2Collection dbms_sql.NUMBER_table;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO column1Collection, column2Collection limit 20;
        EXIT WHEN column1Collection.COUNT = 0;
        FORALL forIndex IN 1..column1Collection.COUNT
            INSERT INTO table2 VALUES (
                column1Collection(forIndex),
                column2Collection(forIndex)
            );
    END LOOP;
    CLOSE cursorVariable;
END;

UPDATE 포함¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    column1Collection dbms_sql.NUMBER_table;
    column2Collection dbms_sql.NUMBER_table;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO column1Collection, column2Collection limit 2;
        EXIT WHEN column1Collection.COUNT = 0;
        FORALL forIndex IN 1..column1Collection.COUNT
            UPDATE table2 SET column2 = column2Collection(forIndex)
            WHERE column1 = column1Collection(forIndex);
    END LOOP;
    CLOSE cursorVariable;
END;

결과 INSERT INTO¶


COLUMN1	COLUMN2
1	2
2	3
3	4
4	5
5	6
1	2

결과 UPDATE¶


COLUMN1	COLUMN2
1	2

Snowflake¶

INSERT INTO 포함¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                $1,
                $2
            FROM
                table1
        );
    END;
$$;

UPDATE 포함¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        UPDATE table2
            SET column2 = column1Collection.$2
            FROM
                (
                    SELECT
                        * FROM
                        table1) AS column1Collection
            WHERE
                column1 = column1Collection.$1;
    END;
$$;

결과 INSERT INTO¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

결과 UPDATE¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000

4. FORALL With Record of Collections¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE recordType IS RECORD(
        column1Collection dbms_sql.NUMBER_table,
        column2Collection dbms_sql.NUMBER_table
    );
    columnRecord recordType;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO columnRecord.column1Collection, columnRecord.column2Collection limit 20;
        FORALL forIndex IN 1..columnRecord.column1Collection.COUNT
            INSERT INTO table2 VALUES (
                columnRecord.column1Collection(forIndex),
                columnRecord.column2Collection(forIndex)
            );
        EXIT WHEN cursorVariable%NOTFOUND;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

Scripting FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                $1,
                $2
            FROM
                table1
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

5. FORALL With Dynamic SQL¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    cursorVariable SYS_REFCURSOR;
    TYPE collectionTypeDefinition IS
        TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
    query VARCHAR(200) := 'SELECT * FROM table1';
BEGIN
    OPEN cursorVariable FOR query;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            INSERT INTO table2 VALUES collectionVariable(forIndex);
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        query VARCHAR(200) := 'SELECT * FROM
   table1';
    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TEMPORARY TABLE query AS ' || :query;
        INSERT INTO table2
        (
            SELECT
                *
            FROM
                query
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000

6. FORALL Without LOOPS¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE  myProcedure IS
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    SELECT * BULK COLLECT INTO collectionVariable FROM table1;
        FORALL forIndex IN 1..collectionVariable.COUNT
            INSERT INTO table2 VALUES (
                collectionVariable (forIndex).column1,
                collectionVariable (forIndex).column2
            );
        collectionVariable.DELETE;
END;

결과¶


COLUMN1	COLUMN2
1	2
1	2
2	3
3	4
4	5
5	6

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        INSERT INTO table2
        (
            SELECT
                column1,
                column2
            FROM
                table1
        );
    END;
$$;

결과¶


COLUMN1	COLUMN2
1.000000000000000000	2.000000000000000000
1.000000000000000000	2.000000000000000000
2.000000000000000000	3.000000000000000000
3.000000000000000000	4.000000000000000000
4.000000000000000000	5.000000000000000000
5.000000000000000000	6.000000000000000000

7. FORALL With UPDATE Statements¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            UPDATE table2 SET column1 = '54321' WHERE column2 = collectionVariable(forIndex).column2;
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶


COLUMN1	COLUMN2
54321	2

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        UPDATE table2
            SET column1 = '54321'
            FROM
                (
                    SELECT
                        * FROM
                        table1) AS collectionVariable
            WHERE
                column2 = collectionVariable.column2;
    END;
$$;

결과¶

ambiguous column name 'COLUMN2'

8. FORALL With DELETE Statements¶

Oracle¶

FORALL의 예¶

CREATE OR REPLACE PROCEDURE myProcedure IS
    CURSOR cursorVariable IS
        SELECT * FROM table1;
    TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
    collectionVariable collectionTypeDefinition;
BEGIN
    OPEN cursorVariable;
    LOOP
        FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
        EXIT WHEN collectionVariable.COUNT = 0;
        FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
            DELETE FROM table2 WHERE column2 = collectionVariable(forIndex).column2;
        collectionVariable.DELETE;
    END LOOP;
    CLOSE cursorVariable;
END;

결과¶

no data found

Snowflake¶

FORALL의 해당 항목¶

CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$

    BEGIN
        --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
        --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        DELETE FROM
            table2
        USING (
            SELECT
                * FROM
                table1) collectionVariable
                WHERE
            table2.column2 = collectionVariable.column2;
    END;
$$;

결과¶

Query produced no results

Known Issues¶

문제가 발견되지 않았습니다.

LOOP¶

Oracle LOOP 문을 Snowflake Scripting으로 변환하기 위한 변환 참조

설명¶

기본 LOOP 문이 반복될 때마다 해당 문이 실행되며 제어는 루프의 맨 위로 반환됩니다. 루프 내부의 문이 루프 외부로 제어를 전송하거나 예외가 발생하면 LOOP 문이 종료됩니다.\ (Oracle PL/SQL 언어 참조 BASIC LOOP문)

Oracle BASIC LOOP 구문¶

LOOP statement... END LOOP [ label ] ;

Snowflake Scripting BASIC LOOP 구문¶

LOOP
  <statement>;
  [ <statement>; ... ]
END LOOP [ <label> ] ;

Oracle BASIC LOOP 동작은 문을 사용하여 수정할 수도 있습니다.

CONTINUE
EXIT
GOTO
RAISE

샘플 소스 패턴¶

루프 단순 케이스¶

참고

이 경우는 기능적으로 동일합니다.

Oracle¶

CREATE TABLE loop_testing_table
(
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE loop_procedure 
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN  
  LOOP
    EXIT WHEN I = J;
    INSERT INTO loop_testing_table VALUES(TO_CHAR(I));
    I := I+1;
  END LOOP;
END;

CALL loop_procedure();
SELECT * FROM loop_testing_table;

결과¶


ITERATOR
1
2
3
4
5
6
7
8
9

Snowflake Scripting¶

CREATE OR REPLACE TABLE loop_testing_table
  (
      iterator VARCHAR(5)
  )
  COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
  ;

  CREATE OR REPLACE PROCEDURE loop_procedure ()
  RETURNS VARCHAR
  LANGUAGE SQL
  COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
  EXECUTE AS CALLER
  AS
  $$
  DECLARE
      I NUMBER(38, 18) := 1;
      J NUMBER(38, 18) := 10;
  BEGIN
      --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
      LOOP
        IF (:I = :J) THEN
          EXIT;
        END IF;
        INSERT INTO loop_testing_table
        VALUES(TO_CHAR(:I));
        I := :I +1;
      END LOOP;
  END;
  $$;

  CALL loop_procedure();

  SELECT * FROM
  loop_testing_table;

결과¶


ITERATOR
1
2
3
4
5
6
7
8
9

Known Issues¶

문제가 발견되지 않았습니다.

OUTPUT PARAMETERS¶

설명¶

출력 매개 변수 는 저장 프로시저/함수 모듈에서 호출하는 PL/SQL 블록으로 값이 다시 전달되는 매개 변수입니다. 출력 매개 변수는 Snowflake Scripting에서 지원되지 않으므로 해당 기능을 에뮬레이션하기 위한 솔루션이 구현되었습니다.

샘플 소스 패턴¶

매개 변수 단일화¶

Oracle¶

-- Procedure with output parameter declaration
CREATE OR REPLACE PROCEDURE proc_with_single_output_parameters(param1 OUT NUMBER)
IS
BEGIN
    param1 := 123;
END;

-- Procedure with output parameter being called
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_single_output_parameters
IS
    var1 NUMBER;
BEGIN
    proc_with_single_output_parameters(var1);
    INSERT INTO TABLE01 VALUES(var1, -1);
END;

Snowflake Scripting¶

-- Procedure with output parameter declaration
CREATE OR REPLACE PROCEDURE proc_with_single_output_parameters (param1 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        param1 := 123;
    END;
$$;

-- Procedure with output parameter being called
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE01" **
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_single_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
    BEGIN
        CALL
        proc_with_single_output_parameters(:var1);
        INSERT INTO TABLE01
        VALUES(:var1, -1);
    END;
$$;

다중 출력 매개 변수¶

Oracle¶

-- Procedure with output parameters declaration
CREATE OR REPLACE PROCEDURE proc_with_multiple_output_parameters(
    param1 OUT NUMBER,
    param2 IN OUT NUMBER
)
IS
BEGIN
    param1 := 123;
    param2 := 456;
END;

-- Procedure with output parameters being called
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_multiple_output_parameters
IS
    var1 NUMBER;
    var2 NUMBER;
BEGIN
    proc_with_multiple_output_parameters(var1, var2);
    INSERT INTO TABLE01 VALUES(var1, var2);
END;

Snowflake Scripting¶

-- Procedure with output parameters declaration
CREATE OR REPLACE PROCEDURE proc_with_multiple_output_parameters (param1 OUT NUMBER(38, 18), param2 OUT NUMBER(38, 18)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        param1 := 123;
        param2 := 456;
    END;
$$;

-- Procedure with output parameters being called
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE01" **
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_multiple_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
        var2 NUMBER(38, 18);
    BEGIN
        CALL
        proc_with_multiple_output_parameters(:var1, :var2);
        INSERT INTO TABLE01
        VALUES(:var1, :var2);
    END;
$$;

기능이 올바르게 에뮬레이션되고 있는지 확인하기 위해 다음 쿼리는 앞서 언급한 테이블의 프로시저와 SELECT 를 실행합니다.

Oracle¶

CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();

SELECT * FROM table01;

결과¶


COL1	COL2
123	-1
123	456

Snowflake Scripting¶

CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();

SELECT * FROM table01;

결과¶


COL1	COL2
123.000000000000000000	-1
123.000000000000000000	456.000000000000000000

고객 데이터 타입 OUT 매개 변수¶

출력 매개 변수가 고객 유형인 경우 프로세스는 일반 데이터 타입과 유사합니다.

Oracle¶

CREATE OR REPLACE PROCEDURE procedure_udtype_out_params (
    p_employee_id NUMBER,
    p_address OUT address_type
)
AS
BEGIN
    -- Retrieve the employee's address based on the employee ID.
    SELECT home_address INTO p_address
    FROM employees
    WHERE employee_id = p_employee_id;
END;

Snowflake Scripting¶

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "address_type", "employees" **
CREATE OR REPLACE PROCEDURE procedure_udtype_out_params (p_employee_id NUMBER(38, 18), p_address OUT VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'address_type' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        -- Retrieve the employee's address based on the employee ID.
        SELECT home_address INTO
            :p_address
        FROM
            employees
        WHERE employee_id = :p_employee_id;
    END;
$$;

커서 OUT 매개 변수¶

Snowflake에서는 커서 out 매개 변수가 지원되지 않습니다. 그럼에도 불구하고 Oracle의 동작을 에뮬레이션하는 해결 방법이 변환된 코드에 적용됩니다. out 매개 변수가 있는 프로시저는 동적 이름을 가진 임시 테이블을 생성하고, 프로시저 호출은 임시 테이블의 이름을 문자열로 정의하여 프로시저 호출 내에서 테이블을 생성합니다.

Oracle¶

CREATE OR REPLACE PROCEDURE get_employees_by_dept (
  p_department_id IN NUMBER,
  p_employee_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
 OPEN p_employee_cursor FOR
     SELECT employee_id, first_name, last_name
     FROM   employees_sample
     WHERE  department_id = p_department_id
     ORDER BY last_name;
END get_employees_by_dept;
/
               
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_cursor()
AS
DECLARE
   l_emp_id NUMBER;
   l_first_name VARCHAR;
   l_last_name VARCHAR;
   l_cursor  SYS_REFCURSOR;
BEGIN
   get_employees_by_dept(10, l_cursor);
   LOOP
       FETCH l_cursor INTO l_emp_id, l_first_name, l_last_name;
       EXIT WHEN l_cursor%NOTFOUND;
       INSERT INTO employee VALUES (l_emp_id, l_first_name, l_last_name);
    END LOOP;
    CLOSE l_cursor;
END;
/

Snowflake Scripting¶

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employees_sample" **
CREATE OR REPLACE PROCEDURE get_employees_by_dept (p_department_id NUMBER(38, 18), p_employee_cursor VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
 BEGIN
  CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:p_employee_cursor) AS
   SELECT employee_id, first_name, last_name
   FROM
    employees_sample
   WHERE  department_id = :p_department_id
   ORDER BY last_name;
 END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employee" **
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
 DECLARE
    l_emp_id NUMBER(38, 18);
    l_first_name VARCHAR;
    l_last_name VARCHAR;
    l_cursor_res RESULTSET;
 BEGIN
    CALL
    get_employees_by_dept(10, 'proc_calling_proc_with_cursor_l_cursor');
    LET l_cursor CURSOR
    FOR
   SELECT
    *
   FROM
    IDENTIFIER('proc_calling_proc_with_cursor_l_cursor');
    OPEN l_cursor;
    --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
   --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        FETCH l_cursor INTO
    :l_emp_id,
    :l_first_name,
    :l_last_name;
   IF (l_emp_id IS NULL) THEN
    EXIT;
   END IF;
        INSERT INTO employee
   SELECT
    :l_emp_id,
    :l_first_name,
    :l_last_name;
     END LOOP;
        CLOSE l_cursor;
 END;
$$;

레코드 OUT 매개 변수¶

레코드는 Snowflake에서 기본적으로 지원되지 않습니다. 그러나 이를 출력 매개 변수로 에뮬레이션하기 위한 해결 방법이 사용되었습니다. 레코드 대신 OBJECT 변수를 정의하여 out 매개 변수 결과를 각 오브젝트 속성에 할당해 레코드의 필드 구조를 에뮬레이션할 수 있습니다. 또한 out 매개 변수로 할당된 각 레코드 필드에 대해 필드 유형의 새 변수가 생성됩니다.

Oracle¶

CREATE OR REPLACE PROCEDURE procedure_with_out_params(
  param1 OUT INTEGER,
  param2 OUT INTEGER) 
IS
BEGIN
  param1 := 123;
  param2 := 456;
END;

CREATE OR REPLACE PROCEDURE test_proc
IS 
  TYPE custom_record1 IS RECORD(field3 INTEGER, field4 INTEGER);
  TYPE custom_record2 IS RECORD(field1 INTEGER, field2 custom_record1);
  var1 custom_record2;
BEGIN 
  procedure_with_out_params(var1.field1, var1.field2.field4);
END;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE procedure_with_out_params (param1 OUT INTEGER, param2 OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    param1 := 123;
    param2 := 456;
  END;
$$;

CREATE OR REPLACE PROCEDURE test_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
    TYPE custom_record1 IS RECORD(field3 INTEGER, field4 INTEGER);
    !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
    TYPE custom_record2 IS RECORD(field1 INTEGER, field2 custom_record1);
    var1 OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - custom_record2 DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
    var1_field1 INTEGER;
    var1_field2_field4 INTEGER;
  BEGIN
    CALL
    procedure_with_out_params(:var1_field1, :var1_field2_field4);
    var1 := OBJECT_INSERT(COALESCE(var1, OBJECT_CONSTRUCT()), 'field1', :var1_field1, true);
    var1 := OBJECT_INSERT(COALESCE(var1, OBJECT_CONSTRUCT()), 'field2', OBJECT_INSERT(COALESCE(var1:field2, OBJECT_CONSTRUCT()), 'field4', :var1_field2_field4, true), true);
  END;
$$;

패키지 변수를 OUT 매개 변수로¶

패키지는 Snowflake에서 지원되지 않으므로 변수 또는 상수와 같은 로컬 멤버도 해결 방법을 사용하여 보존해야 합니다. 이 시나리오에서 패키지 변수는 출력 매개 변수 결과로 로컬 변수를 설정한 후 업데이트되는 세션 변수를 사용하여 에뮬레이트됩니다.

Oracle¶

CREATE OR REPLACE PACKAGE scha1.pkg1 AS
    PKG_VAR1 NUMBER;
END my_package;
/

CREATE OR REPLACE PROCEDURE PROC_WITH_OUT_PARAM(param1 OUT NUMBER)
AS
BEGIN
   param1 := 0;
END;
CREATE OR REPLACE PROCEDURE PROC ()
AS
BEGIN 
   PROC_WITH_OUT_PARAM(param1 => scha1.pkg1.PKG_VAR1);
END;

Snowflake Scripting¶

CREATE SCHEMA IF NOT EXISTS SCHA1_PKG1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
;

SET "SCHA1_PKG1.PKG_VAR1" = '~';

CREATE OR REPLACE PROCEDURE PROC_WITH_OUT_PARAM (param1 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      param1 := 0;
   END;
$$;

CREATE OR REPLACE PROCEDURE PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      SCHA1_PKG1_PKG_VAR1 VARIANT;
   BEGIN
      CALL
      PROC_WITH_OUT_PARAM(param1 => :SCHA1_PKG1_PKG_VAR1);
      CALL UPDATE_PACKAGE_VARIABLE_STATE_UDF('SCHA1_PKG1.PKG_VAR1', TO_VARCHAR(:SCHA1_PKG1_PKG_VAR1));
   END;
$$;

Known Issues¶

1. Procedures with output parameters inside packages may not work correctly¶

현재 패키지 내부에 있는 프로시저의 의미 체계 정보를 수집하는 데 문제가 있어 출력 매개 변수에 대한 변환이 부분적으로 작동하거나 전혀 작동하지 않을 수 있습니다. 이 문제를 해결하기 위한 작업이 이미 진행 중입니다.

2. Some data types may not work properly¶

변환에서 볼 수 있듯이, 호출된 프로시저에서 값을 검색할 때 VARIANT 에서 변수가 지정한 유형으로 암시적 형변환이 수행됩니다. 가능한 데이터 타입이 많기 때문에 일부 형변환은 실패하거나 다른 데이터를 포함할 수 있습니다.

NESTED PROCEDURES¶

설명¶

Oracle의 PL/SQL, NESTED PROCEDURES 정의는 다른 PL/SQL 블록의 선언적 섹션 내에서 선언되고 정의된 프로시저를 나타냅니다. 이 상위 블록은 다른 프로시저, 함수 또는 패키지 본문일 수 있습니다. 자세한 내용은 Oracle 프로시저 선언 및 정의를 참조하세요.

참고

아래에 설명된 변환은 다른 프로시저나 패키지에 임베드된 프로시저에만 적용됩니다.

샘플 소스 패턴¶

중첩 프로시저의 IN 매개 변수 모드¶

Snowflake 중첩 프로시저는 암시적으로 IN 매개 변수만 지원하므로 IN 키워드는 제거됩니다.

Oracle¶

CREATE OR REPLACE PROCEDURE calculate_basic_salary (
    p_base_salary IN NUMBER,
    p_bonus_amount IN NUMBER
)
AS
    v_total_salary NUMBER := p_base_salary;
    PROCEDURE add_bonus (
        p_bonus_to_add IN NUMBER
    )
    AS
    BEGIN
        v_total_salary := v_total_salary + p_bonus_to_add;
        INSERT INTO salary_logs (description, result_value)
        VALUES ('Bonus added', v_total_salary);
    END add_bonus;
BEGIN
    INSERT INTO salary_logs (description, result_value)
    VALUES ('Starting calculation', v_total_salary);
    add_bonus(p_bonus_to_add => p_bonus_amount);
    INSERT INTO salary_logs (description, result_value)
    VALUES ('Final salary', v_total_salary);
END calculate_basic_salary;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE calculate_basic_salary (p_base_salary NUMBER(38, 18), p_bonus_amount NUMBER(38, 18)
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        v_total_salary NUMBER(38, 18) := :p_base_salary;
        add_bonus PROCEDURE (p_bonus_to_add NUMBER(38, 18)
           )
        RETURNS VARCHAR
        AS
            BEGIN
                v_total_salary := :v_total_salary + :p_bonus_to_add;
            INSERT INTO salary_logs(description, result_value)
            VALUES ('Bonus added', :v_total_salary);
            END;
        BEGIN
        INSERT INTO salary_logs(description, result_value)
        VALUES ('Starting calculation', :v_total_salary);
        CALL
        add_bonus(:p_bonus_amount);
        INSERT INTO salary_logs(description, result_value)
        VALUES ('Final salary', :v_total_salary);
        END;
$$;

중첩 프로시저의 OUT 매개 변수 모드¶

SnowScript의 중첩 프로시저는 출력 매개 변수를 지원하지 않습니다. Snowflake에서 이 기능을 복제하기 위해 RETURN 유형은 출력 매개 변수를 기반으로 생성해야 합니다.

출력 매개 변수가 하나만 있는 경우 해당 매개 변수는 끝에 반환됩니다. 출력 매개 변수가 여러 개인 경우 해당 값을 포함하는 오브젝트 구문이 생성됩니다. 호출 중에 이러한 값은 변수에 할당되고, 이후에 이러한 결과는 해당 변수 또는 매개 변수에 할당됩니다.

Oracle¶

CREATE OR REPLACE PROCEDURE calculate_net_salary (
    p_base_salary IN NUMBER,
    p_bonus_amount IN NUMBER,
    p_net_salary OUT NUMBER
)
AS
    PROCEDURE calculate_tax (
        p_gross_amount IN NUMBER,
        p_net_result OUT NUMBER
    )
    AS
    BEGIN
        p_net_result := p_gross_amount * 0.8;
    END calculate_tax;
BEGIN
    calculate_tax(p_base_salary + p_bonus_amount, p_net_salary);
END calculate_net_salary;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE calculate_net_salary (p_base_salary NUMBER(38, 18), p_bonus_amount NUMBER(38, 18), p_net_salary OUT NUMBER(38, 18)
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        calculate_tax PROCEDURE (p_gross_amount NUMBER(38, 18), p_net_result NUMBER(38, 18)
           )
        RETURNS NUMBER
        AS
            BEGIN
                p_net_result := :p_gross_amount * 0.8;
                RETURN p_net_result;
            END;
        call_results NUMBER;
        BEGIN
        call_results := (
            CALL
            calculate_tax(:p_base_salary + :p_bonus_amount, :p_net_salary)
        );
        p_net_salary := :call_results;
        END;
$$;

중첩 프로시저의 다중 OUT 매개 변수¶

Oracle¶

CREATE OR REPLACE PROCEDURE calculate_comprehensive_salary (
    p_base_salary IN NUMBER,
    p_bonus_amount IN NUMBER,
    p_final_salary OUT NUMBER,
    p_tax_calculated OUT NUMBER,
    p_total_gross OUT NUMBER
)
AS
    l_running_total NUMBER := p_base_salary;
    l_tax_amount NUMBER;
    l_net_amount NUMBER;
    PROCEDURE calculate_all_components (
        p_base_amount IN NUMBER,
        p_bonus_amt IN NUMBER,
        p_running_total_inout IN OUT NUMBER,
        p_tax_out OUT NUMBER,
        p_net_out OUT NUMBER
    )
    AS
    BEGIN
        p_running_total_inout := p_base_amount + p_bonus_amt;
        p_tax_out := p_running_total_inout * 0.25;
        p_net_out := p_running_total_inout - p_tax_out;
    END calculate_all_components;
BEGIN
    calculate_all_components(
        p_base_amount => p_base_salary,
        p_bonus_amt => p_bonus_amount,
        p_running_total_inout => l_running_total,
        p_tax_out => l_tax_amount,
        p_net_out => l_net_amount
    );
    
    p_final_salary := l_net_amount;
    p_tax_calculated := l_tax_amount;
    p_total_gross := l_running_total;
END calculate_comprehensive_salary;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE calculate_comprehensive_salary (p_base_salary NUMBER(38, 18), p_bonus_amount NUMBER(38, 18), p_final_salary OUT NUMBER(38, 18), p_tax_calculated OUT NUMBER(38, 18), p_total_gross OUT NUMBER(38, 18)
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        l_running_total NUMBER(38, 18) := :p_base_salary;
        l_tax_amount NUMBER(38, 18);
        l_net_amount NUMBER(38, 18);
        calculate_all_components PROCEDURE (p_base_amount NUMBER(38, 18), p_bonus_amt NUMBER(38, 18), p_running_total_inout NUMBER(38, 18), p_tax_out NUMBER(38, 18), p_net_out NUMBER(38, 18)
           )
        RETURNS VARIANT
        AS
            BEGIN
                p_running_total_inout := :p_base_amount + :p_bonus_amt;
                p_tax_out := :p_running_total_inout * 0.25;
                p_net_out := :p_running_total_inout - :p_tax_out;
                RETURN OBJECT_CONSTRUCT('p_running_total_inout', :p_running_total_inout, 'p_tax_out', :p_tax_out, 'p_net_out', :p_net_out);
            END;
        call_results VARIANT;
        BEGIN
        call_results := (
            CALL
            calculate_all_components(:p_base_salary, :p_bonus_amount, :l_running_total, :l_tax_amount, :l_net_amount)
        );
        l_running_total := :call_results:p_running_total_inout;
        l_tax_amount := :call_results:p_tax_out;
        l_net_amount := :call_results:p_net_out;
        p_final_salary := :l_net_amount;
        p_tax_calculated := :l_tax_amount;
        p_total_gross := :l_running_total;
        END;
$$;

다단계 중첩 프로시저¶

Snowflake는 중첩 프로시저에 대해 한 수준의 중첩만 허용합니다. 따라서 다른 중첩 프로시저 내의 중첩 프로시저는 지원되지 않습니다. 이 문제가 발생하면 변환에 !!!RESOLVE EWI!!! /*** SSC-EWI-0111 - ONLY ONE LEVEL OF NESTING IS ALLOWED FOR NESTED PROCEDURES IN SNOWFLAKE. ***/!!! 오류가 포함됩니다.

Oracle¶

CREATE OR REPLACE PROCEDURE calculate_executive_salary (
    p_result OUT NUMBER
)
AS
    PROCEDURE calculate_senior_level (
        senior_result OUT NUMBER
    )
    AS
        PROCEDURE calculate_base_level (
            base_result OUT NUMBER
        )
        AS
        BEGIN
            base_result := 75000;
        END calculate_base_level;
    BEGIN
        calculate_base_level(senior_result);
        senior_result := senior_result * 1.5;
    END calculate_senior_level;
BEGIN
    calculate_senior_level(p_result);
END calculate_executive_salary;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE calculate_executive_salary (p_result OUT NUMBER(38, 18)
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        calculate_senior_level PROCEDURE (senior_result NUMBER(38, 18)
           )
        RETURNS NUMBER
        AS
            DECLARE
                !!!RESOLVE EWI!!! /*** SSC-EWI-0111 - ONLY ONE LEVEL OF NESTING IS ALLOWED FOR NESTED PROCEDURES IN SNOWFLAKE. ***/!!!
                PROCEDURE calculate_base_level (
                    base_result OUT NUMBER
                )
                AS
                BEGIN
                    base_result := 75000;
                END calculate_base_level;
                call_results NUMBER;
            BEGIN
                call_results := (
                CALL
                calculate_base_level(:senior_result)
                );
                senior_result := :call_results;
                senior_result := :senior_result * 1.5;
                RETURN senior_result;
            END;
        call_results NUMBER;
        BEGIN
        call_results := (
            CALL
            calculate_senior_level(:p_result)
        );
        p_result := :call_results;
        END;
$$;

중첩 프로시저의 기본값¶

중첩 프로시저 인자는 기본 절을 지원하지 않습니다. 따라서 중첩 프로시저 호출에서 선택적 매개 변수를 생략하는 경우 해당 인자의 기본값을 프로시저 호출 내에서 제출해야 합니다. SnowConvert AI는 이러한 시나리오를 자동으로 식별하고 프로시저 호출을 적절하게 채웁니다.

Oracle¶

CREATE OR REPLACE PROCEDURE calculate_total_compensation (
    p_base_salary IN NUMBER,
    p_final_compensation OUT NUMBER
)
AS
    v_total NUMBER := p_base_salary;
    l_bonus NUMBER;
    PROCEDURE add_bonus (
        p_salary_amount IN NUMBER,
        p_multiplier IN NUMBER DEFAULT 1.1,
        p_calculated_bonus OUT NUMBER
    )
    AS
    BEGIN
        p_calculated_bonus := p_salary_amount * (p_multiplier - 1);
    END add_bonus;
BEGIN
    add_bonus(p_base_salary, p_calculated_bonus => l_bonus);
    v_total := v_total + l_bonus;
    add_bonus(p_base_salary, 1.2, p_calculated_bonus => l_bonus);
    v_total := v_total + l_bonus;
    p_final_compensation := v_total;
END calculate_total_compensation;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE calculate_total_compensation (p_base_salary NUMBER(38, 18), p_final_compensation OUT NUMBER(38, 18)
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        v_total NUMBER(38, 18) := :p_base_salary;
        l_bonus NUMBER(38, 18);
        add_bonus PROCEDURE (p_salary_amount NUMBER(38, 18), p_multiplier NUMBER(38, 18), p_calculated_bonus NUMBER(38, 18)
           )
        RETURNS NUMBER
        AS
            BEGIN
                p_calculated_bonus := :p_salary_amount * (:p_multiplier - 1);
                RETURN p_calculated_bonus;
            END;
        call_results NUMBER;
        BEGIN
        call_results := (
            CALL
            add_bonus(:p_base_salary, 1.1, :l_bonus)
        );
        l_bonus := :call_results;
        v_total := :v_total + :l_bonus;
        call_results := (
            CALL
            add_bonus(:p_base_salary, 1.2, :l_bonus)
        );
        l_bonus := :call_results;
        v_total := :v_total + :l_bonus;
        p_final_compensation := :v_total;
        END;
$$;

중첩 프로시저 오버로딩¶

Snowflake는 중첩 프로시저의 오버로딩을 지원하지 않습니다. 이 문제가 발생하면 EWI SSC-EWI-0112 - NESTED PROCEDURE OVERLOADING IS NOT SUPPORTED가 추가됩니다.

Oracle¶

CREATE OR REPLACE PROCEDURE demonstrate_salary_calculations(
    final_summary OUT VARCHAR2
)
AS
    result1 VARCHAR2(100);
    result2 VARCHAR2(100);
    result3 VARCHAR2(100);
    PROCEDURE calculate_salary(
        output OUT VARCHAR2
    )
    AS
    BEGIN
        output := 'Standard: 55000';
    END;
    PROCEDURE calculate_salary(
        base_amount IN NUMBER,
        output OUT VARCHAR2
    )
    AS
    BEGIN
        output := 'Calculated: ' || (base_amount * 1.15);
    END;
    PROCEDURE calculate_salary(
        employee_level IN VARCHAR2,
        output OUT VARCHAR2
    )
    AS
    BEGIN
        output := 'Level ' || UPPER(employee_level) || ': 60000';
    END;
BEGIN
    calculate_salary(result1);
    calculate_salary(50000, result2);
    calculate_salary('senior', result3);
    final_summary := result1 || ' | ' || result2 || ' | ' || result3;
END demonstrate_salary_calculations;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE demonstrate_salary_calculations (final_summary OUT VARCHAR
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        result1 VARCHAR(100);
        result2 VARCHAR(100);
        result3 VARCHAR(100);
        calculate_salary PROCEDURE(output VARCHAR
            )
        RETURNS VARCHAR
        AS
            BEGIN
                output := 'Standard: 55000';
                RETURN output;
            END;
        !!!RESOLVE EWI!!! /*** SSC-EWI-0112 - NESTED PROCEDURE OVERLOADING IS NOT SUPPORTED. ***/!!!
        calculate_salary PROCEDURE(base_amount NUMBER(38, 18), output VARCHAR
            )
        RETURNS VARCHAR
        AS
            BEGIN
                output := 'Calculated: ' || NVL((:base_amount * 1.15) :: STRING, '');
                RETURN output;
            END;
        !!!RESOLVE EWI!!! /*** SSC-EWI-0112 - NESTED PROCEDURE OVERLOADING IS NOT SUPPORTED. ***/!!!
        calculate_salary PROCEDURE(employee_level VARCHAR, output VARCHAR
            )
        RETURNS VARCHAR
        AS
            BEGIN
                output := 'Level ' || NVL(UPPER(:employee_level) :: STRING, '') || ': 60000';
                RETURN output;
            END;
        call_results VARCHAR;
        BEGIN
        call_results := (
            CALL
            calculate_salary(:result1)
        );
        result1 := :call_results;
        call_results := (
            CALL
            calculate_salary(50000, :result2)
        );
        result2 := :call_results;
        call_results := (
            CALL
            calculate_salary('senior', :result3)
        );
        result3 := :call_results;
        final_summary := NVL(:result1 :: STRING, '') || ' | ' || NVL(:result2 :: STRING, '') || ' | ' || NVL(:result3 :: STRING, '');
        END;
$$;

매개 변수 목록이 없는 중첩 프로시저¶

Snowflake에서 중첩 프로시저 정의는 매개 변수가 없는 경우 구문적으로 유효하려면 빈 괄호 ()가 필요합니다. Oracle에서는 필요하지 않습니다. SnowConvert AI는 변환 중에 이러한 괄호를 자동으로 추가합니다.

Oracle¶

CREATE OR REPLACE PROCEDURE reset_salary_system
AS
    PROCEDURE cleanup_salary_data
    AS
    BEGIN
        DELETE FROM salary_results;
        INSERT INTO salary_results VALUES (0);
    END cleanup_salary_data;
BEGIN
    cleanup_salary_data();
END reset_salary_system;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE reset_salary_system ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        cleanup_salary_data PROCEDURE ()
        RETURNS VARCHAR
        AS
            BEGIN
                DELETE FROM
                salary_results;
            INSERT INTO salary_results
                VALUES (0);
            END;
        BEGIN
        CALL
        cleanup_salary_data();
        END;
$$;

REFCURSOR 출력 매개 변수가 있는 중첩 프로시저¶

Oracle¶

CREATE OR REPLACE PROCEDURE process_department_salaries (
    p_department_id IN NUMBER
)
AS
    v_employee_cursor SYS_REFCURSOR;
    v_employee_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    PROCEDURE get_department_employees (
        p_dept_id IN NUMBER,
        p_cursor OUT SYS_REFCURSOR
    )
    AS
    BEGIN
        OPEN p_cursor FOR
            SELECT employee_id, first_name, last_name
            FROM employees
            WHERE department_id = p_dept_id;
    END get_department_employees;
BEGIN
    get_department_employees(p_department_id, v_employee_cursor);
    LOOP
        FETCH v_employee_cursor INTO v_employee_id, v_first_name, v_last_name;
        EXIT WHEN v_employee_cursor%NOTFOUND;
        INSERT INTO salary_audit VALUES (v_employee_id, v_first_name || ' ' || v_last_name);
    END LOOP;
    CLOSE v_employee_cursor;
END process_department_salaries;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE process_department_salaries (p_department_id NUMBER(38, 18)
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        v_employee_cursor_res RESULTSET;
        v_employee_id NUMBER(38, 18);
        v_first_name VARCHAR(50);
        v_last_name VARCHAR(50);
        get_department_employees PROCEDURE (p_dept_id NUMBER(38, 18), p_cursor VARCHAR
           )
        RETURNS VARCHAR
        AS
            BEGIN
                CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:p_cursor) AS
                SELECT employee_id, first_name, last_name
                FROM
                    employees
                WHERE department_id = :p_dept_id;
                RETURN p_cursor;
            END;
        call_results VARCHAR;
        BEGIN
        call_results := (
            CALL
            get_department_employees(:p_department_id, 'process_department_salaries_v_employee_cursor')
        );
        LET v_employee_cursor CURSOR
        FOR
            SELECT
                *
            FROM
                IDENTIFIER('process_department_salaries_v_employee_cursor');
        OPEN v_employee_cursor;
        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
        LOOP
            --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
            FETCH v_employee_cursor INTO
                :v_employee_id,
                :v_first_name,
                :v_last_name;
            IF (v_employee_id IS NULL) THEN
                EXIT;
            END IF;
            INSERT INTO salary_audit
            SELECT
                :v_employee_id,
                NVL(:v_first_name :: STRING, '') || ' ' || NVL(:v_last_name :: STRING, '');
        END LOOP;
        CLOSE v_employee_cursor;
        END;
$$;

NOCOPY 매개 변수 옵션이 있는 중첩 프로시저¶

Oracle PL/SQL에서, NOCOPY 키워드는 OUT 및 IN OUT 프로시저 매개 변수에 대한 최적화 힌트입니다. 기본적으로, Oracle은 이러한 매개 변수를 값으로 전달하여 호출 중에 값비싼 데이터 복사본을 생성하고 완료 시 다시 복사합니다. 이로 인해 대규모 데이터 구조의 경우 상당한 성능 오버헤드가 발생할 수 있습니다.

NOCOPY는 Oracle이 대신 참조로 전달하도록 지시하여 프로시저가 원본 데이터를 직접 수정할 수 있도록 합니다. 이를 통해 복사 오버헤드가 제거되고 성능이 향상됩니다. 그러나 변경 사항은 즉시 적용되며 프로시저 내에서 처리되지 않은 예외가 발생하는 경우 암시적으로 롤백되지 않습니다.

따라서 NOCOPY 매개 변수 옵션을 만들고 이 FDM SSC-FDM-OR0050 - EXCEPTIONS WITH NOCOPY PARAMETERS MAY LEAD TO DATA INCONSISTENCY를 추가합니다. 이는 예외 발생 시 프로시저 실행이 종료되어 RETURN 문에 도달하지 못하기 때문입니다. 결과적으로 프로시저가 할당을 위해 새 값을 성공적으로 반환하지 못하므로 호출자의 선언 블록에 있는 변수는 초기 값을 유지합니다.

Oracle¶

CREATE OR REPLACE PROCEDURE calculate_bonus_with_nocopy (
    p_base_salary IN NUMBER,
    p_multiplier IN NUMBER,
    p_bonus_result OUT NOCOPY NUMBER
)
AS
    PROCEDURE compute_bonus(bonus_amount OUT NOCOPY NUMBER)
    AS
    BEGIN
        IF p_multiplier = 0 THEN
            bonus_amount := NULL;
        ELSE
            bonus_amount := p_base_salary * p_multiplier * 0.1;
        END IF;
    END compute_bonus;
BEGIN
    compute_bonus(p_bonus_result);
END calculate_bonus_with_nocopy;

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE calculate_bonus_with_nocopy (p_base_salary NUMBER(38, 18), p_multiplier NUMBER(38, 18), p_bonus_result OUT NUMBER(38, 18)
    )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/22/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
        DECLARE
        compute_bonus PROCEDURE(bonus_amount
        --** SSC-FDM-OR0050 - EXCEPTIONS WITH NOCOPY PARAMETERS MAY LEAD TO DATA INCONSISTENCY. **
        NUMBER(38, 18))
        RETURNS NUMBER
        AS
            BEGIN
                IF (:p_multiplier = 0) THEN
                bonus_amount := NULL;
            ELSE
                bonus_amount := :p_base_salary * :p_multiplier * 0.1;
                END IF;
                RETURN bonus_amount;
            END;
        call_results NUMBER;
        BEGIN
        call_results := (
            CALL
            compute_bonus(:p_bonus_result)
        );
        p_bonus_result := :call_results;
        END;
$$;

Known Issues¶

1. Multi-level Nested Procedures¶

Snowflake의 중첩 프로시저에 대한 변환 작업은 다른 프로시저 내에 직접 중첩된 변환 작업으로 제한되며, 한 수준의 중첩만 지원합니다. 중첩 수준이 1을 초과하거나 프로시저가 독립 실행형 함수 내에 중첩된 경우 변환이 지원되지 않으며 EWI !!!RESOLVE EWI!!! /*** SSC-EWI-0111 - ONLY ONE LEVEL OF NESTING IS ALLOWED FOR NESTED PROCEDURES IN SNOWFLAKE. ***/!!!가 추가됩니다.

2. Nested procedures overloading¶

또한 중첩 프로시저의 오버로딩은 Snowflake에서 지원되지 않습니다. 이러한 경우에는 EWI !!!RESOLVE EWI!!! /*** SSC-EWI-0112 - NESTED PROCEDURE OVERLOADING IS NOT SUPPORTED. ***/!!!가 추가됩니다.

3. Nested procedures within anonymous blocks¶

익명 블록 내의 중첩 프로시저에 대한 변환은 현재 보류 중입니다. EWI !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED IN THIS SCENARIO ***/!!!가 추가됩니다.

PROCEDURE CALL¶

PROCEDURE CALL aka SUBPROGRAM INVOCATION에 대한 변환 참조

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명¶

이 섹션에서는 프로시저 또는 익명 블록과 같은 PL 블록 내의 하위 프로그램 호출 구문에 대해 설명합니다.

이 주제에 대한 자세한 내용은 Oracle의 서브프로그램 설명서를 참조하십시오. (Oracle PL/SQL Language Reference 서브프로그램 호출 문)

프로시저 호출은 선택적 매개 변수가 없고 순서가 공식 매개 변수와 일치하는 한 Snowflake로 마이그레이션할 수 있습니다. 프로시저 호출은 호출 문으로 마이그레이션된다는 점에 유의하십시오.

Oracle 하위 프로그램 호출 구문¶

<subprogram invocation> := subprogram_name [ ( [ parameter [, parameter]... ] ) ]

<parameter> := {
  <actual parameter>
  | <formal parameter name> => <actual parameter>
  }

일부 함수상의 차이는 있지만 Snowflake Scripting은 이 문을 지원합니다.

Snow Scripting 하위 프로그램 호출 구문¶

<subprogram invocation> := CALL subprogram_name [ ( [ parameter [, parameter]... ] ) ]

<parameter> := {
  <actual parameter>
  | <formal parameter name> => <actual parameter>
  }

샘플 소스 패턴¶

참고

아래 예제에 대한 다음 테이블과 프로시저를 참조하십시오.

Oracle¶

CREATE TABLE procedure_call_test_table(
    col1 INTEGER
);

-- Simple Called procedure
CREATE OR REPLACE PROCEDURE called_procedure (param1 INTEGER)
AS
BEGIN
    INSERT INTO procedure_call_test_table VALUES (param1);
END;

Snowflake¶

CREATE OR REPLACE TABLE procedure_call_test_table (
        col1 INTEGER
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

    -- Simple Called procedure
CREATE OR REPLACE PROCEDURE called_procedure (param1 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        INSERT INTO procedure_call_test_table
        VALUES (:param1);
    END;
$$;

간단한 호출¶

Oracle¶

CREATE OR REPLACE PROCEDURE simple_calling_procedure
AS
BEGIN
    called_procedure(1);
END;

CALL simple_calling_procedure();

SELECT * FROM procedure_call_test_table;

결과¶


COL1
1

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE simple_calling_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        called_procedure(1);
    END;
$$;

CALL simple_calling_procedure();

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "procedure_call_test_table" **

SELECT * FROM
    procedure_call_test_table;

결과¶


COL1
1

선택적 매개 변수를 사용하여 프로시저 호출하기¶

경고

이 샘플에는 몇 가지 기능적 차이점에 대한 수동 개입이 포함되어 있으며 이를 설명하는 데 사용됩니다. 이러한 차이점에 대한 자세한 내용은 아래의 알려진 문제 섹션을 참조하세요.

Oracle¶

-- Procedure with optional parameters
CREATE OR REPLACE PROCEDURE proc_optional_parameters (param1 INTEGER, param2 INTEGER := 8, param3 INTEGER)
AS
BEGIN
    INSERT INTO procedure_call_test_table VALUES (param1);
    INSERT INTO procedure_call_test_table VALUES (param2);
    INSERT INTO procedure_call_test_table VALUES (param3);
END;

CREATE OR REPLACE PROCEDURE calling_procedure
AS
BEGIN
    -- positional convention
    proc_optional_parameters(1, 2, 3);
    
    -- named convention
    proc_optional_parameters(param1 => 4, param2 => 5, param3 => 6);
    
    -- named convention, second gets ommited
    proc_optional_parameters(param1 => 7, param3 => 9);
    
    -- named convention, different order
    proc_optional_parameters(param3 => 12, param1 => 10, param2 => 11);
END;

CALL calling_procedure();

SELECT * FROM procedure_call_test_table;

결과¶


COL1
1
2
3
4
5
6
7
8
9
10
11
12

Snowflake Scripting¶

-- Procedure with optional parameters
CREATE OR REPLACE PROCEDURE proc_optional_parameters
                                                     --** SSC-FDM-0041 - DEFAULT PARAMETERS WERE REORDERED TO THE END OF THE PARAMETER LIST TO MATCH SNOWFLAKE REQUIREMENTS. CALLERS USING POSITIONAL ARGUMENTS MAY NEED TO BE UPDATED **
                                                     (param1 INTEGER, param2 INTEGER DEFAULT 8, param3 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        INSERT INTO procedure_call_test_table
        VALUES (:param1);
        INSERT INTO procedure_call_test_table
        VALUES (:param2);
        INSERT INTO procedure_call_test_table
        VALUES (:param3);
    END;
$$;

CREATE OR REPLACE PROCEDURE calling_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        -- positional convention
        proc_optional_parameters(1, 2, 3);
        CALL

        -- named convention
        proc_optional_parameters(param1 => 4, param2 => 5, param3 => 6);
        CALL

        -- named convention, second gets ommited
        proc_optional_parameters(param1 => 7, param3 => 9);
        CALL

        -- named convention, different order
        proc_optional_parameters(param1 => 10, param2 => 11, param3 => 12);
    END;
$$;

CALL calling_procedure();


SELECT * FROM
    procedure_call_test_table;

결과¶


COL1
1
2
3
4
5
6
7
8
9
10
11
12

Known Issues¶

1. Default parameter reordering¶

Snowflake requires default parameters to appear at the end of the parameter list. SnowConvert AI automatically reorders them and emits an SSC-FDM-0041 notice. When positional callers are detected, they are converted to named arguments.

2. Named parameters are accepted, but not functionally equivalent¶

Named parameters are supported in Snowflake. When default parameters are reordered, SnowConvert AI automatically converts positional call sites to use named arguments to preserve the original semantics.

3. Calling Subprograms with Out Parameters is not supported¶

Snowflake는 매개 변수 모드를 지원하지 않지만, 해당 기능을 에뮬레이션하는 솔루션이 구현되고 있습니다. 출력 매개 변수 변환에 대한 자세한 내용은 다음 문서 출력 매개 변수를 참조하십시오.

RAISE¶

설명¶

RAISE 문은 명시적으로 예외를 발생시킵니다.

예외 처리기 외부에서 예외 이름을 지정해야 합니다. 예외 처리기 내에서 예외 이름을 생략하면 RAISE 문이 현재 예외를 다시 발생시킵니다. (Oracle PL/SQL Language Reference Raise 문)

이 문은 Snowflake Scripting에서 완벽하게 지원되지만, 일부 커밋 및 롤백 문이 있을 때 약간의 차이가 있을 수 있음을 고려하십시오.

RAISE <exception_name> ;

Snowflake Scripting은 이 문을 지원합니다.

RAISE <exception_name> ;

샘플 소스 패턴¶

간단한 예외 throw¶

Oracle¶

CREATE OR REPLACE PROCEDURE simple_exception_throw_handle(param1 INTEGER)
IS
    my_exception EXCEPTION;
    my_other_exception EXCEPTION;
BEGIN
    IF param1 > 0
        THEN RAISE my_exception;
    END IF;
EXCEPTION
    WHEN my_exception THEN
        IF param1 = 1
            THEN RAISE;
        END IF;
        RAISE my_other_exception;
END;

--Completes without issue
CALL simple_exception_throw_handle(0);
--Throws my_exception
CALL simple_exception_throw_handle(1);
--Throws my_exception, catches then raises second my_other_exception
CALL simple_exception_throw_handle(2);

결과¶

Call completed.
-----------------------------------------------------------------------
Error starting at line : 31 in command -
CALL simple_exception_throw_handle(1)
Error report -
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYSTEM.SIMPLE_EXCEPTION_THROW_HANDLE", line 12
ORA-06512: at "SYSTEM.SIMPLE_EXCEPTION_THROW_HANDLE", line 7
ORA-06512: at line 1
06510. 00000 -  "PL/SQL: unhandled user-defined exception"
*Cause:    A user-defined exception was raised by PL/SQL code, but
           not handled.
*Action:   Fix the problem causing the exception or write an exception
           handler for this condition. Or you may need to contact your
           application administrator or DBA.
-----------------------------------------------------------------------
Error starting at line : 33 in command -
CALL simple_exception_throw_handle(2)
Error report -
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYSTEM.SIMPLE_EXCEPTION_THROW_HANDLE", line 14
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYSTEM.SIMPLE_EXCEPTION_THROW_HANDLE", line 7
ORA-06512: at line 1
06510. 00000 -  "PL/SQL: unhandled user-defined exception"
*Cause:    A user-defined exception was raised by PL/SQL code, but
           not handled.
*Action:   Fix the problem causing the exception or write an exception
           handler for this condition. Or you may need to contact your
           application administrator or DBA.

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE simple_exception_throw_handle (param1 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        my_exception EXCEPTION;
        my_other_exception EXCEPTION;
    BEGIN
        IF (:param1 > 0) THEN
            RAISE my_exception;
        END IF;
        EXCEPTION
            WHEN my_exception THEN
            IF (:param1 = 1) THEN
                    RAISE;
            END IF;
                RAISE my_other_exception;
        END;
$$;

--Completes without issue
CALL simple_exception_throw_handle(0);

--Throws my_exception
CALL simple_exception_throw_handle(1);

--Throws my_exception, catches then raises second my_other_exception
CALL simple_exception_throw_handle(2);

결과¶

Call Completed
-----------------------------------------------------------------------
Uncaught exception of type 'MY_EXCEPTION' on line 7 at position 9
-----------------------------------------------------------------------
Uncaught exception of type 'MY_OTHER_EXCEPTION' on line 14 at position 9

Known Issues¶

문제가 발견되지 않았습니다.

RAISE_APPICATION_ERROR¶

raise_application_error 문에 대한 변환 참조입니다.

일반 설명¶

RAISE_APPLICATION_ERROR 프로시저를 사용하면 저장된 하위 프로그램에서 사용자 정의 ORA- 오류 메시지를 발행할 수 있습니다. 이렇게 하면 애플리케이션에 오류를 보고하고 처리되지 않은 예외를 반환하는 것을 방지할 수 있습니다(Oracle 설명서).

Oracle 구문¶

raise_application_error(
      error_number, message[, {TRUE | FALSE}]);

참고

error_number 는 -20000 … -20999 범위의 음수이고 message 는 최대 2048바이트 길이의 문자 문자열입니다.

선택적 세 번째 매개 변수가 TRUE 인 경우 오류는 이전 오류 스택에 배치됩니다. 매개 변수가 FALSE (기본값)인 경우 오류는 이전의 모든 오류를 대체합니다.

Snowflake에서 이에 해당하는 문은 RAISE 절이지만, RAISE 문을 호출하기 전에 사용자 정의 예외를 변수로 선언해야 합니다.

Snowflake 구문¶

<exception_name> EXCEPTION [ ( <exception_number> , '<exception_message>' ) ] ;

참고

자세한 내용은 Snowflake 설명서 를 참조하십시오.

샘플 소스 패턴¶

1. Exception in functions without declaring section¶

이 시나리오에서는 선언 섹션이 없는 함수가 예외 선언이 있는 프로시저로 변환됩니다. 참고하십시오.

예외 변수 이름은 대문자로 선언합니다.
예외 변수 이름은 설명을 기반으로 하며 끝은 예외 코드 이름 뒤에 연속된 숫자로 구성됩니다.
선언 섹션은 초기 함수나 프로시저에 포함되어 있지 않더라도 생성됩니다.

Oracle¶

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    raise_application_error(-20001, 'First exception message', FALSE);
    raise_application_error(-20002, 'Second exception message');
  RETURN 1;
END TEST;

출력¶

ORA-20001: First exception message

Snowflake¶

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE TEST (
    SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
    SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20001, 'FIRST EXCEPTION MESSAGE');
    SECOND_EXCEPTION_MESSAGE_EXCEPTION_CODE_1 EXCEPTION (-20002, 'SECOND EXCEPTION MESSAGE');
  BEGIN
    --** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT FALSE WAS REMOVED. **
    RAISE FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
    RAISE SECOND_EXCEPTION_MESSAGE_EXCEPTION_CODE_1;
    RETURN 1;
  END;
$$;

출력¶

FIRST EXCEPTION MESSAGE

2. Exception code number outside limits¶

다음 예제는 프로시저 본문에 설명된 변환을 보여줍니다. 해당 코드가 Snowflake의 애플리케이션 코드 제한을 벗어났기 때문입니다. 해결책은 쿼리 섹션에서 사용 가능한 코드의 예외 코드를 변경하는 것입니다.

Oracle¶

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    raise_application_error(-20000, 'My exception message');
    RETURN 1;
END TEST;

출력¶

ORA-20000: My exception message

Snowflake¶

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE TEST (
    SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
    SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20000, 'MY EXCEPTION MESSAGE');
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/!!!
        RAISE MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
        RETURN 1;
    END;
$$;

출력¶

 Invalid error code '-20,000'. Must be between -20,999 and -20,000

3. Exception stack functionality¶

예외 스택 기능은 Snowflake에서 지원되지 않으며 예외 선언에서 제거됩니다.

Oracle¶

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    raise_application_error(-20001, 'My exception message', TRUE);
    RETURN 1;
END TEST;

출력¶

ORA-20001: My exception message

Snowflake¶

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE TEST (
    SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
    SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20001, 'MY EXCEPTION MESSAGE');
    BEGIN
        --** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
        RAISE MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
        RETURN 1;
    END;
$$;

출력¶

MY EXCEPTION MESSAGE

4. Multiple exceptions with the same exception code¶

선언 섹션에 동일한 예외가 여러 개 공존하여 문을 발생시킬 수 있습니다.

Oracle¶

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    IF TRUE THEN 
        raise_application_error(-20001, 'The first exception');
    ELSE 
        raise_application_error(-20001, 'Other exception inside');
    END IF;
    RETURN 1;
END TEST;

출력¶

ORA-20000: The first exception

Snowflake¶

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE TEST (
    SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
    SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        THE_FIRST_EXCEPTION_EXCEPTION_CODE_0 EXCEPTION (-20001, 'THE FIRST EXCEPTION');
        OTHER_EXCEPTION_INSIDE_EXCEPTION_CODE_1 EXCEPTION (-20001, 'OTHER EXCEPTION INSIDE');
    BEGIN
        IF (TRUE) THEN
            RAISE THE_FIRST_EXCEPTION_EXCEPTION_CODE_0;
            ELSE
            RAISE OTHER_EXCEPTION_INSIDE_EXCEPTION_CODE_1;
            END IF;
            RETURN 1;
    END;
$$;

출력¶

THE FIRST EXCEPTION

Known Issues¶

SQLREM 함수를 검토할 수 있습니다.
Snowflake에서 해당 제한을 벗어난 예외 코드 번호는 사용 가능한 코드 예외로 변경해야 합니다.
오류 스택에 추가는 지원되지 않습니다.

UDF CALL¶

사용자 정의 함수(UDF) 호출에 대한 변환 참조

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명¶

널리 알려진 바와 같이, Oracle의 비스칼라 사용자 정의 함수(UDFs)는 보다 복잡한 함수를 수용하기 위해 Snowflake 저장 프로시저로 변환됩니다.

이 변환은 함수가 호출되는 방식도 변경하여 기존 함수 호출에서 저장 프로시저 호출로 변환합니다.

저장 프로시저 호출에 대한 자세한 내용은 여기(PROCEDURE CALL)의 설명서를 참조하십시오.

샘플 소스 패턴¶

참고

아래 예제의 다음 함수와 테이블을 고려하십시오.

Oracle¶

CREATE OR REPLACE FUNCTION sum_to_varchar_function(p_number1 IN NUMBER, p_number2 IN NUMBER)
RETURN VARCHAR
IS
    result VARCHAR(100);
BEGIN
    result := TO_CHAR(p_number1 + p_number2);
    RETURN result;
END sum_to_varchar_function;

CREATE TABLE example_table (
    id NUMBER,
    column1 NUMBER
);
INSERT INTO example_table VALUES (1, 15);

CREATE TABLE result_table (
    id NUMBER,
    result_col VARCHAR(100)
);

Snowflake¶

CREATE OR REPLACE FUNCTION sum_to_varchar_function (p_number1 NUMBER(38, 18), p_number2 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/14/2024",  "domain": "test" }}'
AS
$$
    WITH declaration_variables_cte1 AS
    (
        SELECT
            TO_CHAR(p_number1 + p_number2) AS
            result
    )
    SELECT
        result
    FROM
        declaration_variables_cte1
$$;

CREATE OR REPLACE TABLE example_table (
       id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
       column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
   )
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/14/2024",  "domain": "test" }}'
;

INSERT INTO example_table
VALUES (1, 15);

CREATE OR REPLACE TABLE result_table (
    id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
       result_col VARCHAR(100)
   )
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "11/14/2024",  "domain": "test" }}'
;

UDF 호출¶

Oracle¶

CREATE OR REPLACE PROCEDURE procedure_calling_function(param1 IN NUMBER)
IS
    result_value VARCHAR(200);
BEGIN 
    result_value := sum_to_varchar_function(3, param1);
    INSERT INTO result_table VALUES (1, result_value);
END;

BEGIN
    procedure_calling_function(5);
END;

결과¶

ID	RESULT_COL
1	8

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE procedure_calling_function (param1 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        result_value VARCHAR(200);
    BEGIN
        result_value := sum_to_varchar_function(3, :param1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'sum_to_varchar_function' NODE ***/!!!;
        INSERT INTO result_table
        VALUES (1, :result_value);
    END;
$$;

DECLARE
    call_results VARIANT;

    BEGIN
    CALL
    procedure_calling_function(5);
    RETURN call_results;
    END;

결과¶

ID	RESULT_COL
1	8

쿼리 내 UDF 호출¶

함수 호출이 쿼리 내에 포함되어 있으면 쿼리 내에서 직접 프로시저를 호출할 수 없다는 Snowflake의 제한으로 인해 호출 프로세스가 더 복잡해집니다. 이 제한을 극복하기 위해 프로시저 호출을 쿼리 외부로 이동하고 그 결과를 변수에 할당합니다. 그런 다음 이 변수를 쿼리 내에서 참조하여 기능적 동등성을 달성합니다. 이 접근법을 사용하면 프로시저 제약 조건을 준수하면서 Snowflake 쿼리 내에서 더 복잡한 동작을 실행할 수 있습니다.

Oracle¶

CREATE OR REPLACE PROCEDURE procedure_calling_function(param1 IN NUMBER)
IS
    result_value VARCHAR(200);
    result_value2 VARCHAR(200);
BEGIN 
    SELECT
        sum_to_varchar_function(1, param1) AS result_column,
        sum_to_varchar_function(2, param1) AS result_column2
    INTO result_value, result_value2
    FROM example_table ext;

    INSERT INTO result_table VALUES (1, result_value);
    INSERT INTO result_table VALUES (2, result_value2);
END;

BEGIN
    procedure_calling_function(5);
END;

결과¶

ID	RESULT_COL
1	6
2   7

Snowflake Scripting¶

CREATE OR REPLACE PROCEDURE procedure_calling_function (param1 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        result_value VARCHAR(200);
        result_value2 VARCHAR(200);
    BEGIN
        SELECT
            sum_to_varchar_function(1, :param1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'sum_to_varchar_function' NODE ***/!!! AS result_column,
            sum_to_varchar_function(2, :param1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'sum_to_varchar_function' NODE ***/!!! AS result_column2
        INTO
            :result_value,
            :result_value2
        FROM
            example_table ext;

        INSERT INTO result_table
        VALUES (1, :result_value);
        INSERT INTO result_table
        VALUES (2, :result_value2);
    END;
$$;

DECLARE
    call_results VARIANT;

    BEGIN
    CALL
    procedure_calling_function(5);
    RETURN call_results;
    END;

결과¶

ID	RESULT_COL
1	6
2   7

Known Issues¶

1. Unsupported Usage of UDFs in Queries with Query Dependencies¶

쿼리 종속성이 있는 쿼리 내에서 사용자 정의 함수(UDFs)를 호출할 때 열을 인자로 포함하는 임베디드 함수가 포함된 시나리오는 지원되지 않습니다. 이 제한은 쿼리 외부에서 열 값에 액세스할 수 없기 때문에 발생합니다. 지원되지 않는 시나리오의 예는 다음과 같습니다.

BEGIN
    SELECT
        sum_to_varchar_function(ext.col1, ext.col2) -- columns as arguments not supported
    INTO
        result_value
    FROM example_table ext;
END;

\ 지원되는 시나리오에는 리터럴 값, 외부 변수 또는 매개 변수와 같은 다른 유형의 인자를 사용한 함수 호출이 포함됩니다. 예를 들어:

BEGIN
    SELECT
        sum_to_varchar_function(100, param1)
    INTO
        result_value
    FROM example_table ext;
END;

지원되는 시나리오에서는 함수를 효과적으로 마이그레이션할 수 있습니다.

WHILE¶

Oracle WHILE 문을 Snowflake Scripting으로 변환하기 위한 변환 참조

설명¶

WHILE LOOP 문은 조건이 TRUE인 동안 하나 이상의 문을 실행합니다. \ (Oracle PL/SQL 언어 참조 WHILE 문)

Oracle WHILE 구문¶

WHILE boolean_expression
  LOOP statement... END LOOP [ label ] ;

Snowflake Scripting WHILE 구문¶

WHILE ( <condition> ) { DO | LOOP }
  <statement>;
  [ <statement>; ... ]
END { WHILE | LOOP } [ <label> ] ;

Oracle WHILE 동작은 문을 사용하여 수정할 수도 있습니다.

CONTINUE
EXIT
GOTO
RAISE

샘플 소스 패턴¶

While 단순 케이스¶

참고

이 경우는 기능적으로 동일합니다.

Oracle¶

CREATE TABLE while_testing_table
(
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE while_procedure 
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN  
  WHILE I <> J LOOP
    INSERT INTO while_testing_table VALUES(TO_CHAR(I));
    I := I+1;    
  END LOOP;
END;

CALL while_procedure();
SELECT * FROM while_testing_table;

결과¶


ITERATOR
1
2
3
4
5
6
7
8
9

Snowflake Scripting¶

CREATE OR REPLACE TABLE while_testing_table
(
    iterator VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE while_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
    I NUMBER(38, 18) := 1;
    J NUMBER(38, 18) := 10;
BEGIN
    WHILE (:I <> :J) 
    --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
      INSERT INTO while_testing_table
      VALUES(TO_CHAR(:I));
      I := :I +1;
    END LOOP;
END;
$$;

CALL while_procedure();

SELECT * FROM
while_testing_table;

결과¶


ITERATOR
1
2
3
4
5
6
7
8
9

Known Issues¶

문제가 발견되지 않았습니다.

SnowConvert AI - Oracle - PL/SQL을 Snowflake Scripting으로 변환¶

ASSIGNMENT STATEMENT¶

설명¶

Oracle 할당 구문¶

Snowflake Scripting 할당 구문¶

샘플 소스 패턴¶

1. Scalar Variables¶

Oracle¶

결과¶

Snowflake Scripting¶

결과¶

2. Out Parameter Assignment¶

3. Not Supported Assignments¶

Oracle¶

Snowflake Scripting¶

Known Issues¶

1. Several Unsupported Assignment Statements¶

관련 EWIs¶

CALL¶

설명¶

1-CALL 문:¶

2 호출 사양:¶

Known Issues¶

관련 EWIs¶

CASE¶

설명¶

단순 케이스¶

Oracle CASE 구문¶

Snowflake Scripting CASE 구문¶

검색된 케이스¶

Oracle CASE 구문¶

Snowflake Scripting CASE 구문¶

샘플 소스 패턴¶

Sample auxiliary table¶

Oracle¶

Snowflake¶

단순 케이스¶

Oracle¶

결과¶

Snowflake Scripting¶

결과¶

검색된 케이스¶

Oracle¶

결과¶

Snowflake Scripting¶

결과¶

알려진 문제¶

1. Labels are not supported in Snowflake Scripting CASE syntax¶

관련 EWIs¶

COMPOUND STATEMENTS¶

일반 설명¶

Oracle 구문¶

Snowflake 구문¶

샘플 소스 패턴¶

1. IF-ELSE block¶

Oracle¶

결과¶

Snowflake¶

결과¶

2. CASE statement¶

Oracle¶

결과¶

Snowflake¶

결과¶

3. LOOP statements¶

Oracle¶

결과¶

Snowflake¶

첫 번째 탭¶

결과¶

4. Procedure call and OUTPUT parameters¶

Oracle¶

결과¶

Snowflake¶

결과¶

5. Alter session¶

Oracle¶

결과¶

Snowflake¶

결과¶