DBMS 를 잘 활용하기 위해서는 function의 기능과 procedure의 기능을 알아두면 편리합니다.
오늘은 간단하게 function과 procedure에 대해 알아보고 간단한 사용 예를 기록해두려고 합니다.
함수와 프로시저
프로시저
- 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이며, 일련의 작업을 정리한 절차입니다.
- 보통 단독으로 실행해야 할 작업을 위임받았을 때 사용합니다.
함수
- 하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 코드의 집합입니다.
- 즉, 함수가 여러 작업을 위한 기능이라면 프로시저는 작업을 정리한 절차입니다.
- 보통 로직을 도와주는 역할이며, 간단한 계산, 수치 등을 나타낼 때 사용합니다.
매개변수
프로시저
- 매개변수를 입력, 출력, 입출력 형식으로 받을 수 있습니다.
함수
- 매개변수를 입력 형식으로만 받을 수 있습니다.
반환값
프로시저
- 반환값을 가질 수도 있고 가지지 않을 수도 있습니다.
함수
- 반환값을 반드시 가져야 합니다.
쿼리문 내에서 실행
프로시저
- SELECT, WHERE 문 등에서 사용 불가합니다.
함수
- SELECT, WHERE 문 등에서 사용 가능합니다.
실행 속도
프로시저
- 클라이언트(화면)에서 값을 받아 서버에서 작업을 한 뒤 클라이언트에게 전달합니다.
- 즉, 서버에서 실행이 되어 속도면에서 빠른 성능을 보여줍니다.
함수
- 클라이언트(화면)에서 값을 받고 서버에서 필요한 값을 가져와서 클라이언트에서 작업을 하고 반환합니다.
- 즉, 클라이언트(화면)에서 실행이 되어 프로시저보다는 속도가 느립니다.
Example
간단한 예시를 통해 연습을 진행해봅니다.
함수를 생성하는 예는 아래와 같습니다.
Practice 1
create or replace function add(a int,b int)
returns int
as $$
begin
return a+b;
end;
$$ language plpgsql;
Practice 2
create or replace function hello_world(name text)
returns text as $$
declare
greeting text;
begin
greeting := 'Hello, ' || name || '!' ;
return greeting;
end;
$$ language plpgsql ;
Practice 3
프로시저를 생성하는 예는 다음과 같습니다.
함수는 call 명령어를 통해 실행합니다.
화면에 특정 문구를 보이고 싶다면 raise notice 문을 활용합니다.
create or replace procedure hello_world2(name text)
language plpgsql
as $$
declare
greeting text ;
begin
greeting := 'Hello, ' || name || '!';
raise notice '%', greeting ;
end ;
$$ ; 
다음은 입력받은 매개변수 user_id 이하의 index를 가진 user를 반환하는 함수를 생성합니다.
Create Function Sample
create or replace function fetch_user_by_id(user_id int)
returns record
as $$
declare
result record ;
begin
select * into result from dummy_test where idx2 <= user_id ;
return result;
end;
$$
language plpgsql ;
결과값은 위의 사진과 같습니다.
declare 문 안에서 result 변수를 record라는 타입으로 지정해주었습니다. record 타입은 하나의 row 만을 가질 수 있기 때문에 user_id가 5 이하인 user를 모두 반환해야하지만 하나의 row 만 보여주게됩니다.
그렇다면 이런 경우 어떻게 해당 조건에 일치하는 row를 모두 불러올 수 있을까요?
Procedure를 활용한 방법으로 알아보겠습니다.
Ceate Pocedure Smple
create or replace procedure fetch_user_by_id(user_id int)
language plpgsql
as $$
declare
user_cursor cursor for
select * from dummy_test where idx2 < 5;
user_record record;
begin
-- CURSOR를 열고 데이터를 반복적으로 읽을 수 있습니다.
open user_cursor;
-- CURSOR를 통해 데이터를 반복적으로 읽어옵니다.
loop
fetch user_cursor into user_record;
exit when not found; -- 더 이상 데이터가 없으면 루프를 종료합니다.
-- user_record 변수에 현재 행의 데이터가 들어 있습니다.
-- 여기에서 원하는 작업을 수행할 수 있습니다.
-- 예시로 출력하는 경우:
RAISE NOTICE 'idx2: %, test_string1: %, test_string2: %', user_record.idx2, user_record.test_string1, user_record.test_string2;
end loop;
close user_cursor;
end;
$$;
위와 같이 잘 나오는 것을 알 수 있습니다.
Reference
아래 코드는 제가 공부하는데 도움받은 GPT 4.0이 제공한 코드입니다.
참고를 위해 남겨두겠습니다.
CREATE OR REPLACE PROCEDURE process_users_above_18()
LANGUAGE plpgsql
AS $$
DECLARE
user_cursor CURSOR FOR
SELECT name, email FROM users WHERE age > 18;
user_record RECORD;
BEGIN
-- CURSOR를 열고 데이터를 반복적으로 읽을 수 있습니다.
OPEN user_cursor;
-- CURSOR를 통해 데이터를 반복적으로 읽어옵니다.
LOOP
FETCH user_cursor INTO user_record;
EXIT WHEN NOT FOUND; -- 더 이상 데이터가 없으면 루프를 종료합니다.
-- user_record 변수에 현재 행의 데이터가 들어 있습니다.
-- 여기에서 원하는 작업을 수행할 수 있습니다.
-- 예시로 출력하는 경우:
RAISE NOTICE 'User Name: %, Email: %', user_record.name, user_record.email;
END LOOP;
-- CURSOR를 닫습니다.
CLOSE user_cursor;
END;
$$;
프로시저와 함수를 이용하면
DB Engineer