J.3. 版本发布 13.8-23.4

J.3. 版本发布 13.8-23.4
Prev	Up	Appendix J. 版本说明	Home	Next

J.3.1. Oracle 兼容
J.3.2. pl/sql 增强
J.3.3. MySQL 兼容
J.3.4. lightdb 新特性
J.3.5. ltjdbc 增强
J.3.6. 新增关键字

版本发布日期：. 2023年12月31日

J.3.1. Oracle 兼容

支持set define 命令，可以用于开启或关闭变量替换功能。请参见set define on/off 命令。

客户端命令行支持'/'。请参见斜杠。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

Select 1+2;
?column? 
----------
3
(1 row)

/

支持大写字段名加双引号进行查询。请参见词法结构。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table t1(id int, "Id" int);

insert into t1 values(1,2),(3,4),(5,6);

select "ID" from t1;
 ID 
----
  1
  3
  5
(3 rows)

支持timestamp、oracle.date和数值的加减运算。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

Create table t_data(dt date, d1 integer, d2 bigint, d3 smallint, d4 numeric(10, 2), dd date);

insert into t_data values(to_date('2023-11-28 20:10:30', 'YYYY-MM-DD HH24:mi:ss'), 1, 2, 3, 4.4, to_date('2023-11-27 20:10:30', 'YYYY-MM-DD HH24:mi:ss'));

insert into t_data values(to_date('2023-11-28 00:00:01', 'YYYY-MM-DD HH24:mi:ss'), 1, 2, 3, 4.5, to_date('2023-11-27 00:00:02', 'YYYY-MM-DD HH24:mi:ss'));

select dt, dt - d1, dt - d1 + d2 , dt - d1 + d2 - d3, dt - d1 + d2 - d3 + d4, dt - dd from t_data;
         dt          |      ?column?       |      ?column?       |      ?column?       |      ?column?       |        ?column?        
---------------------+---------------------+---------------------+---------------------+---------------------+------------------------
 2023-11-28 20:10:30 | 2023-11-27 20:10:30 | 2023-11-29 20:10:30 | 2023-11-26 20:10:30 | 2023-12-01 05:46:30 | 1.00000000000000000000
 2023-11-28 00:00:01 | 2023-11-27 00:00:01 | 2023-11-29 00:00:01 | 2023-11-26 00:00:01 | 2023-11-30 12:00:01 | 0.99998842592592592500
(2 rows)

支持rownum作为右值表达式来使用。请参见ROWNUM。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

SELECT TO_CHAR(TO_DATE(TRIM('20230110'), 'YYYYMMDD') + ROWNUM, 'YYYYMMDD') from dual;
 to_char  
----------
 20230111
(1 row)

支持decode函数参数中数值类型与该数值小数位带有无效0等价。请参见DECODE。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

SELECT decode(0.0, 0, 1, 2), decode(0, 0, 1, 2), decode(0, 0.0, 1, 2), decode(0, 1, 2, 0.0, 4, 5)  FROM dual;
 decode | decode | decode | decode 
--------+--------+--------+--------
      1 |      1 |      1 | 4
(1 row)

SELECT decode(1.10, 1.1, 1, 2) FROM dual;
 decode 
--------
      1
(1 row)

SELECT decode(1.0, 1, 1, 2) FROM dual;
 decode 
--------
      1
(1 row)

substr函数支持text,numeric,numeric或者text,numeric参数组合。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table tt_substr(a number(10), b number(10));

INSERT INTO tt_substr (a, b) VALUES(2, 1);

select substr('aaaaa'::varchar2, 2, a + b) FROM tt_substr;
 substr 
--------
 aaa
(1 row)

select substr('aaaaa'::varchar2, a) FROM tt_substr;
 substr 
--------
 aaaa
(1 row)

to_char函数将日期、数字格式改成文本格式呈现。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

Select to_char(sysdate+1 + interval '-1' year,'d','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''');
 to_char 
---------
 6
(1 row)

rawtohex函数支持uuid类型作参数。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

select rawtohex(sys_guid()) from dual;
             rawtohex             
----------------------------------
 6653ab0b8ad34634bdc1645f3f264455
(1 row)

rowidtochar函数将rowid类型转换为字符类型。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table tt1(idd int);

insert into tt1 values(3);

create table tt7 as select rowidtochar(rowid) as rowid1,*  from tt1;

\d tt7
                 Table "public.tt7"
 Column |   Type   | Collation | Nullable | Default 
