In Oracle, a cursor is a temporary work area created in memory to hold the result set of a SQL query.
Cursors enable you to retrieve and manipulate individual rows of data rather than the entire set of rows returned by a SQL statement.
Here's an example of how to use a cursor in Oracle:
DECLARE
// Declare variables to hold the data fetched from the cursor
emp_id employees.employee_id%TYPE;
emp_name employees.first_name%TYPE;
emp_salary employees.salary%TYPE;
// Declare the cursor
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary FROM employees;
BEGIN
//Open the cursor
OPEN emp_cursor;
//Loop through the cursor and fetch each row
LOOP
FETCH emp_cursor INTO emp_id, emp_name, emp_salary;
EXIT WHEN emp_cursor%NOTFOUND;
// Process the data fetched from the cursor
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_salary);
DBMS_OUTPUT.PUT_LINE('----------------------');
END LOOP;
// Close the cursor
CLOSE emp_cursor;
END;
In this example, we declare variables to hold the data fetched from the cursor. Then we declare the cursor emp_cursor with a SELECT statement to fetch employee_id, first_name, and salary from the employees table.
Inside the BEGIN block, we open the cursor using the OPEN statement. Then we loop through the cursor using a LOOP statement and fetch each row into the declared variables using the FETCH statement. We exit the loop when the cursor reaches the end of the result set using the %NOTFOUND attribute of the cursor.
Inside the loop, we can process the fetched data as needed. In this example, we print out each employee's ID, name, and salary using the DBMS_OUTPUT.PUT_LINE statement.
Finally, we close the cursor using the CLOSE statement to release the resources used by the cursor.
.
