MySQL存储程序

今天药忘吃喽~ 2022-05-20 09:52 131阅读 0赞

### **1.什么是MySQL存储程序？** ###

存放在MySQL服务器端，供重复使用的对象叫做存储程序。存储程序分为以下四种：

（1）存储过程：不直接返回一个计算结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户端。

一条SQL语句如果比作一行java代码，存储过程就相当于一个java方法，可以包含许多SQL语句，进行更复杂的操作。

（2）存储函数：返回一个计算结果，该结果可以用在表达式里。

就相当于自定义MySQL函数一样，它的作用和MySQL函数类似，只不过需要我们自己去定义。

（3）触发器：与数据表相关联，当那个数据表被INSERT、DELETE或UPDATE语句修改时，触发器将自动执行。

如果表关联了触发器，当表数据有修改操作时，触发器将自动执行，至于做什么是自己定义的。

（4）事件：根据时间表在预定时刻自动执行。

比如，可以自己定一个开始时间点，然后让它每隔指定的时间段重复做某些事情。

**【\#\#\#】**存储例程（stored routine）又是什么？

指的是存储过程和存储函数两者。

### **2 .存储程序有那些优点** ###

（1）存储程序对象的可执行部分可以用复合语句（可以包括 局部变量、代码块、循环、条件语句）来编写，对SQL语法进行了扩展。

（2）存储程序都保存在服务端，定义他们的代码只需要在创建它们的时候通过网络传递一次，而不是每次执行都要传递一次，大大减少了开销。

（3）把复杂的计算封装为程序单元，通过简单的程序单元的名字调用他们。

（4）可以把一组存储程序打包成一个“函数库”，供其他应用程序调用，让其他程序以相同的方式完成操作。

（5）提供了一种错误处理机制。

（6）可以提高数据库安全性，可以限制存储程序的访问权限。

### **3.最简单的存储程序** ###

存储程序不一定非得用复合语句来写，一个存储程序可以只包含一条SQL语句。例如，下面这个存储过程：

CREATE PROCEDURE my_proc1() SELECT * FROM user;

**【注意】**要以分号（英文的分号）结束SQL语句。

调用上面的存储过程：

CALL my_proc1;
    或
    CALL my_proc1();

显示一个结果集（存储过程没有返回值）。

### **4.如何编写复合语句** ###

上面说过，存储程序对象的可执行部分可以用复合语句来编写。但是如何编写复合语句呢，它的语法是怎么样的？

**（1）复合语句的语法**

**【\#\#\#】**由 BEGIN 开头，END 结束。在其中可以写任意数量的SQL语句，从 BEGIN 到 END 构成了一个语句块。

因为BEGIN和END之间可以有多条SQL语句，那么问题就来了： 学过MySQL的都知道，通常一条MySQL语句是以分号结束的，然后你这样写：

CREATE PROCEDURE my_proc2()
    BEGIN
        SELECT * FROM USER;
        SELECT * FROM TEST;
        ....
    END;

然后就尴尬了，还没到END呢，第一个分号就给你结束了。结果就是一个存储过程被分号给五马分尸，第一句就变成了：

CREATE PROCEDURE my_proc1() BEGIN SELECT * FROM USER;

这条语句当然变成什么都不是了，一执行就会报错，剩下的尸块就不管了...

**【\#\#\#】**那么，如何解决这个问题？

在定义存储程序之前（CREATE xxx 之前），可以用 delimiter 命令临时改变MySQL的结束符，例如：

delimiter $

这行语句就用 $ 代替了分号作为语句结束符，然后在存储程序结束后（END之后），重新定义成分号：

delimiter ;

**【注意】**

（1）定义存储程序前不一定要临时改变MySQL结束符的（例如，有的存储程序内部的语句没用到分号）。

（2）临时的MySQL结束符用什么来代替自己看着办（$只是举个例子。注意避开MySQL内部使用的符号，例如反斜杠\[ \\ \]是MySQL的转义字符），也可以用多个字符，例如：EOF。

所以，上面的存储过程定义就变成如下：

