쌩로그

혼공 SQL - ch07(스토어드 프로시저) 본문

DB/RDB

혼공 SQL - ch07(스토어드 프로시저)

.쌩수. 2023. 8. 9. 18:03
반응형

목록

  1. 포스팅 개요
  2. 본론
        2-1. 스토어드 프로시저 사용 방법
        2-2. 스토어드 함수와 커서
        2-3. 자동 실행되는 트리거
  3. 요약

1. 포스팅 개요

혼자 공부하는 SQL 챕터 7을 요약 및 정리한 포스팅

2. 본론

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

스토어드 프로시저(stored procedure)

  • MySQL에서 제공하는 프로그래밍 기능
  • SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.
  • SQL + 프로그래밍 기능 = 스토어드 프로시저
  • 스토어드 프로시저를 사용하면 복잡한 SQL을 한 번에 실행할 수 있다.
  • '쿼리문의 집합'이라고도 할 수 있다.
  • 데이터베이스 개체로써 데이터베이스 내부에 저장된다.

스토어드 프로시저 형식

DELIMITER $$ -- 스토어드 프로시저를 묶어주는 기능을한다.
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEIGIN

    ...
    -- 이 부분에 SQL 프로그래밍 코드를 작성
    ...

END $$
DELIMITER ;

DELIMITER

  • 구분자라는 의미
  • 스토어드 프로시저에는 무수히 많은 SQL의 끝에 세미콜론(;)을 사용하는데, 세미콜론(;)이 나왔을 때, 이것이 SQL의 끝인지 스토어드 프로시저의 끝인지 모호하기 때문에, 구분자를 세미콜론(;) 외에 다른 것으로 바꿔 스토어드 프로시저의 내부 SQL과 스토어드 프로시저를 구분하기 위해 사용한다.
  • 마지막에 다시 DELIMITER에서 세미콜론(;)을 바꿔주면 원래대로 SQL의 구분자가 세미콜론(;)으로 돌아온다.

스토어드 프로시저 생성

DELIMITER $$                 -- 구분자
CREATE PROCEDURE 스토어드_프로시저_이름() -- 스토어드 프로시저 생성
BEGIN
    ...
    SQL문;
    ...
END $$
DELIMITER ;

스토어드 프로시저 실행

CALL 스토어드_프로시저_이름();

스토어드 프로시저를 실행한다.

입력 매개변수 저장

  • 입력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 매개변수를 지정해야한다.
IN 입력_매개변수_이름 데이터_형식

입력 매개변수가 있는 스토어드 프로시저 실행

CALL 프로시저_이름(전달_값);

사용 예시

DROP PROCEDURE IF EXISTS 스토어드_프로시저_이름; -- 있으면 삭제


DELIMITER $$                    -- 스토어드 프로시저 시작
CREATE PROCEDURE 스토어드_프로시저_이름(    -- 스토어드 프로시저 생성
        in 입력_매개변수_1 데이터_형식,        -- 입력 매개변수 1
        in 입력_매개변수_2 데이터_형식   )     -- 입력 매개변수 2
BEGIN
    SELECT * FROM 테이블        -- 실행될 SQL문
        where 컬럼명1 > 입력_매개변수_1 AND 컬럼명2 > 입력_매개변수_2;
END $$
DELIMITER ;                        -- 구분자 끝

call user_proc2(입력_매개변수_1, 입력_매개변수_2);    -- 입력 매개변수 2개

출력 매개변수
스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해서 얻을 수도 있다.
형식은 다음과 같다.

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

출력 매개변수가 있는 스토어드 프로시저 실행

CALL 프로시저_이름(@변수명);
SELECT @변수명;

사용 예시

DROP PROCEDURE IF EXISTS 스토어드_프로시저_이름;

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(    
    IN 입력_매개변수 데이터_형식,             -- 입력 매개변수 
    OUT 출력_매개변수 데이터_형식)            -- 출력 매개변수
BEGIN
    INSERT INTO 테이블_이름 VALUES(값1, 값2);    
    SELECT MAX(id) INTO 출력_매개변수 from 테이블_이름; -- SELECT 결과가 출력_매개변수로 들어간다.
END $$
DELIMITER ;

CALL 스토어드_프로시저_이름(입력_매개변수, @출력_매개변수);    -- 스토어드 프로시저 실행
Select @출력_매개변수;    -- 매개변수 출력

스토어드 프로시저를 생성할 때는 테이블이 없어도 스토어드 프로시저를 생성할 수 있다
그러나 호출할 때는 테이블이 생성되어있어야만, 스토어드 프로시저를 호출할 수 있다.

스토어드 프로시저 삭제

DROP PROCEDURE 스토어드_프로시저_이름;

CREATE PROCEDURE에서는 프로시저 이름 뒤에 괄호()를 붙이지만,
DROP PROCEDURE에서는 프로시저 이름 뒤에 괄호()를 붙이지 않는다.

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

