Back to blog
Databases

Procedures vs Functions in PL/SQL

March 23, 2026

Introduction

PL/SQL allows you to reuse code using procedures and functions.

They look similar but serve different purposes.

Procedures

CREATE OR REPLACE PROCEDURE Intro
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/

Run it: 

EXECUTE Intro;

A procedure performs an action but does not return a value.

Functions

CREATE OR REPLACE FUNCTION tax_cal(p_value IN NUMBER)
RETURN NUMBER IS
BEGIN
   RETURN p_value * 0.09;
END;
/

Use it: 

SELECT tax_cal(50000) FROM dual;

A function returns a value.

Main Difference

  • procedures do something
  • functions return something

When You Would Use Them

Procedure:

  • create or update data
  • log actions
  • enforce rules

Function:

  • calculate values
  • return results
  • use inside queries

Practical Use

Instead of letting users run direct updates on a table, a system can use procedures.

This ensures:

  • only allowed operations are performed
  • rules are always applied
  • behavior stays consistent

Parameters

CREATE OR REPLACE PROCEDURE query_emp (
   p_id IN NUMBER,
   p_name OUT VARCHAR2
)

Parameters allow data to be passed in and out.

Conclusion

Procedures and functions help organize logic and reduce repetition.

They are widely used in real database systems.