BE
[혼자공부하는SQL] 19강
07-2. 스토어드 함수와 커서
1. 스토어드 함수
스토어드 함수는 앞에서 배운 스토어드 프로시저와 비슷하다.
하지만 사용 방법이나 용도가 조금 다르니, 별개로 알아둘 필요가 있다.
1) 스토어드 함수의 개념과 형식
MySQL에서 제공하는 함수 외에, 사용자가 원하는 함수를 직접 만들 수 있다.
이를 스토어드 함수라고 한다.
(1) 생성 형식
DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
RETURNS 반환형식
BEGIN
- 이 부분에 프로그래밍 코딩
RETURN 반환값;
END $$
DELIMITER ;
SELECT 스토어드_함수_이름();RETURNS 문으로 반환할 값의 데이터 형식을 지정한다.
본문 안에서는 RETURN 문으로 하나의 값을 반환해야 한다.
스토어드 함수의 매개변수는 모두 입력 매개변수이며, IN을 붙이지 않는다.
스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT 문 안에서 호출된다.
스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT를 사용할 수 없다.
스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용한다.
2) 스토어드 함수의 사용
스토어드 함수를 사용하기 위해서는 먼저 SQL로 스토어드 함수 생성 권한을 허용해야 한다.
SET GLOBAL log_bin_trust_function_creators = 1;
(1) 예시1 : 두 수의 합계
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) 예시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 mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수'
FROM member;2. 커서로 한 행씩 처리하기
커서는 테이블에서 한 행씩 처리하기 위한 방식이다.
1) 커서의 기본 개념
(1) 개념
커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.
처음에는 커서가 행의 시작을 가리킨 후에 한 행씩 차례대로 접근한다.
Ex) 커서를 통해 테이블의 전체 인원 수를 알고 싶은 경우
(2) 커서의 작동 과정
커서는 대부분 스토어드 프로시저와 함께 사용된다.
2) 커서의 단계별 실습
(1) 사용할 변수 준비하기
DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
DECLARE toNumber INT DEFAULT 0; -- 전체 인원의 합계-- 행의 끝을 파악하기 위한 변수
DECLARE endOfRow BOOLEAN DEFAULT FALSE;(2) 커서 선언하기
DECLARE memberCursor CURSOR FOR
SELECT mem_number FROM member;(3) 반복 조건 선언하기
: 행의 끝에 다다르면, 앞에서 선언한 endOfRow 변수를 TRUE로 설정한다.
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;(4) 커서 열기
OPEN memberCursor;(5) 행 반복하기
cursor_loop : LOOP
이 부분을 반복
END LOOP cursor_loop-- 반복문을 빠져나갈 조건
IF endOfRow THEN
LEAVE cursor_loop;
END IF;LEAVE는 반복할 이름을 빠져나간다.
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_lopp;SELECT (totNumber / cnt) AS '회원의 평균 인원 수';(6) 커서 닫기
CLOSE memberCursor;3) 전체 코드
USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
DECLARE memNumber INT; -- 회원의 인원수
DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
DECLARE totNumber INT DEFAULT 0; -- 인원의 합계
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)
DECLARE memberCuror CURSOR FOR-- 커서 선언
SELECT mem_number FROM member;
DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입
FOR NOT FOUND SET endOfRow = TRUE;
OPEN memberCuror; -- 커서 열기
cursor_loop: LOOP
FETCH memberCuror 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 memberCuror;
END $$
DELIMITER ;
CALL cursor_proc();