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. | 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 : 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> |