未加星标

MySQL学习笔记8:MySQL存储过程(storageprocedure)

字体大小 | |
[数据库(综合) 所属分类 数据库(综合) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏
存储过程简介

mysql命令的执行流程,如下图:


MySQL学习笔记8:MySQL存储过程(storageprocedure)
如果我们将上述过程简化一下,省略掉其中的语法分析和编译这2个环节,MySQL的执行效率就可以提高。
本文介绍的存储过程可以实现上述任务。。。
存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。
存储过程存储在数据库内,可以由应用程序调用执行;允许用户声明变量,以及进行流程控制;
存储过程可以接收输入类型的参数,也可以接收输出类型的参数;可以存在多个返回值!
存储过程的效率比单一的SQL语句的效率高。如果我们写了2个SQL语句,MySQL引擎对这2个SQL语句逐一的进行语法分析,
逐一的进行编译,最后,逐一的执行。如果采用存储过程,第一次调用的时候会进行语法分析和编译,以后客户端再进行
调用的时候,直接调用第一次编译的结果就可以了,省略了语法分析和编译这2个环节,效率自然就提高了。
存储过程的优点,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
存储过程语法结构解析
创建存储过程语法,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
[DEFINER={user|CURRENT_USER}] DEFINER是创建者,若省略了这句话,默认的创建者是当前登录到MySQL客户端的用户。
sp_name指的是存储过程的名字。存储过程可以带0个,1个,或多个参数。
参数的前面可以有3个可选的选项,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
存储过程的特性,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
MySQL学习笔记8:MySQL存储过程(storageprocedure)
关于过程体的几点说明,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
创建不带参数的存储过程
mysql> CREATE PROCEDURE sp1() SELECT VERSION();#创建不带参数的存储过程
Query OK, 0 rows affected (0.07 sec)
调用存储过程,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
mysql> CALL sp1();#调用存储过程
+-----------+
| VERSION() |
+-----------+
| 5.5.37 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> CALL sp1;#如果存储过程不带参数,调用时可以省略小括号。
+-----------+
| VERSION() |
+-----------+
| 5.5.37 |
+-----------+
1 row in set (0.00 sec)
创建带有IN类型参数的存储过程
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password VARCHAR(32) NOT NULL,
-> age TINYINT(3) UNSIGNED NOT NULL,
-> sex TINYINT NOT NULL
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> DESC users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| sex | tinyint(4) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)
mysql> INSERT users VALUES(DEFAULT,'A',md5('jsdfdfggf'),22,1);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT users VALUES(DEFAULT,'B',md5('jfdfggf'),32,1);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT users VALUES(DEFAULT,'C',md5('jgfdfggf'),36,0);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT users VALUES(DEFAULT,'D',md5('jgfdfg'),26,1);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT users VALUES(DEFAULT,'E',md5('jfdfg'),29,1);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT users VALUES(DEFAULT,'F',md5('jfg'),19,0);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 3 | C | 858f56e159d975d18b3eb798d08b4adf | 36 | 0 |
| 4 | D | 4db6a6fb1c6b1284a9b80e220144305f | 26 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
6 rows in set (0.00 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED) #注意:参数名不能与数据表中的字段名相同!
-> BEGIN
-> DELETE FROM users WHERE id=p_id;
-> END
-> //
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER ;
mysql> CALL removeUserById(3);
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM users;#可以看到,id=3的记录已经被删除
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 4 | D | 4db6a6fb1c6b1284a9b80e220144305f | 26 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
5 rows in set (0.00 sec)
创建带有IN和OUT类型参数的存储过程
删除指定用户,并返回剩余用户总数
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserAndReturnNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
-> BEGIN
-> DELETE FROM users WHERE id=p_id;
-> SELECT COUNT(*) FROM users INTO userNums;#将SELECT的结果(剩余用户总数)存入userNums中。
-> END
-> //
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER ;
mysql> SELECT COUNT(*) FROM users;#目前,数据表users中有5条记录
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
1 row in set (0.04 sec)
注意下面的命令:调用存储过程时,@nums是用户变量!调用存储过程之后,可以查看其值。
mysql> CALL removeUserAndReturnNums(4,@nums);#调用存储过程(删除id=4的记录,并返回剩余记录总数)
Query OK, 1 row affected (0.06 sec)
mysql> SELECT @nums;#查看变量的值(查看剩余记录总数)
+-------+
| @nums |
+-------+
| 4 |
+-------+
1 row in set (0.04 sec)
mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
关于变量:
通过DECLARE语句声明的变量称为局部变量,局部变量的作用范围仅仅在BEGIN和END语句块之间。
即局部变量只在BEGIN和END语句块之间有效;BEGIN和END语句块运行结束后,局部变量就失效了。
注意:在BEGIN和END语句块之间声明局部变量的时候,DECLARE语句必须位于语句块的第一行!
mysql> SET @var=8;#声明用户变量(对当前用户所使用的客户端有效)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @var;
+------+
| @var |
+------+
| 8 |
+------+
1 row in set (0.00 sec)
用户变量和MySQL客户端绑定在一起,用户变量只对当前用户所使用的客户端生效!
mysql> SELECT COUNT(*) FROM users INTO @var;#将SELECT的结果存入@var
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @var;
+------+
| @var |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
创建带有多个OUT类型参数的存储过程
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
4 rows in set (0.00 sec)
下面,我们创建一个存储过程,可以根据age来删除用户,并且返回删除的用户数和剩余的用户数。
在创建存储过程之前,我们先学习一个系统函数ROW_COUNT(),该函数返回插入,更新或删除的记录总数。
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| -1 |
+-------------+
1 row in set (0.02 sec)
下面,我们示例一下ROW_COUNT()函数的使用:
mysql> CREATE TABLE test(
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.14 sec)
mysql> DESC test;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> INSERT INTO test(username) VALUES('A'),('B'),('C');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();#此处的ROW_COUNT()返回的是 插入的记录数
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
mysql> UPDATE test SET username = CONCAT(username,'--haha') WHERE id<=2;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT ROW_COUNT();#此处的ROW_COUNT()返回的是 更新的记录数
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | A--haha |
| 2 | B--haha |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
mysql> DELETE FROM test WHERE id=3;
Query OK, 1 row affected (0.05 sec)
mysql> SELECT ROW_COUNT();#此处的ROW_COUNT()返回的是 删除的记录数
+-------------+
| ROW_COUNT() |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | A--haha |
| 2 | B--haha |
+----+----------+
2 rows in set (0.00 sec)
综上,通过ROW_COUNT()函数可以得到插入,更新或删除的记录总数!
下面,我们来创建一个存储过程,它可以根据age来删除用户,并且返回删除的用户数和剩余的用户数。
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteNums SMALLINT UNSIGNED,OUT restNums SMALLINT UNSIGNED)
-> BEGIN
-> DELETE FROM users WHERE age=p_age; #根据年龄 删除记录
-> SELECT ROW_COUNT() INTO deleteNums;#将删除记录总数存入deleteNums中
-> SELECT COUNT(*) FROM users INTO restNums;#将剩余记录数存入restNums中
-> END
-> //
Query OK, 0 rows affected (0.05 sec)
mysql> DELIMITER ;
mysql> SELECT * FROM users;此时,数据表中有4条记录
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
4 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM users WHERE age=32;#数据表中,age=32的记录只有1条
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> CALL removeUserByAgeAndReturnInfos(32,@deleteN,@restN);#调用存储过程(删除age=32的记录)
Query OK, 1 row affected (0.22 sec)
mysql> SELECT @deleteN,@restN;#查看 删除的记录数和剩余的记录数
+----------+--------+
| @deleteN | @restN |
+----------+--------+
| 1 | 3 |
+----------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users;#调用存储过程之后,数据表中只剩下3条记录
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
3 rows in set (0.00 sec)
存储过程与自定义函数的区别,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
修改存储过程,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)
存储过程只能修改一些简单的特性,不能修改过程体;如果要修改过程体,只能先将存储过程删除,再重新创建。
删除存储过程,如下图:
MySQL学习笔记8:MySQL存储过程(storageprocedure)

本文数据库(综合)相关术语:系统安全软件

主题: SQLMySQL存储过程AU删除UT数据DU变量TI
分页:12
转载请注明
本文标题:MySQL学习笔记8:MySQL存储过程(storageprocedure)
本站链接:http://www.codesec.net/view/483025.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(综合) | 评论(0) | 阅读(38)