存储过程和函数

前提

变量

分类

• 系统变量：由系统提供，属于服务器层面
  • 全局变量
  • 会话变量
• 自定义变量
  • 用户变量
  • 局部变量

系统变量

使用

• 查看所有系统变量：show global | 【session】 variables; // 不写默认是会话变量
• 查看满足条件的系统变量: show global | 【session】variables like ‘%char’;
• 查看某个系统变量的值：show @@global | 【session】系统变量名;
• 为系统变量设置值：
  • set global | 【session】系统变量名 = 值
  • set @@global | 【session】系统变量名 = 值

作用域

• 全局变量：服务器每次启动将为所有的全局变量赋初始值，针对所有的会话都有效，但不能跨重启
• 会话变量：仅仅针对当前的会话有效

自定义变量

• 自定义变量使用步骤
  • 定义
  • 赋值
  • 使用

使用

用户变量使用

• 声明并初始化
  • set @用户变量名=值
  • set @用户变量名:=值
  • select @用户变量名:=值
• 赋值
  • 方式一
    • set @用户变量名=值
    • set @用户变量名:=值
    • SELECT @用户变量名:=值
  • 方式二
    • select 字段 into 变量名 from 表;
• 查看用户变量名
  • select @用户变量名

局部变量使用

• 声明
  • declare 变量名 类型;
  • declare 变量名 类型 default 值;
• 赋值
  • 方式一
    • set @用户变量名=值
    • set @用户变量名:=值
    • SELECT @用户变量名:=值
  • 方式二
    • select 字段 into 变量名 from 表;

作用域

• 会话变量：针对当前会话有效
• 局部变量：仅仅在定义它的begin…end语句块中

用户变量和局部变量对比

	作用域	定义和使用位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	begin…end中	只能在begin…end中，且为第一句话	一般不加@符号，需要限定类型

举例

用户变量

set @m=1;
set @n=2;
set @num=@m+@n
select @num;

局部变量

begin
declare m int default 1;
declare n int default 2;
declare num int;
set num=m+n;
select num
end

存储过程和函数

存储过程

基本介绍

• 含义：一组预先编译好的SQL语句的集合，理解成批处理语句
• 优点
  • 提高代码的可重用性
  • 简化操作
  • 减少编译次数并且减少了和数据库服务器的连接次数，提高了效率

语法

• 创建语法

create producer 存储过程名(参数列表)
begin
	存储过程体;     //一组合法的SQL语句
end

• 说明
  • 参数列表包含三部分
    • 参数模式：in、out、inout
      • in:该参数需要调用方传入值
      • out:该参数可以作为返回值
      • inout:既可以作为输入又可以作为输出，既需要传入值又可以返回值
    • 参数名
    • 参数类型
  • 如果存储过程体只有一句话，begin…end可以省略
  • 存储过程体的每条SQL语句结尾必须加分号，存储过程结尾可以使用delimiter重新设定
    • delimiter 结束标记
• 调用语法
  • call 存储过程名(实参列表);
• 删除语法
  • drop procedure 过程名; //一次只能删除一个存储过程
• 查看存储过程信息语法
  • show create procedure 存储过程名

举例

• 传空参数调用

// 创建存储过程
create procedure insert_admin()
DECIMAL $
BEGIN
	insert into admin(user_name, pwd) values ('tom',1234), ('tom1',1435);
END
// 调用存储过程
CALL insert_admin()$

• 创建存储过程实现用户是否登录成功