delimiter $
    CREATE PROCEDURE my_proc2()
    BEGIN
        SELECT * FROM USER;
        SELECT * FROM TEST;
        ....
    END$
    delimiter ;

这样， MySQL程序就会把整个存储过程当做一条语句传给服务器了。

### **5.存储函数** ###

定义的函数：

DELIMITER $$
    
    USE `test`$$
    
    DROP FUNCTION IF EXISTS `fun1`$$
    
    CREATE DEFINER=`root`@`localhost` FUNCTION `fun1`() 
    RETURNS INT(5)
    
    BEGIN
    	RETURN (SELECT id FROM USER WHERE NAME = '张三');
    END$$
    
    DELIMITER ;

（1）RETURNS INT(5)：

用来表明其返回值类型，int类型可以不指定长度；字符串类型用 VARCHAR(10)，必须指定长度。

（2）函数体至少需要包含一条 RETURN 语句。

（3）存储函数可以有参数，例如：

CREATE DEFINER=`root`@`localhost` FUNCTION `fun1`(num INT)

（4）如果不小心定义的函数名和数据库内置的函数重名了，则调用的时候必须带上数据库名：

test.fun()

**【\#\#\#】**调用存储函数：

select fun1();
        -- 可以直接用select调用存储函数
        -- 注意：函数名后必须加括号
    
    select * from user where id > fun1()
        -- 也可以直接在表达式中调用函数

### **6.存储过程** ###

定义存储过程：

DELIMITER $$
    
    USE `test`$$
    
    DROP PROCEDURE IF EXISTS `my_proc`$$
    
    CREATE DEFINER=`root`@`localhost` PROCEDURE `my_proc`()
    
    BEGIN
    	SELECT * FROM student;
    	SELECT * FROM teacher;
    END$$
    
    DELIMITER ;

（1）存储过程只能用 CALL 语句来调用，不能用在表达式里。

（2）一个过程可以生成多个结果集，它们将依次被发送到客户。但存储过程更多的是被用来做修改操作，而不是查询。

（3）存储过程的参数类型

存储过程的参数分为3种类型：IN、OUT、INOUT；如果参数没有指定类型，则默认为 IN 类型。

IN 类型：调用存储过程时可以通过IN类型参数将值传递给存储过程，存储过程可以对这个值进行修改，但任何修改对外部调用者是不可见的。

OUT 类型：与IN类型参数作用相反，在存储过程中可以把值赋给OUT类型参数，返回给外部调用者。

INOUT 类型：见名知意，同时包含了IN类型和OUT类型参数的作用。既可以作为入参，也可以作为出参。

示例：

DELIMITER $$
    
    CREATE PROCEDURE `test`.`param_test`(IN var1 INT,OUT var2 INT,INOUT var3 INT)
        
        BEGIN
    	SET var2 = var1 + 666;
    	SET var3 = var3 + 666;
        END$$
    
    DELIMITER ;

调用带参存储过程：

SET @var1 = 11;
    SET @var2 = 22;
    SET @var3 = 33;
    CALL param_test(@var1,@var2,@var3);
    SELECT @var1,@var2,@var3;

结果：

![70][]

（4）IN、OUT、INOUT关键字不适合用于存储函数、事件或触发器；因为对于存储函数，所有参数都相当于IN参数；而事件和触发器则根本没有任何参数。

（5）错误处理机制

在存储过程中通常还会用到错误处理机制（但不仅仅是存储才能使用错误处理机制），下面通过一个例子简单了解下什么是错误处理机制。

DELIMITER $$
    
    USE test$$
    
    DROP PROCEDURE IF EXISTS error_test$$
    
    CREATE PROCEDURE error_test()
        BEGIN
            DECLARE t_error INT DEFAULT 0;
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;	
            START TRANSACTION;
    	
            UPDATE 
                employee
            SET 
                salary = salary + 2000
            WHERE 
                age > 25;
    	
            IF t_error = 1 THEN  
                ROLLBACK;
            ELSE  
                COMMIT;
            END IF;  
    
        END$$
    
    DELIMITER ;

