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 a7756fd3172 Enhance the support for Oracle SQL parsing (#30742)
a7756fd3172 is described below
commit a7756fd317256778e4ab13c75bcf8ff79c9bd3aa
Author: zhuruixuan <[email protected]>
AuthorDate: Wed Apr 3 13:46:47 2024 +0800
Enhance the support for Oracle SQL parsing (#30742)
* Change1 SQL
* Change1 SQL
* Change1 Cases
* Change1 Keyword
* Change1 PLSQL
* Change1 Baserule
* Change2 case
* Change2 keyword
* Change2 BaseRule
* Change3 cases
* Increase the indentation
* Increase1 the indentation
* Increase2 the indentation
* 解决冲突
* solve conflct
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 5 +++
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 4 +++
.../oracle/src/main/antlr4/imports/oracle/PLSQL.g4 | 26 +++++++++++---
.../src/main/resources/case/ddl/create-trigger.xml | 2 ++
.../resources/sql/supported/ddl/create-trigger.xml | 40 ++++++++++++++++++++++
5 files changed, 72 insertions(+), 5 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index f3e11698035..09fe2704bb3 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -663,6 +663,11 @@ expr
| expr datetimeExpr
| multisetExpr
| builtinFunctionsExpr
+ | dataManipulationLanguageExpr
+ ;
+
+dataManipulationLanguageExpr
+ : UPDATING | INSERTING | DELETING
;
andOperator
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
index 8b6d011f79e..05cf60f028c 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
@@ -7971,3 +7971,7 @@ REP
APP
: A P P
;
+
+EXCEPTION_INIT
+ : E X C E P T I O N UL_ I N I T
+ ;
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 f58dd3f23b4..da97943a084 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
@@ -311,10 +311,10 @@ gotoStatement
;
ifStatement
- : IF booleanExpression THEN statement+
+ : (IF booleanExpression THEN statement+)*
(ELSIF booleanExpression THEN statement+)*
(ELSE statement+)?
- END IF SEMI_
+ (END IF SEMI_)+
;
modifyingStatement: IF modifyingExpression THEN statement+ (ELSIF
modifyingExpression THEN statement+)* (ELSE statement+)? END IF SEMI_;
@@ -424,7 +424,15 @@ declareSection
;
declareItem
- : typeDefinition | cursorDeclaration | itemDeclaration |
functionDeclaration | procedureDeclaration | cursorDefinition |
functionDefinition | procedureDefinition | pragma
+ : typeDefinition
+ | cursorDeclaration
+ | itemDeclaration
+ | functionDeclaration
+ | procedureDeclaration
+ | cursorDefinition
+ | functionDefinition
+ | procedureDefinition
+ | pragma
;
cursorDefinition
@@ -507,7 +515,7 @@ cursorVariableDeclaration
;
exceptionDeclaration
- : variableName EXCEPTION SEMI_
+ : variableName (EXCEPTION SEMI_)?
;
recordVariableDeclaration
@@ -566,10 +574,18 @@ rowtypeAttribute
;
pragma
- : autonomousTransPragma | restrictReferencesPragma
+ : autonomousTransPragma | restrictReferencesPragma | exceptionInitPragma
// TODO Support more pragma
;
+exceptionInitPragma
+ : (PRAGMA EXCEPTION_INIT LP_ exceptionDeclaration COMMA_ errorCode RP_
SEMI_)+
+ ;
+
+errorCode
+ : MINUS_ INTEGER_
+ ;
+
autonomousTransPragma
: PRAGMA AUTONOMOUS_TRANSACTION SEMI_
;
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 563d503f8ac..008f55af83b 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
@@ -32,4 +32,6 @@
<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" />
+ <create-trigger
sql-case-id="create_trigger_with_dataManipulationLanguage_statement" />
+ <create-trigger sql-case-id="create_trigger_with_exceptionInit_pragma" />
</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 58f6b4ccf8d..ad9f328d328 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
@@ -29,4 +29,44 @@
<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-case id="create_trigger_with_dataManipulationLanguage_statement"
value="CREATE OR REPLACE TRIGGER lineitems_trigger
+ AFTER INSERT OR UPDATE OR DELETE ON lineitems
+ FOR EACH ROW
+ BEGIN
+ IF (INSERTING OR UPDATING)
+ THEN
+ UPDATE orders SET line_items_count = NVL(line_items_count,0)+1
+ WHERE order_id = :new.order_id;
+ END IF;
+ IF (DELETING OR UPDATING)
+ THEN
+ UPDATE orders SET line_items_count = NVL(line_items_count,0)-1
+ WHERE order_id = :old.order_id;
+ END IF;
+ END;" db-types="Oracle"/>
+ <sql-case id="create_trigger_with_exceptionInit_pragma" value="CREATE OR
REPLACE TRIGGER Employee_permit_changes
+ BEFORE INSERT OR DELETE OR UPDATE ON employees
+ DECLARE
+ Dummy INTEGER;
+ Not_on_weekends EXCEPTION;
+ Nonworking_hours EXCEPTION;
+ PRAGMA EXCEPTION_INIT (Not_on_weekends, -4097);
+ PRAGMA EXCEPTION_INIT (Nonworking_hours, -4099);
+ BEGIN
+ IF (TO_CHAR(Sysdate, 'DAY') = 'SAT' OR
+ TO_CHAR(Sysdate, 'DAY') = 'SUN') THEN
+ RAISE Not_on_weekends;
+ END IF;
+ IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
+ TO_CHAR(Sysdate, 'HH24') > 18) THEN
+ RAISE Nonworking_hours;
+ END IF;
+ EXCEPTION
+ WHEN Not_on_weekends THEN
+ Raise_application_error(-20324,'Might not change '
+ ||'employee table during the weekend');
+ WHEN Nonworking_hours THEN
+ Raise_application_error(-20326,'Might not change '
+ ||'emp table during Nonworking hours');
+ END;" db-types="Oracle"/>
</sql-cases>