create procedure login(in username varchar(20), in password varchar(20))
begin
		declare result int default 0;   //声明并初始化
		select count(*) into result  //赋值
		from admin
		where admin.username=username
		and admin.password=password;      //变量名和字段名一样时会采用就近原则，此时字段名需要使用表名来指明
		select if(result>0,'成功‘，’失败‘);     //使用
end $

call  login('张三’,'12132')$

• 创建带out模式的存储过程

create procedure myprocedure1(in beautyName VARCHAR(20), out boyName varchar(20), out userCP int)
BEGIN
	select bo.boyName, bo.userCP into boyName, userCP
	from boys bo
	inner join beauty b on bo.id=b.bl_groups
	where b.name=beautyName;
END $

call myprocedure1('小王', @bName, @usercp)$
select @bName$, @usercp

• 创建带inout模式的存储过程

create procedure myprocedure2(inout a int, inout b int)
BEGIN
		set a=a*2;
		set b=b*2;
END $

set @m=10$
set @n=20$
call myprocedure2(@m, @n)
select @m, @n $

函数

储过程和函数区别

• 存储过程可以有0个返回或者有多个返回，适合做批量插入、批量更新
• 函数有且仅有一个返回，适合做处理数据后返回一个结果

语法

• 创建语法

create function 函数名(参数列表) returns 返回类型		 
begin
		函数体
end

• 说明
  • 参数列表包含两部分
    • 参数列表
    • 参数类型
  • 函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不报错，但不建议,eturn 值
  • 函数体中只有一句话时，可以省略begin end
  • 使用delimiter语句设置结束标记
• 调用语法
  • select 函数名(参数列表)
• 查看函数：show create function 函数名;
• 删除函数：drop function 函数名;

举例

无参数又返回

create function myf1() returns int
begin
		declare c int default 0;		//定义变量
		select count(*) into c      	//赋值
		from employees;
		return c;
end $

select myf1()$

有参有返回

create function myf2(empName varchar(20)) returns double
begin
		set @sal=0;			//定义用户变量
		select salary into @sal		//赋值
		from employees
		where last_name = empName;
		return @sal;
end $

select myf2('小李‘)$

流程控制结构

顺序结构

• 从上往下依次执行

if函数

• 功能：实现简单的双分支
• 语法
  if (表达式1，表达式2，表达式3） //如果表达式1成立执行表达式2，否则执行表达式3
• 应用：任何地方

case结构

情形一

• 类似于编程中的switch…case语句，一般用于实现等值判断
• 语法

		case 变量|表达式|字段
		when 要判断的值 then 返回的值1；
		....
		else 要返回的值n；
		end case；

情形二

• 类似于多重if判断，一般用于实现区间判断
• 语法

		case 
		when 要判断的条件1 then 返回的值1；
		....
		else 要返回的值n；
		end case；

特点

• 可以作为单独的表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end外面都可以
• 可以作为独立的语句使用，只能放在begin end中
• when中的值满足或者条件成立，则执行对应的then后面的语句，并且结束case，如果都不满足，则执行else中的语句或值
• else可以省略，如果else省略，并且所有的when条件都不满足，则返回null

if结构

• 功能：实现多重分支
• 语法

if 条件1 then 

分支结构

• 程序从两条或者多条路径中选择一条去执行

循环结构

• 程序在满足一定条件的基础上，重复执行一段代码，只能放在begin end中
• 分类：while、loop、repeat
• 循环控制
  • iterrate类似于contimue
  • leave类似于break跳出

while

• 不加标签时无法使用循环控制
• 先判断后执行
• 语法

[标签:] while 循环条件 do
		循环体;
end while [标签];		

• 举例

// 1.向admin表中插入多条记录
create procedure pro_while(in count int)
begin
		declare i int default 1;
		while i <= count do
				insert into admin(username, password) values(concat('zsx',i), '123');
				set i=i+1;
		end while;		
end $

call pro_while(100);

//2. 添加循环控制
create procedure pro_while1(in count int)
begin
		declare i int default 1;
		a:while i <= count do
				insert into admin(username, password) values(concat('zsx',i), '123');
				if i >= 20 then leave a;
				end if;
				set i=i+1;
		end while a;		
end $

call pro_while1(100);

loop

• 可以用来模拟死循环（不传条件即可）
• 语法

[标签：]loop 
		循环体;
end loop [标签];

repeat

• 先执行后判断
• 语法

[标签：] repeat
		循环体;
until 循环结束条件;