--------+----------+-----------+----------+---------
 rowid1 | varchar2 |           |          | 
 idd    | integer  |           |          |

to_timestamp将字符型时间值转换为时间戳格式。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

SELECT TO_TIMESTAMP('2023-10-11 10:10:10', 'YYYY-MM-DD HH:MI:SS PM')  FROM dual;
      to_timestamp      
------------------------
 2023-10-11 10:10:10+08
(1 row)

支持empty_clob/empty_blob初始化lob对象，返回不为null, 即empty_blob() is null不成立。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

Select empty_clob();
 empty_clob 
------------
 
(1 row)

Select empty_blob();
 empty_blob 
------------
 \x
(1 row)

systimestamp(n)支持精度参数。请参见日期/时间函数。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

select current_timestamp,systimestamp(0);
       current_timestamp       |      systimestamp      
-------------------------------+------------------------
 2024-01-02 01:48:16.899369-05 | 2024-01-02 01:48:17-05
(1 row)

select current_timestamp,systimestamp(1);
       current_timestamp       |       systimestamp       
-------------------------------+--------------------------
 2024-01-02 01:48:16.900603-05 | 2024-01-02 01:48:16.9-05

支持nvl函数传参为null。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

select nvl(null::numeric, '0');
 nvl 
-----
   0
(1 row)

支持to_date函数时间的字符串与输出数据格式不匹配。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

select to_date('2022/02/02','yyyy-mm-dd') from dual;
       to_date       
---------------------
 2022-02-02 00:00:00
(1 row)

支持xmlparse,xmlagg函数。请参见XML 函数。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

SELECT XMLPARSE(CONTENT '124 <purchaseOrder poNo="12435"> 
<customerName> Acme Enterprises</customerName>
<itemNo>32987457</itemNo>
</purchaseOrder>' 
WELLFORMED) AS PO FROM DUAL;

po                         
---------------------------------------------------
 124 <purchaseOrder poNo="12435">                 
 <customerName> Acme Enterprises</customerName> 
 <itemNo>32987457</itemNo>                      
 </purchaseOrder>
(1 row)

创建分区表的名称不超过31个字符长度。请参见CREATE TABLE。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE TABLE test_partition_table_len_31__lh(a int,b int) 
PARTITION BY list(a)
SUBPARTITION BY hash(b)
(
    PARTITION p11 VALUES(1,2,3,4),
    PARTITION p12 VALUES(5,6,7,8)
);

支持replace重建object类型。请参见CREATE TYPE。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE OR REPLACE TYPE employee_type AS OBJECT(
x int,
y int,
MEMBER FUNCTION psum(a int, b int) RETURN int,
MEMBER PROCEDURE p(prompt varchar)
);
CREATE TYPE.

支持alter session enable/disable parallel dml命令。请参见ALTER SESSION。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

alter session enable parallel dml;

支持tabs视图，用来查看数据库中所有表空间及其关联对象信息的内置视图。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE TABLE measurement (                                                                                                                    
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

SELECT table_name, tablespace_name FROM oracle.TABS WHERE TABLE_NAME IN ('TEST_VIEW', 'MEASUREMENT', 'MEASUREMENT_Y2018') ORDER BY TABLE_NAME;

table_name  | tablespace_name 
-------------+-----------------
 MEASUREMENT | DEFAULT
(1 row)

支持普通表在线转换成list类型分区表,并且同步更新索引。请参见CREATE TABLE。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE TABLE my_list_tab ( a NUMBER(38,0), cu VARCHAR2(20));
insert into my_list_tab values(1,'china');
insert into my_list_tab values(2,'THAILAND');
insert into my_list_tab values(3,'GERMANY');
insert into my_list_tab values(4,'ITALY');
insert into my_list_tab values(5,'SWITZERLAND');
insert into my_list_tab values(6,'AMERICA');
insert into my_list_tab values(7,'INDIA');

CREATE INDEX i_a ON my_list_tab (a);
CREATE INDEX i_cu ON my_list_tab (cu);

ALTER TABLE my_list_tab MODIFY PARTITION BY LIST (cu) (
   PARTITION asia VALUES ('CHINA', 'THAILAND'),
   PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
   PARTITION west VALUES ('AMERICA'),
   PARTITION east VALUES ('INDIA'),
   PARTITION rest VALUES (DEFAULT))
online
   update indexes
  ( i_a GLOBAL,
    i_cu local
  );

\d+ my_list_tab
                             Partitioned table "public.my_list_tab"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 a      | numeric(38,0) |           |          |         | main     |              | 
 cu     | varchar2(20)  |           |          |         | extended |              | 
Partition key: LIST (cu)
Indexes:
    "i_a" btree (a)
    "i_cu" btree (cu)
Partitions: "my_list_tab$asia" FOR VALUES IN ('CHINA', 'THAILAND'),
            "my_list_tab$east" FOR VALUES IN ('INDIA'),
            "my_list_tab$europe" FOR VALUES IN ('GERMANY', 'ITALY', 'SWITZERLAND'),
            "my_list_tab$west" FOR VALUES IN ('AMERICA'),
            "my_list_tab$rest" DEFAULT

SELECT多表查询使用(+)做外连接时，支持在where条件中非重复列可以不指定表名。请参见SELECT。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

Create table t1(key1 int, key2 int);

Create table t2(key1 int);

Select * from t1, t2 where t1.key1(+)=t2.key1 and key2=2;
 key1 | key2 | key1 
------+------+------
(0 rows)

支持聚合count(*)运算与order by结合运算。请参见SELECT。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE TABLE EMP
(   EMPNO    NUMBER(4, 0),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4, 0),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2, 0),
    DNAME    VARCHAR2(100),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH',  'CLERK',    7902, to_date('1980-12-17','yyyy-mm-dd'), 800.00,  null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN',  'SALESMAN', 7698, to_date('1981-02-20','yyyy-mm-dd'), 1600.00, 300.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD',   'SALESMAN', 7698, to_date('1981-02-22','yyyy-mm-dd'), 1250.00, 500.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES',  'MANAGER',  7839, to_date('1981-04-02','yyyy-mm-dd'), 2975.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','yyyy-mm-dd'), 1250.00, 1400.00, 30, null);

