PL/SQL Procedures – Syntax, Examples, Benefits & Use Cases

What is a Procedure in PL/SQL?

In PL/SQL, a procedure is a named block of code that is stored in the database. Unlike anonymous blocks, procedures are permanent and reusable. A procedure is designed to perform a specific task or a series of tasks. It may or may not return values, but it always indicates success or failure of execution.

A procedure in Oracle PL/SQL is created once and can be executed many times, either manually or programmatically from other PL/SQL blocks, scripts, or database applications.

Key Features of PL/SQL Procedures

Named Block: Every procedure has a unique name.
Stored in Database: Permanently saved for reuse.
Reusable: Can be called from anywhere within the database session or application.
Optional Parameters: Can take input, output, or both using parameters.
Execution Feedback: Confirms success or failure of execution.
Access Control: Owned by the creator, but access can be granted to others.
Supports Modularity: Promotes code reuse and modular programming.

Syntax of a Procedure

The basic syntax for creating a procedure is as follows:

CREATE OR REPLACE PROCEDURE procedure_name (
   parameter1 [IN | OUT | IN OUT] datatype,
   parameter2 ...
) IS
   -- Variable declarations (if needed)
BEGIN
   -- Executable statements
EXCEPTION
   -- Optional exception handling
END procedure_name;
/

IN: Passes a value into the procedure (read-only).
OUT: Returns a value from the procedure (write-only).
IN OUT: Can both read and modify the value passed.

Example: Procedure to Display Employee Name by ID:

CREATE OR REPLACE PROCEDURE get_employee_name (
   p_emp_id IN NUMBER,
   p_emp_name OUT VARCHAR2
) IS
BEGIN
   SELECT first_name || ' ' || last_name
   INTO p_emp_name
   FROM employees
   WHERE employee_id = p_emp_id;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      p_emp_name := 'Employee not found';
END get_employee_name;
/

How to Call the Procedure:

DECLARE
   v_name VARCHAR2(100);
BEGIN
   get_employee_name(101, v_name);
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/

When to Use Procedures in PL/SQL

Procedures are ideal for tasks that need to be performed repeatedly or are part of your application’s business logic. Common use cases include:

Validating and inserting data
Performing calculations or updates
Automating repetitive tasks (e.g., payroll, monthly reports)
Handling back-end logic in web or desktop applications
Creating APIs inside Oracle databases

Benefits of Using Procedures

Improved Code Reusability: Define once and use across multiple programs.
Security: Access can be controlled via GRANT and REVOKE.
Performance: Precompiled and cached for faster execution.
Modular Design: Keeps the code organized and easier to maintain.
Maintainability: Logic can be updated in one place rather than many scripts.
Debugging: Easier to isolate and fix errors in modular procedures.

Anonymous Block vs Procedure

Aspect	Anonymous Block	Procedure
Definition	Temporary PL/SQL block with no name, not stored in the database.	Named program unit stored permanently in the database.
Name	No name assigned.	Has a unique identifier (name).
Storage	Not stored in the database.	Stored as a database object.
Reusability	Cannot be reused unless saved manually.	Reusable by calling its name.
Scope	Limited to current session.	Accessible throughout the schema or application.
Parameter Support	Does not accept parameters.	Supports IN, OUT, IN OUT parameters.
Compilation	Compiled every time it runs.	Compiled once and stored for reuse.
Call from Other Blocks	Cannot be called from another block.	Can be called from procedures/functions/triggers.
Best Use Case	Testing, scripting, one-time operations.	Reusable business logic and automation.
Error Handling	Optional EXCEPTION block.	Optional EXCEPTION block with better control.
Performance	May be slower due to re-compilation each run.	Better performance via pre-compilation.

FAQ Section

What is a procedure in PL/SQL?

A procedure in PL/SQL is a named block of code stored in the database. It can perform tasks, accept parameters, and be called from other blocks or applications.

How is a procedure different from an anonymous block?

A procedure is stored permanently and reusable, while an anonymous block is unnamed, not stored, and meant for one-time use.

Can I pass parameters to PL/SQL procedures?

Yes, PL/SQL procedures support parameters using IN, OUT, and IN OUT modes to send or receive values between the caller and the procedure.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.