참고
스토어드 프로그램
NOTE
스토어드 프로그램 종류는 4가지이다.
•
스토어드 프로시저
•
스토어드 함수
•
커서
•
트리거
스토어드 프로시저
NOTE
일련의 쿼리를 모아 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.
함수 생성 → 함수 호출 이라고 생각하면 된다.
# 스토어드 프로시저 생성
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN
SQL 프로그래밍 코드가 들어가는 부분
END $$
DELIMITER ;
# 스토어드 프로시저 호출
CALL 스토어드_프로시저_이름();
SQL
복사
•
쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로 사용된다.
•
자주 사용하는 일반적인 쿼리를 반복하는 것보다 스토어드 프로시저로 묶어 놓고, 필요할 떄마다 간단히 호출만 하면 훨씬 편리하게 사용이 가능하다.
스토어드 함수
NOTE
필요에 따라 직접 만들어서 사용하는 함수를 스토어드 함수라한다!
프로시저 와 비슷하게 보이지만 사용처가 엄연히 다르다.
# 스토어드 함수 형식
DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
RETURNS 반환형식
BEGIN
이 부분에 프로그래밍 코딩
RETURN 반환값;
END $$
DELIMITER ;
# 스토어드 함수 호출
SELECT 스토어드_함수_이름();
SQL
복사
스토어드 함수 생성과 호출
SET GLOBAL log_bin_trust_function_creators = 1;
SQL
복사
스토어드 함수를 사용하기 위한 권한설정
•
스토어드 함수는 RETURNS문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서 RETURN 문으로 하나의 값을 반환해야 한다.
•
스토어드 함수의 매개변수는 모두 입력 매개변수이며 IN을 붙이지 않는다.
•
스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELETCT문 안에서 호출된다.
커서(CURSOR, No offset)
NOTE
테이블에서 한 행씩 처리하기 위한 방식이다!
한 행씩 처리한다.
•
프로시저 내부에서 커서를 사용할 수 있다.
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
# 변수 선언
DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
DECLARE memberCursor CURSOR FOR
SELECT mem_number FROM member;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;
# 커서 오픈
OPEN memberCursor;
cursor_loop: LOOP
FETCH memberCursor INTO memNumber;
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
SET cnt = cnt + 1;
SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;
SELECT (totNumber / cnt) AS '회원의 평균 인원 수';
# 커서 클로즈
CLOSE memberCursor;
END $$
DELIMITER ;
CALL cursor_proc();
SQL
복사
실제 커서 사용코드
트리거
NOTE
자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다!
# 테이블 생성
CREATE TABLE IF NOT EXISTS trigger_table (id INT, txt VARCHAR(10));
INSERT INTO trigger_table VALUES(1, '레드벨벳');
INSERT INTO trigger_table VALUES(2, '잇지');
INSERT INTO trigger_table VALUES(3, '블랙핑크');
# 트리거 생성
DELIMITER $$
CREATE TRIGGER myTrigger -- 트리거 이름
AFTER DELETE -- DELETE 후에 작동하도록 지정
ON trigger_table -- 트리거를 부착할 테이블 지정
FOR EACH ROW -- 각 행마다 적용
BEGIN
SET @msg = '가수 그룹이 삭제됨'; -- 트리거 실행 시 작동되는 코드
END $$
DELIMITER ;
DELETE FROM trigger_table WHERE id = 3;
SELECT @msg;
SQL
복사
트리거 사용의 예
결과화면