[혼자 공부하는 SQL] chapter 7. 스토어드 프로시저(7-2)

https://www.hanbit.co.kr/store/books/look.php?p_code=B6846155853

혼자 공부하는 SQL

위 내용은 <혼자 공부하는 SQL>을 기반으로 공부하여 작성 내용입니다.

 

7-2 스토어드 함수와 커서

• 스토어드 함수 : MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공함
• 스토어드 프로시저와 모양이 비슷하지만 용도가 다르며, RETURNS 예약어를 통해서 하나의 값을 반환해야 함

 

• 커서 : 스토어드 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식
• 형태가 대부분 비슷하게 고정되어 있음

 

스토어드 함수

스토어드 함수의 개념과 형식

• 사용자가 원하는 모든 함수를 제공하지 않으므로 사용자가 필요한 함수를 만들어서 사용 가능

-- 기본 형식
delimiter $$
create function 스토어드 함수 이름(매개변수)
    returns 반환 형식
begin

    이 부분에 프로그래밍 코딩
    return 반환 값;
    
end $$
delimiter ;

select 스토어드 함수 이름();

• 스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값 반환해야 함
• 스토어드 함수의 매개변수는 모두 입력 매개변수임. 그리고 in을 붙이지 않음
• 스토어드 프로시저는 CALL로 호출, 스토어드 함수는 SELECT 문 안에서 호출
• 스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT를 사용 불가
• 스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용
• 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용

 

스토어드 함수의 사용

• 스토어드 함수를 사용하기 위해서는 먼저 다음 SQL로 스토어드 함수 생성 권한을 허용해줘야 함
• 한 번만 설정해주면 이후에는 신경쓰지 않아도 됨

set global log_bin_trust_function_creator = 1;

 

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

use market_db;

drop function if exists sumFunc;
delimiter $$
-- 2개의 정수형 매개변수 전달 받음
create function sumFunc(number1 int, number2 int)
    returns int -- 반환 데이터 형식을 정수로 지정
begin
    return number1 + number2; -- 정수형 결과 반환
end $$
delimiter ;

select sumfunc(100, 200) as '합계'; -- 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 ~ INTO ~ 로 저장했다가 사용 가능
• 함수의 반환값을 각 변수에 저장한 후, 계산에 사용

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;

 

함수 삭제

drop function calcYearFunc;

 

 

커서로 한 행씩 처리하기

• 커서(cursor) : 테이블에서 한 행씩 처리하기 위한 방식

커서의 기본 개념

• 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리함
• 커서 처리 순서(4~5 부분을 반복함)
  1. 커서 선언하기
  2. 반복 조건 선언하기
  3. 커서 열기
  4. 데이터 가져오기
  5. 데이터 처리하기
  6. 커서 닫기

커서의 단계별 실습

커서 통합 코드

use market_db;
drop procedure if exists cursor_proc;
delimiter $$
create procedure cursor_proc()
begin
    -- 사용할 변수 준비
    declare memNumber int; -- 회원의 인원수
    declare cnt int default 0; -- 읽은 행의 수
    -- 전체 인원의 합게와 읽은 행의 수를 누적시켜야 하기 때문에 0으로 초기화
    declare totNumber int default 0; -- 전체 인원의 합계
    -- 행의 끝을 파악하기 위한 변수 선언, 처음에는 행의 끝이 아닐테니 false로 초기화
    declare end0fRow boolean default false;
    
    -- 커서 선언
    declare memberCursor cursor for
        select mem_number from member;
        
    -- 행이 끝나면 반복하지 않음
	declare continue handler
        for not found set end0fRow = true;
        
    -- 커서 열기
	open memberCursor;
    
    -- 행 반복
    cursor_loop: loop
        -- 한 행씩 읽어 옴
        -- 각 회원의 인원수가 한 번에 하나씩 저장됨
        fetch memberCursor into memNumber;
        
        IF end0fRow 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()