파트 8-2. 스토어드 프로그램 - 스토어드 함수, 커서
1. 스토어드 함수
mysql이 사용자가 원하는 모든 함수를 제공하진 않으므로 사용자가 직접 함수를 만들어서 사용할 필요가 있다. 이렇게 사용자가 직접 만들어서 사용하는 함수를 스토어드 함수라고 한다.
- 스토어드 프로시저의 파라미터와 달리 in,out 등을 사용할 수 없다. 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다.
- 스토어드 함수는 returns문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 return 문으로 하나의 값을 반한해야 한다. 스토어드 프로시저는 별도의 반환하는 구문이 없으며, 꼭 필요하다면 여러 개의 out 파라미터를 사용해서 값을 반환한다.
- 스토어드 프로시저는 call로 호출하지만, 스토어드 함수는 select 문장 안에서 호출된다.
- 스토어드 프로시저 안에는 select문을 사용할 수 있지만, 스토어드 함수 안에서는 집합 결과를 반환하는 select를 사용할 수 없다.
- 스토어드 프로시저는 여러 sql문이나 숫자 계산 등의 다양한 용도로 사용되지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용된다.
- 스토어드 함수를 사용하기 위해서는 먼저 쿼리문으로 스토어드 함수 생성 권한을 허용해줘야 한다.
-- 형식
set global log_bin_trust_function_creators =1; -- 권한 허용
delimiter $$
create function 스토어드함수이름(파라미터)
returns 반환형식
begin
코딩
return 반환값;
end $$
delimiter ;
select 스토어드함수이름();
-- 예시
drop function if exists getagefunc;
delimiter $$
create function userfunc(byear int)
returns int
begin
declare age int;
set age = year(curdate()) - byear; -- 현재날짜에서 연도만 받아서 - 매개 인자
return age;
end $$
delimiter ;
select getagefunc(1979);
select getagefunc(1979) into @age1979; -- 반환값을 변수에 저장
select userid, name, getagefunc(birthyear) as '나이' from usertbl; -- 함수의 반환값을 열로 사용
drop function getagefunc; -- 삭제
2. 커서
스토어드 프로시저 내부에서 커서를 사용할 수 있다. 커서는 테이블에서 여러 개의 행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식이다.
그림과 같은 방식이 표준이 되는 커서 방식이다. 커서의 대부분의 순서는 다음과 같다. 커서끝을 확인하기 위한 변수 선언, 커서 선언, 반복조건선언, 커서 오픈, 루프문, 커서 닫기순이다. 예시를 한 번 보자.
-- 예시 1
DROP PROCEDURE IF EXISTS cursorProc;
DELIMITER $$
-- 프로시저 선언
CREATE PROCEDURE cursorProc()
BEGIN
DECLARE userHeight INT; -- 고객의 키
DECLARE cnt INT DEFAULT 0; -- 고객의 인원 수(=읽은 행의 수)
DECLARE totalHeight INT DEFAULT 0; -- 키의 합계
-- 행의 끝의 여부를 확인하기 위한 변수
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
-- 이 부분이 커서 선언인데 파이썬의 for문과 비슷하다.
-- usertbl에서 뽑아온 모든 height행 usercuror에 들어있다고 보면 된다.
-- 나중에 fetch로 행 하나씩 뽑아쓴다.
-- declare 커서이름 커서타입 for 어디서가져올지
DECLARE userCuror CURSOR FOR-- 커서 선언
SELECT height FROM userTbl;
-- 반복 조건
-- 커서에 이제 들어갈게 없어서 끝에 도달하면 true로 바꿔주는 구문
DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입
FOR NOT FOUND SET endOfRow = TRUE;
-- 여기 위에까지가 커서를 사용하기 위한 선언, 조건들을 작성한 것
OPEN userCuror; -- 커서 열기
cursor_loop: LOOP
-- 여기서도 파이썬의 for문과 비슷하다 약간 위에 비슷하다고 한 것과 같이보면
-- 파이썬의 for문을 나눠논 것 같은 느낌이다.
-- usercuror에 있는 height하나를 뽑아내서 userheight에 대입
-- 만약 더이상 뽑아내지 못하면 위에서 적은대로 endofrow 를 true로 수정한다.
FETCH userCuror INTO userHeight; -- 고객 키 1개를 대입
IF endOfRow THEN -- 더이상 읽을 행이 없으면 Loop를 종료
LEAVE cursor_loop;
END IF;
SET cnt = cnt + 1;
SET totalHeight = totalHeight + userHeight;
END LOOP cursor_loop;
-- 고객 키의 평균을 출력한다.
SELECT CONCAT('고객 키의 평균 ==> ', (totalHeight/cnt));
CLOSE userCuror; -- 커서 닫기
END $$
DELIMITER ;
CALL cursorProc();
-- 예시 2 커서를 이용해 열로 고객의 등급을 추가
ALTER TABLE userTbl ADD grade VARCHAR(5); -- '고객등급' 열 만들기
DROP PROCEDURE IF EXISTS gradeProc;
DELIMITER $$
-- 프로시저 선언
CREATE PROCEDURE gradeProc()
BEGIN
DECLARE id VARCHAR(10); -- 사용자 아이디를 저장할 변수
DECLARE hap BIGINT; -- 총 구매액을 저장할 변수
DECLARE userGrade CHAR(5); -- 고객 등급 변수
-- 끝을 표시할 변수 선언
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
DECLARE userCuror CURSOR FOR-- 커서 선언
SELECT U.userid, sum(price*amount)
FROM buyTbl B
RIGHT OUTER JOIN userTbl U
ON B.userid = U.userid
GROUP BY U.userid, U.name ;
-- 더이상 뽑아낼 행이 없다면 endofrow = ture
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;
OPEN userCuror; -- 커서 열기
grade_loop: LOOP
-- usercuror에서 행 하나 뽑아서 id,hap에 대입
FETCH userCuror INTO id, hap;
IF endOfRow THEN
LEAVE grade_loop;
END IF;
-- 케이스에 따라 usergrade 대입
CASE
WHEN (hap >= 1500) THEN SET userGrade = '최우수고객';
WHEN (hap >= 1000) THEN SET userGrade ='우수고객';
WHEN (hap >= 1) THEN SET userGrade ='일반고객';
ELSE SET userGrade ='유령고객';
END CASE;
-- usergrade 업데이트
UPDATE userTbl SET grade = userGrade WHERE userID = id;
END LOOP grade_loop;
CLOSE userCuror; -- 커서 닫기
END $$
DELIMITER ;
CALL gradeProc(); -- 프로시저 호출
SELECT * FROM userTBL; -- 잘 업데이트 됬는지 확인
