Understanding cursor (3) Use of implicit cursor

Understanding cursors (3) Use of implicit cursors links: Understanding cursors (1): cursors Overview http://www.bkjia.com/database/201212/177395.html Understanding the cursor (2) Introduction to the attribute of the cursor and demonstration of different cursor categories http://www.bkjia.com/database/201212/178978.html When we execute DML or Select, the PL/SQL engine will declare an implicit cursor for us and manage this cursor. The reason is "implicit" because the workflow database related to the cursor has automatically completed for us to use the implicit cursor, actually, it is expected to return a row. Here there is a principle: www.2cto.com for single-row record query, we should always encapsulate it into a function in the package and hide this query behind a function interface, then return data using the return statement implicit cursor is a select statement with the following special properties: ① this select statement appears in the execution unit of the code block, the display cursor is defined in the Declaration unit. ② the query has an into Statement (bulk collect into for Batch Processing). ③ we do not need to open, extract, or close the SELECT statement, all these operations are the general structure of implicit queries performed by the database for us: SELECT column_list [bulk collect] into pl/SQL variable_list although the database automatically opens, extracts, and closes for us, however, we can still obtain information about the recently executed SQL statement through the property value of the implicit cursor www.2cto.com (I) Implicit cursor example. The common usage of the implicit cursor is to search based on the primary key. The following example uses the query to query a row. information is retrieved from a record table: customers primary key: cust_id [SQL] DECLARE l_customer customers % rowtype; BEGIN SELECT * INTO l_customer FROM customers WHERE cust_id = 1; END; for such single row queries, according to the above principle, we usually hide them behind the function interface. If we want to obtain more information than one row, we must query or use the display cursor, alternatively, you can use the bulk collect into Statement (II) to handle implicit cursor exceptions: ① NO_DATA_FOUND: no matching row found ② TOO_MANY_ROWS: more than one row of returned results is used as a rule, we always need to include NO_DATA_FOUND and TOO_MANY_ROWS Exception Handling handle www.2cto.com [SQL] FUNCTION book_title (isbn_in IN books. isbn % TYPE) RETURN books. title % type is return_value books. title % TYPE; begin select title INTO return_value FROM books WHERE isbn = isbn_in; RETURN return_value; exception when NO_DATA_FOUND then return null; WHEN TOO_MANY_ROWS THEN errpkg. record_and_stop ('data error: '| isbn_in); RAISE; END; (3) Implicit SQL cursor attribute. We can access the recently executed SQL information through the cursor attribute.
If the session has not executed an implicit cursor, all the implicit cursor attributes will return NULL. Otherwise, the returned value is the attribute value of the last executed SQL statement, regardless of the code block or program in which the SQL statement is executed, check whether the attribute value of the correct SQL statement is true, we should immediately Save the attribute value to the local variable www.2cto.com [SQL] PROCEDURE show_book_count IS l_count pls_integer; l_numfound pls_integer; BEGIN SELECT count (*) INTO l_count FROM books -- take a snapshot of the property value Rochelle numfound: = SQL % ROWCOUNT; update books set .... DBMS_OUTPUT.PUTLINE (l_numfound); END;