Supports functions substr and substring. see function.

for example:

lightdb@postgres=# create database test_createdb_mysql with lightdb_syntax_compatible_type mysql;
CREATE DATABASE
lightdb@postgres=# \c test_createdb_mysql
You are now connected to database "test_createdb_mysql" as user "lightdb".
lightdb@test_createdb_mysql=# select substr('sadadertyui',-3,3) FROM DUAL;
 substr 
--------
 yui
(1 row)

Support MySQL division, including integer division by integer, division by 0 operation, etc see myfce.

for example:

lightdb@test_createdb_mysql=# select 4/0;
WARNING:  division by zero
 ?column? 
----------
         
(1 row)
lightdb@test_createdb_mysql=# select 4/1;
      ?column?      
--------------------
 4.0000000000000000
(1 row)

Support for str_to_date function. see myfce.

for example:

select str_to_date('2022/09/10 11,13,13', '%Y/%m/%d %h,%i,%s');
      str_to_date     
---------------------
  2022-09-10 11:13:13
(1 row)

Support for @ variables. see User Variable.

User variables are written as @var_name, where the variable name var_name must be an Identifiers or an Key Words. If you quote it identifier，var_name will be treated as a column name (for example, @"my-var"). @var_name and @ var_name are different. @var_name is a user variable expression, @ var_name is the absolute value of the column var_name.

lightdb@test=# select @c1 := 5;
 @c1 
-----
   5
(1 row)

lightdb@test=# SELECT c1, @c1, @ c1 FROM t;
 c1 | @c1 | ?column? 
----+-----+----------
  0 |   5 |        0
  1 |   5 |        1
(2 rows)

Support specifying indexes when querying, only syntax support. force index enforces the use of the specified index; ignore index discards the specified index. see SELECT.

    -- multiple index
    select * from lt_test_mysql_ddl use index(pk_lt_test_mysql_ddl,uk_lt_test_mysql_ddl);
    select * from lt_test_mysql_ddl force index(pk_lt_test_mysql_ddl,uk_lt_test_mysql_ddl);
    select * from lt_test_mysql_ddl ignore index(pk_lt_test_mysql_ddl,uk_lt_test_mysql_ddl);

    -- multiple table join
    select * from lt_test_mysql_ddl a use index for order by(primary) join b using(id);
    select * from lt_test_mysql_ddl a force index for order by(pk_lt_test_mysql_ddl) join b using(id);
    select * from lt_test_mysql_ddl a ignore index for order by(primary,pk_lt_test_mysql_ddl) join b using(id);
    select * from lt_test_mysql_ddl a ignore index for order by(pk_lt_test_mysql_ddl) join b using(id);
   

Support specifying engine, collate, character set, comment and other attributes when creating a table, among which engine and comment are only supported syntaxally. Compatible with backquotes ('') and tinyint type compatible. see Integer Types.

Support duplicate key update syntax, when the PRIMARY KEY or UNIQU constraint is violated, update the operation, and vice versa. see ON DUPLICATE KEY UPDATE.

INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=t1.c+1;

UPDATE t1 SET c=c+1 WHERE a=1;
      

Support replace into syntax, when the PRIMARY KEY or UNIQU constraint is violated, delete the old tuple first, and then insert the new tuple. see REPLACE INTO.

lightdb@testdb=# REPLACE INTO test VALUES (1, 'Old name', '18');
INSERT 0 1

lightdb@testdb=# REPLACE INTO test VALUES (1, 'New name');
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | age 
----+------------------+-----
  1 | New name         |   0
(1 row)

Added no_unnest and pq_distribute optimization prompts. see lt_hint_plan.

no_unnest optimization prompts.

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists(select * from test2 where test1.key1=test2.key1);
               QUERY PLAN               
----------------------------------------
 Hash Join
   Hash Cond: (test1.key1 = test2.key1)
   ->  Seq Scan on test1
   ->  Hash
         ->  HashAggregate
               Group Key: test2.key1
               ->  Seq Scan on test2
