Mysql3

Mysql的存储过程

-- ----MySQL的存储

-- 数据准备
create database mysql7_procedure;
use mysql7_procedure;

-- 1.创建存储过程
/*
delimiter 自定义结束符号
create procedure 储存名([in ,out ,inout ] 参数名 数据类型...)
begin
	sql语句
end 自定义的结束符合
delimiter ;
*/

delimiter $$
create procedure proc01()
begin
	select empno,ename from emp;
	end $$
	delimiter ;
	
-- 调用存储过程
call proc01();

-- 变量定义
-- 声明定义：declare var_name type [default var_value];

delimiter $$
create procedure proc02()
begin
	declare var_name01 varchar(20) default 'aaa';-- 声明/定义变量
	set var_name01='zhangsan';-- 给变量赋值
	select var_name01;-- 输出变量的值
end $$
	delimiter ;
	
call proc02();


--
delimiter $$
create procedure proc03()
begin
	declare var_name01 varchar(20) ;-- 声明/定义变量
	select ename into my_ename from emp where empno =1001;-- 给变量赋值
	select my_ename;-- 输出变量的值
end $$
	delimiter ;
	
call proc03();

-- 定义一个用户变量
-- 不用声明，声明及使用

delimiter $$
create procedure proc04()
begin
	set @var_name01='beijing';
	select @var_name01;
end $$
	delimiter ;
call proc04();

select @var_nam01;-- 也可以使用变量


-- 系统变量-全局变量
-- 由系统提供，在整个数据库有效

use mysql7_procedure
-- 查看全局变量
show global variables;
-- 查看某个全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size=40000;
set @@global.sort_buffer_size=40000;

select @@global.sort_buffer_size;

-- 系统变量-会话变量
-- 由系统提供，当前会话连接有效


-- 查看会话变量
show session variables;
-- 查看某个全局变量
select @@session.auto_increment_increment;
-- 修改全局变量的值
set session sort_buffer_size=50000;
set @@session.sort_buffer_size=50000;

select @@session.sort_buffer_size;


-- 存储过程传参-IN
-- ------------传入参数：in -------------------
use mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in empno int)
begin 
	select * from emp where emp.empno=empno;
end $$
delimiter;

call proc06(1001);

-- --多参数
-- ----封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定的员工信息

delimiter $$
create procedure proc07(in param_dname varchar(50) ,in param_sal decimal(7,2))
begin 
	select * from dept a,emp b where a.deptno=b.deptno and a.dname=param_dname and b.sal>param_sal;
end $$
delimiter;

call proc07('学工部',2000);



-- --------传出参数：out---------
use mysql7_procedure;
-- -封装有参数的存储过程，传入员工编号，返回员工的名字
delimiter $$
create procedure proc08(in in_empno int,out out_ename varchar(50))
begin 
	select ename into out_ename from emp where empno=in_empno;
end $$
delimiter ;

call proc08(1002,@o_ename);

select @o_ename;




delimiter $$
create procedure proc09(in in_empno int,out out_ename varchar(50),out out_sal decimal(7,2) )
begin 
	select ename,sal into out_ename,out_sal
	from emp where empno=in_empno;
end $$
delimiter ;

call proc09(1002,@o_ename,@o_sal);

select @o_ename;
select @o_sal;


-- -----INOUT-----
use mysql7_procedure;
-- 传入一个数字，传出这个数字的10倍
delimiter $$
create procedure proc10(inout num int)
begin 
	set num=num *10;
end $$
delimiter ;


set @inout_num=2;
call proc10(@inout_num);

select @inout_num;

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

set @inout_ename='关羽';
set @inout_sal=3000;

call proc11(@inout_ename,@inout_sal);

select @inout_ename;
select @inout_sal;




-- 存储过程-IF
-- 案例1
-- 输入学生的成绩，来判断成绩的级别


delimiter $$
create procedure proc_12_if(in score int)
begin 
	if score<60
		then 
			select '不及格';
	elseif score >=60 and score <80
		then 
			select '及格';
	elseif score >=80 and score<90
		then 
			select '良好';
	elseif score>=90 and score<=100
		then 
			select '优秀';
	else 
		select '成绩错误';
	end if ;
end $$
delimiter ;

set @score=65;
call proc_12_if(@score);
call proc_12_if(100);

-- 输入员工姓名，查看薪资情况
delimiter $$
create procedure proc_13_if(in in_ename varchar(20))
begin 
	declare var_sal decimal(7,2);
	declare result varchar(20);
	select 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;
	end $$
	delimiter ;


-- case 

-- 1
delimiter $$

