61.1. 版本发布 13.8-24.2

61.1.1. Oracle 兼容
61.1.2. pl/sql 增强
61.1.3. MySQL 兼容
61.1.4. lightdb 新特性
61.1.5. ltjdbc 增强

版本发布日期:. 2024年08月30日

61.1.1. Oracle 兼容

  • 增强Oracle Pro*C PREPARE语法,支持PREPARE后面为AS和ORDER。

    示例:

      EXEC SQL PREPARE as FROM :query;
      EXEC SQL PREPARE order FROM :query;
      
  • 允许 ecpg 程序不解析 '#if 0 ... #endif' 之间的代码块。 参见 Section 33.9.3

  • 增强Oracle Pro*C INTO语法,支持INTO后面的主变量不带冒号。 参见 Section 33.4.4

  • 增强Oracle Pro*C 语法,支持从数据库读取数据到一个char类型变量中。 参见 Section 33.15.1

  • Pro*C 支持批量插入结构体数组. 参见 Section 33.4.5.3.2

  • 增强Oracle Pro*C 语法,支持从数据库fetch数据到数组中。 参见 Section 33.3.2

  • 增强Oracle Pro*C 语法,PREPARE准备语句是支持语句中包含注释 ("/*" 和 "--" 两种形式)、双引号。 语句可以是带绑定参数的匿名块。参见 PREPARE

  • 增强Oracle Pro*C 语法,支持语句中包含头文件oraca.h

  • 增强Oracle Pro*C 语法,支持在.h文件中定义主变量。参见 Section 33.4.3.1Section 33.4.3.2

  • 支持新的 Oracle Pro*C 语法。 参见 COMMIT RELEASE

  • 支持 Oracle Pro*C EXEC SQL FETCH cur into :var。 变量的个数可以比输出字段的个数少,这时候只会接收变量个数的输出值。

    示例:

    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;
              
  • 支持ltjdbc新增驱动类名称:com.hundsun.lightdb.Driver,具体如下:

    1、使用com.hundsun.lightdb.Driver时, url串格式为:jdbc:lightdb://host:port/database;

    a)	Host:数据库IP
    b)	Port:数据库端口号
    c)	Database:数据库名称
    				

    2、url链接参数保持不变;

    3、API调用及使用方式保持不变;

  • START WITH子句和CONNECT BY子句作为一个整体可以放在GROUP BY子句的前面或后面。 请参见SELECT

    示例:

    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)
    
               
  • 支持least函数,greatest函数返回值类型与oracle兼容。 在oracle模式下,并且函数的第一个参数类型是oracle.date,原先函数返回值类型是timestamp, 现在函数返回值类型是oracle.date。

    示例:

    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)
                 
  • sysdate是oracle.date类型,原先不支持to_date函数的入参类型为oracle.date类型,现在已支持。具体用法说明如下:

    1、支持to_date(oracle.date)函数操作,返回值为oracle.date类型;

    2、支持在普通SQL、嵌套子查询、存储过程、函数、匿名块、ECPG中使用;

    请参见to_date(oracle.date)

    示例:

    --以普通SQL为例
    lightdb@oracle_test=# select to_date(sysdate) from dual;
           to_date       
    ---------------------
     2024-09-12 20:39:08
    (1 row)
                  
  • 支持新的数据类型binary_integer,存储有符号整数的数据类型,是INT4的别名。具体用法说明如下:

    支持普通SQL、包、匿名块、表字段中的类型定义;

    请参见binary_integer

    示例:

    --普通SQL
    SELECT '123'::binary_integer;
    
    --包中的binary_integer类型
    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
    
    --匿名块调用
    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
                  
  • SEQUENCE支持全局缓存而非会话缓存的特性。具体用法说明如下:

    1、任意多个会话调用同一序列时,全局连续递增,而非跳跃递增;

    2、已经获取的序列的下一个值,并且SQL插入报错回滚当前值,则下一次使用序列跳过该值;

    3、数据库重启后,序列连续;

    请参见 CREATE SEQUENCE

    示例:

    -- 创建序列
    CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 20;
    
    -- 创建测试表
    CREATE TABLE test_table (
        id NUMBER PRIMARY KEY,
        name VARCHAR2(50),
        age NUMBER,
        email VARCHAR2(100)
    );
    
    -- session1 插入一些测试数据并提交
    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 INTO test_table (id, name, age, email) VALUES (test_sequence.NEXTVAL, '王五', 28, 'wangwu@example.com');
    
    --查询执行结果
    session1:
    	select test_sequence.currval from dual;
    	 currval 
    	---------
           2
    (1 row)
    
    session2:
    	select test_sequence.currval from dual;
    	 currval 
    	---------
           3
    (1 row)
                  
  • 支持merge into带where子句的用法。具体用法说明如下:

    1、merge into语句on条件匹配到的值,会走matched then update语句;

    2、when matched then update中出现where条件,走merge逻辑;

    merge into语法,请参见MERGE INTO

    示例:

    -- 测试MERGE带where子句
    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);
    
    --执行MERGE INTO操作
    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);
      
    --查询执行结果
    SELECT * FROM target ORDER BY tid;
     tid | balance 
    -----+---------
       1 |      10
       2 |      20
       3 |      30
       5 |      55
    (4 rows)
                  
  • 支持merge into update set delete的sql用法。具体用法说明如下:

    1、新增支持merge into when matched delete 子句;

    2、on()中条件匹配,则执行update语句;若on()中条件匹配且where条件也匹配,则执行delete语句;

    3、普通SQL和存储过程都支持该sql语句;

    merge into update set delete语法,请参见MERGE INTO

    示例:

    --建两张表,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');
    
    --执行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;
    
    --查询执行结果
    select * from a;
     id1 | id2 | name 
    -----+-----+------
    (0 rows)
                  
  • 支持ORDER BY语句接列别名表达式。具体用法说明如下:

    1、支持ORDER BY后面接列别名表达式,表达式包含加减乘除、函数、case when判断,其中聚合函数除外,列别名可以为常量的别名;

    2、列别名可以是表中具体列的别名;

    具体请参见 ORDER BY

    示例:

    举例1、ORDER BY后面接函数表达式
    --建一张表,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);
    
    --执行ORDER BY语句
    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);
    
    --查询执行结果
     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)
    
    举例2、列别名是表里具体列的别名。比如emp表的ename 
    select e.*,e.ename as e_name from emp e order by length(e_name);
    
    --查询执行结果
     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)
                  
  • 支持CONNECT BY子查询。具体用法说明如下:

    1、支持connect by子查询中带伪列rownum;

    2、支持start with connect by选项;

    3、支持connect by prior选项;

    具体请参见 CONNECT BY

    示例:

    --建一张表,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);
    
    --执行connect by sql语句
    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)
                  
  • 窗口函数支持distinct去重操作。具体用法说明如下:

    1、聚合函数支持distinct对列进行去重操作;

    2、窗口聚合函数范围包含:count() over、max() over()、avg() over()、min() over()、sum() over();

    具体请参见 distinct

    示例:

    --查询出正确结果
    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;
                  
  • 新增1个优化器提示/*+index(table_name index_name)*/,将该指定索引下推到FROM之后的子查询。

    1、支持下推到FROM之后的子查询。如: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、支持最少3层嵌套;

    具体用法,请参见lt_hint_plan

    示例:

    --建表、索引
    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);
    --分析结果
    1.不指定索引下推
    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.指定索引下推
    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)
    
                 
  • 新增5个(ECPG(Oracle Pro*c兼容))特性。 请参见ECPG(Oracle Pro*c兼容)

    • ECPG中,匿名块调用带DML操作的存储函数时,支持commit、rollback事务提交。具体说明如下:

      1、在匿名块调用带DML操作(含增、删、改)并commit的存储函数时,确保函数内事务提交成功;

      2、匿名块调用带DML操作(含增、删、改),发生异常时,需要rollback,确保函数内回滚成功;

      示例:

      --构建EMP表,对某一行的工资进行更新,并进行commit/rollback操作
      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;
      }
                         
    • ECPG中,支持外层做MAX/MIN/AVG/COUNT/SUM,里层做count(*)的聚合函数嵌套应用。具体说明如下:

      以max(count(*))为例;

      1、支持 max(count(*)) 用法,输出正确结果;

      2、聚合函数的嵌套应用范围,支持普通SQL、函数、存储过程、ECPG;

      示例:

      --新建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');
      
      --在ECPG中,对TEST1表按name进行分组求和,再求最大值
      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;
      }
                         
    • ECPG中,支持Oracle 动态SQL方法4的动态SQL调用。适用于查询列表以及宿主变量个数均不能确定的SQL语句。具体说明如下:

      1、支持sqlda结构体创建(描述SQL变量和指示器变量的数据结构,包含了一系列的指针和长度,用于存储和描述SQL查询中使用的变量的信息);

                          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、支持SQL语法(初始化描述符以保存选择列表项或输入主机变量的说明)

      语法:EXEC SQL DESCRIBE BIND VARIABLES FOR sql_stmt INTO bind_des;

      3、支持SQL语法(检查 PREPAREd 动态查询中的每个选择列表项,以确定其名称、数据类型、约束、长度、小数位数和精度。然后,它将这些信息存储在选择描述符中)

      语法:EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name INTO select_descriptor_name;

      4、支持SQL语法(通过游标从选择描述符中,读取array_size行信息)

      语法: EXEC SQL FOR :array_size FETCH Cursorbase USING DESCRIPTOR select_descriptor_name;

    • ECPG中,支持上下文变量的定义、及建立到用户的全局运行时上下文的连接、分配运行时上下文、使用运行时上下文。具体说明如下:

      1、通过EXEC SQL CONNECT :usr1;建立到指定用户的数据库连接;

      2、通过EXEC SQL CONTEXT ALLOCATE :ctx1;(分配一个运行时上下文ctx1);

      3、通过EXEC SQL CONTEXT USE :ctx1;(使用分配的运行时上下文ctx1)来达到获取ctx1的信息,包含游标、会话ID、用户名。

      通过EXEC SQL CONTEXT USE DEFAULT;恢复默认的运行时上下文,包含游标、会话ID、用户名。

      详见请参见ECPG(SQL-CONTEXT)

    • ECPG中,支持可以不在EXEC SQL BEGIN DECLARE SECTION;EXEC SQL END DECLARE SECTION;的语句中声明主机变量。

      详见请参见ECPG(DECLARE-SECTION)

    • ECPG中,增加EXEC SQL CONNECT连接串Oracle兼容格式。具体说明如下:

      支持下面两种ECPG连接串格式:

      1、支持EXEC SQL connect : username/passwd; (其中’:’和username/passwd中间,存在空格或者不存在空格都支持)

      2、支持EXEC SQL connect : username/passwd@sdbname; (其中’:’和username/passwd@sdbname中间,存在空格或者不存在空格都支持)

      示例:

      --以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);
      }
                         
    • ECPG中,open cursor中using 进行绑定变量,同一个变量可以进行同名赋值。具体说明如下:

      1、使用using 进行绑定变量的时候,同一个绑定变量(数量不限),可以进行同名赋值;

      2、通过游标能读取的数据和实际一致;

      示例:

      --以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);
      }
                         
    • ECPG中,主机变量支持普通类型绑定变量名的空格、冒号、括号写法和支持指针类型的绑定变量名空格、冒号、括号、星号写法。具体说明如下:

      1、普通变量:增、删、改、查场景下,支持绑定变量带空格(空格数不受限制),带括号的写法,忽略括号、空格,并能成功运行出正确结果;;

      2、指针变量:增、删、改、查场景下,支持指针类型的绑定变量(指针类型支持int类型、char类型,double类型,一级指针),同时支持带空格(空格数不受限制)、带括号,带*号的写法,并成功运行出正常结果;

      示例:

      --举例如下
      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--");		
      		 // 初始化宿主变量  
      	  strcpy(first_name, "Unknown");  
      		salary = &temp;
      	  
      	  // 1、执行 SQL 查询,使用绑定变量 空格、*号 
      	  EXEC SQL SELECT ename, sal  
      	             INTO : first_name, :*salary  
      	             FROM emp  
      	             WHERE DEPTNO = :dept_id;  
      		
      		// 输出查询结果  
      		printf("test1:*号\n"); 
      	  printf("First Name: %s\n", first_name);  
      	  printf("sal*: %d,sqlrr = %d\n", *salary,sqlca.sqlcode);
                         
    • ECPG中,主机变量支持普通类型绑定变量名的空格、冒号、括号写法和支持指针类型的绑定变量名空格、冒号、括号、星号写法。具体说明如下:

      1、普通变量:增、删、改、查场景下,支持绑定变量带空格(空格数不受限制),带括号的写法,忽略括号、空格,并能成功运行出正确结果;;

      2、指针变量:增、删、改、查场景下,支持指针类型的绑定变量(指针类型支持int类型、char类型,double类型,一级指针),同时支持带空格(空格数不受限制)、带括号,带*号的写法,并成功运行出正常结果;

      示例:

      --举例如下
      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--");		
      		 // 初始化宿主变量  
      	  strcpy(first_name, "Unknown");  
      		salary = &temp;
      	  
      	  // 1、执行 SQL 查询,使用绑定变量 空格、*号 
      	  EXEC SQL SELECT ename, sal  
      	             INTO : first_name, :*salary  
      	             FROM emp  
      	             WHERE DEPTNO = :dept_id;  
      		
      		// 输出查询结果  
      		printf("test1:*号\n"); 
      	  printf("First Name: %s\n", first_name);  
      	  printf("sal*: %d,sqlrr = %d\n", *salary,sqlca.sqlcode);
                         
  • Oracle模式下,ltsql -f 导入的sql文件将去掉\r字符。

  • Oracle模式下,like条件语句后面内容取消默认转义字符反斜杠。

    示例:

    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)
    
              

