GIF 출처 : https://logpresso.store/ko/apps/mysql

🖥 Contents

---

1 ) 스토어드 프로시저 ( Stored Procedure )

2 ) SQL 프로그래밍

3 ) 동적 SQL

4 ) 스토어드 함수 ( Stored Function )

---

1 ) 스토어드 프로시저 ( Stored Procedure )

---

• 스토어드 프로시저 ( Stored Procedure ) 란?

  : SQL로 프로그래밍하여 SQL에 저장하고 , 해당 내용을 재사용할 수 있도록 만드는 것으로 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리 집합이다. 데이터베이스에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한다고 해서 영구 저장 모듈 ( Persistent Storage Module ) 이라고도 한다.

  
  

  • 스토어드 프로시저의 장점

  ① 절차적 기능 구현

  : SQL 쿼리는 절차적 기능을 제공하지 않으나 , 스토어드 프로시저 내에서는 IF,WHILE과 같은 제어 문장을 사용할 수 있기에 절차적 프로그래밍이 가능하다.

  
  

  ② 유지보수

  : 스토어드 프로시저를 호출하는 곳에서는 스토어드 프로시저의 이름으로만 호출하므로 , 스토어드 프로시저를 수정할 경우 호출한 곳에서는 별도의 수정 작업이 필요 없어 유지보수에 용이하다.

  
  

  ③ 트래픽 감소

  : 한 번의 요청으로 여러 SQL 문을 실행할 수 있으며 SQL을 직접 작성하지 않고 매개변수만 전달하면 되기 때문에 서버와 클라이언트간의 네트워크 트래픽이 감소한다.

  
  

  • 스토어드 프로시저 단점

  ① 처리 성능

  : MySQL은 스토어드 프로시저를 실행할 때마다 스토어드 프로시저의 코드를 분석 ( 파싱 ) 하기 때문에 속도가 감소한다.

  ② 스토어드 프로시저 문법차이

  : 데이터베이스마다 구문 및 규칙이 다르기 때문에 다른 데이터베이스와의 호환성이 낮아질 수 있다.

  
  
  

• 스토어드 프로시저 생성

  
  

  < 스토어드 프로시저 기본형식 >

DELIMITER &&
CREATE PROCEDURE [ 프로시저 명 ]( IN | OUT | INOUT , 매개변수 ])
BEGIN
   변수 선언 , 함수 실행 코드 ... 
END &&
DELIMITER;

ex )

DELIMITER $$
CREATE PROCEDURE woojuice_procedure_ex()
BEGIN
    DECLARE customer_cnt INT;
    DECLARE add_number INT;

    SET customer_cnt = 0;
    SET add_number = 100;

    SET customer_cnt = (SELECT COUNT(*) FROM customer);

    SELECT customer_cnt + add_number;

END $$
DELIMITER ;

• 스토어드 프로시저 내용 확인하기

: 앞서 생성한 스토어드 프로시저의 내용을 확인하기 위해서는 다음 쿼리를 실행하면 된다.

SHOW CREATE PROCEDURE woojuice_procedure_ex;

• 스토어드 프로시저 삭제하기

: DROP PROCDEURE 명령문을 통해 스토어드 프로시저를 삭제할 수 있다.

DROP PROCEDURE woojuice_procedure_ex;

---

2) SQL 프로그래밍

---

앞서 언급한바와 같이 스토어드 프로시저는 IF,WHILE문과 같은 제어문을 사용하여 다른 프로그래밍 언어처럼 조건에 따른 명령 실행이 가능하다.

• IF문

< IF 문 기본형식 >

IF [ 조건식 ] THEN [ 실행 식 ] 
ELSE [ 실행 식 ] 
END IF;

ex )

SELECT store_id , IF(store_id = 1 , '일' , '이') AS one_two
FROM customer GROUP BY customer_id;

• CASE 문

< CASE문 기본형식 >

CASE
   WHEN [ 조건 1 ] THEN [ 명령문 1 ]
   WHEN [ 조건 2 ] THEN [ 명령문 2 ] 
   .
   .
   .
   .
   WHEN [ 조건 n ] THEN [ 명령문 n ] 
   ELSE [ 명령문 n+1 ] 
END

ex )

SELECT customer_id , SUM(amount) AS amount ,
       CASE
           WHEN SUM(amount) >= 150 THEN 'VVIP'
           WHEN SUM(amount) >= 120 THEN 'VIP'
           WHEN SUM(amount) >= 80 THEN 'SILVER'
           ELSE 'BRONZE'
       END AS customer_level
FROM payment GROUP BY customer_id;

• WHILE 문

< WHILE 문 기본형식 >

WHILE [ 조건식 ] DO [ 명령문 ] 
END WHILE;

ex )

DROP PROCEDURE IF EXISTS woojuice_procedure_while_ex;

DELIMITER $$
CREATE PROCEDURE woojuice_procedure_while_ex(param_1 INT ,param_2 INT)
BEGIN
    DECLARE i INT;
    DECLARE while_sum INT;

    SET i = 1;
    SET while_sum = 0;

    WHILE( i <= param_1 ) DO
        SET while_sum = while_sum + param_2;
        SET i = i + 1;
        END WHILE;
    SELECT while_sum;
END $$
DELIMITER ;

---

3 ) 동적 SQL

---

• 동적 SQL 란?

  : 동적 SQL은 변숫값을 할당 받아 MySQL 서버 내부 또는 스토어드 프로시저에서 쿼리를 재작성하는 것을 의미한다. 동적 SQL은 실행시점에 SQL문을 생성하고 실행한다.

  
  

  < 기본 형식 >

PREPARE [ 동적 쿼리명 ] FROM ' 쿼리작성 '
EXCUTE [ 동적 쿼리명 ]
DEALLOCATE PREPARE [ 동적 쿼리명 ] 

ex )

PREPARE dynamic_query FROM ' SELECT * FROM customer WHERE customer_id = ? ';
SET @a = 1;
EXCUTE dynamic_query USING @a;
DEALLOCATE PREPARE dynamic_query;


DROP PROCEDURE IF EXISTS woojuice_ex_dynamic;

DELIMITER $$
CREATE PROCEDURE woojuice_ex_dynamic(t_name VARCHAR(50),c_name VARCHAR(50),customer_id INT)
BEGIN
    SET @t_name = t_name;
    SET @c_name = c_name;
    SET @customer_id = customer_id;
    SET @sql = CONCAT('SELECT ', @c_name, ' FROM ', @t_name, ' WHERE customer_id = ', @customer_id);
    SELECT @sql;
    PREPARE dynamic_query FROM @sql;
    EXECUTE dynamic_query;
    DEALLOCATE PREPARE dynamic_query;
END $$
DELIMITER ;

---

4 ) 스토어드 함수 ( Stored Function )

---

• 수정중

---