InformIT: Sams Teach Yourself SQL in 24 Hours > Examples of Extensions from Some Implementations

Table of Contents

Examples of Extensions from Some Implementations

Both PL/SQL and Transact-SQL are considered fourth-generation programming languages. Both are procedural languages, whereas SQL is a non-procedural language. We will also briefly discuss another implementation of SQL called MySQL, which can be downloaded from the Internet.

The non-procedural language SQL includes statements such as the following:

INSERT
UPDATE
DELETE
SELECT
COMMIT
ROLLBACK

A SQL extension considered a procedural language includes all the preceding statements, commands, and functions of standard SQL. In addition, extensions include statements such as

Variable declarations
Cursor declarations
Conditional statements
Loops
Error handling
Variable incrementing
Date conversions
Wildcard operators
Triggers
Stored procedures

These statements allow the programmer to have more control over the way data is handled in a procedural language.

Transact-SQL

Transact-SQL is a procedural language, which means you tell the database the hows and wheres of finding and manipulating data. SQL is non-procedural, and the database decides the hows and wheres of selecting and manipulating data. Some highlights of Transact-SQL's capabilities include declaring local and global variables, cursors, error handling, triggers, stored procedures, loops, wildcard operators, date conversions, and summarized reports.

An example Transact-SQL statement follows:

   
IF (SELECT AVG(COST) FROM PRODUCTS_TBL) > 50
BEGIN
  PRINT "LOWER ALL COSTS BY 10 PERCENT."
END
ELSE
  PRINT "COSTS ARE REASONABLE."
END

This is a very simple Transact-SQL statement. It states that if the average cost in the PRODUCTS_TBL table is greater than 50, the text " LOWER ALL COSTS BY 10 PERCENT ." will be printed. If the average cost is less than or equal to 50, the text "COSTS ARE REASONABLE." will be printed.

Notice the use of the IF...ELSE statement to evaluate conditions of data values. The PRINT command is also a new command. These additional options are not even a drop in the bucket of Transact-SQL capabilities.

PL/SQL

PL/SQL is Oracle's extension to SQL. Like Transact-SQL, PL/SQL is a procedural language. PL/SQL is structured in logical blocks of code. There are three sections to a PL/SQL block, two of which are optional. The first section is the DECLARE section and is optional. The DECLARE section contains variables, cursors, and constants. The second section is called the PROCEDURE section. The PROCEDURE section contains the conditional commands and SQL statements. This section is where the block is controlled. The PROCEDURE section is mandatory. The third section is called the EXCEPTION section. The EXCEPTION section defines how the program should handle errors and user-defined exceptions. The EXCEPTION section is optional. Highlights of PL/SQL include the use of variables, constants, cursors, attributes, loops, handling exceptions, displaying output to the programmer, transactional control, stored procedures, triggers, and packages.

An example PL/SQL statement follows:

   
DECLARE
  CURSOR EMP_CURSOR IS SELECT EMP_ID, LAST_NAME, FIRST_NAME, MID_INIT
                       FROM EMPLOYEE_TBL;
  EMP_REC EMP_CURSOR%ROWTYPE;
BEGIN
  OPEN EMP_CURSOR;
  LOOP
    FETCH EMP_CURSOR INTO EMP_REC;
    EXIT WHEN EMP_CURSOR%NOTFOUND;
    IF (EMP_REC.MID_INIT IS NULL) THEN
      UPDATE EMPLOYEE_TBL
      SET MID_INIT = 'X'
      WHERE EMP_ID = EMP_REC.EMP_ID;
      COMMIT;
    END IF;
  END LOOP;
  CLOSE EMP_CURSOR;
END;

There are two out of three sections being used in this example: the DECLARE section and the PROCEDURE section. First, a cursor called EMP_CURSOR is defined by a query. Second, a variable called EMP_REC is declared, whose values have the same data type (%ROWTYPE) as each column in the defined cursor. The first step in the PROCEDURE section (after BEGIN) is to open the cursor. After the cursor is opened, you use the LOOP command to scroll through each record of the cursor, which is eventually terminated by END LOOP. The EMPLOYEE_TBL table should be updated for all rows in the cursor. If the middle initial of an employee is NULL, the update sets the middle initial to 'X'. Changes are committed and the cursor is eventually closed.

MySQL

MySQL is a multi-user, multi-threaded SQL database client/server implementation. MySQL consists of a server daemon, a terminal monitor client program, and several client programs and libraries. The main goals of MySQL are speed, robustness, and ease of use. MySQL was originally designed to provide faster access to very large databases.

MySQL can be downloaded from http://www.mysql.com. To install a MySQL binary distribution, you need GNU gunzip to uncompress the distribution and a reasonable TAR to unpack the distribution. The binary distribution file will be named mysql- VERSION - OS .tar.gz, where VERSION is the version ID of MySQL, and OS is the name of the operating system.

An example query from a MySQL database follows:

   
   

   mysql> SELECT CURRENT_DATE(),VERSION();

   
+----------------+-----------+
| current_date() | version() |
+----------------+-----------+
| 1999-08-09     | 3.22.23b  |
+----------------+-----------+

1 row in set (0.00 sec)
mysql>

Interactive SQL Statements | Next Section

Previous Section