🍀
Aloo Coding

컴퓨터 공부/📗 CS

[데이터베이스] 11장 - stored procedure + 프로시저의 동작 방식과 특징, stored function과의 차이

letzgorats 2024. 1. 15. 17:32

✅ stored procedure 의 뜻과 예제

: 사용자가 정의한 프로시저

: RDBMS에 저장되고 사용되는 프로시저

: 구체적인 하나의 task(태스크)를 수행한다.

 

stored procedure를 만들 때도, delimiter 라는 키워드를 사용한다.

 

delimiter 는 구분자를 의미하는데, 기본적으로 SQL 에서 사용되는 delimiter는 (;)세미콜론이다.

하지만, stored program의 정의를 서버로 보내기 위해서는 delimiter를 일시적으로 재정의 해주어야만 한다.

이 때, 주의해야 할 점은 DELIMITER를 재정의하고 stored program을 정의한 후에 delimiter를 기존의 세미콜론(;)으로 바꿔주는 것이 좋다. (계속해서 DELIMITER를 사용자가 재정의한 것으로 사용할게 아니라면 말이다.)

또한, 사용자가 위 코드처럼 DELIMITER를 $$ 가 아닌 어떤 문자나 문자열을 한글자 혹은 여러글자로 구성해서 선언할 수도 있다. 하지만, 백슬래시(\)만큼은 피해야 한다. 왜나하면 백슬래시(\)는 MySQL에서 escape문이기 때문이다.
만약 stored program 내부에 ;(세미콜론)이 사용되지 않는다면 굳이 불필요하게 DELIMETER를 사용하지 않아도 된다.

 

대략적인 구문은 이렇다.

delimiter 를 지정해주고 시작한다.
CREATE PROCEDURE 키워드를 통해 프로시저를 만들고 프로시저 이름을 지정한다. 
입력 파라미터가 있다면, IN 키워드를 적어준 뒤, (파라미터 자료형) 순으로 적어줘야한다.
반환 파라미터가 있다면, OUT 키워드를 적어준 뒤, (파라미터 자료형) 순으로 적어줘야한다.
(※ IN 이나 OUT 키워드를 적어주지 않으면, 디폴트로 입력 파라미터로 인식을 한다. 그래서 진짜 입력 파라미터는 IN 키워드를 생략할 수 있다. 하지만, OUT 파라미터는 반드시 앞에 OUT 키워드를 명시해야 OUT파라미터로 인식한다.)

body 부분은 BEGIN 으로 시작하고 END 로 끝난다. ( 이 때는 SET 키워드로 값을 반환한다.)
지정했던 delimiter 로 함수생성완료를 닫아주고, 다시 세미콜론(;)으로 바꿔준다.

프로시저 호출을 할 때는, call 키워드 뒤에 만들었던 프로시저이름을 써주고, 각 파라미터에 인자값을 준다.
이 때, OUT 파라미터에 해당하는 값은 @사용자변수 를 사용해서 값을 사용자변수에 담아내고, select @사용자변수 로 값을 볼 수 있다. 

 

※ 그럼 이제, stored function 을 작성하고 각 예제를 보면서 차근차근 살펴보도록 하자.

 

stored procedure 예제 1)

- 두 정수의 곱셈 결과를 가져오는 프로시저를 작성하자.

 

delimiter $$
CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
    BEGIN
        SET result = a * b;
    END
    $$
delimiter ;

프로시저 작성

call product(5,7,@result);
select @result;

프로시저 호출 후, 결과 확인

 

 

다른 예제를 살펴보자. stored procedure 예제 2)

- 두 정수를 맞바꾸는 프로시저를 작성하자. (swap)
delimiter $$
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
    BEGIN
        SET @temp = a;
        SET a = b;
        SET b = @temp;
    END
    $$
delimiter ;

 

이 때는 INOUT 키워드를 사용한다.

INOUT 키워드 뒤에는 값을 전달할 수 있으면서 동시에 값을 바꿔서 저장할 수도 있는 인자가  올 수 있다.

 

