MYSQL 프로시저
- 프로시저 정의
- 프로시저는 여러 쿼리를 한번에 수행하는 것이다.
- 프로시저 선언은 다음과 같다. (call 프로시저명, 같이 호출한다.)
DELIMITER $$
CREATE PROCEDURE EMP_INFO(IN V_EMPNO INT)
BEGIN
declare emp_name varchar(20);
declare emp_hiredate date;
declare emp_sal decimal(8, 2);
select first_name, hire_date, salary
into emp_name, emp_hiredate, emp_sal
from employees
where employee_id = v_empno;
select emp_name as 이름
,emp_hiredate as 입사일
,emp_sal as 월급;
END;
$$
DELIMITER ;
DROP PROCEDURE IF EXISTS '프로시저이름';
call emp_info(100);
- 프로시저 if문
- 프로시저 내 if문은 if ... then, elseif ... then, else end if; 와 같이 사용 가능하다.
delimiter
create procedure hiclass(in v_empno int)
begin
declare emp_name varchar(20);
declare emp_sal decimal(8,2);
select first_name,salary
into emp_name, emp_sal
from employees
where employee_id = v_empno;
if emp_sal >= 10000 then
select "상위레벨,", emp_sal;
elseif emp_sal < 10000 and emp_sal > 6000 then
select "평균레벨.", emp_sal;
else
select "하위레벨.",emp_sal;
end if;
end;
delimiter ;
call hiclass(100);
call hiclass(180);
call hiclass(200);
- 프로시저 내 반복문
- 반목문은 while, loop repeat이 사용가능하다.
delimiter $$
create procedure loopTest()
begin
declare x int;
set x = 1;
loop_label:LOOP
if x > 10 then
leave loop_label;
end if;
insert into testTable(num)
values(x);
set x = x + 1;
end LOOP;
end
$$
delimiter ;
- cursor (포인트개념)
delimiter
create procedure test_cursor(in emp_name varchar(20))
begin
declare cnt int default 0;
declare v_sal decimal(8, 2);
declare v_first_name varchar(20);
declare endOfRow boolean default false;
declare userCursor cursor for
select first_name, salary from employees where first_name = emp_name;
declare continue handler for not found set endOfRow = true;
open userCursor;
cloop: WHILE TRUE DO
FETCH USERCURSOR INTO v_first_name, v_sal;
IF endOfRow THEN
leave cloop;
end if;
set cnt = cnt + 1;
select concat(cnt, '번 ', v_first_name);
END WHILE;
close userCursor;
update employees
set salary = salary*1.1
where first_name = v_first_name;
end
delimiter ;
call test_cursor("Alana");
delimiter
create procedure expCursor_test(in v_deptno int)
begin
declare v_dname varchar(30);
declare emp_cnt double;
declare sal_avg double;
declare dept_avg cursor for
select department_name, count(employee_id) cnt, round(avg(salary), 3) sal
from employees e, departments d
where e.department_id = d.department_id
and e.department_id = v_deptno
group by department_name;
open dept_avg;
fetch dept_avg into v_dname, emp_cnt, sal_avg;
select concat('부서명:', v_dname);
select concat('사원수:',emp_cnt);
select concat('평균월급:',sal_avg);
close dept_avg;
end;
delimiter ;
drop procedure if exists expCursor_test;
call expCursor_test(30);
```;''