[데이터베이스] stored procedure 생성 실습

문제 :

정답 :

use warehouse;

/*
show global variables like 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = 'ON';
*/

drop function if exists getClass;
drop procedure if exists grading;

delimiter //
create function getClass(qty int, gold_lb int, sliver_lb int)
	returns varchar(10)
begin
	declare class varchar(10);
    if qty >= gold_lb then
		set class = 'gold';
	elseif qty >= sliver_lb then
		set class = 'silver';
	else
		set class = 'bronze';
    end if;
    return class;
end //
delimiter ;
select getClass(100);

delimiter //
create procedure grading(in sname varchar(45))
begin
	declare _jno char(2);
    declare _jname varchar(45);
    declare _jcity varchar(45);
    declare _sumQty int;
    declare _class varchar(10);
 
    declare endOfRow boolean default false;
    declare jCursor cursor for select spj.jno, sum(spj.qty), j.jname, j.city from s join spj on s.sno = spj.sno join j on spj.jno = j.jno where s.sname = sname group by spj.jno, j.jname, j.city;
    declare continue handler for not found set endOfRow = true;
    
    set @sliver_lb = 600;
    set @glod_lb = 1000;
    set @setClassSql = 'select getClass(_sumQty, ?, ?) into @_class;';
    
    open jCursor;
	grade_loop: loop
		fetch jCursor into _jno, _sumQty, _jname, _jcity;
        -- select getClass(_sumQty, 1000, 600) into _class; 
        prepare statements from @setClassSql;
        execute statements using @glod_lb, @sliver_lb, @_class;
        if endOfRow then
			leave grade_loop;
		end if;
        -- select CONCAT('sum of qty:', _sumQty, '| 등급: ', _class, '| jname: ', _jname, '| city: ', _jcity) as 'output';
        
	end loop grade_loop;
    close jCursor;
end //
delimiter ;

call grading('Jones');

위 sqlfile을 저장해서 실행하려고 했는데 우분투에서는 auto save error가 나서 실행하지 못했다.

그래서 workbench의 create procedure기능과 create function 기능을 사용하여서 위 쿼리들을 나눠서 실행했다.

CREATE DEFINER=`root`@`localhost` FUNCTION `getClass`(qty int, gold_lb int, sliver_lb int) RETURNS varchar(10) CHARSET utf8mb3
BEGIN
		declare class varchar(10);
    if qty >= gold_lb then
		set class = 'gold';
	elseif qty >= sliver_lb then
		set class = 'silver';
	else
		set class = 'bronze';
    end if;
    return class;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `grading`(in sname varchar(45))
begin
	declare _jno char(2);
    declare _jname varchar(45);
    declare _jcity varchar(45);
    declare _sumQty int;
    declare _class varchar(10);
 
    declare endOfRow boolean default false;
    declare jCursor cursor for select spj.jno, sum(spj.qty), j.jname, j.city from s join spj on s.sno = spj.sno join j on spj.jno = j.jno where s.sname = sname group by spj.jno, j.jname, j.city;
    declare continue handler for not found set endOfRow = true;
    
    -- set @sliver_lb = 600;
    -- set @glod_lb = 1000;
    -- set @setClassSql = 'select getClass(_sumQty, ?, ?) into @_class;';
    
    open jCursor;
	grade_loop: loop
		fetch jCursor into _jno, _sumQty, _jname, _jcity;
        select getClass(_sumQty, 1000, 600) into _class; 
        -- prepare statements from @setClassSql;
        -- execute statements using @glod_lb, @sliver_lb, @_class;
        if endOfRow then
			leave grade_loop;
		end if;
        select CONCAT('sum of qty:', _sumQty, '| 등급: ', _class, '| jname: ', _jname, '| city: ', _jcity) as 'output';
        
	end loop grade_loop;
    close jCursor;
end

풀이

커서를 통해 entity들을 순회 하는데, 그 entity를 모아두는 테이블을
"declare jCursor cursor for select spj.jno, sum(spj.qty), j.jname, j.city from s join spj on s.sno = spj.sno join j on spj.jno = j.jno where s.sname = sname group by spj.jno, j.jname, j.city;" 를 통해서 설정해주는 것이 핵심이다.

즉, 입력으로 받은 sname(공급자 이름)과 일치하는 sno(공급자 번호)과 관련된 j(프로젝트)의 정보들을 spj(공급자 - 부품 - 프로젝트 정보),j(프로젝트) 테이블과 join해서 테이블을 만드는 것이다.

이때 커서를 통해서 순회하면서 function을 호출해서 j에 따라서 grade를 부여해주는데, 알 수 없는 에러가 나서 동적 sql을 사용하라는 조건을 사용하지 못했다. set @setClassSql =~~에서 class 변수가 초기화 되지 않았다고 떴다.

그래서 동적 sql 관련 쿼리들은 주석처리하고 동적 sql을 사용하지 않고 바로 변수를 넘겨주게끔 작성하였다. (맨 위의 쿼리문만 동적 sql 사용, 밑에 두 쿼리는 사용 x)

공부에 도움을 준 분들

https://velog.io/@juhyeon1114/MySQL-Cursor-%EA%B0%9C%EB%85%90-%EC%A0%95%EB%A6%AC-%EC%98%88%EC%A0%9C