Oracle Learning (11) Cursors

The PL/SQL statement provides the ability to process a result set line by row through a cursor. A cursor can be treated as a special pointer that is associated with a query result and can point to any location in the result set to handle the data at the specified location. You can use cursors to process data while querying it. Cursors are divided into both explicit and implicit cursors.

One, explicit cursors

Using a display cursor requires that you follow the declaration cursor--> Open Cursor--> read the data--> close the cursor four steps.

1. Declaring cursors

The methods defined are as follows:

DECLARE CURSOR cursorname is select_statement

Where Cursorname is the cursor name, select_statement is a SELECT statement that produces the result set associated with the declared cursor like the query.

For example, the following is a definition instance of a cursor:

DECLARE CURSOR modulecursor is select name,parent from T_module;

2. Open cursor

After declaring a cursor, you must first open the cursor before using the data. Open the cursor in Pl/sql by using the Open statement in the format:

Open Cursorname

Where the Cursorname is the name of the cursor that needs to be opened, and after the cursor is opened, you can use the system variable%rowcount to see where the cursor is currently located.

example, define a cursor modulecursor, and then open the cursor to output its current location.

DECLARE CURSOR modulecursor is 
    select Name,parent from T_module;  
Begin
    Open modulecursor;  
    Dbms_output.put_line (modulecursor%rowcount);  
End

3. Read the data

After you open the cursor, you can use the FETCH statement to read the data from it. The syntax format for the FETCH statement is:

Fetch cursorname [into Variable_name,... N]

Where Cursorname represents the name of the cursor from which the data is extracted, into means that the extracted data is stored in the variable variable_name.

Sample code:

DECLARE
  modulename char (m);  
  ModuleID number (3);  
  Cursor Modulecursor 
  is select ID, name from T_module;  
Begin
  Open modulecursor;  
  Dbms_output.put_line (modulecursor%rowcount);  
  Fetch modulecursor into ModuleID, modulename; -A fetch exercise must be done before the found Operation

, or the cursor will not point to the beginning of the result set, and the found operation will return false forever. While  
  modulecursor%found  
  loop  
    dbms_output.put_line (' modulename: ' | | modulename | | ', ModuleID: ' | | ModuleID);  
    Fetch modulecursor into ModuleID, modulename;  
  End Loop;  
  Close modulecursor;  
End