mysql的学习笔记（阶段三）

-- mysql存储过程
			delimiter $$
			CREATE PROCEDURE proc01 () 
			BEGIN
				SELECT
					emp_name,
					emp_salary 
				FROM
					emp;
			END $$
			delimiter;-- 调用存储过程
CALL proc01 ();-- 很类似调用函数(实际上就是将sql封装为函数)


--  使用变量
delimiter $$
create procedure prooc02()
begin
	declare var_name01 varchar(20) default 'aaa'; -- 声明变量(局部变量)
	set var_name01 = '张三'; -- 给变量赋值
	select var_name01; --  输出变量的值
end $$
delimiter ;

call prooc02();
--  可以使用 select .. into 为变量赋值
select emp_name into var_name from emp where dept_no = 0001; -- 将此写入存储过程代码中

--  定义一个用户变量
-- @var_name

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

-- 看看用户变量是否可以在外部使用
select @var_name01;

-- 系统变量(mysql提前定义的，不能用户自己定义)
 -- 系统变量-全局
 -- @@global.var_name;
 
-- 查看所有的全局变量
show global variables;
select @@global.auto_increment_increment; -- 查看指定的全局变量
--  修改全局变量
select @@global_sort_buffer_size = 30000;
-- 会话变量
show session variables;
set session sort_buffer_size = 50000; -- 修改
set @@session.sort_buffer_size = 60000;

-- 并不是所有的会话变量可以修改

-- 封装有参数的存储过程
-- 采用in进行传参
delimiter $$
create procedure  proc06(in param_empno int)
begin 
	select * from emp where id = param_empno;
end $$
delimiter;

call proc06(2);
drop procedure proc06;

-- out传参(一般用在需要返回值的时候)
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(1001,@o_ename);
select @o_ename; -- 查询返回的名字




-- inout 从外部传入的传入的参数可以在函数内部进行修改，然后修改完之后还可以自动传出来
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 proc_12_if(in score int)
begin
	if score < 60 
	   then 
				select "不及格";
			elseif score >=60 and score < 90
				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)

drop procedure if exists proc_13_if;
delimiter $$
create procedure proc_13_if(in ename varchar(20))
begin
	declare var_sal decimal(7,2);
	declare result varchar(20);
	select emp_salary into var_sal from emp where emp_name = ename;
	if var_sal <3000
		then 
			set result = "试用薪资";
	else
		
			set result = "转正薪资";
			
	end if;
	select result;
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);


-- 循环
-- 创建一个测试表
create table jgdabc
(
	uid int primary key,
	username varchar(50),
	password varchar(50)

);
--  向表中添加指定条数据
delimiter $$
create procedure proc16_while(in insertCount int )
begin
declare i int default 1;
	while i <=insertCount do 
		insert into jgdabc(uid,username,password)values(i,concat('user-',i),'123456');
			set i = i+1;
	end while;
end $$
delimiter;
 -- 如果要用到leave跳出，那么必须使用lable
 
delimiter $$
create procedure proc16_while(in insertCount int )
begin
declare i int default 1;
	lable : while i <=insertCount do 
		insert into jgdabc(uid,username,password)values(i,concat('user-',i),'123456');
			set i = i+1;
			if i =5 then
				leave lable;
			end if;
			
	end while lable;
end $$
delimiter;
 -- iterate 相当于continue
call proc16_while(10);

delimiter $$
create procedure proc16_while(in insertCount int )
begin
declare i int default 1;
	lable : while i <=insertCount do 
		insert into jgdabc(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;

delimiter $$
create procedure proc16_while(in insertCount int )
begin
declare i int default 1;
	lable : while i <=insertCount do 
	
			set i = i+1;
			if i =5 then
				iterate lable; --  死循环
			end if;
				insert into jgdabc(uid,username,password)values(i,concat('user-',i),'123456');
	end while lable;
	select "循环结束";
end $$
delimiter;
--  interate 跳出本层循环，继续下一次循环

-- repeat --- until
-- 相当于do -- while()
delimiter $$
create procedure proc16_while(in insertCount int )
begin
declare i int default 1;

	   label:repeat
				insert into jgdabc(uid,username,password)values(i,concat('user-',i),'123456');
			  set i = i+1;
				until i >insertcount
			end repeat label;
			
	    select "循环结束";
end $$
delimiter;

--  loop
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
	declare i in 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);
	
	-- cursor(游标)用来存储查询的结果集
	-- 声明游标
	declare cursor_name cursor for select_statemant  -- 语法
	-- 打开游标
	open cursor_name
	-- 取值语法
	fetch cursor_name into var_name 
	-- 关闭游标
	close cursor_name
	
	-- 操作游标
	-- 需求，输入一个部门名，查询员工的编号，名字，薪资，将查询的结果集添加到游标
	delimiter $$
	create procedure proc19_cursor(in in_dname varchar(50))
	begin
	--  定义局部变量
	declare var_empno int;
	declare var_ename varchar(50);
	declare var_sal   decimal(7,2);
	-- 声明游标
	declare my_cyrsor cursor for
		select empno,ename,salary
		from dept a,emp b
		where a.deptno = b.deptno and a.dname = in_dname;
	-- 打开游标
	open my_cursor;
	label:loop
	-- 通过游标获取值(fetch一次获取一行，所以写一个循环)
		fetch my_cursor into var_empno,var_name,var_sal; -- 这样一直向下获取，当获取不到的时候会自己中断，然后报一个异常。
		select var_empno,var_ename,var_sal;
	end loop label;
	-- 关闭游标
	close my_cursor;
	end $$;
	delimiter ;
	call proc19_cursor('销售部');
	
	-- 句柄
	-- handler 句柄
	
	user mysql;
	drop procedure if exists proc21_cursor_handler;
	delimiter $$
	create procedure proc21_cursor(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_name;
			--  定义句柄，定义异常的处理方法
			--  异常处理完之后，程序该怎么执行
			-- contunue 继续执行剩余代码
			-- exit 直接终止程序
			-- undo : 不支持
			--  触发条件：条件码....,条件名....
			-- 异常触发之后执行什么代码
				-- 设置flag的值
		declare continue handler for 1329 set flag =0;
		-- 判断标志位
		-- 如果flag地值为1就执行，反之，不执行
		
		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 if;
		end loop label;
		close my_cursor;
			
			
	
-- 	句柄就是mysql的异常处理