Shulou(Shulou.com)06/01 Report--

The following will bring you the specific steps of creating stored procedures in mysql. If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the specific steps of creating stored procedures in mysql.

Advantages (why use stored procedures?) :

① encapsulates some highly repetitive operations into a stored procedure, simplifying the calls to these SQL

② batch processing: SQL+ cycle to reduce traffic, that is, "run batch"

③ unified interface to ensure the security of data

Compared with oracle database, the stored procedure of MySQL is relatively weak and less used.

I. creation and invocation of stored procedures

A stored procedure is a piece of code with a name that is used to perform 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_bodyproc_parameter: [IN | OUT | INOUT] param_name typecharacteristic: 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 that deletes all matches played by a given player

Mysql > delimiter $$# change the closing symbol of the statement from the semicolon; temporarily change to two $(which can be custom) mysql > CREATE PROCEDURE delete_matches (IN p_playerno INTEGER)-> BEGIN-> DELETE FROM MATCHES-> WHERE playerno = paired playerno;-> END$$Query OK, 0 rows affected (0.01 sec) mysql > delimiter; # restore the closing symbol of the statement to the semicolon

Parsing:

By default, stored procedures are associated with the default database. If you want to specify that the stored procedure is created under a specific database, prefix the procedure name with the database name.

When defining a procedure, use the DELIMITER $$command to change the closing symbol of the statement from a semicolon to two $$temporarily, so that the semicolon used in the process body is passed directly to the cloud server without being interpreted by the client, such as mysql.

Parameters of the stored procedure

A stored procedure can have 0 or more parameters for the definition of the stored procedure.

There are 3 types of parameters:

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

OUT output parameter: indicates that the procedure sends a value to the caller (multiple values can be returned) (outgoing values can only be variables)

INOUT input and output parameters: indicates that the caller passes a value to the procedure and that the procedure sends a value to the caller (the value can only be a variable)

1. In input parameters

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

As you can see above, p_in is modified in the stored procedure, but does not affect the value of @ p_id, because the former is a local variable and the latter is a global variable.

2. Out output parameters

Mysql > delimiter / / mysql > create procedure out_param (out p_out int)-> begin-> select paired outbound;-> set paired outbound 2;-> select paired outout;-> end-> / / mysql > delimiter; mysql > set @ paired outbound 1: MySQL > call out_param (@ p_out) +-+ | p_out | +-+ | NULL | +-# because out outputs parameters to the caller and does not receive input parameters, the p_out in the stored procedure is null+-+ | p_out | +-+ | 2 | +-+ mysql > select @ p_out +-+ | @ p_out | +-+ | 2 | +-+ # called the out_param stored procedure, output parameters, and changed the value of the p_out variable

3. Inout input parameters

Mysql > delimiter $$mysql > create procedure inout_param (inout p_inout int)-> begin-> select paired inoutout;-> select paired inoutout 2;-> end-> $mysql > delimiter; mysql > set @ paired inoutoutout1 MySQL > call inout_param (@ p_inout) +-+ | p_inout | +-+ | 1 | +-+ + | p_inout | +-+ | 2 | +-+ mysql > select @ p_inout | +-+ | 2 | +-+

# call the inout_param stored procedure, accept the input parameters, also output the parameters, and change the variables

Note:

① if the procedure has no parameters, it must also write parentheses after the procedure name

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

② ensures that the name of the parameter is not equal to the name of the column, otherwise the parameter name is treated as a column name in the process body

It is strongly recommended:

Entering values using the in parameter

The return value uses the out parameter

> inout parameter is used as little as possible.

Read the details of the specific steps of creating stored procedures in mysql above and see if there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.