MySQL tutorial thoroughly understands stored procedures

1. Concepts related to stored procedures

What is a stored procedure:
Similar to methods in java and functions in python.

Benefits of using stored procedures:
1. Improve code reusability;
2. Simplify the operation;
3. Reduces the number of compilations and the number of connections to the database server, improving efficiency. Note: Normally, each time a SQL statement is executed, the MySQL server will be connected once;

The meaning of stored procedures:
A collection of precompiled SQL statements.

2. Usage of stored procedures

1) Syntax for creating a stored procedure

create procedure stored procedure name (parameter list)
begin
     Stored procedure body (a set of legal and valid SQL statements)
end

2) Understanding of creation syntax

① Parameter list: The parameter list consists of 3 parts

Parameter mode Parameter name Parameter type For example: in stuname varchar(20)

② Parameter mode classification

in: This parameter can be used as input, that is, the caller needs to pass in a value for this parameter.

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

inout: This parameter can be used as both input and output, that is, the parameter needs to pass in values ​​and can return values.

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

Ⅰ A semicolon is required at the end of each SQL statement in the stored procedure body.
Ⅱ The end of the stored procedure can be reset using the delimiter, otherwise using ";" will cause confusion.
Syntax: delimiter end marker For example: delimiter $ -- means $ is used as the end symbol.

④ Calling syntax of stored procedure

call stored procedure name (actual parameter list);

3. Storage procedure of empty parameters

"The entire execution process of the stored procedure is best executed 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 the stored procedure call myp()$
-- View the results.
select * from admin$

The results are as follows:

4. Stored procedures with in mode

1) Case: Create a stored procedure to query the corresponding department name based on 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 $
-- Call the stored procedure call myp2(10)$

The results are as follows:

2) Case: Create a stored procedure to check whether the user has logged 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 the stored procedure call myp3('john','8888');

The results are as follows:

5. Stored procedures with out mode

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 #Redefine a variable @boyname to receive the return value boyName.
call myp4("Zhao Min",@boyname)$
select @boyname$

call myp4("刘岩",@boyname)$
select @boyname$

The results are as follows:

2) Case 2: Create a stored procedure to return the corresponding male god name and male god charm value according to the goddess name

-- 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 #Redefine a variable @boyname to receive the return value boyName.
call myp5("赵敏",@boyname,@usercp)$
select @boyname,@usercp$

call myp5("刘岩",@boyname,@usercp)$
select @boyname,@usercp$

The results are as follows:

6. Stored Procedure with inout Mode

1) Case 1: Pass in two values, a and b, and finally 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 do not need to be enclosed in @ symbols.
    set a=a*2;
		set b=b*2;
end $
-- Calling -- Pay special attention to the calling part.
set @m=10$
set @n=20$
call myp6(@m,@n)$
select @m,@n$

The results are as follows:

7. Case analysis of stored procedures

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

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

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

1) Create a stored procedure or function to pass in the username and password and insert them 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 $

-- Call the stored procedure call pro1("Lu Zhishen","123abc")$
select * from admin$

The results are as follows:

2) Create a stored 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 $

-- Call the stored procedure call pro2(2,@beautyname,@beautyphone)$
select @beautyname,@beautyphone$

call pro2(3,@beautyname,@beautyphone)$
select @beautyname,@beautyphone$

The results are as follows:

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

-- Create a stored procedure delimiter $
create procedure pro3(in borndate1 datetime,in borndate2 datetime,out result int)
begin
    select datediff(borndate1,borndate2) into result;
end $

-- Call the stored procedure call pro3("1993-8-12",now(),@result)$
select @result$

The results are as follows:

8. Deleting a stored procedure

drop procedure stored procedure name;

9. View the information of a stored procedure

10.Stored procedure case assessment

1) Create a stored procedure or function to pass in the goddess name and return a string of the format: goddess AND male god
If imported: Xiao Zhao
Return: Xiao Zhao AND Zhang Wuji
2) Create a stored procedure or function to query the records of the beauty table based on the number of entries and the starting index passed in.

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

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 are as follows:

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

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 are as follows:

The above is the detailed content of the MySQL series on thoroughly understanding stored procedures. For more information about MySQL stored procedures, please pay attention to other related articles on 123WORDPRESS.COM!