Cursor-based records

Oracle PL/SQL Cursor-based records are a type of data structure that allows for efficient retrieval and manipulation of data stored in a database table.

Cursor-based records are especially well suited for working with large datasets because they allow the developer to fetch only the data that is needed, instead of loading the entire dataset into memory.

The PL/SQL Cursor-based records also offer a number of advantages over traditional database query results, including the ability to scroll backwards and forwards through the data, or to jump to specific rows without having to start from the beginning of the dataset.

Syntax

To use PL/SQL cursor-based records in Oracle database, you first need to declare a cursor variable.

This can be done using the following syntax:

CURSOR cursor_name IS 
SELECT column1, column2, column3 
FROM table_name; 

Once you have declared your cursor variable, you can then open it and fetch data from it using the following syntax:

OPEN cursor_name; 
FETCH cursor_name 
INTO variable1, variable2, variable3; 

You can continue fetching data from the cursor until it reaches the end of the dataset, at which point the FETCH statement will return a not found error.

Once you have finished working with the data in the cursor, you should close it to release any resources that it is using.

This can be done using the following syntax:

CLOSE cursor_name;

Example

Course table

COURSE_ID	NAME	PRICE
1	SQL 1	10
2	SQL 2	50
3	HTML5	10

Cursor-based records

DECLARE
CURSOR c1 IS 
SELECT * FROM course where price=10;
course_rec c1%rowtype;
BEGIN
OPEN c1;
LOOP
FETCH c1 into course_rec;
EXIT WHEN c1%notfound;
DBMS_OUTPUT.PUT_LINE
('Id: '||course_rec.COURSE_ID||' Name: '||course_rec.NAME||' Price: '||course_rec.PRICE);
END LOOP;
CLOSE c1;
END;
/

Output

Id: 1 Name: SQL 1 Price: 10

Id: 3 Name: HTML5 Price: 10