저장 프로시저 예제

1. 사원번호를 매개변수로 입력 받아 사원번호, 이름, 담당업무, 연봉, 소속부서명을 보여주는 stored procedure 를 작성하시오 (p_emp_sel_1)

CREATE PROCEDURE p_emp_sel_1(id int)
BEGIN
	select empno, ename, job, sal, dname from emp, dept
	where emp.deptno=dept.deptno and empno = id;
END

 

2. 부서번호, 부서명, 위치를 매개변수로 입력 받아 새로운 부서 정보를 생성하는 stored procedure 를 작성하시오 (p_dept_insert_1)

CREATE  PROCEDURE p_dept_insert_1(d_num int , d_name varchar(10), d_loc varchar(10))
BEGIN
	insert into dept values(d_num, d_name, d_loc);
END

 

3. 사원번호, 사원 이름을 매개변수로 입력 받아 이름을 수정하는 stored procedure 를 작성하시오 (p_emp_update_1)

CREATE PROCEDURE p_emp_update_1 (e_num int, e_name varchar(10))
BEGIN
	update emp set ename=e_name where empno = e_num;
END

 

 

1. 사원번호를 매개변수로 입력 받아 사원의 담당업무가 ‘CLERK’ 이면 급여를 20% 올리고, 아닌 경우는 10%를 올리는 stored procedure 를 작성하시오 (p_emp_update_2)

CREATE PROCEDURE p_emp_update_2 (e_num int)
BEGIN
	declare e_job varchar(10);
    	select job into e_job
    	from emp
    	where empno=e_num;
    
    	if(e_job = 'clerk') then
			update emp 
			set sal=sal*1.2 
			where empno=e_num;
    	end if;
    
    	if(e_job != 'clerk') then
			update emp
        	set sal=sal*1.1
        	where empno=e_num;
		end if;
END

 

2. 사원번호를 매개변수로 입력 받은 후에 그 사원이 속한 부서 사람들의 연봉 합계를 구하여 출력하는 stored procedure 를 작성하시오 (p_emp_sel_2)

CREATE PROCEDURE p_emp_sel_2 (e_num int)
BEGIN
    select sum(sal) 
    from emp
    where deptno = (select deptno from emp where empno = e_num);
END

 

3. 사원번호를 매개변수로 입력 받은 후에 사원의 급여가 평균급여 이상이면 해당 사원의 근무지를 보이고, 그렇지 않으면 사원의 직무를 보이는 stored procedure 를 작성 하시오. (p_emp_sel_3)

CREATE PROCEDURE p_emp_sel_3 (e_num int)
BEGIN
	declare e_sal decimal(10,4);
    	declare avg_sal decimal(10,4);
    
    	select sal into e_sal
    	from emp
    	where empno=e_num;
    
   	 	select avg(sal) into avg_sal
    	from emp;
    
   
    	if(avg_sal <= e_sal) then
			select loc from empd where empno=e_num;
		else
			select job from emp where empno=e_num;
		end if;
END