What is the use of mysql stored procedures?

The role of mysql stored procedures: 1. Simplify complex operations by encapsulating processing in easy-to-use units; 2. Simplify the management of changes; 3. Help improve application performance; 4 , Helps reduce 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. It can enhance the functionality and flexibility of SQL statements, It enables mysql to complete complex judgments and more complex operations; 6. It can improve the security of the database and the integrity of the data, etc.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Introduction to stored procedures

A stored procedure is a set of SQL statements designed to complete a specific function. The purpose of using stored procedures is to pre-write common or complex work with SQL statements and store them with a specified name. This procedure is compiled and optimized and stored in the database server, so it is called a stored procedure. When you need the database to provide the same service as the defined stored procedure in the future, you only need to call "CALL stored procedure name" to automatically complete it.

SQL statements commonly used to operate the database need to be compiled first and then executed when executed. Stored procedures take another approach to executing SQL statements.

A stored procedure is a programmable function that is created and saved in the database. It generally consists of SQL statements and some special control structures. Stored procedures are particularly suitable when you want to perform the same specific function on different applications or platforms.

MySQL 5.0 version did not support stored procedures before, which greatly reduced the application of MySQL. MySQL has supported stored procedures since version 5.0, which not only improves the processing speed of the database, but also improves the flexibility of database programming.

Stored procedures are an important function in the database. Stored procedures can be used to Converting data, migrating data, and making reports is similar to a programming language. Once executed successfully, it can be called at any time to complete specified functional operations.

Using stored procedures can not only improve the efficiency of database access, but also improve the security of database use.

For the caller, the stored procedure encapsulates the SQL statement, and the caller does not need to consider the specific implementation process of the logical function. Just a simple call, it can be called from programming languages ​​such as Java and C#.

The role of stored procedures (advantages)

• Simplify complexity by encapsulating processing in easy-to-use units Operation;

• Simplify the management of changes. If the table name, column name or business logic changes. Only the code of the stored procedure needs to be changed, and those who use it will not change their own code;

• Usually stored procedures help improve the performance of the application. When 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 the stored procedure, MySQL places it in the cache. MySQL maintains its own cache of stored procedures for each connection. If the application uses the stored procedure multiple times in a single connection, use the compiled version, otherwise the stored procedure works like a query;

• Stored procedures help reduce application and database Traffic between servers, because the application does not have to send multiple lengthy SQL statements, but only the name and parameters of the stored procedure;

• can enhance the functionality and flexibility of SQL statements
  

  Stored procedures can be written using flow control statements, which have strong flexibility and can complete complex judgments and more complex operations.

• Improving the security of the database and the integrity of the data

  One way to improve the security of stored procedures is to use them as intermediate components. In the stored procedures, certain Perform related operations on the table, and then provide the stored procedure as an interface to external programs. In this way, external programs cannot directly operate database tables and can only operate corresponding tables through stored procedures. Therefore, security can be improved to a certain extent.

• Make data independent

  The independence of data can achieve the effect of decoupling, that is to say, the program can call stored procedures instead of executing multiple SQL statements. In this case, the stored procedure isolates the data from the user. The advantage is that when the structure of the data table changes, there is no need to modify the program when calling the table. The database administrator only needs to rewrite the stored procedure.

Disadvantages of Stored Procedures

• If you use a large number of stored procedures, then use these stores The memory usage per connection of the process will increase significantly. In addition, if you overuse a large number of logical operations in stored procedures, CPU usage will also increase, because the original design of the MySQL database focused on efficient queries and was not conducive to logical operations;

• The construction of stored procedures makes it more difficult to develop stored procedures with complex business logic;

• 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;

• 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 application development and maintenance phases.

Stored procedures in MySQL

Creating and calling procedures

Create a stored procedure, the code is as follows:

-- 创建存储过程 
create procedure mypro(in a int,in b int,out sum int) 
begin 
set sum = a+b; 
end;

The running results are as follows

You can also view the process under the "Function" node of the Navicat client, as follows As shown in the figure:

#Call the stored procedure, the code is as follows:

call mypro(1,2,@s);-- 调用存储过程 
select @s;-- 显示过程输出结果

Running results

##Stored procedure syntax analysis

• create procedure Used to create procedures;
• mypro is used to define the process name;
• (in a int, in b int, out sum int) represents the parameters of the process, where in represents the input parameters , out represents output parameters. Similar to the formal parameters and return values ​​when defining methods in Java;
• begin and end represent the beginning and end of the process body, equivalent to a pair of Java defining methods Curly brackets;
• call is used to call the process, @s is the variable used to receive the process output parameters

Parameters of stored procedures

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

• IN Input parameters: Indicates that the caller passes a value to the process (the incoming value can be a literal or variable);
• OUT Output parameters: Indicates the process Pass out a value to the caller (can return multiple values) (the outgoing value can only be a variable);
• INOUTInput and output parameters: It means that the caller passes in a value to the process, It also means that the process passes a value to the caller (the value can only be a variable).

Stored procedures can be divided into four categories according to parameters:

1). Procedures without parameters;

2). Procedures with only input parameters;

3). A procedure with only output parameters;

4). A procedure containing input and output parameters.

Variables

The stored procedures in MySQL are similar to the methods in java.

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

