Stored Procedures in SQL

What is a Stored Procedure?

A Stored Procedure is a precompiled SQL block that is stored in the database and executed as a single unit. It improves performance, ensures reusability, and enhances security by preventing direct access to database tables.

---

Advantages of Stored Procedures

1. Performance Improvement – Since they are compiled once and stored, they run faster than normal queries.
2. Code Reusability – The same procedure can be called multiple times.
3. Security – Users can execute procedures without accessing table data directly.
4. Reduced Network Traffic – Since only procedure calls are sent over the network, data transfer is minimal.
5. Encapsulation of Business Logic – Helps maintain a clean architecture.

---

Creating a Stored Procedure

Example 1: Simple Stored Procedure (MySQL / PostgreSQL / Oracle)

DELIMITER $$

CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT * FROM customers;
END $$

DELIMITER ;

Example 2: Stored Procedure with Input Parameter

CREATE PROCEDURE GetCustomerById(IN cust_id INT)
BEGIN
    SELECT * FROM customers WHERE customer_id = cust_id;
END;

Example 3: Stored Procedure with Input & Output Parameters

CREATE PROCEDURE GetCustomerOrderCount(IN cust_id INT, OUT order_count INT)
BEGIN
    SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = cust_id;
END;

Example 4: Stored Procedure with Transactions

CREATE PROCEDURE TransferFunds(IN from_acc INT, IN to_acc INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
    COMMIT;
END;

---

Calling a Stored Procedure from Java

1. Using JDBC CallableStatement

Steps to Call a Stored Procedure from Java using JDBC:

1. Establish a connection to the database.
2. Create a CallableStatement object.
3. Set input parameters (if any).
4. Execute the stored procedure.
5. Retrieve the results (if applicable).

---

Example 1: Calling a Stored Procedure without Parameters

import java.sql.*;

public class CallStoredProcedure {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             CallableStatement stmt = conn.prepareCall("{CALL GetAllCustomers()}")) {

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Customer ID: " + rs.getInt("customer_id") +
                                   ", Name: " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

Example 2: Calling a Stored Procedure with Input Parameters

import java.sql.*;

public class CallStoredProcWithParams {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             CallableStatement stmt = conn.prepareCall("{CALL GetCustomerById(?)}")) {

            stmt.setInt(1, 101); // Set input parameter
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                System.out.println("Customer Name: " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

Example 3: Calling a Stored Procedure with Input & Output Parameters

import java.sql.*;

public class CallStoredProcWithOutput {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             CallableStatement stmt = conn.prepareCall("{CALL GetCustomerOrderCount(?, ?)}")) {

            stmt.setInt(1, 101); // Input Parameter
            stmt.registerOutParameter(2, Types.INTEGER); // Output Parameter

            stmt.execute(); // Execute Procedure

            int orderCount = stmt.getInt(2); // Retrieve Output Parameter
            System.out.println("Total Orders: " + orderCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

4. Using Spring Boot to Call a Stored Procedure

Method 1: Using @Procedure in Spring Data JPA

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {
    @Procedure(name = "GetCustomerOrderCount")
    int getCustomerOrderCount(@Param("cust_id") int customerId);
}

Method 2: Using EntityManager

@Service
public class CustomerService {
    @Autowired
    private EntityManager entityManager;

    public int getCustomerOrderCount(int customerId) {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("GetCustomerOrderCount")
            .registerStoredProcedureParameter("cust_id", Integer.class, ParameterMode.IN)
            .registerStoredProcedureParameter("order_count", Integer.class, ParameterMode.OUT)
            .setParameter("cust_id", customerId);

        query.execute();
        return (int) query.getOutputParameterValue("order_count");
    }
}

---

Conclusion

• Stored procedures optimize performance and encapsulate business logic.
• They reduce network traffic and enhance security.
• JDBC’s CallableStatement allows executing stored procedures from Java.
• Spring Boot provides different ways to call stored procedures via JPA or EntityManager.