1. Stored procedures and functions

1.1 overview of stored procedures and functions

Stored procedures and functions are a collection of SQL statements compiled and stored in the database in advance. Calling stored procedures and functions can simplify a lot of work of application developers and reduce the transmission of data between the database and the application server, which is good for improving the efficiency of data processing.

The difference between a stored procedure and a function is that a function must have a return value, while a stored procedure does not.

Function: it is a process with return value;

Procedure: it is a function without return value;

1.2 creating stored procedures

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL sentence
end ;

Example:

delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
delimiter ;

Delimiter: this keyword is used to declare the separator of SQL statement and tell the mysql interpreter whether the command has ended and whether mysql can be executed. By default, delimiter is a semicolon;. In the command-line client, if a command ends with a semicolon, mysql will execute the command after entering.

1.3 calling stored procedures

call procedure_name();

1.4 viewing stored procedures

-- query db_name All stored procedures in the database
select name from mysql.proc where db='db_name';
-- Query the status information of stored procedure
show procedure status;
-- Stored procedure definition of a query
show create procedure test.pro_test1

1.5 deleting stored procedures

DROP PROCEDURE [IF EXISTS] sp_name ；

1.6 grammar

Stored procedures are programmable, which means that variables, expressions and control structures can be used to complete more complex functions.

1.6.1 variables

• DECLARE
  A local variable can be defined through DECLARE. The scope of the variable can only be in the BEGIN... END block.

DECLARE var_name[,...] type [DEFAULT value]

Example:

delimiter $
create procedure pro_test2()
begin
declare num int default 5;
select num+ 10;
end$
delimiter ;

• SET
  SET can be used for direct assignment, and constant or expression can be assigned. The specific syntax is as follows:

SET var_name = expr [, var_name = expr] ...

Example:

DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE NAME VARCHAR(20);
	SET NAME = 'MYSQL';
	SELECT NAME ;
END$

DELIMITER ;

You can also assign values by select ing... into:

DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
	declare countnum int;
	select count(*) into countnum from city;
	select countnum;
END$
DELIMITER ;

1.6.2 if condition judgment

Syntax structure:

if search_condition then statement_list
	[elseif search_condition then statement_list] ...
	[else statement_list]
end if;

Requirements:

According to the defined height variable, determine the body type of the current height
180 And above ----------> Tall and tall
170 - 180 ---------> Standard figure
170 following ----------> General figure

Example:

delimiter $
create procedure pro_test6()
begin
	declare height int default 175;
	declare description varchar(50);
	if height >= 180 then
	set description = 'Tall and tall';
	elseif height >= 170 and height < 180 then
	set description = 'Standard figure';
	else
	set description = 'General figure';
	end if;
	select description ;
end$
delimiter ;

1.6.3 transfer parameters

Syntax format:

create procedure procedure_name([in/out/inout] Parameter name parameter type)
...

IN : This parameter can be used as input, that is, the value needs to be passed in by the caller , default
OUT: This parameter is used as output, that is, it can be used as return value
INOUT: It can be used as input parameter or output parameter

IN - input

Demand: determine the stature type of the current height according to the defined height variable

Example:

delimiter $
create procedure pro_test5(in height int)
begin
	declare description varchar(50) default '';
	if height >= 180 then
	set description='Tall and tall';
	elseif height >= 170 and height < 180 then
	set description='Standard figure';
	else
	set description='General figure';
	end if;
	select concat('height ', height , 'The corresponding figure type is:',description);
end$
delimiter ;

OUT output

Demand: get the body type of the current height according to the incoming height variable

Example:

create procedure pro_test5(in height int , out description varchar(100))
begin
	if height >= 180 then
		set description='Tall and tall';
	elseif height >= 170 and height < 180 then
		set description='Standard figure';
	else
		set description='General figure';
	end if;
end$

Call:

call pro_test5(168, @description)$
select @description$

@description: this kind of variable should be preceded by the "@" symbol in the variable name, which is called the user session variable, which represents the whole session process. It is functional, which is similar to the global variable.

@@global.sort_buffer_size: this kind of variable with "@ @" sign is called system variable

1.6.4 case structure

Syntax structure:

Mode 1 :
CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list] ...
	[ELSE statement_list]
