MySQL database stored procedures and stored functions

MySql database stored procedures and functions

Preface

For MySql database, can you only meet the needs of one database, and then write a lot of SQL statements? Can MySql SQL statements provide a function model like normal programming, by encapsulating the SQL statement for certain business requirements, and then the next time you use it, you can directly call the function? The answer is yes, the stored procedures and stored functions of the database are to solve the problems described above, this article will explainMySql stored procedures and stored functions.

One, the difference between stored procedures and stored functions

Stored procedures and functions are a collection of SQL statements that have been compiled and stored in the database in advance.
The difference between a stored procedure and a function is that the function must have a return value, while the stored procedure does not.

Second, how to create a stored procedure

  • Grammatical structures
delimiter $ --将sql语句结束符号修改为$,这样只有sql遇到$时才开始执行
create procedure 存储过程名(参数列表)
begin
sql语句集合
end$
delimiter ; --将结束符修改为默认的分号 
  • Example
Insert picture description here

Three, how to call the stored procedure

  • grammar
call 存储过程名(参数列表);
  • Example
Insert picture description here

Fourth, how to view the stored procedure

  • grammar
select * from information_schema.routines where routine_schema='数据名' \G;
  • Example
Insert picture description here

Five, how to delete the stored procedure

  • grammar
drop procedure [if exists] 存储过程名;
  • Example
drop procedure if exists pro_t1;
Insert picture description here

Six, the specific syntax of the stored procedure

Stored procedures and functions can be programmed, which means that you can use variables, expressions, control structures and other syntax to complete more complex functions.

1. Variable

1.1 Declare variable syntax

A local variable can be defined by declare, and the scope of the variable can only be in the BEGIN...END block.

declare 变量名[,...] type [default 默认值]
1.2 Assignment syntax to variables
  1. Assign constants or assignment expressions directly
set 变量名=表达式
  • Example: Write a stored procedure for querying which stored procedures in the db1 database.
create procedure show_pro()
begin
declare intro varchar(100);
set intro = 'db1数据库包含以下存储过程:';
select intro;
select routine_name 存储名, routine_type 类型 from information_schema.routines where routine_schema='db1' ;
end$
Insert picture description here


2. Assignment operation through select… into

select 筛选字段(或聚合函数) into 变量名 from 表名;
  • Example: a stored procedure to query how many rows of records are in city
create procedure pro_t2()
begin
declare num int;
select count(*) into num from city;
select num;
end$
Insert picture description here

2. If condition judgment

  • Grammatical structures
if 满足条件 then
执行语句
elseif 满足条件 then
执行语句
else 
执行语句
end if;
  • Example
    According to the defined height variable, determine the body type of the current height;
    180 and above ----------> height 170-180
    ---------> standard body
    below 170- ---------> general figure
create procedure pro_t3()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select description ;
end$
Insert picture description here

3. Passing parameters

  • Grammatical structures
create procedure 存储过程名([in/out/inout] 参数名 参数类型)
...

IN: The parameter can be used as input, that is, the caller needs to pass in the value. The default
OUT: the parameter is used as the output, that is, the parameter can be used as the return value.
INOUT: can be used as an input parameter or an output parameter

  • In type parameter passing example: For the previously created show_pro to view the stored procedures in the db1 database, we can give show_pro an input parameter to view the stored procedures in any database.
create procedure show_pro(in db_name varchar(20))
begin
declare intro varchar(100);
select routine_name 存储名, routine_type 类型 from information_schema.routines where routine_schema=db_name;
end$
Insert picture description here
  • Example of passing parameters of out type:
    Get the body type of the current height according to the height variable passed in
create procedure pro_t4(in height int , out description varchar(100))
begin
if height >= 180 then
set description='高挑';
elseif height >= 170 and height < 180 then
set description='标准';
else
set description='一般';
end if;
end$

Call method

call pro_t4(180,@res);
select @res

A variable with the @ symbol added before the variable is called a user session variable, and this kind of effect affects the entire session.

Insert picture description here

4. case structure

  • Grammatical structures
case 
when 条件 then
执行sql语句
when 条件 then
执行sql语句
...
else
执行sql语句
end case;
  • Example: Determine the body type of the current height according to the incoming height variable
