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

Understanding the cursor (2) Introduction to the attribute of the cursor and Case Study of Different cursor categories links: Understanding the cursor (1): Overview of the cursor http://www.bkjia.com/database/201212/177395.html PL/SQL provides six types of cursor attributes: The cursor attribute www.2cto.com Name Description Example % FOUND returns true if the record is obtained successfully; otherwise, returns false begin update t set name = 'F' where id = 2; if SQL % FOUND then dbms_output.put_line ('cursor attribute'); end if; end; % NOTFOUND: if the record is not successfully obtained, true is returned. Otherwise, false % FOUND and % NOTFOUND are returned. When should % NOTFOUND be used and % NOTFOUND be used? It depends on which attribute is most naturally expressed, for example: www.2cto.com exit when not SQL % foundexit when SQL % notfound obviously, the latter is more natural % ROWCOUNT returns until now, number of records retrieved from the cursor begin update t set name = 'F' where id = 2; dbms_output.put_line ('rows selected: '| SQL % rowcount); end; rows selected: 1% ISOPEN returns true if the cursor is open; otherwise, the returned value of false is usually used to ensure that the cursor is not always opened when an exception occurs, but the exception when others then if cursor_name % ISOPEN then close cursor_name; end if; % BULK_ROWCOUNT is returned and the forall statement is modified. The number of records % BULK_EXCEPTIONS returns the exception information when the forall statement modifies the record. We can use these cursor attributes in pl/SQL, but cannot use one cursor attribute in SQL statements, you only need to add % after the cursor name or variable. For example, for an implicit cursor, the name of cursor_name % attribute_name is "SQL ", for example, in SQL % FOUND, the t table is used as an example to briefly describe the use of various cursor categories [SQL] SQL> rollback; Rollback complete SQL> select * from t; id name ---------- -------------------- 1 a 2 B 3 c 4 d 5 e ① implicit cursor instance: [SQL] begin -- execute DML operation update t set name = 'ff 'where id = 5; -- determine if any image is affected Response line if SQL % found then -- print the number of affected rows dbms_output.put_line ('Affected rows: '| SQL % rowcount); end if; -- determine 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: [SQL] declare -- declare the cursor table variable and associate it with SQL cursor rowList is select * from t; -- declare the row variable -- 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; ③ an instance with an explicit parameter: [SQL] declare -- declares a parameter-based cursor variable and associates it with an SQL statement, and associate the parameter with SQL. 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 the cursor FOR loop, all are managed by PL/SQL engines. FOR oo in xx is equivalent to fetch xx into oo; [SQL] declare -- declare the cursor variable and associate SQL cursor rowList is select level a from dual connect by level <= 10; begin for rowValue in rowList loop -- rowValue indicates that each record does not need to be declared in advance. rowList indicates all records in the Set, dbms_output.put_line (rowValue. a); -- extract the values in the set to print the end loop; end; declare begin -- you can use the select statement for xx in, whether passing parameters or inserting More convenient and concise. 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 declare begin 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: [SQL] declare type cus_cur_type is ref cursor return t % rowtype; -- strong type Ref cursor, the queried SQL statement must return the 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 it with SQL loop fetch rowList into rowValue; -- extract data by row exit when rowList % notfound; -- determines whether a record exists. If the cursor dbms_output.put_line (rowVal Ue. 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;