PLSQL explicit cursors

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The process includes explicit cursors:

Declare cursor, the cursor is open cursors, retrieval, close the cursor.

Declare cursor

CURSOR c_cursor_name IS statement;

The cursor is equivalent to a query result set, the query results in the cursor, convenient for processing in the block.


A record is a complex data structure, equivalent to the results of a data set of results for traversing the cursor, storage. Record supports three kinds of definition: Based on table, based on vernier, custom.

If a table or a cursor is based on, its definition format:

record_name table_name or cursor_name%ROWTYPE;

Open the cursor

OPEN cursor_name;

Retrieve the cursor

There are two kinds of vernier retrieval methods:

FETCH cursor_name INTO pl/sql variables;


FETCH cursor_name INTO pl/sql record;

When the cursor is retrieved, after each FETCH statement, the active data set pointer to move to the next row. Therefore, FETCH will return the active data set of continuous data rows, until obtain the entire data set. Finally, a FETCH statement does not give the output variable assignment, the latter still retained the original value.

Then the cursor retrieval to the end, how to stop the search.?

Attribute will be used when the cursor.

The attributes of a cursorInclude:

%NOTFOUND the current FETCH operation returns no data line, true, otherwise false;

%FOUND with the opposite;

%The number of records returned by ROWCOUNT cursor;

%ISOPEN cursor is open;

Close cursor

CLOSE cursor_name;

The following is an example of:

 1 declare
 2   cursor c_zip is 
 3          select * from zipcode;--Declare cursor
 4   vr_zip c_zip%rowtype;--To declare a record, structure based on cursor
 5 begin
 6   open c_zip;--Open the cursor
 7   loop
 8     fetch c_zip into vr_zip;--Circulation, retrieval cursor
 9     exit when c_zip%NOTFOUND;--Using attributes, the FETCH operation returns no data, exit
10     dbms_output.putline( || ' ' || || ' ' || vr_zip.state);
11   end loop;
12   close c_zip;--Close cursor
13 end;

Simple method: use the cursor FOR cycle

With the help of a FOR loop, the cursor is opened and closed, the retrieval process is implicitly. This makes it easier to write and maintain code block.

Method of use is similar to C# in foreach, automatically traverse.

The following is the example above for circulation method:

1 declare
2   cursor c_zip is 
3          select * from zipcode;--Declare cursor
4 begin
5   for r_zip in c_zip--Do not have to declare a record
6   loop
7     dbms_output.putline( || ' ' || || ' ' || vr_zip.state);
8   end loop;--Do not need to consider when to exit
9 end;

The incoming parameters

If you want to pass the arguments, the cursor name followed by a similar function's parameter list or something, in the search when the cursor is equivalent to call (i.e. FETCH INTO statement), into the actual parameters in the cursor.

The following is an example.:

 1 declare
 2   cursor c_zip(p_state in zipcode.state%type) is --The incoming parameters
 3          select zip, city, state
 4          from zipcode
 5          where state = p_state;
 6   v_state zipcode.state%type := &a;
 7 begin
 8   for r_zip in c_zip(v_state)--The practical parameters of incoming call
 9       loop
10             dbms_output.put_line( || ', ' || || ', ' || r_zip.state);
11       end loop;
12 exception
13    when no_data_found then dbms_output.put_line('no data found');
14 end;

Happy Chinese Valentine's day. We have to do the safety measures.~~

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Shirley at December 11, 2013 - 1:36 AM