Compound Statements in Sybase SQL Anywhere

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

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

By: Chrysanthus Date Published: 27 Aug 2012

Introduction

This is part 2 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 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.

Compound Statement
We have seen individual SQL statements in the previous division of the series. There are times when you would want to treat a group of SQL statements as one unit. In that case you would have to group them into what is called a compound statement. It is simple to do this. There is a SQL statement called the BEGIN statement. This statement has two reserved words: BEGIN and END. Just put all the SQL statements you want to treat as a unit between the BEGIN and END reserved words. You place semicolons at the end of each SQL statement as usual. You do not place a semicolon after BEGIN; you place a semicolon after END since from BEGIN to END is actually one statement, a compound statement.

The Syntax of the BEGIN statement is:

[ statement-label : ]
BEGIN [ [ NOT ] ATOMIC ]
   [ local-declaration; ... ]
   statement-list
   [ EXCEPTION [ exception-case ... ] ]
END [ statement-label ]

Apart from the reserved words, BEGIN and END and statement-list, every other item in the BEGIN statement is optional. We shall start by looking at these obligatory components.

Use of a Compound Statement
A compound statement is used to accomplish a particular task in SQL Programming. An example of such a task is an event handler, called trigger. When you are using SQL, you are doing programming. I have not approach the study of database in terms of programming in order to make the study easy, but that is what you are doing, with SQL.

The group of SQL statements of interest forms the, statement-list, enclosed by the BEGIN and END reserved words. You can have some instructions before the word BEGIN, as the [ statement-label : ] option in the syntax. These instructions do not end in semicolons; they are still part of the compound statement. You can also have something after the END reserved word, as the [ statement-label ] option in the syntax, which is still part of the compound statement.

The reserved word, END actually ends the BEGIN statement that encloses normal SQL statements.

Demonstration
For this part and in the rest of the series, when I say, start and connect to the database, you should execute the following commands in the command prompt as usual:

cd c:
dbeng12 -n wholesalesrv c:\wholesale\wholesale.db
dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"

When I say drop the connection, stop the database and stop the server, you should execute the following command at the command prompt; type y if necessary and close the Interactive SQL window (without saving the statement text):

    dbstop wholesalesrv

When I say type and execute a statement or group of statements in Interactive SQL, erase whatever is in the SQL Statement pane first.

- Start the database and connect to it.

- Type and execute the following compound statement:

BEGIN
    CREATE TABLE Invoice
    (
        InvoiceID INTEGER DEFAULT AUTOINCREMENT,
        DateAndTime TIMESTAMP DEFAULT TIMESTAMP,

        PRIMARY KEY (InvoiceID ASC)
    );
    INSERT INTO Invoice () VALUES ();
    SELECT * FROM Invoice;
END;

You should see a one-row result set in the Results pane of the Interactive SQL window. Remember, there is no semicolon after BEGIN. In the compound statement and for the statement-list, there are three normal SQL statements. The first SQL statement creates a table called, Invoice. The second SQL statement inserts one row into the table. The brackets of the second SQL statement are empty, because the first column is autonumber, meaning the DBMS inserts the values for you and the second column is default timestamp, which the DBMS inserts for you. The third statement selects all what is in the Invoice table, which is just one row.

That is it for this part of the series. Let us stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Compound Statements in Sybase SQL Anywhere

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

Introduction

Related Courses

Comments