This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 44663fe43c2 Enhanced support for Oracle SQL parsing. (#30729)
44663fe43c2 is described below
commit 44663fe43c21225cbfeedd831d3f2535a8af3735
Author: 啊哈 <[email protected]>
AuthorDate: Tue Apr 2 13:59:25 2024 +0800
Enhanced support for Oracle SQL parsing. (#30729)
* Enhance support for some Oracle SQL parsing.
* Add some new Oracle SQL parsing test cases.
* Fixed some code formatting issues and errors.
---------
Co-authored-by: Desheng Zhang <[email protected]>
---
.../oracle/src/main/antlr4/imports/oracle/PLSQL.g4 | 7 +++-
.../src/main/resources/case/ddl/create-trigger.xml | 5 +++
.../resources/sql/supported/ddl/create-trigger.xml | 41 ++++++----------------
3 files changed, 21 insertions(+), 32 deletions(-)
diff --git a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
index ed9b25989a6..f58dd3f23b4 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
@@ -94,6 +94,7 @@ statement
| forallStatement
| gotoStatement
| ifStatement
+ | modifyingStatement
| nullStatement
| openStatement
| openForStatement
@@ -216,6 +217,8 @@ iterationCtlSeq
: qualIterationCtl (COMMA_ qualIterationCtl)*
;
+modifyingExpression: INSERTING | DELETING | UPDATING;
+
qualIterationCtl
: REVERSE? iterationCcontrol predClauseSeq
;
@@ -314,6 +317,8 @@ ifStatement
END IF SEMI_
;
+modifyingStatement: IF modifyingExpression THEN statement+ (ELSIF
modifyingExpression THEN statement+)* (ELSE statement+)? END IF SEMI_;
+
nullStatement
: NULL SEMI_
;
@@ -582,7 +587,7 @@ dmlEventClause
;
dmlEventElement
- : (DELETE | INSERT | UPDATE) (OF LP_ columnName (COMMA_ columnName)* RP_)?
+ : (DELETE | INSERT | UPDATE) (OF LP_? columnName (COMMA_ columnName)*
RP_?)?
;
systemTrigger
diff --git a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
index 5ca32c2a1c3..563d503f8ac 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
@@ -27,4 +27,9 @@
<create-trigger sql-case-id="create_trigger_with_body" />
<create-trigger
sql-case-id="create_trigger_with_execute_immediate_statement" />
<create-trigger sql-case-id="create_trigger_with_assignment_statement" />
+ <create-trigger sql-case-id="create_trigger_with_logon" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_1" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_2" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_3" />
+ <create-trigger sql-case-id="create_trigger_with_cascade_4" />
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
index aafdd9e562a..58f6b4ccf8d 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
@@ -17,37 +17,16 @@
-->
<sql-cases>
- <sql-case id="create_trigger" value="CREATE TRIGGER reminder1
- ON Sales.Customer
- AFTER INSERT, UPDATE
- AS INSERT INTO SalesPersonQuotaHistory VALUES(t1, 100)"
db-types="SQLServer" />
- <sql-case id="create_trigger_with_database_scoped" value="CREATE TRIGGER
ddl_trig_database
- ON ALL SERVER
- AFTER INSERT
- AS
- SELECT EVENTDATA()" db-types="SQLServer" />
-
+ <sql-case id="create_trigger" value="CREATE TRIGGER reminder1 ON
Sales.Customer AFTER INSERT, UPDATE AS INSERT INTO SalesPersonQuotaHistory
VALUES(t1, 100)" db-types="SQLServer" />
+ <sql-case id="create_trigger_with_database_scoped" value="CREATE TRIGGER
ddl_trig_database ON ALL SERVER AFTER INSERT AS SELECT EVENTDATA()"
db-types="SQLServer" />
<sql-case id="create_trigger_with_create_view" value="CREATE TRIGGER tr1
BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END"
db-types="MySQL" />
<sql-case id="create_trigger_with_dml_event_clause" value="CREATE TRIGGER
scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp BEGIN
IF (IS_SERVERERROR (1017)) THEN NULL; ELSE NULL; END IF; END;"
db-types="Oracle" />
- <sql-case id="create_trigger_with_body" value="CREATE TRIGGER log_errors
- AFTER SERVERERROR ON DATABASE
- BEGIN
- IF (IS_SERVERERROR (1017)) THEN
- NULL;
- ELSE
- NULL;
- END IF;
- END;" db-types="Oracle" />
- <sql-case id="create_trigger_with_execute_immediate_statement"
value="CREATE TRIGGER adg_logon_sync_trigger
- AFTER LOGON ON user.schema
- begin
- if (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL
STANDBY')) then
- execute immediate 'alter session sync with primary';
- end if;
- end;" db-types="Oracle" />
- <sql-case id="create_trigger_with_assignment_statement" value="CREATE
TRIGGER Contacts_BI
- BEFORE INSERT ON Contacts FOR EACH ROW
- BEGIN
- :NEW.ID := Contacts_Seq.NEXTVAL;
- END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_body" value="CREATE TRIGGER log_errors
AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR (1017)) THEN NULL; ELSE
NULL; END IF; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_execute_immediate_statement"
value="CREATE TRIGGER adg_logon_sync_trigger AFTER LOGON ON user.schema begin
if (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) then
execute immediate 'alter session sync with primary'; end if; end;"
db-types="Oracle" />
+ <sql-case id="create_trigger_with_assignment_statement" value="CREATE
TRIGGER Contacts_BI BEFORE INSERT ON Contacts FOR EACH ROW BEGIN :NEW.ID :=
Contacts_Seq.NEXTVAL; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_logon" value="CREATE OR REPLACE TRIGGER
check_user AFTER LOGON ON DATABASE BEGIN check_user; EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20000, 'Unexpected error: '||
DBMS_Utility.Format_Error_Stack); END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_1" value="CREATE OR REPLACE
TRIGGER dept_cascade3 AFTER UPDATE OF Deptno ON dept BEGIN UPDATE emp SET
Update_id = NULL WHERE Update_id = Integritypackage.Updateseq; END;"
db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_2" value="CREATE OR REPLACE
TRIGGER dept_del_cascade AFTER DELETE ON dept FOR EACH ROW BEGIN DELETE FROM
emp WHERE emp.Deptno = :OLD.Deptno; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_3" value="CREATE OR REPLACE
TRIGGER dept_cascade2 AFTER DELETE OR UPDATE OF Deptno ON dept FOR EACH ROW
BEGIN IF UPDATING THEN UPDATE emp SET Deptno = :NEW.Deptno, Update_id =
Integritypackage.Updateseq WHERE emp.Deptno = :OLD.Deptno AND Update_id IS
NULL; END IF; IF DELETING THEN DELETE FROM emp WHERE emp.Deptno = :OLD.Deptno;
END IF; END;" db-types="Oracle" />
+ <sql-case id="create_trigger_with_cascade_4" value="CREATE OR REPLACE
TRIGGER dept_restrict BEFORE DELETE OR UPDATE OF Deptno ON dept FOR EACH ROW
DECLARE Dummy INTEGER; Employees_present EXCEPTION; employees_not_present
EXCEPTION; CURSOR Dummy_cursor (Dn NUMBER) IS SELECT Deptno FROM emp WHERE
Deptno = Dn; BEGIN OPEN Dummy_cursor (:OLD.Deptno); FETCH Dummy_cursor INTO
Dummy; IF Dummy_cursor%FOUND THEN RAISE Employees_present; ELSE RAISE
Employees_not_present; END IF; CLOSE Dummy_cu [...]
</sql-cases>
