MySQL stored procedure and Java stored procedure call

Stored Procedure Overview

The commonly used operating database language SQL statements must be compiled and then executed before execution. The Stored Procedure (Stored Procedure) is a set of SQL statements for specific functions, after compilation, the stored procedure is stored in the database. You can call and execute the stored procedure by specifying the name of the stored procedure and specifying parameters (if the stored procedure has parameters.

A stored procedure is a Programmable function that is created and saved in a database. It can contain SQL statements and some special control structures. Stored procedures are useful when you want to execute the same functions on different applications or platforms or encapsulate specific functions. Stored Procedures in databases can be seen as simulation of object-oriented methods in programming. It allows you to control how data is accessed.

Stored procedures generally have the following advantages:

(1) stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation.

(2) stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time without affecting the application source code.

(3). the stored procedure can achieve fast execution speed. If an operation contains a large number of Transaction-SQL code or is executed multiple times, the stored procedure is much faster than the batch processing. Because the stored procedure is pre-compiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it and provides an execution plan that is finally stored in the system table. The Transaction-SQL statement of batch processing needs to be compiled and optimized each time it is run, which is relatively slow.

(4). stored procedures can reduce network traffic. For operations (such as queries and modifications) on the same database object, if the Transaction-SQL statement involved in this operation is organized by the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly increases network traffic and reduces network load.

(5) stored procedures can be fully utilized as a security mechanism. By limiting the permissions of a stored procedure, the system administrator can restrict the data access permissions to avoid unauthorized users accessing data, this ensures data security.

Create a MySQL Stored Procedure

(1). Format

MySQL stored PROCEDURE creation format: CREATE procedure process name ([process parameter [,...]) PROCEDURE
[Features...] process body

Example:

1. mysql> DELIMITER //

2. mysql> create procedure proc1 (OUT s int)

3.-> BEGIN

4.-> select count (*) INTO s FROM user;

5.-> END

6.-> //

7. mysql> DELIMITER;

Note: (1) DELIMITER // and DELIMITER; DELIMITER indicates the DELIMITER, because MySQL uses ";" as the Separator by default, if we do not declare a DELIMITER, the compiler treats the stored procedure as an SQL statement, and the compilation process of the stored procedure reports an error. Therefore, we must first use the DELIMITER keyword to declare the DELIMITER of the current segment, in this way, MySQL regards ";" as the code in the stored procedure and does not execute the code. After the code is used up, the separator should be restored.

(2) The stored procedure may have input, output, and input and output parameters as needed. Here there is an output parameter s in the type of int. If there are multiple parameters, use ", "Split.

(3) start and END of the Process body are identified by BEGIN and END.

In this way, a MySQL stored procedure is completed, isn't it easy? It doesn't matter if you don't understand it. Next, Let's explain it in detail.

(2). Declare the delimiter

As a matter of fact, the above annotations have been clearly written about the Declaration delimiter. You don't need to say much, but note that if you use the MySQL Administrator management tool, it can be created directly without declaration.

(3). Parameters

MySQL stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, And INOUT. The format is as follows:

Create procedure ([[IN | OUT | INOUT] Parameter Name Data class...])

IN input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter IN the stored procedure cannot be returned, which is the default value.

OUT output parameter: The value can be changed within the stored procedure and can be returned.

INOUT input and output parameters: this parameter is specified during the call and can be changed or returned.

IN parameter example

Create:

1. mysql> DELIMITER //

2. mysql> create procedure demo_in_parameter (IN p_in int)

3.-> BEGIN

4.-> SELECT p_in;/* query input parameters */

5.-> SET p_in = 2;/* modify */

6.-> SELECT p_in;/* view the modified value */

7.-> END;

8.-> //

9. mysql> DELIMITER;

Execution result:

1. mysql> SET @ p_in = 1;

2. mysql> CALL demo_in_parameter (@ p_in );

3. + ------ +

4. | p_in |

5. + ------ +

6. | 1 |

7. + ------ +

8.

9. + ------ +

10. | p_in |

11. + ------ +

12. | 2 |

13. + ------ +

14.

15. mysql> SELECT @ p_in;

16. + ------- +

17. | @ p_in |

18. + ------- +

19. | 1 |

20. + ------- +

As shown above, although p_in is modified in the stored procedure, it does not affect the value of @ p_id.

OUT parameter example

Create:

1. mysql> DELIMITER //

2. mysql> create procedure demo_out_parameter (OUT p_out int)

3.-> BEGIN

4.-> SELECT p_out;/* view output parameters */

5.-> SET p_out = 2;/* modify the parameter value */

6.-> SELECT p_out;/* check for any changes */

7.-> END;

8.-> //

9. mysql> DELIMITER;

Execution result:

1. mysql> SET @ p_out = 1;

2. mysql> CALL sp_demo_out_parameter (@ p_out );

3. + ------- +

4. | p_out |

5. + ------- +

6. | NULL |

7. + ------- +

8./* not defined, return NULL */

9. + ------- +

10. | p_out |

11. + ------- +

12. | 2 |

13. + ------- +

14.

15. mysql> SELECT @ p_out;

16. + ------- +

17. | p_out |

18. + ------- +

19. | 2 |

+ ------- +

INOUT parameter example

Create:

1. mysql> DELIMITER //

2. mysql> create procedure demo_inout_parameter (INOUT p_inout int)

3.-> BEGIN

4.-> SELECT p_inout;

5.-> SET p_inout = 2;

6.-> SELECT p_inout;

7.-> END;

8.-> //

9. mysql> DELIMITER;

 

Execution result:

1. mysql> SET @ p_inout = 1;

2. mysql> CALL demo_inout_parameter (@ p_inout );

3. + --------- +

4. | p_inout |

5. + --------- +

6. | 1 |

7. + --------- +

8.

9. + --------- +

10. | p_inout |

11. + --------- +

12. | 2 |

13. + --------- +

14.

15. mysql> SELECT @ p_inout;

16. + ---------- +

17. | @ p_inout |

18. + ---------- +

19. | 2 |

+ ---------- +

(4). Variables

I. variable definition

DECLARE variable_name [, variable_name...] datatype [DEFAULT value];

1 2 3 Next Page