mysql 存储过程 注入_MySQL之存储过程创建和调用

一、MySQL存储过程_创建-调用

1.1存储过程：SQL中的“脚本”

1.创建存储过程

2.调用存储过程

3.存储过程体

4.语句标签块

二、MySQL存储过程简单介绍：

存储过程(Stored Procedure)：

提示：#SQL语句：先编译后执行

一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

优点(为什么要用存储过程？)：

①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

②批量处理：SQL+循环，减少流量，也就是“跑批”

③统一接口，确保数据的安全

相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

三、存储过程的创建和调用

说明：存储过程就是具有名字的一段代码，用来完成一个特定的功能。创建的存储过程保存在数据库的数据字典中。

3.1创建存储过程

语法介绍：

CREATE

[DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

characteristic:

COMMENT 'string'

| LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

routine_body:

Valid SQL routine statement

[begin_label:] BEGIN

[statement_list]

……

END [end_label]

实例1演示：

创建测试表：

CREATE TABLE `test1_event` (

`id` int(8) NOT NULL AUTO_INCREMENT,

`username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

`password` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

`create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

PRIMARY KEY (`id`) #主键ID

) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入数据：

mysql> INSERT INTO test1_event(username,password,create_time) values('tomcat', 'xiaohuahua',now());

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1_event(username,password,create_time) values('tomcat', 'xiaohuahua',now());

Query OK, 1 row affected (0.00 sec)

mysql> select * from test1_event;

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

| id | username | password | create_time |

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

| 1 | tomcat | xiaohuahua | 2018-09-12 17:18:01 |

| 2 | tomcat | xiaohuahua | 2018-09-12 17:18:39 |

| 3 | tomcat | xiaohuahua | 2018-09-12 17:18:45 |

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

3 rows in set (0.00 sec)

#创建新表备份旧表的数据来用于示例操作：

mysql> create table test_event as select * from test.test1_event;

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test_event;

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

| id | username | password | create_time |

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

| 1 | tomcat | xiaohuahua | 2018-09-12 17:18:01 |

| 2 | tomcat | xiaohuahua | 2018-09-12 17:18:39 |

| 3 | tomcat | xiaohuahua | 2018-09-12 17:18:45 |

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

3 rows in set (0.00 sec)

示例二：创建一个存储过程(给数据表中添加用户和密码并附加创建的时间)

DELIMITER // ###　将语句的结束符号从分号;临时改为两个//(可以是自定义)

DROP PROCEDURE IF EXISTS p_test1//

CREATE PROCEDURE p_test1()

BEGIN

INSERT INTO test_event(username,password,create_time) values('tomcat', 'xiaohuahua',now());

END//

delimiter ; ####将语句的结束符号恢复为分号

查看创建的存储过程详细信息：

mysql> show procedure status;

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

| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |

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

| test | p_test1 | PROCEDURE | root@localhost | 2018-09-12 17:44:40 | 2018-09-12 17:44:40 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |

| test | p_test2 | PROCEDURE | root@localhost | 2018-09-07 18:25:54 | 2018-09-07 18:25:54 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |

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

2 rows in set (0.00 sec)

或者：

mysql> show procedure status\G

*************************** 1. row ***************************

Db: test

Name: p_test1

Type: PROCEDURE

Definer: root@localhost

Modified: 2018-09-12 17:44:40

Created: 2018-09-12 17:44:40

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

mysql查看某个数据库下的所有存储过程函数：

mysql> select `name` from mysql.proc where db = 'test' and `type` = 'PROCEDURE' ;

+---------+

| name |

+---------+

| p_test |

| p_test1 |

| p_test2 |

+---------+

3 rows in set (0.00 sec)

删掉创建的存储过程:

mysql> drop procedure p_test2 ;

Query OK, 0 rows affected (0.00 sec)

mysql> show procedure status;

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

| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |

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

| test | p_test1 | PROCEDURE | root@localhost | 2018-09-12 17:44:40 | 2018-09-12 17:44:40 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |

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

1 row in set (0.00 sec)

查看存储过程的创建代码

mysql> show create procedure p_test1\G

*************************** 1. row ***************************

Procedure: p_test1

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test1`()

BEGIN

INSERT INTO test_event(username,password,create_time) values('tomcat', 'xiaohuahua',now());

END

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

mysql>

解释：

默认情况下，存储过程和默认的数据库相关联，如果先想指定存储过程创建在某个特定的数据库下，那么在过程前面加数据库名做前缀；

在定义过程时，使用DELIMITER // 命令将语句的结束符号从分号 ; 临时改为两个//，使得过程体中使用的分号被直接传递到服务器，而不会被客户端(如mysql)解释。

3.2调用存储过程：call sp_name[(传参)];

实例三：创建一个MySQL定时器调用实例二创建的MySQL存储过程p_test1

提示mysql定时器的创建可以参考此博文：

从2018-09-12 17点47分开始每60秒执行一次

DELIMITER //

CREATE EVENT e_test1

ON SCHEDULE EVERY 60 second STARTS TIMESTAMP '2018-09-12 17:47:00'

ON COMPLETION PRESERVE

DO

BEGIN

CALL p_test1();

END//

delimiter ;

mysql> select * from test_event;

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

| id | username | password | create_time |

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

| 1 | tomcat | xiaohuahua | 2018-09-12 17:18:01 |

| 2 | tomcat | xiaohuahua | 2018-09-12 17:18:39 |

| 3 | tomcat | xiaohuahua | 2018-09-12 17:18:45 |

| 0 | tomcat | xiaohuahua | 2018-09-12 17:47:00 |

| 0 | tomcat | xiaohuahua | 2018-09-12 17:48:00 |

| 0 | tomcat | xiaohuahua | 2018-09-12 17:49:00 |

| 0 | tomcat | xiaohuahua | 2018-09-12 17:50:00 |

| 0 | tomcat | xiaohuahua | 2018-09-12 17:51:00 |

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

21 rows in set (0.00 sec)

实例四：创建一个存储过程并同时开启定时器,定时调用该存储过程向MySQL表test1_event 插入数据

DELIMITER //

DROP PROCEDURE IF EXISTS p_test2//

CREATE PROCEDURE p_test2()

BEGIN

INSERT INTO test1_event(username,password,create_time) values('tomcat', 'xiaohuahua',now());

END//

CREATE EVENT e_test

ON SCHEDULE EVERY 60 second STARTS TIMESTAMP '2018-09-13 18:21:00'

ON COMPLETION PRESERVE

DO

BEGIN

CALL p_test2();

END//

delimiter ;

说明：

在创建的存储过程中设置了需要传参的变量时，调用存储过程的时候，通过传参将数值赋值给存储工程中设置的变量，然后进行存储过程里的SQL操作。

3.3存储过程体

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等

过程体格式：以begin开始，以end结束(可嵌套)

复制代码

BEGIN

BEGIN

BEGIN

statements;

END

END

END

复制代码

注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

4、为语句块贴标签

[begin_label:] BEGIN

[statement_list]

END [end_label]

例如：

复制代码

label1: BEGIN

label2: BEGIN

label3: BEGIN

statements;

END label3 ;

END label2;

END label1

复制代码

标签有两个作用：

①增强代码的可读性

②在某些语句(例如:leave和iterate语句)，需要用到标签