스토어드 함수(stored function)

  • MySQL에서 제공하는 함수 외에 직접 함수를 만드는 기능을 제공한다.
  • 스토어드 프로시저와 모양이 비슷하지만, 세부적으로는 다르다.
    • 특히 용도가 다르다.
  • RETURN 예약어를 통해 하나의 값을 반환해야 하는 특징이 있다.
DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수 데이터_형식)
    RETURNS 반환형식 -- 스토어드 프로시저와 차이
BEGIN

    ...
    이 부분에 프로그래밍 코딩
    RETURN 반환값;   -- 스토어드 프로시저와 차이

END $$
DELIMITER ;
SELECT 스토어드_함수_이름();
  • RETURNS 문으로 반환할 값의 데이터 형식을 지정한다.
  • 본문 안에서는 RETURN으로 하나의 값을 반환해야한다.
  • 스토어드 함수의 매개변수는 모두 입력 매개변수이다.
  • 스토어드 함수의 호출은 SELECT문 안에서 호출된다.
  • 스토어드 함수 안에서는 SELECT를 사용할 수 없다.

스토어드 함수의 사용
스토어드 함수를 사용하기 위해서는 SQL로 스토어드 함수 생성 권한을 허용해줘야 한다.
단, 한 번만 설정해주면 이후에는 신경쓰지 않아도 된다.

SET GLOBAL log_bin_trust_function_creators = 1;

사용예시

DELIMITER $$
CREATE FUNCTION sumFunc(number1 int, number2 int)
    RETURNS int                     -- 반환 형식
BEGIN
    RETURN number1 + number2;       -- 반환 값
END $$
DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';

기존에 작성한 스토어드 함수의 내용을 확인

SHOW CREATE FUNCTION 함수_이름;

함수의 삭제

DROP FUNCTION calcYearFunc;

커서(cursor)

  • 스토어드 프로시저 내부에서 한 행씩 처리할 때 사용하는 프로그래밍 방식
  • 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.

커서의 동작 방식

  1. 커서 선언하기
  2. 반복 조건 선언하기
  3. 커서 열기>
  4. 데이터 가져오기 & 데이터 처리하기 ==>> 이 부분을 반복한다.
  5. 커서 닫기

커서는 대부분 스토어드 프로시저와 함께 사용된다.

사용 예시
다음은 가수 그룹의 평균 회원 수를 커서를 이용해서 구하는 방식이다.

DROP PROCEDURE IF EXISTS cursor_proc; -- 커서프로시저 있으면 삭제

DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
    DECLARE memNumber INT;                     -- 멤버수 변수 선언
    DECLARE cnt INt DEFAULT 0;                -- 한 행씩 읽어들이는 커서의 특성을 이용해서 행의 개수를 저장하기 위한 변수 : 기본값 0
    DECLARE totNumber INt DEFAULT 0;        -- 누적 합계 인원수
    DECLARE endOfRow BOOLEAN DEFAULT false;    -- 끝행인지 아닌지 판별하는 변수

    DECLARE memberCursor CURSOR FOR            -- 커서를 선언한다.
        SELECT mem_number FROM member;       -- 해당 SQL문을 커서로 만든다.

    DECLARE CONTINUE handler                -- 행이 더이상 없을 때,
        for not found set endOfRow = TRUE;    -- 끝행인지 아닌지 판별하는 endOfRow를 True로 바꾼다.

    open memberCursor;                        -- 커서를 연다.

    cursor_loop : LOOP                            -- 행을 반복한다.
        FETCH memberCursor INTO memNumber;    -- 선언한 커서에 있는 SQL문을 FETCH 예약어를 통해 한 행씩 읽어들인다. 

        IF endOfRow THEN                    -- 끝 행을 판별해서 TURE라면  
            LEAVE cursor_loop;                -- 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();

2-3. 자동 실행되는 트리거

트리거(trigger)

  • 사전적 의미로 '방아쇠'를 의미한다.
  • 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다.
  • 테이블에 INSERT, UPDATE, DELETE 작업이 발생하면 실행되는 코드다.
  • 스토어드 프로시저와 문법이 비슷하지만, CALL문으로 직접 실행하진 않는다.
  • 오직 테이블에 INSERT, UPDATE, DELETE 등의 이벤트가 발생할 경우에만 자동으로 실행된다
  • 스토어드 프로시저처럼 IN, OUT 매개변수를 사용할 수 없다.
  • 테이블에 이벤트가 먼저 적용된 후에 트리거가 실행된다.
  • 하나의 테이블에 여러 개의 트리거를 부착시킬 수 있다.

트리거를 사용하는 상황 예시
회원이 탈퇴하는 경우, 곧바로 데이터를 삭제하는 것이 아니라,
탈퇴한 회원으로 분류한 테이블에 옮겨, 탈퇴한 회원으로 관리할 수 있다.
만약, 탈퇴를 한 회원이 가입되어있을 당시의 정보를 알고 싶어할 때, 데이터가 삭제되어 있다면, 정보를 알고싶어도 알 방법이 없다.
따라서, 회원탈퇴 이벤트를 실행하기 전에 탈퇴한 회원을 관리하느 테이블에 데이터를 옮겨놓고, 회원탈퇴를 진행하면 된다.

