[혼공 SQL] 6주차 (1) - CH 07 스토어드 프로시저

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

스토어드 프로시저 기본

스토어드 프로시저의 개념과 형식

스토어드 프로시저는 MySQL에서 제공하는 기능으로 SQL 쿼리문들을 하나로 묶어서 편리하게 사용하는 프로그래밍 기능

• 기본 형식

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름 (IN 또는 OUT 매개변수)
BEGIN

-- SQL 프로그래밍 코드 작성

END $$
DELIMITER;

--호출
CALL 스토어드_프로시저_이름

--수정
ALTER PROCEDURE 스토어드_프로시저_이름

--삭제
DROP PROCEDURE 스토어드_프로시저_이름

--프로시저 내용 조회
SHOW CREATE PROCEDURE 스토어드_프로시저_이름

1. DELIMITER : 구분자 변경, 기존의 ; 사용 시 SQL이 끝난 건지 스토어드 프로시저가 끝난 건지 모르기에 변경
2. CREATE로 PROCEDURE 생성
3. IN, OUT 매개변수 지정
   3-1. 프로시저 내 변수 선언 = DECLARE
   3-2. 변수값 설정 = SET
4. 호출 시 CALL 사용
5. 수정 ALTER PROCEDURE 사용
6. 삭제 DROP PROCEDURE 사용
7. 내용 조회 시 SHOW CREATE PROCEDURE 사용

스토어드 프로시저의 장단점

😀장점

• 하나의 요청으로 여러 SQL문 실행 가능 = 네트워크 부하 감소
• 미리 구분 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간 감소
• DB 트리거와 결합해 복잡한 규칙에 의한 데이터의 참조 무결성 유지 가능

😒단점

• 코드 자산으로서 재사용성이 나쁨 = 실무 사용 거의 X
• 업무 사양 변경 시 외부 응용 프로그램과 함께 프로시저 정의 변경 필수

---

스토어드 프로시저 실습

입력 매개변수의 활용

USE market_db;
DROP PROCEDURE IF EXISTS user_proc1;
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
  SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;

CALL user_proc1('에이핑크');

• 입력 매개변수 사용 시 IN 변수명 데이터형식
• 위 코드는 userName 을 입력받아 mem_name이 매개변수와 같은 행을 member에서 모두 불러옴
• 입력 매개변수는 2개 이상도 가능

출력 매개변수의 활용

DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_porc3(
	IN txtValue CHAR(10),
    OUT outValue INT)
BEGIN
  INSERT INTO noTable VALUES(NULL, txtValue);
  SELECT MAX(id) INTO outValue FROM noTable;
END $$
DELIMITER ;

DESC noTable;

• 출력 매개변수는 OUT 변수명 데이터타입 사용
• noTable 없지만 스토어드 프로시저는 정상 생성되는 모습
  • 스토어드 프로시저 만드는 시점에 존재하지 않는 테이블 사용 가능
  • CALL 할 때는 테이블이 존재해야함

CREATE TABLE IF NOT EXISTS noTable(
id INT AUTO_INCREMENT PRIMARY KEY,
txt CHAR(10)
);

CALL user_proc3('테스트1', @myvalue);
SELECT CONCAT('입력된 ID 값 ==>', @myValue);

@변수명 형태로 출력 매개변수 전달

SQL 프로그래밍의 활용

1. IF ~ ELSE 문

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 ('오마이걸');

• DECLARE 로 지역변수 선언
• IF ~ ELSE 문을 활용해 메세지 제어

2. WHILE 문

DROP PROCEDURE if EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
DECLARE hap INT;
DECLARE num INT;
SET hap = 0;
SET num = 1;

while (num <= 100) DO
SET hap = hap + num;
SET num = num + 1;
END while;
SELECT hap AS '1~100 합계';
END $$
DELIMITER ;

CALL while_proc();

• DECLARE와 SET으로 변수 제어
• WHILE - DO 로 반복문 활용
• 1~100을 모두 더한 값이 나옴 (5050)

3. 동적 SQL

DROP PROCEDURE if EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
IN tableName VARCHAR(20)
)
BEGIN
SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
PREPARE myQuery FROM @sqlQuery;
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL dynamic_proc('member');

• PREPARE와 EXCUTE 활용
• 동적 SQL은 실시간으로 SQL을 만든 후 실행

+) MySQL 날짜 관련 함수

• YEAR(날짜) : 연도
• MONTH(날짜) : 월
• DAY(날짜) : 일
• CURDATE() : 현재 날짜

---

2) 스토어드 함수와 커서

스토어드 함수

스토어드 함수의 개념과 형식

MySQL은 다양한 함수를 제공하고 사용자가 함수를 만들 수도 있다. 이 함수를 스토어드 함수라 부른다.

• 기본 형식

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

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

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

--삭제
DROP FUNCTION 스토어드_함수_이름

스토어드 프로시저와 비슷하지만 차이점을 알아야한다.

