Learning notes for PL/SQL script language cursor usage

PL/SQL script language cursor usage learning notes (1), cursor usage learning (a), cursor refers to the pointer in a result set, through this pointer movement, we can traverse the entire result set www.2cto.com (B). Use the cursor step (1) Declare the cursor (2) Open the cursor (3) process the data in the cursor (4) Close the cursor (c) the most common cursor attributes include % isopen and boolean, which indicate whether the cursor is opened. % Notfound, boolean Type Variable. If the latest fetch statement does not return a record, true is used. % Found, boolean Type Variable. If the latest fetch statement gets the record, true is used. % Rowcount, number type variable, used to represent the total number of rows from the current fetch to the record. (2) cursor Traversal method (1) Loop loop traversal the cursor uses the Loop loop and the % notfound attribute to traverse the cursor (2), While loop traversal cursor uses while loop with % found attribute to achieve cursor traversal (3), for loop traversal cursor uses for loop traversal cursor, no need to open or close the cursor, you don't even need to declare the loop variable. The simplest way is www.2cto.com -- to traverse the cursor declare with three loops -- to declare the cursor c_emp (v_deptno number) is select * from emp e where e. deptno = v_deptno; -- declare the row variable loop traversal of a cursor or while loop using c_emp_row c_emp % rowtype; begin/* -- loop traversal -- open the cursor open c_emp (10 ); -- Use cursor loop fetch c_emp Into c_emp_row; -- get the data exit when c_emp % notfound; values (c_emp_row.ename); end loop; -- close the cursor close c_emp; */values ('-------------------- for loop traversal cursor ------------------------'); -- for loop traversal cursor for emp_row in c_emp (10) loop dbms_output.put_line (emp_row.ename); end loop; dbms_output.put_line ('------------------- while loop traversal cursor -------------------------'); -- while loop traversal cursor open c_emp (10); Fetch c_emp into c_emp_row; -- get data while c_emp % found loop partition (c_emp_row.ename); fetch c_emp into c_emp_row; -- get data end loop; close c_emp; end; www.2cto.com (III) add for update after the select statement of the cursor update result set to prompt oracle to lock the record for update. Use where current of to indicate that the operation is added to the record pointed to by the current cursor. Example declare -- cursor c is select * from emp2 e for update; v_temp c % rowtype; begin for v_temp in c loop dbms_output.put_line (v_temp.sal); if (v_temp.sal <2000) then update emp2 set sal = 7000 where current of c; elsif (v_temp.sal = 5000) then delete from emp2 where current of c; end if; end loop; end;