MySQL 5.0 has supported stored procedures since its release.

A stored procedure is a database object that stores complex programs in the database for invocation by external programs.

A stored procedure is a set of SQL statements designed to accomplish a specific function. It is compiled, created, and saved in the database. Users can call and execute it by specifying the stored procedure's name and providing parameters (when needed).

The idea behind stored procedures is simple: code encapsulation and reuse at the SQL language level of the database.

Advantages

  • Stored procedures can encapsulate and hide complex business logic.
  • Stored procedures can return values and accept parameters.
  • Stored procedures cannot be run using the SELECT command because they are subroutines, unlike views, tables, or user-defined functions.
  • Stored procedures can be used for data validation, enforcing business logic, and so on.

Disadvantages

  • Stored procedures are often customized to specific databases because the supported programming languages differ. When switching to another vendor's database system, existing stored procedures need to be rewritten.
  • The performance tuning and writing of stored procedures are limited by various database systems.

I. Creation and Invocation of Stored Procedures

  • A stored procedure is a named block of code used to accomplish a specific function.
  • Created stored procedures are stored in the database's data dictionary.

Creating 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]

Key syntax in MySQL stored procedures

Declare the statement delimiter; it can be customized:

DELIMITER $$
或
DELIMITER //

Declare the stored procedure:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

Stored procedure start and end symbols:

BEGIN .... END

Variable assignment:

SET @p_in=1

Variable definition:

DECLARE l_int int unsigned default 4000000;

Create MySQL stored procedures and stored functions:

create procedure 存储过程名(参数)

Stored procedure body:

create function 存储函数名(参数)

Example

Create a database and back up a data table for the example operation:

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;

The following is an example of a stored procedure that deletes all matches in which a given player participated:

mysql> delimiter $$　　# Temporarily change the statement delimiter from a semicolon ; to two $$ (can be customized) 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;　　# Restore the statement delimiter to a semicolon

Analysis:By default, a stored procedure is associated with the default database. If you want to create the stored procedure under a specific database, prefix the procedure name with the database name. When defining the procedure, useDELIMITER $$command to change the statement delimiter from a semicolon;temporarily to two$$, so that semicolons used in the procedure body are passed directly to the server and are not interpreted by the client (such as mysql).

Calling the stored procedure:

call sp_name[(传参)];
mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 7 | 1 | 57 | 3 | 0 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 5 rows in set (0.00 sec) mysql> call delete_matches(57); Query OK, 1 row affected (0.03 sec) mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 4 rows in set (0.00 sec)

Analysis:In the stored procedure, a variable p_playerno requiring a parameter is set. When calling the stored procedure, 57 is assigned to p_playerno via the parameter, and then the SQL operations in the stored procedure are executed.

Stored procedure body

  • The stored procedure body contains the statements that must be executed when the procedure is called, such as DML and DDL statements, IF-THEN-ELSE and WHILE-DO statements, DECLARE statements for declaring variables, etc.
  • Procedure body format: starts with BEGIN and ends with END (can be nested)
BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

Note:Each nested block and each statement within it must end with a semicolon. The BEGIN-END block that marks the end of the procedure body (also called a compound statement) does not require a semicolon.

Labeling statement blocks:

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

For example:

label1: BEGIN 　　label2: BEGIN 　　　　label3: BEGIN 　　　　　　statements; 　　　　END label3 ; 　　END label2; END label1

Labels have two purposes:

  • 1. Enhance code readability
  • 2. Certain statements (e.g., LEAVE and ITERATE statements) require labels

II. Parameters of Stored Procedures

MySQL stored procedure parameters are used in the definition of stored procedures. There are three parameter types: IN, OUT, and INOUT, in the form of:

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
  • IN input parameter: indicates that the caller passes a value into the procedure (the input value can be a literal or a variable)
  • OUT output parameter: indicates that the procedure passes a value out to the caller (can return multiple values) (the output value can only be a variable)
  • INOUT input/output parameter: indicates that the caller passes a value into the procedure and that the procedure passes a value out to the caller (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 | +-------+

From the above, it can be seen that p_in is modified in the stored procedure, but it does not affect@p_inthe value of the latter, 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 | +-------+ 　　# Because OUT is an output parameter to the caller and does not accept input parameters, 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 parameter, changing the value of the p_out variable

3. INOUT Input/Output 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 | +----------+ # Called the inout_param stored procedure, which accepts input parameters and also outputs parameters, changing the variable

Note:

1. If the procedure has no parameters, you must still write parentheses after the procedure name. For example:

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

2. Ensure that parameter names are not equal to column names; otherwise, in the procedure body, the parameter name will be treated as a column name.

Suggestions:

  • Use IN parameters for input values.
  • Use OUT parameters for return values.
  • Use INOUT parameters as little as possible.

III. Variables

1. Variable Definition

Local variable declarations must be placed at the beginning of the stored procedure body:

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

Here, datatype is a MySQL data type, such as INT, FLOAT, DATE, VARCHAR(length)

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'; DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

2. Variable Assignment

SET 变量名 = 表达式值 [,variable_name = expression ...]

3. User Variables

Using user variables in the MySQL client:

mysql > SELECT 'Hello World' into @x; mysql > SELECT @x; +-------------+ | @x | +-------------+ | Hello World | +-------------+ mysql > SET @y='Goodbye Cruel World'; mysql > SELECT @y; +---------------------+ | @y | +---------------------+ | Goodbye Cruel World | +---------------------+ mysql > SET @z=1+2+3; mysql > SELECT @z; +------+ | @z | +------+ | 6 | +------+

Using user variables in stored procedures

mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,' World') | +----------------------------+ | Hello World | +----------------------------+

