7、存储过程和函数

2020-02-22 08:44 507人阅读评论(0)

一、存储过程

1、创建存储过程

CREATE PROCEDURE <存储过程名> ([IN|OUT|INOUT <参数名 参数类型>])
[指定存储过程特性]
BEGIN
<存储过程体>
END

IN表示输入参数；OUT表示输出参数；INOUT既表示输入又表示输出
存储过程的特性如下
- LANUAGE SQL：说明存储过程体是由SQL语句组成的
- DETERMINISTIC：指定该特性，相同输入会得到相同输出
- NOT DETERMINISTIC：相同输入可能等待不同输出
- [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]
  - CONTAINS SQL：表明子程序包含SQL语句，但不包含读写数据的语句
  - NO SQL：表明子程序不包含SQL语句
  - READS SQL DATA：表明子程序包含读数据的语句
  - MODIFIES SQL DATA：表明子程序包含写数据的语句
- SQL SECURITY DEFINER|INVOKER：指定谁有权限来执行
  - DEFINER：只有定义者有权限
  - INVOKER：有权限的调用者都可以使用

mysql> DELIMITER $
mysql> CREATE PROCEDURE test()
    -> BEGIN
    -> SHOW DATABASES;
    -> END$
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> CALL test();
+--------------------+
| Database           |
+--------------------+
| information_schema |
| CS_database        |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

使用DELIMITER可以改变结束符号

2、定义条件和处理程序

存储过程中的条件一般是某个错误，当发生错误时执行处理程序
条件和处理程序可以避免程序异常停止
注意：DECLARE只能在存储过程、函数和触发器中使用

（1）定义条件

DECLARE <条件名称> CONDITION FOR 
<SQLSTATE [VALUE] sqlstate_value>|<mysql_error_code>;

条件名称：给指定的条件取名
条件类型
- allstate_value：长度为5的字符串类型错误代码
- mysql_error_code：数值型错误代码
例如定义条件ERROR 1148(42000)

DECLARE command_not_allow CONDITION FOR SQLSTATE VALUE "42000";
或者
DECLARE command_not_allow CONDITION FOR 1148;

（2）定义处理程序

DECLARE <CONTINUE|EXIT|UNDO> HANDLER FOR <错误类型> <程序体>;

处理方式
- CONTINUE：遇到错误不处理，继续执行
- EXIT：遇到错误退出
- UNDO：遇到错误后撤回之前的操作（MySQL不支持）
错误类型：
- SQLSTATE [VALUE] sqlstate_value：字符串类型错误代码
- mysql_error_code：数值型错误代码
- condition_name：由DECLARE CONDITION定义的条件的名字
- SQLWARNING：匹配所有以01开头的SQLSTATE错误代码
- NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
- SQLEXCEPTION：匹配所有没被SQLWARNING或NOT FOUND捕获的错误

mysql> CREATE PROCEDURE test2()                                                                                                     		-> BEGIN
    -> DECLARE command_not_found CONDITION FOR SQLSTATE VALUE "42000";
    -> DECLARE CONTINUE HANDLER FOR command_not_found SELECT "command not found!";                                                  		-> END$                                                                                                                     Query OK, 0 rows affected (0.01 sec)

3、光标的使用

在数据量非常大的情况下，常使用光标逐条读取数据
光标必须在声明处理程序之前声明
变量和条件必须在声明光标或处理程序之前声明

（1）声明光标

DECLARE <光标名> CURSOR FOR <SELECT语句>;

（2）打开光标

OPEN <光标名>;

（3）使用光标

FETCH <光标名> INTO <变量名>;

将光标对应的内容存入到变量里

（4）关闭光标

CLOSE <光标名>;

4、流程控制语句

（1）IF语句

IF <条件1> THEN <执行语句1>;
[ELSEIF <条件2> THEN <执行语句2>;]
[ELSE <执行语句3>;]
END IF;

mysql> CREATE PROCEDURE testForIf(IN var INT)
    -> BEGIN
    -> IF var>0 THEN SELECT "var>0" AS result;                                                                                        
    -> ELSEIF var<0 THEN SELECT "var<0" AS result;                                                                                        
    -> ELSE SELECT "var=0" AS result;                                                                                        
    -> END IF;                                                                                                                      -> END$                                                                                                                     Query OK, 0 rows affected (0.04 sec)

mysql> CALL testForIf(10);
+--------+
| result |
+--------+
| var>0  |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

（2）CASE语句

CASE <表达式>
	WHEN <值1> THEN <执行语句1>;
	[WHEN <值2> THEN <执行语句2>;]
	[ELSE <执行语句3>;]
END CASE;

当WHEN后面的值后表达式相等时，执行该语句
若都不相等，则执行ELSE的语句

CASE 
	WHEN <表达式1> THEN <执行语句1>;
	[WHEN <表达式2> THEN <执行语句2>;]
	[ELSE <执行语句3>;]
END CASE;

从第一个表达式开始判断，直到某个WHEN后面的表达式为真，执行该语句
若所有表达式都为假，则执行ELSE语句

（3）LOOP语句

