mysql手册05_存储过程和存储函数

蔚落 2022-11-27 10:11 129阅读 0赞

**mysql手册05\_存储过程和存储函数**

--------------------

存储过程和存储函数是存储在数据库中的一段**SQL语句集合**。  
通过调用存储过程或存储函数，可以简化开发，减少数据在数据库和服务器间的传输。  
存储过程和存储函数的区别是：**存储函数有返回值**。

**基本语句：**

注：在创建存储过程时，为了避免冲突，需要临时修改语句结束符号：
    delimiter $
    
    创建存储过程：
    delimiter $
    
    create procedure pro_test1() 
    begin 
    select 'Hello MySQL'; 
    end$
    
    delimiter ;
    
    调用存储过程：
    call pro_test1();
    
    +-------------+
    | Hello MySQL |
    +-------------+
    | Hello MySQL |
    +-------------+
    
    查看存储过程的两种方法：
    select name from mysql.proc where db = "demo01"$
    show procedure status\G;
    
    查看存储过程的定义/封装语句：
    show procedure pro_test1\G;
    
    删除存储过程：
    drop procedure pro_test1;

**变量：**

declare定义变量和set设置变量值：
    
    create procedure pro_test1() 
    begin 
    declare num int default 10;
    set num = num+10;
    select concat('num的值为：',num);
    end$
    
    调用存储过程：
    call pro_test1();
    +-------------------------------+
    | concat('num的值为：',num)     |
    +-------------------------------+
    | num的值为：20                 |
    +-------------------------------+
    
    select..into..将查询结果赋值给变量：
    
    create procedure pro_test2() 
    begin 
    declare num int;
    select count(*) into num from city;
    select concat('city表中的记录数为：',num);
    end$
    
    call pro_test2();
    +--------------------------------------------+
    | concat('city表中的记录数为：',num)         |
    +--------------------------------------------+
    | city表中的记录数为：4                      |
    +--------------------------------------------+

**if条件判断：**

create procedure pro_test3()
    begin 
    declare height int default 175;
    declare description varchar(50) default '';
    if height >=180 then
    	set description='高个子';
    elseif height >=170 and height<180 then
    	set description='中等个子';
    else
    	set description='小个子';
    end if;
    select concat('身高：', height,'对应的类型为：',description);
    end$
    
    call pro_test3();
    +-----------------------------------------------------------------+
    | concat('身高：', height,'对应的类型为：',description)           |
    +-----------------------------------------------------------------+
    | 身高：175对应的类型为：中等个子                                 |
    +-----------------------------------------------------------------+

**输入参数：**  
根据传递的身高变量，判定身高类型

create procedure pro_test4(in height int)
    begin 
    declare description varchar(50) default '';
    if height >=180 then
    	set description='高个子';
    elseif height >=170 and height<180 then
    	set description='中等个子';
    else
    	set description='小个子';
    end if;
    select concat('身高：', height,'对应的类型为：',description);
    end$
    
    call pro_test4(198);
    +-----------------------------------------------------------------+
    | concat('身高：', height,'对应的类型为：',description)           |
    +-----------------------------------------------------------------+
    | 身高：198对应的类型为：高个子                                   |
    +-----------------------------------------------------------------+
    
    call pro_test4(178);
    +-----------------------------------------------------------------+
    | concat('身高：', height,'对应的类型为：',description)           |
    +-----------------------------------------------------------------+
    | 身高：178对应的类型为：中等个子                                 |
    +-----------------------------------------------------------------+
    
    call pro_test4(155);
    +-----------------------------------------------------------------+
    | concat('身高：', height,'对应的类型为：',description)           |
    +-----------------------------------------------------------------+
    | 身高：155对应的类型为：小个子                                   |
    +-----------------------------------------------------------------+

**输出参数：**  
根据传入的身高变量，获取身高类型并返回

create procedure pro_test5(in height int, out description varchar(10))
    begin 
    if height >=180 then
    	set description='高个子';
    elseif height >=170 and height<180 then
    	set description='中等个子';
    else
    	set description='小个子';
    end if;
    end$
    
    call pro_test5(188,@description);
    select @description;
    +--------------+
    | @description |
    +--------------+
    | 高个子       |
    +--------------+
    注：@a表示用户会话变量，连接关闭时将释放。@@a表示系统变量。

