游 标.docx

1、游 标游 标 隐式游标SQL - update add emp sal 10% in deptno=10SQL declare 2 l_deptno t_emp.deptno%type; 3 begin 4 l_deptno := &no; 5 update t_emp set sal=sal*1.1 where deptno=l_deptno; 6 if sql%found then 7 dbms_output.put_line(sql%rowcount|emp updated!); 8 end if; 9 if sql%notfound then 10 dbms_output.put_li

2、ne(updated failer!); 11 end if; 12 if sql%isopen then 13 dbms_output.put_line(is open!); 14 else 15 dbms_output.put_line(is closed!); 16 end if; 17 end; 18 /输入 no 的值: 10原值 4: l_deptno := &no;新值 4: l_deptno := 10;3emp updated!is closed!PL/SQL 过程已成功完成。SQL /输入 no 的值: 20原值 4: l_deptno := &no;新值 4: l_dep

3、tno := 20;5emp updated!is closed!PL/SQL 过程已成功完成。SQL /输入 no 的值: 30原值 4: l_deptno := &no;新值 4: l_deptno := 30;6emp updated!is closed!PL/SQL 过程已成功完成。SQL /输入 no 的值: 40原值 4: l_deptno := &no;新值 4: l_deptno := 40;updated failer!is closed!PL/SQL 过程已成功完成。 显示游标不带参游标：-显示部门10的员工信息： 1 declare 2 -l_deptno t_emp.d

4、eptno%type; 3 l_emp t_emp%rowtype; 4 cursor cur_emp is select * from t_emp where deptno=10; 5 begin 6 open cur_emp; 7 fetch cur_emp into l_emp; 8 dbms_output.put_line(l_emp.ename| | 9 l_emp.sal| | 10 l_emp.job| | 11 l_emp.deptno); 12 fetch cur_emp into l_emp; 13 dbms_output.put_line(l_emp.ename| | 1

5、4 l_emp.sal| | 15 l_emp.job| | 16 l_emp.deptno); 17 close cur_emp; 18* end;SQL /CLARK 2695 MANAGER 10KING 5500 PRESIDENT 10PL/SQL 过程已成功完成。*用loop循环： 1 declare 2 l_emp t_emp%rowtype; 3 cursor cur_emp is select * from t_emp where deptno=10; 4 begin 5 open cur_emp; 6 loop 7 fetch cur_emp into l_emp; 8 e

6、xit when cur_emp%notfound; 9 dbms_output.put_line(l_emp.ename| | 10 l_emp.sal| | 11 l_emp.job| | 12 l_emp.deptno); 13 end loop; 14 close cur_emp; 15* end;SQL /CLARK 2695 MANAGER 10KING 5500 PRESIDENT 10MILLER 1430 CLERK 10PL/SQL 过程已成功完成。*用while循环：SQL declare 2 l_emp t_emp%rowtype; 3 cursor cur_emp i

7、s select *from t_emp where deptno=10; 4 begin 5 open cur_emp; 6 fetch cur_emp into l_emp; 7 while cur_emp%found loop 8 dbms_output.put_line(l_emp.ename| | 9 l_emp.sal| | 10 l_emp.job| | 11 l_emp.deptno); 12 fetch cur_emp into l_emp; 13 end loop; 14 close cur_emp; 15 end; 16 /CLARK 2450 MANAGER 10KIN

8、G 5000 PRESIDENT 10MILLER 1300 CLERK 10PL/SQL 过程已成功完成。带参游标：-显示各部门的员工信息： 1 declare 2 l_deptno t_emp.deptno%type; 3 l_emp t_emp%rowtype; 4 cursor cur_emp(v_deptno t_emp.deptno%type) 5 is 6 select *from t_emp where deptno=v_deptno; 7 begin 8 l_deptno := &no; 9 open cur_emp(l_deptno); 10 fetch cur_emp i

9、nto l_emp; 11 while cur_emp%found loop 12 dbms_output.put_line(l_emp.ename| | 13 l_emp.sal| | 14 l_emp.job| | 15 l_emp.deptno); 16 fetch cur_emp into l_emp; 17 end loop; 18 close cur_emp; 19* end;SQL /输入 no 的值: 10原值 8: l_deptno := &no;新值 8: l_deptno := 10;CLARK 2450 MANAGER 10KING 5000 PRESIDENT 10M