select count(*) from emp order by empno;
 count 
-------
     6
(1 row)

connect by支持绑定变量。请参见SELECT。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

PREPARE my_query (VARCHAR) AS
SELECT LEVEL, empno
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH ename = $1;

EXECUTE my_query('ALLEN');
 level | empno 
-------+-------
     1 |  7499
(1 row)

merge into语句中支持insert语句分支使用表别名。请参见MERGE INTO。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE TABLE EMP
(   EMPNO    NUMBER(4, 0),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4, 0),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2, 0),
    DNAME    VARCHAR2(100),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH',  'CLERK',    7902, to_date('1980-12-17','yyyy-mm-dd'), 800.00,  null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN',  'SALESMAN', 7698, to_date('1981-02-20','yyyy-mm-dd'), 1600.00, 300.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD',   'SALESMAN', 7698, to_date('1981-02-22','yyyy-mm-dd'), 1250.00, 500.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES',  'MANAGER',  7839, to_date('1981-04-02','yyyy-mm-dd'), 2975.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','yyyy-mm-dd'), 1250.00, 1400.00, 30, null);
 
declare
i number;
begin
execute immediate q'[MERGE INTO emp e
USING (
    SELECT 
         1 as empno, 
        'ename' as ename
    FROM dual
) new_data
ON (e.empno = new_data.empno)   
WHEN MATCHED THEN 
    UPDATE SET e.ename = new_data.ename
WHEN NOT MATCHED THEN 
    INSERT (e.empno, e.ename) VALUES (
        new_data.empno, 
        new_data.ename
    )]';
commit;
end;
/

新增6个优化器提示（hint）。请参见lt_hint_plan。

支持ignore_row_on_dupkey_index hint。提示忽略特定列集或指定索引的唯一键违规。
支持push_subq hint。提示强制优化器不下推带子链接的过滤条件，而在最后才使用此条件过滤数据。

支持no_push_subq hint。提示强制优化器下推带子链接的过滤条件，尽可能早的使用此条件过滤数据。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table test_no_push_subq1 as select * from pg_class order by oid limit 100;
create table test_no_push_subq2 as select * from pg_class order by oid limit 100;
create table test_no_push_subq3 as select * from pg_class order by oid limit 100;
create table test_no_push_subq4 as select * from pg_class order by oid limit 100;

EXPLAIN (COSTS false)
select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
where a.oid = (select/*+no_push_subq*/ max(oid) from test_no_push_subq2);
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (a.oid = $0)
   InitPlan 1 (returns $0)
     ->  Aggregate
           ->  Seq Scan on test_no_push_subq2 @"lt#0"
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
(9 rows)

支持opt_param hint。提示在生成执行计划的过程中修改guc参数。

支持no_star_transformation hint。提示优化器不执行星型查询转换。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE TABLE times (

  time_id NUMBER PRIMARY KEY,

  calendar_quarter_desc VARCHAR2(20)

);


