This article uses examples to explain the principles and usage of MySQL stored procedures. Share with you for your reference, the details are as follows:

In this article:

• What is a stored procedure
• Creating a stored procedure
• Use of stored procedures
• View stored procedures
• Modify the stored procedure
• Deleting a stored procedure

Release date: 2018-04-17

What is a stored procedure:

• A stored procedure stores a series of SQL statements
• The following is a classic requirement scenario for stored procedures, which is found in many MySQL books:
• A stored procedure stores a series of SQL statements , which simplifies operations and does not require repeated execution of a series of operations . Just call the stored procedure when needed.
• Generally speaking, the functionality of a stored procedure can be considered similar to that of a function (you should have all learned about functions), but just be aware that a stored procedure has no return value, so you can understand a stored procedure based on the scenarios in which a function can be used.

Replenish:

• The difference between stored procedures and triggers: a trigger executes a series of statements when it triggers an event; while a stored procedure is called , and the stored procedure also considers executing "another series of statements" depending on the situation.
• The difference between stored procedures and functions: functions have return values, but stored procedures do not [so they cannot be used in select statements]

Creation of stored procedure:

• create procedure stored procedure name([parameter list]) begin sql statement end;
  • The format of the parameter list is: [type qualified variable name data type]
    • The parameter list has its own type restriction. This type restriction is different from the data type. It limits the scope of the parameter.
      • in: This parameter is limited to being passed to the stored procedure. Since it is passed by value, it can be a variable or constant data [ the parameter modified by in is generally passed to the stored procedure as some conditions and will not be modified by the stored procedure ]
      • out: This parameter is limited to a value passed by the stored procedure. Because there is a value returned, this parameter must be a variable. [The variable modified by out will be assigned a value in the stored procedure so that the changed value can be obtained outside the procedure.]
      • inout: inout is a combination of the above two. It can be used inside the stored procedure and can be modified and used externally. Because it returns a value, this parameter must be a variable.

• In theory, you can use stored procedures to handle any situation where you want concise code, such as when you want to quickly use multiple selects, or when you want to extract multiple values ​​from data and assign them to variables. Therefore, only the usage is given below, and the application scenarios are not described.
  • 1: No parameters are passed in, only certain specific codes are executed
  • 2. Pass in parameters and use them as conditions to execute code
  • 3. Pass in parameters and use them as conditions to execute code, while using variables to get results.
  • [The following call is the calling process]

-- The simplest example create procedure myselect()
begin 
  select @@version;
end;
create procedure getInfo(in mname varchar(15))
begin 
select mname;
end;
call myselect();
call getInfo("lilie");
-- Create procedure getInfo2(in mname varchar(15)) that can obtain the specified content by passing parameters
begin 
select * from student where name = mname;
end;
call getInfo2("lilei");
-- Assign the result to a variable and pass it to the external create procedure getInfo3(in mname varchar(15),out oname varchar(15))
begin 
select name from student where name =mname into oname;
end;
call getInfo3("lilei",@mname);
select @mname;

Replenish:

• Similar to triggers, if you create a stored procedure in command line mode, you need to modify the command terminator.
• There can also be some special options, which are written after ([parameter list]) and before begin
• • comment: is a description of this stored procedure

    create procedure myselect2()
    comment "My series of sql statements"
    begin 
      select * from student;
      select * from class;
    end;
    show create procedure myselect2;

  • There are some other options such as sql security. If you are interested, you can search on Baidu. I won’t explain it here, I’ll just mention this knowledge point.

Use of stored procedures:

• Call stored procedure: call stored procedure name();
• Calling a stored procedure with parameters: call stored procedure name (parameters);
• • For in type, the parameter can be a value or a variable.
  • For out\inout types, the parameter must be a variable
  • All MySQL variables must begin with @.
  • Example: call myselect("lilei",@variable name); Example: call myselect(@variable name,@variable name)

The stored procedure called below is the stored procedure defined in the stored procedure created above:

call myselect();

call getInfo("lilie");

set @mname="lilei";
call getInfo(@mname);

call getInfo3("lilei",@mname);

Use of variables:

• out and inout can modify variables that will be modified by the stored procedure, but this modification will not be successful until the stored procedure call ends [just like if there is a command inside the procedure that modifies it, you can check the variables after this command and find that the global variables have not changed, only the local variables have changed ].
• All MySQL variables must begin with @.
• In a stored procedure, you can use select variable name to use local variables; you can use select @variable name to use global variables;
• The definition and use of specific variables will be explained in another blog post of mine. Hyperlink: MySQL variables

View the stored procedure:

• View the creation statement of the stored procedure: show create procedure stored procedure name;
• View the status of the stored procedure: show procedure status; [The displayed content includes creation time, comments, defined users, security type, etc.]

Modify the stored procedure:

• When modifying a stored procedure, you can only modify those options (the specific options are not explained here, you can search on Baidu if you want to know), and you cannot modify the incoming and outgoing parameters or SQL statements.
• alter procedure stored procedure name option;
• • 

Delete the stored procedure:

• Syntax: drop procedure stored procedure name;
• Example:

• drop procedure getInfo;

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.