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的异常处理