Prerequisites

Before we begin, here are the requirements for following along with this guide:

Database: Oracle Database (since PL/SQL is Oracle-specific).

The syntax, examples, and stored procedures/functions in this tutorial are based on Oracle PL/SQL only.

Other databases (e.g., MySQL, PostgreSQL) have different procedural languages and syntax.

Introduction

PL/SQL (Procedural Language / SQL) is Oracle’s extension of SQL.
It adds programming features (like loops, conditions, procedures, and error handling) on top of SQL. It allows you to combine SQL statements with procedural logic.

PL/SQL Block

A block is a basic unit of code (procedures, functions, and anonymous blocks) that make up the PL/SQL program logical. A PL/SQL block includes three sections: declaration, executable, and exception-handling sections. The executable section is mandatory, while others are optional.

Types of PL/SQL Blocks

• Anonymous Blocks
  • Not stored in the database.
  • Written and executed directly (like test scripts).
• Named Blocks
  • Stored in the database for reuse.
  • Examples: Procedures, Functions, Packages, Triggers.

An anonymous block

A block without a name is an anonymous block. An anonymous block is not saved in the Oracle Database server, so it is just for one-time use. However, PL/SQL anonymous blocks can be useful for testing purposes.

Example

SET SERVEROUTPUT ON; declare p_emp table_emp%rowtype; begin select * into p_emp from table_emp where emp_id='123456'; dbms_output.put_line('Result:'|| p_emp.name || p_emp.emp_id); exception when NO_DATA_FOUND then dbms_output.put_line('No Result:'); end; 

/
The slash (/) executes the block.

Functions or Procedures (named Block)

Functions or Procedures is an example of a named block. A named block is stored in the Oracle Database server and can be reused later.

Create Procedure Example

create or replace procedure getEmpById(id in varchar2) AS p_emp table_emp%rowtype; begin select * into p_emp from table_emp where emp_id=id; dbms_output.put_line('Result:'|| p_emp.name || p_emp.emp_id); end ; 

Execute procedure

begin getEmpById('12345'); end; 

OR

exec getEmpById('12345');

Delete procedure

drop procedure getEmpById;

Create Function Example

 create or replace function getEmpById(id in varchar2) return varchar2 IS p_name varchar2(255); begin select name into p_name from table_emp where emp_id=id; return(p_name); end; 

Execute function

select getEmpById('123456') from dual;

OR

declare num number := &emp_id; e_name varchar2(255); begin e_name := getEmpById(num); dbms_output.put_line('the employee name '|| e_name); end; / 

Delete function

drop function getEmpById;

Calling a Stored Procedure with Spring Data JPA

Stored procedures with Spring Data JPA (or any framework) come with pros and cons. Here’s a balanced view:

@Procedure Annotation

public interface EmployeeRepository extends JpaRepository<Employee, Long> { @Procedure(procedureName = "increase_salary") void increaseSalary(@Param("p_emp_id") Long empId, @Param("p_percent") Double percent); } 

@NamedStoredProcedureQuery

@Entity @NamedStoredProcedureQuery( name = "Employee.increaseSalary", procedureName = "increase_salary", parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_emp_id", type = Long.class), @StoredProcedureParameter(mode = ParameterMode.IN, name = "p_percent", type = Double.class) } ) public class Employee { @Id private Long empId; private String name; } 

Repository:

public interface EmployeeRepository extends JpaRepository<Employee, Long> { @Procedure(name = "Employee.increaseSalary") void increaseSalary(@Param("p_emp_id") Long empId, @Param("p_percent") Double percent); } 

Main Use Cases of PL/SQL Blocks

• Data Processing – update/modify data in bulk.
• Business Logic & Validation – enforce rules beyond SQL constraints.
• Error Handling – gracefully manage runtime errors.
• Automation via triggers – run logic on INSERT/UPDATE/DELETE.
• Reusable Code – stored procedures & functions.
• Batch Jobs & Scheduling – cleanup, reporting, automation tasks.
• Complex Transactions – ensure atomic operations (commit/rollback).

Rule of thumb:

• Use stored procedures for heavy DB operations, bulk processing, and centralized business rules.
• Use JPA queries for simple, CRUD-like operations and portability.