MYSQL中的调用存储过程，变量的定义，

1.调用存储过程

1.其本质就是将一些sql语句封装成固定代码块的过程类似于c语言中的函数。

例如：以下就是进行封装成固定的代码块

create procedure proc01()
begin
 select empno,ename from emp;
end $$
delimiter;

2.调用存储过程：

call 定义的过程名称
例如：

call proc01();

2.声明/定义变量(用户变量，全局变量，会话变量)

1.定义/声明变量;

declare var_name01 varchar(20) default 'aaa';

利用存储过程改变声明变量

delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa'; -- 声明/定义变量
set var_name01 = 'shangsan';
select var_name01;
end $$
delimiter;
call proc02();

2.定义用户变量;

@var_name01;
例如：

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

3.定义全局变量：

@@global.var_name

-- 查看全局变量
show global variables;
-- 查看某个局部变量
select @@global.auto_increment_increment;
-- 修改全局变量
set global sort_buff_size = 50000;
set @@global.sort_buff_size = 50000;

4.定义会话变量

用法于与全局变量和用户变量类似

@@session.var_name

只在当前会话下起作用

3.关键字in,out,inout的使用

in

delimiter $$
create procedure proc06(in param_empno int)
begin
select * from emp where empno = param_empno;
end $$
delimiter;
call proc06(1001);

delimiter $$
create procedure proc07(in dname varchar(50), in sal decimal(7,2))
begin
select * from dept a,emp b where a.deptno = b.deptno and a.dname = dname and b.sal = sal;
end $$
delimiter;
call proc07('学工部'，20000);

out关键字：

delimite $$
create procedure proc08(in in_empno int,out out_name varchar(50))
begin 
 select ename into out_ename from emp where empno = in_empno;
 end $$
 delimiter;
call proc08(1001,@o_ename);
select @o_ename;

inout关键字：

-- inout
delimiter $$
create procedure proc10(inout num int)
begin
set num = num * 10;
end $$
delimiter ;
set @inout_num = 3;
call proc10(@inout_num);
select @inout_num;

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

4.存储过程中的判断

-- 控制-判断
-- 语法
/*[elseif search condition 2 then statement list 2]
[else statement list n] 
end 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 '优秀';
	elseif score>100
	 then 
		select '错误';
	end if;
end $$
delimiter ;
set @score = 55;
call proc_12_if(@score);

delimiter $$
create procedure proc_13_if(in in_name varchar(20))
begin
declare var_sal decimal(7,2);
declare result varchar(20);
seelct sal into var_sal from emp where ename = in_name;
if var_sal<10000
	then 
		set result = '试用薪资';
elseif var_cal < 20000
	then 
		set result = '转正薪资';
else
	set result = '元老薪资';
end if;
end $$
delimiter ;
-- 调用：
call proc_13_if('关羽')



-- 流程控制语句：case
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(2);

-- 格式2
delimiter $$
create procedure proc_14_case(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 '优秀';
	when score>100
	 then 
		select '错误';
	end case;
end $$
delimiter ;

5.存储过程中的循环：

-- 循环:
create table user(
 uid int primary key,
 username varchar(50),
 password varchar(50)
 );

delimiter $$
create procedure proc16_while(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');
		set i = i+1;
	end while lable;
end $$
delimiter;

call proc17_while_leave(10);


-- while+leave
truncate table user;



delimiter $$
create procedure proc17_while_leave(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;
end $$
delimiter;


-- while + iterate 跳过本次循环，进行下次循环

delimiter $$
create procedure proc18_while_iterate(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');
		set i = i+1;
		if i = 5 then
			iterate lable;
			end if;
			
	end while lable;
end $$
delimiter;




-- repeat




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



-- loop循环
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);

6.存储过程中的游标

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


delimiter $$
create procedure proc_18_cursor(in 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 = in_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 proc19_cursor();

7.存储结构中的异常处理：

delimiter $$
create procedure proc_21_cursor_handler(in 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 :直接终止程序
	2.触发条件：
		条件码：
		条件名：
		SQLWARNING
		NOT 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;
-- 
if flag = 1 then
select var_empno,var_ename,var_sal;
else 
leave label;
end loop label;
-- 关闭游标
close my_cursor;
end $$
delimiter;

call proc21_cursor();