存储过程procedure、触发器trigger

一、存储过程procedure

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

select user(),database(),version(),now();
select * from stu limit 10;

-- 建立存储过程
delimiter $
create procedure sss2()
begin
    select * from stu limit 10;
    /*  声明变量 if语句 loop循环语句 sql语句 */
end $
delimiter ;

-- 调用执行存储过程
call sss;
call sss();

-- 查看wxdb数据库所有存储过程名称
select SPECIFIC_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'wxdb' and ROUTINE_TYPE = 'PROCEDURE';

-- 建立查看数据库的存储过程
delimiter $
create procedure sp(dbname varchar(100))
begin
    select SPECIFIC_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = dbname and ROUTINE_TYPE = 'PROCEDURE';
end $
delimiter ;
-- 删除存储过程
drop procedure sp;
call sp('wxdb');

call sp('hbcf')

use mysql;
select user(),database(),version(),now();
-- 当前库在mysql上，可以跨库调用存储过程
call wxdb.sp('wxdb');

use wxdb;

call sp('wxdb');
show global variables ;
show variables ;
select @@version,@@port;

-- 查看某数据库存储
show procedure status where db='wxdb';

select count(*) from stu;

delimiter $
create procedure sp_count(in tname varchar(50),out cc int)
begin
    --  SET @sqlcmd = concat('select count(*) into ',cc,' from ', tname);
     -- prepare stmt from @sqlcmd;
     -- execute stmt;
     -- deallocate prepare stmt;

    select count(*) into cc from stu;
end $
delimiter ;
drop procedure sp_count;
call sp_count('stu',@aa);
select @aa;

use d3;
/*
   在mysql中，语句默认是 ; 号结束，mysql存储过程中有大量的语句代码如果，转换直接执行。

   delimiter $
   最后建立存储过程完后，再delimiter ; 修改为默认
*/
-- 存储过程　procedure
delimiter $
create procedure sp_stu()
begin
    select * from stu;
end$
delimiter ;

-- 如何调用存储过程，过程预编译的，保存到服务器数据库中，执行速度要快。
call sp_stu;
call sp_stu();

-- 字符串函数判断有没有字符
select instr('李四','%') = 0 and instr('李_','_') = 0;

-- 普通查询
select * from stu;

-- 建立存储过程 in 传入  out 传出参数，可理解返回值
delimiter //
create procedure sp2(in nn varchar(15),out scount int)
begin
    if instr(nn,'%') = 0 and instr(nn,'_') = 0 then
    select count(*) into scount from stu where name = nn;
    else
    select count(*) into scount from stu where name like nn;
    end if;
end //
delimiter ;

drop procedure if exists sp2;

-- 调用
call sp2('李%',@c);
select @c;

call sp2('李四',@n);
select @n;

-- 普通语句
select count(*) from stu where name like '李%';

-- 删除存储过程
drop procedure sp_stu;
drop procedure if exists sp_stu;

-- 查看wxdb数据库所有存储过程名称
select SPECIFIC_NAME from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_SCHEMA = 'd3' and ROUTINE_TYPE = 'PROCEDURE';

-- 建立存储过程，查看指定的数据库有哪些存储过程
delimiter $
create procedure sp(in dbname varchar(100))
begin
    if dbname is null then
    select SPECIFIC_NAME from INFORMATION_SCHEMA.ROUTINES
    where ROUTINE_SCHEMA = (select database()) and ROUTINE_TYPE = 'PROCEDURE';
    else
        select SPECIFIC_NAME from INFORMATION_SCHEMA.ROUTINES
    where ROUTINE_SCHEMA = dbname and ROUTINE_TYPE = 'PROCEDURE';
    end if;
end $
delimiter ;

--
drop procedure sp;

call sp('d3');
call sp(null);

call sp('d1');

call sp();

create table t_goods(
id char(36) primary key,
name varchar(100) not null,
price decimal(8,1)
)
drop table t_goods;
-- 建立触发器 mysql 5.6.51
delimiter $
create trigger tgt1 before insert on t_goods for each row
begin
-- if new.id is null then
set NEW.id = uuid();
-- end if;
end $
delimiter ;
create table t_goods(
id char(36) primary key default(uuid()), /*8.0.13 支持表达默认值可以实现
uuid自动填写*/
name varchar(100) not null,
price decimal(8,1)
)
drop table t_goods;
-- 建立触发器 uuid主键 使用触发器 实现uuid 主键自动填写
create table t_goods(
id char(36) primary key,
name varchar(100) not null,
price decimal(8,1)
)
-- 建立触发器 mysql 8.0
delimiter $
create trigger tgt1 before insert on t_goods for each row
begin
if new.id is null then
set new.id = uuid();
end if;
end $
delimiter ;
drop trigger tgt1;
insert t_goods value(uuid(),'优盘32G',80);
insert t_goods set name='键盘001',price=110;

insert t_goods(name,price) value('键盘0023',100);
truncate t_goods;
select * from t_goods;
-- 触发器
-- 是一种自动执行存储过程，insert delete update 执行
-- 学生信息表
create table s1(
id int unsigned auto_increment,
name varchar(30),
score tinyint unsigned,
dept varchar(50),
primary key(id)
);
insert into s1 value(null,'李四',60,'计算机科学');
insert into s1 value(null,'赵六',30,'会计');
insert into s1 value(null,'李四玉',70,'计算机科学');
insert into s1 value(null,'周五',80,'英语');
insert into s1 value(null,'赵强',10,'计算机科学');
insert into s1 value(null,'王七',98,'英语');
-- 学生信息补考表(补考表数据 根据s1数据变化，自动变化)
create table s2 like s1;
-- 建立触发器 new 表 old 表 临时表 ，
delimiter //
create trigger t1 after insert on s1 for each row
begin
if NEW.score<60 60 then insert into s2 values(new.id,new.name,new.score,new.dept); end if; delimiter ; create trigger t2 after delete on s1 for each row begin from where old.id="id;" t3 update if new.score < replace value(new.id,new.name,new.score,new.dept); else id="new.id;" -- 删除触发器 drop t3; select * s1; s2; 查看当前数据库触发器代码 show triggers d3; code></60>

Original: https://www.cnblogs.com/zcxxcvbn/p/15925003.html
Author: 一份人间烟火
Title: 存储过程procedure、触发器trigger