MySQL函数

-- 创建函数
delimiter $$ -- 自定义结束符
create procedure proc01()
begin
select eid,ename from emp;
end $$
delimiter ; -- 将结束符还原成系统默认的符号

call proc01(); -- 调用函数


delimiter $$
create procedure proc02()
begin
-- 给变量赋值
declare var_name01 varchar(20) default'aa'; -- 声明变量，局部变量
set var_name01 = '牛圣三'; -- 变量赋值
select var_name01; -- 输出变量，只能在end里面来调用
end $$
delimiter ;

call proc02();


delimiter $$
create procedure proc03()
begin
-- 给变量赋值
declare my_ename varchar(20); -- 声明变量
-- set var_name01 = '牛圣三'; -- 变量赋值
select ename into my_ename from emp where eid=1;
select my_ename; -- 输出变量，只能在end里面来调用
end $$
delimiter ;

call proc03();


delimiter $$
create procedure proc04()
begin
set @var_name01='北京'; -- @ 定义全局变量，无需提前声明
select @var_name01;
end $$
delimiter ;

call proc04();
select @var_name01;

show global variables; -- 查看所有的全局变量
select @@global.auto_increment_increment; -- 自增长变量
set global sort_buffer_size=40000;
select @@global.sort_buffer_size;

-- 传入员工编号，查询员工信息
delimiter $$
create procedure proc05 (in num varchar(20))
begin
select * from emp where eid=num;
end $$
delimiter ;

call proc05(3);

-- 传入部门名称和年龄，查询指定部门名，年龄>指定值的员工信息
delimiter $$
create procedure proc06(in nname varchar(20), in nage int)
begin
select * from emp e,dept d where e.dept_id=d.deptno and d.name=nname and e.age>nage;
end $$
delimiter ;

call proc06('研发部', 20);


-- 传入员工号，返回员工名
delimiter $$
create procedure proc07(in in_eid varchar(20), out out_ename varchar(20))
begin
select ename into out_ename from emp where eid=in_eid;
end $$
delimiter ;

call proc07(1, @o_ename);
select @o_ename;


-- 传入员工号，返回员工名和年龄
delimiter $$
create procedure proc08(in in_eid varchar(20), out out_ename varchar(20), out out_age int)
begin
select ename,age into out_ename,out_age from emp where eid=in_eid;
end $$
delimiter ;

call proc08(1, @out_ename, @out_age);
select @out_ename;
select @out_age;

-- 传入一个人参数，传出这个数10倍的值
delimiter $$
create procedure proc09(inout num int)
begin
set num = num*10;
end $$
delimiter;

set @inout_num=2;
call proc09(@inout_num);
select @inout_num;


-- 传入员工名，拼接部门号，传入薪资，算出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(20), inout inout_sal int)
begin
select concat_ws('-', dept_id, ename) into inout_ename from emp where ename=inout_ename;
set inout_sal=inout_sal*12;
end $$
delimiter ;

set @inout_ename='虚竹';
set @inout_sal=8000;
call proc10(@inout_ename, @inout_sal);
select @inout_ename;
select @inout_sal;


-- 存储过程流程控制
-- if输入员工姓名判断工资情况，sal<10000 试用薪资，sal>=10000 and sal<20000 转正薪资，sal>=20000 元老薪资

delimiter $$
create procedure proc11(in in_ename varchar(20))
begin
declare var_sal int; -- 创建一个变量
declare result varchar(20);

select sal into var_sal from emp where ename=in_ename;
if var_sal<10000 then set result='试用薪资';
elseif var_sal<20000 then set result='转正薪资';
else set result='元老薪资';
end if;

select result; -- 显示输出

end $$
delimiter;

call proc11('鸠摩智');
call proc11('李秋水');


-- case 支付方式： 1.'微信支付', 2.'支付宝支付', 3.'银行卡支付';

-- 写法1
delimiter $$
create procedure proc12(in pay_type int)
begin

case pay_type
when 1 then select '微信支付';
when 2 then select '支付宝支付';
when 3 then select '银行卡支付';
else select '其他支付方式';
end case;

end $$
delimiter ;

call proc12(2);
call proc12(10);


