lightdb 24.1新特性

J.1. 版本发布 13.8-24.1

J.1.1. Oracle 兼容
J.1.2. plorasql 增强
J.1.3. MySQL 兼容
J.1.4. lightdb 新特性
J.1.5. ltjdbc 增强

版本发布日期：. 2024年04月30日

J.1.1. Oracle 兼容

  • 支持group by常量，即支持按常量分组聚集，其中分组字段可以有一个或多个常量字段，也可以常量字段和变量以及表达式混合使用。 请参见聚集函数。

    示例:

    lightdb@oracle=# select 0,
lightdb@oracle-#         count(*) as rowcount ,
lightdb@oracle-#         sum(sal) as sum_salary,
lightdb@oracle-#         sum(comm) as sum_comm
lightdb@oracle-# from emp group by -100;
 ?column? | rowcount | sum_salary | sum_comm 
----------+----------+------------+----------
        0 |       14 |      29025 |     2200
(1 row)
 
lightdb@oracle=# select 0,
        count(*) as rowcount ,
        sum(sal) as sum_salary,
        sum(comm) as sum_comm,deptno
from emp group by -100,deptno,1,23,'aa';
 ?column? | rowcount | sum_salary | sum_comm | deptno 
----------+----------+------------+----------+--------
        0 |        3 |       8750 |          |     10
        0 |        6 |       9400 |     2200 |     30
        0 |        5 |      10875 |          |     20
(3 rows)
 
lightdb@oracle=# select mgr,count(*) from emp group by 100,-11111111111.23232423543545657,mgr;
 mgr  | count 
------+-------
      |     1
 7566 |     2
 7782 |     1
 7902 |     1
 7788 |     1
 7839 |     3
 7698 |     5
(7 rows)

  • 支持merge partitions功能，包含list和range分区，不支持hash分区。 请参见ALTER TABLE。

    range分区管理示例：

    CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate) (
	PARTITION p0 VALUES LESS THAN (TO_DATE('2020-01-01', 'yyyy-mm-dd')),
	PARTITION p1 VALUES LESS THAN (TO_DATE('2020-02-01', 'yyyy-mm-dd')),
	PARTITION p2 VALUES LESS THAN (TO_DATE('2020-03-01', 'yyyy-mm-dd')),
	PARTITION p3 VALUES LESS THAN (TO_DATE('2020-04-01', 'yyyy-mm-dd')),
	PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX idx_measurement_logdate ON measurement (logdate);
insert into measurement values(0, to_date('2019-01-01', 'yyyy-mm-dd'), 0, 0);
insert into measurement values(1, to_date('2020-01-01', 'yyyy-mm-dd'), 1, 1);
insert into measurement values(2, to_date('2020-02-01', 'yyyy-mm-dd'), 2, 2);
insert into measurement values(3, to_date('2020-03-01', 'yyyy-mm-dd'), 3, 3);
insert into measurement values(4, to_date('2020-04-01', 'yyyy-mm-dd'), 4, 4);

--合并后的分区名可以和原来某个分区名同名
alter table measurement merge partitions p0, p1 into partition p0;
insert into measurement values (6, to_date('2020-01-08', 'yyyy-mm-dd'), 6, 6);
select * from pg_indexes where tablename = 'measurement$p0';

    list分区管理示例：

    CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    total_amount NUMBER(10, 2)
)
PARTITION BY LIST (customer_id)
(
    PARTITION p1 VALUES (1001, 1002, 1003),
    PARTITION p2 VALUES (2001, 2002, 2003),
    PARTITION p3 VALUES (3001, 3002, 3003),
    PARTITION p4 VALUES (4001, 4002, 4003),
    PARTITION p5 VALUES (DEFAULT)
);
\d+ orders
insert into orders values (1, 999, 1);
insert into orders values (2, 1001, 2);
insert into orders values (3, 2002, 3);
insert into orders values (4, 3003, 4);
insert into orders values (5, 4002, 5);

--list 分区可以有 default 分区
alter table orders merge partitions p1, p2, p3 into partition p1;
--= 3
select count(*) from orders$p1;

alter table orders merge partitions p4, p5 into partition p2;
--= 2
select count(*) from orders$p2;

  • 支持设置会话级日期格式nls_date_format。 请参见orafce。

    示例：

    alter session set NLS_DATE_FORMAT to "yyyymmdd hh24";
select sysdate;
alter session set NLS_DATE_FORMAT to "yyyy-mm-dd hh24";
select to_date('2024-01-20 10:30:00', 'YYYY-MM-DD HH24:MI:SS');

  • UNION支持和NULL类型匹配。 请参见UNION。

    示例：

    lightdb@oracle_test=# select null l_zqlbmx2 from dual
lightdb@oracle_test-# union all
lightdb@oracle_test-# select null l_zqlbmx2 from dual
lightdb@oracle_test-# union all
lightdb@oracle_test-# select 0 l_zqlbmx from dual;
 l_zqlbmx2 