Variable definition

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

• declare is used to declare variables;

• variable_name represents the variable name;

• datatype is the data type of MySQL;

• default is used to declare the default value;

• For example:

  declare name varchar(20) default ‘jack’。

Variable assignment

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

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

use schooldb;-- 使用 schooldb 数据库
-- 创建过程
create procedure mypro1()
begin
declare name varchar(20);
set name = '丘处机';
select * from studentinfo where studentname = name;
end;
-- 调用过程
call mypro1();

Running results

Flow control statement

if conditional statement

IF statement contains multiple conditional judgments , execute the statement based on the result being TRUE, FALSE, and if, else if, else## in the programming language # The syntax is similar. Define the stored procedure, input an integer, and use the if statement to determine whether it is a positive or negative number. The code is as follows:

-- 创建过程
create procedure mypro2(in num int)
begin
if num<p>Running results</p><p></p><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/fe178bfb1a0eb7dd09df67d79c722d95-4.png?x-oss-process=image/resize,p_40" class="lazy" alt="What is the use of mysql stored procedures?"></p><h4>
<a id="case__169">case conditional statement </a><strong></strong>
</h4>#case<p> is another conditional statement, similar to <code>choose## in programming languages #, </code>when<code> syntax. The </code>case<code> statement in MySQL has two syntax </code> formats. <code></code>Define the 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: <br></p><pre class="brush:php;toolbar:false">-- 创建过程
create procedure mypro3(in num int)
begin
case -- 条件开始
when numRunning results<p></p><p></p><p>Define the stored procedure, enter an integer, and use the case statement to determine whether it is 1 or 2. The code is as follows: <img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/fcb90e661eadc288d2d1d57364b7978a-5.png?x-oss-process=image/resize,p_40" class="lazy" alt="What is the use of mysql stored procedures?"></p><pre class="brush:php;toolbar:false">-- 创建过程
create procedure mypro4(in num int)
begin
case num -- 条件开始
when 1 then select '数值是 1';
when 2 then select '数值是 2';
else select '不是 1 也不是 2';
end case; -- 条件结束
end;
-- 调用过程
call mypro4(3);

Running results

两种 case 语法都可以实现条件判断，但第一种适合范围值判断，而第二种适合确定值判断。

while 循环语句

while语句的用法和 java中的 while循环类似。

定义存储过程，使用 while 循环输出 1 到 10 的累加和，代码如下所示：

-- 创建过程
create procedure mypro5(out sum int)
begin
declare num int default 0;
set sum = 0;
while num<p>运行结果</p><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/fcb90e661eadc288d2d1d57364b7978a-7.png?x-oss-process=image/resize,p_40" class="lazy" alt="What is the use of mysql stored procedures?"></p><h4>
<a id="repeat__243"></a><strong>repeat 循环语句</strong>
</h4><p><code>repeat</code>语句的用法和 <code>java</code>中的 <code>do…while</code> 语句类似，都是先执行循环操作，再判断条件，区别是 <code>repeat</code>表达<br> 式值为 <code>false</code>时才执行循环操作，直到表达式值为 <code>true</code>停止。</p><p>定义存储过程，使用 repeat 循环输出 1 到 10 的累加和，代码如下所示：</p><pre class="brush:php;toolbar:false">-- 创建过程
create procedure mypro6(out sum int)
begin
declare num int default 0;
set sum = 0;
repeat-- 循环开始
set num = num+1;
set sum = sum+num;
until num>=10
end repeat; -- 循环结束
end;
-- 调用过程
call mypro6(@sum);
-- 查询变量值
select @sum;

运行结果

loop 循环语句

循环语句，用来重复执行某些语句。

执行过程中可使用 leave语句或 iterate 跳出循环，也可以嵌套 IF等判断语句。

• leave语句效果相当于 java 中的 break，用来终止循环；
• iterate语句效果相当于 java 中的 continue，用来结束本次循环操作，进入下一次循环。

定义存储过程，使用 loop 循环输出 1 到 10 的累加和，代码如下所示：

-- 创建过程
create procedure mypro7(out sum int)
begin
declare num int default 0;
set sum = 0;
loop_sum:loop-- 循环开始
set num = num+1;
set sum = sum+num;
if num>=10 then
leave loop_sum;
end if;
end loop loop_sum; -- 循环结束
end;
-- 调用过程
call mypro7(@sum);
-- 查询变量值
select @sum;

运行结果

代码中的 loop_sum 相当于给循环贴个标签，方便多重循环时灵活操作。

存储过程的管理

存储过程的管理主要包括：显示过程、显示过程源码、删除过程。

比较简单的方式就是利用 navicat 客户端工具进行管理，鼠标点击操作即可，如下图所示：

显示存储过程

SHOW PROCEDURE STATUS;

显示特定数据库的存储过程

SHOW PROCEDURE status where db = 'schooldb';

显示特定模式的存储过程，要求显示名称中包含“my”的存储过程

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

显示存储过程“mypro1”的源码

SHOW CREATE PROCEDURE mypro1;

删除存储过程“mypro1”

drop PROCEDURE mypro1;

【相关推荐：mysql视频教程】

The above is the detailed content of What is the use of mysql stored procedures?. For more information, please follow other related articles on the PHP Chinese website!