Oracle PL/SQL Procedures

By /

1. Introduction to PL/SQL Procedures

A Procedure in Oracle PL/SQL is a named program unit that performs a specific action. Procedures are mainly used to execute business logic and perform operations, rather than return a value.

Procedures:

  • Are stored in the database
  • Can accept parameters
  • Can be invoked explicitly
  • May or may not return values using OUT parameters

Why Use Procedures?

  • Encapsulate business logic
  • Improve performance by reducing network calls
  • Enhance code reuse
  • Improve maintainability and security

2. Basic Syntax of a Procedure

CREATE OR REPLACE PROCEDURE procedure_name (
    parameter_name mode datatype
)
IS
BEGIN
    -- executable statements
END procedure_name;
/

Parameter Modes

  • IN (default): Read-only input
  • OUT: Returns value to caller
  • IN OUT: Passes value in and out

3. Simple Example (Beginner Level)

CREATE OR REPLACE PROCEDURE display_message
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL Procedure');
END;
/

Executing the Procedure

BEGIN
    display_message;
END;
/

4. Procedure with Parameters

CREATE OR REPLACE PROCEDURE calculate_total (
    p_value1 IN NUMBER,
    p_value2 IN NUMBER,
    p_total  OUT NUMBER
)
IS
BEGIN
    p_total := p_value1 + p_value2;
END;
/

Calling the Procedure

DECLARE
    v_result NUMBER;
BEGIN
    calculate_total(10, 20, v_result);
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_result);
END;
/

5. Real-Time Use Cases of Procedures

Common Enterprise Scenarios

  • Batch data processing
  • Data validation logic
  • ETL transformations
  • Transaction control (commit/rollback)
  • Scheduled jobs using DBMS_SCHEDULER

Example: Validation Procedure

CREATE OR REPLACE PROCEDURE validate_amount (
    p_amount IN NUMBER
)
IS
BEGIN
    IF p_amount < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Amount cannot be negative');
    END IF;
END;
/

6. Advanced Concepts

6.1 Exception Handling

CREATE OR REPLACE PROCEDURE safe_division (
    p_num1 IN NUMBER,
    p_num2 IN NUMBER
)
IS
    v_result NUMBER;
BEGIN
    v_result := p_num1 / p_num2;
    DBMS_OUTPUT.PUT_LINE(v_result);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Division by zero error');
END;
/

6.2 Transaction Control in Procedures

CREATE OR REPLACE PROCEDURE process_transaction
IS
BEGIN
    -- DML operations
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Oracle recommends careful transaction control, especially when procedures are called from applications.

6.3 Overloading Procedures

CREATE OR REPLACE PROCEDURE log_event(p_message IN VARCHAR2) IS
BEGIN
    NULL;
END;
/

CREATE OR REPLACE PROCEDURE log_event(p_message IN VARCHAR2, p_level IN NUMBER) IS
BEGIN
    NULL;
END;
/

7. Debugging Procedures

Techniques

  • DBMS_OUTPUT.PUT_LINE
  • Exception logging
  • Compilation errors via USER_ERRORS
  • Oracle SQL Developer debugger

View Compilation Errors

SELECT line, position, text
FROM user_errors
WHERE name = 'PROCEDURE_NAME';

8. Oracle Data Dictionary Views for Procedures

View NameDescription
USER_PROCEDURESLists procedures owned by user
USER_OBJECTSStatus (VALID / INVALID)
USER_SOURCESource code
USER_ERRORSCompilation errors
ALL_PROCEDURESAccessible procedures

9. Best Practices

  • Use meaningful naming conventions
  • Keep procedures focused on one responsibility
  • Avoid excessive commits
  • Handle exceptions explicitly
  • Use packages for better organization
  • Validate input parameters
  • Avoid hardcoding values

10. Practical Implementation Tips

  • Deploy via version control scripts
  • Use CREATE OR REPLACE
  • Test independently before application integration
  • Monitor invalid objects after deployment

Package-Based Implementation (Procedure + Function)

Oracle strongly recommends using packages for organizing related procedures and functions.

📦 Package Design Overview

Package Name: lab_business_pkg
Contains:

  • One procedure
  • One function

đź“„ Package Specification

CREATE OR REPLACE PACKAGE lab_business_pkg
IS
    PROCEDURE calculate_total_amount (
        p_quantity IN NUMBER,
        p_unit_price IN NUMBER,
        p_total OUT NUMBER
    );

    FUNCTION calculate_discount (
        p_total IN NUMBER
    ) RETURN NUMBER;
END lab_business_pkg;
/

đź“„ Package Body

CREATE OR REPLACE PACKAGE BODY lab_business_pkg
IS

    PROCEDURE calculate_total_amount (
        p_quantity IN NUMBER,
        p_unit_price IN NUMBER,
        p_total OUT NUMBER
    )
    IS
    BEGIN
        p_total := p_quantity * p_unit_price;
    END calculate_total_amount;


    FUNCTION calculate_discount (
        p_total IN NUMBER
    )
    RETURN NUMBER
    IS
    BEGIN
        IF p_total > 1000 THEN
            RETURN p_total * 0.10;
        ELSE
            RETURN p_total * 0.05;
        END IF;
    END calculate_discount;

END lab_business_pkg;
/

đź“Ś Benefits of Package Usage

  • Better modularity
  • Improved performance (memory caching)
  • Clear separation of specification and implementation
  • Enhanced security (private procedures/functions)

Implementation Using SQL Statements

This section shows real usage of procedures and functions with SQL and PL/SQL blocks.

▶️ Calling a Procedure from PL/SQL

DECLARE
    v_total NUMBER;
BEGIN
    lab_business_pkg.calculate_total_amount(
        p_quantity   => 10,
        p_unit_price => 150,
        p_total      => v_total
    );

    DBMS_OUTPUT.PUT_LINE('Total Amount: ' || v_total);
END;
/

Summary

Procedures are ideal for:

  • Performing actions
  • Managing transactions
  • Encapsulating complex logic

They form the backbone of enterprise PL/SQL development.

Disclaimer

All labs, packages, procedures, and functions shown above are strictly for educational purposes.
They use synthetic logic and non-real data and do not represent any real business system.
Always follow your organization’s standards and consult the latest official Oracle documentation before implementing in production environments.

Scroll to Top