Examples of Extensions from Some Implementations

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 24.  Extensions to Standard SQL


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.

graphics/note_icon.gif

Standard SQL is primarily a non-procedural language, which means that you issue statements to the database server. The database server decides how to optimally execute the statement. Procedural languages allow the programmer not only to request the data to be retrieved or manipulated, but to tell the database server exactly how to carry out the request.


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 :

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

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:

 graphics/mysql_icon.gif 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; 
graphics/analysis_icon.gif

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:

 graphics/oracle_icon.gif graphics/input_icon.gif  mysql> SELECT CURRENT_DATE(),VERSION();  graphics/output_icon.gif +----------------+-----------+  current_date()  version()  +----------------+-----------+  1999-08-09      3.22.23b   +----------------+-----------+ 1 row in set (0.00 sec) mysql> 

Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net