以下plsql程序用的scott用户的dept,emp表。
1.光标的使用:
1 --查询并打印员工的姓名名和薪水
2 /*
3 光标属性:
4 %found %notfound
5
6 */
7
8 set serveroutput on;
9
10 declare
11
12 CURSOR C1 IS SELECT ENAME,SAL FROM EMP;
13 PENAME EMP.ENAME%TYPE;
14 PSAL EMP.SAL%TYPE;
15
16 BEGIN
17
18 --打开光标
19 OPEN C1;
20
21 LOOP
22
23 --取一条记录,
24 FETCH C1 INTO PENAME,PSAL ;
25 EXIT WHEN C1%notfound;
26 --1.循环什么时候退出 2.FETCH一定能取到数据吗
27 DBMS_OUTPUT.PUT_LINE(PENAME||'的薪水是'||PSAL);
28 END LOOP;
29
30 --关闭光标
31 CLOSE C1;
32
33 END;
34 /
2.给员工涨工资
1 --给员工涨工资, 总裁涨1000 ,经理涨800 ,其他 400
2
3 set serveroutput on;
4
5 declare
6
7 cursor cemp is select empno,job from emp;
8 pempno emp.empno%type;
9 pjob emp.job%type;
10
11
12 begin
13
14 open cemp;
15 loop
16
17 fetch cemp into pempno,pjob ;
18 exit when cemp%notfound;
19
20 IF pjob = 'PRESIDENT' then update emp set sal= sal + 1000 where empno = pempno;
21 ELSIF pjob = 'MANAGER' then update emp set sal=sal+800 where empno = pempno;
22 ELSE update emp set sal=sal + 400 where empno = pempno;
23 END IF;
24
25 end loop;
26
27 close cemp;
28
29 commit;
30 --对应oracle ,默认的事务隔离级别是 read committed
31 --事务的ACID :原子性 一致性 隔离性 持久性
32 SYS.DBMS_OUTPUT.PUT_LINE('涨工资完成');
33
34
35
36 end;
37
38 /
3.操作带参数的光标
1 --查询某个部门中员工的姓名
2
3 set serveroutput on;
4
5 declare
6
7 cursor cemp(dno number) is select ename from emp where deptno = dno;
8 pename emp.ename%type;
9
10 begin
11 open cemp(10);
12 dbms_output.put_line('10号部门的人有:');
13 loop
14
15 fetch cemp into pename;
16 exit when cemp%notfound;
17 dbms_output.put_line(pename);
18
19 end loop;
20
21
22
23 close cemp;
24
25 end;
26
27 /
知识点出处: http://www.imooc.com/learn/360