存储过程、游标，触发器 &&&视图

于 2021-09-06 20:41:32 发布
阅读量75 收藏
点赞数
文章标签： mysql big data 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45803813/article/details/119487225
版权

存储过程procedure

  1. 创建存储过程，把一定数目的数据插入到一个表中，某类别的产品数量写入到一个变量中

    create procedure 存储过程(参数)
use purchase;
create table test_table (id INT PRIMARY KEY AUTO_INCREMENT,
a VARCHAR(10),
b VARCHAR(10));
drop procedure if exists insert_many_rows;
-----------------------------------------------------
delimiter $$
create procedure insert_many_rows (IN loops INT)
modifies sql data
begin
declare v1 int;
set v1=loops;

while v1 > 0 do
    insert into test_table(a, b) values('qpq', 'rst');
    set v1 = v1 - 1;
 end while
end;
$$
delimiter ;
---------------
call insert_many_rows(100);
select *from test_table;


    use 数据库的名字
desc 获取表的结构

--------------------------
delimiter$$
create procedure 存储结构的名字（in 输入的产品id ，out 输出的产品数量 类型）
reads sql data
begin
	select count(*) into 输出的产品数量；
	from product;
	where 表中的id=输入的产品id；
end;
delimiter;
--------------------------
set @输入的产品id=   ;
call 存储结构的名字（@输入的变量名字，@输出的变量名字）；
select @输出的变量名字

  2. 查看存储过程的定义

    show create procedure 存储过程名字

  3. 修改存储过程的定义

    alter procedure 存储过程名字

  4. 存储过程的异常和错误处理(为错误状态定义名称/如果。。则。。。)

    declare 异常名字 condition for 错误类型

游标cursor
示例7: 定义存储过程，通过游标逐行更新某一类产品的所有产品的价格：如果大于1000，则上涨5%；否则，上涨10%。

drop procedure if exists update_price_proc;
​-------------------------------
delimiter $$
create procedure update_price_proc (in v_sort_name VARCHAR(20))
modifies sql date;
begin
    -- 定义变量
    declare v_product_id INT;
   declare v_price decimal(8, 2);
   declare state char(20);
    -- 定义游标
   declare price_cur cursor
       select product_id, price 
        from product natural join sort 
        where sort_name = v_sort_name;
  -- 定义异常处理: continue 发生错误继续运行接下来的语句， 若 exit 表示 发生错误终止程序
    declare continue handler for 1329 set state = 'Error';
    
    open price_cur; -- 打开游标
    -------------------------
    repeat
        fetch price_cur into v_product_id, v_price;  -- 移动游标，获取数据
        IF (v_price > 1000) THEN 
           SET v_price = v_price * 1.05;
        ELSE 
           SET v_price = v_price * 1.1;
        END IF;
​
        update product 
        SET price = v_price 
        WHERE product_id = v_product_id;
        UNTIL state = 'Error'  -- 如果没发生异常，则state为null；如果发生1329异常，state的值为error，此时终止repeat
    end repeat;
    -----------------------
    
   close price_cur;  -- 关闭游标
END;
$$
delimiter ;

通过游标逐行更新某一类产品的所有产品的价格，如果。。。。则。。。否则。。。

触发器trigger

创建触发器

create trigger 触发器名字 时机 事件 on 表名 for each row
begin 
	触发程序
end;

查看触发器

show triggers from 表;
show create trigger 触发器的名字；

删除触发器

drop trigger 触发器名字

分析：需要分别定义从表和主表的更新和删除行为（before）

主表：

删除一行时，检查从表中是否存在参照值
更新一行时，检查从表中是否存在参照行值，可进一步定义从表的参照值是否也对应更新（cascade）
从表：

新增一行时，检查主表是否存在被参照值，如果不存在，则插入失败。
更新一行时，检查主表是否存在被参照值，如果不存在，则更新失败

-- sort表上的更新: 更新subsort表对应的sort_id on update cascade---

------------------------删除一行的时候查看是否存在参照值
drop trigger if exists sort_update_after_trigger;
​
DELIMITER $$
create trigger sort_update_after_trigger after update on sort for each row  -- 此处设置为after update是为了不和subsort上的before update trigger会冲突
begin
  update  subsort
    set sort_id = new.sort_id
    where sort_id = old.sort_id;
end;
$$
DELIMITER ;
​
-- sort表上的删除: 检查subsort表中有没有对应的sort_id记录，有则删除 on delete cascade
 drop trigger if exists sort_delete_before_trigger;
​
delimiter $$
create trigger sort_delete_before_trigger before delete on sort for each row
BEGIN
   delete subsort
    where sort_id = old.sort_id;
END;
$$
delimiter ;
​
-- subsort表上的插入: 检查sort表中是否存在需插入的sort_id，若不存在，引发一个异常
DROP TRIGGER IF EXISTS subsort_insert_before_trigger;
​
DELIMITER $$
CREATE TRIGGER subsort_insert_before_trigger BEFORE INSERT ON subsort FOR EACH ROW
BEGIN
    SELECT COUNT(*) INTO @row_count 
    FROM sort 
    WHERE sort_id=new.sort_id;
    
    IF (@row_count = 0) THEN
        INSERT INTO mytable VALUES (0);
    END IF;
