MySQL stored procedures

Introduction to Stored Procedures

A stored procedure is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).

Stored procedures can be executed by the application through a call, and the user is allowed to declare variables. At the same time, a stored procedure can receive and output parameters, return a state value that executes a stored procedure, or nest calls.

Advantages:

1. Reduce the amount of network traffic. Calling a stored procedure with a low number of rows may not be very different from the network traffic that calls the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, the performance is definitely much higher than a single call to the SQL statement.

2, the execution speed is faster. When the stored procedure is created, the database has been parsed and optimized once. Second, once the stored procedure is executed, a copy of the stored procedure is kept in memory so that the next time the same stored procedure is executed, it can be read directly from memory.

3, stronger security. Stored procedures provide access to specific data and improve code security, such as preventing SQL injection, by granting permissions to users, rather than table-based.

Disadvantages:

1, portability: When migrating from one database to another, many of the stored procedures are written to be partially modified.

2, stored procedures need to spend a certain amount of study time to learn

To create a stored procedure :  

1. No Parameters

CREATE PROCEDUREhelloword () LANGUAGE SQL deterministic SQL SECURITY definer COMMENT'A Procedure'    BEGIN    SELECT 'Hello World!';END1First, after defining the terminator, use the CreatePROCEDURE+stored procedure name method to create a stored procedure, the language option specifies the language used, where SQL is used by default. 2The role of the deterministic keyword is to use this keyword when determining the input and output of each stored procedure is the same, otherwise the default is not deterministic. 3The SQL security keyword is the permission to check the user when the call is invoked.　　When the value is Invoker, it is checked when the user calls the stored procedure, which defaults to Definer, which is when the stored procedure is created. 4The comment section is the comment Description section of the stored procedure. 5in the Begin End section, is the body part of the stored procedure. //To Delete a stored procedureDROP PROCEDURE IF EXISTSHelloword;//Call a stored procedure-----> can also pass parametersCall Helloword ()

View Code

2, with parameters

//stored procedure with parametersCREATE PROCEDURESelectuser (inchUsercodeVARCHAR(255))    BEGIN        Select *  fromTuserwhereUser_code=Usercode; ENDCall Selectuser ('9001');Create procedureUsp_getemployeename (inchUsercodeVARCHAR(255), out nameVARCHAR(255)) begin        SelectRegname intoName fromTuserwhereUser_code=Usercode;EndCall Usp_getemployeename ('9001',@uName);Create procedurePr_param_inout (inout IDint)    begin    SelectId asid_inner_1;--The ID value is the value passed in by the caller    if(ID is  not NULL) Then    SetId=Id+ 1; SelectId asid_inner_2; Else    Select 1  intoID; End if; SelectId asid_inner_3; End;Set @id = Ten; call Pr_param_inout (@id);

View Code

MySQL stored procedures