mysql存储过程和函数

MySQL变量
系统变量：（作用域每次将为数据库赋值）

1. 全局变量：变量由系统提供，不是用户定义，属于服务器层面
   使用语法：
   a.查看所有的系统变量
   show global |(session) variables;
   b.查看满足条件的部分系统变量
   show global |(session) variables likes ‘%char%’;
   c.查看指定某个系统变量的值
   select @@global |session.系统变量名
   select global |session 系统变量名
   d.为某个系统变量赋值
   方式一：set global |session 系统变量名=值；
   方式二：set @@global|session.系统变量名=值；

global 针对的全局的 session针对的是当前用户
2. 会话变量：（针对当前会话）

自定义变量：（变量是用户自定义的不是由系统的）
3. 用户变量:
使用步骤：声明 赋值 使用
1.声明：
set @用户变量=值；
select @用户变量=值；
2.赋值：
通过set 或 select
set @用户变量=值；
select @用户变量=值；
通过select into
select 字段 into 变量名 from 表；
3.使用（查看变量的值）
select @变量名；
案例：

声明并初始化
set @name='john';
set @name=100;
set @count=1;
赋值
select count(*) into @count from employees; 
查看
select @count ;

局部变量
作用域：仅仅在定义的begin end 中有效，而且只能是第一句话
1声明
declare 变量名 类型；
deClare 变量名 类型 default 值；
2赋值
通过set 或者select
set 局部变量名 =值；
select @局部变量名 =值；
方式二select into
select 字段 into 局部变量名 from 表；
3.使用
select 局部变量名；

对比局部变量名和用户变量；
。。。。。作用域 。。。。。位置。。。 。。。。。。。语法。。。。。

用户变量 ： 当前会话 ： ： 会话任意位置 : ：必须要加@，不需要限定类型
局部变量 ： begin end： 在begin end之间且为第一句话：：一般不加@，需要限定类型

案例：声明两个变量并赋值求和
用户变量：

set @name1=1;
set @name2=10;
set @sum=@name1+@name2;
select @sum;

存储过程和函数

存储过程和函数 类似于java 中的方法；
好处：提高代码重用性，简化操作、减少了编译次数和连接服务器的连接次数提高效率
存储过程：
意思就是一组预先编译好的sql语句的集合可以理解为批处理语句
一、创建语法
create procedure 存储过程名（参数列表）
begin
存储过程体（一组合法的sql 语句）
end
注意：
1.参数列表包含三部分
参数模式 。。。参数名 。。。参数类型
in…stuanme…varchar(20)
参数模式：
in:该参数可以作为输入、也就是该参数需要调用方传值
out：该参数也可以作为输出，也就是可以作为返回值
inout：既可以做输入值，也可以作为输出，既可以传入值也可以返回值
2.如果存储过程体仅仅只有一句话，begin end 可以省略
存储过程体中的每条sql语句的结尾必须要加分号
存储过程的结尾可以使用DELIMTER重新设置
语法： delimiter 结束标记
delimiter $
二、使用语法
call 存储过程名（实参列表）；
1.空参列表
例如，向admin 表中添加5条记录

delimiter $
create PROCEDURE myp1()
begin 
insert into admin(username,`password`) 
values('tom1','1234aa'),('tom2','12341'),
('tom3','123412'),('tom4','1234a'),('tom5','1234as');
end
$

调用
call myp1()$

2.创建带in 的模式参数的存储过程
案例1.创建存储过程实现根据女神名，查询对应的男神名

create PROCEDURE myp2(in beautyName varchar(20))
begin
select bo.* 
from beauty b
left join boys bo
on b.boyfriend_id=bo.id
where beautyName =b.name;
end $

案例2：创建存储过程，实现用户是否登录成功

create PROCEDURE myp3(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 $

根据对应的女神名，返回对应的男神名

create PROCEDURE myp4(in girlname VARCHAR(20),out boyName varchar(20))
begin
select bo.boyName into boyName
from boys bo
inner join beauty b
on b.boyfriend_id=bo.id
where b.name=girlname;
end $
call myp4('小昭',@bname) $
select @bname $

根据女神名，返回对应的男神名和男神魅力值

create PROCEDURE myp5(in girlname varchar(20),out boyName varchar(20),out userCP int)
begin
select bo.boyName,bo.userCP into boyName,userCP
from beauty b
inner join boys bo
on b.boyfriend_id=bo.id
where b.name=girlname;

end $

call myp5('小昭',@bname,@userCP) $
select @bname,@userCP $

3.创建带inout模式参数的存储过程
案例1.传入a和b两个值，最终a和b都翻倍返回

create PROCEDURE myp6(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end $

调用：这里的调用要用变量调用
set @m=10$
set @n=20$
call myp6(@m,@n) $
select @m,@n $

删除存储过程
drop procedure p1;
查看存储过程的结构
show create procedure p1;

存储过程和函数的区别
1.存储过程：可以有0个也可以多个返回，适合做批量插入、批量更新
函数：有且只有1个返回，适合做处理数据返回一个结构

一。创建语法
create function 函数名（参数列表）returns 返回类型
begin
函数体
end
注意：
1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return 语句，如果没有会报错
如果renturn 语句没有方在函数体的最后也不会报错，不建议这样做；

3.delimiter 语句结束标记

二、调用语法
select 函数名（参数列表）

1.无参数返回 统计部门表中的员工的个数

create function  test2() returns int
begin
declare c int default 0;
select count(*) into c
from employees ;
return c;
end $

2.有参返回
案例1.根据员工名，返回它的工资

流程控制结构
顺序结构：
分支结构：
循环结构：

1.分支结构：
if函数
if（表达式1，表达式2，表达式3）

2.case 结构
case 语句或表达式
when 条件 then 表达式或语句
when 条件 then 表达式或语句
…
else
表达式或语句
end

2.循环结构
while loop repeat

循环控制：
iterate类似于 continue 继续 结束本次循环 继续下一次
leave 类似于breake 跳出 结束当前循环

1.while
语法：
【标签】while 循环条件 do
循环体；
end while 【标签】；
2.loop
语法：
【标签】loop
循环体；
end loop
【标签】
用于模拟简单的死循环
3.repeat
语法：
【标签】repeat
循环体；
until 结束循环的条件
end repeat 【标签】；