人大金仓KingbaseES数据库管理系统- oracle模式下mod用法

关键字：人大金仓、KingbaseES、mod

简介

Mod，数学运算符，指取模运算。

语法

1）作为函数

1. 作为操作符

n2 MOD n1

功能：MOD 返回 n2 除以 n1 的余数。在默认情况下：n1 必须不为0，且n1和n2必须为数值类型。n1 如果和的乘积 n2 为负，则此函数/操作符的行为与经典数学模函数不同。经典模数可以使用 MOD 具有以下公 式的函数表示：

n2 - n1 * FLOOR(n2/n1)

Mod作为操作符时，在oracle模式下与%操作符具有相同功能。

注：在oracle模式下，设置超参数ora_numop_style可以对输入特殊值进行处理

1. n1 = 0时，返回n2;
2. n1 = null时，返回null;
3. 当n1和n2其中一个为整数，另一个为可以隐式转换为数值数据类型的数据时，输出为取余的实际结果。

使用示例

示例1：SQL, PL/SQL兼容性

测试语句：

SQL中：

select 5 MOD 3;

select null MOD null;

PL/SQL中：

Drop table t1;

create table t1 (a int);

insert into t1 values(1);

insert into t1 values(2);

\set SQLTERM @

CREATE OR REPLACE FUNCTION create_function_parameter_in()

RETURNS VARCHAR

AS

DECLARE

S VARCHAR(100);

F VARCHAR(100);

X INT;

BEGIN

S:='SUCCESS';

F:='FAIL';

SELECT a INTO X FROM t1 where a = 1;

IF (x MOD 2) = 1 THEN

RETURN S;

ELSE

RETURN F;

END IF;

END

@

\set SQLTERM ;

Select create_function_parameter_in();

测试结果：

示例2：数值类型

表结构构造:

drop table t1;

create table t1(id int, num number, value int, name varchar(20), dat date);

insert into t1 select rownum id, rownum+0.5 num, rownum+3 value, 'row'||rownum name, '2022-10-'||rownum from dual connect by level <=7;

测试语句：

select id MOD value from t1;

select id MOD num from t1;

select id MOD name from t1;

select id MOD dat from t1;

select id MOD null from t1;

select num MOD (num+1.1) from t1;

select num MOD id from t1;

select num MOD name from t1;

select num MOD dat from t1;

select num MOD null from t1;

select 5.001 MOD 0;

select 5.001 MOD '10';

测试结果：

PL/SQL中：

测试语句：

Drop table t1;

create table t1 (a int);

insert into t1 values(1);

insert into t1 values(2);

\set SQLTERM @

CREATE OR REPLACE FUNCTION create_function_parameter_in()

RETURNS VARCHAR

AS

DECLARE

S VARCHAR(100);

F VARCHAR(100);

X INT;

BEGIN

S:='SUCCESS';

F:='FAIL';

SELECT a INTO X FROM t1 where a = 1;

IF (x MOD 2) = 1 THEN

RETURN S;

ELSE

RETURN F;

END IF;

END

@

\set SQLTERM ;

Select create_function_parameter_in();

测试结果：

测试结论：

Kingbase ES V8R6 oracle模式中，MOD操作符

1. 支持数值 MOD 数值、数值MOD null；

2）不支持数值MOD日期；

3）支持数值 MOD可以隐式转换为数值数据类型的非数值数据类型。

4）PL/SQL中 expr mod 0 形式创建函数不会报错，调用时才会报错。

Bug：第二个参数为0报错。

示例3：日期MOD各种类型

表结构构造:

drop table t1;

create table t1(id int, num numeric(10,2), name varchar(20), value int,dat date);

insert into t1 values(1,1.5,'row1',5,'2022-10-01'),(2,2.5,'row2',5,'2022-10-02'),(3,3.5,'row3',5,'2022-10-03'),(4,4.5,'row4',5,'2022-10-04'),(5,5.5,'row5',5,'2022-10-05'),(6,6.5,'row6',5,'2022-10-06'),(7,7.5,'row7',5,'2022-10-07');

测试语句：

select dat1 MOD dat2 from t1;

select dat1 MOD id from t1;

select dat1 MOD num from t1;

select dat1 MOD name from t1;