DECLARE t\_error INT DEFAULT 0;

\-- 定义了一个INT类型的临时变量，赋予默认值0。

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t\_error = 1;

\-- 这句SQL的意思是：如果执行下面的SQL出现异常时，将变量 t\_error 赋值为1，并继续向下执行。

START TRANSACTION;

\-- 开启事物

IF t\_error = 1 THEN    
        ROLLBACK;  
ELSE    
        COMMIT;  
END IF;

\-- 如果变量 t\_error 值为1（即前面的SQL执行出现了异常），则回滚事物；否则提交事物。

### 7.事件 ###

事件 = 存储程序 + 时间表。

时间表用来定义事件发生的时间、次数以及何时消失。

MySQL 5.1.6及更高版本有一个事件调度器，它使我们可以把数据库操作安排在预定事件执行。

事件调度器将根据时间表来调度指定存储过程，即执行事件。

（1）启用事件调度器

默认情况下，事件调度器不会运行。如果想使用事件，必须先启用事件调度器。

如何启用事件调度器？

方式一：在MySQL服务启动时自动启用事件调度器

在一个MySQL选项文件中添加如下配置

[mysqld]
    event_scheduler=ON

MySQL服务器在启动的时候会读取所有选项文件中的配置，启用事件调度器。

如果在启动服务器时把 event\_scheduler 设置为 DISABLED，则在系统运行时，你将只能查看它的状态，而不能改变它的状态。在此基础上，你依然可以创建事件，但它们将不能执行。

方式二：在系统运行时启用事件调度器

先查看事件调度器的状态

SHOW VARIABLES LIKE 'event_scheduler';
    -- 或
    SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
    -- 或
    SHOW VARIABLES LIKE 'event%';

修改状态

SET GLOBAL event_scheduler = OFF; -- OFF 或 0 都可以
    SET GLOBAL event_scheduler = ON;  -- ON 或 1 都可以

注意：event\_scheduler 是一个全局（GLOBAL）变量，你必须要有 SUPER 权限才能对他进行修改。

（2）错误日志

事件调度器将把它的执行情况写到服务器的“错误”日志里，你可以从这个日志查看到关于事件调度器正在干什么的信息。它会把它运行的每一个事件以及事件执行过程中发生的错误记载到日志里。如果事件没有按照自己的预期去运行，可以在该日志里找原因。

（3）创建事件

类型一：按照指定时间间隔重复执行的事件

