跨境电商网络营销是什么,镇江关键字优化公司,怎么在拼多多开网店,网站备案好弄吗这是Mysql系列第17篇。
环境#xff1a;mysql5.7.25#xff0c;cmd命令中进行演示。
代码中被[]包含的表示可选#xff0c;|符号分开的表示可选其一。
需求背景介绍
线上程序有时候出现问题导致数据错误的时候#xff0c;如果比较紧急#xff0c;我们可以写一个存储来…这是Mysql系列第17篇。
环境mysql5.7.25cmd命令中进行演示。
代码中被[]包含的表示可选|符号分开的表示可选其一。
需求背景介绍
线上程序有时候出现问题导致数据错误的时候如果比较紧急我们可以写一个存储来快速修复这块的数据然后再去修复程序这种方式我们用到过不少。
存储过程相对于java程序对于java开发来说可能并不是太好维护以及阅读所以不建议在程序中去调用存储过程做一些业务操作。
关于自定义函数这块若mysql内部自带的一些函数无法满足我们的需求的时候我们可以自己开发一些自定义函数来使用。
所以建议大家掌握mysql中存储过程和自定义函数这块的内容。
本文内容 详解存储过程的使用 详解自定义函数的使用
准备数据
/*建库javacode2018*/
drop database if exists javacode2018;
create database javacode2018;/*切换到javacode2018库*/
use javacode2018;/*建表test1*/
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (id INT NOT NULL PRIMARY KEY COMMENT 编号,age SMALLINT UNSIGNED NOT NULL COMMENT 年龄,name VARCHAR(16) NOT NULL COMMENT 姓名
) COMMENT 用户表;存储过程
概念
一组预编译好的sql语句集合理解成批处理语句。
好处 提高代码的重用性 简化操作 减少编译次数并且减少和数据库服务器连接的次数提高了效率。
创建存储过程
create procedure 存储过程名([参数模式] 参数名 参数类型)
begin存储过程体
end参数模式有3种 in该参数可以作为输入也就是该参数需要调用方传入值。 out该参数可以作为输出也就是说该参数可以作为返回值。 inout该参数既可以作为输入也可以作为输出也就是说该参数需要在调用的时候传入值又可以作为返回值。 参数模式默认为IN。 一个存储过程可以有多个输入、多个输出、多个输入输出参数。 调用存储过程
call 存储过程名称(参数列表);注意调用存储过程关键字是call。 删除存储过程
drop procedure [if exists] 存储过程名称;存储过程只能一个个删除不能批量删除。 if exists表示存储过程存在的情况下删除。 修改存储过程
存储过程不能修改若涉及到修改的可以先删除然后重建。
查看存储过程
show create procedure 存储过程名称;可以查看存储过程详细创建语句。 示例
示例1空参列表
创建存储过程
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc1;
/*创建存储过程proc1*/
CREATE PROCEDURE proc1()BEGININSERT INTO t_user VALUES (1,30,路人甲Java);INSERT INTO t_user VALUES (2,50,刘德华);END $/*将结束符置为;*/
DELIMITER ;delimiter用来设置结束符当mysql执行脚本的时候遇到结束符的时候会把结束符前面的所有语句作为一个整体运行存储过程中的脚本有多个sql但是需要作为一个整体运行所以此处用到了delimiter。 mysql默认结束符是分号。 上面存储过程中向t_user表中插入了2条数据。 调用存储过程
CALL proc1();验证效果
mysql select * from t_user;
------------------------
| id | age | name |
------------------------
| 1 | 30 | 路人甲Java |
| 2 | 50 | 刘德华 |
------------------------
2 rows in set (0.00 sec)存储过程调用成功test1表成功插入了2条数据。 示例2带in参数的存储过程
创建存储过程
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc2;
/*创建存储过程proc2*/
CREATE PROCEDURE proc2(id int,age int,in name varchar(16))BEGININSERT INTO t_user VALUES (id,age,name);END $/*将结束符置为;*/
DELIMITER ;调用存储过程
/*创建了3个自定义变量*/
SELECT id:3,age:56,name:张学友;
/*调用存储过程*/
CALL proc2(id,age,name);验证效果
mysql select * from t_user;
------------------------
| id | age | name |
------------------------
| 1 | 30 | 路人甲Java |
| 2 | 50 | 刘德华 |
| 3 | 56 | 张学友 |
------------------------
3 rows in set (0.00 sec)张学友插入成功。 示例3带out参数的存储过程
创建存储过程
delete a from t_user a where a.id 4;
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc3;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc3*/
CREATE PROCEDURE proc3(id int,age int,in name varchar(16),out user_count int,out max_id INT)BEGININSERT INTO t_user VALUES (id,age,name);/*查询出t_user表的记录放入user_count中,max_id用来存储t_user中最小的id*/SELECT COUNT(*),max(id) into user_count,max_id from t_user;END $/*将结束符置为;*/
DELIMITER ;proc3中前2个参数没有指定参数模式默认为in。 调用存储过程
/*创建了3个自定义变量*/
SELECT id:4,age:55,name:郭富城;
/*调用存储过程*/
CALL proc3(id,age,name,user_count,max_id);验证效果
mysql select user_count,max_id;
----------------------
| user_count | max_id |
----------------------
| 4 | 4 |
----------------------
1 row in set (0.00 sec)示例4带inout参数的存储过程
创建存储过程
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc4;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc4*/
CREATE PROCEDURE proc4(INOUT a int,INOUT b int)BEGINSET a a*2;select b*2 into b;END $/*将结束符置为;*/
DELIMITER ;调用存储过程
/*创建了2个自定义变量*/
set a10,b:20;
/*调用存储过程*/
CALL proc4(a,b);验证效果
mysql SELECT a,b;
------------
| a | b |
------------
| 20 | 40 |
------------
1 row in set (0.00 sec)上面的两个自定义变量a、b作为入参然后在存储过程内部进行了修改又作为了返回值。 示例5查看存储过程
mysql show create procedure proc4;
------------------------------------------
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
------------------------------------------
| proc4 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINERrootlocalhost PROCEDURE proc4(INOUT a int,INOUT b int)
BEGINSET a a*2;select b*2 into b;END | utf8 | utf8_general_ci | utf8_general_ci |
------------------------------------------
1 row in set (0.00 sec)函数
概念
一组预编译好的sql语句集合理解成批处理语句。类似于java中的方法但是必须有返回值。
创建函数
create function 函数名(参数名称 参数类型)
returns 返回值类型
begin函数体
end参数是可选的。 返回值是必须的。 调用函数
select 函数名(实参列表);删除函数
drop function [if exists] 函数名;查看函数详细
show create function 函数名;示例
示例1无参函数
创建函数
/*删除fun1*/
DROP FUNCTION IF EXISTS fun1;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun1()returns INTBEGINDECLARE max_id int DEFAULT 0;SELECT max(id) INTO max_id FROM t_user;return max_id;END $
/*设置结束符为;*/
DELIMITER ;调用看效果
mysql SELECT fun1();
--------
| fun1() |
--------
| 4 |
--------
1 row in set (0.00 sec)示例2有参函数
创建函数
/*删除函数*/
DROP FUNCTION IF EXISTS get_user_id;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION get_user_id(v_name VARCHAR(16))returns INTBEGINDECLARE r_id int;SELECT id INTO r_id FROM t_user WHERE name v_name;return r_id;END $
/*设置结束符为;*/
DELIMITER ;运行看效果
mysql SELECT get_user_id(name) from t_user;
-------------------
| get_user_id(name) |
-------------------
| 1 |
| 2 |
| 3 |
| 4 |
-------------------
4 rows in set (0.00 sec)存储过程和函数的区别
存储过程的关键字为procedure返回值可以有多个调用时用call一般用于执行比较复杂的的过程体、更新、创建等语句。
函数的关键字为function返回值必须有一个调用用select一般用于查询单个值并返回。
存储过程函数返回值可以有0个或者多个必须有一个关键字procedurefunction调用方式callselect
