intro
Let’s see how to declare variables through the SQL DECLARE statement in MySQL, PostgreSQL, SQL Server, and Oracle!
Just like in coding, writing complex SQL stored procedures or functions becomes easier with a mechanism to store local values. That's exactly what the SQL DECLARE statement is all about—a powerful tool to define variables within your SQL code.
In this article, you will learn what the DECLARE SQL statement is, how it varies across different databases, and how to use it effectively.
Let's dive in!
What Is the SQL DECLARE Statement?
In databases like SQL Server, MySQL, PostgreSQL, and Oracle, the SQL DECLARE statement is used to define variables. SQL variables work similarly to variables in various other programming languages, assigning a particular name to a data type and an optional initialization value.
Specifically, the DECLARE SQL statement is used to define local SQL variables in stored procedures, cursors, batch procedures, and functions. These variables are useful for storing intermediate results, handling dynamic queries, or controlling the flow of SQL code.
DECLARE SQL Statement: Syntax and Usage
The syntax and allowed usage of the SQL DECLARE statement varies between databases. Time to explore how to use DECLARE in MySQL, SQL Server, and Oracle!
MySQL
In MySQL, the DECLARE statement can be utilized to define:
Note: In MySQL, a stored program is either a stored procedure, function, trigger, or event.
Keep in mind that in MySQL, DECLARE is permitted only inside BEGIN ... END statements and must be at its start, before any other statement. In particular, DECLARE MySQL statements must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.
The syntax for declaring local variables is:
1
DECLARE var_name_1 [, var_name_2] ... var_type [DEFAULT value];
As you can see, the MySQL DECLARE statement enables you to define one or more variables at the same time. The optional DEFAULT clause specifies a default value for the variable(s). If the DEFAULT clause is missing, the initial value is always NULL.
You can use DECLARE in MySQL as in the sample stored procedure below:
1
CREATE PROCEDURE getSales(IN saleDate DATE)
2
BEGIN
3
-- initialize the total sales to 0
4
DECLARE totalSales INT DEFAULT 0;
5
6
-- calculate total sales for the given input date
7
SELECT SUM(salesAmount) INTO totalSales
8
FROM sales
9
WHERE salesDate = saleDate;
10
11
-- output the result
12
SELECT totalSales, saleDate;
13
END
The getSales() procedure uses a local variable to calculate and return the total sales for a specified date provided as an input parameter.
If you are wondering how to assign or re-assign a variable defined with DECLARE, that is done with the SET instruction.
Explore the official documentation to see how to use DECLARE for conditions, handlers, and cursors.
PostgreSQL
The DECLARE statement is part of PL/pgSQL, the PostgreSQL procedural language. A PL/pgSQL block is defined as:
1
[ DECLARE declarations ]
2
BEGIN
3
statements
4
END [ label ];
declarations are a list of variables defined as below:
1
var_name [ CONSTANT ] var_type [ NOT NULL ] [ DEFAULT = value ];
The keys to understanding this statement are:
For detailed examples, explore the official docs. In PostgreSQL, DECLARE can also be used to define cursors.
SQL Server
In SQL Server, the DECLARE statement allows you to declare variables in a transaction or the body of a batch or procedure. You can then assign values to these variables by using either a SET or SELECT statement. After declaration, all variables are initialized as NULL, unless a specific value is provided.
The syntax of the DECLARE SQL Server statement is:
1
DECLARE @local_var_name [AS] var_data_type [ = value ];
Where:
Use the SQL Server DECLARE statement as in the example below:
1
-- declare two variables
2
DECLARE @totalSales INT;
3
DECLARE @saleDate DATE = '2024-08-22';
4
5
-- calculate total sales for the given date
6
-- and assign the @totalSales variable to the date
7
SELECT @totalSales = SUM(salesAmount)
8
FROM sales
9
WHERE salesDate = @saleDate;
10
11
-- output the result
12
SELECT @totalSales, @saleDate;
As shown in the documentation, the same statement can also be used to declare cursors.
Oracle
In, PL/SQL, the DECLARE statement defines variables and constants you can then use in procedural statements. For a complete example, see the DECLARE SQL statement that follows:
1
DECLARE
2
v_hiredate DATE;
3
v_deptno NUMBER(2) NOT NULL := 15;
4
v_location VARCHAR2(13) := 'New York';
5
c_comm CONSTANT NUMBER := 42;
You can assign a value to an Oracle variable in the following ways:
Declaring Variables in DbVisualizer
Defining variables in SQL is definitely useful, but it comes with some challenges. The main issue is that not all databases support the SQL DECLARE statement. Plus, each database implements it with its own syntax.
Sometimes, you just want to use variables to create parameterized SQL statements. That is where DbVisualizer variables come in!
DbVisualizer is a powerful database client that supports many database management systems and has the highest user satisfaction in the market. Among its many features, it supports the definition of custom variables for parameterized queries, as shown in the example below:
1
SELECT *
2
FROM employee
3
WHERE first_name LIKE '${First Name || Phil}$'
4
AND age > ${Age || 20}$
The complete syntax for defining DbVisualizer variables is:
1
${name || value || type || options}$
Thanks to these variables, you can build parameterized SQL statements. When trying to execute such a query in DbVisualizer, you will be prompted for values:
This mechanism is especially useful when running the same SQL query repeatedly with different data inputs.
Conclusion
In this guide, you saw what DECLARE is, why it is useful, and how to use it in MySQL, PostgreSQL, SQL Server, and Oracle database management systems. You now know that the SQL DECLARE statement allows you to define variables and cursors.
As learned here, dealing with variables is simple with a powerful tool like DbVisualizer. This fully-featured database client supports several DBMS technologies, has advanced query optimization capabilities, and can generate ERD-type schemas with a single click. Try DbVisualizer for free!
FAQ
Is the SQL DECLARE statement part of the standard?
No, the SQL DECLARE statement is not part of the ISO/ANSI SQL standard. It is specific to certain SQL dialects, such as T-SQL for SQL Server, PL/SQL for Oracle, and PL/pgSQL for PostgreSQL. That means each database system has its own syntax and features for variable declaration and handling.
How to declare a variable in SQL Server?
You can declare a variable In SQL Server using the DECLARE statement, as in the following example:
1
DECLARE @counter INT = 0;
This will declare a variable named @counter of type INT with an initial value of 0.
Does PostgreSQL support the DECLARE statement?
Yes, PostgreSQL supports the DECLARE SQL statement, but it's used within the context of PL/pgSQL, PostgreSQL's procedural language. In PL/pgSQL, you can declare variables in a BEGIN...END block or inside functions.
When is declaring a variable in SQL useful?
Declaring a variable in SQL is useful when you need to store and manipulate data within SQL procedural code. Variables are helpful for intermediate calculations, controlling execution flow, or reusing values across multiple statements. They enhance readability and maintainability by letting you define descriptive names instead of hard-coded values.
Why should I use an SQL client?
Consider using an SQL client like DbVisualizer to build, manage, and maintain your database stack — the maintenance of any database is not a walk in the park, and with DbVisualizer being a feature-rich SQL client rated as the #1 choice for software companies worldwide, you cannot go wrong when choosing it as your connoisseur. Give it a try today—you will not be disappointed!
