The concept, principle and common usage of MySQL stored procedure

Time:2021-5-3

This paper introduces the concept, principle and common usage of MySQL stored procedure. The details are as follows:

1. The concept of stored procedure

In some languages, such as Pascal, there is a concept called “procedure” and “function”. In PHP, there is no procedure, only function.

Procedure: encapsulates several statements, which are executed when called
Function: a procedure with a return value
Summary: a procedure is a function with no return value

In MySQL:

We encapsulate a number of SQL and name them process
Store this procedure in a database — stored procedure

2. Create stored procedure

create procedure procedureName()
begin
  //--SQL statement
end$

3. View existing stored procedures


show procedure status

4. Delete stored procedure


drop procedure procedureName;

5. Calling stored procedure


call procedureName();

6. The first stored procedure

Note: I have changed the ending identifier of Mysql to $. If you want to know how to set it to $, please refer to another article: MySQL trigger.


create procedure p1()
begin
  select 2+3;
end$

Call:


call p1();

Display results:

这里写图片描述

7. Introducing variables

Stored procedure is programmable, which means that variables, expressions and control structures can be used to complete complex functions

Declare variable name variable type [default default value]

use:

create procedure p2()
begin
  declare age int default 18;
  declare height int default 180;
  Select concat ('age ','age','height ','height');
end$

Display results:

这里写图片描述

8. Introducing expressions

In stored procedures, variables can be operated legally in SQL statements, such as + – * /. Variable assignment form:

Set variable name: = expression

use:

create procedure p3()
begin
  declare age int default 18;
  set age := age + 20;
  Select concat ('age after 20 years','age ');
end$

Display results:

这里写图片描述

9. Introducing selection control structure

Format:


if condition then
  statement
elseif
  statement
else
  statement
end if;

use:

create procedure p4()
begin
  declare age int default 18;
  if age >= 18 then
  Select 'adult';
  else
  Select 'minor';
  end if;
end$

Display results:

这里写图片描述

10. Passing parameters to stored procedures

In the brackets that define stored procedures, you can declare parameters

[in / out / inout] parameter name parameter type

use:

create procedure p5(width int,height int)
begin
  Select concat ('your area is: ', width * height) as area;
  if width > height then
    You are fat;
  elseif width < height then
    You are thinner;
  else
  Select 'you compare';
  end if;
end$

Display results:

这里写图片描述

11. Using the while loop structure

Demand: from 1 to 100

use:


create procedure p6()
begin
  declare total int default 0;
  declare num int default 0;
  while num <= 100 do
    set total := total + num;
    set num := num + 1;
  end while;
  select total;
end$

Display results:

这里写图片描述

12. Input and output types of stored procedure parameters

There are mainly three types: in, out and inout
Demand: from 1 to n
The data of input type is the value given by us, and the data of output type is the variable name given by us, which is used to multiply the output value.

(1) In type, where in is the input line parameter, it can receive our input


create procedure p7(in n int)
begin
  declare total int default 0;
  declare num int default 0;
  while num <= n do
    set total := total + num;
    set num := num + 1;
  end while;
  select total;
end$

Call:


call p7(100);

Output results:

这里写图片描述

(2) Parameters of type out


create procedure p8(in n int,out total int)
begin
  declare num int default 0;
  set total := 0;
  while num <= n do
    set total := total + num;
    set num := num + 1;
  end while;
end$

Call:

call p8( 100,@total ); -- 100 is an input parameter and @ total is an output variable
select @total; -- Output @ total variable

Output results:

这里写图片描述

(3) Parameters of type inout


create procedure p9(inout age int)
begin
  set age := age+20;
end$

Call:

set @age = 18; -- Set the @ age variable to 18
call p9(@age); -- Call the P9 stored procedure with the @ age variable as the argument
select @age; -- Display @ age variable

Output results:

这里写图片描述

The argument of an inout variable is also a variable name, which is used as both an input variable and an output variable in a stored procedure.

13. The usage of case structure

use:

create procedure p10()
begin
  declare pos int default 0;
  set pos := floor(5*rand());
  case pos
  When 1 then select 'still flying';
  When 2 then select 'fall in the sea';
  When 3 then select 'land';
  I don't know where it is;
  end case;
end$

Output results:

这里写图片描述

14. Repeat loop structure

Format:


[begin_label:] REPEAT
  statement_list
UNTIL search_condition
END REPEAT [end_label]

Demand: from 1 to 100


create procedure p11()
begin
  declare total int default 0;
  declare num int default 0;
  r:repeat
    set total:= total + num;
  set num:=num + 1;
  until num > 100
  end repeat r;
  select total;
end$

Output results:

这里写图片描述

For more information about mysql, readers interested in it can see the topics on this website: skills of MySQL stored procedures, summary of MySQL common functions, operation skills of MySQL log, summary of operation skills of MySQL transaction and summary of relevant skills of MySQL database lock

I hope this article will be helpful to your MySQL database design.

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]