What is the difference between a cursor and a record in PL/SQL?

PL/SQL Developer Interview Question: Difference between Cursor and Record in PL/SQL

In PL/SQL, both cursor and record are used as programming constructs to handle database operations; however, they serve different purposes and have distinct characteristics.

Cursors in PL/SQL:

A cursor in PL/SQL is a database object used to retrieve and manipulate multiple rows of data from a result set. Cursors are often used in conjunction with SELECT statements to process query results row by row. Cursors are explicitly opened, fetched, and closed.

Records in PL/SQL:

A record in PL/SQL is a composite data structure that stores a single row's worth of data fetched from a table or returned from a cursor. Records are typically defined using the %ROWTYPE attribute, which maps the record structure to the columns of a table or cursor result set.

Differences between Cursor and Record:

1. Cursors are used to process multiple rows of data, while records are used to store data for a single row.
2. Cursors are explicitly opened and closed, whereas records are accessed directly within the scope of a PL/SQL block.
3. Cursors require FETCH operations to retrieve data, while records directly hold the fetched data.
4. Cursors are used to iterate over result sets, while records are used to hold temporary data within a program.

In summary, a cursor is a database object used for traversing and processing result sets, while a record is a data structure used to hold a single row's worth of data within PL/SQL code.