KingbaseES 的oracle兼容性参数

KingbaseES用户可通过设置相关的数据库兼容参数，部分或全部启用Oracle兼容特性。

常用的兼容性参数有以下这些：

参数名称	参数说明
ora_forbid_func_polymorphism	是否是否禁用函数多态。 on 表示开启禁用多态，同模式下不能创建同名的函数或存储过程。 off(默认), 则表示启用多态。
ora_input_emptystr_isnull	输入空字符串时的处理措施。 on 表示将输入的空字符串作为null 值处理。 off 表示不处理。
ora_numop_style	是否将 integers 操作符当做 numeric 操作符。
ora_statement_level_rollback	是否启用语句级回滚。
ora_style_nls_date_format	这个变量设置为on 时，日期时间的隐式输入与输出字符串格式才会使用参数nls_date_format、 nls_timestamp_format和nls_timestamp_tz_format 的配置。
ignore_zero_number	当打开时，number 类型输出时末尾忽略“0”。默认为off。
enable_func_colname	启用或禁用函数别名作为列名功能。默认此功能禁用。
enable_upper_colname	开关打开，查询结果的列名将转换为大写。 只在oracle模式，并且大小敏感(enable_ci=off)的时有效

下面通过一些实验来说明这些参数的具体用途。1.ora_forbid_func_polymorphism
函数多态是指在同一个用户模式下，一组具有相同名字，但是参数个数不同或者参数个数相同但参数类型不同的函数。
最常见的多态函数就是to_char函数。
参数只影响设置后
我们用创建加密扩展(kbcrypto)来验证ora_forbid_func_polymorphism 参数的影响

test=# show ora_forbid_func_polymorphism ;
 ora_forbid_func_polymorphism 
------------------------------
 off
(1 行记录)

test=# create extension kbcrypto;
CREATE EXTENSION
test=# drop extension kbcrypto;
DROP EXTENSION
test=# set ora_forbid_func_polymorphism=on;
SET
test=# create extension kbcrypto;
错误:  function or procedure "digest" already exists

在禁用函数多态的情况下，扩展创建失败。 查看创建扩展需要执行的脚本，可以看到脚本会创建2个digest函数(参数类型不同)

[root@singlekbdb extension]# grep digest kbcrypto--1.3.sql
CREATE FUNCTION digest(text, text)
AS 'MODULE_PATHNAME', 'sys_digest'
CREATE FUNCTION digest(bytea, text)
AS 'MODULE_PATHNAME', 'sys_digest'

2.ora_input_emptystr_isnull
将参数设置不同的值，分别往表中插入空字符串'' (两个连续的单引号)和null

test=# create table tnull(c1 int,c2 varchar); 
CREATE TABLE
test=# show ora_input_emptystr_isnull;
 ora_input_emptystr_isnull 
---------------------------
 on
(1 行记录)

test=# insert into tnull values (1,'');
INSERT 0 1
test=# insert into tnull values (2,null);
INSERT 0 1
test=# set ora_input_emptystr_isnull=off;
SET
test=# insert into tnull values (3,'');
INSERT 0 1
test=# insert into tnull values (4,null);
INSERT 0 1

测试在ora_input_emptystr_isnull不同情况下的查询结果

test=# show ora_input_emptystr_isnull;
 ora_input_emptystr_isnull 
---------------------------
 on
(1 行记录)

test=# select * from tnull where c2 is null;
 c1 | c2 
----+----
  1 | 
  2 | 
  4 | 
(3 行记录)

test=# select * from tnull where c2='';         
 c1 | c2 
----+----
(0 行记录)

test=# set ora_input_emptystr_isnull=off;
SET
test=# select * from tnull where c2 is null;
 c1 | c2 
----+----
  1 | 
  2 | 
  4 | 
(3 行记录)

test=#  select * from tnull where c2='';     
 c1 | c2 
----+----
  3 | 
(1 行记录)

结果可以看到对于在ora_input_emptystr_isnull=on时插入的数据(1,2两行)，不管参数值设置为什么，查询结果都是一致的。
对于ora_input_emptystr_isnull=off时插入的数据(3,4两行)，参数值设置的不同对于空字符串('')的查询结果是不同的。

