이것이MySQL이다 - 02) 스토어드 프로그램

스토어드 함수

 

스토어드 함수 (Stored Function)

사용자가 직접 만들어서 사용하는 함수

스토어드 프로시저와 유사

• 형태와 사용 용도에 있어 차이 있음

스토어드 함수의 개요

 

 

스토어드 함수와 스토어드 프로시저의 차이점

스토어드 함수

• 파라미터에 IN, OUT 등을 사용할 수 없음
  • 모두 입력 파라미터로 사용
• RETURNS문으로 반환할 값의 데이터 형식 지정
  • 본문 안에서는 RETURN문으로 하나의 값 반환
• SELECT 문장 안에서 호출
• 함수 안에서 집합 결과 반환하는 SELECT 사용 불가
  • SELECT… INTO… 는 집합 결과 반환하는 것이 아니므로 예외적으로 스토어드 함수에서 사용 가능
• 어떤 계산 통해서 하나의 값 반환하는데 주로 사용

 

스토어드 프로시저

• 파라미터에 IN, OUT 등을 사용 가능
• 별도의 반환하는 구문이 없음
  • 필요하다면 여러 개의 OUT 파라미터 사용해서 값 반환 가능
• CALL로 호출
• 스토어드 프로시저 안에 SELECT문 사용 가능
• 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용

 

스토어드 함수

• 스토어드 함수를 사용하기 위해서는 스토어드 함수 생성 권한을 허용 해야함

• ex) 2개의 숫자의 합계를 계산하는 스토어드 함수

 

 

 

 

스토어드 함수 실습

• 저장해 놓았던 sqlDB.sql 이용해 sqlDB 데이터베이스 초기화
• 출생년도 입력하면 나이 출력되는 함수 작성
  • 테이블을 조회할 때 주로 사용되는 함수

 

 

• 함수 호출
  • SELECT getAgeFunc(1979);
  • 1979년생의 현재 나이가 출력됨
• 함수의 반환값을 SELECT … INTO … 로 저장했다가 사용 가능

• 두 출생년도의 나이차가 출력됨

 

• 함수는 주로 테이블을 조회할 때 활용됨
  • SELECT userID, name, getAgeFunc(birthYear) AS '만 나이' FROM userTbl;

 

 

• 현재 저장된 스토어드 함수의 이름 및 내용 확인
  • SHOW CREATE FUNCTION getAgeFunc;

 

• 스토어드 함수 삭제
  • 다른 데이터베이스 rocph아 마찬가지로 DROP문 사용
  • DROP FUNCTION getAgeFunc;

 

 

커서

 

 

커서의 개요

커서(Cursor)

• 스토어드 프로시저 내부에 사용
• 일반 프로그래밍 언어의 파일 처리와 방법이 비슷함
• 행의 집합을 다루기 편리한 기능 제공
  • 테이블에서 여러 개의 행을 쿼리한 후,
• 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식

 

 

 

 

 

 

 

 

커서의 처리 순서

 

 

커서 이용해 고객의 평균 키 구하는 스토어드 프로시저

• AVG() 내장 함수와 동일한 기능 구현(AVG() 내장 함수가 빠르고 편리)
  • 커서 이해를 위한 실습
  • 조건이 붙은 평균은 AVG() 함수대신 커서 활용

use sqldb;

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;	-- 행의 끝인지 확인
    
    -- 커서 선언
    declare userCursor cursor for
		select height from usertbl;
        
	declare continue handler		-- 행의 끝이면 endOfRow 변수에 true를 대입
		for not found set endOfRow = true;
        
	open userCursor; 		-- 커서 열기
    
    cursor_loop: loop
		fetch userCursor into userheight;	-- 고객 키를 하나씩 대입
        if endOfRow then 		-- endOfRow 더이상 읽을 행이 없으면 Loop 빠져나가기
			leave cursor_loop;
		end if;
        
        set cnt = cnt + 1;					-- 고객 수를 누적
        set totalHeight = totalHeight + userHeight;
    end loop cursor_loop;
    
    select concat('고객의 키 평균 ==> ', (totalHeight/cnt));
    
    close userCursor;
end $$
delimiter ;

call cursorProc();

 

• 스토어드 프로시저 호출

 

 

 

커서의 처리 순서

테이블에 열 하나 추가 후 구매총액 따라 회원등급 설정(P. 460 ~ 461)

• userTBL에 고객 등급을 입력할 열을 추가
  • ALTER TABLE userTbl ADD grade VARCHAR(5);  -- 고객 등급 열 추가
• 스토어드 프로시저 작성

-- 고객 등급
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;
            
	declare continue handler
		for not found set endOfRow = true;
        
	open userCuror;		-- 커서 열기
    grade_loop: Loop
		fetch userCuror into id, hap;	-- 첫 행 값을 대입
        if endOfRow then
			leave grade_loop;
		end if;
        
        case 
        	when (hap >= 1500) then set userGrade = '최고우수고객';
			when (hap >= 1000) then set userGrade = '우수고객';
			else set userGrade = '유령고객';
		end case;
        
		update userTbl set grade = userGrade where userID =id;
		end loop grade_loop;
        
		close userCuror; -- 커서 닫기
end $$
delimiter ;

call gradePrc();
select * from userTbl;

 

테이블에 열 하나 추가 후 구매총액 따라 회원등급 설정

• 스토어드 프로시저 호출 및 고객 등급 확인