저장 프로시저 또는 사용자 정의 함수 작성 여부 선택하기

이 항목에서는 저장 프로시저와 UDF를 각각 호출할 수 있는 방법과 수행할 수 있는 작업의 차이점을 비롯하여, 저장 프로시저와 UDF 간의 주요 차이점에 대해 설명합니다.

상위 수준에서 저장 프로시저와 UDF는 아래에서 설명하는 대로 일반적으로 이들을 사용하는 방식이 다릅니다.

저장 프로시저 목적

사용자 정의 함수 목적

일반적으로 SQL 문을 실행하여 관리 작업을 수행합니다. 저장 프로시저의 본문은 값(예: 오류 표시기)을 명시적으로 반환하는 것이 허용되지만 필수는 아닙니다.

값을 계산하고 반환합니다. 함수는 항상 식을 지정하여 값을 명시적으로 반환합니다. 예를 들어 JavaScript UDF의 본문에는 값을 반환하는 return 문이 있어야 합니다.

이 항목의 내용:

저장 프로시저 또는 UDF를 만드는 시점

일반적으로, 저장 프로시저 또는 UDF를 만들지 여부를 결정할 때 다음 권장 사항을 고려하십시오.

다음 경우에 저장 프로시저를 만듭니다.

다음 경우에 UDF를 만듭니다.

  • 다른 애플리케이션/시스템에서 기존 저장 프로시저를 마이그레이션하는 경우.

  • 다음 데이터베이스 작업을 수행해야 하는 경우:

    • 일반적인 쿼리와 DML(예: SELECT, UPDATE 등).

    • 임시 테이블 삭제 , N 일 이상 지난 데이터 삭제 또는 사용자 추가와 같은 DDL을 포함한 관리 작업.

  • 다른 애플리케이션/시스템에서 기존 UDF를 마이그레이션하는 경우.

  • SQL 문의 일부로 호출할 수 있고 문에서 사용할 값을 반환해야 하는 함수가 필요한 경우.

  • 출력에 모든 입력 행 또는 모든 그룹에 대한 값을 포함해야 하는 경우. 예:

    select MyFunction(col1) from table1;
    
    select MyAggregateFunction(col1) from table1 group by col2;
    
    Copy

지원되는 처리기 언어

프로시저 또는 UDF를 작성할 때는 지원되는 언어 중 하나로 논리를 처리기로 작성합니다. 다음 표에는 지원되는 언어가 나열되어 있습니다.

저장 프로시저

사용자 정의 함수

Java

Java

JavaScript

JavaScript

Python

Python

Scala

Snowflake Scripting

SQL

사용법 및 동작의 차이점

다음 섹션에서는 프로시저와 UDF로 지원되는 동작의 구체적인 차이점에 대해 설명합니다.

UDF는 반환하지만 저장 프로시저는 값을 반환할 필요가 없음

저장 프로시저와 함수는 둘 다 값을 반환할 수 있습니다. 하지만:

  • 저장 프로시저의 본문은 값(예: 오류 표시기)을 명시적으로 반환하는 것이 허용되지만 필수는 아닙니다. 그 이유는 저장 프로시저의 목적이 일반적으로 SQL 문을 실행하여 관리 작업을 수행하는 것이기 때문입니다.

    프로시저가 명시적으로 아무 것도 반환하지 않더라도 모든 CREATE PROCEDURE 문에는 반환 타입을 지정하는 RETURNS 절이 포함되어야 합니다. 프로시저가 명시적으로 값을 반환하지 않으면 암시적으로 NULL을 반환합니다.

    다음 예제의 코드에서는 RETURNS 절을 사용하여 프로시저에 대한 반환 유형을 선언하지만 오류가 발생한 경우에만 값이 반환됩니다. 즉, 모든 코드 경로가 값을 반환하는 것은 아닙니다.

    create or replace procedure do_stuff(input number)
    returns varchar
    language sql
    as
    $$
    declare
      error varchar default 'Bad input. Number must be less than 10.';
    
    begin
      if (input > 10) then
        return error;
      end if;
    
      -- Perform an operation that doesn't return a value.
    
    end;
    $$
    ;
    
    Copy

    프로시저가 명시적으로 값을 반환하지 않으면 암시적으로 NULL을 반환합니다.

  • UDF는 항상 식을 지정하여 값을 명시적으로 반환합니다. 이는 UDF의 목적이 값을 계산하고 반환하는 것이기 때문입니다. 예를 들어 JavaScript UDF의 본문에는 값을 반환하는 return 문이 있어야 합니다.

UDF 반환 값은 SQL에서 직접 사용할 있지만 저장 프로시저 반환 값은 직접 사용할 수 없음

함수에서 반환된 값과는 달리, 저장 프로시저에서 반환된 값은 SQL에서 직접 사용할 수 없습니다.

저장 프로시저는 값을 반환할 수 있지만, CALL 명령의 구문은 (대다수 처리기 언어의 경우) 반환된 값을 저장할 장소나 이 값에 대해 작업하거나 값을 다른 작업으로 전달할 방법을 제공하지 않습니다. SQL에서는 다음과 같은 문을 구성할 방법이 없습니다.

