MYSQL- 03存储过程和函数

01杭呐

已于 2022-12-31 13:34:49 修改

阅读量71

点赞数

分类专栏： MYSQL 文章标签：数据库

于 2022-12-31 13:33:19 首次发布

本文链接：https://blog.csdn.net/weixin_55863682/article/details/128501130

版权

MYSQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

4. 存储过程和函数

4.1 存储过程和函数概述

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于有无返回值。

函数：是一个有返回值的过程；
过程：是一个没有返回值的函数；

4.2 创建存储过程

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

示例：

delimiter $ -- 将 SQL 结束的符号; 名声为 $ 

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

delimiter ;

知识小贴士

DELIMITER关键字：用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

4.3 调用存储过程

call procedure_name() ;

4.4 查看存储过程

-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';


-- 查询存储过程的状态信息
show procedure status;


-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;

查询存储过程的状态信息 show procedure status;

查询某个存储过程的定义 show create procedure test.pro_test1 \G;

4.5 删除存储过程

DROP PROCEDURE  [IF EXISTS] sp_name ；

4.6 语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

4.6.1 变量

DECLARE

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中

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

示例 :

delimiter $

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

 delimiter ;

执行效果

直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

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

示例 :

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

也可以通过select ... into 方式进行赋值操作 :

DELIMITER $

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

DELIMITER ;

执行效果

4.6.2 if条件判断

语法结构 :

if search_condition then statement_list

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

需求：

根据定义的身高变量，判定当前身高的所属的身材类型

   180 及以上 ----------> 身材高挑

   170 - 180 ---------> 标准身材

   170 以下 ----------> 一般身材

delimiter $

create procedure pro_test6()
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$

delimiter ;

执行效果

4.6.3 传递参数

语法格式 :

create procedure procedure_name([in/out/inout] 参数名   参数类型)
...

IN :   --该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT:   --该参数作为输出，也就是该参数可以作为返回值
INOUT: --既可以作为输入参数，也可以作为输出参数

需求 :

根据传入的身高变量，获取当前身高的所属的身材类型

示例:

create procedure pro_test5(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 pro_test5(168, @description)$

select @description$

小知识

@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@global.sort_buffer_size : 这种在变量前加上 "@@" 符号, 叫做系统变量

4.6.4 case结构

语法结构 :

方式一 : 

CASE case_value

  WHEN when_value THEN statement_list
  
  [WHEN when_value THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;


方式二 : 

CASE

  WHEN search_condition THEN statement_list
  
  [WHEN search_condition THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;

示例 : 给定一个月份, 然后计算出所在的季度

delimiter $


create procedure pro_test9(month int)
begin
  declare result varchar(20);
  case 
    when month >= 1 and month <=3 then 
      set result = '第一季度';
    when month >= 4 and month <=6 then 
      set result = '第二季度';
    when month >= 7 and month <=9 then 
      set result = '第三季度';
    when month >= 10 and month <=12 then 
      set result = '第四季度';
  end case;
  
  select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
  
end$


delimiter ;

4.6.5 while循环

语法结构:

while search_condition do

    statement_list
    
end while;

示例 :计算从1加到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 ;

4.6.6 repeat结构

有条件的循环控制语句, 当满足条件的时候退出循环。while 是满足条件才执行，repeat 是满足条件就退出循环。

语法结构 :

REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

示例 : 计算从1加到n的值

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 ;

4.6.7 loop语句

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：

[begin_label:] LOOP

  statement_list

END LOOP [end_label]

如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

4.6.8 leave语句

用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE 的简单例子 , 退出循环：

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 ;

4.6.9 游标/光标

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明光标：

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN 光标：

OPEN cursor_name ;

FETCH 光标：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光标：

CLOSE cursor_name ;

示例 :

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

通过循环结构 , 获取游标中的数据 :

句柄需要声明在游标之后
NOT FOUND 时触发句柄 -- DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;

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为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
    until has_data = 0
  end repeat;
  
  close emp_result;
end$

DELIMITER ;

4.7 存储函数

存储过程和函数的区别在于有无返回值。

函数：是一个有返回值的过程；
过程：是一个没有返回值的函数；
存储函数可以实现的，存储过程亦可以（给存储过程加上 OUT 具体可看4.6.3 传递参数）

语法结构

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

案例 :

定义一个存储过程, 请求满足条件的总记录数 ;


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 ;

函数调用用select 过程调用用call

select count_city(1);

select count_city(2);

01杭呐

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL- 03存储过程和函数

存储过程和函数的区别、基本语法
复制链接

扫一扫

专栏目录

MYSQL- 03存储过程和函数

4. 存储过程和函数

4.1 存储过程和函数概述

4.2 创建存储过程

4.3 调用存储过程

4.4 查看存储过程

4.5 删除存储过程

4.6 语法

4.6.1 变量

4.6.2 if条件判断

4.6.3 传递参数

4.6.4 case结构

4.6.5 while循环

4.6.6 repeat结构

4.6.7 loop语句

4.6.8 leave语句

4.6.9 游标/光标

4.7 存储函数

“相关推荐”对你有帮助么？