«

MySQL存储过程创建使用及实现数据快速插入的方法是什么

时间:2024-8-5 09:34     作者:韩俊     分类: Mysql


本文小编为大家详细介绍“MySQL存储过程创建使用及实现数据快速插入的方法是什么”,内容详细,步骤清晰,细节处理妥当,希望这篇“MySQL存储过程创建使用及实现数据快速插入的方法是什么”文章能帮助大家解决疑惑,下面跟着小编的思路慢慢深入,一起来学习新知识吧。

一,存储过程介绍

自MySQL 5.0 版本开始支持存储过程。存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。

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

存储过程思想就是数据库 SQL 语言层面的代码封装与重用。类似于Java开发中封装工具类方便以后直接调用的作用。

二,存储过程的优缺点

优点

    存储过程可以一次编译多次使用。存储过程只在创建时进行编译,之后的使用都不需要重新编译,k就提升了sQL的执行效率。

    可以减少开发工作量。将代码封装成模块,实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以重复使用,在减少开发工作量的同时,还能保证代码的结构清晰。

    存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限,这样就和视图一样具有较强的安全性。

    可以减少网络传输量。因为代码封装到存储过程中,每次使用只需要调用存储过程即可,这样就减少了网络传输量。

    良好的封装性。在进行相对复杂的数据库操作时,原本需要使用一条一条的sQL语句,可能要连接多次数据库才能完成的操作,现在变成了一次存储过程,只需要连接一次即可。

缺点

    可移植性差。存储过程不能跨数据库移植,比如在MysQL、Oracle和sQL Server里编写的存储过程,在换成其他数据库时都需要重新编写。

    调试困难。只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说,开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试,但要收费。

    存储过程的版本管理很困难。比如数据表索引发生变化了,可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理,但是存储过程本身没有版本控制,版本迭代更新的时候很麻烦。

    它不适合高并发的场景。高并发的场景需要减少数据库的压力,有时数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了。

 三,存储过程的创建与调用

    存储过程就是具有名字的一段代码,用来完成一个特定的功能。

    创建的存储过程保存在数据库的数据字典中。

3.1,存储过程中的常用语法及参数

声明语句结束符,可以自定义:

DELIMITER $$

声明存储过程:

CREATE PROCEDURE pro_name(IN num int)

存储过程开始和结束符号:

BEGIN .... END

变量赋值:

SET num=1

变量定义:

DECLARE num int unsigned default 100;

创建mysql存储过程、存储函数:

create procedure 存储过程名(参数)

存储过程体:

create function 存储函数名(参数)

参数解析:

MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

    IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)

    OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

    INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

需要注意的是,这里的参数根据需求而定,如果不需要参数,亦可不填写!

3.2,存储过程的使用

下面依次根据实例对不同的情况进行演示:

首先准备一张my_datas表:

mysql> create table if not exists `my_datas`(
`id` int(20) not null auto_increment comment '数据id',
`name` varchar(30) default null comment '姓名',
`address` varchar(45) default null comment '地址',
`time` datetime default null comment '创建时间',
primary key(`id`)
)engine=innodb auto_increment=1 default charset=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.33 sec)

查看结构是否正确:

mysql> show columns from `my_datas`;
+---------+-------------+------+-----+-------------------+-------------------+
| Field   | Type        | Null | Key | Default           | Extra             |
+---------+-------------+------+-----+-------------------+-------------------+
| id      | int         | NO   | PRI | NULL              | auto_increment    |
| name    | varchar(30) | YES  |     | NULL              |                   |
| address | varchar(45) | YES  |     | NULL              |                   |
| time    | datetime    | YES  |     | NULL              | DEFAULT_GENERATED |
+---------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

(1),创建存储函数,向数据表中插入50条数据

mysql> delimiter //    #定义结束符
mysql> drop procedure if exists addMyData;
    -> create procedure addMyData()  #创建一个存储过程,名为:addMyData
    -> begin
    -> declare num int;
    -> set num =1;
    -> while num <=50   #插入50条数据
    -> do
    -> insert into `my_datas`(id,name,address,time) 
    -> values(null,concat('数据_',num,'号'),concat('北京四 合院',round(rand()*10),'号'),concat(current_timestamp()));  #concat函数拼接信息
    -> set num =num +1;
    -> end
    -> while;
    -> end //;
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql> delimiter;  #将语句的结束符号恢复为分号

默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀。 在定义过程时,使用 DELIMITER //命令将语句的结束符号从分号 ; 临时改为两个//,使得过程体中使用的分号被直接传递到服务器,而不会被客户端解释。

调用存储函数,并查询插入结果

mysql> call addMyData();
Query OK, 1 row affected (0.58 sec)
 