y = stored_procedure1(x);                         -- Not allowed.
Copy

다음과 같이 저장 프로시저의 반환 값을 간접적으로 사용할 방법은 있습니다.

  • 다른 저장 프로시저 내에서 저장 프로시저를 호출할 수 있습니다. 예를 들어 저장 프로시저 처리기가 JavaScript로 작성된 경우 외부 저장 프로시저의 JavaScript는 내부 저장 프로시저의 출력을 불러오고 저장할 수 있습니다. 하지만 외부 저장 프로시저(그리고 각각의 내부 저장 프로시저)는 여전히 호출자에게 둘 이상의 값을 반환할 수 없습니다.

  • 저장 프로시저를 호출한 다음 RESULT_SCAN 함수를 호출하여 저장 프로시저에 대해 생성된 문 ID를 이 함수에 전달할 수 있습니다.

  • 결과 세트를 임시 테이블이나 영구 테이블에 저장하고 저장 프로시저 호출에서 해당 테이블을 반환한 후 사용할 수 있습니다.

  • 데이터 볼륨이 지나치게 크지 않다면 여러 행과 여러 열을 VARIANT 에 저장하고(예: JSON 값으로 저장) 그 VARIANT 를 반환할 수 있습니다.

UDF는 다른 문의 컨텍스트에서 호출할 수 있고 저장 프로시저는 독립적으로 호출함

저장 프로시저는 값으로 평가되지 않으며, 일반 식을 사용할 수 있는 모든 컨텍스트에서 사용할 수는 없습니다. 예를 들어, 사용자는 SELECT my_stored_procedure()... 를 실행할 수 없습니다.

UDF는 값으로 평가되며, 일반 식을 사용할 수 있는 컨텍스트(예: SELECT my_function() ...)에서 사용될 수 있습니다.

저장 프로시저는 독립 문으로서 호출됩니다. 아래 코드는 저장 프로시저 호출과 함수 호출의 차이점을 보여줍니다.

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;
Copy

함수 및 프로시저 호출에 대한 자세한 내용은 다음을 참조하십시오.

여러 UDF를 하나의 문으로 호출할 수 있고 단일 저장 프로시저는 하나의 문으로 호출함

단일 실행 가능 문은 저장 프로시저를 하나만 호출할 수 있습니다. 반대로, 단일 SQL 문은 여러 함수를 호출할 수 있습니다.

이와 유사하게, 저장 프로시저는 함수와는 달리 식의 일부로 호출할 수 없습니다.

하지만 저장 프로시저 내에서, 저장 프로시저는 다른 저장 프로시저를 호출하거나 재귀적으로 자신을 호출할 수 있습니다. 이에 대한 예는 코드 예 섹션 에 나와 있습니다.

함수 및 프로시저 호출에 대한 자세한 내용은 다음을 참조하십시오.

UDF는 데이터베이스에 액세스할 수 없지만 저장 프로시저는 액세스할 수 있음

저장 프로시저 내에서 SELECT, UPDATE, CREATE와 같은 데이터베이스 작업을 실행할 수 있습니다.

  • 예를 들어 JavaScript 저장 프로시저에서 JavaScript API를 사용하여 이러한 작업을 수행할 수 있습니다.

    아래의 예에서는 저장 프로시저가 다른 저장 프로시저를 호출하는 SQL 문을 만들고 실행하는 방법을 보여줍니다. $$ 는 저장 프로시저에서 JavaScript 처리기 코드의 시작과 끝을 나타냅니다.

    create procedure ...
      $$
      // Create a Statement object that can call a stored procedure named
      // MY_PROCEDURE().
      var stmt1 = snowflake.createStatement( { sqlText: "call MY_PROCEDURE(22)" } );
      // Execute the SQL command; in other words, call MY_PROCEDURE(22).
      stmt1.execute();
      // Create a Statement object that executes a SQL command that includes
      // a call to a UDF.
      var stmt2 = snowflake.createStatement( { sqlText: "select MY_UDF(column1) from table1" } );
      // Execute the SQL statement and store the output (the "result set") in
      // a variable named "rs", which we can access later.
      var rs = stmt2.execute();
      // etc.
      $$;
    
    Copy
  • Snowflake Scripting 저장 프로시저에서 SQL 문을 실행할 수 있습니다.

    아래의 예에서는 저장 프로시저가 다른 저장 프로시저를 호출하는 SQL 문을 만들고 실행하는 방법을 보여줍니다. $$ 는 저장 프로시저에서 Snowflake Scripting 코드의 시작과 끝을 나타냅니다.

    CREATE PROCEDURE ...
      -- Call a stored procedure named my_procedure().
      CALL my_procedure(22);
      -- Execute a SQL statement that includes a call to a UDF.
      SELECT my_udf(column1) FROM table1;
    
    Copy

저장 프로시저와 달리, UDF는 데이터베이스 작업을 수행할 수 있는 API에 대한 액세스 권한이 없습니다.