MySQL 요약 노트-6

soyeong·2022년 10월 12일
mysql스토어드 프로시저
0

MySQL

목록 보기
6/7
post-thumbnail

MySQL 요약 노트 (2022년 1월 ~ 2022년 3월까지 공부했던 내용 정리)
혼자 공부하는 SQL책과 한빛미디어에서 제공하는 유튜브 강의로 공부함

📌 7강 스토어드 프로시저

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

  • 스토어드 프로시저: MySQL에서 제공하는 프로그래밍 기능, 쿼리 문의 집합
    • SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있음
  • 스토어드 프로시저 형식
    • DELIMITER: 구분자
    • 스토어드 프로시저의 이름에 _proc(procedure의 의미)를 붙임
    • IN 또는 OUT 매개변수: 입력 또는 출력 매개변수
    • CREATE PROCEDURE: 스토어드 프로시저를 만드는 것으로 실행(호출)한 것은 아님
    DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN
  이 부분에 SQL 프로그래밍을 코드를 작성
END $$
DELIMITER ;
  • 스토어드 프로시저 실행(호출) 형식
    CALL 스토어드_프로시저_이름();
  • 스토어드 프로시저의 생성
    USE 데이터베이스_이름; -- 데이터베이스 사용
DROP PROCEDURE IF EXISTS 스토어드_프로시저_이름; -- 해당 이름의 스토어드 프로시저가 있다면 삭제
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN
  이 부분에 SQL 프로그래밍을 코드를 작성
END $$
DELIMITER ; -- 스토어드 프로시저 만들기
CALL 스토어드_프로시저_이름(); -- 스토어드 프로시저 호출
  • 스토어드 프로시저의 삭제
    DROP PROCEDURE 스토어드_프로시저_이름;
  • 매개변수의 사용
    • 입력 매개변수 지정 형식
      IN 입력_매개변수_이름 데이터_형식
    • 입력 매개변수가 있는 스토어드 프로시저 실행
      CALL 프로시저_이름(전달_값);
    • 출력 매개변수 지정 형식
      OUT 출력_매개변수_이름 데이터_형식
    • 출력 매개변수가 있는 스토어드 프로시저 실행
      CALL 프로시저_이름(@변수명);
SELECT @변수명;
  • 날짜와 관련된 MySQL 함수
    • YEAR(날짜) : 연
    • MONTH(날짜) : 월
    • DAY(날짜) : 일
    • CURDATE() 함수 : 현재 날짜
    SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
  • 동적 SQL: 다이나믹하게 SQL을 생성한 후 실행함, PREPARE 문과 EXECUTE 문을 사용함
    PREPARE
EXECUTE
DEALLOCATE PREPARE

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

  • 스토어드 함수: MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공

  • 스토어드 함수의 사용

    • SQL로 스토어드 함수 생성 권한을 허용해주는 구문
    SET GLOBAL log_bin_trust_function_creators = 1;

  • 스토어드 함수의 형식

    • RETURNS 문: 반환할 값의 데이터 형식을 지정
    • RETURN 문: 하나의 값을 반환
    • 스토어드 함수의 매개변수는 모두 입력 매개변수이기 때문에 IN을 붙이지 않음
    • 스토어드 함수 호출은 SELECT 문
    • 스토어드 함수안에서 SELECT를 사용할 수 없음
    DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
  RETURNS 반환형식
BEGIN

  이 부분에 프로그래밍 코딩
  RETURN 반환값;

END $$
DELIMITER ;
SELECT 스토어드_함수_이름();

  • 스토어드 함수의 내용 확인

    SHOW CREATE FUNCTION 함수_이름;

  • 스토어드 함수의 삭제

    DROP FUNCTION 스토어드_함수_이름;
  • 커서: 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리
    • 커서 작동 순서
      1) 커서 선언하기
      2) 반복 조건 선언하기
      3) 커서 열기
      4) 데이터 가져오기 -> 데이터 처리하기: 이 부분을 반복
      5) 커서 닫기
  • 커서의 단계별 실습
    • 1) 사용할 변수 준비하기
      • DEFAULT 문: 초기값을 설정하기
      DECLARE 변수명 데이터형식 (DEFAULT 숫자);
      • endOfRow: 행의 끝을 파악하기
      DECLARE endOfRow BOOLEAN DEFAULT FALSE;
    • 2) 커서 선언하기
      • 커서라는 것은 결국 SELECT 문임
      DECLARE 커서_이름 CURSOR FOR
    SELECT 열_이름 FROM 테이블_이름;
    • 3) 반복 조건 선언하기
      • DECLARE CONTINUE HANDLER: 반복 조건을 준비하는 예약어
      • FOR NOT FOUND: 더 이상 행이 없을 때 이어진 문장을 수행함 (행이 끝나면 endOfRow에 TRUE를 대입함)
      DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;
    • 4) 커서 열기
      OPEN 커서_이름;
    • 5) 행 반복하기
      • cursor_loop: 반복할 부분의 이름을 지정한 것
      cursor_loop: LOOP
  이 부분을 반복
