11. DBMS에 저장해서 쓰는 함수 2

Stored Procedure

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

예제 1.

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

mysql> delimiter $$
mysql> CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
    -> BEGIN
    ->  SET result = a*b;
    -> END
    -> $$
Query OK, 0 rows affected (0.01 sec)

파라미터에 값을 입력하고 @result에 값을 저장해준다.

mysql> call product(5, 7, @result);
Query OK, 0 rows affected (0.00 sec)

mysql> select @result;
+---------+
| @result |
+---------+
|      35 |
+---------+
1 row in set (0.00 sec)

예제 2.

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

mysql> delimiter $$
mysql> CREATE PROCEDURE swap(INOUT a int, INOUT b int)
    -> BEGIN
    ->  set @temp = a;
    ->  set a = b;
    ->  set b = @temp;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

변수를 선언하고 프로시저를 실행하면 아래와 같이 값을 확인할 수 있다.

mysql> set @a=5, @b=7;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    5 |    7 |
+------+------+
1 row in set (0.00 sec)

mysql> call swap(@a,@b);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    7 |    5 |
+------+------+
1 row in set (0.00 sec)

예제 3.

각 부서별 평균 연봉을 가져오는 프로시저 작성해보자

mysql> CREATE PROCEDURE get_dept_avg_salary()
    -> BEGIN
    ->  select dept_id, avg(salary)
    ->  from employee
    ->  group by dept_id;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

부서별 평균 연봉을 구하기위해 group by 사용하여 묶어주고 평균 연봉을 구한 후 select를 사용하여 return없이 결과를 출력해 줄 수 있다.

mysql> call get_dept_avg_salary();
+---------+----------------+
| dept_id | avg(salary)    |
+---------+----------------+
|    NULL | 140333333.3333 |
|    1003 | 110000000.0000 |
|    1004 | 170000000.0000 |
|    1005 |  88333333.3333 |
+---------+----------------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

예제 4.

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

mysql> 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
    ->  ); //nickname_logs테이블에 user_id가 같은 id, nickname, 현재 시간을 저장한다.
    ->  update users set nickname = new_nick where id = user_id;//user_id와 동일한id의 nickname를 변경
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

stored procedure

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

stored procedure 와 stored function의 차이점