-- 写法2
delimiter $$
create procedure proc13(in pay_type int)
begin

case
when pay_type=1 then select '微信支付';
when pay_type=2 then select '支付宝支付';
when pay_type=3 then select '银行卡支付';
else select '其他支付方式';
end case;

end $$
delimiter ;

call proc12(1);
call proc12(3);


-- while 创建表
create table user(
uid int primary key,
username varchar(50),
password varchar(50)
);
create table user2(
uid int,
username varchar(50),
password varchar(50)
);


-- 向表中添加指定条数据
delimiter $$
create procedure proc14(in insertcount int)
begin
declare i int default 1;

-- 标签(可不写)：while 条件 do
lable:while i<=insertcount do
insert into user(uid,username,password)
values(i,concat('user_',i),'123456'); -- concat(); 拼接函数
set i=i+1;
end while lable;
end $$
delimiter ;

call proc14(10);



-- while-leave
truncate table user; -- 清空表
delimiter $$
create procedure proc15(in insertcount int)
begin
declare i int default 1;
	lable:while i<insertcount do
	insert into user(uid,username,password)
	values(i,concat('user_',i),'123456');
		if i=5 then
		leave lable; -- 跳出循环
		end if;
	set i=i+1;
	end while lable;
select '循环结束';
end $$
delimiter ;


call proc15(10);


-- while-iterate
truncate table user2;
delimiter $$
create procedure proc16(in insertcount int)
begin
declare i int default 0;
	lable:while i<insertcount do
	set i=i+1;
		if i=5 then
		iterate lable; -- 跳出本次循环
		end if;
	insert into user2(uid,username,password)
	values(i,concat('user_',i),'123456');
	end while lable;
select '循环结束';
end $$
delimiter ;

call proc16(10);


-- repeat
truncate table user;
delimiter $$
create procedure proc17(in insertcount int)
begin
declare i int default 1;
lable:repeat
insert into user(uid,username,password)
values(i,concat('user_',i),'123456'); -- concat(); 拼接函数
set i=i+1;
until i>insertcount
end repeat lable;
end $$
delimiter ;

call proc17(10);


-- loop 
truncate table user;
delimiter $$
create procedure proc18(in insertcount int)
begin
declare i int default 1;
	lable:loop
		insert into user(uid,username,password)
		values(i,concat('user_',i),'123456'); -- concat(); 拼接函数
		set i=i+1;
		if i>insertcount
			then
				leave lable;
		end if;
	end loop lable;
end $$
delimiter ;

call proc18(10);




-- 游标

-- 声明游标

-- 打开游标

-- 游标取值

-- 关闭游标



-- 输入一个部门名，查该员工的编号、名字、薪资，将查询的结果添加游标
delimiter $$
create procedure proc19(in in_name varchar(20))
begin
declare var_eid varchar(20);
declare var_ename varchar(20);
declare var_sal int;
declare my_cursor cursor for
select eid,ename,sal from dept a,emp b
where a.deptno=b.dept_id and a.name=in_name; -- 声明游标
open my_cursor; -- 打开游标
	lable:loop
	fetch my_cursor into var_eid,var_ename,var_sal; -- 通过游标获取值
	select var_eid,var_ename,var_sal;
	end loop lable;
close my_cursor; -- 关闭游标
end $$
delimiter ;

call proc19('研发部');


drop procedure if exists proc19; -- 删除存储过程

-- 存储函数
-- 创建无参数的
drop function if exists myfunc1; -- 删除存储函数

delimiter $$
create function myfunc1()
begin
declare cnt int default 0;
select count(*) into cnt from emp;
return cnt;
end $$
delimiter ;

存储函数

delimiter $$
create function myfun1() returns int
begin
declare cnt int default 0;
select count(*) into cnt from emp;
return cnt;
end $$
delimiter ;

select myfun1()

-- 有参数
-- 传入参数，传入员工号，返回员工名
delimiter $$
create function myfun2(in_empno varchar(20)) returns varchar(20)
begin
declare out_ename varchar(20);
select ename into out_ename from emp where eid=in_empno;
return out_ename;
end $$
delimiter ;

select myfun2(6);