[SQL] Chapter 7. (18~20강)

18강.

스토어드 프로시저

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

• 스토어드 프로시저(Stored Procedure)란 MySQL에서 제공하는 프로그래밍 기능이다.
  • C언어, Java, Python 등의 프로그래밍과는 조금 차이가 있지만, MySQL 내부에서 사용할 때 적절한 프로그래밍 기능을 제공해준다.
  • 또한, 스토어드 프로시저는 쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용한다.

 

• 자주 사용하는 일반적인 쿼리를 반복하는 것보다는 스토어드 프로시저로 묶어놓고, 필요할 때마다 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다.

 

• ①: 필수 항목으로 스토어드 프로시저를 묶어주는 기능을 한다.
  • $$는 $ 1개만 사용해도 되지만 명확하게 표시하기 위해 2개를 사용한다.
  • ##, %%, &&, // 등으로 바꿔도 된다.
• ②: 스토어드 프로시저의 이름을 정해준다.

 

• 스토어드 프로시저를 호출하는 형식은 다음과 같다.

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

 

 

• market_db의 member 테이블을 호출하는 스토어드 프로시저

USE market_db ;

DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
	SELECT * FROM member ;
END $$
DELIMITER ;

CALL user_proc() ;
--------------------------------
DROP PROCEDURE user_proc ; #스토어드 프로시저 삭제

 

---

(2) 스토어드 프로시저 실습

매개변수의 사용

• 스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다.
• 입력 매개변수를 지정하는 형식은 다음과 같다.

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

 

• 입력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 괄호 안에 값을 전달하면 된다.

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

 

• 출력 매개변수의 형은 다음과 같다.

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

 

• 출력 매개변수에 값을 대입하기 위해서는 주로 SELECT ~ INTO 문을 사용한다.

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

 

(2 - 1) 입력 매개변수의 활용

• mem_name을 입력변수로 활용

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

CALL user_proc1('에이핑크') ;

 

• mem_number와 height를 입력변수로 활용

DELIMITER $$
CREATE PROCEDURE user_proc2(
	IN userNumber INT,
	IN userHeight INT )
BEGIN
	SELECT * FROM member
	WHERE mem_number > userNumber AND height > userHeight ;
END $$
DELIMITER ;

CALL user_proc2(6,165) ;

 

(2 - 2) 출력 매개변수의 활용

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

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

CALL user_proc3('테스트1', @myValue) ;

SELECT CONCAT('입력된 ID 값 ==>', @myValue) ;

• 입력 매개변수로 CHAR(10) 형식의 txtValue를 받고, 출력 매개변수로 INT 형식의 outValue를 받는다.
• 실제 실행되는 SQL은 noTable 테이블에 NULL값과 txtValue값을 받는다.
  • 이때, noTable의 outValue에는 MAX(id)값을 받는다.

 

• noTable은 INT 형식의 PK인 id와 CHAR(10) 형식의 txt를 열로 갖는다.
  • id는 NULL 값이니까 1부터 1씩 증가하는 인덱스로 생성된다.

 

• CALL에서 입력 매개변수 txtValue에 '테스트1'을 받고 출력 매개변수로 @myValue를 받는다.
  • 출력 매개변수는 outValue = MAX(id) = @myValue

3번 CALL했을 때, noTable	3번 CALL했을 때, @myValue

 

(2 - 3) IF ~ ELSE 문 활용

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로 변수 선언

출력 결과

 

(2 - 4) WHILE 문 활용

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() ;

출력 결과

 

(2 - 5) 동적 스토어드 프로시저

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') ;

---

19강.

1. 스토어드 함수

• 스토어드 함수는 스토어드 프로시저와 비슷하지만, 사용 방법이나 용도가 조금 다르기에 별개로 알아 둘 필요가 있다.
• MySQL은 SUM(), CAST(), CONCAT() 등 다양한 함수를 제공한다. 
  • 그러나, 사용자가 원하는 모든 함수를 제공하지는 않으므로, 원하는 함수를 직접 만들어서 사용할 필요가 있다.
  • 이를 스토어드 함수라고 한다.

 

