MySQL Stored Procedure

MySQL Stored Procedure
A storage routine is a set of SQL statements stored on the database server. You can run these SQL statements by calling a specified name in the query.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 stored procedures:

(1 ).Enhanced SQL functions and flexibility: The stored procedure can be written with control statements. It is flexible and can complete complicated judgment and computation.

(2 ).Standard Component Programming: After a stored procedure is created, it can be called multiple times in the program without re-writing the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time without affecting the application source code.

(3 ).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 ). reduce network traffic: operations (such as query and modification) on the same database object. If the Transaction-SQL statement involved in this operation is organized into the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly reduces network traffic and network load.

(5 ). as a security mechanism to make full use of it: by limiting the permissions to execute a stored procedure, you can restrict the access permissions to the corresponding data, this prevents unauthorized users from accessing data and ensures data security.

 

Stored procedures are divided into parameters and Parameters

 

Process body

 

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

 

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:

MySQLSupportedIN(Passed to the stored procedure ),OUT(From the Stored Procedure) andINOUTParameters of the stored procedure. The Stored Procedure Code is located inBEGINAndENDStatement, as previously seen, they are a seriesSELECTStatement, used to retrieve the value, and then save it to the corresponding variable (by specifyingINTOKeyword)

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:Stored Procedure with only 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;

 

OUT:Stored Procedure that only contains Parameters

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

# Stored procedure out parameter 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;

 

INOUT:Stored Procedure for bringing parameters and Output Parameters

Can be changed and returned.

# Stored procedure inout parameter 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;