6.数据库高级编程

数据库高级编程

• 存储过程
• 游标的操作
• 条件定义和处理
• 判断和循环
• 自定义函数
• 触发器

存储过程 procedure

作用: 是数据库的编程、可以将复杂的业务使用 存储过程来进行定义、可以减少与数据库的交互次数

类似于Java中的方法

存储过程的定义

delimiter ;;  -- 设置 语句的 结束标识，默认是 ; 

create  procedure  <procedureName>(参数列表) 
begin 
   -- 实现功能的业务SQL(每条sql语句后面必须加分号)
end ;;

delimiter ;  -- 还原标识

过程的参数形式

• in 参数 (输入参数)

  delimiter $
  create procedure query_book(in v_id int)
  begin
  	select * from t_book where id = v_id;
  end $
  
  call query_book(1);
  

• out 参数 (输出参数)

  delimiter $
  create procedure query_book(in v_price double,out v_count int)
  begin
  	select * from t_book where price>v_price;
  	select count(1) into v_count from t_book where price>v_price;
  end $
  
  delimiter ;  -- 恢复结束标识
  
  call query_book(20,@number);
  select @number;
  

• inout 参数（输入输出参数)

  delimiter $
  create procedure add1(inout a int,inout b int)
  begin
  	set a = a*2;
  	set b = b*2;
  end $
  
  set @a=5;
  set @b=2;
  call add1(@a,@b);
  select @a,@b;  -- 10  4
  

如果在定义参数的时候，没有写参数的类型，则默认是 输入参数in

语法 in|out|inout

调用 存储过程

set @inoutparam = '' ;
call <procedureName>(参数,....@inoutparam) ;
select @inoutparam ;  -- 获取 输出参数

删除存储过程

drop procedure  <procedureName> ;  -- 一次只能删除一个存储过程

查看存储过程

show create procedure add1;

变量的声明 和 赋值

-- 声明变量 
declare  <varName>  <varType> ;

-- 声明变量、并设置默认值
declare <varName>  <varType> default <defaultValue>; 

-- 修改 变量的值 
set <varName> = <varValue> ;

流程控制-判断

• if函数（实现简单的双分支）

  if(a>b,a,b) -- 即a>b为true，则输出a，否则输出b

• if结构（实现多重分支）

if  <condition>  then 语句1;
...
elseif <condition> then 语句2;
...
else  语句n;
...
end if ;    -- 应用在begin end中

流程控制-循环

iterate 类似于continue，继续，结束本次循环，继续下一次

leave 类似于break, 跳出，结束当前所在的循环

• loop循环

<labelName>: loop

	....
    leave <labelName> ;  -- 退出循环(break)
      
end loop ;

• repeat 循环

repeat 
  ....
  until <condition>    -- 直到满足某个条件退出，注意 不能有分号

end repeat ;

• while 循环

while <condition> do
	...
end while ;

游标

存储 查询的结果集, 游标不是 查询语句

游标有 4个操作，分别是 声明游标、打开游标、提取游标中的数据、关闭游标

声明游标

declare done int default 0 ; -- 代表 游标中的数据没有提取完成
-- 声明游标
declare <cursorName> cursor for select ... ;
-- 声明一个处理器，用来处理游标中的数据是否提取完成
declare continue handler for not found set done = 1 ;

打开游标

open <cursorName> ;

提取游标

x: loop 
  fetch <cursorName>  into  <varName>, ...  <varName> ;
  if done = 1 then 
  	 leave x ;
  end if ;
end loop ;

关闭游标

close <cursorName> ;

自定义函数

create function <functionName>(参数列表)
-- 参数列表（参数名，参数类型--a int）
returns <returnType>
begin 
   -- ...函数体（必须有return语句，否则会报错）
end ;

函数一般不对表做增删改处理

函数一般参数 类型为输入参数

set global log_bin_trust_function_creators=TRUE;  -- 解决函数创建报错的问题

delimiter ;; 
create function tel(v_tel varchar(11))
returns varchar(11) 
begin 
	declare prefix varchar(3) ;
	declare suffix varchar(4) ;
	-- 获取手机号前3位， 后四位
	set prefix = substr(v_tel, 1 , 3) ;
	set suffix = substr(v_tel, 8) ;
    -- 函数必须要有返回值
	return concat(prefix, '****', suffix); 
end ;;

调用函数

select 函数名（参数列表）

删除函数

drop function <functionName> ;

函数与存储过程的区别

存储过程：可以有0至多个返回值（批量插入，批量更新）

函数：有且仅有一个返回值，适合做处理数据后返回一个结果

触发器

不推荐使用，会产生一些很难想到的问题

特点：自动调用

create trigger <triggerName> before|after  [insert|update|delete] on <tableName> for each row
begin 
  ... 
end ;

old : 代表 原数据库中的数据，一般适用于 update 和 delete 动作

new : 代表 新值，一般适用于 insert 和 update

触发器是基于表的，如果表被删除，触发器会自动丢失

delimiter ;;
CREATE TRIGGER auto_back_password AFTER UPDATE ON t_user FOR EACH ROW begin 
     -- 将 t_user 表中的老密码，写入到 t_user_history 表
	 -- old : 原数据对象 
	 -- new : 新数据对象
	 insert into t_user_history(tel, password, create_time) 
	 values( old.tel, old.password, now() );
end;;

查看触发器

show triggers ;  -- 查看所有的触发器
show create trigger auto_back_password ; -- 查询某个触发器的代码

删除触发器

drop trigger auto_back_password ;