mysql advanced part 1: stored procedures and functions

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 without return value;

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 ;

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.

Call stored procedure

call procedure_name() ;

View stored procedure

-- 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;

Delete stored procedure

DROP PROCEDURE  [IF EXISTS] sp_name ；

grammar

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

variable

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 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 ;

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 ;

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='General figure';
  end if;
  select concat('height ', height , 'The corresponding figure type is:',description);
end$

delimiter ;

OUT output

Requirements:

According to the incoming height variable, get the body type of the current height

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$

Little knowledge

@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

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;

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 ;

while Loop

Syntax structure:

while search_condition do

	statement_list
	
end while;

Requirements:

Calculate 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 ;

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.

Note: UNTIL search_condition end condition has no semicolon

Syntax structure:

REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

Requirements:

Calculate 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     //Note: the end condition does not have a semicolon
  end repeat;
  
  select total ;
  
end$


delimiter ;

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   //Alias LOOP 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.

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 ;

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 ;  //Declare cursors to encapsulate the results of the select statement query

OPEN cursor:

OPEN cursor_name ;   //Open the cursor to iterate

FETCH cursor:

FETCH cursor_name INTO var_name [, var_name] ...  //Iterate the cursor and call it once to read a row of data in the cursor

CLOSE cursor:

CLOSE cursor_name ;   //Close cursor

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;  //The current cursor encapsulates the result of the select query
  
  open emp_result;  //Start cursor
  
  fetch emp_result into e_id,e_name,e_age,e_salary;  //Get the data of the cursor and assign it to the variable
  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;  //Close cursor
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;  //If the data cannot be retrieved, the handle will be triggered, which means that when the data cannot be retrieved, has will be triggered_ Data is set to 0, and the statement must be declared after the cursor statement
  
  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 ;

Storage function

Syntax structure:

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

Case:

Define a storage function 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);

Keywords: MySQL

Added by mandukar on Tue, 08 Feb 2022 21:16:52 +0200

Programming VIP

mysql advanced part 1: stored procedures and functions

Overview of stored procedures and functions

Create stored procedure

Call stored procedure

View stored procedure

Delete stored procedure

grammar

variable

if condition judgment

Transfer parameters

case structure

while Loop

repeat structure

loop statement

leave statement

Cursor / cursor

Storage function

Popular Keywords