-----------
 
 
 0
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select 5.55::numeric l_zqlbmx from dual;
 l_zqlbmx2 
-----------
 
 
 5.55
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select sysdate l_zqlbmx from dual;
      l_zqlbmx2      
---------------------
 
 
 2024-01-26 08:23:42
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select current_timestamp l_zqlbmx from dual;
           l_zqlbmx2           
-------------------------------
 
 
 2024-01-26 16:28:07.278097+08
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select 'hello' l_zqlbmx from dual;
 l_zqlbmx2 
-----------
 
 
 hello
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

  • 支持||与-直接连接无空格语法解析。 请参见CREATE OPERATOR。

    示例：

    lightdb@oracle=# select 1 || -1 from dual;
 ?column? 
----------
 1-1
(1 row)

lightdb@oracle=# select 1||-'1'from dual;
 ?column? 
----------
 1-1
(1 row)

  • 支持表达式左边为ROWNUM。 请参见ROWNUM。

    示例：

    SELECT rownum FROM table WHERE id <= 10;
SELECT * FROM table WHERE rownum <= 10;
SELECT * FROM table WHERE rownum <= 10 order by id;
SELECT rownum + 1, 1 + rownum FROM table WHERE rownum <= 10;

  • 支持聚合函数order by用法，当没有group by子句同时有聚合函数时,允许出现order by子句。 请参见syntax-compatible-type。

    示例：

    --有group by子句
create table student(id integer primary key, score integer, classid integer);
insert into student values(1,90,1);
insert into student values(2,100,1);
insert into student values(2,100,2);
select count(score) from student group by classid;
select avg(score) from student group by classid order by classid;
select avg(score)from student group by classid order by score; 

--无group by子句
create table student(id integer primary key, score integer, classid integer);
insert into student values(1,90,1);
insert into student values(2,100,1);
insert into student values(2,100,2);
select count(*) from student order by classid; 
select count(*)+1 from stduent order by classid;

  • 支持操作符>=、<=、!=、<>字符中间有空格的语法。

    示例：

    select count(*) from dual where 1 >  = 1;
select count(*) from dual where 1 <   =  1;
select count(*) from dual where 1 !     =   2;
select count(*) from dual where 1 <       >   1;

  • 支持 connect by rownum 的语法。 请参见SELECT。

    示例：

    -- 生成序列
select
  rownum
from
  dual CONNECT BY rownum <= 6;
 rownum 
--------
      1
      2
      3
      4
      5
      6
(6 rows)
-- rownum表达式
select
  rownum
from
  duals CONNECT BY rownum + 2 - 1 <= 2 + 2;
 rownum 
--------
      1
      2
      3
(3 rows)
  
SELECT empno,ename,mgr,LEVEL FROM emp_ CONNECT BY rownum + empno < 3;
ERROR:  connect by rownum does not support rownum Op column
LINE 7:   emp_ CONNECT BY rownum + empno < 3;
                                         
select rownum from duals CONNECT BY 6 > rownum * 2;
ERROR:  connect by rownum does not support Multiplication or division operation
LINE 4:   duals CONNECT BY 6 > rownum * 2;
                             
-- 生成序列，对序列增加Non-SPJ操作
select rownum from dual CONNECT BY rownum <= 6 ORDER BY 1 DESC LIMIT 2;
 rownum 
--------
      6
      5
(2 rows)
  
-- 绑定变量
PREPARE my_query (VARCHAR) AS select rownum from duals CONNECT BY rownum <= $1;
EXECUTE my_query(6);
 rownum 
--------
      1
      2
      3
      4
      5
      6
(6 rows)
  
-- 不支持多表
SELECT empno,ename,mgr,LEVEL FROM emp_,emp_2 where emp_.empno = emp_2.mgr CONNECT BY rownum <= 6;
ERROR:  connect by rownum not support multi tables
  
SELECT empno,ename,mgr,LEVEL FROM emp_ join emp_2 on emp_.empno = emp_2.mgr CONNECT BY rownum <= 6;
ERROR:  connect by rownum not support multi tables
  
-- 函数
CREATE OR REPLACE FUNCTION generate_rownums(limit_value BIGINT)
RETURNS SETOF BIGINT AS $$ BEGIN RETURN QUERY
select
  rownum
from
  duals CONNECT BY rownum <= limit_value;
  
END;
$$ LANGUAGE plpgsql;
select generate_rownums(6);
 generate_rownums 
------------------
                1
                2
                3
                4
                5
                6
(6 rows)

  • 支持rownum 作为约束条件时，使用"COUNT STOPKEY" 查询优化技术，原理是在扫描表时，会在满足rownum < N 条件的记录数量达到指定值后停止扫描，而不是继续扫描整个表。

    示例：

    -- 默认走了并行，执行时间 568.314 ms
