DataBase/관계형 DB
Function & Procedures
sellen
2025. 7. 21. 00:11
💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass
아래의 글과 함께 보시면 좋습니다.
절차형 SQL 중에서 아직 다루지 못한 사용자 정의 함수와 프로시저를 다루겠습니다.
절차형 SQL (Procedural SQL) 개념
일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다.
종류
| 종류 | 설명 |
| 프로시저 (Procedure) |
일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합 |
| 사용자 정의 함수 (User-Defined Function) |
일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL |
| 트리거 (Trigger) |
DB 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL |
Function
| 구분 | 설명 |
| VOLATILITY |
|
| STABLE |
|
| IMMUTABLE |
|
Function 생성
매개변수 없이 정의
OR REPLACE는 사용하지 않아도 되나 해당 함수를 재정의 할 상황이 있을 경우 사용한다.
CREATE FUNCTION {function_name}( )
RETURNS {type} AS
$$ {query} $$
LANGUAGE {language};CREATE OR REPLACE FUNCTION hello_world()
RETURNS TEXT AS -- 어떤 타입의 값을 반환할 건지 선언해야 한다.
$$ -- 다른 구분자를 사용해도 상관없다. postgreSQL이 자동으로 인식한다.
SELECT 'hello World';
$$
LANGUAGE SQL; -- 어떤 언어를 사용할 것인지 선언해야 한다.사용할 때는 DML과 같이 사용한다.
SELECT hello_world();매개변수와 같이 정의
CREATE FUNCTION {function_name}( argument_name type )
RETURNS {type} AS
$$ {query} $$
LANGUAGE {language};CREATE OR REPLACE FUNCTION hello_world(user_name text) -- 매개변수와 매개변수의 타입을 선언
RETURNS TEXT AS
$$
SELECT 'hello ' || user_name;
$$
LANGUAGE SQL;사용할 때는 SELECT와 같이 사용한다.
SELECT hello_world('nico');여러 개의 매개변수와 같이 정의
파라미터 없이 타입만 선언할 수도 있다.
이때, $num을 사용하여 몇 번째 파라미터인지 선택이 가능하다.
CREATE OR REPLACE FUNCTION hello_world(text, text)
RETURNS TEXT AS
$$
SELECT 'hello ' || $1 || ' & ' || $2;
$$
LANGUAGE SQL;Function 특징
CREATE OR REPLACE FUNCTION hello_world() -- 매개변수 없음
RETURNS TEXT AS
$$
SELECT 'hello ' || user_name;
$$
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION hello_world(user_name text) -- 매개변수 있음
RETURNS TEXT AS
$$
SELECT 'hello ' || user_name;
$$
LANGUAGE SQL;이렇게 매개변수가 없는 함수를 선언하고 매개변수가 존재하는 같은 이름의 함수를 선언했을 때,
SELECT hello_world(); -- 출력값 : hello World
SELECT hello_world('nico'); -- 출력값 : hello nico이렇게 실행을 할 경우 SQL은 이름은 같지만 매개변수가 다르므로 각각 다른 함수로 실행되어 출력된다.
이처럼 SQl은 함수의 이름, 매개변수, 출력값에 따라 구분한다.
Function 삭제
DROP FUNCTION {function_name} (argument_type);DROP FUNCTION hello_world(); -- 매개변수가 없는 hello_world 함수만 삭제
DROP FUNCTION hello_world(text); -- text를 매개변수로 가지는 hello_world 함수만 삭제Query에서 사용하기
1. Table을 매개변수로 하는 함수 선언
-- 예산 보다 수익이 높은지 아닌지 체크
CREATE OR REPLACE FUNCTION isHit(movie movies) -- table을 타입으로 사용가능
RETURNS text AS
$$
SELECT
CASE
-- movies 매개변수의 이름을 movie로 설정했으므로 movie. 을 사용
WHEN movie.revenue > movie.budget THEN 'Hit'
WHEN movie.revenue < movie.budget THEN 'Flop'
ELSE 'Tie'
END;
$$
LANGUAGE SQL;2. 쿼리에 사용
SELECT
movie_id, title, budget, revenue,
isHit(movies) as result -- movies 테이블을 매개변수로 받음
FROM
movies여러 개의 값을 반환하는 함수 선언
리턴을 변경할 경우 기존의 함수를 삭제하고 다시 선언해야 한다.
DROP FUNCTION IF EXISTS isHit;
CREATE OR REPLACE FUNCTION isHit(movie movies)
RETURNS TABLE (result TEXT, Differ numeric) AS
$$
SELECT
CASE
WHEN movie.revenue > movie.budget THEN 'Hit'
WHEN movie.revenue < movie.budget THEN 'Flop'
ELSE 'N/A'
END,
movie.revenue - movie.budget
$$
LANGUAGE sql;SELECT
movie_id, title,
isHit(movies) as result -- (N/A, 0) 이렇게 튜플 값으로 출력된다.
FROM
movies
order by movie_id;튜플로 출력되는 것을 방지하기 위해 ( )로 감싸주고 .*을 사용하면 된다.
SELECT
movie_id, title,
(isHit(movies)).*
FROM
movies
order by movie_id;PostgreSQL에서 updated_at을 자동으로 입력하기
MySQL과는 달리 PostgreSQL은 ON UPDATE가 없어서 별도로 구현을 해야 한다.
1. 업데이트 시간을 변경하는 함수 선언
trigger를 반환하므로 sql이 아닌 plpgsql을 사용한다.
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS
$$
-- plpgsql는 BEGIN ~ END 사이에 작업 내용을 작성한다.
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW; -- 새로운 row를 반환
END;
-- PostgreSQL에서 지원하는 Procedural Language PostgreSQL
-- trigger나 function을 작성할 때 사용
$$ LANGUAGE plpgsql;2. TRRIGER 선언
OF {column_name}을 사용하면 특정 컬럼에서만 동작하도록 할 수 있다
CREATE TRIGGER updated_at
BEFORE UPDATE -- OF title
ON moviesProcedures
💡 Function과의 차이점
- return 값이 없어도 된다
- DML에서 사용되지 않는다
Procedures 생성
반환값 없이 Procedure 생성
CREATE PROCEDURE {procedure_name} AS
$$ {query} $$
LANGUAGE {language_type};CREATE PROCEDURE set_zero_revenue() AS
$$
UPDATE movies SET revenue = NULL WHERE revenue = 0;
$$
LANGUAGE SQL;DML을 통해 호출하던 function과는 다르게 CALL을 사용한다.
CALL set_zero_revenue();반환값이 존재하는 Procedure 생성
-- 입력값과 반환값의 타입이 같을 경우 INOUT을 사용해도 된다.
CREATE PROCEDURE hello_world(IN name TEXT, OUT greeting TEXT) AS
$$
BEGIN
greeting = 'Hello, ' || name || '!';
END;
$$
LANGUAGE plpgsql;입력값과 반환값을 설정했으므로 Procedure를 호출할 때 둘을 입력해야 한다.
CALL hello_world('nico', NULL); -- 반환할 때 사용하는 변수의 초기값을 NULL로 지정Plpgsql을 사용하여 Procedure 생성
Plipgsql
- Procedural Language PostgreSQL
- 로직이 들어간 연산이나 변수를 생성하는 등의 작업을 개선하기 위해 만들어진 언어
Plipgsql로 Procedure 생성
CREATE PROCEDURE translate_hello(IN name TEXT, IN lang TEXT, OUT greeting TEXT) AS
$$
DECLARE
-- plpgsql에서는 = 대신 := 를 사용
spanish TEXT := 'hola';
italian TEXT := 'ciao';
korean TEXT := '안녕';
BEGIN
-- 비교를 할 때 == 가 아닌 = 를 사용한다.
IF lang = 'kr' THEN
greeting := korean || ' ' || name || '!';
ELSIF lang = 'es' THEN
greeting := spanish || ' ' || name || '!';
ELSIF lang = 'it' THEN
greeting := italian || ' ' || name || '!';
ELSE
greeting := 'hello' || ' ' || name || '!';
END IF;
END;
$$
LANGUAGE plpgsql;CALL translate_hello('nico', 'kr', NULL); -- 안녕 nico!
CALL translate_hello('nico', 'es', NULL); -- hola nico!
CALL translate_hello('nico', 'it', NULL); -- ciao nico!
CALL translate_hello('nico', 'ja', NULL); -- hello nico!