E.1. Release 13.8-24.2
Prev UpAppendix E. Release NotesHome Next

E.1. Release 13.8-24.2

E.1.1. Oracle Compatibility
E.1.2. pl/sql enhancement
E.1.3. MySQL compatibility
E.1.4. Lightdb-x New Features

Release date: 2024-09-30

E.1.1. Oracle Compatibility

  • Allow ecpg program not to parse code block between '#if 0 ... #endif'. See Section 34.9.3

  • Enhance the Oracle Pro*C PREPARE syntax to allow following AS or ORDER token.

    Example: 

    EXEC SQL PREPARE as FROM :query;
EXEC SQL PREPARE order FROM :query;

  • Enhance the Oracle Pro*C INTO syntax to allow the main variable following INTO without a colon. Refer to Section 34.4.4

  • Enhance the Oracle Pro*C syntax to support get data from database to a char variable. Refer to Section 34.15.1

  • Enhance the Oracle Pro*C syntax to to support defining master variables in .h files. Refer to Section 34.4.3.1, Section 34.4.3.2

  • Enhance the Oracle Pro*C syntax to support fetch data to the array. Refer to Section 34.3.2

  • Enhance the Oracle Pro*C syntax, PREPARE statements are supported to contain comments ("/*" and "--" forms), double quotes;Statements can be anonymous blocks with binding parameters. Refer to PREPARE

  • Enhance the Oracle Pro*C syntax, Support statement contains header file oraca.h.

  • Supports new Oracle Pro*C syntax. See COMMIT RELEASE

  • Pro*C support insert struct array on one statment. See Section 34.4.5.3.2

  • Supports Oracle Pro*C EXEC SQL FETCH cur into :var. The number of variables can be less than the number of output fields, in which case only the output value of the number of variables will be received.

    Example: 

    exec sql begin declare section;
char *stmt1 = "SELECT c, id t FROM t1";
char str[6];
exec sql end declare section;

EXEC SQL prepare cr from :stmt1;
EXEC SQL DECLARE cur cursor for cr;
EXEC SQL open cur;

exec sql whenever not found do break;

while (1)
{
    EXEC SQL FETCH cur into :str;

    printf("%s\n", str);
}
EXEC SQL close cur;

  • The START WITH clause and CONNECT BY clause as a whole can be put before or after GROUP BY clause. See SELECT for more information.

    Example: 

    create table EMP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR(10),
  job      VARCHAR(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2),
  dname    VARCHAR(100)
);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 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('22-02-1981', 'dd-mm-yyyy'), 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('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20, null);

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10, null);

select deptno,max(sys_connect_by_path(ename,'/')) from emp where deptno in(10,20,30) start with mgr is null connect by prior empno=mgr group by deptno;
 deptno |           max           
--------+-------------------------
     20 | /KING/JONES/SCOTT/ADAMS
     30 | /KING/BLAKE/WARD
     10 | /KING/CLARK/MILLER
(3 rows)

select deptno,max(sys_connect_by_path(ename,'/')) from emp where deptno in(10,20,30) group by deptno start with mgr is null connect by prior empno=mgr;
 deptno |           max           
--------+-------------------------
     20 | /KING/JONES/SCOTT/ADAMS
     30 | /KING/BLAKE/WARD
     10 | /KING/CLARK/MILLER
(3 rows)

  • Support least function, greatest function back to the value type compatible with oracle. In oracle mode, and the first parameter type of the function is oracle.date, the original function return value type is timestamp, now the function return value type is oracle.date.

    For example: 

    lightdb@oracle=# select pg_typeof(least(sysdate,sysdate));
  pg_typeof  
-------------
 oracle.date
(1 row)

lightdb@oracle=# select pg_typeof(greatest(sysdate,sysdate));
  pg_typeof  
-------------
 oracle.date
(1 row)

  • Support nvl Function return value type compatible with oracle. see orafce.

    For example: 

    lightdb@oracle=# select pg_typeof(nvl(sysdate, '2024-09-12'::oracle.date));
  pg_typeof  
-------------
 oracle.date
(1 row)
 
lightdb@oracle=# select pg_typeof(nvl(null, '2024-09-12'::oracle.date));
  pg_typeof  
-------------
 oracle.date
(1 row)
 
lightdb@oracle=# select pg_typeof(nvl('2024-09-12'::oracle.date, null));
  pg_typeof  
-------------
 oracle.date
(1 row)

  • sysdate is of type oracle.date, which was not supported for the to_date function whose entry parameter was of type oracle.date, but is now supported. Specific usage is described below:

    1、Support to_date(oracle.date) function operation, the return value is of type oracle.date;

    2、Supported in normal sql, nested subqueries, procedures, functions, anonymous blocks, ECPG;

    Seeto_date(oracle.date)。

    For example: 

    --Take a normal sql for example
lightdb@oracle_test=# select to_date(sysdate) from dual;
       to_date       
---------------------
 2024-09-12 20:39:08
(1 row)

  • Support for new data typesbinary_integer, a data type that stores signed integers and is an alias for INT4. Specific usage descriptions are as follows:

    Support for type definitions in normal sql, packages, anonymous blocks, and table fields;

    Seebinary_integer。

    For example： 

    --Normal SQL
SELECT '123'::binary_integer;

--The binary_integer type in the package
CREATE OR REPLACE PACKAGE HSTYPE IS
	HsStatus VARCHAR2(1);
	HsFutuPrice binary_integer;
	TYPE HsChar255List is table of varchar2(255) index by binary_integer;
	END HsType;
	/
CREATE PACKAGE

--Anonymous block call
declare
  v1 binary_integer := 2147483646;
  v2 binary_integer;
begin
  v2 = v1+1;
  dbms_output.serveroutput(true);
  dbms_output.put_line('v2='||v2);
end;
/

v2=2147483647
DO

  • SEQUENCEsupports the feature of global caching instead of session caching. The usage is described below:

    1、Global continuous increment, not jump increment, when any number of sessions call the same sequence;

    2、The next value of a sequence that has been fetched, and the sql insertion error rolls back the current value, is skipped the next time the sequence is used.

    3、After the database is restarted, the sequence is consecutive;

    See CREATE SEQUENCE。

    For example: 

    -- Creating a Sequence
CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 20;

-- Creating a Test Table
CREATE TABLE test_table (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    age NUMBER,
    email VARCHAR2(100)
);

-- session1 insert some test data and submit
INSERT INTO test_table (id, name, age, email) VALUES (test_sequence.NEXTVAL, '张三', 25, 'zhangsan@example.com');
INSERT INTO test_table (id, name, age, email) VALUES (test_sequence.NEXTVAL, '李四', 30, 'lisi@example.com');

