MySQL #프로시저, 커서

 

프로시저(Proceduer)

 

프로시저는 간단하게 파이썬에서 함수라고 생각하면 된다.

함수 호출
call 함수명()

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(enum int)
BEGIN
	declare ename varchar(20); #변수선언
    select last_name into ename
    from employees where employee_id = enum;
    select ename;
END

 

같은 아웃풋을 보이는 프로시저들을 다양한 방법으로 표현해보자.

(if/case/loop/repeat/while)

 

• IF

CREATE DEFINER=`root`@`localhost` PROCEDURE `p5`(enum int)
BEGIN
	declare dept_id int default 0;
	declare dept_name varchar(20);
	select department_id into dept_id from employees where employee_id=enum;
    if dept_id = 10 then set dept_name = '개발1팀';
	elseif dept_id = 20 then set dept_name = '개발2팀';
    elseif dept_id = 30 then set dept_name = '개발3팀';
    elseif dept_id = 40 then set dept_name = '개발4팀';
    else set dept_name = '디자인팀';
    end if;
    select dept_name;
END

 

• case

CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`(enum int)
BEGIN
	declare dept_id int default 0;
	declare dept_name varchar(20);
	select department_id into dept_id from employees where employee_id=enum;
    case 
		when dept_id = 10 then set dept_name = '개발1팀';
		when dept_id = 20 then set dept_name = '개발2팀';
		when dept_id = 30 then set dept_name = '개발3팀';
		when dept_id = 40 then set dept_name = '개발4팀';
		else set dept_name = '디자인팀';
    end case;
    select dept_name;
END

 

• Loop

CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`(x int)
BEGIN
	declare y int default 1;
    l1:loop
		select y;
        set y=y+1;
        if y>x then leave l1;   #y가 x보다 크면 루프를 나가라
        end if;
	end loop;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `p8`(x int)
BEGIN
	declare y int default 1;
    l1:loop
		select y;
        set y=y+1;
        if y <=x then iterate l1;   #반복할 조건
        end if;
	end loop;
END

 

• 반복문 Repeat

CREATE DEFINER=`root`@`localhost` PROCEDURE `p9`(x int)
BEGIN
	declare y int default 1;
    repeat         #반복문
		select y;
        set y=y+1;
        until y > x end repeat;   #조건 만족시 반복끝
END

 

• while

CREATE DEFINER=`root`@`localhost` PROCEDURE `p10`(x int)
BEGIN
	declare y int default 1;
    while y <= x do        #반복문
		select y;
        set y=y+1;
	end while;
END

 

 

커서(Cusor)

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `cusor_test`()
BEGIN
	declare enum int;
    declare ename varchar(20);
    declare sal int;
    
    #cursor: 여러줄, 여러컬럼으로 구성된 검색결과. 
    declare done tinyint default 0;
    declare c1 cursor for select employee_id, last_name, salary from employees;
    declare continue handler for not found set done=1;  #반복조건으로 사용
    #cursor에서 한줄씩 fetch. 언제까지? not found(커서에서 더 읽을줄이 없음)까지.
    #open -> fetch(한줄씩 읽음) : 반복 -> close
    
    open c1;
    l1: loop
		fetch from c1 into enum, ename, sal;  #커서 c1에서 한 줄씩 읽음
        if done then leave l1;  #not found이면 루프 빠져나감
        end if;
        select enum, ename, sal;   #변수값 출력
	end loop;
    

END