Plsql explicit cursor

The processing process of an explicit cursor includes:

Declare the cursor, open the cursor, search the cursor, and close the cursor.

Declared cursor

CURSOR c_cursor_name IS statement;

A cursor is equivalent to a query result set. The query result is placed in the cursor to facilitate processing in the block.

A record is a composite data structure, which is equivalent to a row of data in the result set. It is used to traverse the cursor and store results. Three types of records are supported: Table-based, cursor-based, and custom.

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

Record_name table_name or cursor_name % ROWTYPE;

Open cursor

OPEN cursor_name;

Search cursor

There are two kinds of cursor retrieval methods:

FETCH cursor_name INTO pl/SQL variables;

Or

FETCH cursor_name INTO pl/SQL record;

When the cursor is retrieved, after each FETCH statement, the active dataset pointer continues to be migrated to the next data row. Therefore, FETCH returns continuous rows in the active dataset until the entire dataset is obtained. The last FETCH statement does not assign values to the output variables. The latter still saves the original values.

So how can I stop the search at the end of the cursor search?

The cursor attribute is required.

Cursor propertyIncluding:

% NOTFOUND: true if no data row is returned for the current FETCH operation; otherwise, false;

% FOUND is opposite to the above;

% ROWCOUNT the number of records returned by the cursor;

% ISOPEN check whether the cursor is opened;

Close cursor

CLOSE cursor_name;

The following is an example:

     c_zip               zipcode;   vr_zip c_ziprowtype;     c_zip;       c_zip  vr_zip;       c_zipNOTFOUND;     dbms_output.putline(vr_zip.zip     vr_zip.city           c_zip; ;

 

Easy way: Use a cursor FOR Loop

With the help of the cursor FOR loop, the cursor opens, and the retrieval and closure processes are implicitly implemented. This makes code blocks easier to write and maintain.

The usage method is similar to the foreach in C # And is automatically traversed.

The for loop statement in the preceding example is as follows:

     c_zip               zipcode;     r_zip  c_zip      dbms_output.putline(vr_zip.zip     vr_zip.city        loop; ;

 

Input parameters

If you want to input parameters, add a list of parameters similar to the function after the cursor name, which is equivalent to a call (I .e. a FETCH INTO statement) When retrieving the cursor, and pass in the actual parameters after the cursor name.

The following is an example:

     c_zip(p_state  zipcode.statetype)                                 state    v_state zipcode.statetype :      r_zip  c_zip(v_state)              dbms_output.put_line(r_zip.zip    r_zip.city                no_data_found  dbms_output.put_line( ;