-- session2 insert data and submit
INSERT INTO test_table (id, name, age, email) VALUES (test_sequence.NEXTVAL, '王五', 28, 'wangwu@example.com');

--Query execution results
session1:
	select test_sequence.currval from dual;
	 currval 
	---------
       2
(1 row)

session2:
	select test_sequence.currval from dual;
	 currval 
	---------
       3
(1 row)

  • Supports the usage of merge into with a where clause. The usage is described as follows:

    1、Merge into statement on condition matches to a value that will go to the matched then update statement;

    2、Where condition occurs in when matched then update, merge logic is applied.

    merge intosyntax，seeMERGE INTO。

    For example： 

    
-- Test merge with where clause
truncate target;
truncate source;
alter table target add primary key(tid);
INSERT INTO target VALUES (1, 10);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 30);
INSERT INTO source VALUES (1, 11);
INSERT INTO source VALUES (5, 55);

--Perform the MERGE INTO operation
MERGE INTO target t
USING source s
ON (t.tid = s.sid)
WHEN MATCHED THEN
  UPDATE
     SET t.balance = s.delta where 1 = 2
WHEN NOT MATCHED THEN
  INSERT (t.tid, t.balance) VALUES (s.sid, s.delta);
  
--Query execution results
SELECT * FROM target ORDER BY tid;
 tid | balance 
-----+---------
   1 |      10
   2 |      20
   3 |      30
   5 |      55
(4 rows)

  • Support for sql usage of merge into update set delete. Specific usage is described below:

    1、Added support for merge into when matched delete clauses;

    2、If the condition in on() matches, the update statement is executed; if the condition in on() matches and the where condition also matches, the delete statement is executed.

    3、Both normal sql and stored procedures support sql statements;

    merge into update set deletesyntax，seeMERGE INTO。

    For example： 

    --Create two tables, a,b
create table a (id1 number,id2 number,name varchar(20));
insert into a values (1,1,'aa');
create table b (id1 number,id2 number,name varchar(20));
insert into b values (1,2,'aa');

--Execute merge into update set delete语句
merge into a using b on (a.id1 = b.id1)
when matched then update set a.name = 'bb'
delete where a.id1 = b.id1;

--Query execution results
select * from a;
 id1 | id2 | name 
-----+-----+------
(0 rows)

  • Support for ORDER BY statements to concatenate column alias expressions. The usage is described below:

    1、Support ORDER BY followed by a column alias expression, the expression contains addition, subtraction, multiplication, division, function, case when judgement, except for the aggregation function, the column alias can be a constant alias;

    2、A column alias can be an alias for a specific column in a table;

    Please refer to ORDER BY 。

    For example： 

    Example 1: ORDER BY followed by a function expression
