Stored Procedure Overview

저장 프로시저는 CALL문에 의해 호출되는 루틴입니다. 입력 매개변수, 출력 매개변수 혹은 입력이면서 동시에 출력인 매개변수를 가질 수 있습니다

저장 프로시저 생성

다음은 저장 프로시저 작동을 확인하는 스켈레톤 예제입니다:

DELIMITER //

CREATE PROCEDURE Reset_animal_count() 
 MODIFIES SQL DATA
 UPDATE animal_count SET animals = 0;
//

DELIMITER ;

우선, 프로시저 정의 내에 기존의 구분기호인 세미콜론이 포함되기 때문에 구분기호(delimiter)를 변경합니다. 프로시저는 Reset_animal_count로 명명되었고, MODIFIES SQL DATA는 프로시저가 일종의 쓰기 동작을 수행하고 데이터를 변경할 것이라는 것을 알려줍니다.(MODIFIES SQL DATA는 단순 정보 제공용으로, 쿼리 동작에 실제로 영향을 미치지 않습니다.) 마지막으로 실제 SQL문인 UPDATE문이 위치합니다.

SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|     101 |
+---------+

CALL Reset_animal_count();

SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       0 |
+---------+

은행에서 실제로 사용되는 프로시저처럼, 입력 파라미터가 있는 복잡한 프로시저 예제입니다:

CREATE PROCEDURE
  Withdraw                             /* Routine name */
  (parameter_amount DECIMAL(6,2),     /* Parameter list */
  parameter_teller_id INTEGER,
  parameter_customer_id INTEGER)
  MODIFIES SQL DATA                   /* Data access clause */
  BEGIN                        /* Routine body */
    UPDATE Customers
        SET balance = balance - parameter_amount
        WHERE customer_id = parameter_customer_id;
    UPDATE Tellers
        SET cash_on_hand = cash_on_hand + parameter_amount
        WHERE teller_id = parameter_teller_id;
    INSERT INTO Transactions VALUES (
        parameter_customer_id,
        parameter_teller_id,
        parameter_amount);
  END;

전체 구문의 세부 사항은CREATE PROCEDURE를 참조하십시오.

저장 프로시저를 사용하는 이유는 무엇입니까?

가장 큰 이유는 보안입니다. 은행은 저장 프로시저를 사용함으로서 애플리케이션 또는 사용자가 테이블에 직접 접근하지 못하도록 합니다. 또한 저장 프로시저는 다양한 언어와 클라이언트가 동일한 작업을 수행하는데 사용되는 환경에서도 유용합니다.

저장 프로시저 목록 및 정의

서버에 어떤 저장 프로시저가 있는지 확인하려면 SHOW PROCEDURE STATUS을 사용하십시오.

SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
                  Db: test
                Name: Reset_animal_count
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2013-06-03 08:55:03
             Created: 2013-06-03 08:55:03
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

또는 INFORMATION_SCHEMA 데이터베이스 내의 routines 테이블에 직접 쿼리합니다:

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_TYPE='PROCEDURE';
+--------------------+
| ROUTINE_NAME       |
+--------------------+
| Reset_animal_count |
+--------------------+

저장 프로시저가 수행하는 작업을 확인하려면 SHOW CREATE PROCEDURE을 사용하십시오.

SHOW CREATE PROCEDURE Reset_animal_count\G
*************************** 1. row ***************************
           Procedure: Reset_animal_count
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Reset_animal_count`()
    MODIFIES SQL DATA
UPDATE animal_count SET animals = 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

저장 프로시저 제거 및 변경

저장 프로시저를 제거하려면 DROP PROCEDURE문을 사용하십시오

DROP PROCEDURE Reset_animal_count();

저장 프로시저의 특성을 변경하려면 ALTER PROCEDURE을 사용하십시오. 그러나 이 방법으로는 저장 프로시저의 매개변수나 본문을 변경할 수는 없습니다. 이러한 변경을 수행하려면 DROP PROCEDURE 와 CREATE PROCEDURE을 이용해 프로시저를 제거하고 다시 생성해야 합니다.

저장 프로시저 권한

Stored Routine Privileges을 참조하십시오.