mysql> select * from `my_datas`;
+----+--------------+----------------------+---------------------+
| id | name         | address              | time                |
+----+--------------+----------------------+---------------------+
|  1 | 数据_1号     | 北京四合院3号        | 2022-08-24 14:21:17 |
|  2 | 数据_2号     | 北京四合院8号        | 2022-08-24 14:21:17 |
|  3 | 数据_3号     | 北京四合院4号        | 2022-08-24 14:21:17 |
|  4 | 数据_4号     | 北京四合院3号        | 2022-08-24 14:21:17 |
|  5 | 数据_5号     | 北京四合院3号        | 2022-08-24 14:21:17 |
|  6 | 数据_6号     | 北京四合院7号        | 2022-08-24 14:21:17 |
|  7 | 数据_7号     | 北京四合院7号        | 2022-08-24 14:21:17 |
|  8 | 数据_8号     | 北京四合院5号        | 2022-08-24 14:21:17 |
|  9 | 数据_9号     | 北京四合院1号        | 2022-08-24 14:21:17 |
| 10 | 数据_10号    | 北京四合院1号        | 2022-08-24 14:21:17 |
| 11 | 数据_11号    | 北京四合院3号        | 2022-08-24 14:21:17 |
| 12 | 数据_12号    | 北京四合院1号        | 2022-08-24 14:21:17 |
| 13 | 数据_13号    | 北京四合院6号        | 2022-08-24 14:21:17 |
| 14 | 数据_14号    | 北京四合院8号        | 2022-08-24 14:21:17 |
| 15 | 数据_15号    | 北京四合院3号        | 2022-08-24 14:21:17 |
| 16 | 数据_16号    | 北京四合院9号        | 2022-08-24 14:21:17 |
| 17 | 数据_17号    | 北京四合院7号        | 2022-08-24 14:21:17 |
| 18 | 数据_18号    | 北京四合院8号        | 2022-08-24 14:21:17 |
| 19 | 数据_19号    | 北京四合院1号        | 2022-08-24 14:21:17 |
| 20 | 数据_20号    | 北京四合院9号        | 2022-08-24 14:21:17 |
| 21 | 数据_21号    | 北京四合院2号        | 2022-08-24 14:21:17 |
| 22 | 数据_22号    | 北京四合院2号        | 2022-08-24 14:21:17 |
| 23 | 数据_23号    | 北京四合院3号        | 2022-08-24 14:21:17 |
| 24 | 数据_24号    | 北京四合院10号       | 2022-08-24 14:21:17 |
| 25 | 数据_25号    | 北京四合院1号        | 2022-08-24 14:21:17 |
| 26 | 数据_26号    | 北京四合院5号        | 2022-08-24 14:21:17 |
| 27 | 数据_27号    | 北京四合院1号        | 2022-08-24 14:21:17 |
| 28 | 数据_28号    | 北京四合院3号        | 2022-08-24 14:21:17 |
| 29 | 数据_29号    | 北京四合院10号       | 2022-08-24 14:21:17 |
| 30 | 数据_30号    | 北京四合院10号       | 2022-08-24 14:21:17 |
| 31 | 数据_31号    | 北京四合院0号        | 2022-08-24 14:21:17 |
| 32 | 数据_32号    | 北京四合院2号        | 2022-08-24 14:21:17 |
| 33 | 数据_33号    | 北京四合院8号        | 2022-08-24 14:21:17 |
| 34 | 数据_34号    | 北京四合院6号        | 2022-08-24 14:21:17 |
| 35 | 数据_35号    | 北京四合院5号        | 2022-08-24 14:21:17 |
| 36 | 数据_36号    | 北京四合院7号        | 2022-08-24 14:21:17 |
| 37 | 数据_37号    | 北京四合院1号        | 2022-08-24 14:21:17 |
| 38 | 数据_38号    | 北京四合院3号        | 2022-08-24 14:21:17 |
| 39 | 数据_39号    | 北京四合院3号        | 2022-08-24 14:21:17 |
| 40 | 数据_40号    | 北京四合院6号        | 2022-08-24 14:21:17 |
| 41 | 数据_41号    | 北京四合院9号        | 2022-08-24 14:21:17 |
| 42 | 数据_42号    | 北京四合院7号        | 2022-08-24 14:21:17 |
| 43 | 数据_43号    | 北京四合院9号        | 2022-08-24 14:21:17 |
| 44 | 数据_44号    | 北京四合院5号        | 2022-08-24 14:21:17 |
| 45 | 数据_45号    | 北京四合院8号        | 2022-08-24 14:21:17 |
| 46 | 数据_46号    | 北京四合院3号        | 2022-08-24 14:21:17 |
| 47 | 数据_47号    | 北京四合院1号        | 2022-08-24 14:21:17 |
| 48 | 数据_48号    | 北京四合院7号        | 2022-08-24 14:21:17 |
| 49 | 数据_49号    | 北京四合院10号       | 2022-08-24 14:21:17 |
| 50 | 数据_50号    | 北京四合院9号        | 2022-08-24 14:21:17 |
+----+--------------+----------------------+---------------------+
50 rows in set (0.00 sec)

