数据库版本:KingbaseES V008R006C008B0014
简介
函数是一种在数据库管理系统中执行特定任务的程序段或子程序,它们可以接受输入参数并返回结果,主要用于各种数据处理、计算、验证、转换等。本篇文章以kingbase为例介绍。
文章目录如下
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;