Stored Procedures

A stored procedure is a named PL/SQL block compiled and stored in the Oracle data dictionary. It can accept parameters, perform DML, call other procedures, and return values through OUT parameters. Unlike functions, procedures do not return a value through the RETURN statement.

Creating a Procedure

CREATE OR REPLACE PROCEDURE procedure_name(
    param1  IN  datatype,
    param2  OUT datatype,
    param3  IN OUT datatype
) IS
    -- Local variable declarations
BEGIN
    -- Executable statements
EXCEPTION
    -- Error handlers
END procedure_name;
/

• CREATE OR REPLACE — creates the procedure if new, or silently replaces the existing one.
• IS or AS — both are identical.
• The trailing END procedure_name; with the name is optional but strongly recommended.

Parameter Modes

*OUT parameters are NULL on entry unless initialized by the caller.

IN Parameters

CREATE OR REPLACE PROCEDURE give_raise(
    p_emp_id     IN employees.employee_id%TYPE,
    p_pct        IN NUMBER DEFAULT 5   -- default value
) IS
    v_current  employees.salary%TYPE;
    v_new_sal  employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_current
    FROM   employees
    WHERE  employee_id = p_emp_id;

    v_new_sal := ROUND(v_current * (1 + p_pct / 100), 2);

    UPDATE employees SET salary = v_new_sal WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE(
        'Emp ' || p_emp_id || ': $' || v_current || ' → $' || v_new_sal
    );
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee ' || p_emp_id || ' not found');
END give_raise;
/

OUT Parameters

CREATE OR REPLACE PROCEDURE get_emp_info(
    p_emp_id   IN  employees.employee_id%TYPE,
    p_name     OUT VARCHAR2,
    p_salary   OUT employees.salary%TYPE,
    p_dept     OUT departments.department_name%TYPE
) IS
BEGIN
    SELECT e.first_name || ' ' || e.last_name,
           e.salary,
           d.department_name
    INTO   p_name, p_salary, p_dept
    FROM   employees   e
    JOIN   departments d ON d.department_id = e.department_id
    WHERE  e.employee_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, 'Employee ' || p_emp_id || ' not found');
END get_emp_info;
/

-- Calling with OUT parameters
DECLARE
    v_name    VARCHAR2(100);
    v_salary  NUMBER;
    v_dept    VARCHAR2(100);
BEGIN
    get_emp_info(101, v_name, v_salary, v_dept);
    DBMS_OUTPUT.PUT_LINE(v_name || ' | $' || v_salary || ' | ' || v_dept);
END;
/

IN OUT Parameters

CREATE OR REPLACE PROCEDURE normalize_name(
    p_name IN OUT VARCHAR2
) IS
BEGIN
    -- Clean up the name passed in and return the normalized version
    p_name := INITCAP(TRIM(p_name));
END normalize_name;
/

DECLARE
    v_name VARCHAR2(100) := '   STEVEN   king   ';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Before: [' || v_name || ']');
    normalize_name(v_name);
    DBMS_OUTPUT.PUT_LINE('After:  [' || v_name || ']');
END;
/

Output:

Before: [   STEVEN   king   ]
After:  [Steven   King   ]

Calling Procedures

Positional Notation

BEGIN
    give_raise(100, 10);  -- emp_id=100, pct=10
END;
/

Named Notation

BEGIN
    give_raise(p_emp_id => 100, p_pct => 10);
    give_raise(p_pct => 5, p_emp_id => 101);  -- order doesn't matter
END;
/

Mixed Notation

BEGIN
    give_raise(100, p_pct => 10);  -- positional first, then named
END;
/

Default Parameter Values

BEGIN
    give_raise(102);  -- uses default p_pct => 5
END;
/

Local Procedures (Nested)

Procedures can be declared locally inside another procedure or package body — they are only visible within their enclosing block:

CREATE OR REPLACE PROCEDURE process_department(p_dept_id IN NUMBER) IS

    -- Local helper procedure — not callable from outside
    PROCEDURE log_action(p_msg IN VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' — ' || p_msg);
    END log_action;

BEGIN
    log_action('Starting processing for dept ' || p_dept_id);

    UPDATE employees
    SET    salary = salary * 1.05
    WHERE  department_id = p_dept_id;

    log_action('Updated ' || SQL%ROWCOUNT || ' employees.');
    COMMIT;
END process_department;
/

Viewing and Recompiling

-- View procedure source
SELECT text FROM user_source
WHERE  name = 'GIVE_RAISE'
AND    type = 'PROCEDURE'
ORDER BY line;

-- Check for compilation errors
SHOW ERRORS PROCEDURE give_raise;

-- Recompile after dependency changes
ALTER PROCEDURE give_raise COMPILE;

-- List all procedures
SELECT object_name, status, last_ddl_time
FROM   user_objects
WHERE  object_type = 'PROCEDURE'
ORDER BY object_name;

Dropping a Procedure

DROP PROCEDURE give_raise;

Granting Execute Permission

-- Allow the hr_app role to execute the procedure
GRANT EXECUTE ON give_raise TO hr_app_role;

-- Revoke
REVOKE EXECUTE ON give_raise FROM hr_app_role;

Summary

• Procedures are named, stored PL/SQL blocks called with EXECUTE or BEGIN ... END;.
• Three parameter modes: IN (read-only), OUT (write-only), IN OUT (read and write).
• DEFAULT values on IN parameters make them optional.
• Named notation (p_name => value) makes calls self-documenting and order-independent.
• Local procedures are nested helpers visible only inside their enclosing block.
• SHOW ERRORS reveals compilation failures; ALTER PROCEDURE ... COMPILE recompiles manually.
• Grant EXECUTE privileges to allow other users or roles to call a procedure without seeing its source.