MySQL Stored Procedure

MySQL Stored Procedure

A stored procedure is a set of SQL statements stored on the database server. You can run these SQL statement commands by calling a specified name in the query.

Introduction

The SQL statement needs to be compiled and then executed, while the Stored Procedure (Stored Procedure) is a set of SQL statements to complete specific functions, which are compiled and Stored in the database, you can call and execute a stored procedure by specifying its name and giving a parameter (if the stored procedure has a parameter.

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

Advantages of MySQL Stored Procedure

Usually stored procedures help improve application performance. After a stored procedure is compiled, it is stored in the database. However, the stored procedures in MySQL are slightly different. MySQL stored procedures are compiled as needed. After compiling the stored procedure, MySQL puts it into the cache. MySQL maintains its own high-speed cache for each connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used. Otherwise, the stored procedure works in a similar way to a query.

Stored procedures help reduce traffic between applications and database servers, because applications do not have to send multiple lengthy SQL statements, but can only send Stored Procedure names and parameters.

Stored programs are reusable and transparent to any application. Stored procedures expose database interfaces to all applications so that developers do not have to develop functions that are already supported in stored procedures.

Stored programs are secure. The database administrator can grant appropriate permissions to applications that access stored procedures in the database, without granting any permissions to the basic database tables.

Disadvantages of MySQL Stored Procedure

If a large number of stored procedures are used, the memory usage for each connection using these stored procedures will be greatly increased. In addition, if you excessively use a large number of logical operations in the stored procedure, the CPU usage will also increase because the database server is not designed properly for logical operations.

The construction of stored procedures makes it more difficult to develop stored procedures with complex business logic.

It is difficult to debug the stored procedure. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not support debugging stored procedures.

It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures usually requires a professional skill that is not owned by all application developers. This may cause problems in the application development and maintenance phases.

Process body

Start and END of the Process body are identified by BEGIN and END.

Stored procedures are divided into parameters and Parameters

Stored Procedure without parameters:

DELIMITER ;;
Create procedure 'select _ students_count '()
BEGIN
SELECT count (id) from students;
END ;;
DELIMITER;
Call select_students_count ();

Stored Procedure with parameters:

MySQL supports parameters of the in, OUT, And INOUT types for the stored procedure. The Stored Procedure Code is located in the BEGIN and END statements. As previously seen, they are a series of SELECT statements used to retrieve values and save them to corresponding variables (by specifying the INTO keyword)

Stored Procedures may have input, output, and input/output parameters as needed. If multiple parameters exist, separate them. MySQL stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, And INOUT.

IN: The stored procedure that only contains parameters.

The parameter value must be specified when the stored procedure is called. The value of modifying this parameter in the stored procedure cannot be returned, which is the default value.

DELIMITER //
Create procedure in_param (IN p_in int)
BEGIN
SELECT p_in;
SET p_in = 2;
SELECT p_in;
END //
DELIMITER;
# Call
SET @ p_in = 1;
CALL in_param (@ p_in );
SELECT @ p_in;

Result:

OUT: A stored procedure that only contains parameters.

This value can be changed within the stored procedure and can be returned

# Stored Procedure OUT parameters
DELIMITER //
Create procedure out_param (OUT p_out int)
BEGIN
SELECT p_out;
SET p_out = 2;
SELECT p_out;
END //
DELIMITER;
# Call
SET @ p_out = 1;
CALL out_param (@ p_out );
SELECT @ p_out;

Result:

INOUT: stored procedure for bringing in and out parameters

Can be changed and returned.

# Stored Procedure INOUT Parameters
DELIMITER //
Create procedure inout_param (INOUT p_inout int)
BEGIN
SELECT p_inout;
SET p_inout = 2;
SELECT p_inout;
END //
DELIMITER;
# Call
SET @ p_inout = 1;
CALL inout_param (@ p_inout );
SELECT @ p_inout;

Result:

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151073.htm