mysql 过程函数_mysql 存储过程和函数

最新推荐文章于 2024-06-29 13:56:33 发布

缪加

最新推荐文章于 2024-06-29 13:56:33 发布

阅读量171

点赞数

文章标签： mysql 过程函数

本文链接：https://blog.csdn.net/weixin_34832150/article/details/113131882

版权

函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可

IN,OUT,INOUT三个参数前的关键词只适用于存储过程，对函数而言所有的参数默认都是输入参数

IN输入参数用于把数值传入到存储过程中；OUT输出参数将数值传递到调用者，初始值是NULL；INOUT输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者

mysql> select * fromstudents;+-----+--------+--------+---------+

+-----+--------+--------+---------+

| 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.00sec)

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.42sec)

mysql>delimiter ;

mysql> call simpleproc(1,@a);

Query OK,1 row affected (0.00sec)

mysql> select@a;+------+

| @a |

+------+

| 7 |

+------+

1 row in set (0.00sec)

mysql> select count(*) from students where sid>1;+----------+

| count(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00sec)

mysql> call simpleproc(3,@a);

Query OK,1 row affected (0.00sec)

mysql> select@a;+------+

| @a |

+------+

| 5 |

+------+

1 row in set (0.00sec)

mysql> delimiter //mysql>create procedure simpleproc2()->BEGIN-> SELECT COUNT(*) FROM students where sid > 1;-> END //Query OK, 0 rows affected (0.07sec)

mysql>delimiter ;

mysql>call simpleproc2();+----------+

| COUNT(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)

mysql>show create procedure simpleproc2;+-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

+-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| 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.01sec)

mysql> set global log_bin_trust_function_creators=on;

Query OK,0 rows affected (0.00sec)

mysql> delimiter //mysql> CREATE FUNCTION hello (s CHAR(20))-> RETURNS CHAR(50)-> RETURN CONCAT('Hello,',s,'!');-> //Query OK, 0 rows affected (0.06sec)

mysql>delimiter ;

mysql> select hello('a');+------------+

| hello('a') |

+------------+

| Hello, a! |

+------------+

1 row in set (0.00sec)

mysql> select * fromteacher;+----+-----------+---------+

| 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.03sec)

mysql> select hello(name) fromteacher;+-------------------+

| hello(name) |

+-------------------+

| Hello, Zhang san! |

| Hello, Li si! |

| Hello, Wang wu! |

| Hello, Liu liu! |

| Hello, Ding qi! |

+-------------------+

5 rows in set (0.00sec)

mysql>start transaction;

Query OK,0 rows affected (0.00sec)

mysql> update teacher set name=hello(name);

Query OK,5 rows affected (0.00sec)

Rows matched:5 Changed: 5 Warnings: 0mysql> select * fromteacher;+----+-------------------+---------+

| 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.00sec)

mysql>rollback;

Query OK,0 rows affected (0.01sec)

mysql> delimiter //mysql>create procedure simpleproc2()-> SELECT COUNT(*) FROM students where sid > 1;-> //Query OK, 0 rows affected (0.05sec)

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.09sec)+----------+

| COUNT(*) |

+----------+

| 6 |

+----------+

1 row in set (0.09sec)

mysql>delimiter ;

mysql>call simpleproc2();+----------+

| COUNT(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)

mysql>show create procedure simpleproc2;+-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

+-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| 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.00sec)

mysql>drop procedure simpleproc2;

Query OK,0 rows affected (0.13sec)

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.06sec)

mysql>delimiter ;

mysql>call simpleproc2();+----------+

| COUNT(*) |

+----------+

| 7 |

+----------+

1 row in set (0.00sec)+----------+

| COUNT(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)

mysql>show create procedure simpleproc2;+-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

+-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| 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.00sec)

mysql>use information_schema ;

Reading table informationforcompletion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql> select * from routines where routine_schema='course'

->;+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

SELECT COUNT(*) INTO param2 FROM students where sid >param1;

SELECT COUNT(*) FROM students where sid > 1;

SELECT COUNT(*) FROM students where sid > 2;

3 rows in set (0.00sec)

mysql> select routine_name from routines where routine_schema='course'

->;+--------------+

| ROUTINE_NAME |

+--------------+

| hello |

| simpleproc |

| simpleproc2 |

+--------------+

3 rows in set (0.00sec)

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.00sec)

mysql>use course;

Reading table informationforcompletion of table and column names

You can turn offthis 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 < 10THEN ITERATE label1; END IF;->LEAVE label1;->END LOOP label1;-> set p2=p1;->END;-> //Query OK, 0 rows affected (0.01sec)

mysql>delimiter ;

mysql> call doiterate(1,@x);

Query OK,0 rows affected (0.00sec)

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.03sec)

