Understanding the cursor (2) Introduction to the attribute of the cursor and demonstration of different cursor categories

PL/SQL provides six types of cursor attributes:

Cursor property

Name	Description	Example
% Found	True is returned if the record is obtained successfully. Otherwise, false is returned.	Begin Update t set name = 'F' where id = 2; If SQL % found then Dbms_output.put_line ('cursor attribute '); End if; End;
% Notfound	True is returned if the record is not obtained successfully. Otherwise, false is returned.	% Found and % notfound are opposite attributes. When should % found be used and % notfound be used? It depends on which attribute is most natural to express, for example: Exit when not SQL % found Exit when SQL % notfound Obviously, the latter is more natural
% Rowcount	Returns the number of records that have been retrieved from the cursor so far.	Begin Update t set name = 'F' where id = 2; Dbms_output.put_line ('rows selected: '| SQL % rowcount ); End; Rows selected: 1
% Isopen	If the cursor is open, true is returned; otherwise, false is returned.	It is usually used to ensure that the cursor is not always open but not closed when an exception occurs. Exception When others then If cursor_name % isopen then Close cursor_name; End if;
% Bulk_rowcount	Returns the number of records modified by the forall statement.
% Bulk_exceptions	Returns the exception message when the forall statement modifies the record.

 

These cursor attributes can be used in PL/SQL, but cannot be used in SQL statements.

To use a cursor attribute, you only need to add % after the cursor name or cursor variable. For example: cursor_name % attribute_name
For an implicit cursor, the cursor name is always "SQL", such as SQL % found

 

The following uses table t as an example to describe the usage of various types of cursors.

SQL> rollback; Rollback complete SQL> select * from t;         ID NAME---------- --------------------         1 a         2 b         3 c         4 d         5 e

① Implicit cursor instance:

Begin -- execute the DML operation update t set name = 'ff 'Where id = 5; -- determine whether there are affected rows If SQL % found then -- print affected rows dbms_output.put_line (' affected rows: '| SQL % rowcount); end if; -- determines whether there are no affected rows. If SQL % notfound then dbms_output.put_line ('record with ID 5 does not exist'); end if; end;

② No explicit parameter cursor instance:

Declare -- declare the cursor table variables and associate SQL cursor rowlist is select * from t; -- declare the row variables -- if the preceding query statement has only one query field, the normal variable declaration method (v_rowvalue varchar2 (20);) can also be used here );). Rowvalue T % rowtype; begin open rowlist; -- open the cursor -- if it is determined that there is only one record in the cursor, loop and end loop can be left empty, and exit must exist in the cursor loop, therefore, you do not need to write. Loop fetch rowlist into rowvalue; -- put the value in the cursor into rowvalue exit when rowlist % notfound; -- determine whether there are still records. If there is no termination cursor dbms_output.put_line (rowvalue. name); -- print the obtained value. If there is only one field in the query, you only need to write the variable name here. End loop; close rowlist; -- close the cursor end;

③ Example of an explicit parameter cursor:

Declare -- declare the cursor variable with parameters and associate the SQL statement. Then, associate the parameter with the SQL statement. cursor rowlist (c_name varchar2, c_id number) is select * from t where T. name = c_name and T. id = c_id; -- declare the row variable -- if the preceding query statement has only one query field, you can use the normal variable declaration method (v_rowvalue varchar2 (20 );). Rowvalue T % rowtype; begin open rowlist ('think', 1); -- open the cursor and give the parameter -- if it is determined that there is only one record in the table, loop and end loop can be left empty, while exit must exist in the cursor loop, so no need to write. Loop fetch rowlist into rowvalue; -- put the value in the cursor into rowvalue exit when rowlist % notfound; -- determine whether there are still records. If there is no termination cursor dbms_output.put_line (rowvalue. name); -- print the value to. If there is only one field in the query, you only need to write the variable name here. End loop; close rowlist; -- close the cursor end;

④ Cursor for loop instance:
You do not need to manually open or manage a cursor for loop. All of them are managed by the PL/SQL engine.
For OO in XX is equivalent to fetch XX into Oo;

Declare -- declare the cursor variable and associate it with SQL cursor rowlist is select Level A from dual connect by level <= 10; begin for rowvalue in rowlist loop -- rowvalue is not required for each record, rowlist is all records in the Set dbms_output.put_line (rowvalue. a); -- extract the values in the set to print the end loop; end; declarebegin -- it is more convenient and concise to pass the parameters or nest the SELECT statement for XX in. For rowvalue in (select Level A from dual connect by level <= 10) loop for RV in (Select name from t where T. id = rowvalue. a) loop dbms_output.put_line (RV. name); -- print the obtained value. End loop; end; -- this is the simplest cursor declarebegin for rowvalue in 1 .. 10 loop dbms_output.put_line (rowvalue); -- print the obtained value. If there is only one field in the query, write the variable name here. End loop; end;

⑤ Ref cursor instance:

Declare type cus_cur_type is ref cursor return T % rowtype; -- strong type ref cursor, query SQL must return t table type -- type cus_cur_type is ref cursor; weak type ref cursor, the returned type does not limit rowlist cus_cur_type; -- declare the cursor variable rowvalue T % rowtype; -- declare the row variable begin open rowlist for select * from t; -- open the cursor, and associate SQL loop fetch rowlist into rowvalue; -- extract data by row exit when rowlist % notfound; -- determine whether there are still records. If there is no termination cursor dbms_output.put_line (rowvalue. name); -- print the obtained value. If the query has only one field Write the variable name. End loop; close rowlist; -- close the cursor end;