一次编写多次条用——MySQL存储过程

最新推荐文章于 2022-02-27 12:42:44 发布
最新推荐文章于 2022-02-27 12:42:44 发布
阅读量331 收藏
点赞数
分类专栏： MySQL学习笔记 文章标签： MySQL存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37972723/article/details/79661780
版权

存储过程（Srored Procedure）

一、什么是存储过程

一组可编程的函数，是为了完成特定功能的SQL语句集，进编创建并保存在数据库的服务器中，它的存储和调用都是在服务器进行的，用户可以通过调用存储过程的名字并给定参数（需要时）来调用并执行。

二、优点

将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用
批量处理： SQL+ 循环，并且存储过程是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输，减少网络流量。
统一接口，确保数据安全。

三、存储过程的基本操作

创建和调用

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

create procedure sp_name([参数列表])
[存储过程特性] 
过程体

调用存储过程

call 存储过程名字

创建不带参数的存储过程

mysql> CREATE PROCEDURE sp1()
    -> SELECT VERSION();
    -> //
Query OK, 0 rows affected (0.05 sec)

mysql> CALL sp1();
    -> //
+-----------+
| VERSION() |
+-----------+
| 5.5.59    |
+-----------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 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 UNSIGNED NOT NULL DEFAULT 10,
    -> sex BOOLEAN
    -> )//
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE PROCEDURE removeUsersById(IN p_id INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE id = p_id;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL removeUsersById(2);
    -> //
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM users;//
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  20 |    0 |
|  3 | C        | 0d61f8370cad1d412f80b84d143e1257 |  23 |    1 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |  24 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  24 |    0 |
|  6 | F        | 800618943025315f869e4e1f09471012 |  23 |    0 |
|  7 | G        | dfcf28d0734569a6a693bc8194de62bf |  22 |    0 |
|  8 | H        | c1d9f50f86825a1a2302ec2449c17196 |  23 |    0 |
|  9 | I        | dd7536794b63bf90eccfd37f9b147d7f |  23 |    0 |
| 10 | J        | ff44570aca8241914870afbc310cdb85 |  22 |    1 |
| 11 | K        | a5f3c6a11b03839d46af9fb43c97c188 |  22 |    1 |
| 12 | L        | d20caec3b48a1eef164cb4ca81ba2587 |  22 |    0 |
| 13 | M        | 69691c7bdcc3ce6d5d8a1361f22d04ac |  24 |    1 |
| 14 | N        | 8d9c307cb7f3c4a32822a51922d1ceaa |  21 |    0 |
| 15 | O        | f186217753c37b9b9f958d906208506e |  20 |    0 |
| 16 | P        | 44c29edb103a2872f519ad0c9a0fdaaa |  20 |    1 |
| 17 | Q        | f09564c9ca56850d4cd6b3319e541aee |  24 |    1 |
| 18 | R        | e1e1d3d40573127e9ee0480caf1283d6 |  24 |    1 |
+----+----------+----------------------------------+-----+------+
17 rows in set (0.00 sec)

mysql> SELECT * FROM users WHERE id = 2;
    -> //
Empty set (0.05 sec)

创建带有in和out类型的参数的存储过程
OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
要在调用存储过程时定义一个全局变量用于接收out类型的值

mysql> USE test;
Database changed
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  20 |    0 |
|  3 | C        | 0d61f8370cad1d412f80b84d143e1257 |  23 |    1 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |  24 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  24 |    0 |
|  6 | F        | 800618943025315f869e4e1f09471012 |  23 |    0 |
|  7 | G        | dfcf28d0734569a6a693bc8194de62bf |  22 |    0 |
|  8 | H        | c1d9f50f86825a1a2302ec2449c17196 |  23 |    0 |
|  9 | I        | dd7536794b63bf90eccfd37f9b147d7f |  23 |    0 |
| 10 | J        | ff44570aca8241914870afbc310cdb85 |  22 |    1 |
| 11 | K        | a5f3c6a11b03839d46af9fb43c97c188 |  22 |    1 |
| 12 | L        | d20caec3b48a1eef164cb4ca81ba2587 |  22 |    0 |
| 13 | M        | 69691c7bdcc3ce6d5d8a1361f22d04ac |  24 |    1 |
| 14 | N        | 8d9c307cb7f3c4a32822a51922d1ceaa |  21 |    0 |
| 15 | O        | f186217753c37b9b9f958d906208506e |  20 |    0 |
| 16 | P        | 44c29edb103a2872f519ad0c9a0fdaaa |  20 |    1 |
| 17 | Q        | f09564c9ca56850d4cd6b3319e541aee |  24 |    1 |
| 18 | R        | e1e1d3d40573127e9ee0480caf1283d6 |  24 |    1 |
+----+----------+----------------------------------+-----+------+
17 rows in set (0.01 sec)

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(id) FROM users INTO userNums;
    -> END
    -> //
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

mysql> SELECT count(id) FROM users;
+-----------+
| count(id) |
+-----------+
|        17 |
+-----------+
1 row in set (0.06 sec)

mysql> CALL removeUserAndReturnNums(5,@nums);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT @nums;
+-------+
| @nums |
+-------+
|    16 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  20 |    0 |
|  3 | C        | 0d61f8370cad1d412f80b84d143e1257 |  23 |    1 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |  24 |    1 |
|  6 | F        | 800618943025315f869e4e1f09471012 |  23 |    0 |
|  7 | G        | dfcf28d0734569a6a693bc8194de62bf |  22 |    0 |
|  8 | H        | c1d9f50f86825a1a2302ec2449c17196 |  23 |    0 |
|  9 | I        | dd7536794b63bf90eccfd37f9b147d7f |  23 |    0 |
| 10 | J        | ff44570aca8241914870afbc310cdb85 |  22 |    1 |
| 11 | K        | a5f3c6a11b03839d46af9fb43c97c188 |  22 |    1 |
| 12 | L        | d20caec3b48a1eef164cb4ca81ba2587 |  22 |    0 |
| 13 | M        | 69691c7bdcc3ce6d5d8a1361f22d04ac |  24 |    1 |
| 14 | N        | 8d9c307cb7f3c4a32822a51922d1ceaa |  21 |    0 |
| 15 | O        | f186217753c37b9b9f958d906208506e |  20 |    0 |
| 16 | P        | 44c29edb103a2872f519ad0c9a0fdaaa |  20 |    1 |
| 17 | Q        | f09564c9ca56850d4cd6b3319e541aee |  24 |    1 |
| 18 | R        | e1e1d3d40573127e9ee0480caf1283d6 |  24 |    1 |
+----+----------+----------------------------------+-----+------+
16 rows in set (0.00 sec)

创建带有多个参数的存储过程

mysql> CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUser SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE age=p_age;
    -> SELECT ROW_COUNT() INTO deleteUser;
    -> SELECT COUNT(id) FROM users INTO userCounts;
    -> END
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM users;
    -> //
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  20 |    0 |
|  3 | C        | 0d61f8370cad1d412f80b84d143e1257 |  23 |    1 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |  24 |    1 |
|  6 | F        | 800618943025315f869e4e1f09471012 |  23 |    0 |
|  7 | G        | dfcf28d0734569a6a693bc8194de62bf |  22 |    0 |
|  8 | H        | c1d9f50f86825a1a2302ec2449c17196 |  23 |    0 |
|  9 | I        | dd7536794b63bf90eccfd37f9b147d7f |  23 |    0 |
| 10 | J        | ff44570aca8241914870afbc310cdb85 |  22 |    1 |
| 11 | K        | a5f3c6a11b03839d46af9fb43c97c188 |  22 |    1 |
| 12 | L        | d20caec3b48a1eef164cb4ca81ba2587 |  22 |    0 |
| 13 | M        | 69691c7bdcc3ce6d5d8a1361f22d04ac |  24 |    1 |
| 14 | N        | 8d9c307cb7f3c4a32822a51922d1ceaa |  21 |    0 |
| 15 | O        | f186217753c37b9b9f958d906208506e |  20 |    0 |
| 16 | P        | 44c29edb103a2872f519ad0c9a0fdaaa |  20 |    1 |
| 17 | Q        | f09564c9ca56850d4cd6b3319e541aee |  24 |    1 |
| 18 | R        | e1e1d3d40573127e9ee0480caf1283d6 |  24 |    1 |
+----+----------+----------------------------------+-----+------+
16 rows in set (0.00 sec)

mysql> DELIMITER ;
mysql> CALL removeUserByAgeAndReturnInfos(20,@a,@b);
Query OK, 1 row affected (0.32 sec)

mysql> SELECT @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    3 |   13 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(id) FROM users;
+-----------+
| COUNT(id) |
+-----------+
|        13 |
+-----------+
1 row in set (0.00 sec)

查看存储过程
show procedure status [like “pattern”]

mysql> show procedure status;
+------------+-------------------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db         | Name                          | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------+-------------------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| learnmysql | loop_example                  | PROCEDURE | root@localhost | 2018-03-15 16:37:13 | 2018-03-15 16:37:13 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| learnmysql | p1                            | PROCEDURE | root@localhost | 2018-03-15 15:56:44 | 2018-03-15 15:56:44 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| learnmysql | p2                            | PROCEDURE | root@localhost | 2018-03-15 16:00:44 | 2018-03-15 16:00:44 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| learnmysql | while_example                 | PROCEDURE | root@localhost | 2018-03-15 16:06:47 | 2018-03-15 16:06:47 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| test       | removeUserAndReturnNums       | PROCEDURE | root@localhost | 2018-03-15 08:00:27 | 2018-03-15 08:00:27 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| test       | removeUserByAgeAndReturnInfos | PROCEDURE | root@localhost | 2018-03-15 08:29:43 | 2018-03-15 08:29:43 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| test       | removeUsersById               | PROCEDURE | root@localhost | 2018-03-14 23:37:41 | 2018-03-14 23:37:41 | DEFINER       |         | gbk                  | gbk_chinese_ci       | utf8_general_ci    |
| test       | sp1                           | PROCEDURE | root@localhost | 2018-03-14 22:56:51 | 2018-03-14 22:56:51 | DEFINER       |         | gbk                  | gbk_chinese_ci       | utf8_general_ci    |
+------------+-------------------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
8 rows in set (0.19 sec)

show create procedure 名字；

mysql> show create procedure sp1;
+-----------+----------------------------------------------------------------+----------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                       | Create Procedure                                                     | character_set_client | collation_connection | Database Collation |
+-----------+----------------------------------------------------------------+----------------------------------------------------------------------+----------------------+----------------------+--------------------+
| sp1       | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`()
SELECT VERSION() | gbk                  | gbk_chinese_ci       | utf8_general_ci    |
+-----------+----------------------------------------------------------------+----------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.13 sec)

两种方式区别
show status语句只能查看存储过程所操作的数据库对象，如存储过程的名称、类型、定义者，修改时间等信息，并不能查看存储过程的具体定义。show create 可以查看具体定义。

删除存储过程
drop procedure if exists 存储过程名字；

mysql> drop procedure if exists sp1;
Query OK, 0 rows affected (0.17 sec)
AFeng521web
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
毕业设计——流媒体视频直播服务器（Java + MySQL + FFmpeg + RTSP + RTP）.zip
01-15
Java程序只要编写一次，就可到处运行。 编译和解释性 Java编译程序生成字节码（byte-code），而不是通常的机器码。Java字节码提供对体系结构中性的目标文件格式，代码设计成可有效地传送程序到多个平台。Java程序...
MySQL存储过程
天元白手的博客
09-19 331
MySQL存储过程 1、什么是存储过程？ https://www.runoob.com/w3cnote/mysql-stored-procedure.html 存储过程是一种SQL语句集，经过编译后存储在数据库中，通过指定存储过程的名称和参数信息来调用存储过程，使其完成特定的功能，在创建存储过程的时候，可以自定义变量来存储一些中间结果的数据，使其完成特定的功能，在创建存储过程的时候，可以自定义变量来存储一些中间结果的数据，也可以在存储过程中定义一些执行逻辑和执行流程。 ​ 存储过程一次编译可以永久使用（只要
MySQL必知必会01：一个完整的存储过程
东南飘雪
02-27 5135
阅读整理自《MySQL 必知必会》- 朱晓峰
mysql在表中一次性插入上万条数据（用存储过程解决）
weixin_39074559的博客
04-15 7258
写一个存储过程，里面写一个循环，就可以了。主键你现在不是自增的，所以写语句的时候，就Insert到3个字段中。 DELIMITER $$ DROP PROCEDURE IF EXISTS `proc_auto_insertdata`$$ CREATE PROCEDURE `proc_auto_insertdata`() BEGIN DECLARE init_...
MySQL入门经典-----7、MySQL存储过程（一次编写多次调用）
hekewangzi
08-03 781
MySQL入门经典-----7、MySQL存储过程（一次编写多次调用）
InOutBoard:InOut Board 作为 Web 应用程序
06-13
一条评论 我编写这个应用程序是为了自学如何更好地使用 、 和 。 这个初始版本是基于网络的 - 下一次迭代将提供一个 Android 应用程序。 入门 克隆/分叉此存储库。 先决条件 Java 1.8 Maven 3 新产品管理 鲍尔 ...
PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part1
06-14
实例112 解决用substr()函数对中文字符串截取时出现乱码的问题 143 实例113 字符串与HTML标记相互转换 144 实例114 运用PHP 5.0新型字符串输出XML数据 145 实例115 判断字符串中是否存在指定子串 146 2.9 正则表达式...
PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part2
06-14
实例112 解决用substr()函数对中文字符串截取时出现乱码的问题 143 实例113 字符串与HTML标记相互转换 144 实例114 运用PHP 5.0新型字符串输出XML数据 145 实例115 判断字符串中是否存在指定子串 146 2.9 正则表达式...
mysql 动态执行存储过程语句
09-11
MSSQL中动态执行sql语句可以使用EXEC()函数。MSSQL中也有类似的函数EXECUTE()，不过不同的是MYSQL中动态执行存储过程语句与MSSQL还是有区别的。
mysql多次调用存储过程
wusoule的专栏
06-30 1803
这个问题也困扰了我很长时间，准确的说正是因为他的存储过程无法在同一连接中2次或者多次执行，我大幅修该了程序架构，全部题换成了sql，但是毕竟sql无法执行有相当逻辑的代码，最总让我从新测试以求寻找解决之道。 问题是这样的，在直接使用mysql c api构建应用的时候，一个连接只能执行一次存储过程，不管怎样free再次利用这个连接的时候就会出现不能执行qurry的提示，我想，这么大型的软件
MySQL数据库（多表查询，存储过程，变量）
weixin_62520626的博客
02-20 1535
级联删除 级联更新 内连接 外连接 子查询 复制表 存储过程 变量的介绍 1.级联删除 删除用户时，把你关联的数据一并删掉。 CREATE TABLE zhu( zid INT PRIMARY KEY AUTO_INCREMENT, -- 主键 zname VARCHAR(20) ); CREATE TABLE cong( zid INT PRIMARY KEY AUTO_INCREMENT, zscore INT, zzid INT
mysql大批量数据写入_存储过程写入大批量数据，用于测试mysql查询优化
weixin_32315511的博客
01-19 278
---恢复内容开始---最近仔细研究了一下mysql优化索引的知识，当然开始玩之前还是要插入大量数据的下面是表结构Ps:这个插入是我看韩老师的mysq优化章节1 #创建表EMP雇员2 CREATE TABLE emp3 (empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/4 ename VARCHAR(20) NOT NULL DEFAULT ""...
Hive文件格式（表stored as 的五种类型）
热门推荐
yangshaojun1992的博客
12-20 2万+
hive文件存储格式包括以下几类： 1、TEXTFILE 2、SEQUENCEFILE 3、RCFILE 4、ORCFILE(0.11以后出现) 5、PARQUET 1、其中TEXTFILE为默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理；     SEQUENCEFILE，RCFILE，ORCFILE,PARQUET格式的表不能直接从本地...
MySQL存储过程一次性添加多条数据
上善若水
09-07 3074
MySQL存储过程一次性添加多条数据 代码块 DELIMITER $$ CREATE PROCEDURE add_in_Student(in i INT) BEGIN DECLARE init_data INTEGER DEFAULT 1; WHILE init_data <= i DO ...
mysql存储过程 每1000条删除一次
最新发布
07-27
对于每1000条删除一次的需求，您可以使用存储过程来实现。以下是一个示例存储过程： ```sql DELIMITER $$ CREATE PROCEDURE delete_rows() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE id INT; DECLARE cur ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值