Passing user variables with global scope between stored procedures

mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure); mysql> CALL p1( ); mysql> CALL p2( ); +-----------------------------------------------+ | CONCAT('Last procedure was ',@last_proc | +-----------------------------------------------+ | Last procedure was p1 | +-----------------------------------------------+

Note:

  • 1. User variable names generally begin with @
  • 2. Overusing user variables can make the program difficult to understand and manage

IV. Comments

MySQL stored procedures can use two styles of comments

Two hyphens--: This style is generally used for single-line comments.

C style: Generally used for multi-line comments.

For example:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc1 --nameStored procedure name ->(IN parameter1 INTEGER) -> BEGIN -> DECLARE variable1 CHAR(10); -> IF parameter1 = 17 THEN -> SET variable1 = 'birds'; -> ELSE -> SET variable1 = 'beasts'; -> END IF; -> INSERT INTO table1 VALUES (variable1); -> END -> // mysql > DELIMITER ;

Calling MySQL Stored Procedures

Use CALL followed by your procedure name and parentheses. Inside the parentheses, add parameters as needed. Parameters include input parameters, output parameters, and input/output parameters. For specific calling methods, refer to the examples above.

Querying MySQL Stored Procedures

If we want to know which tables exist under a database, we generally useshowtables;to view them. Then, if we want to view the stored procedures under a database, can we also use this method? The answer is: we can view the stored procedures under a database, but in a different way.

We can use the following statement to query:

selectname from mysql.proc where db='数据库名';

或者

selectroutine_name from information_schema.routines where routine_schema='数据库名';

或者

showprocedure status where db='数据库名';

If we want to know the details of a specific stored procedure, what should we do? Can we also use DESCRIBE table_name to view it just like we do with tables?

The answer is:We can view the details of the stored procedure, but we need to use another method:

SHOW CREATE PROCEDURE 数据库.存储过程名;

to view the details of the current stored procedure.

Modifying MySQL Stored Procedures

ALTER PROCEDURE

Altering a previously specified stored procedure created with CREATE PROCEDURE does not affect related stored procedures or stored functions.

Dropping MySQL Stored Procedures

Dropping a stored procedure is relatively simple, just like dropping a table:

DROPPROCEDURE

Drop one or more stored procedures from the MySQL table.

Control Statements in MySQL Stored Procedures

(1). Variable Scope

Inner variables have higher priority within their scope. When execution reaches END, inner variables disappear. At that point, they are already outside their scope and the variables are no longer visible, because this declared variable can no longer be found outside the stored procedure. However, you can save its value through an OUT parameter or by assigning its value to a session variable.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3() -> begin -> declare x1 varchar(5) default 'outer'; -> begin -> declare x1 varchar(5) default 'inner'; -> select x1; -> end; -> select x1; -> end; -> // mysql > DELIMITER ;

(2). Conditional statements

1. if-then-else statements

mysql > DELIMITER // mysql > CREATE PROCEDURE proc2(IN parameter int) -> begin -> declare var int; -> set var=parameter+1; -> if var=0 then -> insert into t values(17); -> end if; -> if parameter=0 then -> update t set s1=s1+1; -> else -> update t set s1=s1+2; -> end if; -> end; -> // mysql > DELIMITER ;

2. case statement:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3 (in parameter int) -> begin -> declare var int; -> set var=parameter+1; -> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case; -> end; -> // mysql > DELIMITER ; case when var=0 then insert into t values(30); when var>0 then when var<0 then else end case

(3). Loop statements

1. while ···· end while

mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > DELIMITER ;
while 条件 do
    --循环体
endwhile

2. repeat···· end repeat

It checks the result after performing the operation, whereas while checks before executing.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc5 () -> begin -> declare v int; -> set v=0; -> repeat -> insert into t values(v); -> set v=v+1; -> until v>=5 -> end repeat; -> end; -> // mysql > DELIMITER ;
repeat
    --循环体
until 循环条件  
end repeat;

3. loop ·····endloop

The loop loop does not require an initial condition, similar to the while loop, and like the repeat loop, it does not require an ending condition. The leave statement is used to exit the loop.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

4. LABELS:

Labels can be placed before begin, repeat, while, or loop statements. Statement labels can only be used before legal statements. They can break out of a loop, causing the running instructions to reach the final step of the compound statement.

(4). ITERATE iteration

ITERATE restarts a compound statement by referencing its label:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc10 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> if v=3 then -> set v=v+1; -> ITERATE LOOP_LABLE; -> end if; -> insert into t values(v); -> set v=v+1; -> if v>=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

Reference articles:

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html