MySQL高级_存储过程和函数

一：定义和区别

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。存储过程是可以编程的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
函数 ： 是一个有返回值的过程 ；
过程 ： 是一个没有返回值的函数 ；

二：基本语法：

1）创建存储过程

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

-- 语法说明：
-- procedure_name：存储过程名称
-- [proc_parameter[,...]]：存储过程所需要的参数


-- 举例
delimiter $

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

delimiter ;

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

2）调用存储过程

-- 语法：
call procedure_name() ;

3）查看存储过程（除了以上语句，一般用工具查看）

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

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

-- 方式三：查询名为pro_test1的存储过程的的创建语句
show create procedure pro_test1

4）删除存储过程

-- 删除名为sp_name 的存储过程
DROP PROCEDURE  [IF EXISTS] sp_name

三：编程语法

一：变量

1)变量声明(DECLARE)

-- 语法
DECLARE var_name[,...] type [DEFAULT value]
-- 通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中
-- var_name[,...]：变量名，可以是多个变量，逗号分隔
-- [DEFAULT value]：变量类型


-- 举例
create procedure pro_test1()
begin 
	declare num int default 5;
	select num + 10;
end

2）赋值变量(set 或者 select… into…)

-- 方式一：语法
-- 直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：
SET var_name = expr [, var_name = expr] ...


--举例
create procedure pro_test2()
begin 
	declare myname VARCHAR(20) ;
	set myname = '张三';
	select myname;
end

-- 方式二：语法select... into...
create procedure pro_test3()
begin 
	declare num int;
	select count(*) into num from city;
	select num;
end
-- 说明
-- 将city表的条数赋值给num(主要是into给变量)

二：if条件判断

-- 语法结构 
if search_condition then statement_list

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

-- 语法说明
-- search_condition ：条件
-- statement_list：条件成立，执行的sql语句

-- 举例
create procedure pro2()
begin 
	declare height int default 175;
	declare descrition VARCHAR(20) default '';
	if height >180 then set descrition = '身材高挑';
	elseif height between 170 and 180 then set descrition = '标准身材';
	else set descrition = '一般身材';
	end if;
	select concat('身高:',height,'对应的身材类型：',descrition);
end

三：传递参数

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

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

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

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

-- 举例(输入参数)
create procedure pro3(in height int)
begin 
	declare height int default 175;
	declare descrition VARCHAR(20) default '';
	if height >180 then set descrition = '身材高挑';
	elseif height between 170 and 180 then set descrition = '标准身材';
	else set descrition = '一般身材';
	end if;
	select concat('身高:',height,'对应的身材类型：',descrition);
end

call pro3(198)

-- 举例（输出参数）
create procedure pro4(in height int,out descrition VARCHAR(20))
begin 
	if height >180 then set descrition = '身材高挑';
	elseif height between 170 and 180 then set descrition = '标准身材';
	else set descrition = '一般身材';
	end if;
end
-- 调用之后返回值赋值给了变量 @descrition
call pro4(168,@descrition)
-- 查询变量
select @descrition

四：case结构

-- 方式一 : 
CASE case_value

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

-- 方式一说明
-- case值 = when值 执行 sql语句
-- statement_list：执行的sql语句


-- 方式二 : 
CASE

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

-- 方式二说明
-- search_condition ：条件表达式
-- statement_list：执行的sql语句

-- 举例
create procedure pro6(in m int,out q varchar(10))
begin 
	case when m between 1 and 3 then set q = '第一季度';
	when m between 4 and 6 then set q = '第二季度';
	when m between 7 and 9 then set q = '第三季度';
	else set q = '第四季度';
	end case;
end

call pro6(12,@q);

select @q;

五：循环

1）while 循环

-- 语法
while search_condition do

	statement_list
	
end while;

-- 语法说明
-- search_condition ：判断条件
-- statement_list：sql语句



-- 举例：从1到n累加
create procedure pro2(in n int,out sum int)
begin 
	declare total int default 0;
	declare i int default 1;
	while i<=n do 
	set total = total + i;
	set i =i+1;
	end while;
	set sum = total;
end

call pro2(5,@sum);

select @sum;

2）repeat 循环（ 满足条件退出循环 ）

-- 语法
REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

-- 语法说明
-- statement_list：满足条件执行的sql
-- search_condition:满足这个条件时，退出

-- 举例：从1到n累加

create procedure pro2(in n int,out sum int)
begin 
	declare total int default 0;
	repeat 
	set total = total + n;
	set n = n - 1;
	-- 注意until 后不要加分号
	until n = 0
	end repeat;
	set sum = total;
end

call pro2(100,@sum);

select @sum;

3）loop 语句，leave语句
LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现。
leave语句：用来从标注的流程构造中退出，通常和 BEGIN … END 或者循环一起使用

-- 语法
[begin_label:] LOOP

  statement_list

END LOOP [end_label]

-- 注意：如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。
-- [begin_label:] 循环别名
-- statement_list：sql语句

-- 举例 ：从1到n累加
CREATE PROCEDURE pro_test11(n int)
BEGIN
  declare total int default 0;
  -- ins 别名
  ins: LOOP
    -- 当n <= 0 退出
    IF n <= 0 then
      leave ins;
    END IF;
    
    set total = total + n;
    set n = n - 1;
  	
  END LOOP ins;
  
  select total;
END;

六：游标/光标

游标是用来存储查询结果集的，包括 ：声明、OPEN、FETCH 和 CLOSE
1）声明光标：

DECLARE cursor_name CURSOR FOR select_statement ;

-- cursor_name：游标名称
-- select_statement ：select 语句

2）OPEN 光标

OPEN cursor_name ;

3）FETCH(抓取) 光标：

FETCH cursor_name INTO var_name [, var_name] ...

4）CLOSE 光标：

CLOSE cursor_name ;

游标举例：

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;
-- 拿不到数据的时候，将has_data设置为0
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
-- open游标
  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游标
  close emp_result;
end;
-- 调取的行数和count(*) 相同
CALL pro_test12()

七： 存储函数

-- 创建存储函数

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;

-- count_city：函数名
-- countryId：入参



-- 调用: 
select count_city(1);