Four steps for displaying the cursor in PLSQL

Four steps for displaying the cursor in PLSQL this example briefly introduces the locking and consistent read problems involved in the four processes of the cursor processing. explicit cursor processing requires four PL/SQL steps: www.2cto.com 1 definition/Declaration cursor: defines a cursor name and the corresponding SELECT statement. Format: [SQL] CURSOR cursor_name [(parameter [, parameter]…)] [RETURN datatype] IS select_statement;
The cursor parameter can only be an input parameter, IN the format of parameter_name [IN] datatype [{: = | DEFAULT} expression]
Length constraints are not allowed when you specify a data type. For example, NUMBER (4) and CHAR (10) are all incorrect. [RETURN datatype] is optional, indicating the data returned by the cursor. If this option is selected, it should strictly match the selection list in select_statement in order and data type. It is generally a record of data type or data with "% ROWTYPE.
2. Open the cursor: Execute the SELECT statement corresponding to the cursor, place the query result into the workspace, and point the pointer to the first of the workspace to identify the cursor result set. If the cursor query statement contains the for update option, the OPEN statement also locks the data rows corresponding to the cursor result set in the database table. Format: OPEN cursor_name [([parameter =>] value [, [parameter =>] value]…)]; When passing parameters to a cursor, you can use the same value passing method as the function parameter, that is, location representation and name representation. The PL/SQL program cannot OPEN a cursor again with an OPEN statement.
3. Extract cursor data: it is the data row in the search result set and placed in the specified output variable. Format: www.2cto.com FETCH cursor_name INTO {variable_list | record_variable}; when the FETCH statement is executed, each time a data row is returned, the cursor is automatically moved to the next data row. When the last row of data is retrieved, if the FETCH statement is executed again, the operation fails and the cursor attribute % NOTFOUND is set to TRUE. Therefore, after each execution of the FETCH statement, check the cursor attribute % NOTFOUND to determine whether the FETCH statement is successfully executed and return a data row to determine whether a value is assigned to the corresponding variable.
4. Close the cursor: After the cursor result set data is extracted and processed, the cursor should be closed in time to release the system resources occupied by the cursor and make the workspace of the cursor invalid, you cannot use the FETCH statement to retrieve the data. The closed cursor can be reopened using the OPEN statement. Format: CLOSE cursor_name; an example is provided below:
[SQL] CREATE OR REPLACE PROCEDURE testcur IS CURSOR c_cursor (r_no NUMBER DEFAULT 5) IS SELECT ISBN, TITLE FROM BOOKS WHERE rownum <r_no FOR UPDATE; v_isbn BOOKS. ISBN % TYPE; v_title BOOKS. TITLE % TYPE; BEGIN -- OPEN c_cursor (5); OPEN c_cursor (r_no => 7); DBMS_OUTPUT.PUT_LINE ('opened cursor '); -- afater open -- DBMS_LOCK.SLEEP (30 ); www.2cto.com FETCH c_cursor INTO v_isbn, v_title; DBMS_OUTPUT.PUT_LINE ('fetched cursor '); -- DBMS_LOCK.SLEEP (30 ); WHILE c_cursor % found loop DBMS_OUTPUT.PUT_LINE (v_isbn | '---' | to_char (v_title); FETCH c_cursor INTO v_isbn, v_title; end loop; CLOSE c_cursor; DBMS_OUTPUT.PUT_LINE ('closed cursor '); ROLLBACK; DBMS_OUTPUT.PUT_LINE ('ended transaction'); END testcur;
When you define or declare a cursor, you only associate the SQL statement with the cursor. When the cursor is open, the query relationship is with the table. If the for update statement is used, a lock is applied. In this example, after opening c_cursor, stop '-- afater open' in debug mode ', you can use select * from v $ lock where sid in (select distinct sid from v $ session where username = 'plsql') to view the locking record. in this example, the record is logged on as a PLSQL user. in the fetch process, Oracle will ensure consistent read. You can remove For update, update and submit data in another session during fetch. you can refer to my other blog http://www.bkjia.com/database/201206/136830.htmlto simulate this process. Close the cursor will not release the lock obtained for update, and it will be released only after the transaction ends. in addition, I first want to use DBMS_OUTPUT.PUT_LINE and DBMS_LOCK.SLEEP to observe the locking and release locks. The result is that DBMS_OUTPUT.PUT_LINE is output only after the entire stored procedure is executed, later, we had to observe it through debug in PL/SQL Developer. You should pay attention to it.
You can use the following methods to use a cursor in sqlplus: variable c refcursor; exec open: c for select isbn, title from books where rownum <5 for update; print c; www.2cto.com rollback; in the same way, exec open locks are applied, and the end transaction must be displayed to release the lock.
In PLSQL, there is also a concept called the cursor variable. Unlike the cursor, the cursor variable is dynamic while the cursor is static. A cursor can only be connected to a specified query, that is, it is fixed to the memory processing area of a query, while a cursor variable can be connected to different query statements, it can point to the memory processing area of different query statements (but it cannot point to multiple memory processing areas at the same time, and can only be connected to one query statement at a time ). steps to declare a cursor variable:
1: Define a ref cursou data TYPE, such as: TYPE ref_cursor_type is ref cursor; 2: declare a CURSOR variable of this data TYPE, such as cv_ref REF_CURSOR_TYPE; 3, when opening, it IS associated with an SQL statement: OPEN cursor_variable_nameFOR select_statement; this IS the same as the use of the CURSOR, for example: [SQL] DECLARE TYPE refcur_type IS REF CURSOR; book_refcur refcur_type; v_isbn varchar2 (100); v_title varchar2 (100); begin open book_refcur for select isbn, title from books where rownum <3; FETCH book_refcur INTO v_isbn, v_title; WHILE book_refcur % found loop DBMS_OUTPUT.PUT_LINE (v_isbn | ':' | v_title); FETCH book_refcur INTO v_isbn, v_title; end loop; www.2cto.com CLOSE book_refcur; -- open cursoe with another SQL statement ('------- open cursoe with another SQL statement'); OPEN book_refcur FOR SELECT ISBN, category FROM BOOKS WHERE rownum <3; FETCH book_refcur INTO v_isbn, v_title; WHILE book_refcur % found loop evaluate (v_isbn | ': category is:' | v_title); FETCH book_refcur INTO v_isbn, v_title; end loop; CLOSE book_refcur; END; author kkdelta