# What are the advantages of stored procedures?

Following are the advantages of stored procedures:

• Since stored procedures are compiled and stored, whenever you call a procedure the response is quick.

• you can group all the required SQL statements in a procedure and execute them at once.

• Since procedures are stored on the database server which is faster than client. You can execute all the complicated quires using it, which will be faster.

• Using procedures, you can avoid repetition of code moreover with these you can use additional SQL functionalities like calling stored functions.

• Once you compile a stored procedure you can use it in any number of applications. If any changes are needed you can just change the procedures without touching the application code.

• You can call PL/SQL stored procedures from Java and Java Stored procedures from PL/SQL.

### Example

Assume we have created a table named Employees with the following description:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Name     | varchar(255) | YES  |     | NULL    |       |
| Salary   | int(11)      | NO   |     | NULL    |       |
| Location | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

Assume we have a procedure named myProcedure which inserts data into the Employees table as:

Create procedure myProcedure (IN name VARCHAR(30), IN sal INT, IN loc VARCHAR(45))
-> BEGIN
-> INSERT INTO Employees(Name, Salary, Location) VALUES (name, sal, loc);
-> END //

Following JDBC program calls the above mentioned Java stored procedure.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Example {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/testdb";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Preparing a CallableStatement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");
cstmt.setString(1, "Amit");
cstmt.setInt(2, 3000);
cstmt.execute();
cstmt.setString(1, "Kalyan");
cstmt.setInt(2, 4000);
cstmt.setString(3, "Vishakhapatnam");
cstmt.execute();
}
}

### Output

Connection established......

If you verify the contents the Employees table, you can find the newly added rows as shown below:

+-----------+--------+----------------+
| Name      | Salary | Location       |
+-----------+--------+----------------+
| Amit      | 3000   | Hyderabad      |
| Kalyan    | 4000   | Vishakhapatnam |
+-----------+--------+----------------+