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!