[Database] (쉬운 코드) Stored procedure 개념

Stored procedure

---

Stored procedure는 사용자가 정의한 프로시저로 RDBMS에 저장되고 사용되며 구체적인 하나의 태스크(task)를 수행한다.

ex. 두 정수의 곱셈 결과를 가져오는 프로시저

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

--- 사용법 ---
call product(5, 7 @result);
select @result;

ex. 두 정수를 맞바꾸는 프로시저를 작성하자.

delimiter $$
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
    BEGIN
        set @temp = a;
        set a = b;
        set b = @temp;
    END
    $$
delimiter;

--- 사용법 ---
set @a = 5, @b = 7;
call swap(@a, @b);
select @a, @b;

• IN : 입력 매개변수를 의미하며 프로시저 내에서 값을 변경할 수 없다.
• OUT : 출력 매개변수를 의미하며 프로시저 내에서 값을 변경할 수 있다.
• INOUT : 입력과 출력 매개변수를 의미하며 프로시저 내에서 값을 변경할 수 있다.

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

delimiter $$
CREATE PROCEDURE get_dept_avg_salary()
    BEGIN
        select dept_id, avg(salary)
        from employee
        group by dept_id;
    END
    $$
delimiter ;

--- 사용법 ---
call get_dept_avg_salary();

ex. 사용자가 프로필 닉네임을 바꾸면 이전 닉네임을 로그에 저장하고 새 닉네임으로 업데이트하는 프로시저를 작성하자.

--- user의 현 닉네임 테이블 ---
select * from users;

--- user의 전 닉네임 테이블 ---
select * from nickname_logs;

delimiter $$
CREATE PROCEDURE change_nickname(user_id INT, new_nick varchar(30))
    BEGIN
        insert into nickname_logs(
            select id, nickname, now() from users where id = user_id;
        );
        update users set nickname = new_nick where id = user_id;
    END
    $$
delimiter;

--- 사용법 ---
call change_nickname(1, 'ZIDANE');

이 외에도 수행할 수 있는 것들이 있다.

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

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

transaction

여러개의 SQL 문을 하나의 작업으로 묶어서 처리하는 것을 말한다.

만약에 하나의 SQL 문이라도 실행되지 않으면 아무런 작업도 하지 않고 롤백(rollback)하여 이전 상태로 되돌릴 수 있어 동작 방식을 ALL or NOTHING이라고 한다.

추후에 알아볼 것….

• 다른 function/procedure를 호출할 수 있는지
• resultset(=table)을 반환할 수 있는지
• precompiled execution plan을 만드는지
• try-catch를 사용할 수 있는지