스토어드 프로시저	스토어드 함수
여러 SQL문, 숫자 계산 등 다양한 용도로 사용	계산을 통해 하나의 값을 반환하는데 사용(로직을 위해 사용)
0,1,N개의 반환 값 가능	1개의 반환 값 필수
입출력 파라미터 사용 가능	입력 파라미터만 가능
CALL 로 호출	SELECT로 호출
프로시저 안에서 SELECT문 사용 가능	함수 안에 집합 결과 반환 SELECT문 사용불가

스토어드 함수의 사용

• MySQL 권한 설정 변경 (스토어드 함수 사용 시 필수)

SET GLOBAL log_bin_trust_function_creators = 1;

• 예제1) 간단한 덧셈

DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

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

1. 정수형 매개변수 두개(number1, number2)를 받음
2. RETURN 반환타입 설정 (INT)
3. RETURN값 반드시 명시

• 예제2) 쿼리 문에 활용된 스토어드 함수

DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
    RETURNS INT
BEGIN
    DECLARE runYear INT;
    SET runYear = YEAR(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;

SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 횟수' FROM member;

1. 활동 년수를 구하는 함수
2. YEAR() 내장함수 사용
3. RETURN값 반드시 명시

---

커서로 한 행씩 처리하기

커서의 기본 개념

커서는 한 행씩 처리하기 위한 방식.

• 작동순서
  

커서의 단계별 실습

회원의 평균 인원수를 구하는 스토어드 프로시저 구현에서 커서를 활용해 한 행씩 접근해 회원의 인원수를 누적시키는 방식으로 처리해보자.

• 사용할 변수 준비

DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
DECLARE totNumber INT DEFAULT 0; -- 전체 인원 합계
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 확인용

• 커서 선언

DECLARE memberCursor CURSOR FOR
    SELECT mem_number FROM member;

DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;

1. 커서도 결국 SELECT문이란 것을 기억
2. DECLARE CONTINUE HANDLER : 반복 조건을 준비하는 예약어
3. FOR NOT FOUND : 더 이상 행이 없을 때 수행하는 문장

• 커서 열기

OPEN memberCursor;

• 행 반복 (반복하는 쿼리)

cursor_loop: LOOP
    FETCH memberCursor 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 '회원의 평균 인원 수';

1. LEAVE : 반복문 탈출
2. FETCH : 한 행씩 읽어옴
3. 최종 목표인 회원의 평균 인원 수 계산

• 커서 닫기

CLOSE memberCursor;

커서의 통합 코드

USE market_db;

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 memberCursor CURSOR FOR
        SELECT mem_number FROM member;
	
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET endOfRow = TRUE;
        
    OPEN memberCursor;
        
    cursor_loop: LOOP
        FETCH memberCursor 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 memberCursor;
END $$
DELIMITER ;

CALL cursor_proc();

---

3) 자동 실행되는 트리거

트리거 기본

트리거의 개요

트리거(Trigger)는 특정 테이블에 INSERT, DELETE, UPDATE 등의 DML 문이 수행됐을 때, 자동으로 동작하도록 작성된 프로그램

방아쇠를 당기면 총알이 나가는 과정에서 방아쇠를 당기는 것이 트리거
방아쇠 = DML문, 총알 = 트리거로 작동하는 프로그램

트리거의 기본 작동

---

트리거 활용

USE market_db;

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

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

DELETE FROM trigger_table WHERE id = 3;
SELECT @msg;

• 트리거 이름 = myTrigger
• AFTER DELETE : DELETE가 방아쇠, DELETE 있으면 실행된단 뜻
• ON table_trigger : 트리거 부착할 테이블 지정

+) 왜 쓰나요?

➡️ 은행에서 고객의 정보를 DB에서 삭제
➡️ 고객이 이 전 거래 내역을 알려달라함
➡️ 헉스바리 은행은 이미 삭제했는데???????????????
➡️ 삭제한 고객 정보를 다른 테이블 (ex. 해지 고객)에 자동으로 옮길 수 없을까?
➡️ 트리거를 사용

즉, 데이터 무결성을 지키기 위해서 사용

---

4) 미션

1. singer 테이블

USE market_db;
CREATE TABLE singer(SELECT mem_id, mem_name, mem_number, addr FROM member);
SELECT * FROM singer;

2. backup_singer 테이블

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)
);
SELECT * FROM backup_singer;

3. 트리거 생성 (UPDATE + DELETE)

DROP TRIGGER IF EXISTS singer_updateTrg;
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 ;

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 ;

4. 실제 작용 확인

UPDATE singer SET addr='영국' WHERE mem_id LIKE 'BLK';

DELETE FROM singer WHERE mem_number >= 7;

SELECT * FROM backup_singer;

💡에러 발생💡

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

MySQL 워크벤치는 UPDATE에 대해 안전 모드로 되어있어 설정을 변경해줘야힘

해결법

1. 쿼리문 작성

SET SQL_SAFE_UPDATES = 0;

2. GUI 사용

상단 Edit → Preferences 클릭 → SQL Editor 클릭 → 맨 아래 Safe Upadates 체크 해제 후 워크벤치 재실행

5. 결과