PL/SQL queries multiple records with the cursor

The PL/SQL cursor allows the program to select multiple rows of data from the database, and then separately process each row of data, it provides Oracle with a method to indicate and control each stage of SQL processing. I think you have some knowledge about PL/SQL. Through this article, you will learn:

• Creation of cursor
• Processing the cursor
• Define and use cursor Properties

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:

• Declare cursor
• Open cursor
• Retrieve data from the cursor
• Close the cursor

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 (insert, update, delete, and select) executed on data ). 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. If this record is returned successfully the most recently, the value is true.
% Notfound	Boolean attribute. Its value is always opposite to the value of % found.
% Isopen	Boolean attribute, returns true when the cursor is open
% Rowcount	Number 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.