MySOL自定义函数及存储过程

最新推荐文章于 2023-09-04 13:42:31 发布

摩羯的成长期

最新推荐文章于 2023-09-04 13:42:31 发布

阅读量237

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_42603009/article/details/103379171

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

先介绍下MySQL自带的一些函数：
结构：sql --> 结果 #作用

以下都是运行结果:
字符串相关函数
select concat('zhou','yi');  --> zhouyi  #字符串拼接，可多参数
select concat_ws('&',zhou'','yi'); --> zhou&yi #指定分割符号进行字符串链接
# FOMRAT(N,D,locale); N对象，D小数位数，locale可选
select format(2019.011,1); --> 2019.0 #数字格式化
select lower('ZY'); --> zy # 转小写;upper 转大写
select replace('zhouyibaba','zhouyi',''); --> baba# 替换
SELECT left('chinese', 4); --> chin # 左截取; right(str, len)右截取;substring指定截取
select length('zhouyi'); --> 6 # 返回长度
select char_length("周毅"); --> 2 # 如果是length返回6
select ltrim('    space  A'); --> space  A #删除前空格; rtrim删除后空格;trim 删除前后空格
select trim(both '?'from '??zY?zy??'); --> zY?zy
select trim(leading '?'from '??zY?zy??'); --> zY?zy??
select trim(trailing '?'from '??zY?zy??'); --> ??zY?zy
判断子串：
select locate('ho','zhouyi');---> 2 #从字符串zhouyi中获取ho的开始位置,0代表无


日期时间函数
select now(); --> 2019-12-03 09:11:53 #当前日期和时间
select curdate(); --> 2019-12-03 # 当前日期; curtime()当前时间
SELECT DATE_ADD(now(),INTERVAL 2 DAY); --> 2019-12-05 09:19:48 #向日期添加指定的时间间隔 时间间隔有很多
select DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'); --> Dec 03 2019 09:15 AM # 时间格式化

数值运算(用的叫少带过)
div() 整数除法 mod() 取余数 power() 幂运算 truncate() 数字截取 ceil() 进一取整 floor 舍1取整

以及基本的聚合函数：avg() count() max() min() sum()

1，自定义函数：

创建自定义函数语法格式如下：
CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
RETURNS <类型>
<函数主体>

语法说明如下：
<函数名>：指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。
<参数><类型>：用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字 IN、OUT 和 INOUT。
RETURNS<类型>：用于声明自定义函数返回值的数据类型。其中，<类型>用于指定返回值的数据类型。
<函数主体>：自定义函数的主体部分，也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外，自定义函数体还必须包含一个 RETURN<值> 语句，其中<值>用于指定自定义函数的返回值。
即是：

CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
Routine_body

删除自定义函数语法如下：

DROP FUNCTION [ IF EXISTS ] <自定义函数名>

实际举例：
1，无参数举例：

mysql> create function getName()
    -> returns varchar(45)
    -> return 
    -> (select 'zhouyi');
Query OK, 0 rows affected (0.34 sec)

mysql> select getname();
+-----------+
| getname() |
+-----------+
| zhouyi    |
+-----------+
1 row in set (0.11 sec)

mysql>

2，有参数举例：

mysql> CREATE FUNCTION twoAvg(num1 int,num2 int)
    -> RETURNS FLOAT(10,2)
    -> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)

mysql> select twoAvg(2,3);
+-------------+
| twoAvg(2,3) |
+-------------+
|        2.50 |
+-------------+
1 row in set (0.12 sec)

mysql>

这里遗留问题。字符串的操作，尝试了好几遍都失败了。有空看到这一定要里，一定要补充完整。

更进一步，带复合结构的函数体。
注意：当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为反斜杠是 MySQL 的转义字符。
定义语法：

DELIMITER //
CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
BEGIN
//body
END
//      /* 此处的”//“为告诉系统函数定义结束 */

当函数体内需要执行的是多条语句时，要使用BEGIN…END语句，复合结构可以包括声明、循环、控制结构（包括IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE）；且当编写函数体内容的时候，需要使用DELIMITER关键字将分隔符先修改为别的，否则编写语句的时候写到’;’的时候会直接执行，导致函数编写失败。

另外还需要了解自定函数内常量和变量定义：
用户变量：以”@”开始，形式为”@变量名”。用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
语法：