(7 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists(select/*+no_unnest*/ * from test2 where test1.key1=test2.key1);
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on test1 @"lt#1"
   Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
   SubPlan 1
     ->  Seq Scan on test2 @"lt#0"
           Filter: (test1.key1 = key1)
   SubPlan 2
     ->  Seq Scan on test2 test2_1 @"lt#0"
(7 rows)

pq_distribute optimization prompts.

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
lightdb@lt_test-# EXPLAIN (COSTS false) SELECT /*+ leading(t1 t2) hashjoin(t1 t2)*/ * FROM t1 join t2 on t1.id=t2.id;
                    QUERY PLAN                     
---------------------------------------------------
 Gather
   Workers Planned: 3
   ->  Parallel Hash Join
         Hash Cond: (t1.id = t2.id)
         ->  Parallel Seq Scan on t1 @"lt#0"
         ->  Parallel Hash
               ->  Parallel Seq Scan on t2 @"lt#0"
(7 rows)
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
lightdb@lt_test-# EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t2 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
                 QUERY PLAN                  
---------------------------------------------
 Gather
   Workers Planned: 3
   ->  Hash Join
         Hash Cond: (t1.id = t2.id)
         ->  Parallel Seq Scan on t1 @"lt#0"
         ->  Hash
               ->  Seq Scan on t2 @"lt#0"
(7 rows)

Support Oracle to create user (CREATE USER)syntax; Supports cascading drop of users (DROP USER) and grant statements (see Examples).

for example:

   CREATE USER user_name IDENTIFIED BY passwd DEFAULT TABLESPACE dt_space TEMPORARY TABLESPACE tt_space;
   DROP USER user_name CASCADE;
   GRANT CONNECT TO HS_SES;
   GRANT RESOURCE TO HS_SES;
   GRANT UNLIMITED TABLESPACE TO HS_SES;

Support global temporary tables. see Compatibility.

Create a session-level global temporary table.

  CREATE GLOBAL TEMPORARY TABLE gtt_session(id number,ename varchar(15)) ON COMMIT PRESERVE ROWS;
  

Create a transaction-level global temporary table.

  CREATE GLOBAL TEMPORARY TABLE gtt_transaction(id number,ename varchar(15)) ON COMMIT DELETE ROWS;
  

Automatically create a user with the same name as the database, and the automatically created user with the same name has create, connect, temporary, login permissions in the created database, and this function cannot be used in distributed systems. see CREATE DATABASE.

lightdb@postgres=# create database testdb with lightdb_syntax_compatible_type oracle;
NOTICE:  auto create user "testdb" success
CREATE DATABASE

When the length of the variable type declaration can be supported, it can be supported in characters or bytes, and when the unit is currently characters, only syntax is supported. see orafce.

    create table hs_es(id int, name varchar2(10 byte));
    create table hs_lt(id int, name varchar2(10 char));
   

Added import tool ltldr, compatible with sqlldr tool. By importing data from a specified table into the database through a control file, multiple tables can be imported at the same time. For businesses that use sqlldr tools to import data, there are innate advantages.

Compatible with DBMS_UTILITY built-in package format_error_backtrace function, only record the latest record of the PlorasSQL execution error, use this function to view the location of the error. see support DBMS_UTILITY built-in package format_error_backtrace.

Use function of DBMS_UTILITY.format_error_backtrace, for example:

create or replace procedure pr_error_code_test1 is
    v_backtrace    varchar(1000);
    v_error        varchar(1000); 
begin

    INSERT INTO test_error_stack values (2);
    INSERT INTO test_error_stack values (1);
    commit;
    exception when others then
    insert into t_errlog(operate_error_msg) select to_char(DBMS_UTILITY.format_error_backtrace);
    commit;
    rollback;
    end pr_error_code_test1;
/

select select to_char(DBMS_UTILITY.format_error_backtrace)
    

PloraSQL supports stored procedures to use both return parameters with out decoration and return returns as null. The return null function is to abort the stored procedure. see Returning from a Procedure.

create or replace procedure p_fmt_post_update_cash
(
  p_src_system  varchar2 DEFAULT '0',
  p_init_date   number DEFAULT 0, 
  p_company_no  varchar2 DEFAULT '0',
  o_return_msg  out varchar2, 
  o_return_code out integer
) as
  v_init_date         int := 0;
begin
  if v_init_date = 0 then
    o_return_code := 1;
    o_return_msg  := '请输入正确的校验日期';
	return;
  end if;
 end;
/