Oracle中用游标更新字段值的面试题

如下表

SQL> set pagesize 60;SQL> run;  1* select * from employeeNAME           SALARY---------- ----------SMITH             800ALLEN            1600WARD             1250JONES            2975MARTIN           1250BLAKE            2850CLARK            2450SCOTT            3000KING             5000TURNER           1500ADAMS            1100JAMES             950FORD             3000MILLER           1300已选择14行。

在这个表如果SALARY列小于2500 就加20%。这个很简单，但是要用把游标用进去就要如下思考了：

先建个游标，遍历这个表在这个条件的数据。

 

SQL>  create or replace procedure emp_test  2  is  3   v_name employee.name%type;  4   v_sal employee.salary%type;  5   cursor cursor_sal is  6   select name,salary from employee where salary<2500;  7   begin  8   open cursor_sal ;  9   loop 10   fetch cursor_sal into v_name,v_sal; 11   exit when cursor_sal%notfound; 12   update employee set salary=salary*1.2 where name=v_name; 13   end loop; 14   close cursor_sal; 15   end; 16   /过程已创建。

SQL> set serveroutput on ;SQL> exec emp_test;PL/SQL 过程已成功完成。SQL> select * from employee;NAME           SALARY---------- ----------SMITH          1382.4ALLEN            1920WARD             1500JONES            2975MARTIN           1500BLAKE            2850CLARK            2940SCOTT            3000KING             5000TURNER           1800ADAMS            1320JAMES            1368FORD             3000MILLER           1560已选择14行。