END;
$$
DELIMITER ;
​
-- subsort表上的更新: 检查subsort表更新后的sort_id值，若在sort表中不存在，引发一个异常
DROP TRIGGER IF EXISTS subsort_update_before_trigger;
​
DELIMITER $$
CREATE TRIGGER subsort_update_before_trigger BEFORE UPDATE ON subsort FOR EACH ROW
BEGIN
    SELECT COUNT(*) INTO @row_count 
    FROM sort 
    WHERE sort_id=new.sort_id;
    IF (@row_count = 0) THEN
        INSERT INTO mytable VALUES (0);
    END IF;
END;
$$
DELIMITER ;
-- 如果subsort和sort表有外键约束，先删除
SHOW CREATE TABLE SORT;
SHOW CREATE TABLE SUBSORT;
​
SELECT * FROM SORT;
SELECT * FROM SUBSORT WHERE SORT_ID = 93;
​
-- 插入subsort
insert into subsort(subsort_id, subsort_name, sort_id)
values (9301, 'test', 93); -- 插入失败， error code 1146
​
insert into sort (sort_id, sort_name)
values (93, 'test-sort'); -- 执行sort_id中的插入之后
​
insert into subsort(subsort_id, subsort_name, sort_id)
values (9301, 'test', 93); -- 插入成功
​
-- 更新sort
set sql_safe_updates=0;
​
update sort
set sort_id = 94
where sort_name = 'test-sort';
​
select * from sort;
select * from subsort where subsort_name = 'test';
​
-- 更新subsort
update subsort
set sort_id = 95
where subsort_name = 'test'; -- 执行失败, error 1146
​
-- 删除sort
delete from sort
where sort_name = 'test-sort';
​
select * from sort;
select * from subsort where subsort_name = 'test';

事件调度器event scheduler
create event event_name
on schedule schedule
do sql_statement
创建（立即启动，10秒执行一次，每天定时执行）
查看show event from show create event
修改alter event
删除drop event if exists

创建一个立即启动的事件。


truncate demo_tb;
​
drop event if exists immediate_event;
​
DELIMITER $$
CREATE EVENT immediate_event 
ON SCHEDULE AT now()
ON COMPLETION NOT PRESERVE
ENABLE
DO 
BEGIN
    insert into demo_tb(name) values('demo');
END;
$$
delimiter ; -- 创建成功，执行完毕任务后，该事件被删除
​
SHOW EVENTS;
​
select * from demo_tb;

创建一个每十秒执行一次的

DROP EVENT IF EXISTS interval_event;
​
DELIMITER $$
CREATE EVENT interval_event 
ON SCHEDULE EVERY 10 SECOND
ON COMPLETION PRESERVE
ENABLE
DO 
BEGIN
    insert into demo_tb(name) values('demo_10S');
END;
$$
DELIMITER ;
​
SHOW EVENTS;
SHOW EVENTS FROM purchase; -- 查看purchase上的所有事件
SELECT * FROM information_schema.events; -- 查看所有事件
SHOW CREATE EVENT interval_event; -- 查看定义
​
SELECT * FROM demo_tb;
ALTER EVENT interval_event DISABLE; -- 临时关闭事件

创建一个2020年5月21号起每天00:00:00执行的事件


DROP EVENT IF EXISTS repeat_event_from;
DELIMITER $$
CREATE EVENT repeat_event_from
ON SCHEDULE EVERY 1 DAY STARTS timestamp('2020-05-21 00:00:00') ENDS timestamp('2020-05-27 00:00:00')
ON COMPLETION PRESERVE
ENABLE
DO 
BEGIN
    insert into demo_tb(name) values('demo_INTERAL_0000');
END;
$$
DELIMITER ;

SELECT timestamp('2020-12-21') + INTERVAL 1 DAY;
SELECT '2020-12-21 12:00:00' + INTERVAL 1 DAY;
SELECT DATE_ADD('2020-12-21 00:00:01', INTERVAL 1 DAY);

编程应用
迭代查询，以以前的一次结果集作为条件，继续当前查询，直至没有新的结果集产生
基于触发器实现对日志表的自动管理，（是否满足某一状态）

表中行数不大于10000

USE PURCHASE;
CREATE TABLE his_log(id int primary key auto_increment,
                    userid char(50) not null,
                    operate_time timestamp default current_timestamp());
DELIMITER $$                    
CREATE TRIGGER his_log_constant_rows_trigger BEFORE INSERT ON his_log FOR EACH ROW
BEGIN
    DECLARE num_rows INT;
    SELECT COUNT(*) INTO num_rows
    FROM his_log;
    IF (num_rows >= 10000) THEN
        DELETE FROM his_log
        ORDER BY operate_time LIMIT 1;
    END IF;
END;
$$
DELIMITER ;

事件调度器实现定时管理日志表

事件调度器和存储过程，定时检查外键约束

创建视图
所有字段创建视图，查看视图的结构（desc）定义(show create view)，指定表中字段创建视图，未分组查询构建视图。为多表查询构建驶入，视图字段的重命名

create or replace view 视图的名字
as---视图要执行的操作
select查询语句

查询 product 表中类别编号为 11 的所有记录，创建视图 view_product

create view 视图的名字
as
select * from product
where sort_id=11;
show tables

查看视图的所有记录(select)

修改/重新定义视图

create or replace view 视图名字
as

alter view 视图名字 
as select 查询语句

通过视图操作基本表/改表中的数据

update 视图名字
set
where 
-------------------
insert into 视图名字（列名1,2）
values
-----------------------
delete from 视图名字
where

删除视图
drop view if exists 视图名字；

不疑Luna
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值