oracle plsql cursor usage
一、游标的相关概念及特性
1.定义
2.游标的分类
3.游标使用的一般过程
4.游标的属性
(1)公共的属性
(2)额外的属性
二、隐式游标
1.隐式游标定义
2.隐式游标的属性
3.SELECT INTO时使用隐式游标
三、显式游标
1.显式游标的属性
2.显式游标的使用步骤
(1) 声明游标
(2)打开游标
(3)读取游标数据
(4)关闭游标
3.显式游标完整的使用示例
四、游标FOR循环
五、SELECT FOR UPDATE
1.定义
2.语法
3.使用例子
六、WHERE CURRENT OF 语句
1.语法
2.使用例子
一、游标的相关概念及特性
1.定义
映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求对该行进行相应特定的操作。
2.游标的分类
在Oracel中，游标可以分为两大类：静态游标 和 REF游标（动态游标）。REF游标是一种引用类型，类似于指针。而静态游标又分为显式游标和隐式游标两种。
3.游标使用的一般过程：
显式游标：声明--->打开--->读取--->关闭。
隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的。
4.游标的属性：
(1)公共的属性：%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT。
%FOUND 布尔型属性，返回TRUE如果INSERT或UPDATE DELETE语句影响了一行或多行或SELECT INTO语句返回一行或多行;否则，返回FALSE。
%NOTFOUND 布尔型属性，与%FOUND相反。返回TRUE，如果INSERT或UPDATE DELETE语句没有影响任何行，或SELECT INTO语句没有返回任何行;否则，返回FALSE
%ISOPEN 布尔型属性，判断游标的状态，当游标已经打开时返回TRUE，游标关闭时则返回FALSE。隐式游标总是返回FALSE，因为系统在执行后自动关闭游标。
%ROWCOUNT 数字型属性，返回受SQL影响的行数。
注：当使用隐式游标的属性时，需要在属性前加上SQL。因为Oracle在创建隐式游标时，默认的游标名为SQL。比如：SQL%NOTFOUND;
(2)额外的属性：%BULK_ROWCOUNT，%BULK_EXCEPTIONS。
%BULK_ROWCOUNT被用于FORALL语句。此属性的第i个元素存储 FORALL LOOP中第 i个执行中的处理的行数UPDATE或DELETE语句。如果第 i个执行不会影响任何行%BULK_ROWCOUNT(i)返回零。
%BULK_EXCEPTIONS被用于FORALL语句。此属性存储FORALL
LOOP中第i个执行的异常，%BULK_EXCEPTIONS(i).ERROR_CODE对应错误代
码，%BULK_EXCEPTIONS(i).ERROR_INDEX对应错误信息。
注：
1.不能比较两个游标变量是否相等或者不等。（cv1 emps_rc; cv2 emps_rc; IF cv1 = cv2）
2.CURSOR 类型不能够用于COLLECTION。（TYPE cvs_t IS TABLE OF SYS_REFCURSOR;）
3.基于REF CURSOR的游标变量无法在PACKAGE的级别被定义，只能够定义在过程,函数,匿名块或触发器里面。（CREATE OR REPLACE PACKAGE plch_pkg IS g_cursor SYS_REFCURSOR; END;）
二、隐式游标
1.隐式游标定义
隐式游标由系统自动定义，其过程由oracle控制，完全自动化。比如当DML被使用时，Oracle为每一个不属于显式游标的DML语句都创建一个隐式
游标，其声明、打开、关闭都是系统自动进行。另外，隐式游标默认的名称是SQL，不能对SQL游标显式地执行OPEN,FETCH,CLOSE语句。
2.隐式游标的属性
类似于显式游标，隐式游标同样具有四种属性，只不过隐式游标以SQL%开头，而显示游标以Cursor_name%开头。
并且，通过SQL%总是只能访问前一个DML操作或单行SELECT操作的游标属性，用于判断DML执行的状态和结果，进而控制程序的流程。
SQL%ISOPEN
游标是否打开。当执行select into、insert、update、delete这些DML操作时，Oracle会隐含地打开游标，且在该语句执行完毕或隐含地关闭游标。
因为是隐式游标，故SQL%ISOPEN总是FALSE。
SQL%FOUND
判断SQL语句是否成功执行。当有作用行时则成功执行为TRUE，否则为FALSE。
SQL%NOTFOUND
判断SQL语句是否成功执行。当有作用行时否其值为FALSE，否则其值为TRUE。
SQL%ROWCOUNT
在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL。
对于SELECTINTO语句，如果执行成功，SQL%ROWCOUNT的值为1；
如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND。
3.SELECT INTO时使用隐式游标
SELECT INTO用于将单行结果集放置到变量之中。SELECT INTO处理的结果包括三种情况
查询结果返回单行，SELECT INTO被成功执行；
查询结果没有返回行，PL/SQL将抛出no_data_found异常；
查询结果返回多行，PL/SQL将抛出too_many_rows 异常。
对于上述两种异常发生时，类似于普通异常处理，程序控制权转移到异常处理部分(如没有异常处理则程序中断)。对于异常被激后发，SQL游标的四个属性在此将不可使用，如下面的例子：
1 DECLARE
2 v_ename emp.ename%TYPE;
3 BEGIN
4 SELECT ename INTO v_ename FROM emp WHERE empno=&no;
5 IF SQL%ROWCOUNT=0 OR SQL%NOTFOUND THEN
6 DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );
9 END IF;
10 EXCEPTION
11 WHEN NO_DATA_FOUND THEN
12 DBMS_OUTPUT.PUT_LINE('No data found for '||&no);
13 END;
14 操作及结果：
15 Enter value for no:70
16 No data found for 70
17
18 Enter value for no:7788
19 The name for record 7788 is SCOTT
从上面的演示中可以看到，当select into没有返回行时，IF SQL%ROWCOUNT=0OR SQL%NOTFOUND THEN 语句并没有被执行。
使用下面改进过的代码来执行，即可以将SQL游标属性判断放置到EXCEPTION部分。
1 DECLARE
2 v_ename emp.ename%TYPE;
3 BEGIN
4 SELECT ename INTO v_ename FROM emp WHERE empno=&no;
5 IF SQL%NOTFOUND THEN
6 DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );
9 END IF;
10 EXCEPTION
11 WHEN NO_DATA_FOUND THEN
12 IF SQL%NOTFOUND THEN
13 DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');
14 DBMS_OUTPUT.PUT_LINE('No data found for '||&no);
15 ELSE
16 DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );
17 END IF;
18 END;
19 操作及结果：
20 Enter value for no:80
21 The record 80 is not exist!
22 No data found for 80
三、显式游标
1.显式游标的属性
显式游标的也是4个属性（%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT），与隐式游标的相同。
2.显式游标的使用步骤：声明(定义)--->打开--->读取--->关闭。
(1) 声明游标
格式如下：
1 CURSOR cursor_name [(parameter[, parameter]...)]
2 [RETURN return_type]
3 IS select_statement
4 例子：
5 DECLARE
6 CURSOR emp_cursor(department IN number2 DEFAULT 20)
7 return emp%rowtype
8 IS
9 SELECT *
10 FROM emp
11 WHERE deptno=department;
(2)打开游标
格式：OPEN cursor_name[(VALUE[,VALUE]...)];
例子：OPEN emp_cursor;
(3)读取游标数据
格式：FETCH cursor_name INTO { variable_list | record_variable };
例子：FETCH emp_cursor INTO emp_row;
注：游标使用的参数在执行中变化并不影响结果。如下例中：即使 factor一直在增加，但salary一直是乘2。
1 DECLARE
2 my_sal employees.salary%TYPE;
3 my_job employees.job_id%TYPE;
4 factor INTEGER := 2;
5 CURSOR c1 IS
6 SELECT factor*salary FROM employees WHERE job_id = my_job;
7 BEGIN
8 OPEN c1; -- factor initially equals 2
9 LOOP
10 FETCH c1 INTO my_sal;
11 EXIT WHEN c1%NOTFOUND;
12 factor := factor + 1; -- does not affect FETCH
13 END LOOP;
14 CLOSe c1;
15 END;
16 /
(4)关闭游标
格式：CLOSE cursor_name;
例子：CLOSE emp_cursor;
1 --下面的程序将显示声明一个游标，并在循环中使用SELECT语句提取所有部门为30的员工信息。
2 DECLARE
3 CURSOR emp_cursor(department IN NUMBER DEFAULT 20) IS
4 SELECT empno, ename, job, sal
5 FROM emp
6 WHERE deptno = department;
7
8 TYPE employee IS RECORD(
9 id emp.empno%TYPE,
10 NAME emp.ename%TYPE,
11 job emp.job%TYPE,
12 sal emp.sal%TYPE);
13 emp_row employee;
14 BEGIN
15 OPEN emp_cursor(30);
16 FETCH emp_cursor INTO emp_row;
17 WHILE emp_cursor%FOUND LOOP
18 dbms_output.put('员工编号 ' || emp_row.id);
19 dbms_output.put(' 姓名 ' || emp_row.name);
20 dbms_output.put(' 职位 ' || emp_row.job);
21 dbms_output.put(' 薪金 ' || emp_row.sal);
22 --填充下一条记录
23 FETCH emp_cursor INTO emp_row;
24 END LOOP;
25 CLOSE emp_cursor;
26 END;
在大多数时候我们在自定义显示游标的时候都遵循下面的步骤：
1、打开游标
2、开始循环
3、从游标中取值
4、检查那一行被返回
5、处理
6、关闭循环
7、关闭游标
可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在
于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR 循环的语法如下:
1 FOR record_index in cursor_name
2 LOOP
3 {...statements...}
4 END LOOP;
5 使用例子：
6 CREATE OR REPLACE Function TotalIncome
7 ( name_in IN varchar2 )
8 RETURN varchar2
9 IS
10 total_val number(6);
11 cursor c1 is
12 SELECT monthly_income
13 FROM employees
14 WHERE name = name_in;
15 BEGIN
16 total_val := 0;
17 FOR employee_rec in c1
18 LOOP
19 total_val := total_val + employee_rec.monthly_income;
20 END LOOP;
21 RETURN total_val;
22 END;
五、SELECT FOR UPDATE
1.定义
SELECT FOR UPDATE 语句可以锁住游标结果集。直到下一次 COMMIT 或ROLLBACK 操作执行后，才解锁游标。
2.语法
1 CURSOR cursor_name
2 IS
3 select_statement
4 FOR UPDATE [OF column_list] [NOWAIT];
5
6 cursor_name: 游标名。
7 select_statement: SELECT 查询语句。
8 column_list: 游标结果集中想要更新的字段。
9 NOWAIT: 选择游标是否等待资源。
1 CURSOR c1
2 IS
3 SELECT course_number, instructor
4 FROM courses_tbl
5 FOR UPDATE OF instructor;
6 注：可以使用 WHERE CURRENT OF 语句 来更新或删除被 SELECT FOR UPDATE 锁住的数据。
1 UPDATE table_name
2 SET set_clause
3 WHERE CURRENT OF cursor_name;
4 或
5 DELETE FROM table_name
6 WHERE CURRENT OF cursor_name;
注: WHERE CURRENT OF 语句只更新或删除最后被游标 FETCH 到的记录。
2.使用例子
1 (1)Updating using the WHERE CURRENT OF Statement
2 CREATE OR REPLACE Function FindCourse
3 ( name_in IN varchar2 )
4 RETURN number
5 IS
6 cnumber number;
7 CURSOR c1
8 IS
9 SELECT course_number
10 FROM courses_tbl
11 WHERE course_name = name_in
12 FOR UPDATE of instructor;
13 BEGIN
14 OPEN c1;
15 FETCH c1 INTO cnumber;
16 if c1%notfound then
17 cnumber := 9999;
18 else
19 UPDATE courses_tbl
20 SET instructor = 'SMITH'
21 WHERE CURRENT OF c1;
22 COMMIT;
23 end if;
24 CLOSE c1;
25 RETURN cnumber;
26 END;
27
28 (2)Deleting using the WHERE CURRENT OF Statement
29 CREATE OR REPLACE Function FindCourse
30 ( name_in IN varchar2 )
31 RETURN number
32 IS
33 cnumber number;
34 CURSOR c1
35 IS
36 SELECT course_number
37 from courses_tbl
38 where course_name = name_in
39 FOR UPDATE of instructor;
40 BEGIN
41 open c1;
42 fetch c1 into cnumber;
43 if c1%notfound then
44 cnumber := 9999;
45 else
46 DELETE FROM courses_tbl
47 WHERE CURRENT OF c1;
48 COMMIT;
49 end if;
50 close c1;
51 RETURN cnumber;
52 END;