mysql>delimiter ;

mysql>call simpleproc(@b);

Query OK,1 row affected (0.00sec)

mysql> select@b;+------+

| @b |

+------+

| 0 |

+------+

1 row in set (0.00sec)

mysql> selectn;

ERROR1054 (42S22): Unknown column 'n' in 'field list'mysql>desc students;+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| sid | int(11) | NO | PRI | NULL | auto_increment |

| dept_id | int(11) | YES | MUL | NULL | |

+---------+-------------+------+-----+---------+----------------+

4 rows in set (0.31sec)

mysql> select * fromstudents;+-----+--------+--------+---------+

+-----+--------+--------+---------+

| 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.00sec)

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.04sec)

mysql>delimiter ;

mysql> call sp1(1);+--------+---------+

| xname | xgender |

+--------+---------+

| Andrew | 0 |

+--------+---------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)

mysql> call sp1(2);+-------+---------+

| xname | xgender |

+-------+---------+

| Andy | 0 |

+-------+---------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)

mysql> select case gender when 0 then 'male' when 1 then 'female' else 'unknown' end fromstudents;+------------------------------------------------------------------------+

| case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |

+------------------------------------------------------------------------+

| male |

| female |

| male |

| female |

+------------------------------------------------------------------------+

8 rows in set (0.00sec)

mysql> select gender,case gender when 0 then 'male' when 1 then 'female' else 'unknown' end fromstudents;+--------+------------------------------------------------------------------------+

| gender | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |

+--------+------------------------------------------------------------------------+

| 0 | male |

| 1 | female |

| 0 | male |

| 1 | female |

+--------+------------------------------------------------------------------------+

8 rows in set (0.00sec)

mysql> select gender,case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end fromstudents;+--------+-------------------------------------------------------------------------------+

| gender | case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end |

+--------+-------------------------------------------------------------------------------+

| 0 | unknown |

| 1 | male |

| 0 | unknown |

| 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.33sec)

mysql>delimiter ;

mysql> select * from students where sid in (1,2);+-----+--------+--------+---------+

+-----+--------+--------+---------+

| 1 | Andrew | 0 | 1 |

| 2 | Andy | 0 | 1 |

+-----+--------+--------+---------+

2 rows in set (0.00sec)

mysql> call exp_case(1);

Query OK,1 row affected (0.03sec)

mysql> call exp_case(2);

Query OK,1 row affected (0.04sec)

mysql> select * fromstudents;+-----+--------+--------+---------+

+-----+--------+--------+---------+

| 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.00sec)

mysql> call exp_case(1);

Query OK,1 row affected (0.06sec)

mysql> call exp_case(2);

Query OK,1 row affected (0.02sec)

mysql> select * fromstudents;+-----+--------+--------+---------+

+-----+--------+--------+---------+

| 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.00sec)

另外的写法

delimiter//CREATE PROCEDURE exp_case2(v_sid int)

BEGIN

DECLARE v INT DEFAULT1;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 studentsset 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.07sec)

mysql>DELIMITER ;

mysql> select SimpleCompare(1,2);+--------------------+

| SimpleCompare(1,2) |

+--------------------+

| 1 < 2 |

+--------------------+

1 row in set (0.00sec)

mysql> select SimpleCompare(3,2);+--------------------+

| SimpleCompare(3,2) |

+--------------------+

| 3 > 2 |

+--------------------+

1 row in set (0.00sec)

mysql> select SimpleCompare(3,3);+--------------------+

| SimpleCompare(3,3) |

+--------------------+

| 3 = 3 |

+--------------------+

1 row in set (0.00sec)

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.08sec)

mysql>DELIMITER ;

mysql> select VerboseCompare(1,2);+---------------------+

| VerboseCompare(1,2) |

+---------------------+

| 1 is less than 2. |

+---------------------+

1 row in set (0.00sec)

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.04sec)

mysql>delimiter ;

mysql> call dorepeat(10);

Query OK,0 rows affected (0.00sec)

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 > 0DO-> update students set gender=-1 where sid=v1;-> SET v1 = v1 - 1;->END WHILE;->END;-> //Query OK, 0 rows affected (0.07sec)

mysql>DELIMITER ;

mysql> select * fromstudents;+-----+--------+--------+---------+

+-----+--------+--------+---------+

| 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.01sec)

mysql>call dowhile();

Query OK,1 row affected (0.02sec)

mysql> select * fromstudents;+-----+--------+--------+---------+

+-----+--------+--------+---------+

| 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.00sec)

在函数中必须要有至少一个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.06sec)

mysql>delimiter ;

mysql> selectdoreturn();+------------+

| doreturn() |

+------------+

| 999 |

+------------+

1 row in set (0.00 sec)