Stored Procedure in Oracle Example With IN OUT Parameter

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.

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.