Simple use of MySQL stored procedure _ MySQL

Simple use of the MySQL stored procedure bitsCN.com

Simple use of MySQL stored procedures

MySQL5.0 introduces the concept of stored procedures. Stored procedures can improve execution efficiency and facilitate maintenance.

Before there is no procedure concept, the common business logic SQL code is sometimes very long, and they are usually embedded in the host language (such as Java), which is very difficult to maintain, with stored procedures, they are defined on the database end and pre-compiled. In addition, the business logic SQL code does not need to be transmitted over the network, reducing the network burden. It can be said that the advantages of stored procedures are greater than their disadvantages. Www.bitsCN.com

The following is a simple addition stored procedure.

[SQL]

Mysql> delimiter $

Mysql> create procedure pr_add (a int, B int)

-> Begin

-> Declare c int;

-> If a is null then

-> Set a = 0;

-> End if;

-> If B is null then

-> Set B = 0;

-> End if;

-> Set c = a + B;

-> Select c as sum;

-> End;

-> $

Run the stored procedure in command line mode: www.bitsCN.com

[SQL]

Mysql> delimiter;

Mysql> set @ a = 10;

Query OK, 0 rows affected (0.00 sec)

Mysql> set @ B = 20;

Query OK, 0 rows affected (0.00 sec)

Mysql> call pr_add (@ a, @ B );

+ ------ +

| Sum |

+ ------ +

| 30 |

+ ------ +

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Execute the stored procedure on the client:

[Java]

Public class ProcedureTest {

Private static final String DB_URL = "jdbc: mysql: // localhost: 3306/zjut ";

Private static final String DRIVER = "com. mysql. jdbc. Driver ";

Private static final String USER = "root ";

Private static final String PASSWORD = "XXXXX ";

Private static String SQL = "call pr_add (?,?) ";

Public static void main (String [] args ){

Try {

Class. forName (DRIVER );

Connection conn = DriverManager. getConnection (DB_URL, USER, PASSWORD );

CallableStatement cstmt = conn. prepareCall (SQL );

Cstmt. setInt (1, 10 );

Cstmt. setInt (2, 20 );

ResultSet rs = cstmt.exe cuteQuery ();

While (rs. next ()){

System. out. println (rs. getInt ("sum "));

}

} Catch (ClassNotFoundException e ){

E. printStackTrace ();

} Catch (SQLException e ){

E. printStackTrace ();

}

}

}

The key code is:

[Java]

CallableStatement cstmt = conn. prepareCall ("call pr_add (?,?) ");

Cstmt. setInt (1, 10 );

Cstmt. setInt (2, 20 );

ResultSet rs = cstmt.exe cuteQuery ();

Output:

30

Principle: first create an SQL string, that is, "call pr_add (?,?) ", Call the stored procedure with the call command followed by the stored procedure name, two question marks are placeholders.

Use the Connection object to create a CallableStatement object, that is, conn. prepareCall (SQL). This method accepts a string representing an SQL statement.

Execute the SQL statement using the executeQuery () of the CallableStatement object and return a ResultSet object.

Use the ResultSet object to traverse the data.

BitsCN.com