[LOOP标注名] LOOP
<循环体>
END LOOP [LOOP标注名]

LOOP语句使用LEAVE语句跳出循环

mysql> CREATE PROCEDURE testForLoop()
    -> BEGIN
    -> DECLARE var INT;
    -> SET var=0;
    -> add_loop:LOOP
    -> SET var=var+1;
    -> SELECT var AS reslut;
    -> IF var>3 THEN LEAVE add_loop;
    -> END IF;
    -> END LOOP add_loop;
    -> END$
Query OK, 0 rows affected (0.01 sec)

mysql> CALL test_for_loop();
+--------+
| reslut |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

+--------+
| reslut |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

+--------+
| reslut |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

+--------+
| reslut |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

（4）LEAVE和ITERATE语句

LEAVE语句用于跳出循环
ITERATE语句将执行顺序跳转到语句段开头处（即结束本次循环，开始下一次循环）
ITERATE语句只能用于LOOP、REPEAT和WHILE语句中

LEAVE <标注名>;
ITERATE <标注名>;

（5）REPEAT语句

REPEAT语句是一个带条件判断的循环语句
直到条件为真之前，都会执行循环体语句

[REPEAT标注名] REPEAT
<循环体>
UNTIL <判断条件>
END REPEAT [REPEAT标注名]

（6）WHILE语句

直到表达式为假之前都会执行循环体语句

[WHILE标注名] WHILE <表达式> DO
<循环体>
END REPEAT [WHILE标注名]

二、函数

函数和存储过程的最大区别在于返回值的不同
存储过程可以返回0、1或者多个值；函数只能使用RETURN语句返回一个值或单张表
函数的参数默认为IN类型，函数必须有一个RETURN VALUE语句

1、创建函数

CREATE FUNCTION <[数据库.]函数名>([IN|OUT|INOUT 参数名])
RETURNS <返回值的类型>
[指定函数的特性]
BEGIN
<函数体>
RETURN (<返回值>);
END

函数的特性和存储过程的特性相同

mysql> CREATE FUNCTION employees.count_table()
    -> RETURNS INT
    -> READS SQL DATA
    -> BEGIN
    -> RETURN (SELECT COUNT(*) FROM employees);
    -> END$
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT count_table()$
+---------------+
| count_table() |
+---------------+
|        300024 |
+---------------+
1 row in set (0.05 sec)

2、其它

同存储过程

三、查看存储过程和函数

（1）使用SHOW STATUS语句

SHOW PROCEDURE|FUNCTION STATUS [LIKE "表达式"];

mysql> SHOW PROCEDURE STATUS\G;
*************************** 1. row ***************************
                  Db: mysql
                Name: test
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-02-04 20:05:54
             Created: 2020-02-04 20:05:54
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
                  Db: mysql
                Name: test2
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-02-04 23:32:37
             Created: 2020-02-04 23:32:37
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
...
28 rows in set (0.00 sec)

（2）使用SHOW CREATE语句

查看存储过程时，需要指定数据库

SHOW CREATE PROCEDURE <数据库.存储过程名>[\G];

mysql> SHOW CREATE PROCEDURE mysql.test\G;     # test存储在mysql数据库中
*************************** 1. row ***************************
           Procedure: test
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
SHOW DATABASES;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

（3）直接查看ROUTINES表

存储过程和函数都存储在information_schema数据库下的ROUTINES表里

mysql> SELECT ROUTINE_NAME AS name, ROUTINE_TYPE AS type 
			 FROM ROUTINES 
			 WHERE ROUTINE_NAME="test";
+------+-----------+
| name | type      |
+------+-----------+
| test | PROCEDURE |
+------+-----------+
1 row in set (0.00 sec)

四、修改存储过程和函数

ALTER PROCEDURE|FUNCTION <存储过程名或函数名> [存储过程或函数特性];

MySQL只能修改存储过程或函数的特性，而不能修改程序

五、删除存储过程或函数

DROP PROCEDURE|FUNCTION [IF EXISTS] <存储过程名或函数名>;

mysql> DROP PROCEDURE mysql.test;
Query OK, 0 rows affected (0.03 sec)
mysql> DROP FUNCTION IF EXISTS count_table;
Query OK, 0 rows affected (0.01 sec)

转载：https://blog.csdn.net/qq_40650558/article/details/104434053

查看评论

飞道的博客

飞道的博客

个人资料

文章分类

文章存档

阅读排行

评论排行

推荐文章

7、存储过程和函数

文章目录

一、存储过程

1、创建存储过程

2、定义条件和处理程序

（1）定义条件

（2）定义处理程序

3、光标的使用

4、流程控制语句

（1）IF语句

（2）CASE语句

（3）LOOP语句

（4）LEAVE和ITERATE语句

（5）REPEAT语句

（6）WHILE语句

二、函数

1、创建函数

2、其它

三、查看存储过程和函数

（1）使用SHOW STATUS语句

（2）使用SHOW CREATE语句

（3）直接查看ROUTINES表

四、修改存储过程和函数

五、删除存储过程或函数

* 以上用户言论只代表其个人观点，不代表本网站的观点或立场