数据库高级编程
- 存储过程
- 游标的操作
- 条件定义和处理
- 判断和循环
- 自定义函数
- 触发器
存储过程 procedure
作用: 是数据库的编程、可以将复杂的业务使用 存储过程来进行定义、可以减少与数据库的交互次数
存储过程的定义
delimiter ;; -- 设置 语句的 结束标识,默认是 ;
create procedure <procedureName>(参数列表)
begin
-- 实现功能的业务SQL
end ;;
delimiter ; -- 还原标识
过程的参数形式
- in 参数 (输入参数)
- out 参数 (输出参数)
- inout 参数(输入输出参数)
如果在定义参数的时候,没有写参数的类型,则默认是 输入参数
语法 in|out|inout
调用 存储过程
set @inoutparam = '' ;
call <procedureName>(参数,....@inoutparam) ;
select @inoutparam ; -- 获取 输出参数
删除存储过程
drop procedure <procedureName> ;
变量的声明 和 赋值
-- 声明变量
declare <varName> <varType> ;
-- 声明变量、并设置默认值
declare <varName> <varType> default <defaultValue>;
-- 修改 变量的值
set <varName> = <varValue> ;
流程控制-判断
if <condition> then
...
elseif <condition> then
...
else
...
end if ;
流程控制-循环
- loop循环
<labelName>: loop
....
leave <labelName> ; -- 退出循环
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>(参数列表)
returns <returnType>
begin
...
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 ;;
删除函数
drop function <functionName> ;
触发器
不推荐使用,会产生一些很难想到的问题
会自动调用
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 ;