Detailed explanation of creating, calling and managing MySQL stored procedures

Introduction to stored procedures

Why use stored procedures?

MySQL version 5.0 began to support stored procedures.

Most SQL statements are single statements against one or more tables. Not all operations are that simple. Often a complete operation requires multiple statements to complete.

Simply put, a stored procedure is a collection of one or more MySQL statements saved for later use. Think of it as a batch file. Their role is not limited to batch processing though.

The idea of ​​stored procedures is very simple, which is to encapsulate and reuse codes at the database SQL language level.

Advantages of stored procedures

1. Simplify complex operations by encapsulating processing into easy-to-use units;
2. Simplify the management of change. If the table name, column name or business logic changes. Only the code of the stored procedure needs to be changed, and the people who use it will not change their own code;
3. Typically stored procedures help improve the performance of your application. Once the created stored procedure is compiled, it is stored in the database. However, MySQL implements stored procedures slightly differently. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL places it in a cache. MySQL maintains its own cache of stored procedures for each connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise the stored procedure works like a query;
4. Stored procedures help reduce the traffic between the application and the database server because the application does not have to send multiple lengthy SQL statements, but only the name and parameters of the stored procedure;
5. Stored procedures are reusable and transparent to any application. Stored procedures expose the database interface to all applications so that developers do not have to develop functionality that is already supported in stored procedures;
6. Stored programs are safe. A database administrator can grant appropriate permissions to applications that access stored procedures in a database without providing any permissions on the underlying database tables.
   

Disadvantages of stored procedures

1. If you use a large number of stored procedures, the memory usage of each connection that uses those stored procedures will increase significantly. In addition, if you overuse a lot of logical operations in stored procedures, CPU usage will also increase, because the original design of the MySQL database focuses on efficient queries and is not conducive to logical operations;
2. The structure of stored procedures makes it more difficult to develop stored procedures with complex business logic;
3. It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide the ability to debug stored procedures;
4. Developing and maintaining stored procedures is not easy. Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess. This can cause problems during the application development and maintenance phases.

Stored procedures in MySQL

Creating and calling procedures

Create a stored procedure, the code is as follows:

-- Create a stored procedure create procedure mypro(in a int, in b int, out sum int) 
begin 
set sum = a+b; 
end;

The results are as follows

You can also view the process under the "Function" node in the Navicat client, as shown below:

Call the stored procedure, the code is as follows:

call mypro(1,2,@s);-- call the stored procedure select @s;-- display the procedure output results

Operation Results

Stored procedure syntax analysis

• create procedure is used to create a procedure;
• mypro is used to define the procedure name;
• (in a int, in b int, out sum int) represents the parameters of the procedure, where in represents input parameters and out represents output parameters. Similar to the parameters and return values ​​when defining a method in Java;
• Begin and end indicate the beginning and end of the process body, which is equivalent to a pair of curly braces that define a method in Java;
• call is used to call the procedure, @s is the variable used to receive the output parameters of the procedure
  

Parameters of stored procedures

MySQL stored procedure parameters are used in the definition of stored procedures. There are three types of parameters:

• IN input parameter: indicates that the caller passes a value to the procedure (the passed value can be a literal or a variable);
• OUT output parameter: indicates that the procedure passes a value to the caller (can return multiple values) (the output value can only be a variable);
• INOUT input and output parameters: It indicates that the caller passes values ​​to the procedure, and the procedure passes values ​​to the caller (the values ​​can only be variables).

Stored procedures can be divided into four categories based on parameters:

1). A procedure without parameters;

2). The process only has input parameters;

3). A process with only output parameters;

4). A procedure that contains input and output parameters.

variable

Stored procedures in MySQL are similar to methods in Java.

In this case, variables can also be used in stored procedures. The scope of local variables in Java is the method where the variable is located, while the scope of local variables in MySQL is the stored procedure where the variable is located.

Variable Definition

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

declare is used to declare variables;

variable_name indicates the variable name;

datatype is the data type of MySQL;

default is used to declare default values;

For example:

declare name varchar(20) default 'jack'.

Variable Assignment

SET variable name = expression value [,variable_name = expression ...]

Use variables in the stored procedure, the code is as follows

use schooldb;-- Use the schooldb database -- Create a procedure create procedure mypro1()
begin
declare name varchar(20);
set name = 'Qiu Chuji';
select * from studentinfo where studentname = name;
end;
-- Call procedure call mypro1();

Operation Results

