Release date: 2023-06-30
The CHAR data type now supports specifying
byte or character lengths. When defining a column in a table
using the CHAR data type, you can now
specify the length of the column value, which can be either
byte length or character length. See
Character Types.
For example:
create table t (id char (1 char)); create table t (id char (1 byte));
The hierarchical query has been enhanced to include new
features such as CONNECT_BY_ROOT, which
displays the root node of the current row, and
CONNECT BY LEVEL < n, which displays all
recursive results up to a specified level
n. Additionally, hierarchical queries now
support subqueries, join queries, and expression calculations.
See CONNECT BY Clause.
For example:
SELECT employee, CONNECT_BY_ROOT employee_id as "Manager", SYS_CONNECT_BY_PATH(employee, '/') "Path" FROM example START WITH manager_id is not null CONNECT BY PRIOR employee_id = manager_id ORDER BY employee_id, manager_id; employee | Manager | Path ----------+---------+----------- Josh | 2 | /Josh Ali | 3 | /Ali Ali | 2 | /Josh/Ali Kyle | 5 | /Kyle (4 rows)
The ALTER TABLE command has been enhanced
to support deleting indexes and constraints on the same
column. See ALTER TABLE.
For example:
ALTER TABLE my_table DROP CONSTRAINT my_constraint, DROP INDEX my_index;
Support the use of the + symbol to
represent LEFT JOIN and
RIGHT JOIN. See
oracle plus.
For example:
SELECT * FROM table1, table2 WHERE table1.id = table2.id (+);
USING INDEX clause is now supported
in CREATE TABLE and ALTER TABLE
statements to specify an index to support a constraint. See
ALTER TABLE.
For example:
ALTER TABLE employees ADD CONSTRAINT emp_id_pk PRIMARY KEY (id) USING INDEX;
FROM is now optional
in DELETE statements. See
DELETE.
For example:
DELETE table_name WHERE col = 1;
SYS.COL system view is now available to
display columns information for all tables, including column
name, data type, length, precision, nullability, default
value, and character set name. See
orafce.
For example:
create table test_col_view(key0 varchar(100), key1 number not null, key2 number(38,0));
select * from col where tname= 'TEST_COL_VIEW' order by colno;
tname | colno | cname | coltype | width | scale | precision | nulls | defaultval | character_set_name
---------------+-------+-------+-----------+-------+-------+-----------+----------+------------+--------------------
TEST_COL_VIEW | 1 | KEY0 | VARCHAR | 100 | | | NULL | |
TEST_COL_VIEW | 2 | KEY1 | NUMBER | | | | NOT NULL | |
TEST_COL_VIEW | 3 | KEY2 | NUMBER | 38,0 | 0 | 38 | NULL | |
(3 rows)
INSERT ALL syntax is now supported for
inserting multiple rows into one or more tables in a single
statement. See INSERT.
For example:
INSERT ALL
INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)
INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)
SELECT 1 FROM DUAL;
INSERT INTO statement now supports using
aliases to specify the table to insert data into. See
INSERT.
For example:
INSERT INTO employees e (employee_id, first_name, last_name) VALUES (1001, 'John', 'Doe');
MERGE INTO statement now supports
distributed scenarios. See MERGE
INTO.
For example:
MERGE INTO target t
USING source s
ON t.tid = s.sid
WHEN MATCHED THEN
update set balance = t.balance + s.delta
WHEN NOT MATCHED THEN
INSERT VALUES(s.sid, s.delta);
Support implicit type
conversion for NUMBER and BOOLEAN data
types. See orafce.
For example:
select 0::NUMBER = TRUE; ?column? ---------- f (1 row) select 1::NUMBER = TRUE; ?column? ---------- t (1 row)
Partitioned tables are now more compatible with Oracle, as they support both Oracle-compatible partition naming and operations on subpartitions. See ALTER TABLE.
For example:
alter table lt_truncate_t_r truncate partition p1; alter table lt_truncate_t_r add partition p2 values less than(30); alter table lt_truncate_t_r truncate partition p2; alter table lt_truncate_t_r drop partition p2;
CREATE TABLE now supports the
STORAGE, compress /
nocompress, and logging
/ nologging keywords, although these are
only syntactic sugar and do not provide actual functionality.
See CREATE TABLE.
For example:
create table comp_log1(id int) compress; create table comp_log3(id int) logging; create table comp_log5(id int) compress logging; create table comp_log13(id int) storage(abcd);
PROMPT command is now supported to
display text messages to users. See
prompt command.
For example:
PROMPT Creating table...
CREATE TABLE test_table (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
);
PROMPT Inserting data...
BEGIN;
INSERT INTO test_table (id, name) VALUES (1, 'John Doe');
INSERT INTO test_table (id, name) VALUES (2, 'Jane Smith');
COMMIT;
PROMPT Querying data...
SELECT * FROM test_table;
DROP table test_table;
PROMPT Done.
ALTER INDEX now supports the
REBUILD keyword to rebuild a specific
index. See ALTER INDEX.
For example:
CREATE TABLE tt(a int); CREATE INDEX idx_tt_a on tt(a); ALTER INDEX idx_tt_a REBUILD;
SEQUENCE statement now supports
order / noorder /
nocycle / nocache
keywords, although these are only syntactic sugar and do not
provide actual functionality. See
CREATE SEQUENCE.
For example:
create sequence lt_seq_test1 order start 100; create sequence lt_seq_test3 start 100 nocycle; create sequence lt_seq_test5 start 100 ORDER nocache NOCYCLE; alter sequence lt_seq_test1 nocache nocycle noorder;
CREATE FORCE VIEW statement is now
supported, although this is only syntactic sugar and does not
provide actual functionality. See
CREATE VIEW.
For example:
CREATE FORCE VIEW force_view as SELECT 1 as ltapk, 2 as ltaut;
ECPG(Oracle Pro*c Compatible) now supports anonymous execution
blocks with binding parameters. See
Executing a Do
Statement with Input and Output Parameters.
For example:
EXEC SQL EXECUTE
begin
begin
update/*3631412*/ t1 set c = 'C' where id = :v_error_no;
exception
when others then
:iReturnCode = 456;
rollback;
when DUP_VAL_ON_INDEX then
null;
if SQL%NOTFOUND then
null;
end if;
end;
end;
END-EXEC;
Added support for sqlcode in pl/sql.
Users can now create functions that return
sqlcode values like in Oracle. See
support SQLCODE.
For example, the following code will output the SQL error code when a zero divide error occurs:
create or replace function zero_divide_f return int as
a int := 10;
b int := 0;
code int;
begin
a := a / b;
exception
when zero_divide then
code := sqlcode;
dbms_output.put_line('this is a normal error ' || code || ' ' || sqlcode || 'sqlcode_to_sqlstate(code) || ' ' ||sqlcode_to_sqlstate(sqlcode));
return 1;
end;
/
Added support for the with clause in
pl/sql. Users can now use the with clause
to define temporary result sets within a single SQL statement.
See Execute a
DML Query with with_clause.
For example:
declare
v_count varchar(100);
begin
with tmp as (select * from company where id=2) select * into v_count from tmp;
DBMS_OUTPUT.PUT_LINE(v_count);
end;
/
Added support for parsing the
PRAGMA AUTONOMOUS_TRANSACTION syntax in
pl/sql. Note that this is a syntax sugar feature and does
not implement the actual functionality in Oracle. See
AUTONOMOUS
TRANSACTION Pragma.
For example:
CREATE OR REPLACE PROCEDURE test_pragma_procedure()
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
NULL;
END;
/
Enabled case-insensitive cursor variable names in pl/sql. This means that users can now use cursor variable names with different capitalizations than when they were declared without causing syntax errors. See Cursors .
For example:
DECLARE
CURSOR D_tmp(zxc NUMBER,bnm NUMBER,ff smallint default 1234) IS
SELECT ff || a as feiwu FROM tt;
ddd D_tMp%ROWTYPE;
BEGIN
OPEN D_tmP(5,6);
FETCH d_tmp INTO ddd;
DBMS_OUTPUT.PUT_LINE(ddd.feiwu);
CLOSE D_tmP;
end;
/
Added support for referencing the type of a variable defined
in a package using the %TYPE syntax in
pl/sql. See
Declarations .
For example:
create table t_binjiang(a int, b int);
-- define package
create or replace package pkgtype is
v_a int := 0;
v_b int := 0;
end;
/
-- anonymous block references variables in the package
declare
a pkgtype.v_a%type := 1;
b pkgtype.v_b%type := 1;
begin
insert into t_binjiang values(a, b);
end;
/
Added support for the goto statement in
pl/sql. Users can now use the goto
statement to jump to a specific label within a block. See
GOTO Statement .
For example:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
goto testlabel;
RAISE NOTICE 'out block 2';
<<testlabel>>
BEGIN
RAISE NOTICE 'in block 1';
goto testlabel2;
RAISE NOTICE 'in block 2';
END;
RAISE NOTICE 'out block 3';
<<testlabel2>>
RAISE NOTICE 'out block 4';
END$$;
NOTICE: out block 1
NOTICE: in block 1
NOTICE: out block 4
Enabled the ability to call package functions in distributed scenarios in pl/sql. See Packages .
For example:
create or replace package typkg
as
type nt is table of tab1%rowtype;
res nt;
function myfunc(v int) return nt;
end;
/
create or replace package body typkg
as
function myfunc(v int) return nt
is
begin
res(1) := ROW(1,1);
res(2) := ROW(2,2);
res(3) := ROW(3,3);
return res;
end;
end;
/
select * from table(typkg.myfunc(1)) t0
where t0.a in (select a from tab1) order by t0.a;
Supports
DROP INDEX [IF EXISTS] index_name ON table_name;
See DROP INDEX .
For example:
create table t1(id int); create unique index idx_t1_id on t1(id); drop index idx_t1_id on t1;
Case-insensitive column querying and case-sensitive column
display. In mysql mode, guc parameter
lightdb_sql_mode is set to
uppercase_identifier. See
SELECT .
For example:
create table test_case_1(identifier1 varchar(10)); select * from test_case_1; identifier1 ------------- (0 rows) select IDentifier1 from test_case_1; IDentifier1 ------------- (0 rows) select identifier1 as IDentifier1 from test_case_1; IDentifier1 ------------- (0 rows)
Supports INSERT IGNORE INTO syntax.
INSERT IGNORE INTO statement is used to
insert data into a table. If the inserted data violates
uniqueness constraints such as primary key or unique index,
the data will be ignored without throwing any errors or
warnings. See INSERT .
For example:
INSERT IGNORE INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Supports adding comments to database objects (such as tables,
columns, etc.) using the COMMENT keyword.
See CREATE TABLE .
For example:
create table t1 (
id int primary comment 'this is id of t1' not null,
name varchar(10) not null comment 'this is name of t1'
) comment = 'this is t1';
Supports JSON-related functions. See MySQL Compatible Functions .
JSON_ARRAY: creates a JSON array.
JSON_OBJECT: creates a JSON object.
JSON_EXTRACT: extracts values from JSON
data.
JSON_CONTAINS: checks whether JSON data
contains the specified value.
JSON_CONTAINS_PATH: checks whether the
specified path exists in JSON string.
JSON_PRETTY: a function used to format JSON
string. It takes an unformatted JSON string as input and
formats it into an easy-to-read form.
Supports sysdate() function for retrieving
the current execution time. See
Current
Date/Time .
For example:
select sysdate();
sysdate
---------------------
2023-05-09 13:21:13
(1 row)
Fixed the issue of dual virtual table in
mysql mode, which caused unauthorized
access for ordinary users. See
MySQL Compatible Functions .
For example:
create user mytestuser with password 'mytestuser';
\c - mytestuser
select 2 from dual;
?column?
----------
2
(1 row)
PWR accurately measures real-time query IO events, IPC events, race conditions and other time overhead, and optimizes the performance report statistics of PWR. See lt_profile .
lt_hint_plan optimizes scenarios that do
not support hints and ignores unimplemented hints. See
lt_hint_plan .
Supports incremental checkpoint to reduce the performance impact on the database system when triggering checkpoints. See CHECKPOINT .
Supports debugging PL/pgsql stored
procedures in pgadmin/navicat. See
pldebugger .
PL/pgsql supports execute immediate syntax.
Note that this is only a syntax sugar feature and does not
implement the corresponding functionality. See
Executing
Dynamic Commands .
For example:
CREATE OR REPLACE FUNCTION get_employee_name_func(p_employee_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
sql_query TEXT;
emp_name VARCHAR;
BEGIN
sql_query := 'SELECT first_name FROM test_employees WHERE employee_id = $1';
EXECUTE IMMEDIATE sql_query INTO emp_name USING p_employee_id;
RAISE NOTICE 'Employee name is: %', emp_name;
RETURN emp_name;
END;
$$ LANGUAGE plpgsql;
Released a client package. After extracting and configuring the environment variables, users can directly use the relevant tools, including the following tools:
lt_distributed_probackup.py
lt_distributed_dump.py
lt_distributed_restore.py
ltuldr
lt_basebackup
lt_dump
lt_dumpall
lt_isready
lt_probackup
lt_restore
lt_verifybackup
ltsql
Added support for reading BLOB values in the form of byte
arrays using
the java.sql.ResultSet #getBlob (java.lang.String) method.
See LightDB JDBC .
Added support for executing SQL statements to create stored
procedures and functions using syntax such
as CREATE PROCEDURE and CREATE FUNCTION in
the ltjdbc library. See LightDB
JDBC .
Added support for converting date column types
to LocalDateTime objects using
the java.sql.ResultSet#getObject(int, java.lang.Class<T>) method.
See See LightDB JDBC .