create procedure pro_t5(height int)
begin
declare description varchar(20);
case
when height >= 180 then
set description='高挑';
when height >= 170 and height < 180 then
set description='标准';
else
set description='一般';
end case;
select concat(height,'是',description,'身材') result;
end$
Insert picture description here

5. While loop

  • Grammatical structures
while 满足的条件 do
执行sql语句
end while;
  • Example: Calculate the value added from 1 to n
create procedure pro_t6(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$
Insert picture description here

6. Repeat structure

Do while in repeat structure programming language.

  • grammar
repeat
执行sql语句
until 不满的条件
end repeat;
  • Example: Calculate the value added from 1 to n
create procedure pro_t7(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$
Insert picture description here

7. Loop and leave statements

LOOP implements a simple loop, usually combined with the LEAVE statement to exit the loop.

  • loop syntax structure
[loop标签名:] loop
执行sql语句(sql语句中应该包括,退出循环的命令:leave loop标签名;如果没有将会造成死循环)
end loop [loop标签名];
  • Example: Calculate the value added from 1 to n
create procedure pro_t8(n int)
begin
declare total int default 0;
lb:loop
if n<=0 then
leave lb;
end if;
set total=total+n;
set n=n-1;
end loop lb;
select total;
end$
Insert picture description here

8. Cursor (cursor)

The cursor, also known as the cursor, is used toStore query result setData types in stored procedures and functionsYou can use the cursor to loop through the result set. Similar to collection types in high-level programming languages.
The use of cursor includes cursor declaration, open, fetch and close. The syntax is as follows:

  • Declare cursor
declare 光标名称 cursor for sql语句;
  • Open cursor
open 光标名;
  • Get the cursor (store the current data of the cursor in a variable)
fetch 光标名 into 变量名[,变量名...];
  • Close cursor
close 光标名;

Example:
1. Create an employee table and insert data

create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key(`id`)
)engine=innodb default charset=utf8 ;

The comment is to add explanatory information for each field.

Insert picture description here
insert into 
emp(id,name,age,salary) 
values
(null,'金毛狮王',55,3800),
(null,'白眉鹰王',60,4000),
(null,'青翼蝠王',38,2800),
(null,'紫衫龙王',42,1800);

2. Create a stored procedure to display the information in the emp table by obtaining the data in the cursor.

create procedure pro_t9()
begin
declare id int(10);
declare name varchar(50);
declare age int(10);
declare salary int(10);
declare has_data int default 1; -- 终止循环的条件
-- 定义游标
declare emp_cursor cursor for select * from emp;
-- 如果游标数据为空,则执行sql语句,并退出
declare exit handler for not found set has_data=0;
-- 打开游标
open emp_cursor;
-- repeat循环获取游标中的数据
repeat
-- 获取游标中的数据
fetch emp_cursor into id,name,age,salary;
select concat('id: ',id,' name: ',name,' age: ',age,' salary: ',salary);
until has_data=0
end repeat;
-- 关闭游标
close emp_cursor;
end$
Insert picture description here


Insert picture description here

Seven, storage function

In the sense, the only difference between a stored function and a stored procedure is that a stored function has a return value, while a stored procedure has no return value. However, because the parameters passed by the stored procedure have output parameter types, which can also be used to replace the return value, the stored procedure can completely complete the operation of the stored function.
Syntactically, the specific syntax of a stored function is the same as that of a stored procedure.

  • Create stored function syntax structure
delimiter $ --将sql语句结束符号修改为$,这样只有sql遇到$时才开始执行

create function 存储函数名([参数名 类型,...])
returns type --返回值类型
begin
... 
return 变量名;
end$

delimiter ; --将结束符修改为默认的分号 
  • Syntax structure of calling stored function
select 存储函数名(参数列表);
  • Delete stored function syntax structure
drop function 存储函数名
  • The syntax of viewing stored functions is the same as viewing stored procedures
  • Example: Define a storage function to get the total number of records that meet the conditions
SET GLOBAL log_bin_trust_function_creators = 1;
create function get_count(cid int)
returns int
begin
declare num int;
select count(*) into num from city where country_id=cid;
return num;
end$
Insert picture description here