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行。
