Shulou(Shulou.com)06/02 Report--

This article mainly introduces "MySQL tutorial how to master the storage process", in daily operation, I believe many people in MySQL tutorial how to master the storage process problem there are doubts, Xiaobian consulted all kinds of information, sorted out simple and easy to use operation methods, hope to answer "MySQL tutorial how to master the storage process" doubts helpful! Next, please follow the small series to learn together!

directory

1. Related concepts of storage process

2. Usage of stored procedures

1) Create syntax for stored procedures

2) Understanding of creation syntax

3. stored procedure with null parameters

4. stored procedure with in pattern

5. Stored procedure with out pattern

6. Stored procedure with inout pattern

7. Case Analysis of Stored Procedure

8. Deletion of stored procedures

9. View information about a stored procedure

10. Storage Process Case Review

1. Related concepts of storage process

What is a storage procedure:

Similar to Java methods, Python functions.

Benefits of using stored procedures:

1. Improve the reusability of code;

2. Simplifying operation;

3, reduce the number of compilations, and reduce the number of connections to the database server, improve efficiency. Note: Usually every time you execute a sql statement, you will connect to mysql server once;

Meaning of stored procedure:

A collection of precompiled SQL statements.

2. Usage of stored procedures 1) syntax for creating stored procedures create procedure stored procedure name (parameter list)begin stored procedure body (a combination of valid sql statements)end2) understanding of creation syntax

① Parameter list: The parameter list consists of 3 parts

parameter mode parameter name Parameter type e.g. in stuname varchar(20)

② Parameter pattern classification

In: This parameter can be used as input, that is, the parameter, which requires the caller to pass in a value.

out: This parameter can be used as output, that is, the parameter can be used as return value.

inout: This parameter can be used as either input or output, i.e. it requires both a passed value and a returned value.

If the stored procedure body is only one sentence, begin/end can be omitted.

At the end of each sql statement in the stored procedure body, a semicolon must be added.

The end of the storage process can be reset with delimiter, otherwise it will cause confusion with ";".

Syntax: delimiter

For example: elimiter $ --means $as the final symbol.

④ Call syntax of stored procedures

call stored procedure name (argument list);

3. stored procedure with null arguments "The entire execution of a stored procedure, preferably in the CMD window"--Create a stored procedure delimiter $create procedure myp()begin insert into admin(username,`password`) values ("tom","1111"),("jerry","2222"), ("jalen","3333"),("rose","4444"),("tonny","5555");end $--Call stored procedure call myp()$--View results. select * from admin$

The results were as follows:

4. stored procedure with in pattern

1) Case: Create a stored process implementation, query the corresponding department name according to the department number.

--Create a stored procedure delimiter $create procedure myp2(in num int)begin select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno where e.deptno=num;end $--invoke stored procedure call myp2(10)$

The results were as follows:

2) Case: Create a stored procedure implementation, whether the user logs in successfully.

The operation is as follows:

--create a stored procedure delimiter $create procedure myp3(in username varchar(10),password varchar(10))begin declare result int; select count(*) into result from admin ad where ad.username=username and ad.password=password; select if(count(*) > 0,"Login successful","Login failed") Login status;end $--Call stored procedure call myp3 ('john ',' 8888');

The results were as follows:

5. Stored procedure with out pattern

1) Case 1: Create a stored procedure to return the corresponding male god name according to the goddess name

--create a stored procedure delimiter $create procedure myp4(in beautyName varchar(20),out boyName varchar(20))begin select b.boyName into boyName from beauty left join boys b on beauty.boyfriend_id=b.id where beauty.name =beautyName;end $--Call #to redefine a variable @boyname to receive the return value boyName. call myp4("Zhao Min",@boyname)$select @boyname$call myp4("Liu Yan",@boyname)$select @boyname$

The results were as follows:

2) Case 2: Create a storage process, according to the goddess name, return the corresponding male god name and male god charm value

--Create a stored procedure delimiter $create procedure myp5(in beautyName varchar(20),out boyName varchar(20),out userCP int)begin select b.boyName,b.userCP into boyName,userCP from beauty left join boys b on beauty.boyfriend_id=b.id where beauty.name =beautyName;end $--Call #to redefine a variable @boyname to receive the return value boyName. call myp5("Zhao Min",@boyname,@usercp)$select @boyname,@usercp$call myp5("Liu Yan",@boyname,@usercp)$select @boyname,@usercp$

The results were as follows:

6. Stored procedures with inout pattern

1) Case 1: Pass in two values, a and b, and eventually both a and b are doubled and returned.

--Create a stored procedure delimiter $create procedure myp6(inout a int ,inout b int)begin --Local variables don't need the @ sign. set a=a*2; set b=b*2;end $--invoke--pay special attention to invoking this block. set @m=10$set @n=20$call myp6(@m,@n)$select @m,@n$

The results were as follows:

7. Case Analysis of Stored Procedure

1) Create a stored procedure or function, pass in the username and password, and insert it into the admin table.

2) Create a storage procedure or function to pass in the goddess number and return the goddess name and goddess phone.

3) Create a stored procedure or function to pass in two goddess birthdays and return the size.

1) Create a stored procedure or function, pass in the username and password, and insert it into the admin table.

--create a stored procedure delimiter $create procedure pro1(in username varchar(20),in userpwd varchar(20))begin insert into admin(username,`password`) values (username,userpwd);end $--invoke stored procedure call pro1("Lu Zhishen","123abc")$select * from admin$

The results were as follows:

2) Create a storage procedure or function to pass in the goddess number and return the goddess name and goddess phone number.

--Create a stored procedure delimiter $create procedure pro2(in id int,out beautyName varchar(20),out beautyPhone varchar(20))begin select beauty.name,beauty.phone into beautyName,beautyPhone from beauty where beauty.id =id; end $--invoke stored procedure call pro2(2,@beautyname,@beautyphone)$select @beautyname,@beautyphone$call pro2(3,@beautyname,@beautyphone)$select @beautyname,@beautyphone$

The results were as follows:

3) Create a stored procedure or function to pass in two goddess birthdays and return the size.

--Create a stored procedure delimiter $create procedure pro3(in date 1 datetime,in date 2 datetime,out result int)begin select datediff(borndate1,borndate2) into result;end $--invoke stored procedure call pro3("1993-8-12",now(),@result)$select @result$

The results were as follows:

8. drop procedure stored procedure name;9. View information about a stored procedure

10. Storage Process Case Review

1) Create a stored procedure or function to pass in the goddess name, return: goddess AND male god, the format of the string

If you send in:

Return: Xiao Zhao AND Zhang Wuji

2) Create a stored procedure or function that queries the records of the beauty table based on the number of entries passed in and the starting index.

1) Create a stored procedure or function that implements a string that passes in the name of the goddess and returns: goddess AND male god, format.

delimiter $create procedure test1(in beautyname varchar(20))begin select concat(beauty.name,"AND",boys.boyName) from beauty left join boys on beauty.boyfriend_id=boys.id where beauty.name =beautyname;end $call test1("Liu Yan")$call test1("Zhao Min")$

The results were as follows:

2) Create a stored procedure or function that queries the records of the beauty table based on the number of entries passed in and the starting index.

delimiter $create procedure test2(in startIndex int,in length int)begin select * from beauty limit startIndex,length;end $--Display 3 records per page--Display page 2 call test2(3,3)$--Display page 3 call test2(6,3)$

The results were as follows:

At this point, the study of "MySQL tutorial how to master stored procedures" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.