二十三、MySQL基础系列笔记之储存过程与函数
存储过程
什么是存储过程
存储过程是一组事先编辑并存储在数据库中的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日
请登录后再评论