这样,50条需要的数据就能快速插入完毕。但是局限性在于插入的数据是在定义存储过程中写死的,不够灵活。

(2),in输入参数的使用

为了方便灵活的插入/修改/删除/查询我们需要的数据,我们可以定义in 来输入参数,如下:

创建一个用域删除指定id信息的存储过程

mysql> delimiter !!
mysql> create procedure delete_data(in ids int)  #定义一个输入的参数
    -> begin
    -> delete from `my_datas` where id=ids;
    -> end !!
Query OK, 0 rows affected (0.05 sec)
mysql> delimiter ;

执行存储过程并查看数据库信息情况:

mysql> call delete_data(3);  #输入指定参数
   
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from `my_datas` limit 3;
 
+----+-------------+---------------------+---------------------+
| id | name        | address             | time                |
+----+-------------+---------------------+---------------------+
|  1 | 数据_1号    | 北京四合院3号       | 2022-08-24 14:21:17 |
|  2 | 数据_2号    | 北京四合院8号       | 2022-08-24 14:21:17 |
|  4 | 数据_4号    | 北京四合院3号       | 2022-08-24 14:21:17 |
+----+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)

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

(3),out参数的使用

构建一个简单的存储过程:

mysql> create procedure sums(a int ,b int ,out c int)
    -> begin
    -> set c = a+b;
    -> end //
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;

进行验证:

mysql> call sums(11,25,@s);
Query OK, 0 rows affected (0.01 sec)
mysql> select @s;  #正常输出c的值
+------+
| @s   |
+------+
|   36 |
+------+

(4),inout参数的使用

构建一个inout型的存储过程:

mysql> delimiter //
mysql> create procedure inout_test(inout test int)
    -> begin
    -> select test;
    -> set test=100;
    -> select test;
    -> end
    -> //
Query OK, 0 rows affected (0.07 sec)
 
mysql> delimiter ;

测试执行过程:

mysql> set @test=52
    -> ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> call inout_test(@test);
+------+
| test |
+------+
|   52 |
+------+
1 row in set (0.00 sec)
 
+------+
| test |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
mysql> select @test;
+-------+
| @test |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

注意事项:参数的名字不要和列名相同,不然在过程体中,参数名会当作列名来处理,并且,存储过程命名尽量不要跟一些常用函数命名一样,否则sql检查会出错。

四,存储过程中的变量及使用细则

4.1,变量定义

局部变量声明一定要放在存储过程体的开始:

DECLARE 变量名  数据类型(int,float,date&hellip;&hellip;)

例如:

DECLARE  numbers int  

DECLARE l_datetime datetime DEFAULT '2022-09-31 16:12:35';

DECLARE l_varchar varchar(255) DEFAULT '黎治跃在内卷!';

4.2,变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

4.3,用户变量的使用

mysql > SELECT '黎治跃在内卷' into @l;  
Query OK, 0 rows affected (0.00 sec)
mysql > SELECT @l;  
+-------------+  
|   @l        |  
+-------------+  
| 黎治跃在内卷 |  
+-------------+  
1 row in set (0.00 sec)
 
mysql> set @z='做个人吧,黎治跃';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @z;
+--------------------------+
| @z                       |
+--------------------------+
| 做个人吧,黎治跃         |
+--------------------------+
1 row in set (0.00 sec)
 
mysql> SET @y=5+2+52; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @y;
+------+
| @y   |
+------+
|   59 |
+------+
1 row in set (0.00 sec)

存储过程中使用用户变量:

mysql> create procedure see() select concat(@lzy,'Java全能选手');
Query OK, 0 rows affected (0.02 sec)
 
mysql> set @lzy ='黎治跃:';
Query OK, 0 rows affected (0.00 sec)
 
mysql> call see();
+---------------------------------+
| concat(@lzy,'Java全能选手')     |
+---------------------------------+
| 黎治跃:Java全能选手            |
+---------------------------------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

在存储过程间传递全局范围的用户变量:

mysql> CREATE PROCEDURE p1()   SET @last_procedure='l1';  
Query OK, 0 rows affected (0.02 sec)
 
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
Query OK, 0 rows affected (0.03 sec)
 