10、ILLER 1300 CLERK 10PL/SQL 过程已成功完成。SQL /输入 no 的值: 20原值 8: l_deptno := &no;新值 8: l_deptno := 20;SMITH 800 CLERK 20JONES 2975 MANAGER 20SCOTT 3000 ANALYST 20ADAMS 1100 CLERK 20FORD 3000 ANALYST 20PL/SQL 过程已成功完成。*-显示sal2000,deptno=20的员工信息： 1 declare 2 l_deptno t_emp.deptno%type; 3 l_sal t_emp.sal%type;

11、4 l_emp t_emp%rowtype; 5 cursor cur_emp(v_deptno t_emp.deptno%type,v_sal t_emp.sal%type) 6 is 7 select *from t_emp where deptno=v_deptno and salv_sal; 8 begin 9 l_deptno := &no; 10 l_sal := &sal; 11 open cur_emp(l_deptno,l_sal); 12 loop 13 fetch cur_emp into l_emp; 14 exit when cur_emp%notfound; 15

12、dbms_output.put_line(l_emp.ename| | 16 l_emp.sal| | 17 l_emp.deptno); 18 end loop; 19 close cur_emp; 20* end;SQL /输入 no 的值: 20原值 9: l_deptno := &no;新值 9: l_deptno := 20;输入 sal 的值: 2000原值 10: l_sal := &sal;新值 10: l_sal := 2000;JONES 2975 20SCOTT 3000 20FORD 3000 20PL/SQL 过程已成功完成。*只做查询的可以用for循环：-查员工信息

13、：SQL declare 2 cursor cur_emp is select *from t_emp; 3 begin 4 for cur in cur_emp loop 5 dbms_output.put_line(cur.ename| | 6 cur.sal| | 7 cur.deptno); 8 end loop; 9 end; 10 /SMITH 800 20ALLEN 1600 30WARD 1250 30JONES 2975 20MARTIN 1250 30BLAKE 2850 30CLARK 2450 10SCOTT 3000 20KING 5000 10TURNER 1500

14、 30ADAMS 1100 20JAMES 950 30FORD 3000 20MILLER 1300 10PL/SQL 过程已成功完成。*8* 1 declare 2 cursor cur_emp is select *from t_emp where deptno=10; 3 begin 4 for cur in cur_emp loop 5 dbms_output.put_line(cur.ename| |cur.sal); 6 end loop; 7* end;SQL /CLARK 1984.5KING 4050MILLER 1053* 1 declare 2 l_deptno t_e

15、mp.deptno%type; 3 cursor cur_emp 4 is 5 select *from t_emp where deptno=l_deptno; 6 begin 7 l_deptno := &no; 8 for cur in cur_emp loop 9 dbms_output.put_line(cur.ename| |cur.sal); 10 end loop; 11* end;SQL /输入 no 的值: 10原值 7: l_deptno := &no;新值 7: l_deptno := 10;CLARK 1984.5KING 4050MILLER 1053PL/SQL

16、过程已成功完成。SQL /输入 no 的值: 20原值 7: l_deptno := &no;新值 7: l_deptno := 20;SMITH 800JONES 2975SCOTT 3000ADAMS 1100FORD 3000PL/SQL 过程已成功完成。 Ref游标（引用游标reference cursor）强引用游标：（一般建议多用强引用游标，少用弱引用游标！）-可以显示各部门的员工信息： 1 declare 2 l_emp t_emp%rowtype; 3 type ref_cur_emp is ref cursor return t_emp%rowtype; 4 cur_emp

17、ref_cur_emp; 5 begin 6 open cur_emp for select *from t_emp where deptno=&no; 7 loop 8 fetch cur_emp into l_emp; 9 exit when cur_emp%notfound; 10 dbms_output.put_line(l_emp.ename| | 11 l_emp.sal| | 12 l_emp.deptno); 13 end loop; 14 close cur_emp; 15* end;SQL /输入 no 的值: 10原值 6: open cur_emp for select

18、 *from t_emp where deptno=&no;新值 6: open cur_emp for select *from t_emp where deptno=10;CLARK 2450 10KING 5000 10MILLER 1300 10PL/SQL 过程已成功完成。SQL /输入 no 的值: 20原值 6: open cur_emp for select *from t_emp where deptno=&no;新值 6: open cur_emp for select *from t_emp where deptno=20;SMITH 800 20JONES 2975 2

