[MySql Tutorial] Easily guide you to understand stored procedures and how to use them

What is a stored procedure?

Stored Procedure is a database object that stores complex programs in the database so that they can be called by external programs.

Stored procedures are a set of SQL statements to complete specific functions. They are compiled, created and saved in the database. Users can call them by specifying the name of the stored procedure and giving parameters (when needed).

The concept of stored procedures is very simple, it is code encapsulation and reuse at the database SQL language level.

Advantages of stored procedures:

• Stored procedures can be encapsulated and hide complex business logic.

• Stored procedures can return values ​​and accept parameters.

• A stored procedure cannot be run using the select instruction because it is a subroutine, unlike a view table, data table, or user-defined function.

• Stored procedures can be used for data verification, enforcing business logic, etc.

Disadvantages of stored procedures:

• Stored procedures are often customized for specific On the database, because the supported programming languages ​​are different. When switching to a database system from another manufacturer, the original stored procedures need to be rewritten.

• Performance tuning and writing of stored procedures are limited by various database systems.

Creation and calling of stored procedures

• A stored procedure is a piece of code with a name that is used to complete a specific function.

• Create a stored procedure and save it in the data dictionary of the database.

create procedure 存储过程名称（in|out|inout 参数名称 参数类型，……）
begin
过程体;
end

Create stored procedures

create procedure getStudentCount()
begin
select count(*) as num from student where classid=8;
end

Query, modify and delete stored procedures

1 , Query

Query the status of all stored procedures

show procedure status;

View the status of all stored procedures under the corresponding database

show procedure status where db="数据库名";

View the status of the stored procedures whose names contain Student

show procedure status where name like "%Student%";

Query the detailed code of the stored procedure

show create procedure 过程名;

2. Modify

alter procedure 过程名([过程参数[,…]])过程体；

3. Delete

drop procedure 过程名；

Note: cannot be stored in one storage If you delete another stored procedure during the process, you can only call another stored procedure.

Calling stored procedures

Mysql stored procedures use call and the process name and a bracket. In the brackets, add parameters as needed. The parameters include input parameters and output. Parameters, input and output parameters are called.

call 存储过程名（[过程参数[,...]]）

The above is the detailed content of [MySql Tutorial] Easily guide you to understand stored procedures and how to use them. For more information, please follow other related articles on the PHP Chinese website!