新建函数

概述

函数是在数据库内定义的子程序,可以从内置 SQL 语句中调用函数。如果自带的函数不能满足业务需求,则可以通过 ODC 新建函数,添加自定义函数,以实现某些功能运算和完成各种特定操作,且自定义函数可以减少重复编码,提高程序可读性。

函数是一种与存储过程十分相似的过程式数据库对象。与存储过程一样,都是由 SQL 语句和过程式语句组成的代码片段,并且可以被应用程序和其他 SQL 语句调用。

自定义函数与存储过程的区别:

  • 函数有且仅有一个返回,适合做处理数据后返回一个结果;存储过程可有 0 个或多个返回,适合做批量插入、批量更新。

  • 可以直接通过 SELECT 语句调用函数,而存储过程的调用需要使用 CALL 语句。

创建函数1

如上图所示,创建函数包含以下 6 个步骤:

1. 输入函数名称。

2. 选择函数返回值的数据类型。

3. 添加参数。

4. 确定新建函数信息。

5. 编辑函数信息。

6. 完成新建函数。

操作步骤

以在 ODC 中创建员工函数(function_emp)为例,函数 function_emp 中包含 id INT 类型的参数,根据 id 获取员工姓名。具体操作步骤如下:

步骤 1:输入函数名称

在 OceanBase 开发者中心(OceanBase Developer Center,ODC)单击连接名进入连接后,在左导航栏中单击 函数 标签可以查看函数列表。在函数列表的右上角,单击 + 创建函数,或是在 ODC 上方导航栏中单击 新建 以创建所需对象。

创建函数2

步骤 2:选择函数返回值的数据类型

具体数据类型,请参考 OceanBase 数据库的开发指南

步骤 3:添加参数

  1. 参数即调用函数时传入的信息,需指定参数信息:

    • Oracle 模式:需指定参数的 名称模式类型 和 默认值

    • MySQL 模式: 需指定参数的 名称类型 和 长度

  2. 添加函数提供三种功能操作:

    功能项

    说明

    弹窗引导辅助菜单

    通过弹窗引导辅助菜单,可添加、删除、上下移动参数。

    单击表序号

    • 单击表序号,选中整行,显示辅助菜单(删除、上/下移动)。

    • 单击并选中表序号,可拖动整行参数以调整顺序。

    右键操作

    • 拖动鼠标选中整行,右键单击鼠标,进行复制行 / 向下移动一行。

    • 选择单元格,右键单击鼠标,进行复制。

  3. 参数 中需指定的 模式 指参数类型。

    MySQL 模式下不支持设定参数模式,Oracle 模式下支持三种参数模式 IN(输入)、OUT(输出)、INOUT(输入输出):

    参数

    类型

    IN

    输入参数,调用函数时将参数的值传入函数供执行函数时使用。

    OUT

    输出参数,调用函数时,函数会忽略输出参数本身的值并传入一个空值,在函数体中可以修改输出参数并将修改结果返回给输出参数所代表的实参。

    INOUT

    输入输出参数,即同时具备输入参数和输出参数的功能。

    说明

    Oracle 模式下函数和存储过程均包含 IN/OUT/INOUT 参数;MySQL 模式下只包含 IN 参数,而存储过程包含 IN/OUT/INOUT 参数。

步骤 4:确认创建函数

单击 确定,进入 SQL 创建确认页面。

步骤 5:编辑函数语句

创建函数3

在 SQL 创建确认页面 ,编辑函数语句。

同时,在编辑页面的工具栏中提供了以下功能键:

功能

说明

格式化

单击该按钮对选中的 SQL 语句或当前 SQL 窗口中的所有 SQL 语句进行统一的格式化(缩进、换行和高亮关键字等操作)。

查找/替换

在查找框内输入内容以对脚本进行检索,查找后可在替换框内输入内容以对查找内容进行替换。

撤销

将脚本回退到上一步操作执行的结果。

重做

执行 撤销 操作后,对脚本重新执行撤销前的操作。

大小写

提供 全部大写全部小写 和 首字母大写 三种效果,为脚本中选中的语句转换为对应形式。

缩进

提供 添加缩进 和 删除缩进 两种效果,为脚本中选中的语句添加或删除包含的缩进。

注释

提供 添加注释 和 删除注释 两种效果,将脚本中选中的语句转换为注释或转换为 SQL 语句。

IN 值转化

可以将如 A B 的格式转化为 ('A','B') 的格式。

用户可以在 SQL 创建确认页面编辑创建的函数语句。语法格式如下:

CREATE FUNCTION < 函数名 > ([ <参数1> <类型1> [ , <参数2> <类型2>] ] …) 
RETURNS < 类型 > 
< 函数主体 >

参数说明:

参数

说明

<函数名>

指定自定义函数的名称。

重要

自定义函数不能与存储过程具有相同的名称。

<参数><类型>

用于指定自定义函数的参数。

该参数只有名称和类型,不能指定关键字 IN、OUT 和 INOUT。

RETURNS<类型>

用于声明自定义函数返回值的数据类型。其中,<类型> 用于指定返回值的数据类型。

重要

在 RETURN VALUE 语句中包含 SELECT 语句时,SELECT 语句的返回结果只能是一行且只能有一列值。

<函数主体>

自定义函数的主体部分,亦称函数体。

函数体必须包含一个 RETURN<值> 语句,其中<值>用于指定自定义函数的返回值。

示例:

CREATE FUNCTION `function_emp` ( `id` int(45)) RETURNS VARCHAR(300) 

-- 函数体开始
BEGIN

-- 申明变量
DECLARE
  a VARCHAR(300);

-- 变量赋值
SELECT
  name INTO a
FROM
  employee
WHERE
  emp_no = id;

-- 返回值
RETURN a;

-- 函数体结束

END

步骤 6:完成新建函数

单击 创建,完成新建函数后,可以如同调用系统内置函数一样,使用关键字 SELECT 调用用户自定义的函数。

说明

在左侧导航栏中,右键鼠标单击函数列表中的函数名,通过弹出的管理操作列表(包括 查看新建编辑编译调试运行下载删除 和 刷新),可快速管理和操作目标对象。

函数具体操作,请参见 管理函数。

语法格式:

SELECT <函数名> ([<参数> [,...]])

示例:

SELECT function_emp(2);
  • MySQL 模式

    创建函数4
  • Oracle 模式

    新建函数
    重要

    • Oracle 模式下如果包含 OUT 参数,则需通过 CALL 方式调用。

    • 函数列表中如果出现黄色 告警图标,表示目前有错误或者警告。

相关信息

阿里云首页 云数据库 OceanBase 相关技术圈