在参数关闭的情况下，数据库存储的空字符串和null值是不一致，通过length函数可以观察到这一结果

test=# select c1,length(c2) from tnull;
 c1 | length 
----+--------
  1 |       
  2 |       
  3 |      0
  4 |       
(4 行记录)

所以在使用时，尽量不修改ora_input_emptystr_isnull参数，可能会影响后续的select访问结果。
再来看一组空字符串插入/转换不同类型的测试结果

test=# show ora_input_emptystr_isnull;
 ora_input_emptystr_isnull 
---------------------------
 on
(1 行记录)

test=# insert into tnull (c1) values ('');
INSERT 0 1
test=# set ora_input_emptystr_isnull=off;
SET
test=# insert into tnull (c1) values ('');       
错误:  无效的类型 integer 输入语法: ""
第1行insert into tnull (c1) values ('');
                                    ^
test=# select ''::integer;                
错误:  无效的类型 integer 输入语法: ""
第1行select ''::integer;
            ^
test=# select ''::date;   
错误:  无效的类型 timestamp 输入语法: ""
第1行select ''::date;
            ^
test=# reset ora_input_emptystr_isnull;
RESET
test=# select ''::integer;             
 int4 
------
     
(1 行记录)

当ora_input_emptystr_isnull=off时，''属于字符串类型，无法转换成其他数据类型。
当ora_input_emptystr_isnull=on时，''被转成null，而null没有类型约束，所以可以转换为任意类型。

3.ora_numop_style

test=# create table t1(name varchar,money varchar);
CREATE TABLE
test=# insert into t1 values ('zs','1000');
INSERT 0 1
test=# insert into t1 values ('ls','156.9');
INSERT 0 1
test=# insert into t1 values ('ww','9999999999999999');
INSERT 0 1
test=# show ora_numop_style;
 ora_numop_style 
-----------------
 off
(1 行记录)
test=# select money * 10  from t1 where name = 'zs';
 ?column? 
----------
    10000
(1 行记录)

test=# select money * 10  from t1 where name = 'ls';
错误:  无效的类型 integer 输入语法: "156.9"
test=# select money * 10  from t1 where name = 'ww';
错误:  值 "9999999999999999" 超出类型 integer 的范围

test=# set ora_numop_style=on;
SET
test=# select money * 10  from t1
test-# ;
     ?column?      
-------------------
             10000
            1569.0
 99999999999999990
(3 行记录)

以字符串类型存储的数值，sql在计算时会根据常量的数据类型将字段类型会和常量值的类型做统一，再做计算。
如果常量类型是整型，可能会导致一些异常。

在计算过程当中如果有多个常量，则根据计算规则，与优先参与计算的常量数据类型做统一。

test=#  select money * 10 * 10.1 from t1;
错误:  值 "9999999999999999" 超出类型 integer 的范围
test=#  select money * 10.1 * 10 from t1;  
       ?column?        
-----------------------
              101000.0
 1009999999999999899.0
              15846.90
(3 行记录)

test=#  select  10 * money  * 10.1 from t1;
错误:  值 "9999999999999999" 超出类型 integer 的范围
test=#  select  10.1 * money  * 10 from t1;  
       ?column?        
-----------------------
              101000.0
 1009999999999999899.0
              15846.90
(3 行记录)

4.ora_statement_level_rollback
参数指定了plsql中是否启用语句级回滚。

test=# create table t1 (id int);
CREATE TABLE
test=# create table t2 (id int);
CREATE TABLE
test=# insert into t1 values (1);
INSERT 0 1
test=# insert into t2 values (2);
INSERT 0 1
test=# \set SQLTERM /
test=# CREATE OR REPLACE PROCEDURE p1() 
test-# AS 
test-# DECLARE 
test-# v_id int ;
test-# BEGIN 
test-#   SELECT id INTO v_id FROM t1 WHERE id = 1;
test-#   UPDATE t1 SET id = 3 WHERE id = v_id;
test-#   call p2();
test-#   COMMIT;
test-# END;
test-# /
CREATE PROCEDURE
test=# CREATE OR REPLACE PROCEDURE p2() 
test-# AS 
test-# DECLARE 
test-# v_id int ;
test-# BEGIN 
test-#   SELECT id INTO v_id FROM t2 WHERE id = 5;
test-#   UPDATE t2 SET id = 1 WHERE id = v_id; 
test-#   EXCEPTION
test-#     WHEN no_data_found THEN
test-#         RAISE NOTICE 'no_data_found';
test-# END;
test-# /
CREATE PROCEDURE

