[Snowflake] Stored Procedures & UDFs

Stored Procedures 기본 개념

정의

구분	내용
RDBMS 정의	SQL 문장들의 명명된 집합(named collections)
특징	절차적 로직(procedural logic) 포함 가능
Snowflake	분기(branching), 반복(looping) 등 프로그래밍 구조 사용 가능

역할 분리

역할	담당
DBA (Database Admin)	프로시저 생성 CREATE PROCEDURE
DE (Data Engineer)	프로시저 실행 EXECUTE / CALL

주요 활용 목적

#	목적
1	여러 DB 작업이 필요한 태스크 자동화
2	데이터베이스 작업 동적 생성 및 실행
3	소유자(Owner) 권한으로 코드 실행 → 권한 위임 가능

예제 코드 (슬라이드 — MS SQL Server 기준)

CREATE PROCEDURE CLEAR_EMP_TABLES
AS
BEGIN
    DELETE FROM EMP01 WHERE EMP_DATE < DATEADD(MONTH, -1, GET_DATE())
    DELETE FROM EMP02 WHERE EMP_DATE < DATEADD(MONTH, -1, GET_DATE())
    DELETE FROM EMP03 WHERE EMP_DATE < DATEADD(MONTH, -1, GET_DATE())
    DELETE FROM EMP04 WHERE EMP_DATE < DATEADD(MONTH, -1, GET_DATE())
    DELETE FROM EMP05 WHERE EMP_DATE < DATEADD(MONTH, -1, GET_DATE())
END

-- 실행
EXECUTE CLEAR_EMP_TABLES;

---

JavaScript Stored Procedure (Snowflake)

지원 언어 5가지

언어	SP	UDF
Java	✅	✅
JavaScript	✅	✅
Python	✅	✅
Scala	✅	✅
Snowflake Scripting / SQL	✅	✅

핵심 구문 정리

구문	설명
RETURNS	필수(mandatory) — 반환값 없어도 반드시 명시, 없으면 NULL 반환
LANGUAGE	사용할 언어 지정 (예: JAVASCRIPT)
EXECUTE AS OWNER	소유자(Owner) 권한으로 실행
EXECUTE AS CALLER	호출자(Caller) 권한으로 실행
$$	프로시저 본문의 시작/끝 구분자
snowflake.execute()	JavaScript API — DB 작업 실행 메서드

예제 코드 (슬라이드 기준)

CREATE PROCEDURE EXAMPLE_STORED_PROCEDURE(PARAM1 STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS OWNER
AS
$$
    var param1 = PARAM1;
    var sql_command = "SELECT * FROM " + param1;
    snowflake.execute({sqlText: sql_command});
    return "Succeeded.";
$$;

-- 호출
CALL EXAMPLE_STORED_PROCEDURE('EMP01');

⚠️ CALL 키워드로 호출 — SQL 표현식 안에서 직접 호출 불가

---

Stored Procedures vs UDFs 비교

핵심 목적 차이

구분	목적
UDF	값을 계산하고 반환 (calculate & return a value)
Stored Procedure	액션 수행 (perform actions — DDL, DML 등)

기능 비교표

Feature	UDF	SP
SQL 문 안에서 호출	✅	❌
오버로드(Overload) 가능	✅	✅
0개 이상 입력 파라미터	✅	✅
JavaScript API 사용	❌	✅
반환값 선택적(optional)	❌ 항상 필수	✅ 선택 가능
반환값을 SQL에서 직접 사용	✅	❌
재귀 호출 가능	❌	✅ (최대 16depth)

각 항목 근거 요약

Feature	근거
SQL 호출	SP는 CALL로만 실행, SELECT sp() 형태 불가
오버로드	파라미터 수 또는 타입이 다르면 동일 이름 사용 가능
JavaScript API	SP만 snowflake.execute() 사용 가능
반환값	UDF는 항상 return 필수 / SP는 없으면 NULL 반환
재귀 호출	SP 내부에서 자기 자신 호출 가능, 최대 스택 16

---

최종 체크리스트

□ SP 지원 언어 5개 → Java / JavaScript / Python / Scala / Snowflake Scripting
□ RETURNS 절 → 필수(mandatory), 값 없으면 NULL 반환
□ $$ → 본문 시작/끝 구분자
□ EXECUTE AS → OWNER(소유자) / CALLER(호출자) / RESTRICTED CALLER
□ SP 호출 → CALL 키워드, SQL 표현식 안 사용 불가
□ UDF 호출 → SQL 문 안에서 바로 사용 가능
□ JavaScript API → SP만 사용 가능 (UDF 불가)
□ 재귀 호출 → SP만 가능, 최대 depth 16
□ UDF 목적 → 값 계산 후 반환
□ SP 목적 → 액션 수행 (DDL, DML 등)