CREATE TABLE customers (

  cust_id NUMBER PRIMARY KEY,

  cust_city VARCHAR2(20),

  cust_state_province VARCHAR2(20)

);


CREATE TABLE channels (

  channel_id NUMBER PRIMARY KEY,

  channel_class VARCHAR2(20),

  channel_desc VARCHAR2(20)

);


CREATE TABLE sales (

  time_id NUMBER REFERENCES times(time_id),

  cust_id NUMBER REFERENCES customers(cust_id),

  channel_id NUMBER REFERENCES channels(channel_id),

  amount_sold NUMBER

);

EXPLAIN (COSTS false) SELECT/*+no_star_transformation*/ ch.channel_class, c.cust_city, t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
    FROM sales s, times t, customers c, channels ch
    WHERE s.time_id = t.time_id
    AND s.cust_id = c.cust_id
    AND s.channel_id = ch.channel_id
    AND c.cust_state_province = 'CA'
    AND ch.channel_desc in ('Internet','Catalog')
    AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
    GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
LOG:  lt_hint_plan:
used hint:
no_star_transformation
not used hint:
duplication hint:
error hint:

                                              QUERY PLAN                    
                           
----------------------------------------------------------------------------
---------------------------
 GroupAggregate
   Group Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
   ->  Sort
         Sort Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
         ->  Nested Loop
               ->  Nested Loop
                     ->  Hash Join
                           Hash Cond: (s.cust_id = c.cust_id)
                           ->  Seq Scan on sales s @"lt#0"
                           ->  Hash
                                 ->  Seq Scan on customers c @"lt#0"
                                       Filter: ((cust_state_province)::text 
= 'CA'::text)
                     ->  Index Scan using times_pkey on times t @"lt#0"
                           Index Cond: (time_id = s.time_id)
                           Filter: ((calendar_quarter_desc)::text = ANY ('{1
999-Q1,1999-Q2}'::text[]))
               ->  Index Scan using channels_pkey on channels ch @"lt#0"
                     Index Cond: (channel_id = s.channel_id)
                     Filter: ((channel_desc)::text = ANY ('{Internet,Catalog
}'::text[]))
(18 rows)

支持no_push_pred hint。提示优化器不要将连接谓词推送到子查询中。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table test_no_push_pred1 (key1 int primary key, key2 int);
create table test_no_push_pred2 (key1 int primary key, key2 int);
create table test_no_push_pred3 (key1 int primary key, key2 int);

set enable_hashjoin to off;
set enable_mergejoin to off;

EXPLAIN (COSTS false)
select /*+leading(a) no_push_pred(b)*/* from test_no_push_pred1 a join (select * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
                                    QUERY PLAN                              
      
----------------------------------------------------------------------------
------
 Nested Loop
   Join Filter: (a.key1 = test_no_push_pred2.key2)
   ->  Seq Scan on test_no_push_pred1 a @"lt#1"
   ->  Materialize
         ->  Limit
               ->  Index Scan using test_no_push_pred2_pkey on test_no_push_pred2
(6 rows)

支持append hint。提示优化器对insert select模式的语句使用 direct-path 插入。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table test_append(key1 int, key2 int, key3 int, key4 int);
create table test_append1(key1 int, key2 int, key3 int, key4 int);

insert into test_append1 values(1,2,3,4);
insert into test_append1 values(11,21,31,41);
insert into test_append1 values(111,211,311,411);

EXPLAIN (COSTS FALSE) insert/*+append */ into test_append select 1, 1, 1, 2 from dual;
LOG:  lt_hint_plan:
used hint:
not used hint:
append
duplication hint:
error hint:

          QUERY PLAN           
-------------------------------
 Insert on test_append @"lt#1"
   ->  Result
(2 rows)

to_char支持HH24MiSS时间格式。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

select to_char(timestamp 'now','HH24MiSS');
 to_char 
---------
 042121
(1 row)

table表函数column_value伪列。请参见表函数。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create type phone as table of int;
CREATE TYPE

select column_value from table(phone(3,6,9));
 column_value 
--------------
            3
            6
            9
(3 rows)

支持pivot函数。只支持单聚集函数，单列多组的情景。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table test123(name varchar(40),chinese int,math int, course varchar(40), score int);
insert into test123 values('lisi',88,99,'math',99);
insert into test123 values('lisi',88,99,'chinese',88);
insert into test123 values('zhangsan',90,100,'chinese',90);
insert into test123 values('zhangsan',90,100,'math',100);

select * from test123 pivot (sum(score) for course in('chinese','math'));
   name   | chinese | math | 'chinese' | 'math' 
----------+---------+------+-----------+--------
 lisi     |      88 |   99 |        88 |     99
 lisi     |      89 |  100 |           |     99
 lisi     |     100 |   70 |       100 |       
 zhangsan |      76 |   89 |        99 |       
 zhangsan |      90 |  100 |        90 |    100
 zhangsan |      95 |   85 |           |    100
(6 rows)

drop table test123;

merge into支持cte通用表表达式。请参见 MERGE INTO。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE TABLE target (tid integer, balance integer);
CREATE TABLE source (sid integer, delta integer);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 30);

INSERT INTO source VALUES (1, 11);
INSERT INTO source VALUES (5, 55);

WITH s AS ( 
    SELECT sid, delta
    FROM source ), 
U AS ( 
UPDATE target AS t  
    SET tid = 5
    FROM s 
    WHERE t.tid = s.sid
    RETURNING t.tid ) 
INSERT INTO target ( 
    SELECT * FROM s where sid NOT IN (
        SELECT tid FROM U) );

table target;
 tid | balance 
-----+---------
   2 |      20
   3 |      30
   5 |      10
   1 |      11
(4 rows)

支持不可变表（Immutable）。请参见CREATE TABLE。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

CREATE IMMUTABLE TABLE trade_ledger1 
                       (id NUMBER,
                       luser VARCHAR2(40),
                       value NUMBER)
                      NO DROP
                      NO DELETE;
CREATE TABLE

group by跟随字符串常量。请参见SELECT

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

select * from dual group by 1;
 dummy 
-------
 X
(1 row)

支持alter给表添加约束。请参见ALTER TABLE。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

create table add_constraints(id int);

alter table add_constraints add constraint u_add_constraints unique(id);
ALTER TABLE

支持listagg on overflow truncate和error语法。请参见orafce。
创建视图支持force editionable和with read only语法。请参见CREATE VIEW。
支持全局临时表创建在不同schema模式下。请参见System Catalogs。
varchar2支持toast属性。请参见orafce。

number类型格式化不保留小数后缀无效0。请参见orafce。

示例：

create database test_oracle with lightdb_syntax_compatible_type oracle;
\c test_oracle

 select nvl(cast(NULL as int),'1.100');
 nvl 
-----
 1.1
(1 row)

支持dblink。请参见oracle_fdw。

J.3.2. pl/sql 增强

函数支持的出入参数个数超过100。请参见匿名块。
支持创建的存储过程的出入参的类型为sys_refcursor。请参见创建游标变量。
支持可变数组（varray）。请参见可变数组。
匿名块、函数、存储过程和包支持integer类型带长度信息，最多支持19位。请参见整数类型。
支持execute immediate using in out语法。请参见执行动态命令。

J.3.3. MySQL 兼容

新增 CAST强制转换成SIGNED或UNSIGNED目标类型。请参见 MySQL兼容函数。

示例：

create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
select cast(2.5 as unsigned);
 numeric2unsigned 
------------------
                3
(1 row)

select cast('2.5' as unsigned);
 numeric2unsigned 
------------------
                3
(1 row)

select cast(2.5 as signed);
 signed 
--------
      3
(1 row)

select cast('2.5' as signed);
 signed 
--------
      2
(1 row)

新增 CAST强制转换成CHAR目标类型, CHAR默认长度为1，强转的数字、字符串超过长度1不会被截断，上限为1GB。请参见 data type。
示例：
```
create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
select cast(2.5 as char);
 varchar 
---------
 2.5
(1 row)
                
```

支持数值类型与空字符比较。此时空串的值为0。请参见 MySQL兼容函数。

示例：

create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
select 0  = '';
 ?column? 
----------
 t
(1 row)

当字符（仅包括text,varchar,char）转换成数值（仅包括int,bigint,numeric,small int）时, 忽略尾部的不合法字符。

示例：

create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
select '1a' >'';
 ?column? 
----------
 t
(1 row)

优化transform_null_equals 参数。兼容null和''的关系运算。请参见 MySQL兼容函数。

示例：

create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
SELECT '' IS null;
 ?column? 
----------
 f
(1 row)

SELECT null = '';
 ?column? 
----------
 
(1 row)

支持时间类型与字符串。请参见 MySQL兼容函数。

示例：

create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
select current_date > '2023-01-01';
 ?column? 
----------
 t
(1 row)

增加时间类型函数。
- DATEDIFF 函数。支持日期与数值类型求差，数值类型与数值类型求差；当数值型为浮点型时，先truncate浮点型为整型后，再进行求差。请参见 MySQL兼容函数。
  示例：
```
create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
SELECT DATEDIFF(curdate(),curdate()+1.5);
 datediff 
----------
       -1
(1 row)

SELECT DATEDIFF(curdate(),curdate()-1.5);
 datediff 
----------
        2
(1 row)
                   
```
- TIMESTAMPDIFF函数。请参见 MySQL兼容函数。
- CURRENT_DATE函数。请参见当前日期/时间。
- CURDATE函数。函数返回格式只支持'YYYY-MM-DD'，并且不支持乘除运算。请参见 MySQL兼容函数。
- QUARTER函数。返回日期代表的季度，数字范围为1 ~ 4。如果日期为 NULL 则返回NULL；入参仅支持 date 和 timestamp（也即datetime）类型。请参见 MySQL兼容函数。
- LAST_DAY函数。返回参数所在月份的最后一天的日期值。请参见 MySQL兼容函数。
SUBSTRING_INDEX函数。从指定字符串中返回指定分隔符出现n次数前的子字符串。请参见 MySQL兼容函数。
UUID函数。生成一个36字节的UUID字符串。请参见 MySQL兼容函数。
AES加解密。持下列AES加解密和十六进制转换操作。请参见 MySQL兼容函数。
- HEX函数。 HEX函数将bytea类型数据转成text类型。
- UNHEX函数。 UNHEX函数将varchar类型数据转成bytea类型。
- AES_ENCRYPT函数。 AES加密函数。
- AES_DECRYPT函数。 AES解密函数。
时间类型支持字符串赋值。在insert和update语句中，可以使用char(n)，varchar和text类型的值给timestamp with time zone, datetime类型的属性赋值。请参见 MySQL兼容函数。

支持动态预处理语句。

支持SET给@变量赋值。请参见用户变量。
PREPARE FROM预处理语法。请参见 PREPARE。

示例：

create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
create table my_table(a varchar(200));

SET @sql = 'INSERT INTO my_table values(''a'')';  
               @sql               
----------------------------------
 INSERT INTO my_table values('a')
(1 row)

PREPARE stmt FROM @sql;  

EXECUTE stmt;
INSERT 0 1

DEALLOCATE PREPARE stmt;

WITH ROLLUP语法，在聚合查询中生成额外行来进行数据汇总。请参见 GROUP BY 子句。

示例：

create database test_mysql with lightdb_syntax_compatible_type mysql;
\c test_mysql
CREATE TABLE score (
	id int,
	name varchar(100),
	course varchar(100),
	score int
);

INSERT INTO score VALUES(1,'zhangsan','yuwen',58);
INSERT INTO score VALUES(2,'lisi','shuxue',38);

SELECT name, COUNT(id), SUM(score) FROM score GROUP BY name WITH ROLLUP;
   name   | count | sum 
----------+-------+-----
          |     2 |  96
 lisi     |     1 |  38
 zhangsan |     1 |  58
(3 rows)

CASE WHEN类型兼容。 CASE WHEN表达式的结果集可以是字符，数值，日期。同时包含字符，数值和日期中任意两种及两种以上时，返回值的类型为字符串。请参见条件表达式。
数值类型兼容布尔类型。 integer类型的值作为布尔值使用。请参见 MySQL兼容函数。
自增序列支持NULL。请参见 MySQL兼容函数。

J.3.4. lightdb 新特性

lightdb.conf中新增guc参数修改记录。请参见通过SQL影响参数。
所有的类型，系统表，函数的oid内置不变。
调整关键字级别。请参见 SQL关键词。
模板库区分Oracle、MySQL 和Postgres模式。请参见 CREATE DATABASE 。

J.3.5. ltjdbc 增强

支持numeric转换为varchar。
支持int转换为double。
支持varchar转换为long。
支持timestamptz转换为java.time.LocalDateTime。
支持匿名块参数绑定，绑定个数最多为32767。
支持clob.setCharacterstream函数。
支持metaData的getColumnType方法返回varchar2类型。
主键冲突抛出异常java.sql.SQLIntegrityConstraintViolationException。
绑定参数使用setNull传值，类型能够正确匹配。

J.3.6. 新增关键字

pivot。

Prev	Up	Next
J.2. 版本发布 13.8-24.1	Home	J.4. 版本发布 13.8-23.3