mysql java自定义函数_Mysql系列 - 第17篇:存储过程&自定义函数详解

本文介绍了MySQL中的存储过程和自定义函数,包括它们的概念、创建、调用、删除以及示例。强调了存储过程和函数的区别,并提供了一些实际操作的例子,帮助读者掌握在MySQL中如何使用和管理这些功能。
摘要由CSDN通过智能技术生成

Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。

这是Mysql系列第17篇。

环境:mysql5.7.25,cmd命令中进行演示。

代码中被[]包含的表示可选,|符号分开的表示可选其一。

需求背景介绍

线上程序有时候出现问题导致数据错误的时候,如果比较紧急,我们可以写一个存储来快速修复这块的数据,然后再去修复程序,这种方式我们用到过不少。

存储过程相对于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()

BEGIN

INSERT 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))

BEGIN

INSERT 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)

BEGIN

INSERT 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)

BEGIN

SET a = a*2;

select b*2 into b;

END $

/*将结束符置为;*/

DELIMITER ;

调用存储过程:

/*创建了2个自定义变量*/

set @a=10,@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 DEFINER=`root`@`localhost` PROCEDURE `proc4`(INOUT a int,INOUT b int)

BEGIN

SET 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 INT

BEGIN

DECLARE 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 INT

BEGIN

DECLARE 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个或者多个

必须有一个

关键字

procedure

function

调用方式

call

select

Mysql系列目录

mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!

20190723160009295.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值