catalogue
1. Overview of stored procedures and functions
1. Overview of stored procedures and functions
Stored procedures and functions are a collection of SQL statements that have been 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 with no return value;
2. Create stored procedure
CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) begin -- SQL sentence end ;
Example:
delimiter $ create procedure pro_test1() begin select 'Hello Mysql' ; end$ delimiter ;
Knowledge tips
DELIMITER
This keyword is used to declare the separator of the 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.
3. Call stored procedure
call procedure_name() ;
4. View stored procedures
-- query db_name All stored procedures in the database select name from mysql.proc where db='db_name'; -- Querying the status information of stored procedures show procedure status; -- Query the definition of a stored procedure show create procedure test.pro_test1 \G;
5. Delete stored procedure
DROP PROCEDURE [IF EXISTS] sp_name ；
6. Grammar
Stored procedures are programmable, which means that variables, expressions and control structures can be used to complete more complex functions.
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
For direct assignment, SET can be used to assign constants or expressions. 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 select Into mode for assignment:
DELIMITER $ CREATE PROCEDURE pro_test5() BEGIN declare countnum int; select count(*) into countnum from city; select countnum; END$ DELIMITER ;
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 ----------> Average 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 = 'Average figure';
end if;
select description ;
end$
delimiter ;The call result is:
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
Requirements:
According to the defined height variable, determine the body type of the current height
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='Average figure';
end if;
select concat('height ', height , 'The corresponding figure type is:',description);
end$
delimiter ;OUT output
Requirements:
Get the body type of the current height according to the passed in 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='Average figure';
end if;
end$ Call:
call pro_test5(168, @description)$ select @description$
Little knowledge
@description: this variable should be preceded by the "@" symbol, 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 before it is called system variable
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 II : CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE;
Requirements:
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 ;6.5 while loop
Syntax structure:
while search_condition do statement_list end while;
Requirements:
Calculation from 1 to n Value of
Example:
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 ;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;
Requirements:
Calculation from 1 to n Value of
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 ;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 implemented 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 an exit LOOP statement to the list, and the LOOP statement can be used to implement a simple dead LOOP.
6.8 leave statement
Used to exit from the marked process structure, usually 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 ;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);
-- query emp Data in table, And display them line by line
create procedure pro_test11()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
close emp_result;
end$
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 ; 7. Storage function
Syntax structure:
CREATE FUNCTION function_name([param type ... ]) RETURNS type BEGIN ... END;
Case:
Define a stored procedure to 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);