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을 참조하십시오.