**case结构：**  
给定月份，计算所在季度

create procedure pro_test6(mon int)
    begin
    declare result varchar(10);
    case
    when mon>=1 and mon<=3 then
    set result='第一季度';
    when mon>=4 and mon<=6 then
    set result='第二季度';
    when mon>=7 and mon<=9 then
    set result='第三季度';
    else
    set result='第四季度';
    end case;
    select concat('传递的月份为：', mon ,'，计算出的结果为：', result) as content;
    end$
    
    call pro_test6(9);
    +---------------------------------------------------------------+
    | content                                                       |
    +---------------------------------------------------------------+
    | 传递的月份为：9，计算出的结果为：第三季度                     |
    +---------------------------------------------------------------+

**while循环：**  
计算从1加到n

create procedure pro_test7(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$
    
    call pro_test7(10);
    +-------+
    | total |
    +-------+
    |    55 |
    +-------+

**repeat循环：**  
计算从1加到n  
注意until语句后面没有分号！

create procedure pro_test8(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$
    
    call pro_test7(10);
    +-------+
    | total |
    +-------+
    |    55 |
    +-------+

**loop循环，leave退出：**  
计算从1加到n

create procedure pro_test9(n int)
    begin
    	declare total int default 0;
    	c:loop
    	set total=total+n;
    	set n = n-1;
    	if n<=0 then
    		leave c;
    	end if; 
    	end loop c;
    	select total;
    end$
    
    call pro_test9(10);
    +-------+
    | total |
    +-------+
    |    55 |
    +-------+

**游标：用来存储查询结果集:**  
一次fetch读取一行数据  
若fetch次数超过数据行数，将报错

准备工作：
    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;
    
    insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);
    
    查询emp表的数据并逐行获取进行展示：
    create procedure pro_test10()
    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=',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=',e_salary);
    	
    	close emp_result;
    end$
    
    call pro_test10();
    +-------------------------------------------------------------------------+
    | concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
    +-------------------------------------------------------------------------+
    | id=1, name=金毛狮王, age=55, salary=3800                                |
    +-------------------------------------------------------------------------+
    1 row in set (0.05 sec)
    
    +-------------------------------------------------------------------------+
    | concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
    +-------------------------------------------------------------------------+
    | id=2, name=白眉鹰王, age=60, salary=4000                                |
    +-------------------------------------------------------------------------+
    1 row in set (0.05 sec)

**循环获取游标数据：**  
注：退出条件的声明必须紧跟在游标声明之后！

查询emp表的数据并逐行获取进行展示：
    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 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 e_id,e_name,e_age,e_salary;
    		select concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary);
    		until has_data=0
    	end repeat;
    	
    	close emp_result;
    end$
    
    call pro_test11();
    +-------------------------------------------------------------------------+
    | concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
    +-------------------------------------------------------------------------+
    | id=1, name=金毛狮王, age=55, salary=3800                                |
    +-------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    +-------------------------------------------------------------------------+
    | concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
    +-------------------------------------------------------------------------+
    | id=2, name=白眉鹰王, age=60, salary=4000                                |
    +-------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    +-------------------------------------------------------------------------+
    | concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
    +-------------------------------------------------------------------------+
    | id=3, name=青翼蝠王, age=38, salary=2800                                |
    +-------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    +-------------------------------------------------------------------------+
    | concat('id=',e_id,', name=',e_name,', age=',e_age,', salary=',e_salary) |
    +-------------------------------------------------------------------------+
    | id=4, name=紫衫龙王, age=42, salary=1800                                |
    +-------------------------------------------------------------------------+
    1 row in set (0.01 sec)

**存储函数：**

定义一个存储函数，获取满足条件的总记录数:
    create function fun1(countryId int)
    returns int
    begin
    	declare cnum int;
    	select count(*) into cnum from city where country_id = countryId;
    	return cnum;
    end$
    
    调用存储函数：
    select fun1(1);
    +------+
    | $    |
    +------+
    |    3 |
    +------+
    
    删除存储函数：
    drop function fun1;
    
    注：若创建存储函数报错：
    This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
    使用以下命令解决：
    set global log_bin_trust_function_creators=1$