MySQL - 함수, 커서, 트리거

함수(Function)

스토어드 프로시저와 유사하지만 형태와 사용 용도에서의 차이가 있다.

• 스토어드 프로시저의 파라미터와 달리 IN, OUT을 사용할 수 없다. 함수의 파라미터는 모두 입력 파라미터로만 사용된다.
• RETURNS문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN문으로 하나의 값을 반환해야 한다.
• 스토어드 프로시저는 CALL로 호출, 함수는 SELECT 문장 안에서 호출.
• 스토어드 프로시저는 안에 SELECT문을 사용할 수 있지만 함수는 SELECT를 사용할 수 없다
  • 예외적으로 집합 결과를 반환하지 않는 SELECT … INTO … 는 사용할 수 있다.
• 스토어드 프로시저 = 반복되는 과정을 담기 위한 용도, 함수 = 계산을 통해 하나의 값을 반환받기 위한 용도

-- 우선 스토어드 함수 생성 권한을 아래 쿼리문으로 허용해야한다.
SET GLOBAL log_bin_trust_function_creators = 1;

-- 함수 정의
DELIMITER $$
CREATE FUNCTION 함수명(파라미터 리스트)
	RETURNS 반환형식
BEGIN
	실행문
	RETURN 반환값;
END $$
DELIMITER ;

SELECT 함수명()

　

커서(Cursor)

테이블에서 여러 개의 행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식.

여러 줄, 여러 컬럼으로 구성된 검색결과.

일반적으로 알고있는 ‘커서’의 개념처럼 현재 작업중인 위치를 가리키며 한 줄씩 검색을 진행한다.

커서의 처리 순서:

1. 커서 선언 (DECLARE CURSOR)
2. 반복 조건 선언 (DECLARE CONTINUE HANDLER); 더 읽을 행이 없을 경우 실행할 내용 설정
3. 커서 열기 (OPEN)
4. 커서에서 데이터 가져오기 (FETCH)
5. 데이터 처리
6. 커서 닫기 (CLOSE)

CREATE PROCEDURE 프로시저명()
BEGIN
		DECLARE 변수명 타입; -- 커서로 행을 읽어서 값을 대입시킬 변수 선언
    DECLARE 마침변수명 BOOLEAN DEFAULT FALSE; -- 검색할 테이블 행의 끝 도달 여부확인용
		
		DECLARE 커서명 CURSOR FOR SELECT 컬럼명 FROM 테이블명; -- 1.커서 선언
    
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 2.반복 조건 선언
    # cursor에서 한 줄씩 fetch. 언제까지? not found(커서에 읽을 줄이 더 없다)까지.
    # open => fetch(한줄씩출력):반복 => not found 도달 => close
    
		OPEN 커서명;  -- 3.커서 열기
    
		루프명: LOOP  -- 반복구간 지정
				FETCH FROM 커서명 INTO 변수명; -- 4.커서에서 한 줄씩 읽어서 5.변수에 값 저장
        IF 마침변수명
						THEN LEAVE 루프명;  -- 더 이상 읽을 행이 없어 'NOT FOUND'가 되면 루프 종료
        END IF;

        SELECT 변수명;  -- 변수값 출력
		END LOOP;
    CLOSE 커서명; -- 6.커서 닫기
END

　

트리거(Trigger)

테이블에 부착되어 INSERT, UPDATE, DELETE 작업이 발생되면 실행되는 코드

제약조건처럼 데이터의 무결성을 위한 기능으로 주로 활용된다.

• 테이블이 수정되었을 때의 내용들을 기록
• 테이블이 수정되기 전 입력될 데이터 값을 미리 확인

CREATE TRIGGER 트리거이름
	{BEFORE|AFTER) {INSERT|UPDATE|DELETE} -- 작동 시기(전/후), 작동할 동작 선택
	ON 테이블명
	FOR EACH ROW
BEGIN
	실행문;
END