SQL: Stored Procedure

database

SQL: Stored Procedure

Stored Procedure

A stored procedure is a set of SQL statements with an assigned name, which is stored in a relational DBMS as a group. Therefore, a stored procedure can be reused and shared by multiple programs.

In Java, if we want to reuse a piece of code, we can encapsulate the code as a function/method. Then we can call this method if necessary. Similarly, when we want to reuse SQL statements, we can use stored procedure:

1
2
3
4
5
6
7
8
CREATE PROCEDURE procedure_name
(
IN  parameter_name parameter_type,
OUT parameter_name parameter_type
）
BEGIN
    SQL statements
END;

Demo1

Here is a simple stored procedure without any parameter: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!-- Create stored procedure -->
DELIMITER //
CREATE PROCEDURE employee_avg_salary()
BEGIN
        SELECT AVG(salary) FROM employees;
END //

DELIMITER;

<!-- Call this stored procedure -->
CALL employee_avg_salary(); 

<!-- Delete stored procedure -->
DROP PROCEDURE employee_avg_salary;

Demo2

Here is a simple stored procedure with IN parameter: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!-- Create stored procedure -->
DELIMITER //
CREATE PROCEDURE employee_avg_salary(IN dept_no INT)
BEGIN
    SELECT avg(e.salary) FROM employee e WHERE e.deptno = dept_no;
END //

DELIMITER;

<!-- Call this stored procedure -->
CALL employee_avg_salary(10); 

<!-- Delete stored procedure -->
DROP PROCEDURE employee_avg_salary;

Demo3

Here is a simple stored procedure with IN and OUT parameter: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!-- Create stored procedure -->
DELIMITER //
CREATE PROCEDURE employee_avg_salary(IN dept_no INT, OUT avg_salary DECIMAL(8,2))
BEGIN
    SELECT avg(e.salary) INTO avg_salary FROM employee e WHERE e.deptno=dept_no;
END //

DELIMITER ;

<!-- Call this stored procedure, and the output is stored in @result -->
CALL employee_avg_salary(10, @result); 

<!-- We normally use JDBC to check the content of @result-->
SELECT @result;

<!-- Delete stored procedure -->
DROP PROCEDURE employee_avg_salary;

Using JDBC to Call Stored Procedure

Demo1

1
2
3
4
5
6
7
8
DELIMITER //
CREATE PROCEDURE update_employee_salary(IN empno INT)
BEGIN
    UPDATE employees e SET e.salary = e.salary + 1000 WHERE e.employee_no = empno;
END // 
DELIMITER;

CALL update_employee_salary(9527);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public class ProcedureDemo {
    public void main (String... args) {
        Connection connection = null;
        CallableStatement statement = null;
        try {
            connection = JDBCUtils.getConnection();
            
            // Create stored procedure object
            statement = connection.prepareCall("{call update_employee_salary(?)}");

            // Set the input parameter
            statement.setInt(1, 9527);

            // Call stored procedure
            statement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close resource
            JDBCUtils.closeAll(connection, statement);
        }
    }
}

Demo2

1
2
3
4
5
6
7
8
9
DELIMITER //
CREATE PROCEDURE employee_avg_salary(IN dept_no INT, OUT avg_salary DECIMAL(8,2))
BEGIN
    SELECT avg(e.salary) INTO avg_salary FROM employee e WHERE e.deptno=dept_no;
END //
DELIMITER ;

CALL employee_avg_salary(10, @result); 
SELECT @result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public class ProcedureDemo {
    public void main (String... args) {
        Connection connection = null;
        CallableStatement statement = null;
        try {
            connection = JDBCUtils.getConnection();
            
            // Create stored procedure object
            statement = connection.prepareCall("{call employee_avg_salary(?, ?)}");
            
            statement.setInt(1, 10);// Set the input parameter
            statement.registerOutParameter(2, Types.DECIMAL);// Set the output type

            // Call the stored procedure
            statement.execute();

            // Obtain the @result
            System.out.println(statement.getBigDecimal(2));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close resources
            JDBCUtils.closeAll(connection, statement);
        }
    }
}

The advantages of stored procedure

  1. Security: Stored procedure(i.e., callable statement) can prevent SQL injection. Attackers may enter or change data, but do not write procedures. A stored procedure preserves data integrity because information is entered in a consistent manner.

  2. Statements in a stored procedure only need to be written once.

  3. Use of stored procedures can reduce network traffic between clients and servers, because the commands are executed as a single batch of code. This means only the call to execute the procedure is sent over a network, instead of every single line of code being sent individually.