create procedure proc14_case(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 proc14_case(4);


delimiter $$
create procedure proc_15_if(in score int)
begin 
	case
	when score<60
		then 
			select '不及格';
	when score >=60 and score <80
		then 
			select '及格';
	when score >=80 and score<90
		then 
			select '良好';
	when score>=90 and score<=100
		then 
			select '优秀';
	else 
		select '成绩错误';
	end case ;
end $$
delimiter ;

call proc_15_if(88);




-- 流程控制-循环-WHILE 
-- leave 相当于break，跳出当前所在循环；loop 相当于continue，跳出本次循环进入下一循环

use mysql7_procedure;

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

/*while 循环条件 DO
	循环体；
	end while;
	*/
-- 需求：向表中添加10条数据

-- ------存储过程-循环-while
delimiter $$
create procedure proc16_while(in insertCount int)
begin 
declare i int default 1;
	label:while i<=insertCount do
	insert into user(uid,username,password) values(i,concat('user-',i),'123456');
	set i=i+1;
	end while label;
	end $$
delimiter;

call proc16_while(10);

-- -----存储过程-循环控制-while+LEAVE
-- 直接跳出当前循环
truncate table user;

delimiter $$
create procedure proc17_while(in insertCount int)
begin 
declare i int default 1;
	label:while i<=insertCount do
	insert into user(uid,username,password) values(i,concat('user-',i),'123456');
	if i=5 then 
	leave label;
	end if;
	set i=i+1;
	end while label;
	select '循环结束';
	end $$
delimiter;

call proc17_while(10);



-- 循环-while -ITERATE
 -- 跳出本次循环
 
create table user2(
	uid int ,
	username varchar(50),
	password varchar(50)
	);


truncate table user2;

delimiter $$
create procedure proc17_while_iterate(in insertCount int)
begin 
declare i int default 1;
	label:while i<=insertCount do
	insert into user2(uid,username,password) values(i,concat('user-',i),'123456');
	if i=5 then 
	iterate label;
	end if;
	set i=i+1;
	end while label;
	select '循环结束';
	end $$
delimiter;-- 死循环

call proc17_while_iterate(10);


-- 1234678910
delimiter $$
create procedure proc11_while_iterate(in insertCount int)
begin 
declare i int default 0;
	label:while i<insertCount do
	set i=i+1;
	if i=5 then 
	iterate label;
	end if;
	insert into user2(uid,username,password) values(i,concat('user-',i),'123456');
	end while label;
	select '循环结束';
	end $$
delimiter;-- 

call proc11_while_iterate(10);


-- 存储过程-循环控制-repeat 
-- 一直循环 util 条件成立，跳出整个循环
/*REPEAT
	statement_list
UNTIL search_condition END REPEAT;
*/
use mysql7_procedure;
truncate table user;


delimiter $$
create procedure proc18_repeat(in insertCount int)
begin 
declare i int default 1;
	label:repeat 
	insert into user(uid,username,password) values(i,concat('user-',i),'123456');
	set i=i+1;
	until i> insertCount
	end repeat label;
	select '循环结束';
	end $$
delimiter;

call proc18_repeat(10);


-- 流程控制-循环-loop 
use mysql7_procedure;

truncate table user;
 
delimiter $$

create procedure proc19_loop(in insertCount int)
begin 
	declare i int default 1;
	label:loop 
	insert into user(uid,username,password) values(i,concat('user-',i),'123456');
	set i=i+1;
	if i>insertCount 
	then leave label;
	end if;
	end loop label;
	end $$
delimiter ;

call proc19_loop(10);




-- --- 操作游标

-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标

use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求；输入一个部门名，查询该部门员工的编号，名字，薪资，将其进行游标

delimiter $$
create procedure proc19_cursor(int in_dname varchar(50))
begin 
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);

-- 声明游标
declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname ='销售部';

-- 打开游标
open my_cursor;

-- 通过游标获取值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
end loop label;-- 报异常中断
 
-- 关闭游标
close my_cursor;
end $$
delimiter;

call proc9_cursor('销售部');



-- 异常处理-HANDLER句柄
delimiter $$


create procedure proc21_cursor_handler(int in_dname varchar(50))
begin 
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 定义标记值
declare flag int default 1;


-- 声明游标
declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname =in_dname;

-- 定义句柄：定义异常的处理方式
/* 
1：异常处理完之后程序该怎样执行
continue:继续执行剩余代码
exit:直接终止程序
undo:不支持

2：触发条件：
条件码：
1329
条件名：
SQLWARNING
NO FOUND
SQLEXCEPTION
3:异常触发后执行什么代码
设置flag的值 --->0
*/

declare continue handler for 1329 set flag=0;




-- 打开游标
open my_cursor;

-- 通过游标获取值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
-- 判断flag：如果flag的值为1，则执行，否则不执行
if flag=1 then 
select var_empno,var_ename,var_sal;
else 
leave label;
end if;
end loop label;-- 报异常中断
 
-- 关闭游标
close my_cursor;
end $$
delimiter;


/*注意：在语法中，变量声明，游标声明，handler声明是必须按照先后顺序写的，否则创建存储过程出错。
*/


-- Mysql的存储函数

-- 创建一个数据库
create database mydb9_function;
use mydb9_function;

-- 允许创建函数权限信任
set global log_bin_trust_function_creators =TRUE;

-- 创建存储函数-没有参数
drop function if exists myfunc1_emp;
delimiter  $$
create function myfunc1_emp() returns int 
begin 
-- 定义局部变量
declare cnt int default 0;
select count(*) into cnt from emp;
return cnt;
end $$

delimiter;


-- 调用存储函数
select myfunc1_emp();


-- 创建存储过程-有输入参数
-- 需求：传入一个员工编号，返回员工的名字
drop function if exists myfunc2_emp;
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin 
declare out_ename varchar(50);
select ename into out_ename from emp where empno=in_empno;
return out_ename;
end $$
delimiter;


-- 调用存储函数
select myfunc2_emp(1008);