set @name = 值;

全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名或者 set @@global.变量名，对所有客户端生效。只有具有super权限才可以设置全局变量
语法：

set GLOBAL name = 值;

会话变量：只对连接的客户端有效。
局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量
局部变量：
变量定义的语法：

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];

为变量赋值的语法：

SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name

3，复杂一点的自定义函数。

自定义随机生成姓名的自定义函数。

mysql> #自定义开始结束符
mysql> DELIMITER $$
mysql> drop function if exists createRandomName$$
Query OK, 0 rows affected (0.00 sec)

mysql> create function createRandomName()
    -> returns varchar(3)
    -> begin
    -> # 用来存可随机姓 名  形如LMN
    -> declare ln varchar(500);
    -> declare mn varchar(500);
    -> declare nn varchar(500);
    -> # 用来存随机ln mn nn的长度。char_length
    -> declare ln_le int;
    -> declare mn_le int;
    -> declare nn_le int;
    -> # 变量赋值
    -> set ln='ABCDEFGH';
    -> set mn='IJKLMN';
    -> set nn='OPQRYZST';
    -> set ln_le=char_length(ln);
    -> set mn_le=char_length(mn);
    -> set nn_le=char_length(nn);
    -> return (concat(substring(ln,ceil(rand()*ln_le),1),
    -> substring(mn,ceil(rand()*mn_le),1),substring(nn,ceil(rand()*nn_le),1)));
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select createRandomName();
+--------------------+
| createRandomName() |
+--------------------+
| EMR                |
+--------------------+
1 row in set (0.00 sec)

mysql>

解释：

#自定义开始结束符
DELIMITER $$
drop function if exists createRandomName$$
create function createRandomName()
returns varchar(3)
begin
# 申明局部变量，用来存可随机姓 名  形如LMN
declare ln varchar(500);
declare mn varchar(500);
declare nn varchar(500);
# 用来存随机ln mn nn的长度。char_length
declare ln_le int;
declare mn_le int;
declare nn_le int;
# 变量赋值 ,这里由于本人创建数据库时，未指定UTF-8，不能使用。
set ln='ABCDEFGH';
set mn='IJKLMN';
set nn='OPQRYZST';
set ln_le=char_length(ln);
set mn_le=char_length(mn);
set nn_le=char_length(nn);
return (concat(substring(ln,ceil(rand()*ln_le),1),
substring(mn,ceil(rand()*mn_le),1),substring(nn,ceil(rand()*nn_le),1)));
end $$
DELIMITER ;

进一步深入就必须了解控制结构，因为这是学一个东西能实际运用的基础。
IF语句语法：
IF…END IF为一组

IF search_condition THEN statement_list 
   [ELSEIF search_condition THEN statement_list] ... 
   [ELSE statement_list] 
END IF

search_condition表示条件判断语句；statement_list表示条件成立执行的语句。

CASE语句语法：

CASE...END CASE为一组
CASE case_value 
     WHEN when_value THEN statement_list 
     [WHEN when_value THEN statement_list] ... 
     [ELSE statement_list] 
END CASE

case_value表示条件判断的变量；when_value表示变量的取值；
statement_list表示不同when_value值执行的语句。

loop语句语法：

[begin_label:] LOOP 
statement_list
#LEAVE语句 主要用于跳出整个循环控制
LEAVE begin_label 
END LOOP [end_label]

ITERATE语句语法：
ITERATE语句是跳出本次循环，然后直接进入下一次循环。
ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内

ITERATE label

begin_label和end_label分别表示循环开始和结束的标志，两个标志必须相同，而且都可以省略；
statement_list表示需要循环执行的语句。

while语句语法：

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label]

WHILE循环需要使用END WHILE来结束。
其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；

repeat语句语法：

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label]

简单举个栗子：
判断员工优秀？

#自定义开始结束符
DELIMITER $$
drop function if exists getGreade$$
create function getGreade(grade int)
returns varchar(10)
begin
declare ret varchar(10);
CASE 
WHEN grade=1 THEN SET ret='优秀'; 
ELSE SET ret='非常优秀'; 
END CASE ; 
return ret;
end $$
DELIMITER ;
# 以;结束

