Getting Started with PL/SQL Blocks and Calling Them from Spring Boot JPA

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.