19、0SCOTT 3000 20ADAMS 1100 20FORD 3000 20PL/SQL 过程已成功完成。*弱引用游标： 1 declare 2 l_emp t_emp%rowtype; 3 type ref_cur_emp is ref cursor; 4 cur_emp ref_cur_emp; 5 begin 6 open cur_emp for select *from t_emp where deptno=&no; 7 loop 8 fetch cur_emp into l_emp; 9 exit when cur_emp%notfound; 10 dbms_output.put_

20、line(l_emp.ename| | 11 l_emp.sal| | 12 l_emp.deptno); 13 end loop; 14 close cur_emp; 15* end;SQL /输入 no 的值: 10原值 6: open cur_emp for select *from t_emp where deptno=&no;新值 6: open cur_emp for select *from t_emp where deptno=10;CLARK 2450 10KING 5000 10MILLER 1300 10PL/SQL 过程已成功完成。SQL /输入 no 的值: 20原值

21、 6: open cur_emp for select *from t_emp where deptno=&no;新值 6: open cur_emp for select *from t_emp where deptno=20;SMITH 800 20JONES 2975 20SCOTT 3000 20ADAMS 1100 20FORD 3000 20PL/SQL 过程已成功完成。*案例：-查询员工各部门平均工资，最大工资，最小工资等等： 1 declare 2 type rec_static_emp is record( 3 deptno t_emp.deptno%type, 4 sums

22、al number(8), 5 avgsal number(10,4), 6 maxsal t_emp.sal%type, 7 minsal t_emp.sal%type, 8 counter number(3); 9 l_stat rec_static_emp; 10 type ref_stat_emp is ref cursor return rec_static_emp; 11 cur_stat_emp ref_stat_emp; 12 begin 13 open cur_stat_emp for 14 select deptno,sum(sal),avg(sal),max(sal),m

23、in(sal),count(*) 15 from t_emp 16 group by deptno; 17 loop 18 fetch cur_stat_emp into l_stat; 19 exit when cur_stat_emp%notfound; 20 dbms_output.put_line(l_stat.deptno| | 21 l_stat.sumsal| | 22 l_stat.avgsal| | 23 l_stat.maxsal| | 24 l_stat.minsal| | 25 l_stat.counter); 26 end loop; 27 close cur_sta

24、t_emp; 28* end;SQL /30 9400 1566.6667 2850 950 620 10875 2175 3000 800 510 7088 2362.5 4050 1053 3PL/SQL 过程已成功完成。综合案例：-对大于2000的员工进行提薪10%，并将提薪的信息写入日志表： 1 declare 2 l_sal t_emp.sal%type; 3 l_rate number(4,2); 4 l_oldsal t_emp.sal%type; 5 l_emp t_emp%rowtype; 6 cursor cur_emp(v_sal t_emp.sal%type) 7 is

25、 8 select *from t_emp where salv_sal; 9 begin 10 l_sal := &sal; 11 l_rate := &rate; 12 open cur_emp(l_sal); 13 loop 14 fetch cur_emp into l_emp; 15 l_oldsal := l_emp.sal; 16 exit when cur_emp%notfound; 17 update t_emp set sal=sal*l_rate where empno=l_emp.empno; 18 insert into t_log values(seq_log.ne

26、xtval, 19 l_emp.empno|old sal=|l_oldsal| 20 ,newsal=|l_emp.sal*l_rate,sysdate); 21 end loop; 22 close cur_emp; 23 commit; 24* end;SQL /输入 sal 的值: 2000原值 10: l_sal := &sal;新值 10: l_sal := 2000;输入 rate 的值: 1.5原值 11: l_rate := &rate;新值 11: l_rate := 1.5;PL/SQL 过程已成功完成。SQL select *from t_log; ID LOGINFO

27、 TIMER- - - 1 7566old sal=2975,newsal=2975 16-8月 -13 2 7698old sal=2850,newsal=2850 16-8月 -13 3 7788old sal=3000,newsal=3000 16-8月 -13 4 7839old sal=4050,newsal=4050 16-8月 -13 5 7902old sal=3000,newsal=3000 16-8月 -13 6 7566old sal=3272.5,newsal=3272.5 16-8月 -13 7 7698old sal=3135,newsal=3135 16-8月 -13 8 7788old sal=3300,newsal=3300 16-8月 -13 9 7839old sal=4455,newsal=4455 16-8月 -13 10 7902old sal=3300,newsal=3300 16-8月 -13 11 7566old sal=3599.75,newsal=3959.725 16-8月 -13 ID LOGINFO TIMER- - -