How to create a stored procedure in mysql

Stored Procedure: A set of programmable functions, which are a set of SQL statements to complete specific functions. They are compiled, created and saved in the database. Users can specify the name of the stored procedure and give parameters (required ) to call execution.

Recommended courses: MySQL Tutorial.

Advantages (Why use stored procedures?):

① Encapsulate some highly repetitive operations into a stored procedure, simplifying Calling these SQL

②Batch processing: SQL loop, reducing traffic, that is, "running batches"

③Unified interface to ensure data security

Compared to oracle As far as databases are concerned, MySQL's stored procedures have relatively weak functions and are rarely used.

1. Creation and calling of stored procedures

　>A stored procedure is a piece of code with a name that is used to complete a specific function.

　>The created stored procedure is saved in the data dictionary of the database.

1. Create a stored procedure

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

#Create a database and back up the data table for example operations

mysql> create database db1;
mysql> use db1;    
mysql> create table PLAYERS as select * from TENNIS.PLAYERS;
mysql> create table MATCHES  as select * from TENNIS.MATCHES;

Example: Create a stored procedure to delete all events in which a given player participates Competition

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;　　#将语句的结束符号恢复为分号

Analysis:

By default, the stored procedure is associated with the default database. If you want to specify that the stored procedure is created under a specific database, then add the database name in front of the procedure name. Make a prefix;

When defining a procedure, use the DELIMITER $$ command to temporarily change the end symbol of the statement from a semicolon; to two $$, so that the semicolon used in the procedure body is directly passed to the server. And will not be interpreted by the client (such as mysql).

2. Parameters of stored procedures

Stored procedures can have 0 or more parameters, which are used for the definition of stored procedures.

3 parameter types:

IN input parameter: indicates that the caller passes a value to the process (the passed value can be a literal or variable)

OUT output parameter: Indicates that the process passes a value to the caller (can return multiple values) (the outgoing value can only be a variable)

INOUT input and output parameters: It not only means that the caller passes a value to the process, but also means that the process passes a value to the caller Or pass out the value (the value can only be a variable)

1. in input parameter

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)    -> begin    -> 　　select p_in;    -> 　　set p_in=2;    ->    select P_in;    -> end$$
mysql> delimiter ;
mysql> set @p_in=1;
mysql> call in_param(@p_in);+------+
| p_in |
+------+
|    1 |
+------+
+------+
| P_in |
+------+
|    2 |
+------+mysql> select @p_in;+-------+
| @p_in |
+-------+
|     1 |
+-------+

# As can be seen from the above, p_in is modified during the stored procedure, but it does not affect @p_id value, because the former is a local variable and the latter is a global variable.

2. Out output parameter

mysql> delimiter //mysql> create procedure out_param(out p_out int)    ->   begin    ->     select p_out;    ->     set p_out=2;    ->     select p_out;    ->   end    -> //mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);+-------+
| p_out |
+-------+
|  NULL |
+-------+　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null+-------+
| p_out |
+-------+
|     2 |
+-------+mysql> select @p_out;+--------+
| @p_out |
+--------+
|      2 |
+--------+　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

3. Inout input parameter

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)    ->   begin    ->     select p_inout;    ->     set p_inout=2;    ->     select p_inout;    ->   end    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+mysql> select @p_inout;+----------+
| @p_inout |
+----------+
|        2 |
+----------+

#The inout_param stored procedure is called, accepting the input parameters, outputting the parameters, and changing the variables

Note:

①If the process has no parameters, you must also write parentheses after the process name

Example: CREATE PROCEDURE sp_name ([proc_parameter[,...] ]) ......

②Make sure that the name of the parameter is not equal to the name of the column, otherwise in the procedure body, the parameter name will be treated as a column name

Strongly recommended:

>Use in parameters for input values;

>Use out parameters for return values;

>Use inout parameters as little as possible.

The above is the detailed content of How to create a stored procedure in mysql. For more information, please follow other related articles on the PHP Chinese website!