二十三、MySQL基础系列笔记之储存过程与函数

作者: 温新

分类: 【MySql】

阅读: 1610

时间: 2020-11-08 13:08:08

存储过程

什么是存储过程

存储过程是一组事先编辑并存储在数据库中的SQL语句的集合。

如有2条可能会重复使用到的SQL语句,一条一条执行下去,比如重复10次,那么就要输入20次。更加高效的方法,将2条语句封装到存储过程种,执行10次,只组要调用10次存储过程就行了。

存储过程优点

  • 简化操作;
  • 提到代码重用率
  • 减少数据在数据库和应用服务器之间的传输,提高效率。

创建与使用存储过程

创建存储过程语法

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> 存储过程名([<span style="box-sizing: border-box;color: rgb(119, 0, 136)">IN</span>|<span style="box-sizing: border-box;color: rgb(119, 0, 136)">OUT</span>|<span style="box-sizing: border-box;color: rgb(119, 0, 136)">INOUT</span>] 参数名 参数类型)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SQL</span>语句</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span></span>

参数解释:

IN参数:输入参数

OUT参数:输出参数

INOUT参数:既可以是输入也可以是输出

存储过程:可以有0个返回值,也可以有多个返回值。

使用存储过程

语法

CALL 存储过程名(实参列表);

存储过程案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">#案例一 空参存储过程</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DELIMITER</span> $$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> demo1()</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> * <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FROM</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">user</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 调用</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CALL</span> demo1()$$ </span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 案例二 IN参数存储过程</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DELIMITER</span> $$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> demo2(<span style="box-sizing: border-box;color: rgb(119, 0, 136)">IN</span> uid <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> * <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FROM</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">user</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">WHERE</span> id > uid;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">#调用 查询id大于3的所有用户</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CALL</span> demo2(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">3</span>)$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 案例三 OUT参数过程过程</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DELIMITER</span> $$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> demo3(<span style="box-sizing: border-box;color: rgb(119, 0, 136)">IN</span> uid <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>, <span style="box-sizing: border-box;color: rgb(119, 0, 136)">OUT</span> uname <span style="box-sizing: border-box;color: rgb(51, 0, 170)">VARCHAR</span>(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">30</span>))</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> name <span style="box-sizing: border-box;color: rgb(119, 0, 136)">INTO</span> uname <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FROM</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">user</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">WHERE</span> id = uid;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)">#调用。使用一个全局变量@uName来接收返回的结果</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CALL</span> demo3(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">2</span>, <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@uName</span>)$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@uName$$</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 案例四 INOUT参数存储过程</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DELIMITER</span> $$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> demo4(<span style="box-sizing: border-box;color: rgb(119, 0, 136)">INOUT</span> x <span style="box-sizing: border-box;color: rgb(51, 0, 170)">int</span>, <span style="box-sizing: border-box;color: rgb(119, 0, 136)">INOUT</span> y <span style="box-sizing: border-box;color: rgb(51, 0, 170)">INT</span>)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> x=x*<span style="box-sizing: border-box;color: rgb(17, 102, 68)">2</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> y=y*<span style="box-sizing: border-box;color: rgb(17, 102, 68)">2</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 调用</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@xx</span>=<span style="box-sizing: border-box;color: rgb(17, 102, 68)">10</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@yy</span>=<span style="box-sizing: border-box;color: rgb(17, 102, 68)">20</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CALL</span> demo4(<span style="box-sizing: border-box;color: rgb(0, 85, 170)">@xx</span>,<span style="box-sizing: border-box;color: rgb(0, 85, 170)">@yy</span>)$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@xx</span>,<span style="box-sizing: border-box;color: rgb(0, 85, 170)">@yy$$</span></span>

查看存储过程

查看存储过程状态语法

SHOW PROCEDURE STATUS [like 'pattern'];
SHOW PROCEDURE STATUS LIKE 'demo1';

查看存储过程定义的语法

SHOW CREATE PROCEDURE 存储过程名;
SHOW CREATE PROCEDURE demo1;

删除存储过程

语法

DROP PROCEDURE 存储过程名;

案例

DROP PROCEDURE demo1;

函数

函数与存储过程非常类型,不同的是函数只能有一个返回值。

创建函数的语法

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FUNCTION</span> 函数名(参数名 参数类型) <span style="box-sizing: border-box;color: rgb(119, 0, 136)">RETURNS</span> 返回类型</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SQL</span>语句</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span></span>

调用函数

SELECT 函数名(参数列表);

函数案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 案例1 没有参数返回的函数</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DELIMITER</span> $$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FUNCTION</span> fun1() <span style="box-sizing: border-box;color: rgb(119, 0, 136)">RETURNS</span> <span style="box-sizing: border-box;color: rgb(51, 0, 170)">INT</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 声明变量</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> cCount <span style="box-sizing: border-box;color: rgb(51, 0, 170)">INT</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">DEFAULT</span> <span style="box-sizing: border-box;color: rgb(17, 102, 68)">0</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 查询统计为变量赋值</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">COUNT</span>(*) <span style="box-sizing: border-box;color: rgb(119, 0, 136)">INTO</span> cCount <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FROM</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">user</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 返回</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">RETURN</span> cCount;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 调用</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> fun1()$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 案例2 有参数返回的函数</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FUNCTION</span> fun2(uname <span style="box-sizing: border-box;color: rgb(51, 0, 170)">VARCHAR</span>(<span style="box-sizing: border-box;color: rgb(17, 102, 68)">20</span>)) <span style="box-sizing: border-box;color: rgb(119, 0, 136)">RETURNS</span> <span style="box-sizing: border-box;color: rgb(51, 0, 170)">INT</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">DECLARE</span> uid <span style="box-sizing: border-box;color: rgb(51, 0, 170)">INT</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> id <span style="box-sizing: border-box;color: rgb(119, 0, 136)">INTO</span> uid <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FROM</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">user</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">WHERE</span> name = uname;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">RETURN</span> uid;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">END</span>$$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 调用</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> fun2(<span style="box-sizing: border-box;color: rgb(34, 162, 201)">'李四'</span>)$$</span>

查看函数

查看函数状态

SHOW FUNCTION STATUS [like 'pattern'];
SHOW FUNCTION STATUS LIKE 'fun1'$$

查看函数定义的语法

SHOW CREATE FUNCTION 函数名;
SHOW CREATE FUNCTION fun1$$

删除函数

DROP FUNCTION 函数名;
DROP FUNCTION fun1$$

我是小白,期待和优秀的你一起同行!

小白

2020年11月08日

请登录后再评论