END CASE;

Mode 2 :
CASE
	WHEN search_condition THEN statement_list
	[WHEN search_condition THEN statement_list] ...
	[ELSE statement_list]
END CASE;

Demand: given a month, then calculate the quarter

Example:

delimiter $

create procedure pro_test9(month int)
begin
	declare result varchar(20);
	case
		when month >= 1 and month <=3 then
			set result = 'first quarter ';
		when month >= 4 and month <=6 then
			set result = 'Second quarter';
		when month >= 7 and month <=9 then
			set result = 'Third quarter';
		when month >= 10 and month <=12 then
			set result = 'Fourth quarter';
	end case;
	select concat('The month you entered is :', month , ' , The month is : ' , result) as content ;
	
end$
delimiter ;

1.6.5 while cycle

Syntax structure:

while search_condition do
	statement_list
end while;

Demand: calculate the value from 1 to n

delimiter $
create procedure pro_test8(n int)
begin
	declare total int default 0;
	declare num int default 1;
	while num<=n do
		set total = total + num;
		set num = num + 1;
	end while;
	select total;
end$
delimiter ;

1.6.6 repeat structure

Conditional loop control statement that exits the loop when conditions are met. while is executed only when the conditions are met, and repeat is to exit the loop when the conditions are met.

Syntax structure:

REPEAT
	statement_list
	UNTIL search_condition
END REPEAT;

Demand: calculate the value from 1 to n

Example:

delimiter $
create procedure pro_test10(n int)
begin
	declare total int default 0;
	
	repeat
		set total = total + n;
		set n = n - 1;
		until n=0
	end repeat;
	select total ;
end$
delimiter ;

1.6.7 loop statement

Loop implements a simple loop. The conditions for exiting the loop need to be defined by other statements. Generally, it can be realized by using the LEAVE statement. The specific syntax is as follows:

[begin_label:] LOOP
	statement_list
END LOOP [end_label]

If not in the statement_ Add a statement to exit the loop in the list, and the loop statement can be used to realize a simple dead loop.

1.6.8 leave statement

It is used to exit from the marked process structure. It is usually used with BEGIN... END or LOOP. The following is a simple example of using LOOP and LEAVE to exit the LOOP:

delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
	declare total int default 0;
	ins: LOOP
	IF n <= 0 then
		leave ins;
	END IF;
		set total = total + n;
		set n = n - 1;
	END LOOP ins;
	select total;
END$
delimiter ;

1.6.9 cursor / cursor

Cursors are data types used to store query result sets. Cursors can be used to cycle the result sets in stored procedures and functions. The usage of cursor includes cursor declaration, OPEN, FETCH and CLOSE. The syntax is as follows.

Declaration cursor:

DECLARE cursor_name CURSOR FOR select_statement;

OPEN cursor:

OPEN cursor_name ;

FETCH cursor:

 FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor:

CLOSE cursor_name ;

Example:
Initialization script

create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment 'full name',
age int(11) comment 'Age',
salary int(11) comment 'salary',
primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'Golden King ',55,3800),(null,'White browed Eagle
 king',60,4000),(null,'Green winged bat King',38,2800),(null,'Purple Dragon King',42,1800);

Obtain the data in the cursor through the loop structure:

DELIMITER $
create procedure pro_test12()
begin
	DECLARE id int(11);
	DECLARE name varchar(50);
	DECLARE age int(11);
	DECLARE salary int(11);
	DECLARE has_data int default 1;
	DECLARE emp_result CURSOR FOR select * from emp;
	DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
	
	open emp_result;
	repeat
	fetch emp_result into id , name , age , salary;
	select concat('id by',id, ', name by' ,name , ', age by ' ,age , ', Salary is: ',salary);
	until has_data = 0
	end repeat;
	close emp_result;
end$
DELIMITER ;

1.7 storage function

Syntax structure:

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
	...
END;

Case: define a stored procedure and request the total number of records that meet the conditions;

delimiter $
create function count_city(countryId int)
returns int
begin
	declare cnum int ;
	select count(*) into cnum from city where country_id = countryId;
	return cnum;
end$
delimiter 

Call:

select count_city(1);
select count_city(2);