3-触发器、存储过程

如果触发器和存储过程是在命令行中运行，一定要更改结尾标志符。

　　delimiter ||
　　delimiter ;

1、触发器：

　　　　触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

　　　　举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，
　　　　如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，
　　　　触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。
　　　　当然触发器并不是只能进行插入操作，还能执行修改，删除。

　　　　

**************************************************************************触发器***********************************************************************
1. 触发时间（after/before）: 

            after : 执行了sql之后再执行这个触发器内的sql
            before : 在执行了sql之前执行这个触发器内的sql

 2. 触发事件（insert/update/delete）：
      
 3.语法：
        create trigger trigger_name

        after/before   insert/update/delete on tb_name

        for each row

        begin
                   sql 语句：                    //（触发的语句一句或者多句）
        end;

 4. 显示触发器 ：
            show triggers;
            show create trigger 数据库名.触发器名                             

 5. 删除触发器 ：       drop trigger trigger_name;
    —————————————————————————————————————————————————————————————
    ①insert触发器
    —————————————————————————————————————————————————————————————
    drop table if exists student;
    create table student(
        id int,
        name varchar(100)
    );

    drop table if exists copy;
    create table copy(
        copy_id int,
        copy_name varchar(100)
    );
    —————————————————————————————————————————————————————————————

    drop trigger if exists ins_stu;
    create trigger ins_stu 
    after insert on student 
    for each row 
    begin 
        insert into copy(copy_id,copy_name) values(new.id, new.name); 
    end;
    

    测试： insert into student values(1,'A');

               select * from student;
               select * from copy;
    —————————————————————————————————————————————————————————————
    ②update触发器
    —————————————————————————————————————————————————————————————
    DROP TRIGGER if EXISTS upd_stu;
    CREATE TRIGGER upd_stu
    AFTER update ON student
    FOR EACH ROW
    BEGIN
        update copy set copy_name = new.name where copy_id = new.id;
    END;

    测试：update student set name = 'AAA' where id = 1;
              select * from student;
              select * from copy;
    —————————————————————————————————————————————————————————————
    ③delete触发器
    —————————————————————————————————————————————————————————————
    DROP TRIGGER if EXISTS del_stu;
    CREATE TRIGGER del_stu
    AFTER DELETE ON student
    FOR EACH ROW
    BEGIN
        delete from copy where copy_id = old.id;
    END;

    测试：delete from student where id = 1;
              select * from student;
              select * from copy;

2、存储过程

       1.存储过程：

     　　　　 存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。
     　　　　 当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，
     　　　　 存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

       2.优点：

    　　　　　　①增强SQL语言的功能和灵活性
　　　　　　　　②标准组件式编程：
    　　　　　　③较快的执行速度
    　　　　　　④减少网络流量：
    　　　　　　⑤作为一种安全机制来充分利用

3.语法:

   　　　　 CREATE PROCEDURE myproc(OUT s int)
        　　BEGIN
              　　SELECT COUNT(*) INTO s FROM students;
        　　END

 4.变量：

    　　　　①全局变量一般以@开头，局部变量只存在于begin....end中
    　　　　②数据类型为MySQL的数据类型：数值、字符串、日期都可以。

    　　　　③语法：
        　　　　全局变量：SET 变量名 = 变量值 
        　　　　局部变量：declare 变量名 数据类型 [default value];

    　　　　④例子：
          　————————————————————————————————————————————————————————————
        　　　　SET @y='Goodbye Cruel World';
        　　　　SELECT @y;                //定义全局变量
    
        　　　　begin
            　　　　declare num int default 10;        //定义局部变量
            　　　　select num;
        　　　　end
          　————————————————————————————————————————————————————————————

