KingbaseES数据库函数的基本用法

---

数据库版本：KingbaseES V008R006C008B0014

---

简介

    函数是一种在数据库管理系统中执行特定任务的程序段或子程序，它们可以接受输入参数并返回结果，主要用于各种数据处理、计算、验证、转换等。本篇文章以kingbase为例介绍。

        

文章目录如下

1. 函数的基本用法

1.1. 语法说明

1.2. 基础用法

2. 函数的返回类型

2.1. 基本数据类型

2.2. 复合数据类型

3. 函数的参数传递

3.1. 位置参数

3.2. 关键字参数

3.3. 默认参数

4. 函数的内部变量

---

        

1. 函数的基本用法

1.1. 语法说明

函数的基本语法如下：

CREATE [OR REPLACE] FUNCTION 函数名(
    参数1 数据类型,
    参数2 数据类型
    ...
) RETURNS 返回数据类型
AS $$
DECLARE
    -- 设置局部变量和声明(可选)
BEGIN
    -- 函数体
    RETURN 返回值;
END;
$$
LANGUAGE 语言;

• CREATE [ OR REPLACE ] FUNCTION：用于创建一个新的函数。OR REPLACE（可选）表示如果函数已存在，则替换原有函数。
• 函数名：自定义的名称。可以包含字母、数字和下划线。
• 参数（可选）：函数的参数列表。一般用来传递要操作的数据。
• RETURNS：返回数据类型。
• AS：表示函数体的开始，可以在AS后面指定结束符。
• DECLARE（可选）：用于声明本地变量。
• BEGIN：函数体的开始。
• RETURN 返回值：返回语句，指定函数的返回值。
• END：函数体的结束。
• $$：在AS后面定义的结束符，那么在创建完语句后也需要使用 $$ 结束（注意：这个符号不是固定的，也可以是 ## 或 @ 等。
• LANGUAGE 语言：指定使用的语言，包括 sql、plsql、plpgsql 

        

1.2. 基础用法

最基础的创建函数示例

CREATE FUNCTION f1()  --创建函数
RETURNS int           --指定返回类型
AS $$                 --执行结尾符
BEGIN                 --开始
    return 1;         --函数体的语句
END; $$               --结束
LANGUAGE plsql;       --指定语言

        

调用函数方法

SELECT f1();  --括号表示需要传入的参数，创建时没有参数可以为空

函数的主要用法xxx ，根据实际情况构造即可

        

 1.3. 声明变量

一般情形，函数会使用大量变量来存储数据，而这些变量需要在使用前声明它的类型。通过关键字 DECLARE 来定义（可选），例如：

CREATE FUNCTION 函数名()
RETURNS int
AS $$
DECLARE
    num int;    -- 定义变量类型
BEGIN
    num = 1+1;  -- 使用变量(将计算结果存储到变量中)
    RETURN num; -- 返回这个变量的数据
END;
$$ LANGUAGE plsql;

        

大部分情况下，一般采用 SELECT 的方式查询某张表的某条数据，将其存储到变量中，再进行其他操作。例如：需要计算某个商品的价格和数量时：

这里举个例子，id为1的商品价格100，数量为2

写一个用于简单计算的函数

CREATE FUNCTION f1(p_id int)  -- 传入一个需要计算的id
RETURNS int
AS $$
DECLARE
    p_price int;  -- 定义用于存储价格的变量
    p_count int;  -- 定义用于存储数量的变量
    p_result int; -- 定义用于存储计算结果的变量
BEGIN
    -- 将查询的结果插入变量中
    SELECT
        price, count INTO p_price, p_count  --使用into插入到变量
    FROM
        t1
    WHERE 
        id = p_id;
 
    -- 计算结果
    p_result := p_price * p_count;
 
    -- 返回结果
    RETURN p_result;
END;
$$ LANGUAGE plsql;

计算商品ID为1的总金额

        

2. 函数的参数传递

我们一般将常用的功能封装成函数，而参数的作用就是定制具体的行为，可以减少代码的重复编写，提高代码的可读性和维护性。

举个例子，函数的功能为创建表：

CREATE FUNCTION create_table(
    table_name text  --定义一个传入表名的参数
) RETURNS void
AS $$
DECLARE
    sql text;
BEGIN
    /*拼接CREATE语句，执行*/
    sql := 'CREATE TABLE IF NOT EXISTS ' || table_name || ' (id int);' ;
    EXECUTE sql;
    RAISE NOTICE '创建表: %', table_name;
