[MySQL] 7장 스토어드 함수 | 커서 | 트리거

 

 스토어드 함수 

MySQL에서 제공하는 함수 외에 직접 만들어서 사용하는 함수

• 입력매개변수와 반환값을 따로 설정한다

 

스토어드 함수 문법

DELIMITER $$
CREATE FUNCTION 함수_이름 ( 매개변수 )
	RETURNS 반환형식
BEGIN
	프로그래밍 코딩
    RETURN 반환값;
END $$
DELIMITER ;

SELECT 함수_이름();

 

 

예제 1) 곱하기

SET GLOBAL log_bin_trust_function_creators = 1; -- 스토어드 함수 생성 권한 허용 (한 번만 설정해주면 된다)

USE market_db;
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 '합계';

 

 

예제 2) 현재 날짜에서 데뷔 날짜를 빼서 활동 햇수 구하기

DROP FUNCTION IF EXISTS calcYearFunc;
DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
	RETURNS INT
BEGIN
	DECLARE runYear INT; -- 활동기간(연도)
	SET runYear = YEAR(CURDATE()) - dYear; -- CURDATE() : 현재 날짜
	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; -- 테이블 컬럼 활용해서 출력

 

 

결과)

 

 

 

---

 

 커서 

첫 번째 행부터 마지막 행까지 한 행씩 접근해서 처리하는 것

1. 변수 선언
2. 커서 선언
3. 반복 조건 선언
4. 커서 열기
5. 커서 닫기

 

커서는 문법이 복잡해서 예제로 바로 알아보도록 하자

 

예제) 회원의 평균 인원 수 구하기

USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc() -- 커서는 프로시저 안에 들어가는 개념이다
BEGIN
	-- 1. 변수 선언
	DECLARE memNumber INT;
	DECLARE cnt INT DEFAULT 0;
	DECLARE totNumber INT DEFAULT 0;
	DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부 (기본 FALSE)
    
	-- 2. 커서 선언
	DECLARE memberCursor CURSOR FOR
		SELECT mem_number FROM member; 
	
	-- 3. 반복 조건 선언하기
	DECLARE CONTINUE HANDLER
		FOR NOT FOUND SET endOfRow = TRUE; -- 반복조건 : 행의 끝이면 endOfRow를 TRUE로 변환
        
	-- 4. 커서 열기
	OPEN memberCursor;
    
	cursor_loop : LOOP
		FETCH memberCursor INTO memNumber; -- 한 행씩 읽어오기
        
		IF endOfRow THEN
			LEAVE cursor_loop; -- endOfRow가 TRUE면 loop 탈출
		END IF;
        
        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;
	END LOOP cursor_loop;
    
	SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
    
	-- 5. 커서 닫기
	CLOSE memberCursor;
END $$
DELIMITER ;
    
CALL cursor_proc();

 

 

결과)

 

---

 

 

 트리거 

INSERT, UPDATE, DELETE 작업이 일어난 경우 해당 데이터가 삭제되기 전에 다른 곳에 자동으로 저장해주는 기능

 

 

예제 1) 데이터 삭제  시 메세지 나타내기

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

# 실행 코드
SET @msg = '';

INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg; -- 결과 X
UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg; -- 결과 X

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

-- 결과 : '가수그룹이 삭제됨'

 

 

예제 2) 데이터 수정 시 다른 테이블에 저장

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

DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg -- 트리거 이름
	AFTER UPDATE -- UPDATE 데이터를 backup_singer 테이블에 저장
	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() ); -- OLD : 시스템 테이블, 데이터 수정/삭제되기 전에 잠시 머무는 곳
END $$
DELIMITER ;

 

 

      OLD란?

• MySQL의 시스템 테이블로 데이터들이 수정/삭제되기 전에 잠시 머무는 곳이다.

 

 

예제 3) 데이터 삭제 시 다른 테이블에 저장

DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg -- 트리거 이름
	AFTER DELETE -- 데이터 DELETE 시 trigger 실행
    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() ); -- OLD : 시스템 테이블, 수정/삭제되기 전에 
END $$
DELIMITER ;

 

• modType (변경된 타입)을 UPDATE는 '수정', DELETE는 '삭제'로 설정한다

 

 

트리거 실행 코드)

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 6;

 

결과)

backup_singer 테이블