What are the advantages of stored procedures?

The following are the advantages of stored procedures:

• Since stored procedures are compiled and stored, whenever the procedure is called, the response is very quick.

• You can group all required SQL statements in a procedure and execute them immediately.

• Since the procedure is stored on the database server this is faster than the client. You can perform all complex queries using it, which will be faster.

• Using procedures, you avoid duplicating code, and with them you can use additional SQL functionality, such as calling stored functions.

• After you compile a stored procedure, you can use it in any number of applications. If any changes are required, you can simply change the process without touching the application code.

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

Example

Suppose we create 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    |       |
+----------+--------------+------+-----+---------+-------+

Suppose we have a table named myProcedure The procedure, which inserts data into the Employees table, is as follows:

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 //

The following JDBC program calls the above 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.setString(3, "Hyderabad");
      cstmt.execute();
      cstmt.setString(1, "Kalyan");
      cstmt.setInt(2, 4000);
      cstmt.setString(3, "Vishakhapatnam");
      cstmt.execute();
   }
}

Output

Connection established......

If you verify the contents of the Employees table, you can find the newly added rows as follows:

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

The above is the detailed content of What are the advantages of stored procedures?. For more information, please follow other related articles on the PHP Chinese website!