How to call cursor from a stored procedure in oracle?
How to call cursor from a stored procedure in oracle?
1 answer
by
elisha_langworth ,
a month ago
@cortez.connelly
To call a cursor from a stored procedure in Oracle, you first need to declare and define the cursor in the stored procedure. Then you can open the cursor, fetch data from it, and close the cursor. Here is an example of how you can do this:
- Declare and define the cursor in the stored procedure:
1 2 3 4 5 |
CREATE OR REPLACE PROCEDURE get_employee_data
IS
CURSOR employee_cur IS
SELECT employee_id, first_name, last_name
FROM employees;
|
- Open the cursor, fetch data from it, and close the cursor:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE OR REPLACE PROCEDURE get_employee_data
IS
CURSOR employee_cur IS
SELECT employee_id, first_name, last_name
FROM employees;
BEGIN
OPEN employee_cur;
LOOP
FETCH employee_cur INTO emp_id, emp_first_name, emp_last_name;
EXIT WHEN employee_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Name: ' || emp_first_name || ' ' || emp_last_name);
END LOOP;
CLOSE employee_cur;
END;
|
You can then call the stored procedure get_employee_data to execute the cursor and fetch the employee data.