游标1.docx
《游标1.docx》由会员分享，可在线阅读，更多相关《游标1.docx（14页珍藏版）》请在冰点文库上搜索。
游标1
1.什么是游标
为了处理SQL语句，ORACLE必须分配一片内存区域，这就是上下文区域（contextarea）。
上下文区域包含了完成该处理所必需的信息，其中包括语句要处理的行的数目、一个指向语句被分析后产生的表示形式的指针，以及查询的活动集（activeset，这是查询返回的行的集合）。
游标（cursor）就是一个指向上下文区域的句柄（handle）或指针。
通过游标，PL/SQL程序可以控制上下文区域和在处理语句时上下文区域会发生些什么事情。
2.显式游标
处理显式游标包括四个PL/SQL步骤
1）声明游标
2）为查询打开游标
3）将结果提取（fetch）到PL/SQL变量中
4）关闭游标
fetch语句有两种形式
1） fetchcursor_nameintolist_of_variables;
2） fetchcursor_nameintoPL/SQL_record;
这里cursor_name标识了已经被声明并且被打开的游标，list_of_variables是已经被声明的PL/SQL变量的列表（变量之间用逗号隔开），而PL/SQL_record是已经被声明的PL/SQL记录。
游标的四个属性
1）%FOUND 一个布尔属性。
如果前一个FETCH语句返回一个行，那么它就会返回TRUE，否则的话，它会返回FALSE。
如果在未打开游标以前就设置了%FOUND，那么会返回ORA-1001（无效的游标）。
2）%NOTFOUND 行为方式和上面的%FOUND正好相反。
如果前一个FETCH语句返回一个行，那么%NOTFOUND就会返回FALSE。
仅当前一个FETCH语句没有返回任何行，%NOTFOUND才会返回TRUE。
3）%ISOPEN 此布尔属性用来决定相关的游标是否被打开了。
如果被打开了则返回TRUE，否则返回FALSE。
4）%ROWCOUNT 此数字属性返回到目前为止由游标返回的行的数目。
如果在相关的游标还未打开的时候进行引用，那么会返回ORA-1001错误。
参数化游标
viewplaincopytoclipboardprint?
1.declare
2.
3.v_department classes.department%type;
4.
5.v_course classes.course%type;
6.
7.cursor c_classes is
8.
9.select * from classes
10.
11.where department=v_department
12.
13.and course=v_course;
14.
15.
16.
17.--上面这个游标包含了两个变量。
我们可以将它修改为一个等价的参数化游标。
18.
19.
20.declare
21.
22.cursor c_classes（p_department classes.department%type,p_course classes.course%type） is
23.
24.select * from classes
25.
26.where department=v_department
27.
28.and course=v_course;
29.
30.
31.--借助于参数化游标，OPEN语句可以用于将实际数值传递给游标。
32.
33.open c_classes（'HIS',101）;
3.隐式游标
显示游标用来处理返回多于一行的SELECT语句，我们在前面的章节已经看到这一点了。
但是，所有的SQL语句在上下文区域内部都是可执行的，因此都有一个游标指向此上下文区域。
此游标就是所谓的“SQL 游标”（SQLCURSOR）。
与显式游标不同的是，SQL游标不被程序打开和关闭。
PL/SQL隐含地打开SQL游标，处理其中的SQL语句，然后关闭该游标。
隐式游标用于处理INSERT、UPDATE、DELETE和单行的SELECT...INTO语句。
因为SQL游标是通过PL/SQL引擎打开和关闭的，所以OPEN、FETCH和CLOSE命令是无关的。
但是游标属性可以被应用于SQL游标。
viewplaincopytoclipboardprint?
1.--例如，下面的块在UPDATE语句没有找到任何行的时候就执行一条INSERT语句。
2.
3.begin
4.
5.update rooms
6.
7.set number_seats=100
8.
9.where room_id=99980;
10.
11.--如果UPDATE语句没有找到任何行的时候就执行一条INSERT语句
12.
13.if SQL%NOTFOUND then
14.
15.insert into rooms（room_id,number_seats）
16.
17.values（99980,100）;
18.
19.end if;
20.
21.end;
NO_DATA_FOUND和%NOTFOUND
NO_DATA_FOUND异常仅仅被SELECT...INTO语句所触发，当该查询的WHERE子句没有找到任何行的时候就会触发它。
当一个显式游标的WHERE子句没有找到行的时候，%NOTFOUND属性就被设置为TRUE。
如果UPDATE和DELETE语句的WHERE子句没有找到任何行的时候，SQL%NOTFOUND就被设置为TRUE，而不会触发NO_DATA_FOUND。
4.SELECTFORUPDATE游标
在多数情况下，提取循环中所完成的处理都会修改由游标检索出来的行。
PL/SQL提供了进行这样处理的一种方便语法。
这种方法包含两个部分--在游标声明部分的FORUPDATE子句和在UPDATE或DELETE语句中的WHERECURRENTOF子句
1）FORUPDATE
FORUPDATE子句是SELECT语句的一部分。
它是作为该语句的最后一个子句，在ORDERBY子句（如果有的话）的后面。
语法为：
SELECT...FROM...FORUPDATE[OFCOLUMN_REFERENCE][NOWAIT]
通常，SELECT操作不会对正在处理的行执行任何锁定设置，这使得连接到该数据库的其他会话可以改变正在选择的数据。
但是，结果集仍然是一致性的。
当确定了活动集以后，在执行OPEN的时刻，ORACLE会截取下该表的一个快照。
在此时刻以前所提交的任何更改操作都会在活动集中反映出来。
在此时刻以后所进行的任何更改操作，即使已经提交了它们，都不会被反映出来，除非将该游标重新打开（这会对结果集进行重新求值）。
这其实也就是读一致性处理（read-consistencyprocess）。
但是，如果使用了FORUPDATE 子句，那么在OPEN返回以前在活动集的相应行上会加上互斥锁（exclusivelock）。
这些锁会避免其他的会话对活动集中的行进行修改，直到整个的事务被提交为止。
如果另一个会话已经对活动集中的行加上了锁，那么SELECTFORUPDATE操作将等待其他会话释放这些锁以后才能继续进行自己的操作。
这种等待是没有超时限制的--SELECTFORUPDATE将无限期挂起，直到其他会话释放该锁。
如果要处理这种情形，就需要使用NOWAIT子句。
这时如果这些行被另一个会话锁定，那么OPEN将立即返回，同时会触发ORACLE错误:
ORA-54:
resourcebusyandacquirewithNOWAITspecified
在这种情况下，你可能想要稍后重试OPEN或者更改活动集以提取未被锁定的行。
2）WHERECURRENTOF
如果使用了WHERECURRENTOF子句声明了游标，那么可以在UPDATE和DELETE语句中使用WHERECURRENTOF子句。
这个子句的语法是：
WHERECURRENTOFcursor
这里cursor是使用FORUPDATE子句声明的游标的名字。
WHERECURRENTOF子句会求值算出刚刚被游标检索出的行。
viewplaincopytoclipboardprint?
1.--这个块将更新所有在HIS 101注册的学生的当前成绩
2.DECLARE
3. -- Number of credits to add to each student's total
4. v_NumCredits classes.num_credits%TYPE;
5.
6. -- This cursor will select only those students who are currently
7. -- registered for HIS 101.
8. CURSOR c_RegisteredStudents IS
9. SELECT *
10. FROM students
11. WHERE id IN （SELECT student_id
12. FROM registered_students
13. WHERE department= 'HIS'
14. AND course = 101）
15. FOR UPDATE OF current_credits;
16.
17.BEGIN
18. -- Set up the cursor fetch loop.
19. FOR v_StudentInfo IN c_RegisteredStudents LOOP
20. -- Determine the number of credits for HIS 101.
21. SELECT num_credits
22. INTO v_NumCredits
23. FROM classes
24. WHERE department = 'HIS'
25. AND course = 101;
26.
27. -- Update the row we just retrieved from the cursor.
28. UPDATE students
29. SET current_credits = current_credits + v_NumCredits
30. WHERE CURRENT OF c_RegisteredStudents;
31. END LOOP;
32.
33. -- Commit our work.
34. COMMIT;
35.END;
36./
请注意，UPDATE语句仅仅更新在游标声明的FORUPDATE子句处列出的列。
如果没有列出任何列，那么所有的列都可以被更新。
3）COMMIT和提取操作
我们可以注意到，在上面的例子中COMMIT是在提取循环完成以后完成的，因为COMMIT会释放由该会话持有的所有锁。
因为FORUPDATE子句获得了锁，所以COMMIT将释放这些锁。
当锁被释放的时候，该游标就无效了。
所有后继的操作都将返回ORACLE错误。
ORA-1002:
fetchoutofsequence
viewplaincopytoclipboardprint?
1.--这个例子就将引发这个错误
2.DECLARE
3. -- Cursor to retrieve all students, and lock the rows as well.
4. CURSOR c_AllStudents IS
5. SELECT *
6. FROM students
7. FOR UPDATE;
8.
9. -- Variable for retrieved data.
10. v_StudentInfo c_AllStudents%ROWTYPE;
11.BEGIN
12. -- Open the cursor. This will acquire the locks.
13. OPEN c_AllStudents;
14.
15. -- Retrieve the first record.
16. FETCH c_AllStudents INTO v_StudentInfo;
17.
18. -- Issue a COMMIT. This will release the locks, invalidating the
19. -- cursor.
20. COMMIT WORK;
21.
22. -- This FETCH will raise the ORA-1002 error.
23. FETCH c_AllStudents INTO v_StudentInfo;
24.END;
25./
这样，如果再SELECTFORUPDATE提取循环中有一个COMMIT语句，在COMMIT语句后面的任何提取操作都将是无效的。
所以我们不推荐在循环内部使用COMMIT语句。
如果游标没有被定义为一个SELECTFORUPDATE，就不会发生这个问题。
当然，如果你非要更新刚刚从游标中提取出来的行并且在提取循环内部使用COMMIT，该如何做呢？
WHERECURRENTOF不能用，因为游标不能使用FORUPDATE子句进行定义。
但是，你可以在UPDATE的WHERE子句中使用表的主键。
如下面这个例子所示
viewplaincopytoclipboardprint?
1.DECLARE
2. -- Number of credits to add to each student's total
3. v_NumCredits classes.num_credits%TYPE;
4.
5. -- This cursor will select only those students who are currently
6. -- registered for HIS 101.
7. CURSOR c_RegisteredStudents IS
8. SELECT *
9. FROM students
10. WHERE id IN （SELECT student_id
11. FROM registered_students
12. WHERE department= 'HIS'
13. AND course = 101）;
14.
15.BEGIN
16. -- Set up the cursor fetch loop.
17. FOR v_StudentInfo IN c_RegisteredStudents LOOP
18. -- Determine the number of credits for HIS 101.
19. SELECT num_credits
20. INTO v_NumCredits
21. FROM classes
22. WHERE department = 'HIS'
23. AND course = 101;
24.
25. -- Update the row we just retrieved from the cursor.
26. UPDATE students
27. SET current_credits = current_credits + v_NumCredits
28. WHERE id = v_Studentinfo.id;
29.
30. -- We can commit inside the loop, since the cursor is
31. -- not declared FOR UPDATE.
32. COMMIT;
33. END LOOP;
34.END;
35./
这个例子基本上模拟了WHERECURRENTOF子句，但是没有在活动集的行上创建锁。
5.游标变量
至此我们碰到的所有显式游标都是静态游标（staticcursor）--该游标与一个SQL语句相关联，并且在编译该块的时候此语句已经是可知的了。
另一方面，游标变量可以再运行时刻与不同的SQL语句相关联。
游标变量是一种引用类型。
定义一个游标变量类型的语法如下：
typetype_nameisrefcursorreturnreturn_type
这里type_name是新的引用类型的名字，return_type是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。
游标变量的返回类型必须是一个记录类型。
它可以被显式声明为一个用户定义的记录，或者隐式使用%ROWTYPE进行声明。
受限和不受限游标变量
在前面介绍的游标是受限的--它们仅被声明为特定的返回类型。
当稍后打开该变量时，必须为特定的查询打开它，使得该查询的选择列表匹配游标的返回类型，否则，会触发预定义错误ROWTYPE_MISMATCH。
而非受限的游标变量没有必要拥有RETURN子句，稍后打开一个非受限游标变量时，它可以为任何查询打开。
viewplaincopytoclipboardprint?
1.--非受限游标的例子
2.
3.CREATE OR REPLACE PROCEDURE ShowCursorVariable
4. /* Demonstrates the use of a cursor variable on the server.
5. If p_Table is 'classes', then information from the classes
6. table is inserted into temp_table. If p_Table is 'rooms'
7. then information from rooms is inserted. */
8. （p_Table IN VARCHAR2） AS
9.
10. /* Define the cursor variable type */
11. TYPE t_ClassesRooms IS REF CURSOR;
12.
13. /* and the variable itself. */
14. v_CursorVar t_ClassesRooms;
15.
16. /* Variables to hold the output. */
17. v_Department classes.department%TYPE;
18. v_Course classes.course%TYPE;
19. v_RoomID rooms.room_id%TYPE;
20. v_Description rooms.description%TYPE;
21.BEGIN
22. -- Based on the input parameter, open the cursor variable.
23. IF p_Table = 'classes' THEN
24. OPEN v_CursorVar FOR
25. SELECT department, course
26. FROM classes;
27. ELSIF p_table = 'rooms' THEN
28. OPEN v_CursorVar FOR
29. SELECT room_id, description
30. FROM rooms;
31. ELSE
32. /* Wrong value passed as input - raise an error */
33. RAISE_APPLICATION_ERROR（-20000,
34. 'Input must be ''classes'' or ''rooms'''）;
35. END IF;
36.
37. /* Fetch loop. Note the EXIT WHEN clause after the FETCH -
38. with PL/SQL 2.3 we can use cursor attributes with cursor
39. variables. */
40. LOOP
41. IF p_Table = 'classes' THEN
42. FETCH v_CursorVar INTO
43. v_Department, v_Course;
44. EXIT WHEN v_CursorVar%NOTFOUND;
45.
46. INSERT INTO temp_table （num_col, char_col）
47. VALUES （v_Course, v_Department）;
48. ELSE
49. FETCH v_CursorVar INTO
50. v_RoomID, v_Description;
51. EXIT WHEN v_CursorVAR%NOTFOUND;
52.
53. INSERT INTO temp_table （num
升级会员 