explain analyze 
 select rownum, a from test where b < 10 and rownum < 100;
                                                              QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------------------------
  Count StopKey  (cost=1000.00..9455.15 rows=99 width=12) (actual time=0.284..568.213 rows=4 loops=1)
    ->  Gather  (cost=1000.00..34820.60 rows=396 width=12) (actual time=0.283..568.210 rows=4 loops=1)
          Workers Planned: 4
          Workers Launched: 4
          -> Parallel Count StopKey  (cost=0.00..33781.00 rows=99 width=12) (actual time=442.731..555.289 rows=1 loops=5)
                ->  Parallel Seq Scan on test  (cost=0.00..85305.55 rows=250 width=12) (actual time=442.726..555.282 rows=1 loops=5)
                      Filter: (b < 10)
                      Rows Removed by Filter: 1999999
  Planning Time: 0.139 ms
  Execution Time: 568.314 ms
 (10 rows)
  
  
 -- 强制走非并行，执行用时 1618.765 ms
 SET min_parallel_table_scan_size = 99999;
  
 explain analyze select rownum, a from test where b < 10 and rownum < 100;
                                                    QUERY PLAN
 ----------------------------------------------------------------------------------------------------------------
  Count StopKey  (cost=0.00..17726.66 rows=99 width=12) (actual time=0.016..1618.701 rows=4 loops=1)
    ->  Seq Scan on test  (cost=0.00..179057.19 rows=1000 width=12) (actual time=0.015..1618.699 rows=4 loops=1)
          Filter: (b < 10)
          Rows Removed by Filter: 9999996
  Planning Time: 0.189 ms
  Execution Time: 1618.765 ms
 (6 rows)

  • 支持在select后面where in语句中，单个元素查询可以不带括号，直接查询。

    示例：

    create table test (a int,b varchar(100),c varchar2(10),d date,m timestamp(6),n ROWID);

--数值
select * from test where a in 2;

--字符串
select * from test where b in 'abc';
select * from test where c in 'abc';

--日期
select * from test where d in to_date('2022-01-01','yyyy-mm-dd');
select * from test where m in to_timestamp('2022-01-01','yyyy-mm-dd');

--Rowid类型
select * from test where n in '(0,1)'::tid;

--加减运算
	1.数值类型的加减运算：
select * from test where a in 2+1;
select * from test where a in 2-1;
select * from test where a in 2*1;
select * from test where a in 2/1;

	2.表的列名的加减运算
select * from test t where a in t.a/1;
select * from test t where a in t.a+1;
select * from test t where a in t.a-1;
select * from test t where a in t.a*1;
select * from test t where a in t.a/1;
select * from test t where a in t.a+t.a;
select * from test t where a in t.a-t.a;
select * from test t where a in t.a*t.a;

	3.函数加减运算
select a from test where a in TO_NUMBER(2)+2;
select a from test where a in TO_NUMBER(2)+ TO_NUMBER(2)

--函数
SELECT * FROM test WHERE a IN CAST ('1' AS INTEGER);
select * from test where a in TO_NUMBER(2);
select * from test where  m in TIMESTAMP '2022-01-01 00:00:00' AT TIME ZONE 'Asia/Shanghai';

  • 支持timestamp_to_scn(timestamp)函数，根据时间戳返回一个 SCN(System Change Number)。 请参见orafce。

    示例：

    set orafce.timezone = 'Asia/Shanghai';
select timestamp_to_scn(sysdate) from dual;
 timestamp_to_scn
------------------
          5288085
(1 row)

create table foo(a int);
insert into foo values (1);
select ora_rowscn, xmin from foo;
 ora_rowscn |  xmin
------------+---------
    5288454 | 5288454
(1 row)

  • 支持oracle bit_and、bit_or、bit_xor、bit_complement 函数，对RAW类型进行位操作。 请参见orafce。

    示例：

    --bit_and 函数对 raw 类型中的每一位进行 and 操作，即按位与。
--= 0A0B
SELECT UTL_RAW.BIT_AND(HEXTORAW('0A0B'), HEXTORAW('0F')) AS result FROM DUAL;

--bit_or 函数对 raw 类型中的每一位进行 or 操作，即按位或。
--= BBFDEF
SELECT UTL_RAW.BIT_OR(HEXTORAW('ABCDEF'), HEXTORAW('1234')) FROM dual;

--bit_xor 函数对 raw 类型中的每一位进行 xor 操作，即按位异或。
--= B9F9EF
SELECT UTL_RAW.BIT_XOR(HEXTORAW('ABCDEF'), HEXTORAW('1234')) FROM dual;