准备表和存储过程，其中p1更新t1表，并且调用p2。 p2执行中会因为无法查询到数据报错。

test=# show  ora_statement_level_rollback;
 ora_statement_level_rollback 
------------------------------
 off
(1 行记录)

test=# call p1();
注意:  no_data_found
CALL
test=# select * from t1;
 id 
----
  1
(1 行记录)

test=# set ora_statement_level_rollback = on;
SET
test=# call p1();
注意:  no_data_found
CALL
test=# select * from t1;                     
 id 
----
  3
(1 行记录)

在ora_statement_level_rollback=off的情况下，系统执行了全局回滚，所以t1表的记录并没有被更新为3.
在ora_statement_level_rollback=on的情况下，系统执行了语句级回滚，只回滚了p2的错误，所以t1表的记录被更新为3.

如果存储过程中的异常不做处理，即使ora_statement_level_rollback=on一样会进行全局回滚。

恢复t1表的数据
test=# update t1 set id = 1;
UPDATE 1
test=# select * from t1;
 id 
----
  1
(1 行记录)

修改p2，去掉异常捕获部分
test=# \set SQLTERM /
test=# CREATE OR REPLACE PROCEDURE p2() 
test-# AS 
test-# DECLARE 
test-# v_id int ;
test-# BEGIN 
test-#   SELECT id INTO v_id FROM t2 WHERE id = 5;
test-#   UPDATE t2 SET id = 1 WHERE id = v_id; 
test-# END;
test-# /
CREATE PROCEDURE

test=# show ora_statement_level_rollback;
 ora_statement_level_rollback 
------------------------------
 on
(1 行记录)
test=# call p1();
错误:  查询没有返回记录
背景:  PL/SQL函数p2()的第5行的SQL语句
SQL 语句 "CALL p2()"
PL/SQL函数p1()的第7行的CALL
test=# select * from t1;
 id 
----
  1
(1 行记录)

5.ora_style_nls_date_format
参数ora_style_nls_date_format是R6版本时间格式化参数的开关，打开后时间数据会根据nls_date_format、 nls_timestamp_format和nls_timestamp_tz_format等参数设置的格式进行格式化。
oracle的sqlldr可以通过控制文件设置需要导入的时间格式，kes可以通过这些格式化参数的设置来辅助copy和sys_bulkload命令工具导入特定的时间数据。

[kbc5@singlekbdb ~]$ cat t3.csv
20220814164714
test=# \d t3
           数据表 "public.t3"
 栏位 |      类型       | 校对规则 | 可空的 | 预设 
------+-----------------------------+----------+--------+------
 ct  | timestamp without time zone |     |    | 

直接使用copy会报错：
test=# COPY t3 FROM '/home/kbc5/t3.csv';
错误: 日期/时间值超出范围: "20220814164714"
提示: 也许你需要不同的 "datesytle" 设置.
背景: COPY t3, 行 1, 列 ct: "20220814164714"

需要将ORA_STYLE_NLS_DATE_FORMAT设置为on，按照数据情况和表的数据类型设置时间格式化参数NLS_TIMESTAMP_FORMAT 或者 NLS_DATE_FORMAT ，设置好后就可以导入数据了。
本例子数据到时分秒为止，并且表列的数据类型是timestamp，所以设置了set NLS_TIMESTAMP_FORMAT='YYYYMMDDHH24MISS';
test=# set ORA_STYLE_NLS_DATE_FORMAT=on; 
SET
test=# set NLS_TIMESTAMP_FORMAT='YYYYMMDDHH24MISS';
SET
test=# COPY t3 FROM '/home/kbc5/t3.csv';      
COPY 1