增强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.16.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.1、Section 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兼容)。

兼容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('ＡＡ','ＡＡ','ＡＡ','ＡＡ');
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语句支持接查询语句

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)
             

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来控制这个优化. 默认开启;

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