CallableStatement, Stored Procedures, and Functions

1. What is a Stored Procedure?

A stored procedure is a precompiled set of SQL statements that are stored in the database and can be executed as a single unit. It is used to encapsulate reusable logic to simplify complex operations, improve performance, and enforce data integrity.

Key Features:

• Precompiled for faster execution.
• Supports input, output, and input-output parameters.
• Can encapsulate complex business logic.

Types of Parameters:

• IN: Used to pass input values to the procedure.
• OUT: Used to return output values.
• INOUT: Used for both input and output.

2. What is a Function in a Database?

A function is a reusable database object that returns a single value. Functions are often used for calculations or processing data.

Key Features:

• Must return a value.
• Cannot have output parameters.
• Primarily used for computations.

Procedure vs Function:

3. Creating and Executing a Procedure in MySQL

-- Create Procedure
DELIMITER //
CREATE PROCEDURE GetEmployeeCount (OUT count INT)
BEGIN
  SELECT COUNT(*) INTO count FROM employees;
END //
DELIMITER ;

-- Execute Procedure
CALL GetEmployeeCount(@count);
SELECT @count;
      

Calling Procedure Using CallableStatement:

import java.sql.*;

public class CallableStatementExample {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_db", "root", "password")) {
            CallableStatement cs = conn.prepareCall("{CALL GetEmployeeCount(?)}");
            cs.registerOutParameter(1, Types.INTEGER);
            cs.execute();
            int count = cs.getInt(1);
            System.out.println("Employee Count: " + count);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
      

4. Creating and Executing a Function in MySQL

-- Create Function
DELIMITER //
CREATE FUNCTION GetDepartmentCount() RETURNS INT
BEGIN
  DECLARE count INT;
  SELECT COUNT(DISTINCT department) INTO count FROM employees;
  RETURN count;
END //
DELIMITER ;

-- Execute Function
SELECT GetDepartmentCount();
      

Calling Function Using CallableStatement:

import java.sql.*;

public class CallableFunctionExample {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_db", "root", "password")) {
            CallableStatement cs = conn.prepareCall("{? = CALL GetDepartmentCount()}");
            cs.registerOutParameter(1, Types.INTEGER);
            cs.execute();
            int departmentCount = cs.getInt(1);
            System.out.println("Department Count: " + departmentCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
      

5. Statement vs PreparedStatement vs CallableStatement