--bit_complement 函数 raw 类型中的每一位进行按位求补操作。
--= 543210
select UTL_RAW.BIT_COMPLEMENT(HEXTORAW('ABCDEF')) from dual;

         注：在lightdb 24.1.1版本中，内核重新实现了位相关操作，性能提升5倍以上，推荐用户从24.1升级到24.1.1。

  • 支持XMLELEMENT函数省略NAME关键字，功能不受影响。XMLELEMENT 是一种格式化 XML 标记，以包含一个或多个表达式值的函数。 请参见functions。

    示例：

    lightdb@oracle_test=# SELECT xmlelement(foo);
 xmlelement 
------------
 <foo/>
(1 row)

lightdb@oracle_test=# SELECT xmlelement(foo, xmlattributes('xyz' as bar));
    xmlelement    
------------------
 <foo bar="xyz"/>
(1 row)

lightdb@oracle_test=# SELECT xmlelement(foo, xmlattributes(current_date as bar), 'cont', 'ent');
             xmlelement              
-------------------------------------
 <foo bar="2024-03-06">content</foo>
(1 row)

lightdb@oracle_test=#

  • 创建函数或存储过程参数类型是date类型时，兼容oracle的date类型。 请参见orafce。

    示例：

    --存储过程使用示例
create or replace procedure ptest(x date) AS
begin
	DBMS_OUTPUT.PUT_LINE('tmp: ' || x);
end;
/

call ptest(sysdate);

--函数使用示例
create or replace function ftest(x date)  return date AS
begin
	DBMS_OUTPUT.PUT_LINE('tmp: ' || x);
	return x;
end;
/

select ftest(sysdate);

--包使用示例
create or replace package pack is
	procedure ptest(x date);
	function ftest(x date) return date;
end;
/

create or replace package body pack is
	procedure ptest(x date) as
tmp varchar(20);
	begin
		tmp := 'hello world';
DBMS_OUTPUT.PUT_LINE('tmp: ' || tmp);

	end;

	function ftest(x date) return date as
tmp varchar(20);

	begin
		tmp := 'hello world';
DBMS_OUTPUT.PUT_LINE('tmp: ' || tmp);

		return x;
	end;
end;
/

\df pack.ptest
\df pack.ftest

call pack.ptest(sysdate);
select pack.ftest(sysdate);

  • 支持to_date函数，缺省日期和oracle保持一致；支持第一个参数和第二个参数分隔符不匹配场景。 请参见orafce。

    示例：

    select to_date('170458','hh24:mi:ss') FROM dual;
select to_date('10-12 17:04:58','mm-dd hh24:mi:ss') FROM dual;

  • 对TO_TIMESTAMP函数进行增强，对日期转换格式自动匹配。 1.格式串的分隔符支持任意的非字母数字的可见Ascii字符； 2.输入日期和时间没有分隔符而格式串有分隔符的情况下按照格式串长度进行解析； 3.格式字符串模板支持FF,精度到小数点后6位。 请参见functions。

    示例：

    lightdb@lightdb=# set datestyle to iso;
SET
lightdb@lightdb=# select to_timestamp('20231201 170000','yyyy-mm-dd hh24:mi:ss.ff');
      to_timestamp
------------------------
 2023-12-01 17:00:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('20231201 170000','yyyy/mm/dd hh24miss.ff');
      to_timestamp
------------------------
 2023-12-01 17:00:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00','yyyy-mm-dd hh24:mi:ss');
      to_timestamp
------------------------
 2024-01-20 10:30:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2023-02-14 10:11:12.123','yyyy-mm-dd hh:mi:ss.ff');
        to_timestamp
----------------------------
 2023-02-14 10:11:12.123+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00','yyyy-mm-dd hh24:mi:ss');
      to_timestamp
------------------------
 2024-01-20 10:30:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00.123456','yyyy-mm-dd hh24:mi:ss.us');
         to_timestamp
-------------------------------
 2024-01-20 10:30:00.123456+08
(1 row)

lightdb@lightdb=# select to_timestamp('20-jan-24','dd-mon-yy');
      to_timestamp
------------------------
 2024-01-20 00:00:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy-mm-dd hh:mi:ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm/dd hh:mi:ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm/dd hh/mi/ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm-dd hh\mi\ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy:mm:dd hh\mi\ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy:mm:dd hh@mi@ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2011-09-14 12:52:42.123456789', 'yyyy-mm-dd hh24:mi:ss.ff');
         to_timestamp
-------------------------------
 2011-09-14 12:52:42.123456+08
(1 row)

  • create sequence支持设置minvalue值小于INT64_MIN。 请参见CREATE SEQUENCE。

    示例：

    lightdb@oracle_test=# show lightdb_dblevel_syntax_compatible_type ;
 lightdb_dblevel_syntax_compatible_type 
----------------------------------------
 Oracle
(1 row)

lightdb@oracle_test=# create sequence s1 minvalue -1000000000000000000000000000;
NOTICE:  minvalue out of range, set sequence min value to -9223372036854775808
CREATE SEQUENCE
lightdb@oracle_test=# \d+ s1
                                           Sequence "public.s1"
  Type  |        Start         |       Minimum        |       Maximum       | Increment | Cycles? | Cache 
