This is an automated email from the ASF dual-hosted git repository.
terrymanu 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 0ad723bf5ef Support SQLServer table variable declaration parse (#38904)
0ad723bf5ef is described below
commit 0ad723bf5ef83f04f9746105ffa7d280316442ac
Author: Claire <[email protected]>
AuthorDate: Fri Jun 26 22:02:29 2026 +0800
Support SQLServer table variable declaration parse (#38904)
* support declare statement
* release-notes
* update
* update
* update version
* support declare statement
* support declare statement
* update
* update
* update
* add test
---
RELEASE-NOTES.md | 1 +
.../core/database/visitor/SQLVisitorRule.java | 2 +
.../main/antlr4/imports/sqlserver/DDLStatement.g4 | 12 +++--
.../sql/parser/autogen/SQLServerStatement.g4 | 1 +
.../type/SQLServerDDLStatementVisitor.java | 42 +++++++++++++++
.../sqlserver/ddl/DeclareVariableStatement.java | 43 +++++++++++++++
.../sqlserver/SQLServerDDLStatementAssert.java | 6 +++
.../SQLServerDeclareVariableStatementAssert.java | 61 ++++++++++++++++++++++
.../cases/parser/jaxb/RootSQLParserTestCases.java | 4 ++
...erverDeclareTableVariableStatementTestCase.java | 42 +++++++++++++++
.../main/resources/case/ddl/create-function.xml | 34 ++++++++++++
.../main/resources/case/ddl/create-procedure.xml | 42 +++++++++++++++
.../src/main/resources/case/ddl/create-trigger.xml | 5 ++
.../parser/src/main/resources/case/ddl/declare.xml | 13 +++++
.../sql/supported/ddl/create-function.xml | 48 +++++++++++++++++
.../sql/supported/ddl/create-procedure.xml | 46 ++++++++++++++++
.../resources/sql/supported/ddl/create-trigger.xml | 5 ++
.../main/resources/sql/supported/ddl/declare.xml | 1 +
18 files changed, 404 insertions(+), 4 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 52b9d1cb824..c5b587f741a 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -67,6 +67,7 @@
1. Encrypt: Support SqlServer update statement for Specifying a view as the
target object when use encrypt feature -
[#38896](https://github.com/apache/shardingsphere/pull/38896)
1. Sharding: Fix HASH_MOD routing mismatch for same negative numeric values
across numeric Java types with compatibility switch
`normalize-numeric-int-range` -
[#38327](https://github.com/apache/shardingsphere/pull/38327)
1. Proxy Native: Support building Proxy Native via GraalVM CE for JDK 25 -
[#38682](https://github.com/apache/shardingsphere/pull/38682)
+1. SQL Parser: Support SQLServer table variable declaration parse -
[#38904](https://github.com/apache/shardingsphere/pull/38904)
## Release 5.5.3
diff --git
a/parser/sql/engine/core/src/main/java/org/apache/shardingsphere/sql/parser/engine/core/database/visitor/SQLVisitorRule.java
b/parser/sql/engine/core/src/main/java/org/apache/shardingsphere/sql/parser/engine/core/database/visitor/SQLVisitorRule.java
index ea28239459b..3b042b3a844 100644
---
a/parser/sql/engine/core/src/main/java/org/apache/shardingsphere/sql/parser/engine/core/database/visitor/SQLVisitorRule.java
+++
b/parser/sql/engine/core/src/main/java/org/apache/shardingsphere/sql/parser/engine/core/database/visitor/SQLVisitorRule.java
@@ -855,6 +855,8 @@ public enum SQLVisitorRule {
UPDATE_STATISTICS("UpdateStatistics", SQLStatementType.DDL),
+ DECLARE_VARIABLE("DeclareVariable", SQLStatementType.DDL),
+
SPOOL("Spool", SQLStatementType.DAL),
START_REPLICA("StartReplica", SQLStatementType.DAL),
diff --git
a/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4
b/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4
index 7848792fa46..e3656414c1d 100644
---
a/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4
+++
b/parser/sql/engine/dialect/sqlserver/src/main/antlr4/imports/sqlserver/DDLStatement.g4
@@ -637,7 +637,11 @@ databaseLogOns
;
declareVariable
- : DECLARE (variable (COMMA_ variable)* | tableVariable)
+ : DECLARE tableVariable
+ ;
+
+declareLocalVariable
+ : DECLARE variable (COMMA_ variable)*
;
variable
@@ -658,7 +662,7 @@ tableVariableClause
;
variableTableColumnDefinition
- : columnName (dataTypeName | AS expr) (COLLATE collationName)? ((DEFAULT
expr)? | IDENTITY (LP_ NUMBER_ COMMA_ NUMBER_ RP_)?) ROWGUIDCOL?
variableTableColumnConstraint
+ : columnName (dataType | AS expr) (COLLATE collationName)? ((DEFAULT
expr)? | IDENTITY (LP_ NUMBER_ COMMA_ NUMBER_ RP_)?) ROWGUIDCOL?
variableTableColumnConstraint
;
variableTableColumnConstraint
@@ -742,7 +746,7 @@ functionOption
validStatement
: (createTable | alterTable | dropTable | truncateTable | insert
- | update | delete | select | setVariable | declareVariable) SEMI_?
+ | update | delete | select | setVariable | declareLocalVariable |
declareVariable) SEMI_?
;
procParameters
@@ -804,7 +808,7 @@ commonTableExpr
createTriggerClause
: (WITH dmlTriggerOption COMMA_ dmlTriggerOption)? (FOR | AFTER | INSTEAD
OF)
INSERT? COMMA_? UPDATE? COMMA_? DELETE? COMMA_? (WITH APPEND)? (NOT FOR
REPLICATION)?
- AS (compoundStatement | EXTERNAL NAME methodSpecifier)
+ AS (BEGIN? compoundStatement END? | EXTERNAL NAME methodSpecifier)
;
dmlTriggerOption
diff --git
a/parser/sql/engine/dialect/sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
b/parser/sql/engine/dialect/sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
index c67787e4a7c..cc6d5decbc2 100644
---
a/parser/sql/engine/dialect/sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
+++
b/parser/sql/engine/dialect/sqlserver/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/SQLServerStatement.g4
@@ -81,5 +81,6 @@ execute
| revert
| updateStatistics
| merge
+ | declareVariable
) SEMI_? EOF
;
diff --git
a/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/type/SQLServerDDLStatementVisitor.java
b/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/type/SQLServerDDLStatementVisitor.java
index c1df322b240..2b94d5a8eee 100644
---
a/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/type/SQLServerDDLStatementVisitor.java
+++
b/parser/sql/engine/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/engine/sqlserver/visitor/statement/type/SQLServerDDLStatementVisitor.java
@@ -55,6 +55,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Cre
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateTableDefinitionsContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateTriggerContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.CreateViewContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DeclareVariableContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DropColumnSpecificationContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DropConstraintNameContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DropDatabaseContext;
@@ -69,8 +70,12 @@ import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.Dro
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.DropViewContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.ModifyColumnSpecificationContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableConstraintContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TableVariableClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.TruncateTableContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.VariableTableColumnConstraintContext;
+import
org.apache.shardingsphere.sql.parser.autogen.SQLServerStatementParser.VariableTableColumnDefinitionContext;
import
org.apache.shardingsphere.sql.parser.engine.sqlserver.visitor.statement.SQLServerStatementVisitor;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dal.VariableSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.AlterDefinitionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.CreateDefinitionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.column.ColumnDefinitionSegment;
@@ -113,6 +118,7 @@ import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.tr
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.trigger.DropTriggerStatement;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.view.AlterViewStatement;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.view.CreateViewStatement;
+import
org.apache.shardingsphere.sql.parser.statement.sqlserver.ddl.DeclareVariableStatement;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.view.DropViewStatement;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.SelectStatement;
import
org.apache.shardingsphere.sql.parser.statement.core.value.collection.CollectionValue;
@@ -500,4 +506,40 @@ public final class SQLServerDDLStatementVisitor extends
SQLServerStatementVisito
public ASTNode visitDropSequence(final DropSequenceContext ctx) {
return new DropSequenceStatement(getDatabaseType(),
Collections.emptyList());
}
+
+ @Override
+ public ASTNode visitDeclareVariable(final DeclareVariableContext ctx) {
+ VariableSegment variableName = (VariableSegment)
visit(ctx.tableVariable().variableName());
+ Collection<ColumnDefinitionSegment> columnDefinitions = new
LinkedList<>();
+ for (TableVariableClauseContext each :
ctx.tableVariable().variTableTypeDefinition().tableVariableClause()) {
+ if (null != each.variableTableColumnDefinition()) {
+ columnDefinitions.add((ColumnDefinitionSegment)
visit(each.variableTableColumnDefinition()));
+ }
+ }
+ return new DeclareVariableStatement(getDatabaseType(), variableName,
columnDefinitions);
+ }
+
+ @Override
+ public ASTNode visitVariableTableColumnDefinition(final
VariableTableColumnDefinitionContext ctx) {
+ ColumnSegment column = (ColumnSegment) visit(ctx.columnName());
+ DataTypeSegment dataType = null == ctx.dataType() ? null :
(DataTypeSegment) visit(ctx.dataType());
+ return new ColumnDefinitionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), column, dataType,
+ isVariableTableColumnPrimaryKey(ctx),
isVariableTableColumnNotNull(ctx), getText(ctx));
+ }
+
+ private boolean isVariableTableColumnNotNull(final
VariableTableColumnDefinitionContext ctx) {
+ if (null == ctx.variableTableColumnConstraint()) {
+ return false;
+ }
+ VariableTableColumnConstraintContext constraint =
ctx.variableTableColumnConstraint();
+ return null != constraint.NOT() && null != constraint.NULL();
+ }
+
+ private boolean isVariableTableColumnPrimaryKey(final
VariableTableColumnDefinitionContext ctx) {
+ if (null == ctx.variableTableColumnConstraint()) {
+ return false;
+ }
+ VariableTableColumnConstraintContext constraint =
ctx.variableTableColumnConstraint();
+ return null != constraint.PRIMARY() && null != constraint.KEY();
+ }
}
diff --git
a/parser/sql/statement/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/statement/sqlserver/ddl/DeclareVariableStatement.java
b/parser/sql/statement/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/statement/sqlserver/ddl/DeclareVariableStatement.java
new file mode 100644
index 00000000000..c85d8332302
--- /dev/null
+++
b/parser/sql/statement/dialect/sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/statement/sqlserver/ddl/DeclareVariableStatement.java
@@ -0,0 +1,43 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sql.parser.statement.sqlserver.ddl;
+
+import lombok.Getter;
+import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dal.VariableSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.column.ColumnDefinitionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.DDLStatement;
+
+import java.util.Collection;
+
+/**
+ * Declare table variable statement for SQLServer.
+ */
+@Getter
+public final class DeclareVariableStatement extends DDLStatement {
+
+ private final VariableSegment variableName;
+
+ private final Collection<ColumnDefinitionSegment> columnDefinitions;
+
+ public DeclareVariableStatement(final DatabaseType databaseType, final
VariableSegment variableName, final Collection<ColumnDefinitionSegment>
columnDefinitions) {
+ super(databaseType);
+ this.variableName = variableName;
+ this.columnDefinitions = columnDefinitions;
+ }
+}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/dialect/sqlserver/SQLServerDDLStatementAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/dialect/sqlserver/SQLServerDDLStatementAssert.java
index d61a43ae076..339705fe44f 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/dialect/sqlserver/SQLServerDDLStatementAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/dialect/sqlserver/SQLServerDDLStatementAssert.java
@@ -20,11 +20,14 @@ package
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.statement.
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.DDLStatement;
+import
org.apache.shardingsphere.sql.parser.statement.sqlserver.ddl.DeclareVariableStatement;
import
org.apache.shardingsphere.sql.parser.statement.sqlserver.ddl.statistics.SQLServerUpdateStatisticsStatement;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.SQLCaseAssertContext;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.statement.ddl.dialect.sqlserver.type.SQLServerDeclareVariableStatementAssert;
import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.statement.ddl.dialect.sqlserver.type.SQLServerUpdateStatisticsStatementAssert;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.SQLParserTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.statistics.SQLServerUpdateStatisticsStatementTestCase;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.variable.SQLServerDeclareTableVariableStatementTestCase;
/**
* DDL statement assert for SQLServer.
@@ -43,5 +46,8 @@ public final class SQLServerDDLStatementAssert {
if (actual instanceof SQLServerUpdateStatisticsStatement) {
SQLServerUpdateStatisticsStatementAssert.assertIs(assertContext,
(SQLServerUpdateStatisticsStatement) actual,
(SQLServerUpdateStatisticsStatementTestCase) expected);
}
+ if (actual instanceof DeclareVariableStatement) {
+ SQLServerDeclareVariableStatementAssert.assertIs(assertContext,
(DeclareVariableStatement) actual,
(SQLServerDeclareTableVariableStatementTestCase) expected);
+ }
}
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/dialect/sqlserver/type/SQLServerDeclareVariableStatementAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/dialect/sqlserver/type/SQLServerDeclareVariableStatementAssert.java
new file mode 100644
index 00000000000..d3deb5d7b13
--- /dev/null
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/dialect/sqlserver/type/SQLServerDeclareVariableStatementAssert.java
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.statement.ddl.dialect.sqlserver.type;
+
+import lombok.AccessLevel;
+import lombok.NoArgsConstructor;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.ddl.column.ColumnDefinitionSegment;
+import
org.apache.shardingsphere.sql.parser.statement.sqlserver.ddl.DeclareVariableStatement;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.SQLCaseAssertContext;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.SQLSegmentAssert;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.asserts.segment.definition.ColumnDefinitionAssert;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.variable.SQLServerDeclareTableVariableStatementTestCase;
+
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
+
+/**
+ * Declare table variable statement assert for SQLServer.
+ */
+@NoArgsConstructor(access = AccessLevel.PRIVATE)
+public final class SQLServerDeclareVariableStatementAssert {
+
+ /**
+ * Assert declare table variable statement is correct with expected parser
result.
+ *
+ * @param assertContext assert context
+ * @param actual actual declare variable statement
+ * @param expected expected parser result
+ */
+ public static void assertIs(final SQLCaseAssertContext assertContext,
final DeclareVariableStatement actual,
+ final
SQLServerDeclareTableVariableStatementTestCase expected) {
+ SQLSegmentAssert.assertIs(assertContext, actual.getVariableName(),
expected.getVariableName());
+ assertThat(assertContext.getText("Variable name assertion error: "),
actual.getVariableName().getVariable(),
is(expected.getVariableName().getVariable()));
+ assertColumnDefinitions(assertContext, actual, expected);
+ }
+
+ private static void assertColumnDefinitions(final SQLCaseAssertContext
assertContext, final DeclareVariableStatement actual,
+ final
SQLServerDeclareTableVariableStatementTestCase expected) {
+ assertThat(assertContext.getText("Column definitions size assertion
error: "), actual.getColumnDefinitions().size(),
is(expected.getColumnDefinitions().size()));
+ int count = 0;
+ for (ColumnDefinitionSegment each : actual.getColumnDefinitions()) {
+ ColumnDefinitionAssert.assertIs(assertContext, each,
expected.getColumnDefinitions().get(count));
+ count++;
+ }
+ }
+}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
index 589e29fb41b..145d887a3c9 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
@@ -303,6 +303,7 @@ import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.s
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.service.SQLServerCreateServiceStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.service.SQLServerDropServiceStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.statistics.SQLServerUpdateStatisticsStatementTestCase;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.variable.SQLServerDeclareTableVariableStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.standard.AlterPackageStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.standard.CloseStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.standard.CommentStatementTestCase;
@@ -2041,6 +2042,9 @@ public final class RootSQLParserTestCases {
@XmlElement(name = "update-statistics")
private final List<SQLServerUpdateStatisticsStatementTestCase>
updateStatisticsStatementTestCases = new LinkedList<>();
+ @XmlElement(name = "declare-table-variable")
+ private final List<SQLServerDeclareTableVariableStatementTestCase>
declareTableVariableStatementTestCases = new LinkedList<>();
+
@XmlElement(name = "spool")
private final List<OracleSpoolStatementTestCase>
oracleSpoolStatementTestCases = new LinkedList<>();
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/dialect/sqlserver/variable/SQLServerDeclareTableVariableStatementTestCase.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/dialect/sqlserver/variable/SQLServerDeclareTableVariableStatementTestCase.java
new file mode 100644
index 00000000000..a558e4b5527
--- /dev/null
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/dialect/sqlserver/variable/SQLServerDeclareTableVariableStatementTestCase.java
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.dialect.sqlserver.variable;
+
+import lombok.Getter;
+import lombok.Setter;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.SQLParserTestCase;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.definition.ExpectedColumnDefinition;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.expr.ExpectedVariableSegment;
+
+import javax.xml.bind.annotation.XmlElement;
+import java.util.LinkedList;
+import java.util.List;
+
+/**
+ * Declare table variable statement test case for SQLServer.
+ */
+@Getter
+@Setter
+public final class SQLServerDeclareTableVariableStatementTestCase extends
SQLParserTestCase {
+
+ @XmlElement(name = "variable-name")
+ private ExpectedVariableSegment variableName;
+
+ @XmlElement(name = "column-definition")
+ private final List<ExpectedColumnDefinition> columnDefinitions = new
LinkedList<>();
+}
diff --git a/test/it/parser/src/main/resources/case/ddl/create-function.xml
b/test/it/parser/src/main/resources/case/ddl/create-function.xml
index a492633c7c7..c59893efd63 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-function.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-function.xml
@@ -22,6 +22,40 @@
<create-function
sql-case-id="create_function_with_multiple_output_parameters" />
<create-function sql-case-id="create_function_return_table" />
<create-function sql-case-id="create_function_with_execute" />
+ <create-function sql-case-id="create_function_with_declare">
+ <function-name name="ufnGetName" />
+ <sql-statements>
+ <sql-statement start-index="141" stop-index="160"
statement-class-simple-name="SetStatement" />
+ <sql-statement start-index="174" stop-index="186"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </create-function>
+ <create-function sql-case-id="create_function_with_declare_table_variable">
+ <function-name name="ufnTableVar" />
+ <sql-statements>
+ <sql-statement start-index="130" stop-index="152"
statement-class-simple-name="UpdateStatement" />
+ </sql-statements>
+ </create-function>
+ <create-function sql-case-id="create_function_or_alter_with_declare">
+ <function-name name="ufnRefresh" />
+ <sql-statements>
+ <sql-statement start-index="133" stop-index="147"
statement-class-simple-name="SetStatement" />
+ </sql-statements>
+ </create-function>
+ <create-function sql-case-id="create_function_with_declare_cursor">
+ <function-name name="ufnCursor" />
+ </create-function>
+ <create-function
sql-case-id="create_function_with_declare_multiple_variables">
+ <function-name name="ufnMulti" />
+ <sql-statements>
+ <sql-statement start-index="121" stop-index="132"
statement-class-simple-name="SetStatement" />
+ </sql-statements>
+ </create-function>
+ <create-function
sql-case-id="create_function_with_schemabinding_and_declare">
+ <function-name name="ufnSchema" />
+ </create-function>
+ <create-function
sql-case-id="create_function_with_returns_null_on_null_input_and_declare">
+ <function-name name="ufnNullOnNull" />
+ </create-function>
<create-function sql-case-id="create_function_inline_table" />
<create-function sql-case-id="create_function_declare_without_at" />
<create-function sql-case-id="create_function_call_spec_java" />
diff --git a/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
b/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
index 96562a8d460..82a6d2f4a10 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
@@ -263,4 +263,46 @@
<sql-statement start-index="651" stop-index="676"
statement-class-simple-name="SetStatement" />
</sql-statements>
</create-procedure>
+ <create-procedure sql-case-id="create_procedure_with_declare">
+ <procedure-name name="MyProcedure" />
+ <sql-statements>
+ <sql-statement start-index="167" stop-index="191"
statement-class-simple-name="SetStatement" />
+ <sql-statement start-index="205" stop-index="271"
statement-class-simple-name="SelectStatement" />
+ <sql-statement start-index="285" stop-index="325"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </create-procedure>
+ <create-procedure
sql-case-id="create_procedure_with_declare_table_variable">
+ <procedure-name name="uspUpdateTableVar" />
+ <sql-statements>
+ <sql-statement start-index="158" stop-index="202"
statement-class-simple-name="UpdateStatement" />
+ </sql-statements>
+ </create-procedure>
+ <create-procedure sql-case-id="create_procedure_or_alter_with_declare">
+ <procedure-name name="uspRefresh" />
+ <sql-statements>
+ <sql-statement start-index="126" stop-index="145"
statement-class-simple-name="SetStatement" />
+ <sql-statement start-index="159" stop-index="171"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </create-procedure>
+ <create-procedure sql-case-id="create_procedure_with_declare_cursor">
+ <procedure-name name="uspCursor" />
+ </create-procedure>
+ <create-procedure
sql-case-id="create_procedure_with_declare_multiple_variables">
+ <procedure-name name="uspMulti" />
+ <sql-statements>
+ <sql-statement start-index="107" stop-index="118"
statement-class-simple-name="SetStatement" />
+ </sql-statements>
+ </create-procedure>
+ <create-procedure
sql-case-id="create_procedure_with_recompile_and_declare">
+ <procedure-name name="uspRecompile" />
+ <sql-statements>
+ <sql-statement start-index="134" stop-index="143"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </create-procedure>
+ <create-procedure sql-case-id="create_procedure_proc_with_declare">
+ <procedure-name name="uspShort" />
+ <sql-statements>
+ <sql-statement start-index="90" stop-index="100"
statement-class-simple-name="SetStatement" />
+ </sql-statements>
+ </create-procedure>
</sql-parser-test-cases>
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 bb4a6448a97..ac30b30cb42 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
@@ -19,6 +19,11 @@
<sql-parser-test-cases>
<create-trigger sql-case-id="create_trigger" />
<create-trigger sql-case-id="create_trigger_with_database_scoped" />
+ <create-trigger sql-case-id="create_trigger_with_declare" />
+ <create-trigger sql-case-id="create_trigger_with_declare_table_variable" />
+ <create-trigger sql-case-id="create_trigger_or_alter_with_declare" />
+ <create-trigger sql-case-id="create_trigger_with_declare_cursor" />
+ <create-trigger
sql-case-id="create_trigger_with_declare_multiple_variables" />
<!-- <create-trigger sql-case-id="create_trigger_of_balance" />-->
<!-- <create-trigger sql-case-id="create_trigger_with_when" />-->
<!-- <create-trigger sql-case-id="create_trigger_after_update" />-->
diff --git a/test/it/parser/src/main/resources/case/ddl/declare.xml
b/test/it/parser/src/main/resources/case/ddl/declare.xml
index c546b788a6c..b82c71543b3 100644
--- a/test/it/parser/src/main/resources/case/ddl/declare.xml
+++ b/test/it/parser/src/main/resources/case/ddl/declare.xml
@@ -28,4 +28,17 @@
</projections>
</select>
</declare>
+
+ <declare-table-variable sql-case-id="declare_table_variable">
+ <variable-name variable="@MyTableVar" start-index="8" stop-index="18"
/>
+ <column-definition type="INT" not-null="true" start-index="28"
stop-index="45">
+ <column name="EmpID" />
+ </column-definition>
+ <column-definition type="INT" start-index="48" stop-index="67">
+ <column name="NewVacationHours" />
+ </column-definition>
+ <column-definition type="DATETIME" start-index="70" stop-index="90">
+ <column name="ModifiedDate" />
+ </column-definition>
+ </declare-table-variable>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-function.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-function.xml
index d37aff9fb6a..cea315de443 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-function.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-function.xml
@@ -43,6 +43,54 @@
SET @ISOweek=1;
RETURN(@ISOweek);
END" db-types="SQLServer" />
+ <sql-case id="create_function_with_declare" value="CREATE FUNCTION
dbo.ufnGetName (@Id INT)
+ RETURNS NVARCHAR(100) AS
+ BEGIN
+ DECLARE @Name NVARCHAR(100);
+ SET @Name = N'test';
+ SELECT @Name;
+ RETURN 0;
+ END" db-types="SQLServer" />
+ <sql-case id="create_function_with_declare_table_variable" value="CREATE
FUNCTION dbo.ufnTableVar (@Id INT)
+ RETURNS INT AS
+ BEGIN
+ DECLARE @T TABLE (Id INT);
+ UPDATE @T SET Id = @Id;
+ RETURN @Id;
+ END" db-types="SQLServer" />
+ <sql-case id="create_function_or_alter_with_declare" value="CREATE OR
ALTER FUNCTION dbo.ufnRefresh (@Id INT)
+ RETURNS INT AS
+ BEGIN
+ DECLARE @Val INT = 0;
+ SET @Val = @Id;
+ RETURN @Val;
+ END" db-types="SQLServer" />
+ <sql-case id="create_function_with_declare_cursor" value="CREATE FUNCTION
dbo.ufnCursor ()
+ RETURNS INT AS
+ BEGIN
+ DECLARE @cur CURSOR;
+ RETURN 0;
+ END" db-types="SQLServer" />
+ <sql-case id="create_function_with_declare_multiple_variables"
value="CREATE FUNCTION dbo.ufnMulti ()
+ RETURNS INT AS
+ BEGIN
+ DECLARE @A INT, @B INT = 1;
+ SET @A = @B;
+ RETURN @A;
+ END" db-types="SQLServer" />
+ <sql-case id="create_function_with_schemabinding_and_declare"
value="CREATE FUNCTION dbo.ufnSchema (@X INT)
+ RETURNS INT WITH SCHEMABINDING AS
+ BEGIN
+ DECLARE @Y INT = @X;
+ RETURN @Y;
+ END" db-types="SQLServer" />
+ <sql-case id="create_function_with_returns_null_on_null_input_and_declare"
value="CREATE FUNCTION dbo.ufnNullOnNull (@X INT)
+ RETURNS INT
+ WITH RETURNS NULL ON NULL INPUT AS
+ BEGIN
+ DECLARE @Y INT = @X;
+ RETURN @Y;
+ END" db-types="SQLServer" />
<sql-case id="create_function_inline_table" value="CREATE FUNCTION
Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE AS RETURN (
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
index df0e01b97ef..b04bd4fc0e8 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
@@ -76,4 +76,50 @@
<sql-case id="create_procedure_p1_select_set" value="CREATE PROCEDURE p1(x
int) BEGIN SELECT count(*) FROM t1; SELECT * FROM t1; SET @x = x; END;"
db-types="MySQL" />
<sql-case id="create_procedure_test_if_commit" value="CREATE PROCEDURE
test_if_commit() BEGIN ROLLBACK; SELECT IF (COUNT(*) > 0, "YES",
"NO") AS "IMPLICIT COMMIT" FROM trans; DELETE FROM trans;
COMMIT; END;" db-types="MySQL" />
<sql-case id="create_procedure_p1_repeat_handlers" value="create procedure
p1() begin declare counter integer default 0; declare continue handler for
sqlexception begin set counter = counter + 10;end; repeat if rand()>0.5 then
start transaction; end if; if rand()>0.5 then select var_samp(1),
exists(select 1 from t1 lock in share mode) from t1 into @a,@b; end if; if
rand()>0.5 then select var_samp(1), exists(select 1 from t1 for update) from
t1 into @a,@b; end if; if rand()&g [...]
+ <sql-case id="create_procedure_with_declare" value="CREATE PROCEDURE
dbo.MyProcedure @InputParam INT
+ AS
+ BEGIN
+ DECLARE @LocalVar NVARCHAR(100);
+ DECLARE @Count INT = 0;
+ SET @LocalVar = N'Hello';
+ SELECT @Count = COUNT(*) FROM dbo.SomeTable WHERE ID = @InputParam;
+ SELECT @LocalVar AS Msg, @Count AS Total;
+ END;" db-types="SQLServer" />
+ <sql-case id="create_procedure_with_declare_table_variable" value="CREATE
PROCEDURE dbo.uspUpdateTableVar
+ AS
+ BEGIN
+ DECLARE @MyTableVar TABLE (EmpID INT NOT NULL, NewVacationHours
INT);
+ UPDATE @MyTableVar SET NewVacationHours = 40;
+ END" db-types="SQLServer" />
+ <sql-case id="create_procedure_or_alter_with_declare" value="CREATE OR
ALTER PROCEDURE dbo.uspRefresh @Id INT
+ AS
+ BEGIN
+ DECLARE @Name NVARCHAR(50);
+ SET @Name = N'test';
+ SELECT @Name;
+ END" db-types="SQLServer" />
+ <sql-case id="create_procedure_with_declare_cursor" value="CREATE
PROCEDURE dbo.uspCursor
+ AS
+ BEGIN
+ DECLARE @cur CURSOR;
+ END" db-types="SQLServer" />
+ <sql-case id="create_procedure_with_declare_multiple_variables"
value="CREATE PROCEDURE dbo.uspMulti
+ AS
+ BEGIN
+ DECLARE @A INT, @B INT = 1;
+ SET @A = @B;
+ END" db-types="SQLServer" />
+ <sql-case id="create_procedure_with_recompile_and_declare" value="CREATE
PROCEDURE dbo.uspRecompile @X INT
+ WITH RECOMPILE
+ AS
+ BEGIN
+ DECLARE @Y INT = @X;
+ SELECT @Y;
+ END" db-types="SQLServer" />
+ <sql-case id="create_procedure_proc_with_declare" value="CREATE PROC
dbo.uspShort
+ AS
+ BEGIN
+ DECLARE @V INT;
+ SET @V = 1;
+ END" db-types="SQLServer" />
</sql-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 0e289492da6..f8a1b29cbe6 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
@@ -19,6 +19,11 @@
<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_with_declare" value="CREATE TRIGGER
dbo.trgCustomer ON Sales.Customer AFTER INSERT AS BEGIN DECLARE @Count INT = 0;
SET @Count = 1; SELECT @Count; END" db-types="SQLServer" />
+ <sql-case id="create_trigger_with_declare_table_variable" value="CREATE
TRIGGER dbo.trgTableVar ON dbo.Orders AFTER UPDATE AS BEGIN DECLARE @T TABLE
(Id INT); UPDATE @T SET Id = 1; END" db-types="SQLServer" />
+ <sql-case id="create_trigger_or_alter_with_declare" value="CREATE OR ALTER
TRIGGER dbo.trgRefresh ON dbo.Orders AFTER INSERT AS BEGIN DECLARE @Name
NVARCHAR(50); SET @Name = N'test'; END" db-types="SQLServer" />
+ <sql-case id="create_trigger_with_declare_cursor" value="CREATE TRIGGER
dbo.trgCursor ON dbo.Orders AFTER DELETE AS BEGIN DECLARE @cur CURSOR; END"
db-types="SQLServer" />
+ <sql-case id="create_trigger_with_declare_multiple_variables"
value="CREATE TRIGGER dbo.trgMulti ON dbo.Orders AFTER INSERT AS BEGIN DECLARE
@A INT, @B INT = 1; SET @A = @B; END" 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,Doris" />
<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" />
diff --git a/test/it/parser/src/main/resources/sql/supported/ddl/declare.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/declare.xml
index 38bcd5de17b..df55d63b61a 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/declare.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/declare.xml
@@ -18,4 +18,5 @@
<sql-cases>
<sql-case id="declare_cursor" value="DECLARE t_order_cursor BINARY CURSOR
FOR SELECT * FROM t_order;" db-types="PostgreSQL,openGauss" />
+ <sql-case id="declare_table_variable" value="DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL, NewVacationHours INT, ModifiedDate DATETIME);"
db-types="SQLServer" />
</sql-cases>
