Pl/sql query multiple records with the cursor

The PL/SQL cursor provides a way for a program to select multiple rows of data from a database and then handle each row separately, providing Oracle with a means to indicate and control the stages of SQL processing. I will think you have a certain understanding of pl/sql. Through this article, you will learn:
The cursor's creation cursor's processing defines and uses the cursor attribute one, what is the cursor
Oracle uses two cursors: an explicit cursor and an implicit cursor. Regardless of how many records the statement returns, Pl/sql implicitly declares a cursor for each SQL command used for update, delete, and insert. (to manage the processing of SQL statements, you must implicitly define a cursor.) The user declares and uses the display cursor to process multiple records returned by the SELECT statement. The defined cursor that is displayed is a structure that enables the user to specify the memory area for a particular statement for later use.

Second, the role of the cursor
When PL/SQL cursor queries return multiple rows of data, these groups of records are called active sets. Oracle stores this activity set in the named cursor of the display definition that you create. Oracle cursors are a mechanism for easy processing of multiple rows of data, and without cursors, Oracle developers must individually and explicitly retrieve and manage each record selected by the cursor query.
Another function of the cursor that contains a pointer to the record that is currently being accessed, which allows your program to process multiple records at once.

Iii. using the basic method of displaying cursors
The steps are as follows:
DECLARE CURSOR open cursor retrieve data from cursor close CURSOR 1, DECLARE CURSOR
The syntax for declaring the cursor is as follows:
DECLARE cursor_name
Is
SELECT statement
Where cursor_name is the name you specify for the cursor; SELECT statement is a query that returns records to the cursor activity set.
The declaration cursor completes the following two purposes:
Give the cursor a name;
Associates a query with the cursor.
It is noteworthy that the cursor must be declared in the declaration portion of the Pl/sql block; the name given to the cursor is an undeclared identifier, not a pl/sql variable, cannot be assigned a value to the cursor name, nor can it be used in an expression. The Pl/sql block uses this name to refer to the cursor query.
Example: DECLARE
CURSOR C1
Is
SELECT view_name from All_views
WHERE rownum<=10;
In addition, you can declare the parameters of the cursor in the cursor definition statement, 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 refer to the cursor's parameters outside of the cursor range. If you attempt to do so, Oracle returns an error stating that the variable is undefined.
2, open the cursor
The syntax for opening the cursor is as follows:
OPEN cursor_name;
Where cursor_name is the name of the cursor you previously defined.
Opening the cursor activates the query and recognizes the active set, but does not really retrieve the record until the cursor FETCH command is executed. The Open command also initializes the cursor pointer so that it points to the first record of the active set. After the cursor is opened, all data retrieved to the active set is static until it is closed, in other words, the cursor ignores all the SQL DML commands (INSERT, UPDATE, delete, and select) that were executed on the data after the cursor was opened. So you open it only when you need it, and to refresh the active set, simply close and reopen the cursor.
3, retrieve the data from the cursor
The FETCH command retrieves the records in the activity set in one record at a time. The fetch command is usually combined with some sort of iterative processing, and in an iterative process, the FETCH command executes each time and the cursor advances to the next record in the active set.
Syntax for the FETCH command:
FETCH cursor_name into record_list;
Where cursor_name is the name of the previously defined cursor; Record_list is the list of variables that accept the columns in the active set. The fetch command places the results of the active set into these variables.
After the fetch command is executed, the result of the activity set is retrieved into the PL/SQL variable for use in the Pl/sql block. Each record is retrieved, and the pointer to the cursor moves to the next record in the active set.
Cases:
FETCH C1 into VName;
While C1%found loop
Dbms_output. Put_Line (To_char (c1%rowcount) | | '|| VName);
End LOOP;
Where the property '%found ' is used so that when fetch reaches the end of the active set, no exception is thrown. Other attributes and meanings are shown in the following table:
Attribute content
%found Boolean property that is true when the last time the record was successfully returned
%notfound Boolean property whose value is always the opposite of the value of the%found property
%isopen Boolean property that returns True when the cursor is open
%rowcount Numeric property that returns the number of records that have been read from the cursor

Attribute Content%found A Boolean property that, when successfully returned on the most recent record, is a true%notfound Boolean property whose value is always the opposite of the value of the%found property%isopen A Boolean property that returns the True%rowcount numeric property when the cursor is opened. Returns the number of records that have been read from the cursor
4, close the cursor
The close statement closes the previously opened cursor, making the active set indeterminate. When the user's program or session ends, Oracle implicitly closes the cursor. After the cursor is closed, no action can be taken on it, or an exception is thrown.
The syntax for the close statement is:
Close cursor_name;
Where 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
A cursor is a structure that can handle the results of multiple rows of queries one record at a time. An implicit cursor is created for each DML statement, and the explicit cursor is created by the user to handle the query that returns more than one record. Furthermore, by eliminating the repeated parsing of the code, the cursor improves the processing speed of the code.