MySQL03--存储过程、游标、视图、触发器

1.存储过程

• 创建存储过程：

  create procedure 名称(参数1，参数2...)
  begin
  .........
  end
  

• 删除存储过程：

  drop procedure 存储过程;
  

• 执行存储过程：

  cell 存储过程(参数1，参数2...);
  

• 设置变量：declare username varchar(32) default ‘张三’;

• 给变量赋值(set)： set username = ‘李四’;

• 给变量赋值(into)：

  create procedure sp_username()
  begin
     declare user_name varchar(32) default '张三' ;
     select username into user_name from t_user where id = 1;
     select user_name;
  end
  

• 入参出参:
  语法：in | out | inout param_name type

  • in:

    create procedure sp_param01(in age int)
    begin
        set @user_age = age;
    end
    

  • out:

     create procedure sp_param02(in name varchar(64),out age int(11))
     begin
         select a.age into age from tuser a where a.name = name; -- 如果出入参数和字段名一样，表必须起别名
     end
    
     set @age = '1';
     call sp_param02('王五',@age);
     select @age;
    

  • inout:

    create procedure sp_test03(inout name varchar(32))
    begin 
        select concat(a.name,',hello') into name from tuser a where a.name = name;
    end
    
    set @name = '张三';
    call sp_test03(@name);
    select @name;
    

• 判断：

  • if：
    语法：

    IF search_condition THEN statement_list
        [ELSEIF search_condition THEN statement_list] ...
        [ELSE statement_list]
    END IF
    

    例：

    create procedure sp_test04()
    begin
        set @num = 3;
    	if @num = 1 then set @num = 'a';
        elseif @num = 2 then set @num = 'b';
        else set @num = 'c';
        end if;
    	select @num;
    end
    call sp_test04();
    

  • case:
    语法：

     -- 语法一（类比java的switch）：
     CASE case_value
         WHEN when_value THEN statement_list
         [WHEN when_value THEN statement_list] ...
         [ELSE statement_list]
     END CASE
     -- 语法二：
     CASE
         WHEN search_condition THEN statement_list
         [WHEN search_condition THEN statement_list] ...
         [ELSE statement_list]
     END CASE
    

    例：

    create procedure sp_test06()
    begin
        set @num = 2;
        case when @num = 1 then set @num = 'a';
        when @num = 2 then set @num = 'b';
        else set @num = 'c';
        end case;
        select @num;
    end
    call sp_test06();
    

• 循环：

  • loop：
    语法：

    -- 语法
    [begin_label:] LOOP
     statement_list
    END LOOP [end_label]
    

    例：

    CREATE PROCEDURE sp_test07 () 
    BEGIN
        DECLARE mIndex INT DEFAULT 1;
        zhangsan:LOOP
            SELECT mIndex;
            IF mIndex > 7 THEN LEAVE zhangsan;
            else set mIndex = mIndex + 1;
            END IF;
        END LOOP zhangsan;
    END
    

  • repeat：
    语法：

    -- 语法
    [begin_label:] REPEAT
       statement_list
    UNTIL search_condition	-- 直到…为止，才退出循环
    END REPEAT [end_label]
    

    例：

    create procedure sp_test08()
    BEGIN
        declare mIndex int default 1;
        zhangsan:repeat
            select mIndex;
            set mIndex = mIndex + 1;
            until mIndex>5
        end repeat zhangsan;
    end
    

  • while：
    语法：

    -- 语法
    [begin_label:] WHILE search_condition DO
        statement_list
    END WHILE [end_label]
    

    例：

    create procedure sp_test09()
    BEGIN
        declare mIndex int default 1;
    	while mIndex<5 do
        	select mIndex;
        	set mIndex = mIndex+1;
        end while;
    end
    

• leave:流程退出，类似Java的break;

• iterate：结束当前循环，执行下次循环，类似Java的continue;

2.游标

用游标得到某一个结果集，逐行处理数据。类似jdbc的ResultSet。

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name

例：

-- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
	declare e_no int;
	declare e_name varchar(32);
	declare e_sal decimal(7,2);
	
	declare lp_flag boolean default true;
	
	declare emp_cursor cursor for 
		select e.empno,e.ename,e.sal
		from emp e,dept d
		where e.deptno = d.deptno and d.dname = dept_name;
		
	-- handler 句柄
	declare continue handler for NOT FOUND set lp_flag = false;
		
	open emp_cursor;
	
	emp_loop:loop
		fetch emp_cursor into e_no,e_name,e_sal;
		
		if lp_flag then
			select e_no,e_name,e_sal;
		else
			leave emp_loop;
		end if;
		
	end loop emp_loop;
	set @end_falg = 'exit_flag';
	close emp_cursor;
end$$

call sp_create_table02('RESEARCH');

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

3.视图

视图是一个虚拟表，视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]

• 作用：

  1. 封装复杂sql语句,提高复用性
  2. 逻辑放在数据库上面,更新不需要发布程序,面对频繁的需求变更更灵活

• 基本语法：

create view  视图名  as  select 字段名 from 表名;

注：mysql的视图中不允许有from后面的子查询,oracle可以

4.触发器

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行。

触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚

• 基本语法：

  create trigger 触发器名字 触发时间 触发事件 on 表 for each row
  begin
      -- 触发器内容主体，每行用分号结尾
  end
  

• 触发时间
  当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：

• before：表中数据发生改变前的状态

• after：表中数据发生改变后的状态

  注：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)

• 触发事件
  触发器是针对数据发送改变才会被触发，对应的操作只有

• INSERT

• DELETE

• UPDATE

  注意事项：

  • 在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器
  • 每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update
  • MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

• 查看触发器

  show triggers;
  

• 删除触发器

  drop trigger 触发器名字