How to call cursor from a stored procedure in oracle?

by cortez.connelly , in category: MySQL , 3 months ago

How to call cursor from a stored procedure in oracle?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by elisha_langworth , 3 months 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:

  1. 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;


  1. 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.