-- 一个简单的事件
    CRATE EVENT event1
    	ON SCHEDULE EVERY 4 HOUR
    	DO
    		DELETE FROM log WHERE create_time < CURRENT_TIMESTAMP - INTERVAL 1 DAY;
    
    -- 指定了开始时间的事件
    CRATE EVENT event1
    	ON SCHEDULE EVERY 4 HOUR STARTS '2018-07-16 15:30:00'
    	DO
    		DELETE FROM log WHERE create_time < CURRENT_TIMESTAMP - INTERVAL 1 DAY;
    
    -- 执行体为语句块的事件
    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` EVENT `event1`
    	ON SCHEDULE EVERY 3 MINUTE STARTS '2018-07-16 15:30:00'
    	DO
    	BEGIN
    	    CALL proc1();
    	END$$
    DELIMITER ;

> EVERY n interval，指定时间间隔。interval 可以是 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。

> 在 EVERY 子句后面，可以使用 START datetime 和 END datetime 来指定事件的第一次和最后一次执行时间。

> 默认情况，事件将在它被创建后立刻开始它的第一次执行，并将一直执行下去。

> DO子句负责定义事件的语句体部分，可以是一条简单SQL语句，也可以是一个语句块（BEGIN/END）。

> 事件通常和存储过程配置使用 —— 将要进行的负责操作定义在一个存储过程中，然后让事件定期的去调用存储过程。

类型二：只在指定时间执行一次的事件

-- 过一个小时后执行一次该事件
    CREATE EVENT one_time
    	ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    	DO ...;

（4）禁用和激活事件

ALTER EVENT event_name DISABLE; -- 禁用事件
    ALTER EVENT event_name ENABLE;  -- 激活事件

每个事件都隶属于某个数据库，所以你必须拥有哪个数据库的EVENT权限才能为它创建或删除事件。

### **8.触发器** ###

触发器是与特定表相关联的存储程序，当相应的表数据被 INSERT、UPDATE 或 DELETE 语句修改时，触发器将自动执行。

（1）触发器的定义语法：

CREATE TRIGGER trigger_name    -- trigger_name : 触发器的名称
    {BEFORE | AFTER}               -- 修改数据前(BEFORE)/后(AFTER)执行触发器
    {INSERT | UPDATE | DELETE}     -- 执行哪种（增/删/改）操作时触动触发器
    ON tb_name                     -- tb_name : 与该触发器关联的表
    FOR EACH ROW trigger_stmt;     -- trigger_stmt : 触发器的语句体

**【\#\#\#】**通用的触发器命名规范：

bi\_tbName、ai\_tbName、bu\_tbName、au\_tbName、bd\_tbName、ad\_tbName

其中，bi（before insert）即执行插入操作前的意思，ai（after insert）即执行插入操作后的意思，以此类推。

**【\#\#\#】**触发器的语句体（trigger\_stmt）可以是一条普通的SQL语句，也可以是一个复合语句构造的语句块（BEGIN/END）。

（2）NEW 和 OLD 关键字的用法

在触发器的**语句体里**，可以使用 NEW.colName 来引用将由 INSERT 或 UPDATE 语句插入或修改的新数据行里的数据列。类似地，OLD.cloName 可以用来引用将由 UPDATE 或 DELETE 语句修改或删除的老数据行里的数据列。

-- 假设有表：user(id,name,age)
    
    INSERT INTO USER(`name`,age) VALUES('张三',56);
    -- 执行上述INSERT操作，则： NEW.name 的值为'张三'， NEW.age 的值为56
    
    -- 假设user表里有这样一条数据：（1,李四,44）
    DELETE FROM USER WHERE id = 1;
    -- 执行上述DELETE操作，则： OLD.id 的值为1， OLD.name 的值为'李四', OLD.age 的值为44
    
    -- 假设user表里有这样一条数据：（2,王八,58）
    UPDATE USER SET `name` = '王八蛋',age = 55 WHERE id = 2;
    -- 执行上述DELETE操作，则： NEW.name 的值为'王八蛋'， NEW.age 的值为55； OLD.name 的值为'王八', OLD.age 的值为58

（3）示例

DELIMITER $$
    
    USE test$$
    
    DROP TRIGGER IF EXISTS bi_user$$
    
    CREATE TRIGGER bi_user BEFORE INSERT
        ON `user`
        FOR EACH ROW BEGIN
    	IF NEW.name = '王八' THEN    -- 如果插入的用户名为'王八',则改为'王八蛋'，再插入
    		SET NEW.name = '王八蛋';
    	END IF;
    	IF NEW.age = 22 THEN        -- 如果插入的年龄为22，则改为18，再插入
    		SET NEW.age = 18;
    	END IF;	
        END$$
    
    DELIMITER ;

如果是像 INSERT INTO user VALUES(1,'张三',23),(2,'李四',24); 这样 一次插入多条数据的 INSERT 操作，则相当于执行了两条 INSERT 语句，每一条插入的数据都会经过触发器。

还要注意的一点是，在当前表的触发器中不能对当前表进行修改操作。

（4）触发器的权限  
创建和删除触发器需要的权限取决于具体的MySQL版本。在MySQL 5.1.6 版之前，必须要有 SUPER 权限。自 5.1.6 版起，你必须拥有那个数据表的 TRIGGER 权限才能为它创建和删除触发器（因为触发器本来就是和表关联的，这样控制权限才更合理）。

### 9.存储例程（存储函数/存储过程）的权限 ###

未完待续(page:253) ...

### 10.存储程序的安全性 ###

未完待续(page:258) ...

[70]: /images/20220520/c05ffc3bed39464393d6eb7d7cc83967.png