Working with SQL Variables in Sybase SQL Anywhere
Handling Sybase Events with Triggers and Procedures Using SQL – Part 9
Division 5
Forward: In this part of the series, we see how to work with SQL variables in Sybase SQL Anywhere 12.
By: Chrysanthus Date Published: 28 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Variable in a compound Statement
The BEGIN statement is a compound statement. The purpose of the BEGIN statement is to group normal SQL statements together. You can declare a variable inside a compound statement but only the SQL statements in the compound statement will be able to see (use) the variable. Such a variable cannot be seen (used) outside the compound statement. We have seen this before, but here we shall look at it in more detail.
A simplified form of the declaration syntax is:
DECLARE variable-name [, ... ] data-type
[ { = | DEFAULT } initial-value ]
initial-value :
special-value
| string
| [ - ] number
| NULL
special-value :
CURRENT {
DATABASE
| DATE
| TIME
| TIMESTAMP
| USER }
| USER
variable-name is a name of your choice that you give. The initial-value can be a special value (see below). It can be a string, which is text in single quotes. It can be a number preceded by the negative sign if the number is negative. It can also be NULL. Consider NULL as empty.
The special-value can be the phrase, CURRENT DATABASE, or CURRENT DATE, or CURRENT TIME, or CURRENT TIMESTAMP or CURRENT USER.
If you declare a variable without assigning a value to it, you can assign a value later down in the code using a SQL statement known as the SET statement. The syntax of the SET statement is:
SET identifier = expression;
Here, identifier means variable-name. For simplicity, consider expression as a value (number or string) or a variable-name.
You will try the code samples in this tutorial. Before you try any code, start the server and the wholesale database and connect to it using Interactive SQL. When you finish with the samples, drop the connection, stop the database and stop the server. Do these same things to try the code samples in the other parts of the series.
Try the following code:
BEGIN
DECLARE str CHAR(30);
SET str = 'I am some text.';
SELECT str;
END;
The Results pane should finally display the string value, ‘I am some text.’. The heading in the Results pane is the name of the variable. In the code, the variable is first declared without assigning any value to it. Such a variable without an assigned value actually has NULL assigned to it. Lower down in the code, the SET statement assigns a value to the variable. The SELECT statement displays the value of the variable.
The above code is equivalent to the following where in the declaration the value is assign.
BEGIN
DECLARE str CHAR(30) = 'I am some text.';
SELECT str;
END;
There is no SET statement here. You do not need it since in the declaration the value is assigned.
The SET statement assigns a new value to a variable. The variable must have been previously created using a CREATE VARIABLE statement (see below) or DECLARE statement, or it must be an OUTPUT parameter for a procedure.
The SET statement can also be used to change the value of a variable. You do that by just assigning a new value to the variable.
The DECLARE statement is used to declare a variable only inside a compound statement, and the variable declared is seen only inside that compound statement. There is a SQL statement that can be used to create (declare) a variable outside or inside a compound statement. Its syntax is:
CREATE [ OR REPLACE ] VARIABLE identifier data-type [ { = | DEFAULT } initial-value
Here, identifier, means variable-name. You can replace (OR REPLACE) the value of a previously created variable. The other features of the syntax are the same as those for the DECLARE statement.
Try the following code:
CREATE VARIABLE myVar INT = 123;
SELECT myVar;
The Results pane finally displays the value, 123. For data-type the abbreviation for INTEGER is INT, which is used above.
Try the following code, which shows that a variable created outside a compound statement with the CREATE VARIABLE statement, can be seen inside the compound statement:
CREATE VARIABLE herVar INT = 87;
BEGIN
SELECT herVar;
END;
A variable created with the CREATE VARIABLE statement inside a compound statement can be seen outside the compound statement. The following code illustrates this:
BEGIN
CREATE VARIABLE hisVar INT = 50;
END;
SELECT hisVar;
A variable created with the DECLARE statement inside a compound statement cannot be seen outside the compound statement. So the following code will not work:
BEGIN
DECLARE yourVar INT = 66;
END;
SELECT yourVar;
A variable created with the DECLARE statement can only be created in a compound statement. It persists as long the compound statement is operating. On the other hand, a variable created by the CREATE VARIABLE statement, can be created inside a compound statement or outside the compound statement.
When you connect to a database, you can use the CREATE VARIABLE statement to create a variable inside or outside a compound statement. After that you own this variable. In other words, a variable created with the CREATE VARIABLE statement belongs to a connection. That variable will persist until there is disconnection of the connection. However, while the connection is on the variable can be dropped (erased).
The syntax to drop a variable is:
DROP VARIABLE [ IF EXISTS ] identifier;
When you drop a variable, you can still create it again. Try the following code, which illustrates this:
CREATE VARIABLE smallVal INT;
DROP VARIABLE smallVal;
CREATE VARIABLE smallVal INT = 5;
SELECT smallVal;
The following code will not work after the variable has been created (the first time). This is because the connection has not been dropped and the variable created has also not been dropped; so the variable created is still alive and the second creation issues an error message. Try the code:
CREATE VARIABLE variab INT;
CREATE VARIABLE variab INT;
SELECT variab;
Variable and Table Columns
A variable can be used in a SQL expression, anywhere a column name is allowed. In a SELECT statement select-list, the variable is matched to an alias (AS) name.
That is it for this tutorial. We continue in the next part of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT