저장 프로시저 개요

함수와 마찬가지로, 저장 프로시저는 한 번 생성하고 나면 여러 번 실행할 수 있습니다. 저장 프로시저는 CREATE PROCEDURE 명령으로 생성되고 CALL 명령으로 실행됩니다.

다음 언어 중 하나로 저장 프로시저를 작성할 수 있습니다.

저장 프로시저에서 단일 값 또는 (Snowflake Scripting을 사용 중인 경우) 테이블 형식 데이터를 반환할 수 있습니다.

참고

익명 프로시저를 만들기도 하고 호출도 하려면 CALL(익명 프로시저 사용) 를 사용하십시오. 익명 프로시저를 만들고 호출하는 데는 CREATE PROCEDURE 스키마 권한이 있는 역할이 필요하지 않습니다.

이 항목의 내용:

저장 프로시저의 이점

저장 프로시저는 다음을 허용합니다.

  • 단순한 SQL이 지원하지 않는 프로시저 논리(분기 및 루핑).

  • 오류 처리.

  • SQL 문의 동적 생성과 실행.

  • 프로시저를 실행하는 역할의 권한이 아니라 프로시저를 소유한 역할의 권한으로 실행되는 코드 작성. 이를 통해 저장 프로시저 소유자는 지정된 작업을 수행할 수 없는 사용자에게 작업 수행 권한을 위임할 수 있습니다. 하지만 이러한 소유자 권한 저장 프로시저에는 제한이 있습니다.

저장 프로시저의 한 가지 일반적인 용도는 여러 개의 SQL 문이 필요하고 자주 수행되는 작업을 자동화하는 것입니다. 예를 들어, 지정된 날짜보다 오래된 데이터를 삭제하여 데이터베이스를 정리하고 싶다고 생각해 보십시오. 여러 DELETE 문을 작성할 수 있으며, 각각의 명이 특정한 한 테이블에서 데이터를 삭제합니다. 이러한 모든 문을 단일 저장 프로시저에 넣고 마감 날짜를 지정하는 매개 변수를 전달할 수 있습니다. 그런 다음, 그냥 이 프로시저를 호출하여 데이터베이스를 정리할 수 있습니다. 데이터베이스가 변경될 때 프로시저를 업데이트하여 추가 테이블을 정리할 수 있습니다. 정리 명령을 사용하는 사용자가 여러 명인 경우 모든 테이블 이름을 기억하고 각 테이블을 개별적으로 정리하는 게 아니라 하나의 프로시저를 호출해 정리할 수 있습니다.

저장 프로시저와 UDFs의 차이점

저장 프로시저와 UDFs(사용자 정의 함수)는 둘 다 모듈식 코드를 더 쉽게 작성할 수 있게 해줍니다. 하지만 UDFs와 저장 프로시저는 중요한 차이점이 있습니다.

저장 프로시저에서의 값 반환은 선택 사항

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

  • 함수의 목적 은 값을 계산하고 반환하는 것입니다. 함수는 항상 식을 지정하여 명시적으로 값을 반환합니다(언어에 따라 return 과 같은 키워드 포함 또는 제외). 예를 들어 JavaScript UDF의 본문에는 값을 반환하는 return 문이 있어야 합니다.

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

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

    예를 들어, 다음 의사 코드에서 RETURNS 절은 필수이지만 RETURN 문은 선택 사항입니다.

    CREATE PROCEDURE f()
    RETURNS INTEGER          -- required
    LANGUAGE SQL
    AS
    $$
        ...
        RETURN error_code;   -- optional
    $$
    ;
    

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

저장 프로시저는 독립된 문으로 호출됨

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

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

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

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;

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

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

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

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

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

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

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

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

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

CALL 문마다 단 하나의 저장 프로시저 호출

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

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

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

저장 프로시저 호출에 대한 자세한 내용은 저장 프로시저 호출하기 를 참조하십시오.

저장 프로시저는 데이터베이스에 액세스하고 중첩 쿼리를 실행할 수 있습니다

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

  • JavaScript 저장 프로시저에서 JavaScript API(JavaScript 오브젝트 및 메서드 형식)를 사용하여 이러한 작업을 수행할 수 있습니다.

    아래의 예에서는 저장 프로시저가 다른 저장 프로시저를 호출하는 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.
      $$;
    
  • 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;
    

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

저장 프로시저 또는 UDF 생성 선택하기

일반적으로, 저장 프로시저 또는 UDF를 만들지 여부를 결정할 때 다음을 수행합니다.

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

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

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

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

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

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

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

    select MyFunction(col1) from table1;
    
    select MyAggregateFunction(col1) from table1 group by col2;
    
맨 위로 이동