61.1.2. pl/sql 增强

  • 兼容Oracleupdate return into行为,在没有匹配行时,不报异常。具体用法说明如下:

    1、在执行update XX SET A=B FROM…WHERE..RETURN INTO 进行UPDATE且没有找到可更新的行时,不报exception异常,按照正常顺序执行;

    2、在函数、匿名块、存储过程中都支持上面行为;

    示例:

    --update没查到,不会跳到exception,正常返回;
    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;
     /
    
    --结果输出
    函数内:执行结果是3371
    执行结果1是0
    执行结果2是3371
    DO
                  
  • 匿名块支持SQLCODE变量及SQLERRM函数使用语法。具体用法说明如下:

    1、支持的范围为匿名代码块、函数,存储过程;

    2、SQLCODE在exception代码块外使用,结果为0;

    3、SQLERRM支持在exception代码块外使用;

    具体请参见 支持 SQLCODE

    示例:

    --1、匿名块
    begin  
        begin  
            null;  
        exception  
            when others then  
                dbms_output.put_line(sqlcode);  
        end;  
    dbms_output.put_line(sqlcode);  
    dbms_output.put_line(sqlerrm);
    end;  
    /
    
    --查询执行结果
    ZHANGSAN
    LISI
    WANGWU
    DO
    
    --2、存储过程
    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、函数
    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 immediate后面支持跟随变量赋值语句,如有多个位置变量具有相同的名称,则它们表示的是相同的对象,即 USING 子句中的同一个参数。具体用法说明如下:

    1、匿名块、函数或存储过程使动态SQL调用普通SQL,如:execute immediate 'insert into test values (:v2, :v1, :v1, :v3)' using 1,2,3,4;对数据进行插入时使用占位符,同名占位符不等价;

    2、匿名块、函数或存储过程使动态SQL调用匿名块对数据进行插入,在 USING 子句中,参数(与USING后变量类型无关)按照顺序与动态 SQL 语句中的位置变量一一对应。如果动态 SQL 语句中有多个位置变量具有相同的名称,则 USING 子句中的相应变量也会共享相同的值;

    3、绑定变量仅支持 :x ,不支持 : x 或者: xx,即不支持冒号和变量名中间有空格;

    具体请参见 执行动态命令

    示例:

    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入参,a2出参
          :a := tty(:q2,:r4); -- 入参 a5 和 a4,将结果赋值给 a3 和 a4
             end;' using in a1, out a2, out a3, in a5, out a4;
    end;
    /
              
  • CREATE FUNCTION成功之后,再次执行CREATE OR REPLACE之后的函数返回值类型可以和第一次CREATE FUNCTION 之后的返回值类型不同,函数可以再次创建成功。具体用法说明如下:

    1、支持返回值不同的同名、同参函数使用create or replace创建;

    2、不限制返回值类型变化,比如:cursor->number,number->varchar均支持;

    具体请参见 create function

    示例:

    --下面三个同名,返回值类型不同的函数,均能创建成功。
    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 支持将函数入参in、inout类型替换为out类型。

    示例:

    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;  
    /
    
    --将get_employee_salary函数入参类型由inout替换为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;  
    /
    
              
  • 支持创建表时,表名使用双引号大写,当查询表时可直接进行查询,即不带双引号小写也可正常查询,兼容Oracle。

    CREATE TABLE "ABCD" (id int, "A" varchar(100));
    SELECT ID,A from abcd; --可以查询到
    SELECT * from ABCD;--可以查询到
    SELECT * from "ABCD";--可以查询到
    SELECT ID,"A" from abcd; --可以查询到
    SELECT ID,"a" from ABcd;
    
    CREATE TABLE "a123" (id int);
    select * from "a123"; --可以查询到
    select * from a123;   --能查询到
    select * from A123;   --能查询到
                
  • 支持timestamp(N)、date、timestamp with time zone、INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH时间和时间间隔类型字段允许使用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 
    ); 
    --可正常匹配
    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%';
    --可正常匹配
    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%';
              
  • 支持GENERATED ALWAYS AS, 此子句将列创建为generated column。 列无法被写入,读取时将返回指定表达式的结果。

    生成表达式可以引用表中的其他列,但不能引用其他生成的列。使用的任何函数和运算符都必须是不可改变的。不允许引用其他表。

    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;
    
    --支持指定约束 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;
    
    --支持指定约束 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;
    
    --支持约束 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; 
        
  • 支持NLSSORT函数。

    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('AA','AA','AA','AA');
    insert into testpinyin1 values('G传化','G传化','G传化','G传化');
    
    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语句支持接查询语句

    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函数支持default为空语法

    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
    );
    
    -- 注意:BLOB和CLOB字段通常需要特殊的插入方法,这里给出简单示例
    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
    
                
  • 嵌套表联合数组元素类型支持record变量%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
                
  • 支持nvl函数返回值类型与oracle兼容。 请参见orafce

    示例:

    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)
                 
  • 存储过程兼容Oracle,支持STRING类型的声明,和VARCHAR2类型保持一致。

    示例:

    lightdb@oracle=# DECLARE
    lightdb@oracle$#   v_name string(20) := '你好,string!';
    lightdb@oracle$# BEGIN
    lightdb@oracle$#    DBMS_OUTPUT.PUT_LINE(v_name); 
    lightdb@oracle$# END;
    lightdb@oracle$# / 
    你好,string!
    DO
              

