文章目录
一、存储过程
含义:一组经过预先编译的sql语句的集合,理解成批处理语句
好处:
1、提高了sql语句的重用性,减少了开发程序员的压力
2、简化了操作
3、减少了编译次数,并减少了和数据库服务器的连接次数,都提高了效率
分类:
1、无返回无参
2、仅仅带in类型,无返回有参
3、仅仅带out类型,有返回无参
4、既带in又带out,有返回有参
5、带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个
1、创建存储过程
1)创建语法
# 需要设置新的结束标记
# delimiter 新的结束标记
delimiter $ #定义结束标志
create procedure 存储过程名(参数,参数...)
begin
sql语句1;
sql语句2;
end $
2)参数包含三部分
- 参数模式
- in:该参数可以作为输入,也就是该参数需要调用方传入值;
- out:该参数可以作为输出,该参数可以作为返回值;
- inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值。
- 参数名
- 参数类型
比如:in stuname varchar(20);
3)注意事项
- 如果存储过程体仅仅只有一句话,begin end可以省略
- 存储过程体重的每条sql语句的结尾要求必须加分号。
- 存储过程的结尾可以使用 delimiter 重更新设置。
- 语法:
delimiter 结束标记
- 创建相同名称的存储过程,会报错!!
2、调用存储过程
call 存储过程名(实参列表)结束标志
3、实战存储过程
1)无参存储过程
delimiter $
# 创建无参存储过程,实现插入数据
create procedure my_p1()
begin
insert into admin(username,'password')
values('jogn1','0000'),('lily','1111'),('rose','3333'),('jack','4444'),('tom','5555');
end $
call my_p1()$ #调用
2)带in模式参数的存储过程
delimiter $
# 案例一:创建存储过程,实现根据女神名,查询对应的男神信息
# 参数默认是in,所以下面的in可以省略
create procedure my_p2(in beautyName varchar(20))
begin
select bo.*
from boys bo
right join beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
end $
call my_p2('柳岩')$ #调用
# 如果报错说字符集出错,则需要设置字符集:set names gbk/utf8
# 案例二:创建存储过程实现,用户是否登录成功,登录成功返回成功!登录失败返回失败,使用变量!
create procedure my_p3(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 my_p3('柳岩','123')$ #调用
3)创建带out模式的存储过程
delimiter $
# 案例一:根据女神名,返回对应的男神名
create procedure my_p5(in beautyName varchar(20),out boy Name varchar(20))
begin
select bo.boyName into boyName
from boys bo
inner join beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
end $
#调用并写入返回值
#可以定义也可以不定义!set @bName$
call my_p5('小昭',@bName)$
select @bName$
#案例二:根据女神名,返回对应的男神名和男神魅力值
create procedure my_p6(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.boyfriend_id
where b.name=beautyName;
end $
#调用并使用返回值
call my_p6('小昭',@bName,@userCp)$
select @bName,@userCp$
4)创建带inout模式参数的存储过程
delimiter $
# 案例1:传入a和b两个值,最终a和b都翻倍并返回
create procedure my_p8(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end $
set @m=10$
set @n=20$
call my_p8(@m,@n)$
select @m,@n$
5)实战
delimiter $
# 一、创建存储过程实现传入用户名和密码,插入到admin表中
create procedure test_pro1(in username varchar(20),in loginpwd varchar(20))
begin
insert into admin(admin.username,password)
values(username,loginpwd)
end $
call test_pro1('admin','0000')$
select * from admin;
# 二、创建存储过程实现传入女神编号,返回女神名称和女神电话
create procedure test_pro2(in id int,out name varchar(20),out phone varchar(20))
begin
select b.name,b.phone into name,phone
from beauty b
where b.id=id;
end $
call test_pro2(1,@n,@p)$
select @n,@p$
# 三、创建存储存储过程或函数实传入两个女神生日,返回大小
create procedure test_pro3(in birth1 datetime,in birth2 datetime,out result int)
begin
select datediff(birth1,birth2) into result;
end;
$
call test_pro3('1998-1-1',now(),@result)$
select @result$
# 四、创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
create procedure test_pro4(in myDate datetime,out strDate varchar(20))
begin
select date_format(myDate,'%y年%m月%d日') into strDate;
end $
call test_pro4(now(),@str)$
select @str$
# 五、创建存储过程或函数实现传入女神名称,返回:女神and男神 格式的字符串,如:传入小昭,返回小昭 AND 张无忌
create procedure test_pro5(in beautyName varchar(20),out str varchar(50))
begin
select concat(beautyName,' and ',ifnull(boyName,'null'))
from boys bo
right join beauty b on b.boyfriend_id=bo.id
where b.name=beautyName;
end $
call test_pro5('小昭',@str);
select @str $
# 六、创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
create procedure test_pro6(in startIndex int,in size int)
begin
select * from beauty limit startIndex,size;
end $
call test_pro6(3,5)$
4、删除存储过程
# 语法:
drop procedure 存储过程名;#一次性只能删除一个
5、查看存储过程的结构信息
# 语法:
show create procedure 存储过程名;#名字、语法格式、创建的语法、字符集
二、函数
含义:一组经过预先编译的sql语句的集合,理解成批处理语句
好处:
1、提高了sql语句的重用性,减少了开发程序员的压力
2、简化了操作
3、减少了编译次数,并减少了和数据库服务器的连接次数,都提高了效率
区别:
存储过程:可以有0个或多个返回。适合做批量插入、批量更新操作!
函数:有且只有1个返回。适合做处理数据后返回一个结果的情况。
1、创建函数
学过的函数:LENGTH、SUBSTR、CONCAT等
语法:
CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
函数体
END
/*
注意:
1、参数列表 包含两部分:
参数名 参数类型
2、函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
3、函数体中仅有一句话,则可以省略begin end
4、使用 delimiter语句作设置结束标志
delimeter
*/
2、调用函数
SELECT 函数名(实参列表)
3、实战
#1、无参有返回
#案例:返回公司的员工个数
create function my_f1() returns int
begin
declare c int default 0; #定义变量
select count(*)into c #赋值
from employees
end $
select my_f1() $
#有参有返回
#案例1:根据员工名,返回它的工资
create function my_f2(empName varchar(20)) returns double
begin
set @sal=0;#定义用户变量
select salary into @sal
from employees
where last_name=empName
limit 1;
return @sal;
end $
select my_f2('k_ing') $
#案例2:根据部门名,返回该部门的平均工资
create function my_f3(deptName varchar(20)) returns double
begin
declare sal double;
select avg(salary) into sal
from employees e
join departments d on e.department_id = d.department
where d.departmnt_name=deptName;
return sal;
end $
select my_f3('IT') $
#三、查看函数
show create function my_f3;
#四、删除函数
drop function my_f3;
# 无法修改
#案例
# 一、创建函数,实现传入两个float,返回二者之和
create function test_fun1(num1 float,num2 float) returns float
begin
declare nowSum float default 0;
set nowSum=num1+num2;
return nowSum;
end $
select test_fun1(1,2) $
三、流程控制结构
- 顺序结构:程序从上往下依次执行
- 分支结构:程序从两条或多条路径中选择一条去执行
- 循环结构:程序在满足一定条件的基础上,重复执行一段代码
1、分支
1)if函数
语法:if(条件,值1,值2)
特点:可以用在任何位置
实现简单的双分支
# 语法
if(表达式1,表达式2,表达式3)
# 执行顺序:
# 如果表达式1成立,则if函数返回表达式2的值,负责返回表达式3的值
2)case语句
语法:
情况一:类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句,需要加分号)
when 值2 then 结果2或语句2(如果是语句,需要加分号)
...
else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)
情况二:类似于多重if
case
when 条件1 then 结果1或语句1(如果是语句,需要加分号)
when 条件2 then 结果2或语句2(如果是语句,需要加分号)
...
else 结果n或语句n(如果是语句,需要加分号)
end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)
特点:
- 可以作为表达式,嵌套在其他语句中使用,也可以放在任何地方,begin end 中或者begin end 的外面,可以作为独立的语句去使用,只能放在begin end中。
- 如果when中的值满足或条件成立,则执行对应的then后面的语句,并且结束case,如果都不满足,则执行else中的语句或值。
- else可以省略,如果else省略了,并且所有的when条件都不满足,则返回null
# 案例:创建存储过程,根据传入的成绩,来显示等级,比如传入的成绩:90~100,显示A,80-90,显示B,60·80,显示C,否则显示D
create procedure test_case(in score int)
begin
case
when score >= and score <=100 then select 'A';
when score >=80 then select 'B';
when score >=60 then select 'C';
else select 'D';
end case;
end $
call test_case(95) $
#实战,取出当前事件在某个区间,那就取这个区间的内容
SELECT
to_char( SYSDATE, 'hh24:mi:ss' ),
a.CLASSES_BEGINTIME1,
a.CLASSES_ENDTIME1,
CASE
WHEN to_char( SYSDATE, 'hh24:mi:ss' ) >= a.begin_time
AND to_char( SYSDATE, 'hh24:mi:ss' ) < a.end_time THEN
'ok' ELSE 'no'
END AS "isIn"
FROM
SCH_SC_CLASSES a,
sch_sc_schedulinfo b,
dual
3)if elseif语句
语法:
if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
【else 语句n;】 #可以省略
end if;
特点:
只能用在begin end中!!!!!!!!!!!!!!!
三者比较:
应用场合 | |
---|---|
if函数 | 简单双分支 |
case结构 | 等值判断 的多分支 |
if结构 | 区间判断 的多分支 |
# 根据传入的成绩,来显示等级,比如传入的成绩:90~100,返回A,80-90,返回B,60·80,返回C,否则返回D
create function test_if(score int) returns char
begin
if score>=90 and score<=100 then return 'A';
elseif score>=80 then return 'B';
elseif score>=60 then return 'C';
else return 'D';
end if;
end $
select test_if(86)$
循环
分类:
while、loop、repeat
循环控制:
-
iterate类似于 continue,继续,结束本次循环,继续下一次
-
leave类似于break,跳出,结束当前所在的循环
语法:
有循环名字就可以进行循环控制,否则就是一个普通的循环
【循环名字:】WHILE 循环条件 DO
循环体
END WHILE 【循环名字】;
# 可以用来模拟简单的苏循环
【标签:】loop
循环体;
end loop 【标签】;
#先执行再判断,最少执行一次
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】
特点:
只能放在BEGIN END里面
如果要搭配leave跳转语句,需要使用标签,否则可以不用标签
leave类似于java中的break语句,跳出所在循环!!!
案例
# 批量插入,根据次数插入到admin表中多条记录
# 没有添加循环控制语句,没有返回,就用存储过程
create procedure pro_while1(in insertCount int)
begin
declare i int default 1;
while i<=insertCount do
insert into admin(username,password) values(concat('Rose',i),'666');
set i=i+1
end while;
end $
call pro_while1(100)$ #插入100条
#添加leave语句
#批量插入,次数>20则停止
create procedure test_while2(in insertCount int)
begin
declare i int default 1;
a:while i<=insertCount do
insert into admin(username,password) values(concat('xiaofang',i),'0000');
if i>=20
then leave a;
end if;
set i=i+1;
end while a;
end $
call test_while2(100)$
#使用iterate,只添加偶数次
create procedure test_while3(in insertCount int)
begin
declare i int default 0;
a:while i<=insertCount do
set i=i+1;
if mod(i,2)!=0
then iterate a;
end if;
insert into admin(username,password) values(concat('xiaofang',i),'0000');
end while a;
end $
call test_while3(100)$