SQL Stored Procedure-MySQL

SQL Stored Procedure 1 bitsCN.com

1. MySQL stored procedure: a stored procedure is a programmable function that is created and saved in the database. Stored procedures are useful when you want to execute the same functions on different applications or platforms or encapsulate specific functions.

Advantages:

1. stored procedures enhance the functions and flexibility of SQL statements. Stored procedures can be written in a Stream Control mode. with high 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. 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.

4. 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.

II. create a MySQL stored procedure:

(1) format

Format of MySQL stored procedure creation:

Create procedure process name ([process parameter [,...]) procedure [feature...] process body

Example:

Note:

(1) Here we need to note that DELIMITER // and DELIMITER; the DELIMITER means 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 ),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 stored PROCEDURE name ([[IN | OUT | INOUT] parameter name data class...])

IN input parameters: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 parameters:This value can be changed within the stored procedure and can be returned

INOUT input and output parameters:Can be changed and returned.

1. IN parameter example

2. example of the OUT parameter

3,INOUT parameter example

From Song Lixing's column

BitsCN.com