개발자이너

근무중 Trigger를 사용하게 될 일이 생겼는데 trigger 기능을 사용하려다 보니 trigger안에 sql을 직접 짜기 보다는 

stored function을 쓴다고 하는 글을 보게 되었다. 근데 나는 생각해 보니까 Procedure를 쓴다는 글도 본거 같아서 정리를 해보려고 한다.

Stored Function

CREATE FUNCTION 함수이름(파라미터이름 데이터타입, ...)
RETURNS 반환타입 AS $$
BEGIN
    -- 함수 로직
    RETURN 반환값;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION 으로 다음에 함수 이름과 파라미터 목록을 작성

RETURNS 로 함수가 반환할 데이터 타입을 지정

LANGUAGE plpgsql 은 PostgreSQL의 절차적 언어인 PL/pgSQL을 사용한다는 의미이다.

간단한 예제로는 

CREATE FUNCTION is_adult(age INTEGER)
RETURNS BOOLEAN AS $$
BEGIN
    IF age >= 18 THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

와 같이 다음의 나이를 받아 성인인지 여부를 반환한다.

CREATE FUNCTION get_users()
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM users;
END;
$$ LANGUAGE plpgsql;

여러 행의 결과를 반환해야 할 때는 TABLE 키워드를 사용하여 테이블을 반환할 수 있다.

SELECT add_numbers(10, 20);  -- 결과: 30
SELECT is_adult(20);         -- 결과: TRUE
SELECT * FROM get_users();   -- 여러 행의 결과 반환

정의한 함수를 호출하려면 SELECT 문을 사용한다.

Procedure

procedure 인지 stored procedure인지는 정확하게 모르겠다.

기본 구문은 아래와 같다.

CREATE PROCEDURE 프로시저이름(파라미터이름 데이터타입 [IN | OUT | INOUT], ...)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 프로시저 로직
END;
$$;

CREATE PROCEDURE 다음에 프로시저 이름과 파라미터 목록을 작성한다.

파라미터는 IN(입력), OUT(출력), INOUT(입출력)으로 지정이 가능하다.

간단한 예제로는

CREATE PROCEDURE add_employee(name TEXT, age INTEGER, department TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, age, department) VALUES (name, age, department);
END;
$$;

새로운 직원을 추가하는 add_employee 프로시저이다.

또 다른 출력 파라미터를 사용하는 예제는

CREATE PROCEDURE sum_numbers(a INTEGER, b INTEGER, OUT result INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    result := a + b;
END;
$$;

과 같이 두 수의 합을 축력하는 sum_numbers 프로시저도 있다.

프로시저 호출 방법은 CALL구문을 사용한다.

CALL add_employee('John Doe', 30, 'Engineering');

CALL sum_numbers(10, 20, result := 0);
SELECT result; -- 출력 파라미터로 값이 설정됨

그럼 두개의 차이는 무엇일까?

1. 반환 값

• Stored Function: 특정 값을 반환합니다. 호출 시 반드시 하나의 값을 반환해야 하며, 주로 계산 결과나 특정 값을 가져오는 데 사용됩니다.
• Stored Procedure: 값을 반환할 필요가 없으며, 여러 개의 값을 반환하거나 반환하지 않을 수 있습니다. 주로 데이터를 수정하는 작업(예: INSERT, UPDATE, DELETE)에 사용됩니다.

2. 호출 방식

• Stored Function: SQL 구문에서 함수를 다른 구문처럼 사용할 수 있으며, SELECT 문에서 바로 호출하여 결과를 가져올 수 있습니다. 예: SELECT function_name(parameters);
• Stored Procedure: 일반적으로 CALL 키워드를 사용하여 호출됩니다. 예: CALL procedure_name(parameters);

3. 사용 용도

• Stored Function: 주로 값을 계산하거나 특정 데이터를 반환하는 용도로 사용됩니다. 로직을 SQL 내에 삽입해서 활용할 수 있습니다.
• Stored Procedure: 데이터 조작, 여러 SQL 작업을 순차적으로 처리하는 데 유용합니다.

4. 파라미터 전송 방식

• Stored Function: 입력 파라미터만 받을 수 있습니다.
• Stored Procedure: 입력(IN), 출력(OUT), 입력 및 출력(INOUT) 파라미터를 모두 받을 수 있어 다방면으로 데이터 주고받기가 가능합니다.

5. SQL 문에서의 사용 가능성

• Stored Function: 함수는 SQL 문의 일부로서 사용이 가능하여 WHERE, SELECT, ORDER BY 등 다양한 절에서 호출이 가능합니다.
• Stored Procedure: 프로시저는 이러한 구문 내에서 직접 호출할 수 없습니다. 단독으로 호출되어야 합니다.

요약

• Stored Function은 값을 반환하며, SQL 구문에 삽입하여 계산 목적으로 주로 사용됩니다.
• Stored Procedure는 다양한 작업을 처리하며, 단독으로 호출되어 데이터 조작을 수행하는 데 더 적합합니다.