즉, INOUT으로 파라미터를 정의하게 되면, 호출하면서 파라미터에 값을 전달할 수도 있고, 동시에 반환값을 파라미터에 저장을 할 수가 있는 것으로, IN 과 OUT 의 역할을 둘 다 할 수 있는 셈이다.

프로시저 생성

 

SET @a=5,@b=7;
call swap(@a,@b);
SELECT @a,@b;

 

변수가 swap된 결과

 

 

또 다른 예제를 살펴보자. stored procedure 예제 3)

- 각 부서별 평균 연봉을 가져오는 프로시저를 작성하자.

EMPLOYEE 테이블

delimiter $$
CREATE PROCEDURE get_dept_avg_salary()
BEGIN
    SELECT dept_id, avg(salary)
    FROM EMPLOYEE
    GROUP BY dept_id;
END
$$
delimiter ;

프로시저 생성

 

body부분의 쿼리를 살펴보자.

EMPLOYEE 테이블에 dept_id 가 있기 때문에, GROUP BY 를 통해 dept_id 로 묶어주고 각 그룹별로 평균연봉을 계산해서 각 dept_id 와 함께 결과를 가져오는 쿼리이다.

SELECT 문을 작성해주면, mySQL에서는 따로 return 키워드나 OUT 파라미터를 쓰지 않고 쿼리 자체만 써주더라도 결과를 반환할 수 있다.

그래서, 그대로 프로시저를 call() 해주면, 결과를 반환한다.

call get_dept_avg_salary();

부서별 평균 연봉 결과

 

마지막 예제를 살펴보자. stored procedure 예제 4)

- 연예인이 닉네임을 바꾸면 이전 닉네임을 로그에 저장하고 새 닉네임으로 업데이트하는 프로시저를 작성하자.

CELEBRITY 테이블

delimiter $$
CREATE PROCEDURE change_nickname(cel_id INT, new_nick varchar(24))
BEGIN
    INSERT INTO NICKNAME_LOGS(
        SELECT id, nickname, now() FROM CELEBRITY WHERE id=cel_id
    );
    
    UPDATE CELEBRITY SET nickname = new_nick WHERE id=cel_id;
END
$$
delimiter ;

 

change_nickname 프로시저는 정수타입의 매개변수 'cel_id'와 varchar(24)타입의 매개변수 'new_nick' 을 받는다. 이 매개변수는 연예인의 각 id와 새로운 닉네임을 나타낸다. ( * 여기서 따로 IN 키워드를 적어주지 않으면 디폴트로 입력 파라미터로 인식한다.)

 

기존의 닉네임을 NICKNAME_LOGS 라는 테이블에 저장을 해야 하는데, 그 역할을 수행하는 부분이 아래 부분이다.

INSERT INTO NICKNAME_LOGS(
        SELECT id, nickname, now() FROM CELEBRITY WHERE id=cel_id
    );

 

기존의 닉네임 정보를 가져오기 위해 CELEBRITY 테이블에서 파라미터로 전달받은 cel_id 와 id 값이 같다는 조건을 걸고,

해당 연예인의 (id, nickname, 현재시간) 을 가져온다. 현재시간은 업데이트되는 시간을 저장하기 위해서 가져오는 것이다.

 

이렇게 SELECT를 통해서 기존 연예인의 정보를 가져왔다면, 그 nickname 정보를 NICKNAME_LOGS 테이블에 저장해야하기 때문에, INSERT INTO 로 정보를 추가해준다.

기존의 정보를 NICKNAME_LOGS 에 추가해줬고, 새로 바뀐 nickname을 기존 CELEBRITY 테이블에서 바꿔줘야 하는데, 그 역할을 수행하는 부분이 아래 부분이다.

UPDATE CELEBRITY SET nickname = new_nick WHERE id=cel_id;

 

 

UPDATE 문을 통해 기존 닉네임을 new_nick으로 바꿔준다.

change_nickname 프로시저 생성

 

그럼 이제, change_nickname 프로시저가 완성됐으니, call 을 통해 호출을 해보자.