mysql> CALL p1( );  
mysql> CALL p2( );  
+-----------------------------------------------+  
| CONCAT('Last procedure was ',@last_proc       |  
+-----------------------------------------------+  
| Last procedure was l1                         |  
+-----------------------------------------------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

注意:

1、用户变量名一般以@开头

2、滥用用户变量会导致程序难以理解及管理

4.4,存储过程的一些常用查看命令

查看具体存储过程信息

show create procedure 存储过程名 G

查看所有存储过程

show procedure status G

模糊查询对应的存储过程信息

show procedure status like “模糊查询名” G

查看指定表中的存储信息

select * from 表名 where routine_name = '存储过程名' G

五,常用的存储过程的控制语句

5.1,条件语句

5.1.1,if-then-else

mysql > delimiter //  
mysql > create prcedure test2(in s int)  
     -> begin 
     -> declare num int;  
     -> set num=s+1;  
     -> if num=0 then 
     -> insert into `new_table` values(555);  
     -> end if;  
     -> if s=0 then 
     -> update `new_table` set s1=s1+1;  
     -> else 
     -> update `new_table` set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.07 sec)
 
mysql > delimiter ;

5.1.2,case语句

mysql > delimiter //  
mysql > create procedure test(in sb int)  
     -> begin 
     -> declare num int;  
     -> set num=sb+1;  
     -> case num  
     -> when 0 then   
     -> insert into `new_table` values(23);  
     -> when 1 then   
     -> insert into `new_table` values(24);  
     -> else   
     -> insert into `new_table` values(25);  
     -> end case;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.06 sec)
 
mysql > delimiter ;

5.2,循环语句

5.2.1,while &hellip;&hellip;end while语句

mysql > delimiter //  
mysql > create procedure test()  
     -> begin 
     -> declare num int;  
     -> set num=0;  
     -> while num<6 do  
     -> insert into `new_tables` values(num);  
     -> set num=num+1;  
     -> end while;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.03 sec)
 
mysql > delimiter ;

5.2.2,repeat&hellip;&hellip;end repeat语句

这个语句与while语句的不同之处在于while是先检查再执行,而repeat语句是执行操作后检查结果。

mysql > delimiter //  
mysql > create procedure test()  
     -> begin   
     -> declare num int;  
     -> set num=0;  
     -> repeat  
     -> insert into `new_table` values(num);  
     -> set num=num+1;  
     -> until num>=5  #循环条件
     -> end repeat;  
     -> end;  
     -> //  
 
Query OK, 0 rows affected (0.04 sec)
 
mysql > delimiter ;

5.2.3,loop&hellip;&hellip;end loop语句

loop循环相当于一个while True ...if ... break 循环,与repeat一循环不同,loop可以在循环体的任何位置通过leave离开循环,而repeat只能在循环体最后进行until判断 。此外loop还提供了循环标签,用于在嵌套&middot;循环中标识不同层次的循环。

mysql > delimiter //  
mysql > create procedure test5()  
     -> begin 
     -> declare num int;  
     -> set num=0;  
     -> LOOP1:loop  
     -> insert into `new_table` values(num);  
     -> set num=num+1;  
     -> if num >=5 then 
     -> leave LOOP1;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.04 sec)
 
mysql > delimiter ;

六,其他相关知识点

6.1,存储过程体

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

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

BEGIN
  BEGIN
    BEGIN
      statements; 
    END
  END
END

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

为语句块贴标签:

label1: BEGIN   

   label2: BEGIN   

  label3: BEGIN   

    statements;   

  END label3 ;  

 END label2;

END label1

标签有两个作用:

1、增强代码的可读性

2、在某些语句(例如:leave和iterate语句),需要用到标签

6.2,MySQL AlTER命令对表的灵活操作

6.2.1,删除,添加表字段及默认值

删除表字段

ALTER TABLE 表名  DROP 字段名;

添加表字段

ALTER TABLE 表名 ADD 字段名 字段数据类型;

添加表字段默认值

ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;

删除表字段默认值

ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;

另外,如果需要将表字段插入指定的位置,可以使用MySQL提供的关键字 FIRST (设定位第一列), AFTER 字段名(设定位于某个字段之后)。使用show columns查看表结构变化:

mysql> show columns from user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| address  | varchar(45) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)
 
mysql> alter table user add time datetime; #添加一个time字段
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
#在address后面添加一个sex字段
mysql> alter table user add sex tinyint(1) after address;Query OK, 0 rows affected, 1 warning (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> show columns from user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| address  | varchar(45) | YES  |     | NULL    |                |
| sex      | tinyint(1)  | YES  |     | NULL    |                |
| time     | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

6.2.2,CHANGE与MODIFY对修改字段的作用

当需要修改字段类型或者字段名时,常常会使用到change与modify关键字

modify使用

alter table 表名 modify 字段名 字段属性(更改后)   

modify主要用于更改数据字段范围,当遇到在数据库构建时,范围数据定义过小,或者范围数据定义过大浪费内存空间时,对字段属性的更改。

change使用

alter table 表名 change  old字段名  new字段名  对应的字段属性

change关键字主要用于对字段名的更改,在语法上CHANGE 关键字之后,紧跟着的是你要修

标签: mysql

热门推荐