What is a Procedure?

A Procedure is a named PL/SQL block that performs a specific task. It is stored in the Oracle database and can be executed multiple times whenever needed.

Advantages of Procedures

• Code reusability
• Better performance (compiled and stored in the database)
• Easier maintenance
• Improved security (users can execute procedures without direct table access)
• Reduces code duplication

Syntax of Creating a Procedure

CREATE [OR REPLACE] PROCEDURE procedure_name
(
    parameter_name datatype [IN | OUT | IN OUT]
)
IS
-- Variable declarations
BEGIN
    -- Executable statements
EXCEPTION
    -- Exception handling
END;
/

Parameter Modes

Example 1: Simple Procedure (Without Parameters)

Employee Table

Create Procedure

CREATE OR REPLACE PROCEDURE display_message
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome to Oracle PL/SQL');
END;
/

Execute Procedure

EXEC display_message;

or

BEGIN
    display_message;
END;
/

Output

Welcome to Oracle PL/SQL

Example 2: Procedure with IN Parameter

Create Procedure

CREATE OR REPLACE PROCEDURE show_employee
(
    p_empid IN NUMBER
)
IS
    v_name EMPLOYEE.EMP_NAME%TYPE;
    v_salary EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_NAME, SALARY
    INTO v_name, v_salary
    FROM EMPLOYEE
    WHERE EMP_ID = p_empid;

    DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || v_salary);
END;
/

Execute

EXEC show_employee(101);

Output

Employee Name : Rahul
Salary : 35000

Example 3: Procedure with OUT Parameter

Create Procedure

CREATE OR REPLACE PROCEDURE get_salary
(
    p_empid IN NUMBER,
    p_salary OUT NUMBER
)
IS
BEGIN
    SELECT SALARY
    INTO p_salary
    FROM EMPLOYEE
    WHERE EMP_ID = p_empid;
END;
/

Execute

DECLARE
    v_salary NUMBER;
BEGIN
    get_salary(101, v_salary);
    DBMS_OUTPUT.PUT_LINE('Salary = ' || v_salary);
END;
/

Output

Salary = 35000

Example 4: Procedure with IN OUT Parameter

Create Procedure

CREATE OR REPLACE PROCEDURE increase_salary
(
    p_salary IN OUT NUMBER
)
IS
BEGIN
    p_salary := p_salary + 5000;
END;
/

Execute

DECLARE
    v_salary NUMBER := 35000;
BEGIN
    increase_salary(v_salary);
    DBMS_OUTPUT.PUT_LINE('Updated Salary = ' || v_salary);
END;
/

Output

Updated Salary = 40000

Example 5: Procedure to Insert Employee Record

CREATE OR REPLACE PROCEDURE add_employee
(
    p_id NUMBER,
    p_name VARCHAR2,
    p_salary NUMBER
)
IS
BEGIN
    INSERT INTO EMPLOYEE
    VALUES (p_id, p_name, p_salary);

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Employee Added Successfully');
END;
/

Execute

EXEC add_employee(103,'Amit',45000);

Output

Employee Added Successfully

Example 6: Procedure to Update Salary

CREATE OR REPLACE PROCEDURE update_salary
(
    p_empid NUMBER,
    p_increment NUMBER
)
IS
BEGIN
    UPDATE EMPLOYEE
    SET SALARY = SALARY + p_increment
    WHERE EMP_ID = p_empid;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Salary Updated Successfully');
END;
/

Execute

EXEC update_salary(101,5000);

Example 7: Procedure with Exception Handling

CREATE OR REPLACE PROCEDURE employee_details
(
    p_empid NUMBER
)
IS
    v_name EMPLOYEE.EMP_NAME%TYPE;
BEGIN
    SELECT EMP_NAME
    INTO v_name
    FROM EMPLOYEE
    WHERE EMP_ID = p_empid;

    DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee Not Found');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected Error');
END;
/

Execute

EXEC employee_details(999);

Output

Employee Not Found

How to View Procedure Source Code

SELECT TEXT
FROM USER_SOURCE
WHERE NAME = 'DISPLAY_MESSAGE'
ORDER BY LINE;

How to Drop a Procedure

DROP PROCEDURE display_message;

Difference Between Procedure and Function