Detailed explanation of the concepts, principles and common usage of MySQL stored procedures

This article uses examples to explain the concepts, principles, and common uses of MySQL stored procedures. Share with you for your reference, 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 are no procedures, only functions.

Procedure: encapsulates several statements. When called, these encapsulated bodies execute functions: a "procedure" with a return value
Summary: A procedure is a function that has no return value

In MySQL:

We encapsulate several SQL statements and give them a name - procedure. We store this procedure in the database - stored procedure

2. Create a stored procedure

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

3. View existing stored procedures

show procedure status

4. Delete the stored procedure

drop procedure procedureName;

5. Calling a stored procedure

call procedureName();

6. The first stored procedure

Note: I have changed the MySQL end identifier to $ here. If you want to know how to set it to $, please refer to my other article: MySQL trigger.

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

Call:

call p1();

Display results:

7. Introducing variables

Stored procedures are programmable, which means that you can use variables, expressions, and control structures to complete complex functions. In a stored procedure, use declare to declare variables:

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 a stored procedure, variables can be used for legal operations in SQL statements, such as +-*/. The variable assignment format is:

set 變量名:= expression

use:

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

Display results:

9. Introduce 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 'Aged';
  else
  select 'Minors';
  end if;
end$

Display results:

10. Passing parameters to stored procedures

In the parentheses of the stored procedure definition, you can declare parameters. The syntax is:

[in/out/inout] 參數名參數類型

use:

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

Display results:

11. Use while loop structure

Requirement: Add 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 three main types of requirements: in, out, and inout: from 1 to N
The input data is the value we give, and the output data is the variable name we give, which is used to load the output variable value.

(1) in type, in is the input row parameter, which can accept 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:

(2) Parameters of out type

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:

(3) inout type parameters

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, @age variable is the actual parameter select @age; --Display the @age variable

Output:

The actual parameter of an inout type variable is also a variable name. This variable serves as both an input variable and an output variable in the stored procedure.

13. 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 'Falling into the sea';
  when 3 then select 'fall on land';
  else select 'I don't know where';
  end case;
end$

Output:

14. Repeat loop structure

Format:

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

Requirement: Add 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:

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.