수업/SQL
procedure - cursor
케잉
2023. 11. 28. 20:10
cursor
특정 쿼리문의 처리결과를 담은 영역을 가리키는 포인터
📢 문제
부서번호를 입력받아서(파라미터로 받아서- p_deptno number) 부서 평균 급여(변수선언)보다
많이 받으면 10% 적거나 같으면 20% 인상을 적용하여 급여 테이블을 수정(update - commit)하는 프로시저를 작성하시오.
----------------------------------------------
OPEN emp_cur;
CLOSE emp_cur;
변수 rate number(3,1) - 99.9
avg_sal number(7,2) - 99999.99
-----------------------------------------------
--커서 선언하기
CURSOR emp_cur IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = p_deptno;
--급여평균을 구한다
SELECT avg(sal) INTO avg_sal
FROM emp
WHERE deptno = p_deptno;
create or replace procedure proc_emp_update2(p_deptno IN number)
is
--평균급여 담기
avg_sal number(7,2):=0.0;
--커서에서 꺼내온 사원번호 담기
v_empno number(5):=0;
-- 커서에서 꺼내온 급여 담기
v_sal number(7,2):=0;
-- 커서에서 꺼내온 이름 담기
v_ename varchar2(20):=' ';
--인상요율담을 변수
rate NUMBER(3,1) :=0;
CURSOR emp_cur IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = p_deptno;
BEGIN
SELECT avg(sal) INTO avg_sal
FROM emp
WHERE deptno = p_deptno;
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno, v_ename, v_sal;
EXIT WHEN emp_cur%NOTFOUND;--커서에 값이 없을 때
IF v_sal > avg_sal THEN--10%인상요율
rate := 1.1;
ELSIF v_sal <= avg_sal THEN--20%인상요율
rate := 1.2;
END IF;
UPDATE emp
SET sal = sal*rate
WHERE empno = v_empno;
END LOOP;
commit;
CLOSE emp_cur;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/