• mysql 存储过程和函数


    函数与存储过程最大的区别就是函数调用有返回值,调用存储过程用call语句,而调用函数就直接引用函数名+参数即可
    IN,OUT,INOUT三个参数前的关键词只适用于存储过程,对函数而言所有的参数默认都是输入参数
    IN输入参数用于把数值传入到存储过程中;OUT输出参数将数值传递到调用者,初始值是NULL;INOUT输入输出参数把数据传入到存储过程,在存储过程中修改之后再传递到调用者

    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    
    mysql> delimiter //
    mysql> create procedure simpleproc(IN param1 int,OUT param2 INT)
        -> BEGIN
        -> SELECT COUNT(*) INTO param2 FROM students where sid > param1;
        -> END //
    Query OK, 0 rows affected (0.42 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc(1,@a);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @a;
    +------+
    | @a   |
    +------+
    |    7 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> select count(*) from students where sid>1;
    +----------+
    | count(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> call simpleproc(3,@a);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @a;
    +------+
    | @a   |
    +------+
    |    5 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> BEGIN
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> END //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc2();
    +----------+
    | COUNT(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    
    mysql> show create procedure simpleproc2;
    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                                   | character_set_client | collation_connection | Database Collation |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()
    BEGIN
    SELECT COUNT(*) FROM students where sid > 1;
    END | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    1 row in set (0.00 sec)

    Delimiter命令是改变语句的结束符,MySQL默认的结束符为;号,由于procedure和function中的;号并不代表创建的结束,所以要替换成另外的结束符以便表示创建的结束

    mysql> delimiter //
    mysql> CREATE FUNCTION hello (s CHAR(20))
        -> RETURNS CHAR(50)
        -> RETURN CONCAT('Hello',s,'!');
        -> //
    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
    mysql> delimiter ;
    mysql> show variables like '%trust%';
    +---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | log_bin_trust_function_creators | OFF   |
    +---------------------------------+-------+
    1 row in set (0.01 sec)
    
    mysql> set global log_bin_trust_function_creators=on;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter //
    mysql> CREATE FUNCTION hello (s CHAR(20))
        -> RETURNS CHAR(50)
        -> RETURN CONCAT('Hello, ',s,'!');
        -> //
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> delimiter ;
    mysql> select hello('a');
    +------------+
    | hello('a') |
    +------------+
    | Hello, a!  |
    +------------+
    1 row in set (0.00 sec)
    
    mysql> select * from teacher;
    +----+-----------+---------+
    | id | name      | dept_id |
    +----+-----------+---------+
    |  1 | Zhang san |       1 |
    |  2 | Li si     |       1 |
    |  3 | Wang wu   |       2 |
    |  4 | Liu liu   |       3 |
    |  5 | Ding qi   |       3 |
    +----+-----------+---------+
    5 rows in set (0.03 sec)
    
    mysql> select hello(name) from teacher;
    +-------------------+
    | hello(name)       |
    +-------------------+
    | Hello, Zhang san! |
    | Hello, Li si!     |
    | Hello, Wang wu!   |
    | Hello, Liu liu!   |
    | Hello, Ding qi!   |
    +-------------------+
    5 rows in set (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update teacher set name=hello(name);
    Query OK, 5 rows affected (0.00 sec)
    Rows matched: 5  Changed: 5  Warnings: 0
    
    mysql> select * from teacher;
    +----+-------------------+---------+
    | id | name              | dept_id |
    +----+-------------------+---------+
    |  1 | Hello, Zhang san! |       1 |
    |  2 | Hello, Li si!     |       1 |
    |  3 | Hello, Wang wu!   |       2 |
    |  4 | Hello, Liu liu!   |       3 |
    |  5 | Hello, Ding qi!   |       3 |
    +----+-------------------+---------+
    5 rows in set (0.00 sec)
    
    mysql> rollback;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> //
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> delimiter ;

    通过begin…end将这多个SQL语句包含在一起,Begin…end语句通常出现在存储过程、函数和触发器中,其中可以包含一个或多个语句,每个语句用;号隔开

    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> SELECT COUNT(*) FROM students where sid > 2;
        -> //
    Query OK, 0 rows affected (0.09 sec)
    
    +----------+
    | COUNT(*) |
    +----------+
    |        6 |
    +----------+
    1 row in set (0.09 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc2();
    +----------+
    | COUNT(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show create procedure simpleproc2;
    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                         | character_set_client | collation_connection | Database Collation |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()
    SELECT COUNT(*) FROM students where sid > 1; | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql> drop procedure simpleproc2;
    Query OK, 0 rows affected (0.13 sec)
    
    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> BEGIN
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> SELECT COUNT(*) FROM students where sid > 2;
        -> END
        -> //
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc2();
    +----------+
    | COUNT(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    +----------+
    | COUNT(*) |
    +----------+
    |        6 |
    +----------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show create procedure simpleproc2;
    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                                                                                | character_set_client | collation_connection | Database Collation |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()
    BEGIN
    SELECT COUNT(*) FROM students where sid > 1;
    SELECT COUNT(*) FROM students where sid > 2;
    END | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql> use information_schema ;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from routines where routine_schema='course'
        -> ;
    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
    | SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME     | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                                                                                  | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                                              | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
    | hello         | def             | course         | hello        | FUNCTION     | char      |                       50 |                    200 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_0900_ai_ci | char(50)       | SQL          | RETURN CONCAT('Hello, ',s,'!')                                                                      |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2019-04-01 10:24:44 | 2019-04-01 10:24:44 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    | simpleproc    | def             | course         | simpleproc   | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL               | NULL           | SQL          | BEGIN
    SELECT COUNT(*) INTO param2 FROM students where sid > param1;
    END                             |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2019-04-01 10:05:28 | 2019-04-01 10:05:28 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    | simpleproc2   | def             | course         | simpleproc2  | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL               | NULL           | SQL          | BEGIN
    SELECT COUNT(*) FROM students where sid > 1;
    SELECT COUNT(*) FROM students where sid > 2;
    END |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2019-04-01 10:38:17 | 2019-04-01 10:38:17 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
    3 rows in set (0.00 sec)
    
    mysql> select routine_name from routines where routine_schema='course'
        -> ;
    +--------------+
    | ROUTINE_NAME |
    +--------------+
    | hello        |
    | simpleproc   |
    | simpleproc2  |
    +--------------+
    3 rows in set (0.00 sec)
    
    mysql> select routine_name,routine_type from routines where routine_schema='course';
    +--------------+--------------+
    | ROUTINE_NAME | ROUTINE_TYPE |
    +--------------+--------------+
    | hello        | FUNCTION     |
    | simpleproc   | PROCEDURE    |
    | simpleproc2  | PROCEDURE    |
    +--------------+--------------+
    3 rows in set (0.00 sec)
    
    mysql> use course;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    标签label可以加在begin…end语句以及loop, repeat和while语句中通过iterate和leave来控制流程,iterate表示返回指定标签位置,leave表示跳出标签

    Database changed
    mysql> delimiter //
    mysql> CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int)
        -> BEGIN
        -> label1: LOOP
        -> SET p1 = p1 + 1;
        -> IF p1 < 10 THEN ITERATE label1; END IF;
        -> LEAVE label1;
        -> END LOOP label1;
        -> set p2=p1;
        -> END;
        -> //
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> delimiter ;
    
    mysql> delimiter ;
    mysql> call doiterate(1,@x);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |   10 |
    +------+
    1 row in set (0.00 sec)

    Drop procedure/function语句用来删除指定名称的存储过程或函数

    mysql> drop procedure simpleproc;
    Query OK, 0 rows affected (0.16 sec)

    Declare语句通常用来声明本地变量、游标、条件或者handler
    Declare语句只允许出现在begin … end语句中而且必须出现在第一行
    Declare的顺序也有要求,通常是先声明本地变量,再是游标,然后是条件和handler

    mysql> delimiter //
    mysql> create procedure simpleproc(OUT param2 INT)
        -> BEGIN
        -> declare n int default 10;   #必须在第一行   
        -> SELECT COUNT(*) INTO param2 FROM students where sid > n;
        -> END //
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc(@b);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @b;
    +------+
    | @b   |
    +------+
    |    0 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> select n;
    ERROR 1054 (42S22): Unknown column 'n' in 'field list'
    
    
    mysql> desc students;
    +---------+-------------+------+-----+---------+----------------+
    | Field   | Type        | Null | Key | Default | Extra          |
    +---------+-------------+------+-----+---------+----------------+
    | sid     | int(11)     | NO   | PRI | NULL    | auto_increment |
    | sname   | varchar(64) | YES  |     | NULL    |                |
    | gender  | varchar(12) | YES  |     | NULL    |                |
    | dept_id | int(11)     | YES  | MUL | NULL    |                |
    +---------+-------------+------+-----+---------+----------------+
    4 rows in set (0.31 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    mysql> delimiter //
    mysql> CREATE PROCEDURE sp1 (v_sid int)
        -> BEGIN
        -> DECLARE xname VARCHAR(64) DEFAULT 'bob';
        -> DECLARE xgender INT;
        -> SELECT sname, gender INTO xname, xgender
        -> FROM students WHERE sid= v_sid;
        -> SELECT xname,xgender;
        -> END;
        -> //
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> delimiter ;
    
    mysql> call sp1(1);
    +--------+---------+
    | xname  | xgender |
    +--------+---------+
    | Andrew |       0 |
    +--------+---------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call sp1(2);
    +-------+---------+
    | xname | xgender |
    +-------+---------+
    | Andy  |       0 |
    +-------+---------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select case gender when 0 then 'male' when 1 then 'female' else 'unknown' end from students;
    +------------------------------------------------------------------------+
    | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |
    +------------------------------------------------------------------------+
    | male                                                                   |
    | male                                                                   |
    | male                                                                   |
    | female                                                                 |
    | male                                                                   |
    | male                                                                   |
    | female                                                                 |
    | female                                                                 |
    +------------------------------------------------------------------------+
    8 rows in set (0.00 sec)
    mysql> select gender,case gender when 0 then 'male' when 1 then 'female' else 'unknown' end from students;
    +--------+------------------------------------------------------------------------+
    | gender | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |
    +--------+------------------------------------------------------------------------+
    | 0      | male                                                                   |
    | 0      | male                                                                   |
    | 0      | male                                                                   |
    | 1      | female                                                                 |
    | 0      | male                                                                   |
    | 0      | male                                                                   |
    | 1      | female                                                                 |
    | 1      | female                                                                 |
    +--------+------------------------------------------------------------------------+
    8 rows in set (0.00 sec)
    
    mysql> select gender,case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end from students;
    +--------+-------------------------------------------------------------------------------+
    | gender | case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end |
    +--------+-------------------------------------------------------------------------------+
    | 0      | unknown                                                                       |
    | 0      | unknown                                                                       |
    | 0      | unknown                                                                       |
    | 1      | male                                                                          |
    | 0      | unknown                                                                       |
    | 0      | unknown                                                                       |
    | 1      | male                                                                          |
    | 1      | male                                                                          |
    +--------+-------------------------------------------------------------------------------+
    8 rows in set (0.00 sec)

    本地变量可以通过declare语句进行声明
    声明后的变量可以通过select … into var_list进行赋值,或者通过
    set语句赋值,或者通过定义游标并使用fetch … into var_list赋值

    声明的变量作用范围为被声明的begin … end语句块之间
    声明的变量和被引用的数据表中的字段名要区分开来

    第一个语句中case_value与后面各句的when_value依次做相等的对比,如果碰到相等的,则执行对应的后面的statement_list,否则接着对比,
    如果都没有匹配,则执行else后面的statement_list
    第二个语句中当search_condition满足true/1的结果时,则执行对应的statement_list,否则执行else对应的statement_list

    mysql> delimiter //
    mysql> CREATE PROCEDURE exp_case(v_sid int)
        -> BEGIN
        -> DECLARE v INT DEFAULT 1;
        -> select gender into v from students where sid=v_sid;
        -> CASE v
        -> WHEN 0 THEN update students set gender=1 where sid=v_sid;
        -> WHEN 1 THEN update students set gender=0 where sid=v_sid;
        -> ELSE
        -> update students set gender=-1 where sid=v_sid;
        -> END CASE;
        -> END;
        -> //
    Query OK, 0 rows affected (0.33 sec)
    
    mysql> delimiter ;
    mysql> select * from students where sid in (1,2);
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    +-----+--------+--------+---------+
    2 rows in set (0.00 sec)
    
    mysql> call exp_case(1);
    Query OK, 1 row affected (0.03 sec)
    
    mysql> call exp_case(2);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 1      |       1 |
    |   2 | Andy   | 1      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    mysql> call exp_case(1);
    Query OK, 1 row affected (0.06 sec)
    
    mysql> call exp_case(2);
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    另外的写法
    
    delimiter //
    CREATE PROCEDURE exp_case2(v_sid int)
    BEGIN
    DECLARE v INT DEFAULT 1;
    select gender into v from students where sid=v_sid;
    CASE
    WHEN v=0 THEN update students set gender=1 where sid=v_sid;
    WHEN v=1 THEN update students set gender=0 where sid=v_sid;
    ELSE
    update students set gender=-1 where sid=v_sid;
    END CASE;
    END;
    //
    delimiter ;

    MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句,另外return语句也是函数中的特定流程控制语句

    IF语句在存储过程或函数中表明了基础的条件选择语句IF语句中如果search_condition满足true/1的条件,则执行对应的statement_list,否则再判断elseif中的search_condition是否满足
    true/1的条件,如果都不满足则执行else中的statement_list语句

    mysql> DELIMITER //
    mysql> CREATE FUNCTION SimpleCompare(n INT, m INT)
        -> RETURNS VARCHAR(20)
        -> BEGIN
        -> DECLARE s VARCHAR(20);
        -> IF n > m THEN SET s = '>';
        -> ELSEIF n = m THEN SET s = '=';
        -> ELSE SET s = '<';
        -> END IF;
        -> SET s = CONCAT(n, ' ', s, ' ', m);
        -> RETURN s;
        -> END //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> DELIMITER ;
    mysql> select SimpleCompare(1,2);
    +--------------------+
    | SimpleCompare(1,2) |
    +--------------------+
    | 1 < 2              |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> select SimpleCompare(3,2);
    +--------------------+
    | SimpleCompare(3,2) |
    +--------------------+
    | 3 > 2              |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> select SimpleCompare(3,3);
    +--------------------+
    | SimpleCompare(3,3) |
    +--------------------+
    | 3 = 3              |
    +--------------------+
    1 row in set (0.00 sec)
    
    
    mysql> DELIMITER //
    mysql> CREATE FUNCTION VerboseCompare (n INT, m INT)  
        -> RETURNS VARCHAR(50)
        -> BEGIN
        ->   DECLARE s VARCHAR(50);
        ->   IF n = m THEN SET s = 'equals';
        ->   ELSE
        ->       IF n > m THEN SET s = 'greater';
        ->       ELSE SET s = 'less';
        ->       END IF;
        ->       SET s = CONCAT('is ', s, ' than');
        ->   END IF;
        ->   SET s = CONCAT(n, ' ', s, ' ', m, '.');
        ->   RETURN s;
        -> END //
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> DELIMITER ;
    mysql> select VerboseCompare(1,2);
    +---------------------+
    | VerboseCompare(1,2) |
    +---------------------+
    | 1 is less than 2.   |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select VerboseCompare(2,2);
    +---------------------+
    | VerboseCompare(2,2) |
    +---------------------+
    | 2 equals 2.         |
    +---------------------+
    1 row in set (0.00 sec)

    repeat语句是存储过程或函数中表达循环执行的一种方式
    Repeat语句中statement_list一直重复执行直到search_condition条件满足
    Statement_list可以包含一个或多个SQL语句

    mysql> delimiter //
    mysql> CREATE PROCEDURE dorepeat(p1 INT)
        -> BEGIN
        -> SET @x = 0;
        -> REPEAT
        -> SET @x = @x + 1;
        -> UNTIL @x > p1 END REPEAT;
        -> END
        -> //
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> delimiter ;
    mysql> call dorepeat(10);
    Query OK, 0 rows affected (0.00 sec)
    
    
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |   11 |
    +------+
    1 row in set (0.01 sec)

    while语句是存储过程或函数中表达循环执行的一种方式
    当search_condition返回为true时,则循环执行statement_list中的语句,直到search_condition的结果返回为false

    mysql> DELIMITER //
    mysql> CREATE PROCEDURE dowhile()
        -> BEGIN
        -> DECLARE v1 INT DEFAULT 5;
        -> WHILE v1 > 0 DO
        -> update students set gender=-1 where sid=v1;
        -> SET v1 = v1 - 1;
        -> END WHILE;
        -> END;
        -> //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> DELIMITER ;
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.01 sec)
    
    mysql> call dowhile();
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | -1     |       1 |
    |   2 | Andy   | -1     |       1 |
    |   3 | Bob    | -1     |       1 |
    |   4 | Ruth   | -1     |       2 |
    |   5 | Mike   | -1     |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    在函数中必须要有至少一个return语句,当有多个return语句时则表明函数有多种退出的方式
    mysql> delimiter //
    mysql> create function doreturn()
        -> returns int
        -> begin
        ->  select gender into @a from students where sid=1;
        ->  if @a=1 then return 1;
        ->  elseif @a=0 then return 0;
        -> else return 999;
        -> end if;
        -> end;
        -> //
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> delimiter ;
    mysql> select doreturn();
    +------------+
    | doreturn() |
    +------------+
    |        999 |
    +------------+
    1 row in set (0.00 sec)
  • 相关阅读:
    测试一面(宇宙条)
    java实现快速排序
    java实现冒泡排序
    Perl 获取当前系统时间
    日常问题解决:记一次因watchdog未启动导致的resin启动失败解决
    日常问题解决:记一次因信号量不足引起的APACHE启动错误解决以及kernel.sem值优化
    oracle11g使用expdp、impdp导出导入用户表结构
    日常问题解决:rhel6解决curl版本过旧问题
    日常问题解决:解决fork: retry: 资源暂时不可用
    日常问题解决:rhel7修改TCP最大连接数
  • 原文地址:https://www.cnblogs.com/Honeycomb/p/10672659.html
Copyright © 2020-2023  润新知