Oracle-游标

【游标-while】

/*
利用游标，调整公司中员工的工资；

工资范围    调整基数
0-5000        5%
5000-10000    3%
10000-15000   2%
15000-        1%
*/

declare

cursor emp_sal_cursor is select employee_id,salary from employees;

v_temp number(4,2);

v_empid employees.employee_id%type;
v_sal employees.salary%type;

begin

open emp_sal_cursor;
--打开游标
fetch emp_sal_cursor into v_empid,v_sal;
--获取游标

while emp_sal_cursor%found loop

if v_sal <5000 then v_temp := 0.05;
elsif v_sal <100000 then v_temp := .03;
elsif v_sal < 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;

update employees set salary = salary *(1+v_temp)
where employee_id = v_empid;

//重新捕获游标
fetch emp_sal_cursor into v_empid,v_sal;
//关闭循环
end loop;
//关闭游标
close emp_sal_cursor;

end;

【游标-for】

declare

cursor emp_sal_cursor is select employee_id,salary from employees;

v_temp number(4,2);


begin

//不用显示打开、关闭与捕获游标
for c in emp_sal_cursor loop

if c.salary <5000 then v_temp := 0.05;
elsif c.salary <100000 then v_temp := .03;
elsif c.salary < 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;

update employees set salary = salary *(1+v_temp)
where employee_id = c.employee_id;

end loop;

end;

【隐式游标】

--隐式游标：更新指定员工salary(涨工资：10)

begin

update employees set salary = salary +10
where employee_id = 10;

if sql % notfound
then dbms_output.put_line('查无此人');
end if;

end;

【带参游标】

declare

--定义游标参数
cursor emp_sal_cursor(dept_id number,sal number) 
is
select employee_id id ,salary sal from employees
where department_id = dept_id and salary > sal;
//在下面使用游标的时候，可以使用 定义的别名：id  sal
v_temp number(4,2);


begin

--不用显示打开、关闭与捕获游标
for c in emp_sal_cursor(dept_id => 80,sal => 4000) loop
--为游标参数赋值
if c.sal <5000 then v_temp := 0.05;
elsif c.sal <100000 then v_temp := .03;
elsif c.sal < 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;

update employees set salary = salary *(1+v_temp)
where employee_id = c.id;

dbms_output.put_line(c.id||','||c.sal);

end loop;

end;