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 15ee3e3a87d add alter and drop procedure statement for oracle (#20463)
15ee3e3a87d is described below
commit 15ee3e3a87d9241ccd8ae0042085c4e506d4b8b0
Author: tianhao960 <[email protected]>
AuthorDate: Wed Aug 24 08:59:08 2022 +0800
add alter and drop procedure statement for oracle (#20463)
* add createProcedure rule for oracle #1
* add createProcedure rule for oracle #2
* add createProcedure rule for oracle #3
* add createProcedure rule for oracle #4
* add createProcedure rule for oracle #5
* add createProcedure rule for oracle #6
* add createProcedure rule for oracle #6
* add createProcedure rule for oracle #7
* add dropProcedure and alterProcedure rule for oracle
* add javadoc and final for ProcedureStatement
---
.../main/antlr4/imports/oracle/StoreProcedure.g4 | 68 +++++++++++++---------
.../sql/parser/autogen/OracleStatement.g4 | 2 +
.../impl/OracleDDLStatementSQLVisitor.java | 14 +++++
...ent.java => OracleAlterProcedureStatement.java} | 9 +--
.../oracle/ddl/OracleCreateProcedureStatement.java | 5 +-
...ment.java => OracleDropProcedureStatement.java} | 9 +--
.../main/resources/case/ddl/alter-procedure.xml | 1 +
.../src/main/resources/case/ddl/drop-procedure.xml | 1 +
.../sql/supported/ddl/alter-procedure.xml | 1 +
.../resources/sql/supported/ddl/drop-procedure.xml | 1 +
10 files changed, 76 insertions(+), 35 deletions(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/StoreProcedure.g4
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/StoreProcedure.g4
index 23b2713aae3..ed178e01615 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/StoreProcedure.g4
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/imports/oracle/StoreProcedure.g4
@@ -23,12 +23,28 @@ call
: CALL
;
+alterProcedure
+ : ALTER PROCEDURE (schemaName DOT_)? procedureName (procedureCompileClause
| (EDITIONABLE | NONEDITIONABLE))
+ ;
+
+procedureCompileClause
+ : COMPILE DEBUG? (compilerParametersClause)* (REUSE SETTINGS)?
+ ;
+
+compilerParametersClause
+ : parameterName EQ_ parameterName
+ ;
+
+dropProcedure
+ : DROP PROCEDURE (schemaName DOT_)? procedureName
+ ;
+
createProcedure
: CREATE (OR REPLACE)? (EDITIONABLE | NONEDITIONABLE)? PROCEDURE
plsqlProcedureSource
;
plsqlProcedureSource
- : (schemaName DOT_)? procedureName ( LP_ parameterDeclaration ( COMMA_
parameterDeclaration )* RP_)? sharingClause?
+ : (schemaName DOT_)? procedureName (LP_ parameterDeclaration (COMMA_
parameterDeclaration)* RP_)? sharingClause?
((defaultCollationClause | invokerRightsClause | accessibleByClause)*)?
(IS | AS) (callSpec | declareSection? body)
;
@@ -38,8 +54,8 @@ body
//need add more statement type according to the doc
statement
- : ( SIGNED_LEFT_SHIFT_ label SIGNED_RIGHT_SHIFT_ ( SIGNED_LEFT_SHIFT_
label SIGNED_RIGHT_SHIFT_ ) *)?
- ( select
+ : (SIGNED_LEFT_SHIFT_ label SIGNED_RIGHT_SHIFT_ (SIGNED_LEFT_SHIFT_ label
SIGNED_RIGHT_SHIFT_) *)?
+ (select
| update
| delete
| insert
@@ -49,7 +65,7 @@ statement
;
exceptionHandler
- : WHEN ( (typeName (OR typeName)* )| OTHERS ) THEN statement+
+ : WHEN ((typeName (OR typeName)*)| OTHERS) THEN statement+
;
declareSection
@@ -62,11 +78,11 @@ itemList2
;
cursorDefinition
- : CURSOR variableName ( LP_ cursorParameterDec ( COMMA_ cursorParameterDec
)* RP_)? ( RETURN rowtype)? IS select SEMI_
+ : CURSOR variableName (LP_ cursorParameterDec (COMMA_ cursorParameterDec)*
RP_)? (RETURN rowtype)? IS select SEMI_
;
functionDefinition
- : functionHeading ( DETERMINISTIC | PIPELINED | PARALLEL_ENABLE |
resultCacheClause )+ ( IS | AS ) ( declareSection ? body | callSpec )
+ : functionHeading (DETERMINISTIC | PIPELINED | PARALLEL_ENABLE |
resultCacheClause)+ (IS | AS) (declareSection ? body | callSpec)
;
procedureDefinition
@@ -74,15 +90,15 @@ procedureDefinition
;
itemList1
- :( typeDefinition | cursorDeclaration | itemDeclaration |
functionDeclaration | procedureDeclaration )*
+ :(typeDefinition | cursorDeclaration | itemDeclaration |
functionDeclaration | procedureDeclaration)*
;
cursorDeclaration
- : CURSOR variableName ( ( cursorParameterDec (COMMA_ cursorParameterDec )*
) )? RETURN rowtype SEMI_
+ : CURSOR variableName ((cursorParameterDec (COMMA_ cursorParameterDec)*))?
RETURN rowtype SEMI_
;
cursorParameterDec
- : variableName IN? dataType ( (COLON_ EQ_ | DEFAULT) expr )?
+ : variableName IN? dataType ((COLON_ EQ_ | DEFAULT) expr)?
;
rowtype
@@ -97,8 +113,8 @@ itemDeclaration
collectionVariableDecl
: variableName
(
- typeName ( COLON_ EQ_ ( qualifiedExpression | functionCall |
variableName ) )?
- | typeName ( COLON_ EQ_ ( collectionConstructor | variableName ) )?
+ typeName (COLON_ EQ_ (qualifiedExpression | functionCall |
variableName))?
+ | typeName (COLON_ EQ_ (collectionConstructor | variableName))?
| typeName MOD_ TYPE
)
SEMI_
@@ -133,11 +149,11 @@ typemark
;
collectionConstructor
- : typeName LP_ ( identifier (COMMA_ identifier)* )? RP_
+ : typeName LP_ (identifier (COMMA_ identifier)*)? RP_
;
constantDeclaration
- : variableName CONSTANT dataType ( NOT NULL )? ( COLON_ EQ_ | DEFAULT )
expr SEMI_
+ : variableName CONSTANT dataType (NOT NULL)? (COLON_ EQ_ | DEFAULT) expr
SEMI_
;
cursorVariableDeclaration
@@ -149,11 +165,11 @@ exceptionDeclaration
;
recordVariableDeclaration
- : variableName ( typeName | rowtypeAttribute | typeName MOD_ TYPE ) SEMI_
+ : variableName (typeName | rowtypeAttribute | typeName MOD_ TYPE) SEMI_
;
variableDeclaration
- : variableName dataType ( ( NOT NULL )? ( COLON_ EQ_ | DEFAULT ) expr )?
SEMI_
+ : variableName dataType ((NOT NULL)? (COLON_ EQ_ | DEFAULT) expr)? SEMI_
;
typeDefinition
@@ -161,22 +177,22 @@ typeDefinition
;
recordTypeDefinition
- : TYPE typeName IS RECORD LP_ fieldDefinition ( COMMA_ fieldDefinition )*
RP_ SEMI_
+ : TYPE typeName IS RECORD LP_ fieldDefinition (COMMA_ fieldDefinition)*
RP_ SEMI_
;
fieldDefinition
- : typeName dataType ( ( NOT NULL )? ( COLON_ EQ_ | DEFAULT ) expr )?
+ : typeName dataType ((NOT NULL)? (COLON_ EQ_ | DEFAULT) expr)?
;
refCursorTypeDefinition
- : TYPE typeName IS REF CURSOR ( RETURN (
+ : TYPE typeName IS REF CURSOR (RETURN (
(typeName MOD_ ROWTYPE)
| (typeName (MOD_ TYPE)?)
- ) )? SEMI_
+ ))? SEMI_
;
subtypeDefinition
- : SUBTYPE typeName IS dataType ( constraint | characterSetClause )? ( NOT
NULL )?
+ : SUBTYPE typeName IS dataType (constraint | characterSetClause)? (NOT
NULL)?
;
constraint
@@ -184,25 +200,25 @@ constraint
;
collectionTypeDefinition
- : TYPE typeName IS ( assocArrayTypeDef | varrayTypeDef |
nestedTableTypeDef ) SEMI_
+ : TYPE typeName IS (assocArrayTypeDef | varrayTypeDef |
nestedTableTypeDef) SEMI_
;
varrayTypeDef
- : ( VARRAY | (VARYING? ARRAY) ) LP_ INTEGER_ RP_ OF dataType ( NOT NULL )?
+ : (VARRAY | (VARYING? ARRAY)) LP_ INTEGER_ RP_ OF dataType (NOT NULL)?
;
nestedTableTypeDef
- : TABLE OF dataType ( NOT NULL )?
+ : TABLE OF dataType (NOT NULL)?
;
assocArrayTypeDef
- : TABLE OF dataType ( NOT NULL )? INDEX BY ( PLS_INTEGER | BINARY_INTEGER
| ( VARCHAR2 | VARCHAR2 | STRING ) LP_ INTEGER_ RP_ | LONG | typeAttribute |
rowtypeAttribute )
+ : TABLE OF dataType (NOT NULL)? INDEX BY (PLS_INTEGER | BINARY_INTEGER |
(VARCHAR2 | VARCHAR2 | STRING) LP_ INTEGER_ RP_ | LONG | typeAttribute |
rowtypeAttribute)
;
typeAttribute
- : ( variableName | objectName ) MOD_ TYPE
+ : (variableName | objectName) MOD_ TYPE
;
rowtypeAttribute
- : ( variableName | objectName ) MOD_ ROWTYPE
+ : (variableName | objectName) MOD_ ROWTYPE
;
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
index 9e284a84817..a1559108ef2 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
@@ -123,5 +123,7 @@ execute
| alterLockdownProfile
| alterPluggableDatabase
| createProcedure
+ | dropProcedure
+ | alterProcedure
) SEMI_?
;
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDDLStatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDDLStatementSQLVisitor.java
index fa81af70349..6d56cc56e1a 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDDLStatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleDDLStatementSQLVisitor.java
@@ -49,6 +49,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterO
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterPackageContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterLockdownProfileContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterPluggableDatabaseContext;
+import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterProcedureContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterSequenceContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterSessionContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.AlterSynonymContext;
@@ -101,6 +102,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropLo
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropOperatorContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropOutlineContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropPackageContext;
+import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropProcedureContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropRestorePointContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropRollbackSegmentContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DropSynonymContext;
@@ -174,6 +176,7 @@ import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.Ora
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleAlterOutlineStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleAlterPackageStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleAlterPluggableDatabaseStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleAlterProcedureStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleAlterSequenceStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleAlterSessionStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleAlterSynonymStatement;
@@ -219,6 +222,7 @@ import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.Ora
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDropOperatorStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDropOutlineStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDropPackageStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDropProcedureStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDropRestorePointStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDropRollbackSegmentStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDropSynonymStatement;
@@ -984,4 +988,14 @@ public final class OracleDDLStatementSQLVisitor extends
OracleStatementSQLVisito
OracleCreateProcedureStatement result = new
OracleCreateProcedureStatement();
return result;
}
+
+ @Override
+ public ASTNode visitAlterProcedure(final AlterProcedureContext ctx) {
+ return new OracleAlterProcedureStatement();
+ }
+
+ @Override
+ public ASTNode visitDropProcedure(final DropProcedureContext ctx) {
+ return new OracleDropProcedureStatement();
+ }
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleAlterProcedureStatement.java
similarity index 85%
copy from
shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
copy to
shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleAlterProcedureStatement.java
index af7f53ca7e3..663d76118ea 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleAlterProcedureStatement.java
@@ -17,12 +17,13 @@
package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl;
-import lombok.Setter;
import lombok.ToString;
-import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.CreateProcedureStatement;
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.AlterProcedureStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
-@Setter
+/**
+ * Oracle alter procedure statement.
+ */
@ToString(callSuper = true)
-public class OracleCreateProcedureStatement extends CreateProcedureStatement
implements OracleStatement {
+public final class OracleAlterProcedureStatement extends
AlterProcedureStatement implements OracleStatement {
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
index af7f53ca7e3..c55b972560c 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
@@ -22,7 +22,10 @@ import lombok.ToString;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.CreateProcedureStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
+/**
+ * Oracle create procedure statement.
+ */
@Setter
@ToString(callSuper = true)
-public class OracleCreateProcedureStatement extends CreateProcedureStatement
implements OracleStatement {
+public final class OracleCreateProcedureStatement extends
CreateProcedureStatement implements OracleStatement {
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleDropProcedureStatement.java
similarity index 85%
copy from
shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
copy to
shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleDropProcedureStatement.java
index af7f53ca7e3..ebd07bd02ac 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateProcedureStatement.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleDropProcedureStatement.java
@@ -17,12 +17,13 @@
package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl;
-import lombok.Setter;
import lombok.ToString;
-import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.CreateProcedureStatement;
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.DropProcedureStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
-@Setter
+/**
+ * Oracle drop procedure statement.
+ */
@ToString(callSuper = true)
-public class OracleCreateProcedureStatement extends CreateProcedureStatement
implements OracleStatement {
+public final class OracleDropProcedureStatement extends DropProcedureStatement
implements OracleStatement {
}
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/alter-procedure.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/alter-procedure.xml
index 0ac010ebaff..13c267683be 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/alter-procedure.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/alter-procedure.xml
@@ -23,4 +23,5 @@
<alter-procedure sql-case-id="alter_procedure_depends_on" />
<alter-procedure sql-case-id="alter_procedure_set_param" />
<alter-procedure sql-case-id="alter_procedure_reset_param" />
+ <alter-procedure sql-case-id="alter_procedure_for_oracle" />
</sql-parser-test-cases>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/drop-procedure.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/drop-procedure.xml
index ef573f50b3a..e53db924262 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/drop-procedure.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/ddl/drop-procedure.xml
@@ -19,4 +19,5 @@
<sql-parser-test-cases>
<drop-procedure sql-case-id="drop_procedure" />
<drop-procedure sql-case-id="drop_multiple_procedure" />
+ <drop-procedure sql-case-id="drop_procedure_for_oracle" />
</sql-parser-test-cases>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/alter-procedure.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/alter-procedure.xml
index 5fe25f715ef..a0d85fbe9b7 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/alter-procedure.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/alter-procedure.xml
@@ -23,4 +23,5 @@
<sql-case id="alter_procedure_depends_on" value="ALTER PROCEDURE
insert_data(integer, integer) DEPENDS ON EXTENSION myext"
db-types="PostgreSQL,openGauss" />
<sql-case id="alter_procedure_set_param" value="ALTER PROCEDURE
check_password(text) SET search_path = admin,pg_temp"
db-types="PostgreSQL,openGauss" />
<sql-case id="alter_procedure_reset_param" value="ALTER PROCEDURE
check_password(text) RESET search_path" db-types="PostgreSQL,openGauss" />
+ <sql-case id="alter_procedure_for_oracle" value="ALTER PROCEDURE
hr.remove_emp COMPILE" db-types="Oracle" />
</sql-cases>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/drop-procedure.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/drop-procedure.xml
index f4974041a10..f9446a1b36b 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/drop-procedure.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/ddl/drop-procedure.xml
@@ -19,4 +19,5 @@
<sql-cases>
<sql-case id="drop_procedure" value="DROP PROCEDURE do_db_maintenance()"
db-types="PostgreSQL,openGauss" />
<sql-case id="drop_multiple_procedure" value="DROP PROCEDURE
dbo.uspGetSalesbyMonth, dbo.uspUpdateSalesQuotes, dbo.uspGetSalesByYear;"
db-types="SQLServer" />
+ <sql-case id="drop_procedure_for_oracle" value="DROP PROCEDURE
hr.remove_emp" db-types="Oracle" />
</sql-cases>
