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

AspectAnonymous BlockProcedure
DefinitionTemporary PL/SQL block with no name, not stored in the database.Named program unit stored permanently in the database.
NameNo name assigned.Has a unique identifier (name).
StorageNot stored in the database.Stored as a database object.
ReusabilityCannot be reused unless saved manually.Reusable by calling its name.
ScopeLimited to current session.Accessible throughout the schema or application.
Parameter SupportDoes not accept parameters.Supports IN, OUT, IN OUT parameters.
CompilationCompiled every time it runs.Compiled once and stored for reuse.
Call from Other BlocksCannot be called from another block.Can be called from procedures/functions/triggers.
Best Use CaseTesting, scripting, one-time operations.Reusable business logic and automation.
Error HandlingOptional EXCEPTION block.Optional EXCEPTION block with better control.
PerformanceMay be slower due to re-compilation each run.Better performance via pre-compilation.

FAQ Section

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.

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

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.

Facebooktwitterredditpinterestlinkedinmailby feather
Scroll to Top