61.1.3. MySQL 兼容

  • 本期无新增功能。

61.1.4. lightdb 新特性

  • LightDB-x默认开启危险SQL DDL,在创建无主键表时进行主动告警。

    举例:

    CREATE TABLE test(id int);
    WARNING:  LightDB DDL check warn! no primary key!
    DETAIL:  If your system does not have data replication requirement, just ignore it
    CREATE TABLE
    				
  • 支持子查询结果集缓存。 具体请看 Subquery Result Cache.

    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;
    

  • 在执行sql的时候当排序或hash表使用的行数超过1百万时,work_mem 若小于1GB会临时自动调整为1GB。

  • 支持通过线性算法计算并行执行工作者数量。 详情请查看Linear Parallel workers

  • 新增GUC参数 lightdb_order_by_combine_delimiter。

    当查询中包含DENSE_RANK() OVER (ORDER BY 排序字段1,排序字典2, ... )时,这个参数用于

    优化order by的效率,默认值为','。当配置为空时则表示关闭这个优化。否则将要尝试将所有排

    序字段使用lightdb_order_by_combine_delimiter配置的值连接后再进行排序(前提是所有字段

    排序顺序方向一致)。

  • 支持缓存匿名执行块的执行计划,有助于提升通过嵌入式sql重复执行相同匿名块(不同绑定参数)的性能。 添加了一个新的参数 lightdb_cache_dostmt_plan来控制这个优化. 默认开启;

61.1.5. ltjdbc 增强

  • 支持DBeaver通过jdbc执行匿名块、函数、存储过程、包、TYPE对象;