【游标-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;
