혼자 공부하는 SQL / Chapter 07 스토어드 프로시저

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

• 스토어드 프로시저 (stored procedure)
  • SQL에 프로그래밍 기능을 추가한 것 (조건문, 반복문…)
  • 쿼리 문의 집합으로도 볼 수 있다.
  • 어떠한 동작을 일괄 처리하기 위한 용도로 사용
• 스토어드 프로시저 기본 형식

• DELIMITER $$
  CREATE PROCEDURE 스토어드프로시저(IN 또는 OUT 매개변수)
  BEGIN
  	// SQL 프로그래밍 코드 작성
  END $$
  DELIMITER;​

   
  • 매개변수
    • 입력 매개변수
      • IN 입력매개변수이름 데이터형식
      • CALL 프로시저이름(전달값);
      • ex)

      DELIMITER $$
      CREATE PROCEDURE user_proc(IN userName VARCHAR(10))  // 유저네임값을 입력 매개변수로 전달 
      BEGIN
      	SELECT * FROM member WHERE mem_name = userName;  // userName 매개변수에 대입
      END $$
      DELIMITER;
      
      CALL user_proc('유저네임값');  // 해당 유저네임값에 대한 조회 수행 
      

    • 출력 매개변수
      • OUT 출력매개변수이름 데이터형식
      • CALL 프로시저이름(@변수명);
      • SELECT @변수명;
• 스토어드 프로시저 호출
  • CALL 스토어드프로시저이름();
• 스토어드 프로시저 삭제
  • DROP PROCEDURE 스토어드프로시저이름;
• 스토어드 프로시저 조건문
  • SQL 프로그래밍 작성 부분에 작성

  • IF (조건식) THEN
    	SELECT '참일 때 출력할 메시지' AS '메시지';
    ELSE
    	SELECT '거짓일 때 출력할 메시지' AS '메시지';
    END IF;

• 스토어드 프로시저 반복문
  • SQL 프로그래밍 작성 부분에 작성

  • WHILE (몇번까지반복할지조건) DO
    	SET hap = hap + num;
    	SET num = num + 1;
    END WHILE;

 

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

• 스토어드 함수
  • 직접 함수를 작성하는 기능
  • RETURNS 예약어를 통해서 하나의 값을 반환해야 한다.
  • 주로 어떤 계산을 통해서 하나의 값을 반한할 때 사용
• 스토어드 함수 형식

  • DELIMITER $$
    CREATE FUNCTION 스토어드함수(매개변수)
    	RETURNS 반환데이터형식
    BEGIN
    	// SQL 프로그래밍 코딩
    	RETURN 반환값;
    END $$
    DELIMITER;
    
    SELECT 스토어드함수();

  • RETURNS 로 반환할 값의 데이터 형식 지정
  • RETURN 으로 하나의 값 반환 (+나 -같이 계산 형식도 된다)
  • 매개변수는 모두 입력 매개변수이고 IN은 붙이지 않는다.
• 스토어드 함수 호출
  • SELECT 스토어드함수();
• 스토어드 함수 사용 권한
  • SET GLOBAL log_bin_trust_function_creators = 1;
  • MySQL에서 한번만 설정하면 된다.
• 커서
  • 스토어드 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식
  • 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.
  • 대부분 스토어드 프로시저와 함께 사용된다.
• 커서의 단계
  • 사용할 변수 준비하기
    • DECLARE 변수 데이터형식 DEFAULT 0;
    • DECLARE endOfRow BOOLEAN DEFAULT FALSE;
  • 커서 선언하기
    • DECLARE 커서 CURSOR FOR SELECT 변수 FROM 테이블;
  • 반복 조건 선언하기
    • DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE;
  • 커서 열기
    • OPEN 커서;
  • 행 반복하기
    • cursor_loop: LOOP 반복 부분 END LOOP cursor_loop
    • IF endOfRow THEN LEAVE cursor_loop;
    • END IF;
  • 커서 닫기
    • CLOSE 커서;

 

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

• 트리거 (trigger)
  • 테이블에서 INSERT, UPDATE, DELETE 실행 시 트리거 자동으로 작동 (코드 자동 실행!)
  • 작업을 자동으로 수행하여 데이터의 무결성과 사용자의 실수 방지
• 트리거 작성

• DELIMITER $$
  CREATE TRIGGER 트리거
  	AFTER DELETE
  	ON 테이블
  	FOR EACH ROW
  BEGIN
  	// 트리거 실행 시 작동되는 코드
  END $$
  DELIMITER;

---

미션

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