In PL/SQL, a cursor is a mechanism for retrieving data from a database and processing it one row at a time. A cursor can be thought of as a pointer to a specific area in the result set of a query.
Cursors can be classified into two types:
- Implicit cursors: PL/SQL automatically creates an implicit cursor for every SQL statement that is not a SELECT statement, and it is called an implicit cursor.
- Explicit cursors: explicit cursors are created by the programmer using the DECLARE cursor statement.
Example of using explicit cursor:
In the above example, a cursor named “emp_cursor” is declared and it is associated with the SELECT statement to retrieve the data from the “emp” table. The cursor is opened, and the FETCH statement is used to retrieve the data from the cursor one row at a time and store it in the “emp_rec” record. The record “emp_rec” is defined using the %ROWTYPE attribute of the cursor. The record has the same structure as the rows returned by the SELECT statement. Finally, the cursor is closed.
- record : A record in PL/SQL is a composite data structure that can hold multiple fields of different data types. A record can be thought of as a single row of a table or a cursor.
Example of using record:
In the above example, a record type named “emp_rec_type” is declared and it has three fields empno, ename and sal. The record variable “emp_rec” is defined using the record type “emp_rec_type“. The SELECT INTO statement is used to retrieve the data from the “emp” table and store it in the “emp_rec” record.
Both cursor and record are used to store and process data in PL/SQL. Cursor is used to process data one row at a time and records are used to store data in a composite data structure.
