[黑马程序员] 进阶篇-存储过程

[黑马程序员] 存储过程

定义：事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发

人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的

特点：

• 封装，复用
  • 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可
• 可以接收参数，也可以返回数据
  • 再存储过程中，可以传递参数，也可以接收返回值
• 减少网络交互，效率提升
  • 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了

语法

• 创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ]) 
BEGIN
    -- SQL语句 
END ;

• 调用

CALL 名称 ([ 参数 ]);

• 查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
-- 查询指 定数据库的存储过程及状态信息 SHOW CREATE PROCEDURE 存储过程名称 ;
-- 查询某个存储过程的定义

• 删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；

变量

• 系统变量

  • 全局变量
  • 会话变量
  • 查看系统变量：

  SHOW [ SESSION | GLOBAL ] VARIABLES ; 
  -- 查看所有系统变量 
  SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; 
  -- 可以通过LIKE模糊匹配方 式查找变量 
  SELECT @@[SESSION | GLOBAL] 系统变量名; 
  -- 查看指定变量的值
  

  • 设置系统变量

  SET [ SESSION | GLOBAL ] 系统变量名 = 值 ; 
  SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
  

    - 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量
    - mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置
  

• 用户定义变量

  • 用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接
  • 赋值

  SET @var_name = expr [, @var_name = expr] ... ; 
  SET @var_name := expr [, @var_name := expr] ... ;
  
  SELECT @var_name := expr [, @var_name := expr] ... ; 
  SELECT 字段名 INTO @var_name FROM 表名;
  

  • 使用

  SELECT @var_name ;
  

• 局部变量

  • 声明

  DECLARE 变量名 变量类型 [DEFAULT ... ] ;
  

  • 赋值

  SET 变量名 = 值 ; 
  SET 变量名 := 值 ; 
  SELECT 字段名 INTO 变量名 FROM 表名 ... ;
  

• if

  • 语法

  IF 条件1 THEN
      ..... 
  ELSEIF 条件2 THEN -- 可选 
      ..... 
  ELSE -- 可选 
      ..... 
  END IF;
  

  • 案例

  create procedure p3()
  begin
      declare score int default 58;
      declare result varchar(10);
      if score >= 85 then
          set result := '优秀';
      elseif score >= 60 then
          set result := '及格';
      else
          set result := '不及格';
      end if;
          select result;
      end;
  
  call p3();
  

• 参数

  • 参数类型
    • IN 该类参数作为输入，也就是需要调用时传入值
    • OUT 该类参数作为输出，也就是该参数可以作为返回值
    • INOUT 既可以作为输入参数，也可以作为输出参数
  • 语法

    CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ]) 
    BEGIN
        -- SQL语句 
    END ;
    

  • 案例

    create procedure p5(inout score double)
    begin
        set score := score * 0.5;
    end;
    set @score = 198;
    call p5(@score);
    select @score;
    

• case

  create procedure p6(in month int)
  begin
      declare result varchar(10)
      case
          when month >= 1 and month <= 3 then
              set result := '第一季度';
          when month >= 4 and month <= 6 then
              set result := '第二季度';
          when month >= 7 and month <= 9 then
              set result := '第三季度';
          when month >= 10 and month <= 12 then
              set result := '第四季度';
          else
              set result := '非法参数';
      end case ;
      select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
  end;
  call p6(16);
  

• while

  • while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句

  create procedure p7(in n int)
  begin
      declare total int default 0;
      while n>0 do
          set total := total + n;
          set n := n - 1;
      end while;
      select total;
  end;
  call p7(100);
  

• repeat

  • repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环

  create procedure p8(in n int)
  begin
      declare total int default 0;
      repeat
          set total := total + n;
          set n := n - 1;
      until n <= 0
      end repeat;
      select total;
  end;
  call p8(10);
  

• loop

  • LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环
  • LEAVE ：配合循环使用，退出循环
  • ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
  • 案例:计算从1到n之间的偶数累加的值，n为传入的参数值

  create procedure p10(in n int)
  begin
      declare total int default 0;
      sum:loop
          if n<=0 then
              leave sum;
          end if;
          
          if n%2 = 1 then
              set n := n - 1;
              iterate sum;
          end if;
          
          set total := total + n;
          set n := n - 1;
  end loop sum;
  
  call p10(100);
  

存储函数

• 计算从1累加到n的值，n为传入的参数值。

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    return total;
end;
select fun1(50);

触发器

• 触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。
• 使用别名OLD和NEW来引用触发器中发生变化的记录内容
• 现在触发器还只支持行级触发，不支持语句级触发
• 创建语句

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE 
ON tbl_name FOR EACH ROW -- 行级触发器 
BEGIN
trigger_stmt ; 
END;

• 查看

SHOW TRIGGERS ;

• 删除

DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定schema_name，默认为当前数 据库 。

• 案例

create trigger tb_user_insert_trigger 
after insert on tb_user for each row 
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES(null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession)); 
end;