• 매개변수는 모두 입력 매개변수이다.
• CALL 대신에 SELECT로 호출한다.
• 스토어드 함수 안에서는 SELECT를 사용할 수 없다.

---

(1) 두 정수의 합

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

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

출력 결과

 

(2) 데뷔연도를 통해 활동 기간 구하기 

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

SELECT calcYearFunc(2010) AS '활동 기간' ;

출력 결과

 

• 아래와 같이 함수값을 변수에 지정해서 계산할 수도 있다.

SELECT calcYearFunc(2007) INTO @debut2007 ;
SELECT calcYearFunc(2013) INTO @debut2013 ;
SELECT @debut2007-@debut2013 AS '2007과 2013 차이' ;

출력 결과

 

• 전체 데이터에 함수를 적용하는 것도 가능하다.
  • 이와 같이 사용하는 경우가 더 잦다.

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

---

2. 커서 (CURSOR)

• 커서는 테이블에서 한 행씩 처리하기 위한 방식이다.
  • 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.

 

• 커서는 일반적으로 다음과 같은 작동 순서로 처리된다.

---

(1) 커서의 단계별 실습

(1 - 1) 사용할 변수 준비하기

• 회원의 평균 인원수를 계산하기 위해서 각 회원의 인원수, 전체 인원의 합계, 읽은 행의 수 변수를 3개 준비한다.
  • 전체 인원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 DEFAULT 문을 사용해서 초기값을 0으로 설정

DECLARE memNumber INT ;
DECLARE cnt INT DEFAULT 0 ;
DECLARE totNumber INT DEFAULT 0 ;

 

• 추가로 행의 끝을 파악하기 위한 변수 endOfRow를 준비한다.
  • 처음에는 당연히 행의 끝이 아닐 테니 FALSE로 초기화한다.

DECLARE endOfRow BOOLEAN DEFAULT FALSE ;

 

 

(1 - 2) 커서 선언하기

• 커서라는 것은 결국 SELECT 문이다.
  • member 테이블을 조회하는 구문을 커서로 만들어 놓으면 된다.

DECLARE memberCursor CURSOR FOR
	SELECT mem_number FROM member ;

 

(1 - 3) 반복 조건 선언하기

• 행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TRUE로 설정
• DECLARE CONTINUE HANDLER는 반복 조건을 준비하는 예악어이다.
• 그리고 FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 수행한다.
  • 즉, 행이 끝나면 endOfRow에 TRUE를 대입한다.

DECLARE CONTINUE HANDLER
	FOR NOT FOUND SET endOfRow = TRUE ;

 

(1 - 4) 커서 열기

• 위에서 준비한 커서를 OPEN으로 연다.

OPEN memberCursor ;

 

(1 - 5) 행 반복하기

• 커서의 끝까지 한 행씩 접근해서 반복한다.

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 - 6) 커서 닫기

CLOSE memberCursor ;

---

전체 코드

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() ;

출력 결과

---

20강.

트리거

• 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다.
• 테이블에 INSERT, UPDATE, DELETE 작업(이벤트)이 발생하면 실행되는 코드이다.
  • 테이블에 미리 부착되는 프로그램 코드라고 생각하면 된다.  
  • SELECT는 아님.

CREATE TABLE 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
	ON trigger_table
	FOR EACH ROW
BEGIN
	SET @msg = '가수 그룹이 삭제됨' ;
END $$
DELIMITER ;
------------------------------------------------------
SET @msg = '' ;
INSERT INTO trigger_table VALUES(4, '마마무') ;
SELECT @msg ;  #트리거가 작동하지 않음.

DELETE FROM trigger_table WHERE id=4 ;
SELECT @msg ;  #트리거가 작동함.

---

(1) 트리거 활용

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) -- 변경한 사용자
);

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;
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;

TRUNCATE TABLE singer;  #트리거가 작동하지 않음.

SELECT * FROM backup_singer;

출력 결과