第20章 使用游标（The twentieth chapter uses cursors）.docVIP

第20章 使用游标（The twentieth chapter uses cursors） The twentieth chapter use the *****************/ /**************** Batch bind data using the BULK COLLECT clause DECLARE TYPE, teacher_table_type, IS, TABLE, OF, t_teacher,%ROWTYPE; Teacher_table teacher_table_type; CURSOR cursor_teacher IS - declare cursors SELECT, teaName, Dept FROM t_teacher, WHERE deptID =t_10; BEGIN OPEN cursor_teacher; - open the cursor FETCH cursor_teacher BULK, COLLECT, INTO, teacher_table - - extract data using the BULK COLLECT clause CLOSE cursor_teacher - - closes the cursor FOR, I, IN, 1.., teacher_table.COUNT, LOOP - display teacher information DBMS_OUTPUT.PUT_LINE (teachers name: || teacher_table (I teaName). || College: || teacher_table (I) Dept); END LOOP; END; Using subquery in cursor ECLARE CURSOR cursor_teacher IS SELECT, teaID, teaName, age, sex, Dept, profession, salary FROM T_teacher WHERE salary ANY (SELECT salary FROM T_teacher WHERE dept = math department AND dept = = math department ORDER BY salary ASC BEGIN -- perform cursor action code END; An example of the%FOUND property BENGIN OPEN cursor_teacher; - open the cursor LOOP FETCH cursor_teacher - extract data INTO, v_teaID, v_teaName, IF cursor_teacher%FOUND THEN -- if the cursor results in the data in focus DBMS_OUTPUT.PUT_LINE (v_teaID|| || teacher: teachers name: v_ teaName ||); ELSE -- if the cursor does not have data in the result set EXIT - - quit the loop END IF; END LOOP; CLOSE cursor_teacher - - closes the cursor END; An example of the%NOTFOUND property BENGIN OPEN cursor_teacher; - open the cursor LOOP FETCH cursor_teacher - extract data INTO, v_teaID, v_teaName, EXIT WHEN cursor_teacher%NOTFOUND - - if the cursor results in no data in the result, then exit the loop DBMS_OUTPUT.PUT_LINE (v_teaID|| || teacher: teachers name: v_ teaName ||); END LOOP; CLOSE cursor_teacher - - closes the cursor END; An example of the%ROWCOUNT property BENGIN OPEN cursor_teacher; - open the cursor LOOP FETCH cursor_teacher - extract data