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 |