Query multiple records with the cursor in PL/SQL

1. What is the cursor?

Oracle uses two types of cursor: explicit cursor and implicit cursor. No matter how many records are returned by the statement, PL/SQL implicitly declares a cursor for each SQL command, such as UPDATE, DELETE, and INSERT. To manage SQL statement processing, you must implicitly define a cursor for it .) The user declares and uses the display cursor to process multiple records returned by the SELECT statement. The display defines a structure that allows you to specify a memory area for a specific statement for future use.

Ii. Role of the cursor

When PL/SQL cursor queries return multiple rows of data, these record groups are called Activity sets. Oracle stores this activity set in the named cursor of the display definition you created. Oracle cursor is a mechanism for easily processing multi-row data without a cursor. Oracle developers must retrieve and manage each record selected by the cursor query separately and explicitly.

Another feature of the cursor, which contains a pointer to the record that tracks the current access, enables your program to process multiple records at a time.

3. Basic Methods for displaying the cursor

The procedure is as follows:

1. Declare the cursor

The syntax for declaring the cursor is as follows:

DECLARE cursor_name

Is

SELECT statement

Here, cursor_name is the name you specify for the cursor; SELECT statement is the query for records returned by the cursor activity set.

Declaring the cursor completes the following two goals:

Name the cursor;

Associate a query with the cursor.

It is worth noting that the cursor must be declared in the declaration part of the PL/SQL block; The name specified for the cursor is an undeclared identifier rather than a PL/SQL variable, you cannot assign a value to the cursor name or use it in an expression. PL/SQL blocks use this name to reference cursor queries.

Example: DECLARE

CURSOR c1

Is

SELECT VIEW_NAME FROM ALL_VIEWS

Where rownum <= 10;

You can also declare the parameter of the optical mark in the Definition Statement of the cursor, for example:

CURSOR c1 view _ nbr number)

Is

SELECT VIEW_NAME FROM ALL_VIEWS

Where rownum <= view _ nbr;

The cursor parameter is visible only to the corresponding cursor, and cannot reference the cursor parameter outside the cursor range. If you try to do this, Oracle returns an error stating that the variable is not defined.

2. Open the cursor

The syntax for opening the cursor is as follows:

OPEN cursor_name;

Cursor_name is the name of the cursor you previously defined.

Opening the cursor will activate the query and identify the activity set, but the record is not actually retrieved before executing the cursor retrieval command. The OPEN command also initializes the cursor pointer to point it to the first record of the activity set. After the cursor is opened, all data retrieved back to the activity set is static until it is closed. In other words, the cursor ignores all, SQL DML commands for Data Execution INSERT, UPDATE, DELETE, and SELECT ). Therefore, you only need to open it when necessary. to refresh the activity set, you only need to close and re-open the cursor.

3. retrieve data from the cursor

The FETCH Command retrieves records from the activity set in the form of one record each time. The FETCH Command is usually used in combination with an iterative process. In iterative processing, every execution of the FETCH Command advances the cursor to the next record of the active set.

Syntax of the FETCH command:

FETCH cursor_name INTO record_list;

Cursor_name is the name of the cursor defined earlier, and record_list is the Variable list, which accepts columns in the active set. The FETCH Command places the results of the activity set in these variables.

After the FETCH Command is executed, the results in the activity set are retrieved back to the PL/SQL variable for use in the PL/SQL block. Each time a record is retrieved, the pointer of the cursor moves to the next record of the active set.

Example:

FETCH C1 into vname;

WHILE C1 % FOUND LOOP

DBMS_OUTPUT.PUT_LINE (TO_CHAR (C1% ROWCOUNT) | ''| VNAME );

End loop;

The property '% FOUND' is used to prevent exceptions when FETCH reaches the end of the activity set. Other attributes and meanings are shown in the following table:

Attribute content

% FOUND Boolean attribute. The value is TRUE if this record is returned successfully the most recently.

% NOTFOUND: Boolean attribute. Its value is always opposite to the value of % FOUND.

% ISOPEN Boolean attribute. TRUE is returned when the cursor is open.

% ROWCOUNT numeric attribute, returns the number of records read from the cursor

Attribute content

% FOUND Boolean attribute. The value is TRUE if this record is returned successfully the most recently.

% NOTFOUND: Boolean attribute. Its value is always opposite to the value of % FOUND.

% ISOPEN Boolean attribute. TRUE is returned when the cursor is open.

% ROWCOUNT numeric attribute, returns the number of records read from the cursor

4. Close the cursor

The CLOSE statement closes the previously opened cursor, making the activity set uncertain. When your program or session ends, Oracle implicitly closes the cursor. After the cursor is closed, you cannot perform any operations on it. Otherwise, an exception occurs.

The syntax of the CLOSE statement is:

CLOSE cursor_name;

Cursor_name is the name of the previously opened cursor.

The complete program code is as follows:

DECLARE

CURSOR C1 is select VIEW_NAME FROM ALL_VIEWS

Where rownum <= 10

Order by VIEW_NAME;

VNAME VARCHAR2 (40 );

BEGIN

OPEN C1;

FETCH C1 into vname;

WHILE C1 % FOUND LOOP

DBMS_OUTPUT.PUT_LINE (TO_CHAR (C1% ROWCOUNT) | ''| VNAME );

End loop;

END;

...... CLOSE C1;

Iv. Summary

The cursor is a structure that can process the results of multi-row queries in one record at a time. create an implicit cursor for each DML statement, and an explicit cursor is created by the user to process queries that return multiple records. In addition, by eliminating repeated code analysis, the cursor improves the code processing speed.