--------+----------------------+----------------------+---------------------+-----------+---------+-------
 bigint | -9223372036854775808 | -9223372036854775808 | 9223372036854775807 |         1 | no      |     1

lightdb@oracle_test=#

  • 支持用户自定义的无参函数可以不带括号执行。

    示例：

    create function fn_noparam RETURN int
as
begin
  return 1;
end;
/

--= 1, simple expr
select fn_noparam from dual;
--= 2
select fn_noparam + 1 from dual;
--= 1
select 1 from dual where fn_noparam = 1;

--= 1, composed expr
select least(fn_noparam, 2) from dual;

  • 支持兼容Oracle 视图机制。即如果视图依赖的对象被破坏后，影响了视图的定义，则视图会自动处于不正确的状态，当相应的对象恢复之后，视图自动恢复。 请参见CREATE VIEW。 请参见DROP VIEW。

    其中：1，2，3中对视图所依赖的表或视图的修改会使得视图失效，若后续操作使得对应的对象恢复到视图依赖的状态，则对应的视图也自动恢复。

    1.创建表，视图
lightdb@oradb=# create table t(a int);
CREATE TABLE
lightdb@oradb=# create view v as select a from t;
CREATE VIEW
lightdb@oradb=# insert into t(a) values(1);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 1
(1 row)

2.删除表，重建表
lightdb@oradb=# drop table t;
DROP TABLE
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# create table t(a int);
CREATE TABLE
lightdb@oradb=# insert into t(a) values(2);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 2
(1 row)

3.修改列名，列类型
lightdb@oradb=# alter table t rename a to aa;
ALTER TABLE
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# alter table t add column a int;
ALTER TABLE
lightdb@oradb=# select * from v;
 a
---
 
(1 row)
 
lightdb@oradb=# alter table t modify a numeric;
ALTER TABLE
lightdb@oradb=# select * from v;
 a
---
 
(1 row)
 
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 a      | numeric |           |          |         | main    |
View definition:
 SELECT t.a
   FROM t;

4.删除视图依赖列
lightdb@oradb=# alter table t drop a;
ALTER TABLE
lightdb@oradb=# \d+ t
                                     Table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 aa     | integer |           |          |         | plain   |              |
Access method: heap
 
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# alter table t add a int;
ALTER TABLE
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 a      | integer |           |          |         | plain   |
View definition:
 SELECT t.a
   FROM t;
 
lightdb@oradb=# insert into t(a) values(1);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 
 1
(2 rows)

5.重新定义视图
lightdb@oradb=# alter table t add b int;
ALTER TABLE
lightdb@oradb=# alter table t add c int;
ALTER TABLE
lightdb@oradb=# create or replace view v as select b from t;
CREATE VIEW
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 b      | integer |           |          |         | plain   |
View definition:
 SELECT t.b
   FROM t;
 
lightdb@oradb=# create or replace view v as select b,c from t;
CREATE VIEW
 
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 b      | integer |           |          |         | plain   |
 c      | integer |           |          |         | plain   |
View definition:
 SELECT t.b,
    t.c
   FROM t;
    也可通过pg_get_viewdef查看视图定义。如下：
lightdb@postgres=# SELECT pg_get_viewdef('v');
 pg_get_viewdef 
----------------
  SELECT t.id  +
    FROM t;
(1 row)

　　但是pg_get_viewdef存储的是语义解析后展开、反序列化的定义。要查看原始定义，oracle模式下可执行如下SQL：

lightdb@oradb=# select * from lt_view_defs where relname='v1';
schemaname | relname | status | force_pending | query_string
------------+---------+--------+---------------+--------------
public | v1 | t | n | SELECT * +
| | | | FROM v;

  • 支持GLOBAL PARTITION BY hash语法。 请参见CREATE INDEX。

    示例：

    1.创建一个分区表
CREATE TABLE ora_ph_t(a int,b int,c int) PARTITION BY HASH(a) partitions 4;

2.创建分区索引
CREATE INDEX t_global_ph_idx ON ora_ph_t(a) GLOBAL PARTITION BY hash(a) partitions 2;

3.使用tablespace
\! mkdir /tmp/tbs_test_path
create tablespace tbs_test location '/tmp/tbs_test_path';
CREATE INDEX t_global_ph_idx_with_tbs ON ora_ph_t(a ASC ,b DESC) TABLESPACE tbs_test GLOBAL PARTITION BY hash(a) partitions 2;

  • 支持使用嵌套表元素作为数据源插入。 请参见orafce。

    示例1：基于表类型来创建嵌套表，并使用嵌套表元素作为数据源执行insert

    drop table if exists t;
create table t(a int,b float,c number);
insert into t values(1,10,100),(2,20,200);
select * from t;
 
lightdb@oracle=# select * from t;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
(2 rows)
 
 
create or replace procedure p1 is 
  TYPE array_table IS TABLE OF t%rowtype;
  a_table array_table := array_table();
