Write a PL/SQL program to demonstrate Cursors. in advanced database
Sure! Here is an example of a PL/SQL program that demonstrates the use of cursors in an Oracle database.
```
DECLARE
-- Declare variables
cursor employee_cursor is
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 50;
employee_record employee_cursor%ROWTYPE;
BEGIN
-- Open the cursor
OPEN employee_cursor;
-- Fetch the first record
FETCH employee_cursor INTO employee_record;
-- Loop through the records and print employee information
LOOP
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_record.employee_id);
DBMS_OUTPUT.PUT_LINE('First Name: ' || employee_record.first_name);
DBMS_OUTPUT.PUT_LINE('Last Name: ' || employee_record.last_name);
DBMS_OUTPUT.PUT_LINE('-------------------------------');
-- Fetch the next record
FETCH employee_cursor INTO employee_record;
END LOOP;
-- Close the cursor
CLOSE employee_cursor;
END;
/
```
In this example, we first declare a cursor named `employee_cursor` that selects employee IDs, first names, and last names from the `employees` table for a specific department (department_id = 50).
We then declare a variable `employee_record` of type `employee_cursor%ROWTYPE`, which is used to store the fetched record from the cursor.
Next, we open the cursor using the `OPEN` statement.
We then fetch the first record from the cursor using the `FETCH INTO` statement, which assigns the selected fields to the `employee_record` variable.
Inside the loop, we print the employee information using the `DBMS_OUTPUT.PUT_LINE` statement.
We also include an `EXIT WHEN` statement to exit the loop when there are no more records to fetch.
We then fetch the next record using `FETCH INTO` statement to continue looping until all records have been processed.
Finally, we close the cursor using the `CLOSE` statement.
To run this program, you can execute it in any PL/SQL IDE or use the SQL*Plus command line interface. The output will be displayed in the console.