Menu

Differences Stored Procedure vs Functions

Differences Stored Procedure vs Functions

Stored procedures and functions are both database objects that encapsulate a set of SQL statements to perform a specific task. However, they have some key differences in terms of usage, capabilities, and how they are invoked. Here are the main differences:

Stored Procedure vs Functions

1. Purpose

  • Stored Procedures: Generally used to perform a set of operations such as data modification (INSERT, UPDATE, DELETE) and complex business logic. They can return multiple result sets.
  • Functions: Typically used to perform calculations and return a single value. They are mainly used for computations and data retrieval Stored Procedure vs Functions.

2. Return Type

  • Stored Procedures: Do not return a value directly. Instead, they can return multiple result sets and output parameters.
  • Functions: Must return a single value of a specific data type. The return type is mandatory.

3. Invocation

  •  Stored Procedures: Invoked using the CALL statement.
  •  Functions: Invoked as part of an SQL expression. For example, they can be used in the SELECT statement.

4. Usage in SQL Statements

  • Stored Procedures: Cannot be used in SQL statements like SELECT, WHERE, HAVING, etc.
  • Functions: Can be used in SQL statements wherever an expression is allowed.

5. Parameters

  •  Stored Procedures: Can have input, output, and input-output parameters.
  •  Functions: Can only have input parameters.

6. Transaction Handling

  •  Stored Procedures: Can contain transaction management statements (e.g., COMMIT, ROLLBACK).
  •  Functions: Cannot contain transaction management statements.

Example

Let’s illustrate the differences with a Java example where we call a stored procedure and a function from a MySQL database.

Database Table Creation
Before running the Java code, create a table and a stored procedure and function in your database.

Example
sql
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DOUBLE
);

-- Insert some sample data
INSERT INTO employees (name, salary) VALUES ('Alice', 50000), ('Bob', 60000), ('Charlie', 70000);

-- Stored Procedure to increase salary
DELIMITER //
CREATE PROCEDURE increaseSalary(IN emp_id INT, IN increment DOUBLE, OUT new_salary DOUBLE)
BEGIN
    UPDATE employees SET salary = salary + increment WHERE id = emp_id;
    SELECT salary INTO new_salary FROM employees WHERE id = emp_id;
END //
DELIMITER ;

-- Function to calculate annual salary
DELIMITER //
CREATE FUNCTION annualSalary(emp_id INT) RETURNS DOUBLE
BEGIN
    DECLARE annual_salary DOUBLE;
    SELECT salary * 12 INTO annual_salary FROM employees WHERE id = emp_id;
    RETURN annual_salary;
END //
DELIMITER ;


Java Program Calling Stored Procedure vs Functions

java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StoredProcedureFunctionExample {
    public static void main(String[] args) {
        // JDBC URL, username, and password of MySQL server
        String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
        String username = "root";
        String password = "password";

        // JDBC variables for opening and managing connection
        Connection connection = null;

        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish a connection
            connection = DriverManager.getConnection(jdbcURL, username, password);
            System.out.println("Connected to the database!");

            // Call the stored procedure to increase salary
            CallableStatement callableStatement = connection.prepareCall("{CALL increaseSalary(?, ?, ?)}");
            callableStatement.setInt(1, 1); // Employee ID
            callableStatement.setDouble(2, 5000); // Increment amount
            callableStatement.registerOutParameter(3, java.sql.Types.DOUBLE); // Output parameter
            callableStatement.execute();
            double newSalary = callableStatement.getDouble(3);
            System.out.println("New Salary after increment: " + newSalary);
            callableStatement.close();

            // Call the function to calculate annual salary
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT annualSalary(?)");
            preparedStatement.setInt(1, 1); // Employee ID
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                double annualSalary = resultSet.getDouble(1);
                System.out.println("Annual Salary: " + annualSalary);
            }
            resultSet.close();
            preparedStatement.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.out.println("Disconnected from the database.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Example
‘calculateTotalPrice()’, ‘getUserName()’

Explanation of the Stored Procedure vs Functions

  1. Connection Setup:
  2. The JDBC URL, username, and password are specified to connect to the MySQL database.
  3. The Connection object is created using DriverManager.getConnection().

  • Calling Stored Procedure:
  • CallableStatement callableStatement = connection.prepareCall(“{CALL increaseSalary(?, ?, ?)}”); creates a CallableStatement for calling the stored procedure.
  • Input parameters are set using callableStatement.setInt(1, 1) and callableStatement.setDouble(2, 5000).
  • Output parameter is registered using callableStatement.registerOutParameter(3, java.sql.Types.DOUBLE).
  • The stored procedure is executed using callableStatement.execute().
  • The output parameter is retrieved using callableStatement.getDouble(3).

  • Calling Function:
  • PreparedStatement preparedStatement = connection.prepareStatement(“SELECT annualSalary(?)”); creates a PreparedStatement for calling the Stored Procedure vs Functions.
  • Input parameter is set using preparedStatement.setInt(1, 1).
  • The function is executed using preparedStatement.executeQuery().
  • The result is retrieved using resultSet.getDouble(1).

Homepage

Readmore