单从逻辑而言，作为程序员的你理解不难。以上你了解，但是这种东西实际中会需要很多问题，主要时sql报错需要经验。没有相关经验你基本看不出来，而且百度也没有什么效果，网上学习课程教的又很浅，我学习这部分花了很大的时间去调通各种代码问题。且行且珍惜。

2，存储过程
为什么我写了自定义函数。其实我们可以发现这两者有很多相似之处。他就是一种为了方便快捷的一组具有特定功能的SQL语句集组成的可编程的、经编译创建并保存在数据库的函数。（**注：**不同的数据库，语法上存在的差异）。
上面自定函数都可以用到这里来。

存储过程的语法：

CREATE
    #指定当前用户
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
( #上面的参数细化
proc_parameter:
   [ IN | OUT | INOUT ] param_name type

#上面指定数据库引擎细化
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

#上面主体细化
routine_body:
　　Valid SQL routine statement )
　　
[begin_label:] BEGIN
　　[statement_list]　　　
END [end_label]

IN/OUT/INOUT 字面意思in只做输入不可改变,out只做输出可变,inout两者都可万能。
删除及查看存储过程：

#删除
drop procedure sp_name;
#查看
show procedure status;/create procedure sp_name;

前期数据表准备：

mysql> create table `grade`(
    -> `id` int(11) not null auto_increment primary key,
    -> `stu_number` varchar(11) not null,
    -> `math` int(4) not null,
    -> `chinese` int(4) not null,
    -> `english` int(4) not null
    -> )engine=innodb default charset=utf8 comment='学生成绩表';
Query OK, 0 rows affected (0.76 sec)

mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> create table `student`(
    -> `id` int(11) not null auto_increment primary key,
    -> `stu_number` varchar(11) not null,
    -> `stu_name` varchar(12) not null,
    -> `class` int(4) not null
    -> )engine=innodb default charset=utf8 comment='学生信息表';
Query OK, 0 rows affected (0.15 sec)

mysql> show tables;                                                             +-----------------------------+
| Tables_in_activiti_database |
+-----------------------------+
| grade                       |
| student                     |
+-----------------------------+
2 rows in set (0.00 sec)

// sql+数据预置

#学生信息表
drop table if exists student;
create table `student`(
`id` int(11) not null auto_increment primary key,
`stu_number` varchar(11) not null,
`stu_name` varchar(12) not null,
`class` int(4) not null
)engine=innodb default charset=utf8 comment='学生信息表';

#学生成绩表
drop table if exists grade;
create table `grade`(
`id` int(11) not null auto_increment primary key,
`stu_number` varchar(11) not null,
`math` int(4) not null,
`chinese` int(4) not null,
`english` int(4) not null,
`total` int(5) not null
)engine=innodb default charset=utf8 comment='学生成绩表';

insert into student(stu_number,stu_name,class) values
('10000','张一',101),
('10001','张二',101),
('10002','张三',101),
('10003','张四',101),
('10004','张二',102),
('10005','张三',102),
('10006','张四',102);

insert into grade(stu_number,math,chinese,english,total) values
('10000',80,90,101,271),
('10001',82,70,101,253),
('10002',80,60,101,241),
('10003',85,10,101,196),
('10004',80,88,101,269),
('10005',87,77,101,265),
('10006',83,90,101,274);

1，创建一个存储过程找到前全年级总分最高前三名。

drop procedure if exists getavgthree;
#不带参数
create procedure getavgthree()
begin
select s.stu_name,s.class from student s left join grade g on s.stu_number=g.stu_number order by total desc limit 3;
end;

2，找到学号最大的学生，将学号存储到输出参数。

create procedure getMaxSIDByClass(IN classname int, out maxid varchar(11))
BEGIN
select MAX(stu_number) into maxid from student where class=classname;
END;

当然除了上述查询，存储过程更删改查都是可以的，此外也可以使用存储过程备份还原数据（简单来说就是将一张表的数据复制到另一张表）

摩羯的成长期

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySOL自定义函数及存储过程

先介绍下MySQL自带的一些函数：结构：sql --> 结果 #作用以下都是运行结果:字符串相关函数select concat('zhou','yi'); --> zhouyi #字符串链接select concat_ws('&',zhou'','yi'); --> zhou&yi #指定分割符号进行字符串链接# FOMRAT(N,D,local...
复制链接

扫一扫

专栏目录