[DBMS] 저장 프로시저 실습

# 스토어드 프로시저는 반환값이 없으므로 RETURN 명령어를 사용할 수 없다.
# 저장 프로시저 실행 시 CALL 명령어로 실행한다.

# 스토어드 프로시저 4가지 장점
# 1. DB의 성능을 향상시킬 수 있다.
# 2. 유지 관리가 간편하다.
# 3. 모듈식 프로그래밍이 가능하다.
# 4. 보안을 강화시킬 수 있다.

실습> 명령어의 끝 변경하기

명령어의 끝은 ; 으로 끝난다.
MariaDB [naver_db]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

명령어의 끝을 // 로 변경한다.
MariaDB [naver_db]> delimiter //
MariaDB [naver_db]> select user(); 
    -> //
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MariaDB [naver_db]> select user()//
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

명령어의 끝을 ; 으로 변경한다.
MariaDB [naver_db]> delimiter ;
MariaDB [naver_db]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

---

실습> 프로시저 생성

use naver_db

인자 값이 없는 경우

delimiter //  create procedure test1() begin      select * from member where age < 30;      select * from member where address like '서울%'; end //  delimiter ;

show procedure status;   <프로시저 확인>
show procedure status\G   <프로시저 간단하게 확인>
call test1();                      <test1 프로시저 실행>
drop procedure test1;      <test1 프로시저 삭제>

---

실습> show_user() 저장 프로시저 생성하기

-- 저장 프로시저 생성
delimiter // 
CREATE PROCEDURE show_user () 
BEGIN   
    select host, user, password from mysql.user;
    select host, user, db from mysql.db;   
END // 
delimiter ;

-- 저장 프로시저 확인
show procedure status\G

-- 저장 프로시저 실행
call show_user();

-- DB 사용자 생성
create database sbsuser;
create user sbsuser@localhost identified by '111111';
call show_user();
grant all privileges on sbsuser.* to sbsuser@localhost;

-- 저장 프로시저 실행
MariaDB [mysql]> call show_user();

+-----------+---------+-------------------------------------------+ | host      | user    | password                                  | +-----------+---------+-------------------------------------------+ | localhost | root    | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | | 127.0.0.1 | root    | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | | localhost | sbsuser | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |  <--  +-----------+---------+-------------------------------------------+ 3 rows in set (0.00 sec)

+-----------+---------+---------+ | host      | user    | db      | +-----------+---------+---------+ | localhost | sbsuser | sbsuser |  <-- +-----------+---------+---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)

---

실습> 아래 조건에 해당하는 저장 프로시저를 생성하시오.

!!! 프로시저 내부에서만 사용하는 지역변수는 @를 사용하면 안되고 declare를 사용해야 한다. !!!

-- 조건 --
DB 명 : naver_db
저장 프로시저명 : var_test
인수 : 없음
지역 변수 1 : name  가변길이 문자형 (32)
지역 변수 2 : age   정수형  기본값 1
 
실행 결과
NAME  age
홍길동  1
-- 조건 --

use naver_db

-- 저장 프로시저 생성
delimiter //
create procedure var_test()
begin
    declare name varchar(32);
    declare age  int default 1;
    set name = '홍길동';
    select name, age;
end //
delimiter ;

show procedure status\G  -- 저장 프로시저 확인
show create porocedure var_test\G -- 저장 프로시저 내용 확인
call var_test();  -- 저장 프로시저 실행

MariaDB [naver_db]> call var_test();
+-----------+------+
| name      | age  |
+-----------+------+
| 홍길동      |    1 |
+-----------+------+
1 row in set (0.00 sec)

---

실습> 저장 프로시저에서 변수명

set a = 1; -- 에러
set @a = 100;  -- 정상 실행
select @a; -- 변수 a의 값 출력

delimiter // create procedure print_a() begin     select @a; end // delimiter ;

MariaDB [naver_db]> call print_a();
+------+
| @a   |
+------+
|  100 |
+------+

변수의 값을 100 -> 10으로 변경한다.
set @a = 10;

MariaDB [naver_db]> call print_a();

+------+ | @a   | +------+ |   10 | +------+ 1 row in set (0.00 sec)

---

실습> 호출할 때 인자 값을 넣어줄 경우

-- use naver_db

delimiter // create procedure test(p_id varchar(32))  begin      select * from member where id=p_id;  end// delimiter ; call test('shlee'); call test('yjhwang');

# var_test 저장 프로시저 생성

delimiter // create procedure var_test() begin     declare name varchar(32);     declare age int default 1;     declare age2 int;     set name = '홍길동';     set age2 = age;     select name, age, age2;      end // delimiter ; call var_test();

# if 문 

delimiter // create procedure test3 (num int) begin if num > 0 then     select '0보다 큼' as result ; elseif num < 0 then     select '0보다 작음' as result; else     select '0과 같음' as result; end if; end // delimiter ; call test3(10);  # 0보다 큼 call test3(-5);  # 0보다 작음 call test3(0);

# case 조건문

delimiter // create procedure test4 (num int) begin case num when 1 then     select '월요일'; when 2 then     select '화요일'; when 3 then     select '수요일'; else     select '잘못입력'; end case; end // delimiter ; call test4(1);  # 월요일 call test4(2);  # 화요일 call test4(3);  # 수요일 call test4(8);  # 목요일

# while 반목문

delimiter // create procedure while_test(count int) begin     declare tmp int default 0;     while tmp < count      do        select tmp ;        set tmp = tmp + 1;     end while; end // delimiter ; call while_test(3);

# sum_func 만들기

delimiter // create function sum_func(num1 int, num2 int) returns int begin     declare tmp int;     if num1 > num2 then        set tmp = num1;        set num1 = num2;        set num2 = tmp;     end if;     set tmp = 0;     while num1 <= num2 do        set tmp = tmp + num1;        set num1 = num1 + 1;     end while;     return tmp; end // delimiter ;