--Create a table，EMP
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 values (7782, 'CLARK',  'MANAGER',  7839, to_date('1981-06-09','yyyy-mm-dd'), 2450.00, null, 10, null);
insert into EMP values (7788, 'SCOTT',  'ANALYST',  7566, to_date('1987-04-19','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP values (7839, 'KING',   'PRESIDENT',null, to_date('1981-11-17','yyyy-mm-dd'), 5000.00, null, 10, null);
insert into EMP values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','yyyy-mm-dd'), 1500.00, 0.00, 30, null);
insert into EMP values (7876, 'ADAMS',  'CLERK',    7788, to_date('1987-05-23','yyyy-mm-dd'), 1100.00, null, 20, null);
insert into EMP values (7900, 'JAMES',  'CLERK',    7698, to_date('1981-12-03','yyyy-mm-dd'), 950.00,  null, 30, null);
insert into EMP values (7902, 'FORD',   'ANALYST',  7566, to_date('1981-12-03','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP values (7934, 'MILLER', 'CLERK',    7782, to_date('1982-01-23','yyyy-mm-dd'), 1300.00, null, 10, null);

--Executable ORDER BY statement
select e.*,
       (case when e.mgr is null then '公司老板不领工资'
            else (case when e.sal < 1000  then '低收入者'  else '高收入精英' end) end) as company_level
from emp e order by length(company_level);

--Query execution results
 empno | ename  |    job    | mgr  |      hiredate       | sal  | comm | deptno | dname |  company_level   
-------+--------+-----------+------+---------------------+------+------+--------+-------+------------------
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 |      |     30 |       | 低收入者
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 |      |     20 |       | 高收入精英
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 |       | 高收入精英
  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 |      |     20 |       | 高收入精英
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |      |     10 |       | 高收入精英
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 |      |     10 |       | 高收入精英
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 |      |     20 |       | 高收入精英
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000 |      |     10 |       | 公司老板不领工资
(8 rows)

Example 2: A column alias is an alias for a specific column in a table. For example, the emp table's ename.
select e.*,e.ename as e_name from emp e order by length(e_name);

--Query execution results
 empno | ename  |    job    | mgr  |      hiredate       | sal  | comm | deptno | dname | e_name 
-------+--------+-----------+------+---------------------+------+------+--------+-------+--------
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000 |      |     10 |       | KING
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 |      |     20 |       | FORD
  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 |      |     20 |       | ADAMS
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 |      |     30 |       | JAMES
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 |      |     10 |       | CLARK
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 |      |     20 |       | SCOTT
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 |       | TURNER
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |      |     10 |       | MILLER
(8 rows)

  • Support forCONNECT BYsubqueries。Specific usage instructions are as follows：

    1、Support connect by subquery with pseudo-column rownum;

    2、Support for start with connect by option;

    3、Support forconnect by prior option;

    Please refer to CONNECT BY 。

    For example： 

    --Create a table，EMP
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 values (7782, 'CLARK',  'MANAGER',  7839, to_date('1981-06-09','yyyy-mm-dd'), 2450.00, null, 10, null);
insert into EMP values (7788, 'SCOTT',  'ANALYST',  7566, to_date('1987-04-19','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP values (7839, 'KING',   'PRESIDENT',null, to_date('1981-11-17','yyyy-mm-dd'), 5000.00, null, 10, null);
insert into EMP values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','yyyy-mm-dd'), 1500.00, 0.00, 30, null);
insert into EMP values (7876, 'ADAMS',  'CLERK',    7788, to_date('1987-05-23','yyyy-mm-dd'), 1100.00, null, 20, null);
insert into EMP values (7900, 'JAMES',  'CLERK',    7698, to_date('1981-12-03','yyyy-mm-dd'), 950.00,  null, 30, null);
insert into EMP values (7902, 'FORD',   'ANALYST',  7566, to_date('1981-12-03','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP values (7934, 'MILLER', 'CLERK',    7782, to_date('1982-01-23','yyyy-mm-dd'), 1300.00, null, 10, null);

--Execution connect by sql statement 
select * from ( select ename,rownum from emp a ) t connect by level < 2;
 ename  | rownum 
--------+--------
 CLARK  |      1
 SCOTT  |      2
 KING   |      3
 TURNER |      4
 ADAMS  |      5
 JAMES  |      6
 FORD   |      7
 MILLER |      8
(8 rows)

  • Window Function Support distinct de-duplication. Specific usage instructions are as follows：

    1、Aggregate Function Support distinct de-duplication;

    2、The scope of the window aggregation function contains：count() over、max() over()、avg() over()、min() over()、sum() over();

    Please refer to distinct 。

    示例： 

    --Query the correct result
select count(distinct e.sal) over(partition by deptno) from emp e;
select max(distinct e.sal) over(partition by deptno) from emp e;
select avg(distinct e.sal) over(partition by deptno) from emp e;
select min(distinct e.sal) over(partition by deptno) from emp e;
select sum(distinct e.sal) over(partition by deptno) from emp e;

  • Added 1 new optimiser hint/*+index(table_name index_name)*/, Subquery that pushes the specified index down after from.

    1、Support for subqueries that push down after from。for example：select /*+index(x index_name)+*/ * from (select * from (select * from (select * from table_name) a) b) x where key1 = 1 and key2 = 2 and key3 = 3;

    2、Supports a minimum of 3 levels of nesting;

    For specific usage, see lt_hint_plan。

    For example： 

    --Create tables、indexes
create table test_index_hint_push(key1 int, key2 int, key3 int, key4 int);
create index i_test_index_hint_push_key1 on test_index_hint_push(key1);
create index i_test_index_hint_push_key2 on test_index_hint_push(key2);
create index i_test_index_hint_push_key3 on test_index_hint_push(key3);
--analysed result
1.Push down without specifying an index
EXPLAIN (COSTS false) select * from (select * from (select * from (select * from test_index_hint_push) a) b) x where key1 = 1 and key2 = 2 and key3 = 3;
                          QUERY PLAN                          
--------------------------------------------------------------
 Bitmap Heap Scan on test_index_hint_push
   Recheck Cond: ((key3 = 3) AND (key2 = 2))
   Filter: (key1 = 1)
   ->  BitmapAnd
         ->  Bitmap Index Scan on i_test_index_hint_push_key3
               Index Cond: (key3 = 3)
         ->  Bitmap Index Scan on i_test_index_hint_push_key2
               Index Cond: (key2 = 2)
(8 rows)

2.Specify index push down
EXPLAIN (COSTS false) select/*+index(x i_test_index_hint_push_key1)+*/ * from (select * from (select * from (select * from test_index_hint_push) a) b) x where key1 = 1 and key2 = 2 and key3 = 3;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Scan using i_test_index_hint_push_key1 on test_index_hint_push @"lt#3"
   Index Cond: (key1 = 1)
   Filter: ((key2 = 2) AND (key3 = 3))
(3 rows)

  • Added （ECPG(Oracle Pro*c compatible)）features。 please refer to ECPG(Oracle Pro*c compatible)。

    In ECPG, commit and rollback transaction commits are supported for anonymous block calls to stored procedures with DML operations. This is described as follows：

    1、When an anonymous block calls a stored function with DML operations (including add, delete, and change) and commits, it goes to report the success of the transaction commit within the function;

    2、Anonymous block call with DML operations (including add, delete, change), when an exception occurs, you need to rollback, to ensure that the success of the function rollback；

    For example： 

    --Construct table EMP, update the salary for a row, and do a commit/rollback operation
CREATE OR REPLACE FUNCTION update_employee_salary(  
  p_EMPNO EMP.EMPNO%type,  
  p_SAL EMP.SAL%type 
)
return NUMBER
AS  
BEGIN  
    update EMP 
    set SAL =  p_SAL
     WHERE EMPNO=p_EMPNO;
  COMMIT;  
RETURN 0;
EXCEPTION  
  WHEN OTHERS THEN  
    ROLLBACK;  
RETURN -1;
END

int main(void)
{
	EXEC SQL BEGIN DECLARE SECTION;
	int ret = -1;
	int p_EMPNO = 7499;
	int p_SAL = 8888;
	EXEC SQL END DECLARE SECTION;
	
	EXEC SQL CONNECT TO oracle_test@192.168.3.71:5432 AS conn1 USER lightdb USING "lightdb";
	if(sqlca.sqlcode<0){
	   perror("connect err");
	   printf("%d\n",sqlca.sqlcode);
	   return -1;
	}
	else
	{
		printf("connect success!\n");
	}
	
    EXEC SQL EXECUTE
		  BEGIN
			  :ret = update_employee_salary(:p_EMPNO,:p_SAL);
		  END；
    END-EXEC;
    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT conn1;
    return 0;
}

  • Nested applications of aggregation functions where the outer layer does MAX/MIN/AVG/COUNT/SUM and the inner layer does count(*) are supported in ECPG. The details are described below:

    Take max(count(*)) for example;

    1、Supports max(count(*)) usage and outputs correct results

    2、Nested application scope of aggregation functions, support for normal sql, functions, stored procedures, ECPG;

    For example： 

    --Create table TEST1
CREATE TABLE TEST1(id int, name VARCHAR2(30));
INSERT INTO TEST1 VALUES(1,'AA');
INSERT INTO TEST1 VALUES(2,'BB');
INSERT INTO TEST1 VALUES(3,'CC');
INSERT INTO TEST1 VALUES(4,'DD');
INSERT INTO TEST1 VALUES(5,'EE');
INSERT INTO TEST1 VALUES(6,'FF');
INSERT INTO TEST1 VALUES(7,'GG');
INSERT INTO TEST1 VALUES(30,'AA');
INSERT INTO TEST1 VALUES(31,'BB');
INSERT INTO TEST1 VALUES(32,'CC');
INSERT INTO TEST1 VALUES(33,'DD');
INSERT INTO TEST1 VALUES(50,'AA');
INSERT INTO TEST1 VALUES(60,'AA');
INSERT INTO TEST1 VALUES(34,'EE');

--In ECPG, the TEST1 tables are grouped and summed by name and then maximised
EXEC SQL BEGIN DECLARE SECTION;
int  i,j,k;
EXEC SQL END DECLARE SECTION;
int main(void)
{
	EXEC SQL CONNECT TO oracle_test@192.168.3.71:5508 AS conn1 USER lightdb USING "lightdb";
	
	if(sqlca.sqlcode<0){
	   perror("connect err");
	   printf("%d\n",sqlca.sqlcode);
	   return -1;
	}
	
	EXEC SQL SELECT count(*) INTO :i from TEST1;
  EXEC SQL SELECT MAX(count(*))   INTO :j from TEST1 GROUP BY NAME; 
  EXEC SQL SELECT NVL(MAX(count(*)),0)   INTO :k from TEST1 GROUP BY NAME;
  printf("i=%d,j=%d,k=%d\n",i,j,k);
  EXEC SQL COMMIT;
  EXEC SQL DISCONNECT conn1;
  return 0;
}

  • In ECPG, support for Oracle Dynamic SQL Method 4 Dynamic SQL Method 4。 For sql statements where neither the query list nor the number of host variables can be determined. Specific instructions are given below:

    1、Support for sqlda structure creation (a data structure describing SQL variables and indicator variables, containing a series of pointers and lengths for storing and describing information about variables used in SQL queries); 

                        struct SQLDA
                    {
                        long    N;             /* 描述符大小（以条目数表示） */
                        char  **V;             /* 指向主变量地址数组的指针 */
                        long   *L;              /* 指向缓冲区长度数组的指针 */
                        short  *T;              /* 指向缓冲区类型数组的指针 */
                        short **I;               /* 指向指示器变量地址数组的指针 */
                        long    F;                /* 通过DESCRIBE操作找到的变量数量 */
                        char  **S;               /* 指向变量名指针数组的指针 */
                        short  *M;               /* 指向变量名最大长度数组的指针 */
                        short  *C;               /* 指向变量名当前长度数组的指针 */
                        char  **X;               /* 指向指示器变量名指针数组的指针 */
                        short  *Y;               /* 指向指示器变量名最大长度数组的指针 */
                        short  *Z;               /* 指向指示器变量名当前长度数组的指针 */
                    };

    2、Support for SQL syntax (initialising descriptors to hold select list items or entering descriptions of host variables)

    语法：EXEC SQL DESCRIBE BIND VARIABLES FOR sql_stmt INTO bind_des；

    3、SQL syntax support (examine each select list item in the PREPAREd dynamic query to determine its name, data type, constraint, length, number of decimal places, and precision. He then stores this information in the selection descriptor)

    Syntax: EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name INTO select_descriptor_name；

    4、SQL syntax support (read array_size rows from selection descriptor via cursor)

    Syntax: EXEC SQL FOR :array_size FETCH Cursorbase USING DESCRIPTOR select_descriptor_name；

  • In ECPG supports the definition of context variables, and the establishment of connections to the user's global runtime context, the allocation of runtime contexts, and the use of runtime contexts. The details are described below:

    1、Create a database connection to the specified user via EXEC SQL CONNECT :usr1;

    2、Adoption of EXEC SQL CONTEXT ALLOCATE :ctx1;（Allocate a runtime context ctx1）;

    3、Adoption of EXEC SQL CONTEXT USE :ctx1;(using the assigned runtime context ctx1) to reach the information to get ctx1, including cursor, session ID, username;

    Adoption of EXEC SQL CONTEXT USE DEFAULT; Restore the default runtime context, including cursors, session IDs, usernames.

    Please refer to ECPG(SQL-CONTEXT)。

  • In ECPG, there is support for the possibility of not declaring host variables in EXEC SQL BEGIN DECLARE SECTION;EXEC SQL END DECLARE SECTION; statements.

    Please refer to ECPG(DECLARE-SECTION)。

  • In ECPG, add EXEC SQL CONNECT connection string Oracle compatible format. Specific instructions are given below:

    The following two ECPG connection string formats are supported:

    1、Support EXEC SQL connect : username/passwd; (where the presence or absence of a space between ’:’ and username/passwd is supported)

    2、Support EXEC SQL connect : username/passwd@sdbname; (where the presence or absence of a space between ’:’ and username/passwd is supported)

    For example： 

    --For example: EXEC SQL connect : username/passwd@sdbname
int main() 
{
    char *uid = "test/test@gbk";
    EXEC SQL CONNECT :uid;
    if(sqlca.sqlcode == 0)
    {
        printf("gbk conect success！\n");
    }
    else
    {
        printf("%d,login failed\n",sqlca.sqlcode);
    }
    exit(0);
}

  • In ECPG, using in open cursor binds a variable, and the same variable can be assigned with the same name. The details are as follows:

    1、When you use using to bind variables, you can assign the same name to the same bound variable (unlimited number);

    2、The data that can be read by the cursor is the same as the actual data;

    For example： 

    --Take the example of four binding variables with the same name after using
int main() 
{
	EXEC SQL BEGIN DECLARE SECTION;
	char sSQL[1000];
	int sMac = 0;
	int a = 0;
	int b = 0;
	EXEC SQL END DECLARE SECTION;
	EXEC SQL CONNECT TO oracle_test@192.168.3.71:5602 AS conn1 USER lightdb USING "lightdb";
	if(sqlca.sqlcode<0){
	   perror("connect err");
	   printf("%d\n",sqlca.sqlcode);
	   return -1;
	}
	sMac = 4;
	memset(sSQL,'\0',sizeof(sSQL));
	snprintf(sSQL,sizeof(sSQL),
	"select a,b from t1 where b = :sMac" 
	" union " 
	"select a,b from t2 where b = :sMac" 
	" union " 
	"select a,b from t3 where b = :sMac" 
	" union " 
	"select a,b from t4 where b = :sMac");
	EXEC SQL prepare s from :sSQL;
	EXEC SQL declare dycur cursor for s;
	EXEC SQL open dycur using :sMac;
	while(1)
	{
		EXEC SQL FETCH dycur into :a,:b;
		if (sqlca.sqlcode==1403||sqlca.sqlcode==ECPG_NOT_FOUND || sqlca.sqlcode <0)
		{
			printf("游标查询结束，退出！sqlca.sqlcode = %d\n",sqlca.sqlcode);
			break;
		}
  }
    EXEC SQL CLOSE dycur;
	  EXEC SQL DISCONNECT conn1;
    exit(0);
}

  • In ECPG, host variables support space, colon, and parentheses writing for normal type bound variable names and support space, colon, parentheses, and asterisk writing for pointer type bound variable names. Specific instructions are given below:

    1、Ordinary variables: add, delete, change and check scenarios, support binding variables with spaces (the number of spaces is not limited), write with brackets, ignore brackets, spaces, and can successfully run the correct results;

    2、Pointer variables: add, delete, change and check scenarios, support for bound variables of pointer type (pointer type supports int type, char type, double type, first-class pointers), and also supports writing with spaces (the number of spaces is not limited), with brackets, and with the * sign, and successfully run out the correct results;

    For example： 

    --For example
int main() 
{
		char *uid = "test/test@10.20.30.193:1521/test";		
		EXEC SQL BEGIN DECLARE SECTION;
	  int dept_id = 80;   // 假设要查询的员工ID  
	  char first_name[51];     // 预留一个字符给字符串结束符'\0'  
		int *salary;
		int temp = 0;
		EXEC SQL END DECLARE SECTION;
		
		EXEC SQL CONNECT :uid;
	  if(sqlca.sqlcode == 0)
	  {
	      printf("conect success！\n");
	  }
	  else
	  {
        printf("%d,login failed\n",sqlca.sqlcode);
	  }
		
		EXEC SQL WHENEVER SQLERROR DO sql_error("LightDB error--");		
		 // Initialising host variables
	  strcpy(first_name, "Unknown");  
		salary = &temp;
	  
	  // 1、Execute SQL query, use bind variable Space, * sign
	  EXEC SQL SELECT ename, sal  
	             INTO : first_name, :*salary  
	             FROM emp  
	             WHERE DEPTNO = :dept_id;  
		
		// Output query results
		printf("test1:*号\n"); 
	  printf("First Name: %s\n", first_name);  
	  printf("sal*: %d,sqlrr = %d\n", *salary,sqlca.sqlcode);

  • In ECPG, host variables support space, colon, and parentheses writing for normal type bound variable names and support space, colon, parentheses, and asterisk writing for pointer type bound variable names. The details are described below:

    1、Ordinary variables: add, delete, change and check scenarios, support binding variables with spaces (the number of spaces is not limited), write with brackets, ignore brackets, spaces, and can successfully run the correct results;

    2、Pointer variables: add, delete, change, and check scenarios, support for pointer-type bound variables (pointer types support int, char, double, and first-class pointers), as well as support for writing with spaces (the number of spaces is unlimited), with parentheses, and with the * sign, and successfully run out of the correct results;

    For example： 

    --For example
int main() 
{
		char *uid = "test/test@10.20.30.193:1521/test";		
		EXEC SQL BEGIN DECLARE SECTION;
	  int dept_id = 80;   // 假设要查询的员工ID  
	  char first_name[51];     // 预留一个字符给字符串结束符'\0'  
		int *salary;
		int temp = 0;
		EXEC SQL END DECLARE SECTION;
		
		EXEC SQL CONNECT :uid;
	  if(sqlca.sqlcode == 0)
	  {
	      printf("conect success！\n");
	  }
	  else
	  {
        printf("%d,login failed\n",sqlca.sqlcode);
	  }
		
		EXEC SQL WHENEVER SQLERROR DO sql_error("LightDB error--");		
		 // Initialising host variables
	  strcpy(first_name, "Unknown");  
		salary = &temp;
	  
	  // 1、Execute SQL query, use bind variable Space, * sign 
	  EXEC SQL SELECT ename, sal  
	             INTO : first_name, :*salary  
	             FROM emp  
	             WHERE DEPTNO = :dept_id;  
		
		// Output query results  
		printf("test1:*号\n"); 
	  printf("First Name: %s\n", first_name);  
	  printf("sal*: %d,sqlrr = %d\n", *salary,sqlca.sqlcode);

  • In oracle mode,ltsql -f imported sql file will remove \r.

  • In oracle mode,Like conditional statements are followed by a backslash to remove the default escape character.

    For example： 

    lightdb@oracle=# CREATE TABLE test_char(id NUMBER, c_test1 VARCHAR2(20), c_test2 VARCHAR2(20));
WARNING:  LightDB DDL check warn! no primary key!
DETAIL:  If your system does not have data replication requirement, just ignore it
CREATE TABLE
lightdb@oracle=# insert into test_char values(1,'a\1','a/1');
INSERT 0 1
lightdb@oracle=# insert into test_char values(2,'b\\2','a//2%');
INSERT 0 1
lightdb@oracle=# insert into test_char values(3,'c\3','a/3');
INSERT 0 1
lightdb@oracle=# insert into test_char values(4,'d\\\4','a///4');
INSERT 0 1
lightdb@oracle=# insert into test_char values(5,'e=+4%','e/-4');
INSERT 0 1
lightdb@oracle=# insert into test_char values(6,'f=00\4','f+-094');
INSERT 0 1
lightdb@oracle=# insert into test_char values(7,'gdf4','g/+))4');
INSERT 0 1
lightdb@oracle=# insert into test_char values(8,'hll4','h//kkk4');
INSERT 0 1
lightdb@oracle=# insert into test_char values(9,'hll4%%','h//kkk4%%%');
INSERT 0 1
lightdb@oracle=# insert into test_char values(10,'\','/');
INSERT 0 1
lightdb@oracle=# select id,c_test1 from test_char where c_test1 like '\';
 id | c_test1 
----+---------
 10 | \
(1 rows)

lightdb@oracle=# select id,c_test1 from test_char where c_test1 like '\\'; 
 id | c_test1 
----+---------
(0 rows)

lightdb@oracle=# select id,c_test1 from test_char where c_test1 like '%\%';
 id | c_test1 
----+---------
  1 | a\1
  2 | b\\2
  3 | c\3
  4 | d\\\4
  6 | f=00\4
 10 | \
(6 rows)

lightdb@oracle=# select id,c_test1 from test_char where c_test1 like '%\\%';
 id | c_test1 
----+---------
  2 | b\\2
  4 | d\\\4
(2 rows)

E.1.2. pl/sql enhancement

  • Nested tables and associative arraysupportrecord_var%type 

    CREATE OR REPLACE PROCEDURE p_greet()
IS
  type record_paysect is record(
    n1 number,
    n2 number
  );

    type_record_paysect record_paysect;
    type v_paysect_ar is table of type_record_paysect%type;

    paysect v_paysect_ar;

BEGIN
  paysect(1).n1 := 123;
  paysect(2).n2 := 456;
  DBMS_OUTPUT.PUT_LINE('paysect, ' || paysect.count || '!');  
  DBMS_OUTPUT.PUT_LINE('paysect(1).n1 =  ' || paysect(1).n1);  
  DBMS_OUTPUT.PUT_LINE('paysect(2).n2 =  ' || paysect(2).n2);  

END p_greet;
/
call p_greet();
paysect, 2!
paysect(1).n1 =  123
paysect(2).n2 =  456

  • Compatible with Oracleupdate return into behaviour, does not report exceptions when there are no matching rows. The usage is described below:

    1、When executing update XX SET A=B FROM…WHERE..RETURN INTO When update is performed and no updatable rows are found, an exception is not thrown, and the process proceeds as normal.

    2、The above behaviour is supported in functions, anonymous blocks, and stored procedures;

    For example： 

    --If update is not found, it will not jump to exception, but return normally;
CREATE OR REPLACE FUNCTION update_employee_salary3(  
  p_EMPNO EMP.EMPNO%type,  
  p_SAL EMP.SAL%TYPE,
  inout salout EMP.SAL%TYPE
)
return NUMBER
AS  
BEGIN  
    update EMP 
    set SAL =  p_SAL+1
     WHERE EMPNO=p_EMPNO
     return SAL into salout;
    dbms_output.put_line('函数内：执行结果是'||salout);
	RETURN 0;
EXCEPTION  
  WHEN OTHERS THEN  
    ROLLBACK;  
	RETURN -1;
END;
/
select dbms_output.serveroutput(true);
declare 
	EMPNO numeric := 7369;
	SAL numeric := 3370;
	RET numeric := -1000;
	SALOUT numeric := -1000;
 begin
     RET:=update_employee_salary3(EMPNO,SAL,SALOUT);
     dbms_output.put_line('执行结果1是'||RET);
     dbms_output.put_line('执行结果2是'||SALOUT);
 end;
 /

--Result Output
Inside the function: the execution result is 3371
Execution result 1 is 0
Execution result 2 is 3371
DO

  • Anonymous blocks support SQLCODE variables and SQLERRM function usage syntax. The usage is described below:

    1、Supported scopes are anonymous code blocks, functions, procedures；

    2、SQLCODE is used outside the exception block and results in 0;

    3、SQLERRM supports use outside of an exception block;

    Please refer to support SQLCODE。

    For example： 

    --1、anonymous block
begin  
    begin  
        null;  
    exception  
        when others then  
            dbms_output.put_line(sqlcode);  
    end;  
dbms_output.put_line(sqlcode);  
dbms_output.put_line(sqlerrm);
end;  
/

--Query execution results
ZHANGSAN
LISI
WANGWU
DO

--2、procedure
create procedure sqlcode_test  
as  
begin  
    begin  
        null;  
    exception  
        when others then  
            dbms_output.put_line(sqlcode);  
    end;  
dbms_output.put_line(sqlcode); 
dbms_output.put_line(sqlerrm); 
end;  
/  

--3、function
create function sqlcode_test1() return int as  
begin  
    begin  
        null;  
    exception  
        when others then  
            dbms_output.put_line(sqlcode);  
    end;  
dbms_output.put_line(sqlcode);  
dbms_output.put_line(sqlerrm); 
    return 1;  
end;  
/

  • execute immediatesupports following variable assignment statements. If there are multiple positional variables with the same name, they represent the same object, i.e., the same parameter in the using clause. Specific usage is described below:

    1、Anonymous blocks, functions or stored procedures use dynamic SQL to call normal SQL, e.g：execute immediate 'insert into test values (:v2, :v1, :v1, :v3)' using 1,2,3,4; placeholders are used for data insertion, and placeholders with the same name are not equivalent.

    2、An anonymous block, function, or procedure enables dynamic SQL to call an anonymous block to perform an insertion of data, where the parameters (independent of the type of the variable following the USING) correspond in order to their position in the dynamic SQL statement in the USING clause. If more than one positional variable in the dynamic SQL statement has the same name, the corresponding variables in the USING clause share the same value;

    3、Bound variables only support :x, not : x or : xx, i.e., colons and spaces between variable names are not supported. ;

    Please refer to Execute dynamic commands。

    For example： 

    CREATE OR REPLACE procedure ttyout1 (id1 in int,id out varchar)
iS 
begin
id := id1 || '3';
dbms_output.put_line(id);
end;
/ 
create or replace function tty(id in int,name2 out varchar) return int as 
    a1 int := 11;
  a2 int :=13;
begin
  dbms_output.put_line(id || name2);
  name2 := name2 || '1';
  dbms_output.put_line(id || name2);
return id;
end;
/

declare
    a1 int := 11;
  a2 varchar(10) := '11';
  a3 int := 231;
  a4 varchar(10) := '11';
  a5 int := 56;
  a6 int := 23;
  a7 varchar(10) := '11';
  a8 varchar(10) := '11';
begin
    execute immediate
        'begin
      ttyout1(:qwe,:abc,:xx); -- a1 join，a2 leave the club
      :a := tty(:q2,:r4); -- join a5 and a4，assign the result to a3 and a4
         end;' using in a1, out a2, out a3, in a5, out a4;
end;
/

  • After the success of CREATE FUNCTION, the return value type of the function after the execution of CREATE OR REPLACE again can be different from the return value type after the first CREATE FUNCTION, and the function can be created successfully again. Specific usage is described below:

    1、Support for same-name, same-parameter functions with different return values created using create or replace；

    2、Unrestricted return value type change, such as cursor->number，number->varchar are supported;

    Please refer to create function。

    For example： 

    --The following three functions with the same name and different return value types were all created successfully.
CREATE OR REPLACE FUNCTION get_employee_data RETURN DATE IS
    emp_date DATE;
BEGIN
    SELECT MAX(hire_date) INTO emp_date FROM your_table WHERE conditions;
    RETURN emp_date;
END get_employee_data;
/

CREATE OR REPLACE FUNCTION get_employee_data RETURN RAW IS
    emp_raw_data RAW(100); -- Adjust size according to your needs
BEGIN
    -- Retrieve some raw data, you can customize this query
    SELECT RAW_COLUMN INTO emp_raw_data FROM your_table WHERE conditions;

    -- Return the raw data
    RETURN emp_raw_data;
END get_employee_data;
/

CREATE OR REPLACE FUNCTION get_employee_data RETURN INTERVAL DAY TO SECOND IS
    emp_interval INTERVAL DAY TO SECOND;
BEGIN
    SELECT (SYSDATE - hire_date) INTO emp_interval FROM employees WHERE conditions;
    RETURN emp_interval;
END get_employeel_data;
/

  • CREATE OR REPLACE FUNCTION support for replacing the in and inout types of functions with the out type.

    For example： 

    CREATE OR REPLACE FUNCTION get_employee_salary (p_employee_id INOUT NUMBER)  
RETURN NUMBER  
IS  
     type record_paysect is record(
    C number,  --面值
    begin_date number, --周期开始日期
    n1 number,  --本段开始支付周期
    n2 number,  --本段结束支付周期
    R1 number,  --本段剩余面值比例
    R2 number); --本段支付面值比例
    
    type v_paysect_ar is table of record_paysect index by binary_integer;
    paysect v_paysect_ar; 
    
BEGIN  
    paysect(1).C := 123;
	paysect(2).n1 := 456;

    RETURN paysect.count;  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        RETURN NULL; 
    WHEN OTHERS THEN 
        RAISE; 
END;  
/

--Replace the get_employee_salary function entry type from inout to out..
CREATE OR REPLACE FUNCTION get_employee_salary (p_employee_id OUT NUMBER)  
RETURN NUMBER  
IS  
     type record_paysect is record(
    C number,  --面值
    begin_date number, --周期开始日期
    n1 number,  --本段开始支付周期
    n2 number,  --本段结束支付周期
    R1 number,  --本段剩余面值比例
    R2 number); --本段支付面值比例
    
    type v_paysect_ar is table of record_paysect index by binary_integer;
    paysect v_paysect_ar; 
    
BEGIN  
    paysect(1).C := 123;
	paysect(2).n1 := 456;

    RETURN paysect.count;  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        RETURN NULL; 
    WHEN OTHERS THEN 
        RAISE; 
END;  
/

  • Support the use of double quotes when creating tables, table names in upper case, when querying the table can be queried directly, that is, without double quotes in lower case can also be queried normally, compatible with oracle. 

    CREATE TABLE "ABCD" (id int, "A" varchar(100));
SELECT ID,A from abcd; --searchable
SELECT * from ABCD;--searchable
SELECT * from "ABCD";--searchable
SELECT ID,"A" from abcd; --searchable
SELECT ID,"a" from ABcd;

CREATE TABLE "a123" (id int);
select * from "a123"; --searchable
select * from a123;   --searchable
select * from A123;   --searchable

  • Support for timestamp(N)、date、timestamp with time zone、INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH time and interval type fields allows fuzzy matching using like ‘%%’. 

    CREATE TABLE caofa_date (
    d1 TIMESTAMP,
    d2 DATE,
    d3 TIMESTAMP WITH TIME ZONE,
	d4 TIMESTAMP WITHOUT TIME ZONE,
	d5 INTERVAL DAY TO SECOND,
	d6 INTERVAL YEAR TO MONTH
); 
INSERT INTO caofa_date (d1, d2, d3, d4, d5, d6)
VALUES (
    SYSDATE,
    SYSDATE,
	SYSDATE,
	SYSDATE,
    INTERVAL '5' DAY, 
    INTERVAL '2-3' YEAR TO MONTH 
); 
--Matchable
select * from caofa_date;
select * from caofa_date where d1 like '%2024%';
select * from caofa_date where d2 like '%2024%';
select * from caofa_date where d3 like '%2024%';
select * from caofa_date where d4 like '%2024%';
select * from caofa_date where d5 like '%day%';
select * from caofa_date where d6 like '%years%';
--Matchable
select * from caofa_date where d1 not like '%2024%';
select * from caofa_date where d2 not like '%2024%';
select * from caofa_date where d3 not like '%2024%';
select * from caofa_date where d4 not like '%2024%';
select * from caofa_date where d5 not like '%day%';
select * from caofa_date where d6 not like '%years%';

  • supports GENERATED ALWAYS AS, this clause creates the column as generated column. the column cannot be written to, and the result of the specified expression is returned when read.

    Generated expressions can reference other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed. 

    CREATE TABLE ga_c_t5 (N VARCHAR2(100), C1 VARCHAR2(20) INVISIBLE GENERATED ALWAYS AS (substr(n,1,10)));
INSERT INTO ga_c_t5 (N) SELECT num_to_az_string(level * 999999999999999, 12) FROM dual connect by LEVEL < 10;
SELECT * FROM ga_c_t5 ORDER BY N;
DROP TABLE ga_c_t5;

--Support for specifying constraints UNIQUE
CREATE TABLE ga_c_t6 (N VARCHAR2(100), C1 VARCHAR2(20) GENERATED ALWAYS AS (substr(n,1,10)) UNIQUE);
INSERT INTO ga_c_t6 (N) SELECT num_to_az_string(level * 999999999999999, 12) FROM dual connect by LEVEL < 10;
SELECT * FROM ga_c_t6 ORDER BY N;
DROP TABLE ga_c_t6;

--Support for specifying constraints CHECK
CREATE TABLE ga_c_t7 (N VARCHAR2(100), C1 VARCHAR2(20) GENERATED ALWAYS AS (substr(n,1,10)) CHECK(substr(c1, 1, 1) != '#'));
INSERT INTO ga_c_t7 (N) SELECT num_to_az_string(level * 999999999999999, 12) FROM dual connect by LEVEL < 10;
SELECT * FROM ga_c_t7 ORDER BY N;
DROP TABLE ga_c_t7;

--Support for specifying constraints REFERENCE
CREATE TABLE ga_c_t8_other ( id VARCHAR2(10) PRIMARY KEY );
CREATE TABLE ga_c_t8 (
    N VARCHAR2(100),
    C1 VARCHAR2(20) GENERATED ALWAYS AS (substr(n,1,10)) REFERENCES ga_c_t8_other(id)
);
INSERT INTO ga_c_t8_other (id) VALUES ('aaa');
INSERT INTO ga_c_t8 (N) VALUES ('aaa');
INSERT INTO ga_c_t8 (N) VALUES ('aaab'); -- should fail
DROP table ga_c_t8;
DROP table ga_c_t8_other;

  • Support for NLSSORT functions. 

    drop table if exists testpinyin1;
create table testpinyin1(a varchar2(20),b varchar(20),c text,d clob);
insert into testpinyin1 values('阿','阿','阿','阿');
insert into testpinyin1 values('爱','爱','爱','爱');
insert into testpinyin1 values('ＡＡ','ＡＡ','ＡＡ','ＡＡ');
insert into testpinyin1 values('Ｇ传化','Ｇ传化','Ｇ传化','Ｇ传化');

SELECT * FROM testpinyin1 ORDER BY NLSSORT(a, 'NLS_SORT=SCHINESE_PINYIN_M');
SELECT * FROM testpinyin1 ORDER BY NLSSORT(b, 'NLS_SORT=SCHINESE_PINYIN_M');

  • update statement supports the join query statement. 

    explain (costs off)
update (select * from  (select * from t1) left join t3 on a1 = a3 left join t2 on a2 = a3 )
set c1 = c3 where b1 =3;
ERROR:  Upper-level Var found where not expected


explain (costs off)
update (select * from  t1 left join t3 on a1 = a3 left join t2 on a2 = a3 )
set c1 = c3 where b1 =3;
                    QUERY PLAN
--------------------------------------------------
 Update on t1
   ->  Hash Right Join
         Hash Cond: (t2.a2 = t3.a3)
         ->  Seq Scan on t2
         ->  Hash
               ->  Hash Right Join
                     Hash Cond: (t3.a3 = t1.a1)
                     ->  Seq Scan on t3
                     ->  Hash
                           ->  Seq Scan on t1
                                 Filter: (b1 = 3)
(11 rows)

explain (costs off) 
update (select * from  t1, t2, t3 where a2(+) = a1 and a2 = a3(+) ) 
set c1 = 1;
                   QUERY PLAN
------------------------------------------------
 Update on t1
   ->  Hash Right Join
         Hash Cond: (t3.a3 = t2.a2)
         ->  Seq Scan on t3
         ->  Hash
               ->  Hash Right Join
                     Hash Cond: (t2.a2 = t1.a1)
                     ->  Seq Scan on t2
                     ->  Hash
                           ->  Seq Scan on t1
(10 rows)


lightdb@oracle=# table t1;
 a1 | b1 | c1 | d1
----+----+----+----
  1 |  2 |    |
  2 |  4 |    |
  3 |  6 |    |
  4 |  8 |    |
  5 | 10 |    |
  6 | 12 |    |
(6 rows)

lightdb@oracle=# table t2;
 a2 | b2 | c2
----+----+----
  1 |  3 |  5
  2 |  6 | 10
  3 |  9 | 15
(3 rows)

lightdb@oracle=# update (select * from  t1 left join t2 on a2 = a1 ) set c1 = c2, d1 = a2 ;
UPDATE 6
lightdb@oracle=# table t1;
 a1 | b1 | c1 | d1
----+----+----+----
  1 |  2 |  5 |  1
  2 |  4 | 10 |  2
  3 |  6 | 15 |  3
  4 |  8 |    |
  5 | 10 |    |
  6 | 12 |    |
(6 rows)

  • decode function supports the default is empty syntax 

    CREATE TABLE emptest (
    name VARCHAR2(10),
    flag CHAR(1),
    serial_no1 NUMBER(10),
    serial_no2 VARCHAR2(100),
    serial_no3 DATE,
    serial_no4 BINARY_FLOAT,
    serial_no5 BINARY_DOUBLE,
    serial_no6 BLOB,
    serial_no7 CLOB,
    serial_no8 RAW(2000),
    serial_no9 LONG,
    serial_no10 NCHAR(100),
    serial_no11 NVARCHAR2(100),
    serial_no12 TIMESTAMP,
    serial_no13 INTERVAL YEAR TO MONTH,
    serial_no14 INTERVAL DAY TO SECOND
);

-- Note: Clob fields often require special insertion methods, a simple example is given here
INSERT INTO emptest (
    name, flag, serial_no1, serial_no2, serial_no3, serial_no4, serial_no5,
    serial_no6, serial_no7, serial_no8, serial_no9, serial_no10, serial_no11,
    serial_no12, serial_no13, serial_no14
) VALUES (
    'xiaoming', '1', 100, 'A', SYSDATE - 100, 1.1, 2.2,
    UTL_RAW.CAST_TO_RAW('example_blob'), 'example_clob', UTL_RAW.CAST_TO_RAW('example_raw'),
    'example_long', N'example_nchar', N'example_nvarchar',
    SYSTIMESTAMP, INTERVAL '1-2' YEAR TO MONTH, INTERVAL '10 12:30:06.123456' DAY TO SECOND
);

lightdb@testdb=# select name, decode(flag, '1', serial_no1, '') as serial_no from t12;
   name   | serial_no 
----------+-----------
 xiaoming |       100

lightdb@testdb=# 
lightdb@testdb=# select name, decode(flag, '1', serial_no2, '') as serial_no from t12;
   name   |         serial_no          
----------+----------------------------
 xiaoming | \x6578616d706c655f626c6f62

  • Stored procedures are compatible with oracle, support string type declaration, and varchar2 type consistency.

    For example: 

    lightdb@oracle=# DECLARE
lightdb@oracle$#   v_name string(20) := 'nihao，string！';
lightdb@oracle$# BEGIN
lightdb@oracle$#    DBMS_OUTPUT.PUT_LINE(v_name); 
lightdb@oracle$# END;
lightdb@oracle$# / 
nihao，string！
DO

E.1.3. MySQL compatibility

  • No new features in this version。

E.1.4. Lightdb-x New Features

  • Support subquery result cache. Refer Subquery Result Cache for details. 

    create table test_p(key1 int primary key, key2 text);

insert into test_p select s, 'dsdsds' from generate_series(1, 1000000) as s;

create table test_p1(key1 int primary key, key2 text);

insert into test_p1 select s, 'dsdsds' from generate_series(1, 1000000) as s;

select * from 
 ( select/*+lt_result_cache*/  rownum as row_num , a.* from
      (select x.key1 from (select * from test_p order by key2) x, 
            (select * from test_p1 order by key2) x1 where x.key1=x1.key1*2 order by x.key1 
      ) a
 )b where row_num >1 and row_num < 5;

  • When executing SQL, if the number of rows used for sorting or hash tables exceeds one million, work_mem will be automatically adjusted to 1GB if it is less than 1GB.

  • Supports calculating the number of parallel executing workers through a linear algorithm. Refer Linear Parallel workers for details.

  • The newly added parameter is lightdb_order_by_combine_delimiter.

    When a query includes DENSE_RANK() OVER (ORDER BY field1, field2, ...), this parameter is used

    to optimize the efficiency of the ORDER BY clause. The default value for this parameter is a

    comma (,). If the configuration is left empty, it indicates that this optimization is disabled.

    Otherwise, LightDB will attempt to concatenate all the sorting fields using the value configured

    in lightdb_order_by_combine_delimiter before performing the sorting, provided that all fields

    have consistent sorting order and direction.

  • Now supports cache dostmt plan. This helps to improve the performance of repeatedly executing the same anonymous block (with different binding parameters) through embedded SQL. A new parameter, lightdb_cache_dostmt_plan, has been added to control this optimization. Default is on;

Prev Up Next
Appendix E. Release Notes Home E.2. Release 13.8-24.1