이 때 데이터 작업자가 일일이 옮길 수 없으니, 트리거 이용해서 사용자가 회원탈퇴를 할 때 자동으로 탈퇴한 회원 관리 테이블에 자동으로 옮기는 기능을 제공해주는 것이 바로 트리거이다.

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

※ 트리거에는 BEFORE 트리거, AFTER 트리거가 있는데, BEFORE 트리거는 책에서 소개하지 않는다. 일반적으로 AFTER 트리거를 많이 사용한다고 한다.

예시
가수 데이터를 변경(수정/삭제)할 때,
변경된 타입(수정/삭제), 변경된 날짜, 변경한 사용자가 누군지 기록하는 백업 테이블이 있다고 가정했을 때,
변경 이벤트가 발생하면, 관련 기록을 남기는 트리거를 만들어보도록 하자.

CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr from member);


CREATE TABLE backup_singer
( mem_id char(8) not null,
    mem_name varchar(10) not null,
    mem_number int not null,
    addr char(2) not null,
    modType char(2), -- 변경된 타입, '수정' 또는 '삭제'
    modDate DATE, -- 변경된 날짜
    modUser varchar(30) -- 변경한 사용자
);



DROP TRIGGER IF EXISTS signer_updateTrg;

-- UPDATE 이벤트 트리거
DELIMITER $$
CREATE TRIGGER singer_updateTrg -- 트리거 이름
    AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
        FOR EACH ROW -- 각 행마다 적용
BEGIN 
    INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_user() );
END $$
DELIMITER ;    

DROP TRIGGER IF EXISTS singer_deleteTrg;

-- DELETE 이벤트 트리거
DELIMITER $$
CREATE TRIGGER singer_deleteTrg -- 트리거 이름
    AFTER DELETE -- 삭제 후에 적용하도록 지정
    ON singer -- 트리거를 부착할 테이블
    for EACH ROW -- 각 행마다 적용
BEGIN
    INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_user() );
END $$
DELIMITER ;

UPDATE singer SET addr = '영국' where mem_id = 'BLK'; -- 데이터 업데이트
DELETE FROM singer WHERE mem_number >= 7;  -- 데이터 삭제

SELECT * FROM backup_singer; -- 이벤트 발생 후, 테이블 확인

-- BLK    블랙핑크    4    경남    수정    2023-08-09    root@localhost
-- GRL    소녀시대    8    서울    삭제    2023-08-09    root@localhost
-- OMY    오마이걸    7    서울    삭제    2023-08-09    root@localhost
-- SPC    우주소녀    13    서울    삭제    2023-08-09    root@localhost
-- TWC    트와이스    9    서울    삭제    2023-08-09    root@localhost

TRUNCATE TABLE singer; -- 테이블에서 전체 테이블 삭제

SELECT * FROM backup_singer; -- 이벤트 발생 후, 테이블 확인
-- BLK    블랙핑크    4    경남    수정    2023-08-09    root@localhost
-- GRL    소녀시대    8    서울    삭제    2023-08-09    root@localhost
-- OMY    오마이걸    7    서울    삭제    2023-08-09    root@localhost
-- SPC    우주소녀    13    서울    삭제    2023-08-09    root@localhost
-- TWC    트와이스    9    서울    삭제    2023-08-09    root@localhost

-- TRUNCATE문은 DELETE트리거를 작동시키지 않는다.

OLD 테이블과 NEW 테이블

MySQL이 알아서 생성하고 관리하는 테이블

OLD 테이블

  • 테이블의 변경 작업(UPDATE나 DELETE)이 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 일시적인 테이블

NEW 테이블

  • INSERT문을 실행시키면, 테이블에 값이 들어가기 전 NEW 테이블에 값이 잠깐 들어간다.

UPDATE와 OLD테이블과 NEW 테이블

  • UPDATE를 사용하면, NEW테이블과 OLD테이블을 모두 사용한다.
  • 새로운 값은 NEW 테이블에 들어가고, 예전의 값은 OLD 테이블에 들어간다.

3. 요약

스토어드 프로시저, 스토어드 함수, 커서, 트리거에 대해 학습하며,
SQL에도 프로그래밍을 적용해보았다..

혼공 SQL은 이걸로 포스팅을 마무리한다!!
이제 리얼 MySQL로 간다.,,!!

728x90
저작자표시

'DB > RDB' 카테고리의 다른 글

SQL select 절에서 임의의 값 지정하기  (1) 2023.11.07
혼공 SQL - ch06(인덱스)  (0) 2023.08.09
혼공 SQL - ch05(테이블과 뷰)  (0) 2023.08.08
혼공 SQL - ch04(SQL 고호오급 문법)  (0) 2023.08.08
혼공 SQL - ch03(SQL 기본 문법)  (0) 2023.08.06
'DB/RDB' Related Articles more
Comments