Declarations in Sybase Compound Statements

Handling Sybase Events with Triggers and Procedures Using SQL – Part 3

Forward: In this part of the series, we look at declarations in compound statements in Sybase SQL Anywhere.

By: Chrysanthus Date Published: 27 Aug 2012

Introduction

This is part 3 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 look at declarations in compound statements in Sybase SQL Anywhere.

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.

Declaration
A compound statement is a group of statements that behave as a unit to accomplish a particular task. Sometimes a compound statement would need data to work with. Any data used by a compound statement has to be declared first before it can be used. This declaration is done just below the reserved word, BEGIN.

You can declare the following classes of data just below the BEGIN reserved word:

- Variables
- Cursors
- Temporary tables
- Exceptions (error identifiers)

There are four items above. We shall look at variables in this part of the series. I may not treat the other items in this series, since they are not appropriate to other DBMS.

Variable
A variable here is similar to what goes on in mathematics, but not quite. You can have something like,

DECLARE myVar INTERGER = 5;

You must begin a declaration with the reserved word, DECLARE. This is followed by a variable name of your choice that will hold the datum (singular for data). Then you have the data type of the datum. Then you can have the assignment operator, =, and then the datum itself. It is possible to have a declaration as follows:

DECLARE myVar INTERGER;

In this case the assignment operator and the datum are omitted. The datum can be placed into the variable, later down in the compound statement.

The syntax of a variable is:

DECLARE variable-name [, ... ] data-type [ { = | DEFAULT } initial-value ]

Here, initial-value is like 5 above. You can use the reserved word, DEFAULT in place of the assignment operator. You can declare several variables at once separated by commas, like in

DECLARE myVar, yourVar, hisVar INTEGER

Here, the three variables are of the same type, INTEGER

Note: A DECLARE statement ends with semicolon.

Examples
- Start the database and connect to it.
- Read, type and execute the following compound statement:

BEGIN
    DECLARE InvoiceID1 SMALLINT = 1;
    DECLARE ProductID1 SMALLINT = 1;

    CREATE TABLE InvoiceDetails
    (
        InvoiceID SMALLINT,
        ProductID SMALLINT,

        PRIMARY KEY (InvoiceID, ProductID),
     );
    INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (InvoiceID1, ProductID1);
    SELECT * FROM InvoiceDetails;
END;

You should see the one row data of the InvoiceDetails table in the results pane. In the INSERT statement, instead of typing the values, I typed InvoiceID1 and ProductID1, which are variables, declared, that hold the values (1, 1). Note, the column name InvoiceID is not the same thing as the variable name, InvoiceID1 (which has 1 attached to it).

Local Declaration
Anything that you declare can only be seen (used) in the compound statement in which it is declared. It cannot be seen outside the compound statement.

I hope at this point you appreciate the use of declared variables. We continue in the next part of the series.

Chrys

Broad Network

Related Articles

Declarations in Sybase Compound Statements

Handling Sybase Events with Triggers and Procedures Using SQL – Part 3

Introduction

Related Courses

Comments