MySQL Stored Procedures

First, Introduction

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled to perform a particular function, stored in a database, and invoked by the user by specifying the name of the stored procedure and given the parameter (if the stored procedure has parameters).

1. Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.
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 for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.
3. Stored procedures can achieve a faster execution speed. If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.
4. Stored procedures can reduce network traffic. For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.
5. Stored procedures can be used as a security mechanism to make full use of them. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

Ii. stored procedures for MySQL

MySQL does not support stored procedures until 5.0, which makes MySQL a big compromise on applications. Fortunately, MySQL 5.0 has finally started to support stored procedures, which can greatly improve the processing speed of the database, but also improve the flexibility of database programming.

Iii. creating a MySQL stored procedure

CREATE     = {User | Current_User}]    PROCEDURE sp_name ([proc_parameter[,...]])    [Characteristic ...] Routine_body

• Definer: Defaults to the current user, if you specify user_name "@" host_name in the username, MySQL 5.0.3 runs the Save in SQL SECURITY INVOKER mode (definer by default) The storage process needs to have a correspondingEXECUTE权限（存储过程有关的权限有三种：ALTER ROUTINE 编辑或删除存储过程；CREATE ROUTINE 建立存储过程；EXECUTE 运行存储过程 ）
• Sp_name: The name of the stored procedure, which can be modified using the
• Proc_parameter: Parameter list in the format ( [in | Out | INOUT] param type), for example (in param1 int)
• Characteristic: Storage features, including:
  

  characteristic:     ' string '  | LANGUAGE SQL  | [NOT] deterministic  | {CONTAINS SQL | NO SQL | READS SQL DATA | modifies SQL DATA}  | SQL SECURITY {definer | INVOKER}

• Routine_body:sql the contents of the code, you can use the BEGIN ... End to flag the start and end of the SQL code.

To create a stored procedure instance:

// mysql> CREATE PROCEDURE simpleproc (out param1 INT)    , BEGIN       SELECT COUNT (*) into param1 from T;     -END//0 rows affected (0.00  sec) MySQL> delimiter;

This stored procedure name is Simpleproc and has an output parameter param1 type int. The Select statement queries the table into record and stores the variable param1 and enters it.

• DELIMITER: The DELIMITER// and DELIMITER should be noted ;. DELIMITER declares the delimiter meaning, the default MySQL delimiter is; , the above example, if no declaration delimiter is//the compiler executes part of the stored procedure as an SQL statement, resulting in an error. Before the stored procedure definition, the MySQL delimiter is declared AS//(or other characters, such as &&), after the creation is complete, restore the MySQL default delimiter.
• In, out, inout:in indicates that the value of this parameter must be specified when the stored procedure is called, that the value of the parameter modified in the stored procedure cannot be returned; out: The value can be changed inside the stored procedure and can be returned; INOUT: Specified at call and can be changed and returned
  
  • In Parameter instance
    

     mysql > DELIMITER // mysql  >       CREATE PROCEDURE demo_in_parameter (in p_in int  -> BEGIN -> select p_in;        , SET p_in=2  -> select p_in;        -> END; -// Query OK, 0  rows affected ( 0.00   sec) MySQL  > DELIMITER; 

    MySQL > SET @p_in =1;   > Call demo_in_parameter (@p_in);   +------+  | p_in |  +------+  |   1  |   +------+   +------+  | p_in |  +------+  |   2  |   +------+   mysql> SELECT @p_in;   +-------+  | @p_in |  +-------+  |  1    |  +-------+  

    As you can see,p_in is modified in the stored procedure, but does not affect the value of @p_id

  • Out parameter Instance
    

     mysql > DELIMITER // mysql  > CREATE PROCEDURE Demo_out_parameter (out p_out int  ) -> BEGIN -> SELECT P_out;       , SET p_out=2  -> select P_out;       -> END; -// Query OK, 0  rows affected ( 0.00   sec) MySQL  > DELIMITER; 

    MySQL > SET @p_out =1;   > Call sp_demo_out_parameter (@p_out);   +-------+  | p_out |   +-------+  | NULL  |   +-------+   +-------+  | p_out |  +-------+  |   2   |   +-------+   mysql> SELECT @p_out;   +-------+  | p_out |  +-------+  |   2   |  +-------+  

    As you can see, p_out cannot pass inside the stored procedure.

  • INOUT instances
    

     mysql > DELIMITER // mysql  > CREATE PROCEDURE demo_inout_parameter (inout p_inout int  ) -> BEGIN -> select P_inout;       , SET p_inout=2  -> select P_inout;       -> END; -// Query OK, 0  rows affected ( 0.00   sec) MySQL  > DELIMITER; 

    MySQL > SET @p_inout =1;   > Call demo_inout_parameter (@p_inout);   +---------+  | p_inout |  +---------+  |    1    |  +---------+   +---------+  | p_inout |   +---------+  |    2    |  +---------+   > SELECT @p_inout;   +----------+  | @p_inout |   +----------+  |    2     |  

    As can be seen above, p_inout can be stored in and out of the storage process, and the change of P_inout value also affects the value of @p_inout.

• DECLARE: variable definition, formatted as variable_name [, variable_name ...] datatype [DEFAULT value];. For example:
  
  • DECLARE l_int int unsigned default 4000000;
  • DECLARE l_numeric Number (8,2) DEFAULT 9.95;
  • DECLARE l_date date DEFAULT ' 1999-12-31 ';
  • DECLARE l_datetime datetime DEFAULT ' 1999-12-31 23:59:59 ';
• Variable assignment:
  
  • SET variable_name = expression .... For example SET p_inout=2;
  • Use SELECT ... INTO statement, SELECT query column or compound function into variable name from TABLE;. For example, SELECT COUNT (*) into param1 from T;

Iv. modifying MySQL stored Procedures

ALTER PROCEDURE proc_name [characteristic ...] Characteristic:    'string'  |  LANGUAGE SQL  | {CONTAINS SQL | NO SQL | READS SQL DATA | modifies SQL DATA}  | SQL SECURITY {definer | INVOKER}

MySQL stored procedures are only allowed to modify the name (Proc_name) and attributes (characteristic), and the rebuild must be removed if additional content needs to be modified.
To modify a stored procedure instance: (Stored procedure Simpleproc already created above)

mysql> ALTER PROCEDURE simpleproc     , modifies SQL DATA     ,0 rows Affected (0.00

Third, call the MySQL stored procedure

Call Sp_name ();  

• Sp_name: The name of the stored procedure, including the parameters of the stored procedure in parentheses

Iv. viewing MySQL stored Procedures

1. viewing stored procedures
   

   Select  from where db= ' db_name ';

   Or

   Select  from where routine_schema='db_name';

   Or

   where db='db_name';

2. View stored Procedure Details
   

   SHOW CREATE PROCEDURE

V. Delete a MySQL stored procedure

DROP PROCEDURE Proc_name

Delete stored procedures generally use drop procedure, or you can delete stored procedures from a table similar to the previous view.

MySQL Stored Procedures