Stored Procedure in Oracle Example With IN OUT Parameter • Vinish.Dev

In Oracle PL/SQL, a stored procedure can have different types of parameters: IN, OUT, and IN OUT. The IN parameter is used to pass a value into the procedure, while the OUT parameter allows the procedure to return a value to the caller. Below, you will find an example that demonstrates how to create and use a stored procedure with both IN and OUT parameters.

On This Page
Show More

Stored Procedure IN-OUT Parameter Example

The following example shows how to create a procedure named GET_EMP_NAME. This procedure accepts an employee number as an input parameter (i_empno) and returns the corresponding employee name as an output parameter (o_ename).

CREATE OR REPLACE PROCEDURE GET_EMP_NAME (
    i_empno IN emp.empno%TYPE,
    o_ename OUT emp.ename%TYPE
)
IS
    CURSOR c_ename(p_empno emp.empno%TYPE) IS
        SELECT ename
        FROM emp
        WHERE empno = p_empno;
BEGIN
    OPEN c_ename(i_empno);
    FETCH c_ename INTO o_ename;
    CLOSE c_ename;
END get_emp_name;

In this procedure:

i_empno is the IN parameter for the employee number.
o_ename is the OUT parameter that will return the employee's name.
A cursor c_ename is declared to select the employee name based on the provided employee number.
The cursor is opened with the input parameter, the name is fetched into the output variable, and then the cursor is closed.

Executing the GET_EMP_NAME Procedure

To call this procedure and view the result, you can use an anonymous PL/SQL block as shown below. In this example, we are getting the name of the employee whose number is 7566.

SET SERVEROUTPUT ON;
DECLARE
    v_name emp.ename%TYPE;
BEGIN
    get_emp_name(7566, v_name);
    DBMS_OUTPUT.PUT_LINE(v_name);
END;

After running this block, the output will display the employee's name associated with the employee number provided.

Output

JONES
PL/SQL procedure successfully completed.

This example demonstrates how to use IN and OUT parameters in your PL/SQL procedures, making it easy to pass values in and return results from your stored procedures in Oracle.