DB 스토어드 프로시저

스터어드 프로시저

스토어드 프로시저는 데이터베이스 시스템에서 실행할 수 있는 하나 이상의 SQL 명령문의 묶음이다.

MYSQL에서 제공된느 프로그래밍 기능이라고 생각하면 된다. 클리이언트 애플리케이션에서 호출하여 실행할 수 있으며 자주 사용되는 일반적인 쿼리를 사용하는 것보다 이것을 모듈화 시켜서 필요할 때마다 호출 하면 편리하게 사용할 수 있다.

 

스토어드 프로시저의 장점

• 여러 SQL문이 묶여 있기 때문에 트래픽을 줄이고 실행 속도를 높일 수 있다.
• 재사용성이 올라간다.
• 중복된 코드를 줄이고 유지보수가 쉬워진다.
• 보안을 강화할 수 있다.

스토어드 프로시저의 단점

• 디버깅이 어려워 진다.
• 데이터베이스 마다 다른 프로기르맹 언어와 문법을 사용하므로, 이식성이 떨어질 수 있다.

스토어드 프로시저의 정의 형식

CREATE 
	[DEFINER =user]
    PROCEDURE sp_name ([proc_parameter[...]])
    [characteristic ...] routine_body
    
proc_parameter:
    [IN | OUT |INOUT ] param_name type

type:
	Any valid MySQL data type

characteristic:
	COMENT 'string'
    |LANGUAGE SQL
    |[NOT] DETERMINISTIC
    | {CONTAINS SQL | NO SQL | READS SQL DATA |MODIFIES SQL DATA }
    | SQL SECURITY {DEFINER | INVOKER }

routine_body
	Valid SQL routine statement

CREATE PROCEDURE userPROC()
BEGIN
	SELECT * FROM UserTBL;
END $$

CALL userProc()

스토어드 프로시저의 수정과 삭제

프로시저의 수정을 ALTER PROCEDURE를 사용하면 되며, 삭제는 DROP PROCEDURE 를 사용하면 된다.

DROP PROCEDURE IF EXISTS procedure_name;

스토어드 함수

스토어드 함수란 스토어드 프로시저와 같이 SQL을 묶어서 하나의 함수로 만든것이지만 약간의 차이가 있다.

스토어드 프로시저는 값을 반환하지 않을 수도 있고 여러 값을 반환할 수 있고 스토어드 함수는 반드시 하나의 값을 반환해야 한다. 

스터어드 프로시저의 파라미터와 달리 IN,OUT 등을 사용할 수 없다. 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다.

스토어드 프로시저는 CALL로 호출하지만 스토어드 함수는 SELECT 문장 안에서 호출된다.

 

커서

커서는 주로 스토어드 프로시저나 스토어드 함수 내에서 사용된다. 

테이블에서 여러 개의 행을 쿼리한 후에 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식이다

커서의 주요 작업 
DECLARE: 커서를 선언합니다.
OPEN: 커서를 엽니다. 이 작업으로 SQL 쿼리가 실행되고 결과가 저장됩니다.
FETCH: 커서에서 다음 행을 가져옵니다.
CLOSE: 커서를 닫습니다. 이로써 자원을 해제합니다.

트리거

트리거란 테이블의 삽입,수정,삭제등의 이벤트가 발생 시에 자동으로 작동되는 개체로 트리거는 데이터 무결성을 유지하거나, 추가적인 작업을 자동으로 수행하는 등 다양한 목적으로 사용될 수 있다.

트리거의 사용

CREATE 
	[DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body
    
trigger_time: {BEFORE | AGTER }

trigger_event: {INSERT | UPDATE | DELETE }

trigger_order: {FOLLOWS | PRECEDES } other_trigger_name

 

트리거의 종류

AFTER 트리거 

테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거를 말하며 해당 작업 후에 작동한다.

 

BEFORE 트리거

BEFORE 트리거는 이벤트가 발생하기 전에 작동하는 트리거이다.

 

트리거의 주요 구성 요소

Triggering Event: 트리거가 실행되는 이벤트 (예: INSERT, UPDATE, DELETE)
Triggering Time: 이벤트가 발생했을 때 트리거가 언제 실행될 것인지 지정 (BEFORE, AFTER)
Triggered Action: 이벤트 발생 시 실행할 SQL 문장