oracle-35-Implicit cursors & Explicit Cursors

I. Cursor action (or definition)

1.pl/sql provides a cursor mechanism for processing multi-row record result sets;

2. A cursor is similar to a pointer, so that the application can process one row of records at a time , such as placing a cursor in a for loop, processing a row of records at a time, and then looping n times to process n rows of records ;

3.Oracle , can be divided into both explicit and implicit cursors, such as select*fromstudent is to use an implicit cursor to traverse the student table, and then display the query results;

4. Oracle will automatically create a declaration "implicit cursor" in the usual select query,DML operation To process the result data;

6. If a specific function (SELECT,DML operation) needs to be completed, the PL/SQL program to customize an "explicit cursor".

Second, implicit cursors

When an SQL statement is executed, the Oracle server automatically creates an implicit cursor. This cursor stores the result of executing the SQL statement.

1. The main properties of the cursor (both implicit and explicit) are:

(1)%found: Boolean property, returns True if the SQL statement affects at least one row, false otherwise;

For example, the following programs:

Declare

The data type of a emp%rowtype;--a variable is a row in the EMP table, making it easy to later EMP table row data is stored in a

Begin

Select* to a from emp where empno = 7935;

If sql%found then-- uses an implicit cursor to determine whether the previous row of data exists

Dbms_output.put_line (' Employee number 7935 ' exists, whose name is ' | | A.ename);

End If;

End

Example 1: Practice cursor%found Properties

Solution: Note the program in:

If you enter a non-existent number ' h001 ' in the WHERE clause, the rest of the code will be an error, indicating that the data cannot be found:

The above error can be solved by exception, note the procedure in:

(2)%notfound: boolean attribute, opposite to the function of%found;

(3)%isopen: Boolean property that returns True when the cursor is open, false when the cursor is closed;

(4)%rowcount: Numeric attribute that returns the number of rows affected by the SQL statement.

Example 2: Practice the cursor%rowcount property.

Solution: The data in the current student table, note that the remaining data for the ' s015 ' line is null:

Now update the data with the cursor%rowcount property:

We then query the current student table and find that the ' s015 ' row data has been updated:

Therefore, the%rowcount property of the cursor can be viewed after the update data, the original student table is affected by the number of rows, the rest of the additions and deletions to the same%rowcount query can be found.

2. where the implicit cursor needs attention

( 1 ) PL/SQL used in Select statement, must and into keywords used together;

( 2 ) PL/SQL in Select only one row of data is returned, and if a row of data is exceeded, an explicit cursor will be used;

( 3 ) for Select into statement, if the execution succeeds, Sql%rowcount the value is 1 , if not successful, Sql%rowcount the value is 0 and produces an exception No_data_found ;

3."Special attention"

regardless of which property of the cursor, remember the format: cursor name % property, such as an implicit cursor sql%found, and then an explicit cursor cursor_name%found.

Three, an explicit cursor

An explicit cursor must be used when the query result returns more than one row. Using an explicit cursor

The 4 steps:

Step1 : Acoustic clear-type cursors;

STEP2 : Open an explicit cursor;

STEP3 : retrieving explicit data;

STEP4 : Closes an explicit cursor.

1. Syntax format for sound-evident cursors

cursor an explicit cursor name [(Parameter[,parameter ...])] [Return Return_type] is query statement

Where parameter is an input parameter to an explicit cursor that allows the user to pass the value to an explicit cursor when an explicit cursor is opened, parameter the format:

Name of parameter [in] parameter data type [{: = | default} Expresson]

2. Open an explicit cursor format

Open an explicit cursor name (' Zhang San ');

3. Close the cursor format

Close an explicit cursor name

Note Remember to close the cursor when you are finished using the cursor.

4. Retrieve the data using an explicit cursor, use a fetch statement to find the row in the result set, and extract a single value from it to the variable when retrieving the data. The practice is to use a loop Loop to throw the fetch in , for example:

Loop

fetch Emp_cursorinto a;--Use the FETCH statement to emp_cursor the value of the cursor pass to variable a

End Loop;

Example 3: practicing an explicit cursor

Solution: Note the program in:

Program Fragment resolution:

Declare

cursor V_cur (m in varchar2)--audible clear cursor, note Do not specify parameters length of M

is select Sno,sname the from student wheresno=m;--the parameter m value to Sno, after which the SELECT statement filters out the eligible data

type T_record is record (---Define records to facilitate subsequent use of FETCH statements to pass data within the tag

ID VARCHAR2 (20),

Name VARCHAR2 (20)

);

T t_record;--defines the variable t relative to the record T_record,

Begin

Open v_cur (' s001 ');--Open an explicit cursor

Loop

Fetch v_cur into t;--using a FETCH statement to pass data inside the cursor into a variable

Exit when v_cur%notfound;--defines a loop condition that is not inside the cursor

Data when

Dbms_output.put_line (T.id | | "| | T.name);

End Loop;

Close v_cur;--closing an explicit cursor

End

Summary

( 1 Both implicit and explicit cursors have properties:

%found,%notfound,%isopen,%rowcount ;

( 2 an implicit cursor can only manipulate single-row data, and multiple rows of data must be marked with an explicit cursor;

( 3 ) implicit cursor name pinning SQL , the explicit cursor name is to be customized;

( 4 ) An explicit cursor is to be passed Fetch statement to pass a value in a cursor to a variable, usually the Fetch statement into Loop cycle, remember to define the loop to jump out of the loop condition;

(5) When an explicit cursor is used , the cursor cursor name (parameter name in parameter data type) is defined in this way, Do not specify the length in the parameter data type, otherwise the error, such as cursor V_cur (m in Varchar2 (20)) is wrong, must not specify the length, that is, the cursor v_cur (m in varchar2).

oracle-35-Implicit cursors & Explicit Cursors