PL/SQL提供了LOOP、WHILE-LOOP、FOR-LOOP语句来执行循环
如下:
---使用循环结构为所有员工加薪
declare
---定义加薪比例
c_manager constant number := 0.15;
c_salesman constant number := 0.12;
c_clerk constant number := 0.10;
v_job varchar(100); --定义职位变量
v_empno varchar(20); --定义员工编号变量
v_ename varchar(60); --定义员工名称变量
cursor c_emp
is
select job,empno,ename from scott.emp for update;
begin
open c_emp;
loop
fetch c_emp
into v_job,v_empno,v_ename;
exit when c_emp%notfound;
if v_job = 'clerk'
then
update scott.emp set sal = sal * (1 + c_clerk) where current of c_emp;
elsif v_job = 'c_salesman'
then
update scott.emp set sal = sal * (1 + c_salesman) where current of c_emp;
elsif v_job = 'c_manager'
then
update scott.emp set sal = sal * (1 + c_manager) where current of c_emp;
end if;
dbms_output.put_line('已经为员工'||v_empno||':'||v_ename||'成功加薪!');
end loop;
close c_emp;
exception
when no_data_found
then
dbms_output.put_line('没有找到员工数据');
end;