7 MySQL stored procedures and functions

Directory:

1. Overview of stored procedures and functions
2. Preparatory work
3. Stored Procedure Operations
4. Create a stored procedure with parameters
5. View stored Procedures

1. Overview of stored procedures and functions

The stored procedures for MySQL (stored procedure) and functions (stored function) are collectively referred to as stored routines.
Stored procedures and functions are a collection of SQL statements that have been compiled and stored in a database, and calling stored procedures and functions can simplify much of the work of the application developer and reduce the transfer of data between the database and the application server.
The difference between a stored procedure and a function is that the function must have a return value, and the stored procedure does not, the parameters of the stored procedure can use in, out, inout types, and the arguments of the function can only be in type.

2. Preparatory work (in order to improve the independence of the sub-section, the preparatory work will be used repeatedly in the follow-up guidance)

Create tables, mix data

　　

1 Drop Table if existsstudent;2 3 Create TableStudent (4Idint(5) not NULL,5Namevarchar( -),6 Birthday Date7 );8 9 Insert  intoStudentValues(1,'Guo Jing','1990-01-01');Ten Insert  intoStudentValues(2,'Huang Rong','1992-02-02'); One Insert  intoStudentValues(3,'Ling Hu','1993-03-03'); A Insert  intoStudentValues(4,'Dong Fang','1994-04-04');

View Code

3. Stored Procedure Operations

3.1 Creating a stored procedure

Select the SCHOOLDB database and enter the following code in SQL edit:
CREATE PROCEDURE Get_young ()
Begin
SELECT * FROM student where birthday in (select Max (birthday) from student);
end//

(Note change delimiter to//)

　　

3.2 Viewing stored procedures
Show procedure status;

　　
3.3 Calling the stored procedure in the SQL edit box

Call Get_young ();
(Note: Returning an error, it is unclear how to change it, but the stored procedure can be successfully invoked in MySQL Command line client.) ）

　　
3.4 Calling a stored procedure using MySQL Command line client

Click win7 to the bottom left, enter MySQL in the search box, and click Enter MySQL Command line Client.

Call Get_young ();
The call succeeded.

However, it is important to note that in MySQL command line client creation of stored procedures, because the MySQL command line client needs to use a statement to change the delimiter,mysql in the memory delimiter is; , but there is a conflict at the end of the SELECT statement, and you need to modify the delimiter.
Delimiter//
CREATE PROCEDURE Get_old ()
Begin
SELECT * FROM student where birthday in (select min (birthday) from student);
end//
delimiter;

4. Create a stored procedure with parameters

On the MySQL command line, enter
Delimiter//
CREATE PROCEDURE Get_by_year (in year_in varchar (4))
Begin
SELECT * FROM student where year (birthday) =year_in;
End//
delimiter;
Call the stored procedure
Call Get_by_year ();
Returns an error indicating that a parameter needs to be brought in.

　　
Call Get_by_year (1990);
Call succeeded!

5. View stored Procedures

Show procedure status;

　　
6. Delete a stored procedure

drop procedure Get_old;

7 MySQL stored procedures and functions