[혼공S] 6주차_스토어드 프로시저

Ch 7. 스토어드 프로시저

📌 이번 챕터에서는 스토어드 프로시저와 스토어드 함수, 트리거에 대해서 공부하였다.

 

07-1 스토어드 프로시저 사용 방법

• 스토어드 프로시저: SQL에 프로그래밍을 추가한 것으로, 동작 일괄 처리용으로도 사용 가능함
  • 필수 형식

DELIMITER $$ -- 프로시저 묶기(Ⅰ) -- 구분자를 ; 대신 임시로 $$로 설정
CREATE PROCEDURE 스토어드_프로시저_이름( IN 또는 OUT 매개변수 ) -- 프로시저 이름 정해줌
BEGIN

	
			이 부분에 SQL 프로그래밍을 코드로 작성
			
			
END $$ -- 프로시저 묶기(Ⅱ)
DELIMITER ; -- 구분자를 다시 ;로 되돌리기

CALL 스토어드_프로시저_이름(); -- 스토어드 프로시저 호출
DROP PROCEDURE 스토어드_프로시저_이름; -- 스토어드 프로시저 삭제

• 입력 매개변수: 스토어드 프로시저에 값 전달

IN 입력_매개변수_이름 데이터_형식 -- 입력 매개변수 지정

CALL 프로시저_이름(전달값); -- 입력 매개변수 있는 스토어드 프로시저 실행

• 출력 매개변수: 스토어드 프로시저에서 계산된 결과를 돌려받음

OUT 출력_매개변수_이름 데이터_형식 -- 출력 매개변수 형식

CALL 프로시저_이름(@변수명); -- 출력 매개변수 있는 스토어드 프로시저 실행
SELECT @변수명;

• 동적 SQL: PREPARE 문과 EXECUTE 문을 사용하여 SQL 생성 후 실행

07-2 스토어드 함수와 커서

• 스토어드 함수: 사용자가 직접 만들어서 사용하는 함수로, RETURNS 예약어로 반환될 데이터의 형식을 미리 지정해야 함
  • 스토어드 함수를 사용하기 위해서는 스토어드 함수 생성 권한을 허용해야함 : SET GLOBAL log_bin_trust_function_creators = 1;
  • 함수의 반환 값을 SELECT~INTO~로 저장했다가 사용 가능
  • 삭제: DROP FUNCTION 문

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
	RETURNS 반환형식
BEGIN

	이 부분에 프로그래밍 코딩
	RETURN 반환값;
	
END $$
DELIMITER ;
SELECT 스토어드_함수_이름();

• 커서: 테이블에서 SQL 문이나 코드를 한 행씩 처리할 수 있으며 대부분 스토어드 프로시저와 함께 사용. DECLARE로 선언할 수 있고 내용은 SELECT 문으로 지정. 반복문을 끝내기 위해 endOfRow을 준비해서 TRUE면 끝내기
  • 작동 순서: 커서 선언 > 반복 조건 선언 > 커서 열기 > 데이터 가져오기 & 처리하기(반복) > 커서 닫기

07-3 자동 실행되는 트리거

• 트리거: DML(INSERT, UPDATE, DELECT 문)문이 작동될 때 자동으로 실행되는 프로그래밍 기능으로, 사용자가 추가 작업을 잊어버리는 걸 방지
  • 트리거는 행 데이터가 삭제 or 수정되면 기존 데이터를 백업 테이블에 저장하도록 할 수 있음
  • 트리거에서 기존 데이터는 OLD 테이블에, 새로운 데이터는 NEW 테이블에 잠깐 저장됨, 두 테이블은 MySQL이 내부적으로 관리

DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$ 
CREATE TRIGGER myTrigger  -- 트리거 이름
    AFTER  DELETE -- 삭제후에 작동하도록 지정
    ON trigger_table -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용시킴(트리거 필수 문)
BEGIN
    SET @msg = '가수 그룹이 삭제됨' ; -- 트리거 실행시 작동되는 코드들
END $$ 
DELIMITER ;

기본 숙제

p.363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증하기

6주차 기본 숙제 인증 1

6주차 기본숙제 인증 2

작성한 트리거 외에도 본문에 나와있는 데이터 변경에 대한 트리거도 함께 작성한 뒤에 모두 실행하고 백업테이블에 데이터가 잘 있는지 확인하였습니다:)

 

이렇게 6주차를 끝으로 혼공학습단 13기 활동을 모두 완료하였습니다.

 

마무리 회고록에서 뵙겠습니다