select dat1 MOD null from t1;

测试结果：

测试结论：

Kingbase ES V8R6 oracle模式中，MOD操作符

1）不支持日期类型 MOD 各种类型，与oracle行为一致

示例4：字符串 MOD 各种类型

表结构构造:

drop table t1;

create table t1(id int, num numeric(10,2), name1 varchar(20), name2 varchar(20), dat date);

insert into t1 values(1,1.5, 'a','row1','2022-10-01'),(2,2.5, 'b','row2','2022-10-02'),(3,3.5, 'c','row3','2022-10-03'),(4,4.5, 'd','row4','2022-10-04'),(5,5.5, 'e','row5','2022-10-05'),(6,6.5, 'f','row6', '2022-10-06'),(7,7.5, 'g','row7','2022-10-07');

测试语句：

select name1 MOD name2 from t1;

select name2 MOD id from t1;

select name1 MOD num from t1;

select name1 MOD dat from t1;

select name2 MOD null from t1;

select ‘5’ MOD num from t1;

select '5' MOD null;

select '10' MOD '2';

测试结果：

测试结论：

Kingbase ES V8R6 oracle模式中，MOD操作符

1. 仅支持 可以隐式转换为数值数据类型的字符串类型 MOD 数值方式。

Bug：不支持可以隐式转换为数值数据类型的字符串类型 mod 可以隐式转换为数值数据类型的字符串类型。

示例5：null

测试语句：

select null MOD null;

select null MOD 5;

select null MOD 5.01;

select null MOD 'abc';

select null MOD '5';

select null MOD '2020-12-01';

select null MOD 0;

测试结果：

测试结论：

Kingbase ES V8R6 oracle 模式中，MOD操作符

1. 支持null MOD 数值类型，不支持null MOD 除数值类型的其他类型，与oracle行为一致。

示例6：备份与还原

操作步骤

1、初始化数据库后，启动数据库服务

./initdb -D data_old -A trust

2、连接数据库，执行如下的语句

create table t1 ( a int);

insert into t1 values(1);

insert into t1 values(2);

\set SQLTERM @

CREATE OR REPLACE FUNCTION create_function_parameter_in()

RETURNS VARCHAR

AS

DECLARE

S VARCHAR(100);

F VARCHAR(100);

X INT;

BEGIN

S:='SUCCESS';

F:='FAIL';

SELECT a INTO X FROM t1 where a = 1;

IF (x MOD 2) = 1 THEN

RETURN S;

ELSE

RETURN F;

END IF;

END

@

\set SQLTERM ;

3、进行备份

./pg_dump -d test -F c -f test_dump513.SQL

4、创建一个新数据库

create database t1；

5、进行还原

./pg_restore –d t1 test_dump513.SQL

./PL/SQL –d test –f test_dump513.SQL

测试结果：

测试结论：

1）Kingbase ES V8R6添加MOD操作符后，能够备份和还原出数据库。

2）备份出的SQL语句格式为MOD操作符形式

示例7：索引

表结构构造:

drop table t1;

create table t1(id int, num numeric(10,2), name varchar(20), dat1 date, dat2 date);

insert into t1 values(1,1.5,'row1','2022-10-01', '2022-10-01'),(2,2.5,'row2','2023-10-02', '2023-10-02'),(3,3.5,'row3','2022-10-03', '2023-10-03'),(4,4.5,'row4','2022-10-04', '2023-10-04'),(5,5.5,'row5','2022-10-05', '2023-10-05');

测试语句：

CREATE INDEX test1_idx ON t1 (id mod num);

CREATE INDEX test1_idx ON t1 (id % num);

CREATE INDEX test1_idx ON t1 (mod(id,num));

DROP INDEX test1_idx;

测试结果：

测试结论：

1）Kingbase ES V8R6添加MOD操作符后，表达式索引仅支持mod函数不支持mod和%操作符 。

示例8：视图内部显示

表结构构造:

drop table t1;

create table t1(id int, num numeric(10,2), name varchar(20), dat1 date, dat2 date);