call change_nickname(1,'국민 MC');  // 유재석 별명 바꾸기

닉네임 바꾸기

 

닉네임 바꾸기 로그 결과

 

그럼 다른 사람들도 쫌 바꿔보고, CELEBRITY 테이블과 NICKNAME_LOGS 테이블이 어떻게 변하는지 살펴보자.

call change_nickname(1,'날라리');
call change_nickname(2,'소년명수');
call change_nickname(3,'쩌리짱');
call change_nickname(4,'러시아아저씨');
call change_nickname(5,'카이저소제');
call change_nickname(6,'쌍둥이아빠');
call change_nickname(7,'다가와다가와');
call change_nickname(8,'해양생물');
call change_nickname(9,'김동준친구');

call change_nickname(1,'유느님');
call change_nickname(2,'군산의 아들');
call change_nickname(7,'신화출신개그맨');
call change_nickname(3,'하와수 중 하');
call change_nickname(6,'도니');
call change_nickname(4,'촉새');
call change_nickname(9,'홍진경');
call change_nickname(5,'뽀로로');

바뀐 celebrity 테이블
NICKNAME_LOGS 테이블 

 

NICKNAME_LOGS 테이블을 보면, 바로 직전의 닉네임이었던 별명들이 id별로 잘 쌓인 것을 볼 수 있다.

 

✅ 이외에 stored procedure 이 할 수 있는 일

MySQL 에서는 stored procedure은

  • 조건문을 통해 분기처리를 하거나
  • 반복문을 수행하거나 
  • 에러를 핸들링하거나 에러를 일으키는 등의 다양한 동작을 정의할 수 있다.

stored procedure 은 데이터 조작 및 트랜지션 관리를 해서 데이터 무결성을 관리한다. 한 번 컴파일된 후에는 개별 SQL 쿼리보다 빠르게 실행되므로 데이터베이스 서버로 전송되는 정보의 양을 줄일 수 있고, 다양한 프로그램과 애플리케이션에서 재사용될 수 있다. 변경사항을 중앙에서 관리하기 때문에, 유지보수가 간편해지는 것이죠.

하지만, 너무 복잡해지면 유지보수가 어려워질 수도 있으니, 기능을 명확하고 간단하게 설계하는 것이 중요하며, 적절한 접근 제어와 보안 관행을 유지하면서 작성하는 것이 중요하다.

✅ stored procedure VS stored function

(※ MySQL, Oracle, MS SQL Server, PostgreSQL 대상으로 조사하여 공통적인 부분을 묶어서 정리한 내용이다. - 일부 디테일한 부분에서 차이가 있을 수 있다.)

  stored procedure(프로시저) stored function(함수)
create 문법 CREATE PROCEDURE CREATE FUNCTION
return 키워드로 값 반환 불가능
(SQL server는 상태코드 반환용으로는 사용가능)		 가능
(MySQL, SQL server는 값 반환하려면 필수)
파라미터로 값(들) 반환 가능
(값(들) 을 반환하려면 필수)		 일부 가능
(ORACLE은 가능하나, 권장 안함, PostgreSQL은 가능)
값을 꼭 반환해야 하나? 필수 아님 필수
SQL statement 에서 호출 불가능 가능
transaction 사용 가능 대부분 불가능
(ORACLE 경우 가능)
주된 사용 목적 business logic computation

 

CREATE 문법

  • stored procedure 는 'CREATE PROCEDURE' 문을 사용하여 생성하며, 복잡한 비즈니스 로직을 수행하거나 여러 SQL 문을 한 단위로 묶어 실행할 때 사용한다.
  • stored function 은 'CREATE FUNCTION' 문을 사용하여 생성하며, 일반적으로 하나의 결과 값을 반환하는 계산이나 데이터 변환을 위한 로직을 수행할 때 사용한다.

 

반환 값

  • stored procedure 는 값을 반환하지 않거나, OUT 매개변수를 통해 하나 이상의 값을 반환할 수 있다.
  • stored function 은 반드시 하나의 값을 반환해야 하며, 이 값은 SQL 문 내에서 다른 값처럼 사용될 수 있다.

 