BEGIN
 
  SELECT t.* BULK COLLECT INTO a_table FROM t;
  execute immediate 'truncate table t';
 
  FOR i IN a_table.first..a_table.last LOOP
	a_table(i).a := a_table(i).a * 100;
	a_table(i).b := a_table(i).b * 100;
	a_table(i).c := a_table(i).c * 100;
    INSERT INTO t VALUES a_table(i); --支持使用嵌套表元素作为数据源插入
  END LOOP;
 
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/
 
begin
  p1();
end;
/
 
select * from t;
  a  |  b   |   c   
-----+------+-------
 100 | 1000 | 10000
 200 | 2000 | 20000
(2 rows)

    示例2：基于全局type创建嵌套表，并使用嵌套表元素作为数据源执行insert

    drop table if exists t;
create table t(a int,b float,c number);
insert into t values(1,10,100),(2,20,200);
select * from t;
 
lightdb@oracle=# select * from t;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
(2 rows)
 
create type type1 as (a int,b float,c number);
create or replace procedure p1 is 
  TYPE array_table IS TABLE OF type1;	--基于type创建嵌套表
  a_table array_table := array_table();
BEGIN
  execute immediate 'truncate table t';
  a_table.extend();
  a_table.extend;
  a_table(1).a := 111;
  a_table(1).b := 111.222;
  a_table(1).c := 222.222;
  INSERT INTO t VALUES a_table(1);
  
  a_table(2).a := 100;
  a_table(2).b := 100.202;
  a_table(2).c := 222.123456789;
  INSERT INTO t VALUES a_table(2);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/

  • 新增1个优化器提示no_expand hint,用于取消OR-expansion优化，不进行OR-expansion优化。 请参见lt_hint_plan。

    示例：

    create table t_no_expand1(key1 int, key2 int);
create table t_no_expand2(key1 int, key2 int);

lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand*/ * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#0"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select /*+no_expand*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#1"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2 @"lt#0"
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand(@qb)*/ * from t_no_expand1 where exists (select/*+qb_name(qb)*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand(@qb)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#0"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2 @qb
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

  • 新增5个（ECPG）特性。 请参见ECPG。

    • 支持EXEC ORACLE OPTION(CHAR_MAP=STRING)设置。 设置该选项后，将保证字符数组以null结尾。

    • 支持EXEC SQL EXECUTE执行匿名块的语法。

      示例：

      --pgc文件		  
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

static void
print_sqlca()
{
    fprintf(stderr, "==== sqlca ====\n");
    fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode);
    fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate);
    fprintf(stderr, "===============\n");
}

int main() {
	exec sql begin declare section;
	char c_val[2000] = {0};
	exec sql end declare section;

	ECPGdebug(1, stderr);
	EXEC SQL CONNECT TO tcp:postgresql://127.0.0.1:5432/test_o;

	EXEC SQL SET AUTOCOMMIT TO ON;
	EXEC SQL WHENEVER SQLWARNING SQLPRINT;
	EXEC SQL WHENEVER SQLERROR CALL print_sqlca();

	exec sql create table t1(
					id integer,
					t text,
					d1 numeric,
					d2 float8,
					c char(10));
	exec sql insert into t1 values
					(1, 'a', 1.0, 1, 'a'),
					(2, null, null, null, null),
					(4, 'd', 4.0, 4, 'd');

	exec sql execute
	  begin
		update t1 set c ='aa' where id = 2 return c into :c_val;
		end;
	end-exec;

	EXEC SQL EXECUTE
		BEGIN
			:c_val:=dbms_metadata.get_ddl('TABLE', 'T1');
		END;
	END-EXEC;
	
	EXEC SQL DROP table t1;


	exec sql disconnect;
	return 0;
}

    • 支持EXEC SQL FOR :i UPDATE/INSERT语法，其功能是取代 for(;;) { update(or insert)语法; }

      示例：

      --pgc文件
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char *uid = "test/test@ip/test";
EXEC SQL END DECLARE SECTION;

int
main(void)
{
    int i=2;
    EXEC SQL WHENEVER SQLERROR continue;
    char arr[26]="123456789";
    EXEC SQL CONNECT :uid;
	fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    EXEC SQL FOR :i update test set a = :arr where a = :arr[0] ;
	fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    EXEC SQL commit;
	fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    exit(0);
}

    • 支持通过return into来返回return 的值到c变量中。

      示例：

      --pgc文件
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

static void
print_sqlca()
{
    fprintf(stderr, "==== sqlca ====\n");
    fprintf(stderr, "sqlcode: %ld\n", sqlca.sqlcode);
    fprintf(stderr, "sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    fprintf(stderr, "sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    fprintf(stderr, "sqlstate: %5s\n", sqlca.sqlstate);
    fprintf(stderr, "===============\n");
}

int main() {
	exec sql begin declare section;
	char c_val[100] = {0};
	exec sql end declare section;

	ECPGdebug(1, stderr);
	EXEC SQL CONNECT TO tcp:postgresql://127.0.0.1:5432/test_o;

	EXEC SQL SET AUTOCOMMIT TO ON;
	EXEC SQL WHENEVER SQLWARNING SQLPRINT;
	EXEC SQL WHENEVER SQLERROR CALL print_sqlca();

	exec sql create table t1(
					id integer,
					t text,
					d1 numeric,
					d2 float8,
					c char(10));
	exec sql insert into t1 values
					(1, 'a', 1.0, 1, 'a'),
					(2, null, null, null, null),
					(4, 'd', 4.0, 4, 'd');

	exec sql update t1 set c ='aa' where id = 2 return c into :c_val;
	exec sql delete from t1 where id = 2 return c into :c_val;
	exec sql insert into t1 values(2,null, null,null,  'bb') return c into :c_val;

	exec sql
	Do $$
	Begin
		update t1 set c ='aa' where id = 2 return c into :c_val;
		end;
	$$ Language plorasql;

	exec sql
	Do $$
	Begin
		delete from t1 where id = 2 return c into :c_val;
		end;
	$$ Language plorasql;

	exec sql
	Do $$
	Begin
		insert into t1 values(2,null, null,null,  'bb') return c into :c_val;
		end;
	$$ Language plorasql;

	EXEC SQL DROP table t1;


	exec sql disconnect;
	return 0;
}

    • 在匿名块中，支持数组变量参数绑定，让嵌套表与C数组变量之间能够轻松灵活传递。

      示例：

      --pgc文件
#include <stdio.h>
#include <stdlib.h>

void
print_sqlca()
{
    printf("==== sqlca ====\n");
    printf("sqlcode: %ld\n", sqlca.sqlcode);
    printf("sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
    printf("sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
    printf("sqlerrd: %ld %ld %ld %ld %ld %ld\n", 
        sqlca.sqlerrd[0],
        sqlca.sqlerrd[1],
        sqlca.sqlerrd[2],
        sqlca.sqlerrd[3],
        sqlca.sqlerrd[4],
        sqlca.sqlerrd[5]);
    printf("sqlwarn: %d %d %d %d %d %d %d %d\n", 
        sqlca.sqlwarn[0],
        sqlca.sqlwarn[1],
        sqlca.sqlwarn[2],
        sqlca.sqlwarn[3],
        sqlca.sqlwarn[4], 
        sqlca.sqlwarn[5],
        sqlca.sqlwarn[6],
        sqlca.sqlwarn[7]);
    printf("sqlstate: %5s\n", sqlca.sqlstate);
    printf("===============\n");
}

int main()
{
	EXEC SQL BEGIN DECLARE SECTION;
	const char *target = "oracledb@192.168.226.100";
	const char *user = "lightdb";
	const char *passwd = "lightdb";
	long pid = 0;
	int id   = 0;
	int i    = 0;
	int ret  = 0;
	varchar vvcr1arr[3][100];
	varchar vvcr2arr[3][100];
	char    vchr1arr[3][100];
	char    vchr2arr[3][100];
	double  vdoubarr[3];
	float   vflotarr[3];
	short   vint2arr[3];
	int     vint4arr[3];
	long long int vint8arr[3];
	EXEC SQL END DECLARE SECTION;

	memset(vvcr1arr,0,sizeof(vvcr1arr));
	memcpy(vvcr1arr[0].arr, "abc", 3);
	vvcr1arr[0].len = 3;
	memcpy(vvcr1arr[1].arr, "def", 3);
	vvcr1arr[1].len = 3;
	memcpy(vvcr1arr[2].arr, "ghi", 3);
	vvcr1arr[2].len = 3;
	memset(vvcr2arr,0,sizeof(vvcr2arr));
	memcpy(vvcr2arr[0].arr, "qaz", 3);
	vvcr2arr[0].len = 3;
	memcpy(vvcr2arr[1].arr, "wsx", 3);
	vvcr2arr[1].len = 3;
	memcpy(vvcr2arr[2].arr, "edc", 3);
	vvcr2arr[2].len = 3;
	memset(vchr1arr,0,sizeof(char)* 3 * 100);
	memcpy(vchr1arr[0], "abc", 3);
	memcpy(vchr1arr[1], "def", 3);
	memcpy(vchr1arr[2], "ghi", 3);
	memset(vchr2arr,0,sizeof(char)* 3 * 100);
	memcpy(vchr2arr[0], "qaz", 3);
	memcpy(vchr2arr[1], "wsx", 3);
	memcpy(vchr2arr[2], "edc", 3);
	memset(vdoubarr,0,sizeof(double)*3);
	memset(vflotarr,0,sizeof(float) *3);
	memset(vint2arr,0,sizeof(short) *3);
	memset(vint4arr,0,sizeof(int)   *3);
	memset(vint8arr,0,sizeof(long long int)*3);

	EXEC SQL CONNECT TO :target USER :user USING :passwd;
	EXEC SQL WHENEVER SQLERROR CALL print_sqlca();
	EXEC SQL SELECT pg_backend_pid() INTO :pid;
	printf("current pid=%ld\n", pid);

	EXEC SQL 
	DO $$
	BEGIN
		:ret := fhsarray(:vvcr1arr,:vvcr2arr,:vdoubarr,:vflotarr,:vint2arr,:vint4arr,:vint8arr,:id);
	END;
	$$ LANGUAGE plorasql;

	printf("id=%d,ret=%d\n", id,ret);
	for (i = 0; i < 3; i++)
		printf("index=%d,varchar2value=%s,varcharvalue=%s,doublevalue=
		%f,floatvalue=%f,int2value=%hd,int4value=%d,int8value=%lld\n", 
		i,vvcr1arr[i].arr,vvcr2arr[i].arr,vdoubarr[i],vflotarr[i],vint2arr[i],vint4arr[i],vint8arr[i]);

	EXEC SQL DISCONNECT;
}

  • oracle_fdw 支持连表下推增强。 请参见oracle_fdw。

  • oracle_fdw 支持操作符和函数下推。 请参见oracle_fdw。

  • oracle_fdw 支持oracle.date/varchar2。 请参见oracle_fdw。

J.1.2. plorasql 增强

  • 支持string数据类型，允许用户在存储过程中定义该数据类型。 请参见数据类型。

  • 支持long数据类型，允许用户在存储过程中定义该数据类型。long类型具有clob类型基本一致的功能特性。 请参见数据类型。

  • 支持pls_integer数据类型，允许用户在存储过程中定义该数据类型。 请参见数据类型。

  • 支持嵌套表指定类型为RECORD类型。 请参见orafce。

  • 支持存储过程、包中关联数组索引列使用varchar2类型。 请参见orafce。

  • 支持Oracle函数/存储过程的默认值用法，有默认值的参数后面的参数可以没有默认值。 请参见CREATE FUNCTION。 请参见CREATE PROCEDURE。

  • 支持存储过程取余mod运算符，与Oracle行为保持一致。 请参见functions。

  • 支持DBMS_DATAPUMP包，实现数据的高性能导入和导出。 请参见orafce。

  • EXECUTE IMMEDIATE支持bulk collect子句指定SQL命令返回的行应分配到地方。 请参见orafce。

  • 存储过程中支持不带参数声明的游标，可以在open打开时加上括号进行调用。 请参见orafce。

  • 存储过程创建支持不同record类型含有相同字段名。 请参见orafce。

  • 存储过程创建支持inner和outer对变量的引用。 请参见orafce。

  • 新增使用赋值符号:=直接为嵌套表元素赋值。 请参见orafce。

  • 支持PL/SQL自定义异常功能，抛出自定义异常，处理自定义异常 来扩展业务自定义异常，丰富PL/SQL行为。 请参见orafce。

  • 支持用户在存储过程，函数，匿名块中显式使用ROLLBACK回滚语句，极大的支持了事务管理的灵活性。 请参见orafce。

  • 支持用户创建函数最大参数个数，从100个提高到最高200个。 请参见GUC参数设置。

  • 支持OracleDBMS_SQL包。 请参见orafce。

  • 支持OracleUTL_FILE包。 请参见orafce。

  • 支持在游标隐式声明时，嵌套使用WITH子句。 请参见orafce。

J.1.3. MySQL 兼容

  • 本期无新增功能。

J.1.4. lightdb 新特性

  • LightDB-x支持ARM平台的K8S部署。

  • LightDB-x支持欧拉操作系统。

  • 调整serial关键字级别。 请参见 SQL关键词。

  • LightDB-x支持同义词特性。 请参见 CREATE DATABASE 。

  • 支持多表层级查询的下推优化特性。

  • 新增GUC 参数 lightdb_analyze_function_bodies，当该参数为 on 时，将会开启表及其列存在性的检查。

  • LightDB-x支持impdb导入命令。

  • LightDB-x支持CREATE/DROP DIRECTORY功能。 请参见 CREATE DIRECTORY。 请参见 DROP DIRECTORY。

  • LightDB-x支持CREATE/DROP/ALTER TRIGGER功能。 请参见 CREATE TRIGGER。 请参见 DROP TRIGGER。 请参见 ALTER TRIGGER。

J.1.5. ltjdbc 增强

  • 支持CREATE TYPE BODY语法解析。

  • 支持VARCHAR转换为BOOLEAN。

  • 支持Oracle模式数据类型隐式转换功能。

  • 安全性,反SQL注入能力强化。

posted @ 2024-05-26 22:14  zhjh256  阅读(18)  评论(0)  编辑  收藏  举报