END LOOP cursor_loop;
      • LEAVE: 반복할 이름을 빠져나감
      • FETCH: 한 행씩 읽어오는 것
      cursor_loop: LOOP
  FETCH 커서_이름 INTO 변수; -- 한 행씩 읽어오기
  
  IF endOfRow THEN
    LEAVE cursor_loop;
  END IF; -- 행의 끝에 다다르면 반복하는 부분을 빠져나감
  
  SET 변수; -- 변수의 누적
END LOOP cursor_loop;
    • 6) 커서 닫기
      CLOSE 커서_이름;
  • 커서는 스토어드 프로시저 안에 코드를 작성함
    • 스토어드 프로시저를 실행하면 커서가 작동됨

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

  • 트리거: 테이블에 INSERT, UPDATE, DELETE 작업이 발생하면 실행되는 코드

    • 사전적 의미: '방아쇠'
    • 트리거는 테이블에 무슨 일이 일어나면 자동으로 실행됨
    • 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지함
    • 데이터의 무결성: 데이터에 오류가 발생하는 것을 막는 것

  • 트리거의 기본 작동

    • 트리거는 테이블에서 DML 문 (INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동함

    • 테이블에 미리 부착되는 프로그램 코드: AFTER 트리거, BEFORE 트리거

    • 트리거는 IN, OUT 매개변수를 사용할 수 없음

    • 1) 테이블 만들기

      USE 데이터베이스_이름;
CREATE TABLE IF NOT EXISTS 테이블_이름(열_이름1 데이터형식, 열_이름2 데이터형식, ...)
INSERT INTO 테이블_이름 VALUES(데이터, 데이터, ...);

    • 2) 테이블에 트리거를 부착하기

      DROP TRIGGER IF EXISTS 트리거_이름;
DELIMITER $$
CREATE TRIGGER 트리거_이름
  AFTER DELETE -- 삭제(DELETE) 후에 작동하도록 지정
  ON 테이블_이름 -- 트리거를 부착할 테이블
  FOR EACH ROW
BEGIN
  트리거 실행 시 작동되는 코드들
END $$
DELIMITER ;

    • 3) INSERT 문, UPDATE 문, DELETE 문

      SET @msg = '';
INSERT INTO 테이블_이름 VALUES(데이터, 데이터, ...); -- 삽입
SELECT @msg;
UPDATE 테이블_이름 SET 열_이름=데이터 WHERE 조건; -- 수정
SELECT @msg;
DELETE FROM 테이블_이름 WHERE 조건; -- 삭제(DELETE)에 트리거 작동
SELECT @msg;

  • 트리거 활용: 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용

    • 테이블에 이벤트가 먼저 적용된 후에 트리거가 실행됨
    • 백업 테이블: 테이블에 INSERT, UPDATE, DELETE 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 테이블
    • AFTER INSERT: INSERT 트리거는 테이블에서 INSERT 문의 이벤트가 발생될 때만 작동
    • AFTER UPDATE: UPDATE 트리거는 테이블에서 UPDATE 문의 이벤트가 발생될 때만 작동
    • AFTER DELETE: DELETE 트리거는 테이블에서 DELETE 문의 이벤트가 발생될 때만 작동

  • 테이블의 모든 행 데이터 삭제: DELETE 와 TRUNCATE TABLE 문

    • DELETE 트리거는 오직 DELETE문에만 작동하기 때문에 TRUNCATE TABLE로 삭제 시에는 트리거가 작동하지 않음
    DELETE FROM 테이블_이름;
TRUNCATE TABLE 테이블_이름;

  • 트리거가 사용하는 임시 테이블

    • MySQL이 알아서 생성 및 관리
    • 테이블에 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블: NEW 테이블과 OLD 테이블
    • 트리거에서 기존 데이터는 OLD 테이블에, 새로운 데이터는 NEW 테이블에 잠깐 저장됨
      • NEW 테이블: INSERT(새 값) 형태로 실행되면 작동
      • OLD 테이블: DELETE(예전 값) 형태로 실행되면 작동
      • NEW 테이블과 OLD 테이블 모두 사용: UPDATE(새 값, 예전 값) 형태로 실행되면 작동

📌 참고문헌

우재남. (2021). 혼자 공부하는 SQL. 한빛미디어. p330-p368.

profile
soyeong
The ultimate goal is to be a Data Scientist.
이전 포스트

MySQL 요약 노트-5

다음 포스트

MySQL 요약 노트-7

0개의 댓글