MYSQL——存储过程

最新推荐文章于 2023-06-18 11:06:43 发布

小五子了

最新推荐文章于 2023-06-18 11:06:43 发布

阅读量270

点赞数

分类专栏：菜鸟开始走文章标签： mysql 数据库函数存储

本文链接：https://blog.csdn.net/bang3/article/details/78632826

版权

菜鸟开始走专栏收录该内容

15 篇文章 0 订阅

订阅专栏

存储过程简介
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
————
存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式

这里写图片描述

存储过程的优点
1.增强SQL语句的功能和灵活性，部署方便性
2.实现较快的执行速度
3.减少网络流量
存储过程的缺点
1.面对复杂的业务逻辑时，存储过程的过程化的处理会很吃力，不应该用存储过程去处理复杂的业务逻辑。
2.不便于调试
3.没有移植性
4.无法适应数据库的切割
创建存储过程
CREATE
[DEFINER={user|CURRENT_USER}] //定义时的用户，不写默认为当前用户
PROCEDURE sp_name([proc_parameter[,…]]) //可以带0到多个参数、sp_name：存储过程的名字
[characteristic …] routine_body
————
其中参数：
proc_parameter:
[ IN | OUT | INOUT ] param_name type
IN : 表示该参数的值必须在调用存储过程时指定，不能被返回。输入
OUT : 表示该参数的值可以被存储过程改变，并且可以返回。输出
INOUT : 表示该参数的值调用时指定，并且可以被改变和返回
存储过程的特性
COMMENT ‘string’
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY{ DEFINER | INVOKER}
COMMENT:注释
CONTAINS SQL:包含SQL语句, 但不包含读或写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含读数据的语句
MODIFIES SQL DATA:包含写数据的语句
SQL SECURITY {DEFINER|INVOKER}指明谁有权限来执行
过程体 (基本和函数的过程体一样)
1.过程体由合法的SQL语句构成；
2.过程体可以是’任意’SQL语句;对表格进行增删，连接，但是不能创建数据表
3.过程体如果为复合结构则使用BEGIN…END语句
4.复合结构可以使用条件、循环等控制语句
存储过程的调用
1.CALL sp_name([parameter [,…]])
2.CALL sp_name[()]
区别：
1.如果我们的存储过程没有参数，那么调用的小括号带有没带有都可以。
2.如果存储过程带有参数，那么小括号就一定要写
存储过程的修改(只能修改一些简单的特性，并不能修改过程体，如果想要修改过程体就要先将存储过程DROP然后重新创建)
ALTER PROCEDURE sp_name[characteristic …]
COMMENT ‘string’
| { CONTAINS SQL |ＮＯ　SQL | READS SQL DATA | MODIFIES SQL DATA}
|SQL SECURITY {DEFINER | INVOKER }
创建不带参数的存储过程
CREATE PROCEDURE p1()
SELECT NOW();

调用：这里存储过程没有参数，所以用CALL p1;和CALL p1();都可以。

创建带IN类型参数的存储过程
比如删除表user中的一条数据。
DELIMITER // 修改分隔符
CREATE PROCEDURE removeUserById(IN user_id INT UNSIGEND)
BEGIN
DELETE FROM user WHERE id = user_id; //特别注意此处的id和user_id名字不能一样，一样的话数据库会认为是两个字段，那么全表数据都会被删除，一定要区分开。
END
//

调用：CALL removeUserById(4);

创建带有IN和OUT类型参数的存储过程
删除表user中id不固定的数并且返回剩余数量
CREATE PROCEDURE p3(IN user_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id=user_id;
SELECT COUNT(id) FROM user INTO userNums;
END
//
调用： CALL p3(1,@nums);
SELECT @nums；

此处用@nums来接收p3返回的变量剩余数userNums，
用DECLARE声明的变量叫局部变量，只能在BEGIN和END中间，而用@声明的叫用户变量。
用户变量：以‘@’开始，形式名为‘@变量名’
用户变量和MySQL客户端是绑定的，设置的变量，只对当前用户使用的客户端生效。如果以set 来给变量赋值给存储过程的out传递一个@变量，也相当于创建用户变量并传入，在存储过程中被赋值。
全局变量：定义时，以以下两种形式出现，set GLOBAL 变量名或者 set @@global。变量名，对所有客户端生效。只有具有super权限的用户才可以设置全局变量。
会话变量：只对连接的客户端有效
局部变量：作用在BEGIN和END语句块之间，在该语句块之间专门用DECLARE定义局部变量，且必须位于BEGIN和END的第一行来定义。比如：DECLARE 变量名变量类型； set语句是设置不同类型的变量，可以为各种变量赋值，为set 变量名 = 变量值；包括会话变量和全局变量。

上面也可写成：
这里写图片描述
然后调用时就直接输出剩余数不用再select了。

创建带有多个OUT类型参数的存储过程
删除表user中age不固定的数并且返回剩余用户数和删除的用户数
ROW_COUNT():返回插入、删除或更新的，即被影响的行数

比如：
这里写图片描述

CREATE PROCEDURE p0(IN uage INT UNSIGNED,OUT deleteNums INT UNSIGNED,OUT userCounts INT UNSIGNED)
BEGIN
DELETE FROM user WHERE age=uage;
SELECT ROW_COUNT() INTO deleteNums;
SELECT COUNT(id) FROM user INTO userCounts;
END
//
调用：CALL p0(20,@a,@b);
SELECT @a,@b;

存储过程和自定义函数的区别
1.存储过程实现的功能要复杂一些；而函数的针对性更强
2.存储过程可以返回多个值；函数只能有一个返回值
3.存储过程一般独立的来执行；而函数可以作为其他sql语句的组成部分来出现，就像内置函数一样。
总结
1.存储过程：是sql语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
2.参数：输入类型（IN）输出类型（OUT）输入&&输出（INOUT）
3.创建存储过程：CREATE….PROCEDURE 名称……
4.注意事项：
（1）创建存储过程或者自定义函数时需要通过DELIMITER 语句来修改定界符
（2）如果函数体或过程体有多个语句，需要包含在BEGIN….END语句块中
（3）存储过程通过CALL 来调用