[MySQL] 스토어드 프로시저와 SQL의 파이썬 연결

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

스토어드 프로시저?

스토어드 프로시저는 MySQL에서 제공하는 프로그래밍 기능입니다.

쿼리 문의 집합으로 어떤 동작을 일괄 처리하기 위한 용도로 사용합니다. 연산식, 조건문, 반복문 등으로도 사용할 수 있습니다. 아래는 스토어드 프로시저의 필수적인 형식입니다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름 ( IN 또는 OUT 매개변수 )
BEGIN 
 -- SQL 프로그래밍
END $$
DELIMITER ;

여기서 DELIMITER는 '구분자'라는 의미입니다.

SQL문은 항상 세미콜론(;)으로 끝났죠? 이것을 구분자라고 하는데, 프로시저 안에서는 수많은 SQL 문이 사용되고 따라 많은 세미콜론 구분자가 사용됩니다.

프로시저의 끝도 세미콜론으로, SQL문의 끝도 세미콜론으로 구분한다면, 세미콜론이 프로시저의 끝인지 SQL문의 끝인지 모호해지기 때문에 프로시저의 구분자를 $$로 바꾸어 사용합니다 (DELIMITER $$). 프로시저가 끝난 뒤에는 다시 구분자를 세미콜론으로 바꿔줍니다(DELIMITER ;).

만든 프로시저를 호출하기 위해서는 CALL구문을 이용합니다.

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

스토어드 프로시저에는 매개변수를 이용할 수 있습니다.

입력 매개변수를 지정하는 형식은 다음과 같습니다.

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

입력 매개변수가 있는 스토어드 프로시저를 실행할 때에는 다음과 같은 CALL문을 이용합니다.

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

출력 매개변수의 형식은 다음과 같습니다

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

출력 매개변수가 있는 스토어드 프로시저를 이용하기 위해서는 다음과 같이 사용합니다.

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

다음은 SQL 프로그래밍을 활용한 예제입니다.

DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN
    DECLARE debutYear INT; -- 변수 선언
    SELECT YEAR(debut_date) into debutYear FROM member
        WHERE mem_name = memName;

    IF (debutYear >= 2015 ) THEN 
            SELECT '신인 가수네요. 화이팅 하세요.' AS '메세지';
    ELSE
            SELECT '고참 가수네요. 화이팅 하세요.' AS '메세지';
    END IF;
END $$
DELIMITER ;

CALL ifelse_proc ('오마이걸');

스토어드 함수

스토어드 함수란?

스토어드 함수는 MySQL 에서 제공하는 다양한 내장 함수 외에 사용자가 직접 만들어 사용하는 함수입니다.

스토어드 함수는 RETURNS 예약어를 통해 하나의 값을 반환해야 합니다.

RETURNS는 스토어드 함수에서 반환할 값의 데이터 형식을 미리 지정할 때 사용하고, RETURN은 스토어드 함수에서 값을 반환할 때 사용하는 예약어입니다.

다음은 스토어드 함수의 기본적인 형식입니다.

DELIMETER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
    RETURNS 반환형식
BEGIN 
    -- 프로그래밍
    RETURN 반환값;
END $$
DELIMITER ;

스토어드 함수를 사용하기 위해서는 다음 SQL로 스토어드 함수 생성 권한을 허용해야 합니다.

SET GLOBAL log_bin_trust_function_creators = 1;

스토어드 함수를 이용한 예제를 살펴보겠습니다.

DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION     SUMfUNC(number1 INT, number2 INT)
    RETURNS INT
BEGIN 
    RETURN number1 + number2;
END $$
DELIMITER ;
SELECT sumFunc(100, 200) AS '합계';

커서

커서(cursor)는 테이블의 모든 행을 한 행씩 처리할 때 사용하는 방식입니다. DECLARE로 선언하며, 그 내용은 SELECT문입니다.

커서는 다음과 같은 작동 순서를 통해 처리되며, 대부분 스토어드 프로시저와 함께 사용됩니다.

예제를 통해 살펴보겠습니다.

회원의 평균 인원수를 구하는 스토어드 프로시저를 작성하겠습니다.

DROP PROCEDURE IF EXISTS cursor_proc;
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 memberCuror CURSOR FOR 
        SELECT mem_number FROM member;

    DECLARE CONTINUE HANDLER -- 반복 조건을 준비하는 예약어
        FOR NOT FOUND SET endOfRow = TRUE; -- 더 이상 행이 없을 때 set endOfRow = true; 수행

    OPEN memberCuror; -- 커서 열기

    cursor_loop : LOOP -- (시작) 반복 시작
        FETCH memberCuror 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 memberCuror; -- 커서 닫기
END $$
DELIMITER ;
SELECT sumFunc(100, 200) AS '합계';

자동 실행되는 트리거

트리거(trigger) 는 DML 문(Insert, Update, Delete)의 이벤트가 발생할 때 자동으로 실행되는 프로그래밍 기능으로 사용자가 추가 작업을 잊어버리는 실수를 방지합니다.

따라 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있는데 이런 것을 데이터의 무결성이라고 합니다.

다음은 트리거를 테스트할 테이블입니다.

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, '블랙핑크');

테이블에 트리거를 부착해 보겠습니다.

DROP TRIGGER IF EXISTS myTigger;
DELIMITER $$
CREATE TRIGGER myTrigger            -- 트리거 이름을 myTrigger 로 지정
    AFTER DELETE                     -- 이 트리거는 DELETE 문이 발생된 이후에 작동하라는 의미
    ON trigger_table                 -- 이 트리거를 부착할 테이블
    FOR EACH ROW                    -- 각 행마다 적용시킨다는 의미, 보통의 트리거에는 항상 써줌
BEGIN
    SET @msg = '가수 그룹이 삭제됨' ;     -- 트리거 실행 시 작동되는 코드들
END $$
DELIMITER ;

DELETE 문을 실행하니 트리거가 작동해서 @msg 변수에 트리거에서 설정한 내용이 입력된 것을 확인할 수 있습니다.

미션

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

테이블을 복사하여 새로운 테이블 singer를 만들었습니다.

가수 테이블에 INSERT나 UPDATE작업이 일어나는 경우, 변경 전의 데이터를 저장할 백업 테이블을 미리 생성합니다.

UPDATE가 발생하였을 때 작동하는 티리거를 작성했습니다.

OLD테이블은 UPDATE나 DELETE가 수행될 때, 변경하기 전의 데이터가 잠깐 저장되는 임시 테이블입니다. OLD를 이용하여 backup_singer에 수정 전 데이터를 입력합니다.

DELETE가 발생하였을 때 작동하는 트리거를 작성했습니다.

이제 트리거가 잘 작동하는지 테스트해 보겠습니다.

1건이 수정되고 4건이 삭제된 것을 확인할 수 있습니다.

마무리

사실 이번 주에 연동 프로그래밍과, GUI 프로그래밍을 하는 것이 권장 진도율인데

제가 일주일간 여행을 다녀오면서 머리가 좀 리셋이 된 거 같아서 다시 한번 복습하고 진도를 나가려고 합니다.

그때쯤이면 혼공 이벤트가 끝나있겠지만요...

9기가 마지막이라는 게 슬프네요

혼공 시리즈는 알록달록하니 모으는 재미도 있어요

6주 동안 혼공단 덕분에 갓생 산 거 같은 기분