Four steps for displaying the cursor in PLSQL

This example briefly introduces the locking and consistent read problems involved in the four processes of cursor processing.

Explicit cursor processing requires four PL/SQL steps:

1. Define/declare a cursor: defines a cursor name and the SELECT statement corresponding to it.
Format:

    CURSOR cursor_name[(parameter[, parameter]…)]           [RETURN datatype]    IS        select_statement;

The cursor parameter can only be an input parameter, in the following format:
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:
Fetch cursor_name into {variable_list | record_variable };
When a fetch statement is executed, a data row is returned each time, and 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;
The following is an example:

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);  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/declare a cursor, you only associate the SQL statement with the cursor. When the cursor is open, you can query the table. If for update is available
Locks occur. In this example, after open c_cursor is enabled, stop '-- afater open' In the debug mode.
Select * from V $ lock where Sid in (select distinct Sid from V $ session where username = 'plsql') the lock record is displayed. In this example, the PLSQL user logs on. 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://blog.csdn.net/kkdelta/article/details/7183981to simulate this process. Close
Cursor does not release the lock obtained by for update and is 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 method 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;
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, for example:
Type ref_cursor_type is ref cursor;
2: declare a cursor variable of this data type, for example:
Cv_ref ref_cursor_type;
3. associate it with an SQL statement when it is opened:
Open cursor_variable_name
For select_statement;
This is the same as the usage of the cursor, as shown in the following example:

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;    CLOSE book_refcur;        -- open cursoe with another sql statement    DBMS_OUTPUT.PUT_LINE('------- 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       DBMS_OUTPUT.PUT_LINE(v_isbn||': category is: '||v_title);    FETCH book_refcur INTO v_isbn,v_title;    END LOOP;    CLOSE book_refcur;    END;

The following link is a http://www.cnblogs.com/huyong/archive/2011/05/04/2036377.html of very complete interpretations of the cursor