파라미터로 (값) 반환

  • stored procedure 는 값을 반환할 수도 있고, resultset(테이블)을 반환할 수도 있다.(커서를 사용하여)
  • stored function 은 단일 값을 반환하며, 이 값은 함수가 호출된 위치에서 바로 사용할 수 있다.

 

결과값 반환 필요 여부

  • stored procedure 는 필수는 아니다. 작업을 수행하고 그 결과를 반환하지 않을 수 있다.
  • stored function 은 필수이다. 함수는 호출될 때마다 반드시 결과 값을 반환해야 한다.

 

SQL문 내에서 호출

  • stored procedure 는 SQL문 내에서 호출될 수 없다. 일반적으로 독립적인 실행 단위로 사용된다.
  • stored function 은 SQL문 내에서 호출될 수 있다. SELECT 문의 일부로서, 또는 다른 SQL문 내에서 값처럼 사용될 수 있다.

 

트랜잭션 사용

  • stored procedure 는 트랜잭션 사용이 가능하다. 명시적으로 트랜잭션을 시작하고, 커밋하거나 롤백할 수 있다.
  • stored function 에서는 제한적이다. 일반적으로 함수 내에서 트랜잭션을 시작하거나 종료하는 것이 제한되어 있다.

 

주된 사용 목적

  • stored procedure 는 비즈니스 로직을 구현하는 데 주로 사용된다. 이는 데이터베이스 내의 데이터를 조작하거나 여러 테이블과 상화 작용하는 복잡한 작업을 의미할 수 있다. 
  • stored function 은 계산을 수행하는 데 주로 사용된다. 이는 특정 데이터를 가공하거나 계산식을 통해 값을 도출하는 것을 말한다.

 

추가로 고려해야 할 사항

: 다른 function/procedure을 호출할 수 있는지

→ procedure는 'call' 문을 사용하여 호출되는 반면, function은 'select' 문의 열, 조건문 또는 어떤 값이 필요한 곳이라면, 어디에서나 호출될 수 있다.

 

: result(=table) 을 반환할 수 있는지

→ procedure는 결과 집합을 반환할 수 있지만, function는 직접 결과 집합을 반환할 수 없다. 함수는 단일 값을 반환하는 것으로 제한된다.

 

: precompiled execution plan 을 만드는지

→ procedure는 미리 컴파일된 실행 계획을 가질 수 있어, 실행속도가 빨라진다. 하지만, DBMS에 따라 이 부분의 구현과 최적화는 다를 수 있다.

 

: try-catch 를 사용할 수 있는지

→ 일부 데이터베이스 시스템에서는 procedure 내에서 예외처리가 가능하지만, function 에서는 이를 지원하지 않는 경우가 많다.

→ MySQL 프로시저에서는 Declare…handler 구문을 사용하여 예외처리를 할 수 있다. 전통적인 try-catch 문법을 지원하지 않지만, 유사한 기능을 수행할 수 있다는 뜻이다.

참고자료

- 유튜브 쉬운코드

반응형
저작자표시

'컴퓨터 공부 > 📗 CS' 카테고리의 다른 글

[데이터베이스] 12장 - stored procedure를 백엔드 실무에서 쓰기에 조심스러운 이유!  (2) 2024.01.15
[데이터베이스] 10장 - SQL 에서 stored function 이란? + 언제 사용하면 좋을지에 대해  (2) 2024.01.12
[데이터베이스] 9장 - SQL 로 데이터 조회하기! Group by, Aggregation function, Order by  (0) 2024.01.11
[데이터베이스] 8장 - SQL 로 데이터 조회하기! JOIN  (4) 2024.01.09
[데이터베이스] 7장 - SQL 로 데이터 조회하기! NULL 의 의미와 three-valued logic  (0) 2024.01.09

'컴퓨터 공부/📗 CS'의 다른글

  • 현재글[데이터베이스] 11장 - stored procedure + 프로시저의 동작 방식과 특징, stored function과의 차이

관련글

댓글

티스토리툴바