Flow Control Statements

if conditional statement

The IF statement contains multiple conditional judgments and executes statements based on the results of TRUE or FALSE , which is similar to the if , else if , else syntax in programming languages.

Define a stored procedure, input an integer, and use an if statement to determine whether it is a positive or negative number. The code is as follows:

-- Create procedure create procedure mypro2(in num int)
begin
if num<0 then -- condition starts selecting 'negative number';
elseif num=0 then
select 'neither positive nor negative';
else
select 'positive number';
end if;-- condition ends end;
-- Call procedure call mypro2(-1);

Operation Results

case conditional statement

case is another conditional statement, similar to choose and when syntax in programming languages. case statement in MySQL has two syntax formats.

Define a stored procedure, input an integer, and use the case statement to determine whether it is a positive or negative number. The code is as follows:

-- Create procedure create procedure mypro3(in num int)
begin
case -- condition starts when num<0 then select 'negative number';
when num=0 then select 'neither positive nor negative';
else select 'positive number';
end case; -- condition ends end;
-- Call procedure call mypro3(1);

Operation Results

Define a stored procedure, input an integer, and use a case statement to determine whether it is 1 or 2. The code is as follows:

-- Create procedure create procedure mypro4(in num int)
begin
case num -- condition starts when 1 then select 'the value is 1';
when 2 then select 'the value is 2';
else select 'neither 1 nor 2';
end case; -- condition ends end;
-- Call procedure call mypro4(3);

Operation Results

Both case syntaxes can implement conditional judgments, but the first is suitable for range value judgments, while the second is suitable for definite value judgments.

While Loop Statement

The usage of while statement is similar to while loop in java .

Define a stored procedure and use a while loop to output the cumulative sum from 1 to 10. The code is as follows:

-- Create procedure create procedure mypro5(out sum int)
begin
declare num int default 0;
set sum = 0;
while num<10 do -- loop starts set num = num+1;
set sum = sum+num;
end while; -- end of loop end;
-- Call procedure call mypro5(@sum);
--Query variable value select @sum;

Operation Results

repeat loop statement

The usage of the repeat statement is similar to do…while statement in java . Both of them execute the loop operation first and then judge the condition. The difference is that the loop operation is executed only when the value of the repeat expression is false , and it stops until the value of the expression is true .

Define a stored procedure and use a repeat loop to output the cumulative sum from 1 to 10. The code is as follows:

-- Create procedure create procedure mypro6(out sum int)
begin
declare num int default 0;
set sum = 0;
repeat-- loop starts set num = num+1;
set sum = sum+num;
until num>=10
end repeat; -- end of loop end;
-- Call procedure call mypro6(@sum);
--Query variable value select @sum;

Operation Results

loop statement

Loop statements are used to repeatedly execute certain statements.

During the execution process, you can use the leave statement or iterate to jump out of the loop, or you can nest judgment statements such as IF .

The leave statement is equivalent to break in Java and is used to terminate the loop;

iterate statement is equivalent to continue in Java, which is used to end the current loop operation and enter the next loop.

Define a stored procedure and use a loop to output the cumulative sum from 1 to 10. The code is as follows:

-- Create procedure create procedure mypro7(out sum int)
begin
declare num int default 0;
set sum = 0;
loop_sum:loop-- loop starts set num = num+1;
set sum = sum+num;
if num>=10 then
leave loop_sum;
end if;
end loop loop_sum; -- end of loop end;
-- Call procedure call mypro7(@sum);
--Query variable value select @sum;

Operation Results

The loop_sum in the code is equivalent to labeling the loop, which facilitates flexible operation when multiple loops are involved.

Stored procedure management

The management of stored procedures mainly includes: displaying procedures, displaying procedure source code, and deleting procedures.

A relatively simple way is to use the navicat client tool for management, just click the mouse, as shown in the following figure:

Displaying stored procedures

SHOW PROCEDURE STATUS;

Displays stored procedures for a specific database

SHOW PROCEDURE status where db = 'schooldb';

Displays the stored procedures of a specific schema, and requires that the stored procedures whose names contain "my" be displayed

SHOW PROCEDURE status where name like '%my%';

Display the source code of the stored procedure "mypro1"

SHOW CREATE PROCEDURE mypro1;

Delete the stored procedure "mypro1"

drop PROCEDURE mypro1;

Summarize

This concludes this article on creating, calling, and managing MySQL stored procedures. For more information about MySQL stored procedures, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!