　5.参数：(原因：没有return的概念)

    　①IN        ：  必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值(输入参数)
    　②OUT     ：  该值可在存储过程内部被改变，并可返回（输出参数）
    　③INOUT  :    调用时指定，并且可被改变和返回（输入输出参数）

    　④例子      ： 
      ————————————————————————————————————————————————————————————
        IN：
        **********************存储过程*****************

         　　　　DROP PROCEDURE if EXISTS in_param;
           　　　　CREATE PROCEDURE in_param(IN p_in int)
             　　  BEGIN
                 　　　　SELECT p_in;
                　　　　 SET p_in=2;
                　　　　 SELECT p_in;
            　　　　END;

        **********************调用********************

        　　　　SET @p_in=1;
        　　　　CALL in_param(@p_in);
        　　　　SELECT @p_in;
        
        **********************结果********************
        　　　　1      2     1

out：
        **********************存储过程*****************

         DROP PROCEDURE if EXISTS out_param;
           CREATE PROCEDURE out_param(OUT p_out int)
             BEGIN
                 SELECT p_out;
                SET p_out=2;
                SELECT p_out;
            END;

        **********************调用********************
    
        SET @p_out=1;
        CALL out_param(@p_out);
        SELECT @p_out;

        **********************结果********************
        null      2     2


        inout：
        **********************存储过程*****************

         DROP PROCEDURE if EXISTS inout_param;
           CREATE PROCEDURE inout_param(INOUT p_inout int)
             BEGIN
                 SELECT   p_inout;
                SET        p_inout=2;
                SELECT  p_inout;
            END;

        **********************调用********************
    
        SET @p_inout=1;
        CALL inout_param(@p_inout);
        SELECT @p_inout;

        **********************结果********************
        1      2     2

        ————————————————————————————————————————————————————————————    

 7.存储过程查询：
    
    查询所有：
        SHOW PROCEDURE STATUS;
        SHOW PROCEDURE STATUS WHERE db='数据库名'；
    查询单个：
        SHOW CREATE PROCEDURE 数据库名.存储过程名;

 8.存储过程删除：

    DROP PROCEDURE  存储过程名；

 9.控制语句：    
    ————————————————————————————————————————————————————————————
    ①IF-THEN-ELSE-END IF （if语句）
    ————————————————————————————————————————————————————————————

        create table person(
            age int,
            info varchar(100)
        );

        要求：如果年龄大于18，info = “成年”，
                  如果年龄小于18，info = “未成年”

     *********************存储过程*****************

        DROP PROCEDURE IF EXISTS proc2;
        CREATE PROCEDURE proc2(IN age int)

          BEGIN
                IF age > 18 THEN
                set @info = '成年';
                      insert into person values(age,@info);
               ELSE
                      set @info = '未成年';
                insert into person values(age,@info);
                 END IF ;

             select * from person;
          END ;

     **********************调用********************
     　　CALL proc2(16);

    ————————————————————————————————————————————————————————————
    ②CASE-WHEN-THEN-ELSE（case语句）
    ————————————————————————————————————————————————————————————

     **********************存储过程*****************

         DROP PROCEDURE IF EXISTS proc3;
         CREATE PROCEDURE proc3(IN age INT)
             BEGIN
                 CASE age

                    WHEN 10 THEN
                set @info = '我十岁了';
                      insert into person values(age,@info);
                    WHEN 20 THEN
                 set @info = '我二十岁了';
                      insert into person values(age,@info);
                    ELSE
                 set @info = '暂无年龄信息';
                      insert into person values(age,@info);
                  END CASE ;

             select * from person;
             END ;

        **********************调用********************
        CALL proc3(10);

    ————————————————————————————————————————————————————————————
    ③WHILE-DO…END-WHILE（while.....do语句）
    ————————————————————————————————————————————————————————————

        **********************存储过程*****************

         DROP PROCEDURE IF EXISTS proc4;
         CREATE PROCEDURE proc4()
             BEGIN
                  SET @var=0;
                  WHILE @var<6 DO
                        select @var;
                        SET @var=@var+1;
                 END WHILE ;
            END;

        **********************调用********************
        CALL proc4();
    ————————————————————————————————————————————————————————————
    ④REPEAT...END REPEAT（do....while语句）
    ————————————————————————————————————————————————————————————

        **********************存储过程*****************

         DROP PROCEDURE IF EXISTS proc5;
         CREATE PROCEDURE proc5 ()
             BEGIN
                  SET @v=0;
                  REPEAT
                        select @v;
                        SET @v=@v+1;
                    UNTIL @v>=5
                  END REPEAT;
            END;

        **********************调用********************
        CALL proc5();
    ————————————————————————————————————————————————————————————
    ⑤LOOP...END LOOP (LOOP语句)
    ————————————————————————————————————————————————————————————

        LOOP_LABLE定义一个区块，然后循环执行，LEAVE关键字可以跳出来

        **********************存储过程*****************
          DROP PROCEDURE IF EXISTS proc6;
          CREATE PROCEDURE proc6 ()
            BEGIN
                  SET @v=0;
                  LOOP_LABLE:LOOP
                select @v;
                        SET @v=@v+1;
                        IF @v >=5 THEN
                              LEAVE LOOP_LABLE;
                        END IF;
                  END LOOP;
            END;

        **********************调用********************
        CALL proc6();
        ————————————————————————————————————————————————————————————