END; $$
LANGUAGE plsql;

直接调用函数

        

2.1. 位置参数

位置参数指的是函数的参数列表中，参数的顺序和位置对函数调用。比如：

CREATE FUNCTION f1(int, int)  -- 只定义2个参数类型
RETURNS int
AS $$
BEGIN
    -- 返回2个函数的加法
    RETURN $1 + $2;
END;
$$ LANGUAGE plsql;

创建函数时只需要写入类型，不用写名称，使用$n来按顺序调用参数。 

        

2.2. 关键字参数

关键字参数与位置参数不同的是：使用参数名称调用

CREATE FUNCTION f1(
    n1 int,  -- 根据名字定义参数名
    n2 int
)RETURNS int
AS $$
BEGIN
    -- 返回2个函数的加法
    RETURN n1 + n2;
END;
$$ LANGUAGE plsql;

        

2.3. 默认参数

在创建函数时，除了定义参数的类型外，同时支持参数默认值

参数名 参数类型 := 默认值

CREATE FUNCTION f1(
    n1 int := 1,  -- 定义默认值
    n2 int := 1   -- 定义默认值
)RETURNS int
AS $$
BEGIN
    -- 返回2个函数的加法
    RETURN n1 + n2;
END;
$$ LANGUAGE plsql;

        

 3. 函数的返回类型

返回值的作用在于提供一种动态生成数据或执行特定逻辑的方式，并将结果返回给调用者。语法在 create 之后，as 之前

CREATE FUNCTION 函数名()
RETURNS 数据类型
AS $$
......

        

举个例子：计算商品折扣价格，计算后返回一个数值类型

CREATE FUNCTION calculate_discount_price(
    original_price NUMERIC,  --原价
    discount_code VARCHAR    --折扣
)RETURNS NUMERIC
AS $$
DECLARE
    discount_amount NUMERIC; --用于存储折扣信息
    final_price NUMERIC;	 --用于存储计算后的价格
BEGIN
    -- 通过某张表查询折扣
    SELECT discount INTO discount_amount
    FROM discounts
    WHERE code = discount_code;
 
    -- 计算折扣后的价格
    final_price := original_price - (original_price * discount_amount);
    
    -- 返回折扣后的价格
    RETURN final_price;
END; $$
LANGUAGE plsql;

        

以下介绍一些基础的返回类型用法：

3.1. 基本数据类型

• 基本数据类型包括：整数（int）、浮点数（decimal）、布尔值（boolean）等。

【案例一】返回整数类型

CREATE FUNCTION 函数名() RETURNS int  --返回整数
AS $$
BEGIN
    return 1+1;
END;
$$ LANGUAGE plsql;

        

 【案例二】返回浮点数类型

CREATE FUNCTION 函数名() RETURNS decimal(10,2)  --返回浮点数
AS $$
BEGIN
    return 10 / 3;
END;
$$ LANGUAGE plsql;

        

  【案例三】返回布尔类型

CREATE FUNCTION 函数名() RETURNS boolean  --返回布尔值
AS $$
BEGIN
    return true;
END;
$$ LANGUAGE plsql;

        

3.2. 复合数据类型

• 复合数据类型一般用来返回记录、数组等。

【案例一】返回查询结果（仅支持1行数据）

CREATE FUNCTION 函数名()
RETURNS RECORD
AS $$
DECLARE
    --定义一个保存结果的变量
    变量名 RECORD;
BEGIN
    --查询员工信息，将结果保存到变量emp_details中
    SELECT count(*) INTO 变量名 FROM 表名;
    --返回结果
    RETURN 变量名;
END;
$$ LANGUAGE plsql;

         

【案例二】返回explain结果集

CREATE FUNCTION f1()
RETURNS setof text    --返回一个由text类型值组成的集合
AS $$
BEGIN
    --返回结果集
    RETURN QUERY explain语句;
END;
$$ LANGUAGE plsql;

        

【案例三】返回数组类型

CREATE FUNCTION f1()
RETURNS TEXT[]
AS $$
DECLARE
    arr TEXT[];
BEGIN
    -- 返回数组
    arr := ARRAY['Java', 'SQL', 'Python'];
    RETURN arr;
END;
$$ LANGUAGE plsql;

        

【案例四】返回空

CREATE FUNCTION f1()
RETURNS void    --返回空
AS $$
BEGIN
    --执行插入语句
    INSERT INTO t1 VALUES(1);
END;
$$ LANGUAGE plsql;