MYSQL 프로시저

O0·2022년 6월 21일
1

MYSQL

목록 보기
3/6
post-thumbnail

MYSQL 프로시저


  1. 프로시저 정의
  • 프로시저는 여러 쿼리를 한번에 수행하는 것이다.
  • 프로시저 선언은 다음과 같다. (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 이름 -- SELECT 문 수행
				,emp_hiredate as 입사일
               ,emp_sal as 월급;    
END;
$$
DELIMITER ;

DROP PROCEDURE IF EXISTS '프로시저이름';
call emp_info(100);
  1. 프로시저 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);
  1. 프로시저 내 반복문
  • 반목문은 while, loop repeat이 사용가능하다.
-- loop
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 ;
  1. 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				-- cursor 선언
			select first_name, salary from employees where first_name = emp_name;
           
	declare continue handler for not found set endOfRow = true; -- 커서가 발견할 수 없을 때 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;
		-- cursor fetch(붙이는 것)
       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);
```;''//
profile
O0

0개의 댓글