10.游标：

    ①优点：

        因为游标是针对行操作的，所以对从数据库中select查询得到的每一行可以进行分开的独立的相同或不同的操作，
        是一种分离的思想。可以满足对某个结果行进行特殊的操作。

                ②缺点：

        游标的缺点是针对有点而言的，也就是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。

    ③使用场景：

        主要用在循环处理、存储过程、函数中使用，用来处理结果集。

    ④使用步骤：

        定义游标->打开游标->使用游标->关闭游标->释放游标。

    ⑤例子：100个数，输出每个数是否能被2,5,7整除
     ————————————————————————————————————————————————————————————
        create table t1(
            id int,
            info1 varchar(100),
            info2 varchar(100),
            info3 varchar(100)
        );

         DROP PROCEDURE IF EXISTS proc7;
         CREATE PROCEDURE proc7()
             BEGIN
                  SET @var=1;
                  WHILE @var<101 DO
                        insert into t1(id)  values(@var);
                        SET @var=@var+1;
                 END WHILE ;
            select * from t1;
            END;

        call proc7();

        //创建了一个表，并初始化了一些数据
     ————————————————————————————————————————————————————————————


        drop procedure if exists cursor_test;
        create procedure cursor_test()
        begin
                declare id int; 
                declare info1 varchar(100); 
                declare info2 varchar(100); 
                declare info3 varchar(100); 
            DECLARE done INT DEFAULT 0;

                declare mycursor cursor for select *from t1;
            declare continue handler for not FOUND set done = 1;

                open mycursor;
                    LOOP_LABLE:LOOP
                        fetch  next from mycursor into id,info1,info2,info3; 
                IF done = 1 THEN
                              LEAVE LOOP_LABLE;
                        END IF;    
                
                IF MOD(id,2) = 0 THEN
                    set info1=CONCAT(id,':能被2整除；');
                else
                    set info1=CONCAT(id,':不能被2整除；');
                    END IF ;    
                
                IF MOD(id,3) = 0 THEN
                    set info2=CONCAT(id,':能被3整除；');
                else
                    set info2=CONCAT(id,':不能被3整除；');
                    END IF ;    

                IF MOD(id,5) = 0 THEN
                    set info3=CONCAT(id,':能被5整除；');
                else
                    set info3=CONCAT(id,':不能被5整除；');
                    END IF ;    

                set @varid =id;
                set @varinfo1=info1;
                set @varinfo2 =info2;
                set @varinfo3=info3;
                update t1 set info1 = @varinfo1  where id = @varid;    
                  END LOOP;
        
                close mycursor;
        end;

        call cursor_test();
        ————————————————————————————————————————————————————————————
       

 

转载于:https://www.cnblogs.com/lihaozong2013/p/10631178.html