零、数据准备
-- 创建表 emp
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10), -- 姓名
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT
);
-- 插入数据
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
一、存储过程(无参数)
# 存储过程 声明 无参数
delimiter $ # mysql语句结束符 改为 ‘$’
create procedure emp_gender() # 创建
begin
select gender,count(*) from emp group by gender;
end $
delimiter ; # mysql语句结束符 改为 ‘;’
# 过程调用
call emp_gender();
二、存储过程(局域变量)
delimiter $
create procedure emp_gender2()
begin
# 声明变量
declare man char(1);
declare woman char(1);
# 变量赋值
select count(*) into man
from emp where gender='男';
select count(*) into woman
from emp where gender='女';
# 变量使用
select man,woman;
end $
delimiter ;
call emp_gender2();
三、存储过程(传入变量)
delimiter $
create procedure emp_gender3( in money int )
begin
select * from emp where salary>money;
end $
delimiter ;
set @money=5000; -- 声明时必须赋值
select @money; -- 查看赋值是否成功
call emp_gender3(@money); -- 变量传递
call emp_gender3(5000); -- 数值传递
四、存储过程(传入/出参数) 老案例 1-100 偶数求和
delimiter $
create procedure getSum(inout num int,inout sum int)
begin
while num <= 100 # 循环
do
if num%2 = 0 then # 偶数判定
set sum = sum + num; # 值设定方式1 set 方式2 SQL中into
end if ;
set num = num +1;
end while ;
end $
delimiter ;
drop procedure getSum; -- 删除 存储过程
set @num=1, @sum=0; -- 设值
call getSum(@num,@sum); -- 调用
select @num, @sum; -- 查看结果
五、存储函数 - 对比存储过程(有返回值)
delimiter $
create function func_getSum(num int, sum int)
returns int # 返回值类型 +s
begin
while num <= 100
do
if num%2 = 0 then
set sum = num + sum;
end if;
set num = num +1;
end while ;
return sum; -- 返回值 return 无s
end $
delimiter ;
select func_getSum(1,0); -- 调用关键字 select