1. What is a stored procedure?

Stored procedures and functions are sql statements that have been compiled and stored in the database beforehand. Calling stored procedures can simplify our development. The difference between stored procedures and functions is that functions have return values and stored procedures have no return values.

2. Grammar of stored procedures

// Create stored procedures
delimiter$
create procedure p1()
begin
	Statement Set
end$

show procedure status$

Call stored procedures:

call p1()$

3. Variables, expressions, control statements in stored procedures

Variable definitions:

Format declare variable name variable type [default default default]
Variable assignment:

set variable name: = variable name + 10;

Control statement:

if condition then

Sentence

else

Sentence

end if;

Example:

delimiter$
create procedure p1()
begin
	declare num1 int default 20;
	if num1>10 then 
		set num1:=num1+1;
	else
		set num1:=num1+10;
	end if;
	select * from goods where num =num1;
end$

4. Parametric Transfer of Stored Procedures

Example: Conditional Query

create procedure p2(in num1 int,in name varchar(20))
begin
	if num1 is not null then
		if name is not null then
			select * from goods where num=num1 and name=name;
		else
			select * from goods where num=num1;
		end if;
	else
		if name is not null then
			select * from goods where name=name;
		else
			select * from goods;
		end if;
	end if;
end$

call p3(9,'dog')$

5. Cyclic Conditions of Stored Procedures

Similar to the while statement in java

create procedure p3()
begin
	declare total int default 0;
	declare num int default 0;
	while num<=10 do
		set total:=total+num;
		set num:=num+1;
	end while;
	select total;
end$

6. Output parameters of stored procedures

The output of the previous digital accumulation function has been completed:

delimiter$
create procedure p3(in n int,out total int)
begin
	declare num int default 0;
	set total :=0;
	while num<=n do
		set total:=total+num;
		set num:=num+1;
	end while;
end$

Call stored procedures:

call p3(10,@total)$

select @total$

7. Input and output parameters of stored procedures

create procedure p4(inout num int)
begin
	set num:=num+20;
end$

set @currentNum =10$

call p4(@currentNum)$

Print results:

select @currentNum$

8. case statements for stored procedures

Similar to switch... in java. case... default statement

delimiter$
create procedure p5(in pos int)
begin
	case pos
	when 1 then select 'still flying';
	when 2 then select 'fall in the sea';
	when 3 then select 'in the island';
	else select 'I dont know';
	end case;
end$

9. Repat statements for stored procedures

Similar to do... in java. while() statement

create procedure p6()
begin
	declare i int default 1;
	repeat
		select i;
		set i :=i+1;
	until i>4 end repeat;
end$

10. Cursors for stored procedures

In stored procedures or functions, cursors can be used to cycle the result set, including declare declaration, open OPEN, fetch value FETCH, and close CLOSE.

create procedure p6()
begin
	declare r_gid int;
	declare r_name varchar(20);
	declare r_num int;
	declare getgoods cursor for select gid,name,num from goods;
	open getgoods;
	fetch getgoods into r_gid,r_name,r_num;
	select r_name,r_num;
	close getgoods;
end$