스토어드 함수와 커서
스토어드 함수는 MySQL에서 제공하는 내장 함수(MySql에서 제공하는 함수) 외에 직접 함수를 만드는 기능이다. 이는 스토어드 프로시저와 모양이 비슷하나 용도가 다르며 RETURNS 예약어를 통해서 하나의 값을 필수적으로 반환해야 한다.
커서는 스토어드 프로시저 안에서 데이터를 한 행씩 처리할 때 사용하는 프로그래밍 방식을 말한다. 자세한 건 아래에서 확인해보도록 하자.
스토어드 함수
스토어드 함수(Stored Function)이란 사용자가 직접 만들어서 사용하는 함수를 말한다.
DELIMITER $$
CREATE FUNCTION 함수명(매개변수)
RETURNS 반환형식
BEGIN
프로그래밍 코딩
RETURN 반환값;
END $$
DELIMITER ;
SELECT 함수명();스토어드 함수는 RETURNS 문으로 반환할 데이터 형식을 지정하고, RETURN 문으로 하나의 값을 반환한다. 스토어드 함수의 매개변수는 모두 입력 매개변수이며 IN 예약어를 사용하지 않고 명시한다.
스토어드 프로시저와 다른 점은 스토어드 프로시저는 CALL로 호출하나 함수는 SELECT 문 안에서 호출한다. 또한 스토어드 함수 내부에는 SELECT문을 사용할 수 없다. 스토어드 함수는 주로 어떤 계산을 통해 하나의 값을 반환하는데 사용된다.
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 '합계';<결과>
sumFunc란 함수를 생성했고, 2개의 정수형 매개변수 number1, number2를 생성했다. 그리고 RETURN 문으로 정수형 결과를 반환했다.
생성한 함수를 호출하기 위해 SELECT 문으로 2개의 정수값 전달하였다.
<실행>
DROP FUNCTION IF EXISTS calcYearFunc;
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 ~ INTO로 결과값을 변수에 저장했다가 재사용할 수 있다.
<실행>
SELECT mem_id mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수' FROM member;<결과>
이번에는 스토어드 함수를 이용해서 member 테이블의 모든 행에 대해 활동 햇수를 구해보았다. YEAR(날짜) 함수는 연도만 추출해주는 함수로, calcYearFunc() 함수를 SELECT문에 사용하여 각 회원별 활동 햇수를 출력하였다.
SHOW CREATE FUNCTION 함수이름;만약 기존에 작성된 스토어드 함수의 내용을 확인하고 싶다면 위 쿼리문을 입력하여 확인할 수 있다. 함수이름에는 ‘()’를 붙이지 않음에 주의하자.
DROP FUNCTION 함수이름;작성된 함수를 삭제하려면 DROP FUNCTION 함수이름 쿼리문으로 삭제할 수 있다. 이것도 마찬가지로 함수이름에 ‘()’를 붙이지 않는다.
커서
커서는 테이블에서 한 행씩 처리하기 위한 방식이다. 정확히는 일련의 데이터에 순차적으로 액세스할 때 검색 및 ‘현재 위치’를 포함하는 데이터 요소이다.
커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근하여 값을 처리한다. 커서는 행의 시작을 가리킨 후에 데이터가 있는 다음행으로 이동한다. 이렇게 한 행씩 차례대로 접근하여 행의 끝을 간 뒤 종료된다.
커서의 작동 순서는 아래와 같으며, 대부분 스토어드 프로시저와 커서가 함께 사용된다.
- 커서 선언
- 반복 조건 선언
- 커서 열기
- 데이터 가져오기
- 데이터 처리하기
- 커서 닫기
커서는 4, 5번 과정을 반복하고, 반복이 끝나면 커서는 닫으면서 끝난다.
아래에서 각각의 과정을 어떻게 행할 수 있는지 확인해보도록 하자.
1. 커서 선언
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;각 회원의 인원수(memNumber), 전체 인원의 합계(totNumber), 읽은 행의 수(cnt)를 저장하는 변수를 3개 선언한다. 그리고 행의 끝을 파악하기 위한 변수 endOfRow도 생성한다. 만약 해당 값이 TRUE인 경우 행의 끝에 도달했다는 의미이다.
마지막으로 DECLARE 커서명 CURSOR FOR SELECT 열 FROM 테이블명을 통해 커서를 생성했다. 커서라는 것은 결국 SELECT 문으로 위 쿼리에서 member 테이블을 조회하는 구문을 커서로 수행할 것이다.
2. 반복 조건 선언
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;DECLARE COTINUE HANDLER는 반복 조건을 준비하는 예약어이다. 해당 예약어 뒤에 FOR NOT FOUND (종료 시 행할 작업)을 통해 더 이상 행이 없을 때 (종료 시 행할 작업)을 수행한다.
3. 커서 열기
OPEN memberCursor;OPEN 예약어를 사용하여 커서를 열 수 있다. 앞서 생성한 memberCursor를 열도록 하겠다.
4, 5. 데이터 가져오기, 데이터 처리하기 반복
cursor_loop: LOOP
반복 실행 부분
END LOOP cursor_loop루프명: LOOP (반복 실행 문장) END LOOP 루프명을 통해 (반복 실행 문장)에 들어간 코드를 반복 실행한다.
IF endOfRow THEN
LEAVE cursor_loop;
END IF;(반복 실행 부분) 안에서 LEAVE 루프명을 통해 루프명에 해당하는 반복을 탈출할 수 있다. 여기서는 행의 끝에 도달, 즉 endOfRow가 TRUE이면 탈출하도록 설정하였다.
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;주어진 반복 조건과 탈출 조건을 전체를 표현한 쿼리이다. FETCH 커서명 INTO 변수명은 한 행씩 읽어와서 변수에다가 읽어온 값을 할당하는 문장이다. 결국 위 반복은 한 행씩 회원 수를 읽어와서 행의 끝에 도달할 때 까지 전체 회원 수에 덧셈을 하는 반복이다.
SELECT (totNumber/cnt) AS '회원의 평균 인원 수';마지막으로 반복을 탈출하면, 평균 인원수를 계산하여 출력하게 된다.
6. 커서 닫기
CLOSE memberCursor;커서를 열어놓고 닫을때는 CLOSE 예약어를 사용하여 커서를 닫을 수 있다.
<실행>
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();<결과>
앞선 커서 예제를 통합한 쿼리문이다. cursor_proc()이라는 스토어드 프로시저 내부에 커서 예제를 담아서 저장하고 호출하는 쿼리문이다.
출처
- 혼자 공부하는 SQL(우재남 저, 한빛미디어)
