SET serveroutput ON DECLARE--定義光標代表給哪些員工漲工資
-- alter table emp rename column "jop" to empjop;
cursor cemp IS SELECT
empno,
jop
FROM
?emp;
pempno emp.empno % TYPE;
pjop emp.jop % TYPE;
BEGIN--打開光標
?open cemp;
?loop--取出一個員工
?FETCH cemp INTO pempno,
?pjop;
?exit
?WHEN cemp % notfound;
--判斷員工的職位
?IF
??pjop = 'PRESIDENT' THEN
???UPDATE emp
???SET sal = sal + 1000
??WHERE
???empno = pempno;
??
??elseif pjop = 'MANAGER' THEN
???UPDATE emp
???SET sal = sal + 800
???WHERE
????empno = pempno;
???ELSE UPDATE emp
???SET sal = sal + 400
???WHERE
????empno = pempno;
???
??END IF;
??
?END loop;
--關閉光標
close cemp;
END;