insert into t1 values(1,1.5,'row1','2022-10-01', '2022-10-01'),(2,2.5,'row2','2023-10-02', '2023-10-02'),(3,3.5,'row3','2022-10-03', '2023-10-03'),(4,4.5,'row4','2022-10-04', '2023-10-04'),(5,5.5,'row5','2022-10-05', '2023-10-05');

测试语句：

create view v1 as select id mod 5 from t1;

\d+ v1;

测试结果：

测试结论：

1）Kingbase ES V8R6添加MOD操作符后，定义视图后查看视图定义后显示成%操作符形式。

示例9：存储过程显示

测试语句：

Create databse test1;

\c test1

\set SQLTERM /

CREATE OR REPLACE PROCEDURE increment2(IN i INTEGER, OUT result INTEGER)

AS

BEGIN

result := i mod 2;

END;

/

\set SQLTERM ;

测试结果：

测试结论：

1. Kingbase ES V8R6添加MOD操作符后，存储过程中备份后显示成mod操作符形式。

对比总结

比较项

Kingbase ES V8R6

Oracle 21c

MySQL 8.0

DM8

操作符特征

MOD操作符适用场景

SQL

支持

不支持

支持

支持

PL/SQL

支持

优先级和结合性

支持

比‘-‘高，与‘*’同级，左结合

比‘-‘高，与‘*’同级，左结合

比‘-‘高，与‘*’同级，左结合

关键字性质

非保留关键字

非保留关键字

保留关键字

非保留关键字

相似功能

MOD操作符

SQL

支持

不支持

支持

支持

PL/SQL

支持

MOD函数

支持

支持

% 操作符

支持

不支持

参数类型

第一个参数

第二个参数

\

sql

plsql

\

\

数值

整型

数值

整数

整数

不支持

整数

整数

整数

浮点型

取决于余数

取决于余数

浮点型

取决于余数

字符串

可隐式转换为数值类型

取决于余数

取决于余数

取决于转换后的数值类型

取决于余数

不可隐式转换为数值类型

不支持

不支持

返回null(有警告)

不支持

日期

不支持

不支持

取决于余数

不支持

0

返回第一个参数

返回第一个参数

返回null(有警告)

返回第一个参数

null

返回null

返回null

返回null

返回null

浮点型

数值

整数

取决于余数

取决于余数

取决于余数

取决于余数

浮点型

取决于余数

取决于余数

取决于余数

取决于余数

字符串

可隐式转换为数值类型

取决于余数

取决于余数

取决于余数

取决于余数

不可隐式转换为数值类型

不支持

不支持

返回null(有警告)

不支持

日期

不支持

不支持

取决于余数

不支持

0

返回第一个参数

返回第一个参数

返回null(有警告)

返回第一个参数

null

返回null

返回null

返回null

返回null

字符串

可隐式转换为数值类型

数值

整数

取决于余数

不支持

取决于余数

整数

取决于余数

浮点型

取决于余数

取决于余数

取决于余数

取决于余数

字符串

可隐式转换为数值类型

取决于余数

取决于余数

取决于余数

取决于余数

不可隐式转换为数值类型

不支持

不支持

返回null(有警告)

不支持

日期

不支持

不支持

取决于余数

不支持

0

返回第一个参数

返回第一个参数

返回null(有警告)

返回转换为数值的类型

Null

返回null

返回null

返回null

返回null

不可隐式转换为数值类型

数值

整数

不支持

不支持

0(有警告)

不支持

浮点型

0(有警告)

字符串

可隐式转换为数值类型

0(有警告)

不可隐式转换为数值类型

0(有警告)

日期

0(有警告)

0

返回null

Null

返回null(有警告)

日期

数值

整数

不支持

不支持

不支持

整数

不支持

浮点型

浮点型

字符串

可隐式转换为数值类型

取决于余数

不可隐式转换为数值类型

返回null，有警告

日期

整数

0

返回null，有警告

null

返回null，有警告

null

数值

整数

返回null

不支持

返回null

返回null

返回null

浮点型

返回null

返回null

返回null

字符串

可隐式转换为数值类型

返回null

返回null

返回null

返回null

不可隐式转换为数值类型

不支持

不支持

返回null(有警告)

不支持

日期

不支持

不支持

返回null(有警告)

不支持

0

返回null

返回null

返回null

返回null

null

返回null

返回null

返回null

返回null