【基础】自定义函数

谁践踏了优雅 2022-07-17 00:14 186阅读 0赞

# 前言 #

Github：[https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql][https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]

# 概述 #

MySql 5.0 开始支持函数，函数是存在数据库中的一段 sql 集合，调用函数可以减少很多工作量，

减少数据在数据库和应用服务器上的传输，对于提高数据处理的效率。参数类型为 in 类型，函数必须有返回值，

与 oracle 等其他库函数参数类型有区别，如果做数据迁移，或许需要将函数改变成存储过程，

因为 MySql 的存储过程参数包括 in，out，inout 三种模式。

## **1、创建函数语法** ##

**CREATE FUNCTION** fn\_name(func\_parameter\[,...\])  
**RETURNS** type  
\[characteristic...\]  
routine\_body  
  
**参数代表含义：**

**func\_parameter：**param\_name type

**type:** 任何mysql支持的类型

**characteristic：**

LANGUAGE SQL | \[NOT\]DETERMINISTIC  
| \{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA\}  
| SQL SECURITY \{DEFINER | INVOKER\}  
| COMMENT 'String'

**routine\_body：**函数体

## **2、更改函数语法** ##

**ALTER FUNCTION** fn\_name \[characteristic...\]  
  
**characteristic：**

\{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA\}  
| SQL SECURITY \{DEFINER | INVOKER\}  
| COMMENT 'String'

# 一 实例准备条件 #

## 1、创建表 ##

CREATE TABLE `t_user_main` (
      `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',
      `f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',
      `f_age` int(3) DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`f_userId`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

## 2、插入数据 ##

INSERT INTO t_user_main (f_userName, f_age) 
    VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29);

# 二 实例分析函数 #

#创建一个函数
    DELIMITER $$ -- 定界符
    -- 开始创建函数
    CREATE FUNCTION user_main_fn(v_id INT)
    RETURNS VARCHAR(50)
    BEGIN
      -- 定义变量
      DECLARE v_userName VARCHAR(50);
      -- 给定义的变量赋值
      SELECT f_userName INTO v_userName FROM t_user_main 
      WHERE f_userId = v_id;
      -- 返回函数处理结果
      RETURN v_userName;
    END $$ -- 函数创建定界符
    DELIMITER;

## **1、sql 中使用函数** ##

SELECT user\_main\_fn(1) FROM DUAL;

## **2、MySql 中函数创建特别注意的两点** ##

1）需要定义定界符，否则是创建不了函数的，因为 MySql 见到'分号'就认为执行结束了，只有开始

创建时定义分界符，结束时在配对一个分界符，MySql 认为这个时候才结束，使得函数能够完整编译创建。

2）MySql 创建函数是没有 or replace 这个概念的，这个地方与创建视图不同。

在函数中，运行包含 DDL 语句，允许提交或回滚，函数中可以调用其他函数或存储过程。

#创建第二个函数，使用第一个函数
    DELIMITER $$
    CREATE FUNCTION user_main_fn2(v_id INT)
    RETURNS VARCHAR(100)
    BEGIN 
      #定义变量
    	DECLARE v_userName VARCHAR(50);
      DECLARE  v_userNameNew VARCHAR(50);
      #通过into赋值
      SELECT f_userName INTO v_userName FROM t_user_main WHERE f_userId = v_id;
      #使用函数
      SELECT user_main_fn(v_id) INTO v_userNameNew FROM DUAL;
      #返回函数处理结果
      RETURN CONCAT(v_userName,'***',v_userNameNew);
    END $$
    DELIMITER;

## **3、查询新建函数** ##

SELECT user\_main\_fn2(1);

## **4、查询结果** ##

![Center][]

# 三 函数中变量的使用 #

MySql 中变量从 5.1 后不区分大小写。

## 1、变量的定义 ##

通过 DECLARE 可以定义一个局部变量，变量的作用范围 BEGIN...END 块中；

变量语句必须卸载复合语句开头，并且在其他语句的前面;

一次性可以声明多个变量;

## **2、变量定义语法** ##

DECLARE var\_name\[,...\] type \[DEFAULT value\]

## **3、在函数中定义变量的用法** ##

DELIMITER $$
    CREATE FUNCTION user_main_fn2(v_id INT)
    RETURNS VARCHAR(100)
    BEGIN
      #定义变量
        DECLARE v_userName VARCHAR(50);
      DECLARE  v_userNameNew VARCHAR(50);
      #定义变量,可以一次性定义多个
        #DECLARE v_userName,v_userNameNew VARCHAR(50);
      DECLARE v_testSet VARCHAR(50);
      SET v_testSet = 'testSet';
      #通过into赋值
      SELECT f_userName INTO v_userName FROM t_user_main WHERE f_userId = v_id;
      #使用函数
      SELECT user_main_fn(v_id) INTO v_userNameNew FROM DUAL;
      #返回函数处理结果
      RETURN CONCAT(v_userName,'***',v_userNameNew,v_testSet);
    END $$
    DELIMITER;

我们通过 DECLARE 定义一个 v\_userName 变量，变量类型为 varchar，长度为 50;

对于变量定义，对于同类型的变量，可以分开声明，也可以一次声明;

**变量赋值：**变量可以通过直接赋值，也可以通过查询语句赋值。

**直接赋值语法：**SET var\_name = expr\[,var\_name=expr\]...  
  
在上面函数中，定义一个 v\_testSet 变量，通过 set 直接赋值，eg:

DECLARE v\_testSet VARCHAR(50);

SET v\_testSet = 'testSet';

通过 select...INTO...赋值，通过这种方式赋值，要求查询返回只有一行结果。

**使用语法：**

SELECT col\_name\[,...\] INTO var\_name\[,...\] table\_expr;

eg:

SELECT f\_userName INTO v\_userName FROM t\_user\_main WHERE f\_userId = v\_id;

# 四 查看函数状态或定义语句 #

## 1、查看函数状态语法 ##

SHOW FUNCTION STATUS \[LIKE 'pattern'\]

## 2、查看函数的定义语法 ##

SHOW CREATE FUNCTION fn\_name;

eg：

![Center 1][]

# 五 函数删除 #

DROP FUNCTION  \[IF EXISTS\]  fn\_name;

[https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]: https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql
[Center]: /images/20220717/66d55d12da814968b541ea8867cb5702.png
[Center 1]: /images/20220717/5fc8f0f545f345f8adfca1c5f0ef8742.png