“Launch A Product” – Product Launch Video Tutorial
Step-By-Step “Look Over My Shoulder” Video Tutorial Walk Through By Andrew Fox Showing Exactly How This Maverick Marketer Generated 0,565 Within 14 Days Of His Product Launch And How You Can Replicate His Simple Formula.
“Launch A Product” – Product Launch Video Tutorial
Handling Sybase Events with Triggers and Procedures Using SQL – Part 9
Division 5
Introduction
This is part 9 of my series, Handling Sybase Events with Triggers and Stored Procedures Using SQL. This series is the fifth division of my larger series, Database. You must have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we see how to work with SQL variables in Sybase SQL Anywhere 12.
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.
THE SQL SET Statement
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 CREATE VARIABLE Statement
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;
Life of a Variable
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
To arrive at any of the parts of this division, type the corresponding title below in the search box of this page and click Search (use menu if available).
Handling Sybase Events with Triggers and Procedures Using SQL – Part 1
Handling Sybase Events with Triggers and Procedures Using SQL – Part 2
Handling Sybase Events with Triggers and Procedures Using SQL – Part 3
Handling Sybase Events with Triggers and Procedures Using SQL – Part 4
Handling Sybase Events with Triggers and Procedures Using SQL – Part 5
Handling Sybase Events with Triggers and Procedures Using SQL – Part 6
Handling Sybase Events with Triggers and Procedures Using SQL – Part 7
Handling Sybase Events with Triggers and Procedures Using SQL – Part 8
Handling Sybase Events with Triggers and Procedures Using SQL – Part 9
Handling Sybase Events with Triggers and Procedures Using SQL – Part 10
Handling Sybase Events with Triggers and Procedures Using SQL – Part 11
Handling Sybase Events with Triggers and Procedures Using SQL – Part 12
Handling Sybase Events with Triggers and Procedures Using SQL – Part 13
Handling Sybase Events with Triggers and Procedures Using SQL – Part 14
Handling Sybase Events with Triggers and Procedures Using SQL – Part 15
Â
Written by Chrys
This SQL tutorial lives at: www.programmingvideotutorials.com The video describes how to create databases, tables, and indexes.
Video Rating: 4 / 5
Photoshop Tutorials Pro
Discover the Fastest and Easiest Way to Learn Photoshop. Master the Hottest Graphics Program Ever! Affiliates earn 70% commissions. High Conversions with an extremely Low Refund rate. Get Free Affiliate tools: http://www.pstutorialspro.com/affiliates.
Photoshop Tutorials Pro
Sculpture and art tutorials
Body casting and bronze casting, a collection of sculpture tutorials for the professional sculptors, art students, craft people and hobbyist. These quality books provide real value to a wide market and are priced to sell easily. Full promotional support.
Sculpture and art tutorials
This SQL tutorial lives at: www.programmingvideotutorials.com The video covers the SQL data manipulation statements – insert, update, and delete.