Today's material supplements the previous presentations, as Transact-SQL is a supplement to the accepted SQL standard. Today's goals are to
Day 13, "Advanced SQL Topics," briefly covered static SQL. The examples on Day 13 depicted the use of embedded SQL in third-generation programming languages such as C. With this method of programming, the embedded SQL code does not change and is, therefore, limited. On the other hand, you can write dynamic SQL to perform the same functions as a procedural programming language and allow conditions to be changed within the SQL code.
As we have mentioned during the discussion of virtually every topic in this book, almost every database vendor has added many extensions to the language. Transact-SQL is the Sybase and Microsoft SQL Server database product. Oracle's product is PL/SQL. Each of these languages contains the complete functionality of everything we have discussed so far. In addition, each product contains many extensions to the ANSI SQL standard.
To illustrate the use of these SQL extensions to create actual programming logic, we are using Sybase and Microsoft SQL Server's Transact-SQL language. It contains most of the constructs found in third-generation languages, as well as some SQL Server-specific features that turn out to be very handy tools for the database programmer. (Other manufacturers' extensions contain many of these features and more.)
Everyone reading this book can use Transact-SQL--casual relational database programmers who occasionally write queries as well as developers who write applications and create objects such as triggers and stored procedures.
NOTE: Users of Sybase and Microsoft SQL Server who want to explore the true capabilities of relational database programming must use the Transact-SQL features.
SQL extensions overcome SQL's limits as a procedural language. For example, Transact-SQL enables you to maintain tight control over your database transactions and to write procedural database programs that practically render the programmer exempt from exhausting programming tasks.
Day 19 covers the following key features of Transact-SQL:
On Day 9, "Creating and Maintaining Tables," we discussed data types. When creating tables in SQL, you must specify a specific data type for each column.
NOTE: Data types vary between implementations of SQL because of the way each database server stores data. For instance, Oracle uses selected data types, whereas Sybase and Microsoft's SQL Server have their own data types.
Sybase and Microsoft's SQL Server support the following data types.
Money values are inserted into a table using the dollar sign; for example:
insert payment_tbl (customer_id, paydate, pay_amt) values (012845, "May 1, 1997", $2099.99)
The data type bit is often used to flag certain rows of data within a table. The value stored within a column whose data type is bit is either a 1 or 0. For example, the value 1 may signify the condition true, whereas 0 denotes a false condition. The following example uses the bit data type to create a table containing individual test scores:
create table test_flag ( ind_id int not null, test_results int not null, result_flag bit not null)
The column result_flag is defined as a bit column, where the bit character represents either a pass or fail, where pass is true and fail is false.
Throughout the rest of the day, pay attention to the data types used when creating tables and writing Transact-SQL code.
NOTE: The code in today's examples uses both uppercase and lowercase. Although SQL keywords are not case sensitive in most implementations of SQL, always check your implementation.
All right, enough talk. To actually run the examples today, you will need to build the following database tables in a database named BASEBALL.
The BASEBALL database consists of three tables used to track typical baseball information: the BATTERS table, the PITCHERS table, and the TEAMS table. This database will be used in examples throughout the rest of today.
NAME char(30) TEAM int AVERAGE float HOMERUNS int RBIS int
The table above can be created using the following Transact-SQL statement:
1> create database BASEBALL on default 2> go 1> use BASEBALL 2> go 1> create table BATTERS ( 2> NAME char(30), 3> TEAM int, 4> AVERAGE float, 5> HOMERUNS int, 6> RBIS int) 7> go
Line 1 creates the database. You specify the database BASEBALL and then create the table BATTERS underneath BASEBALL.
Enter the data in Table 19.1 into the BATTERS table.
NOTE: The command go that separates each Transact-SQL statement in the preceding example is not part of Transact-SQL. go's purpose is to pass each statement from a front-end application to SQL Server.
| Name | Team | Average | Homeruns | RBIs |
| Billy Brewster | 1 | .275 | 14 | 46 |
| John Jackson | 1 | .293 | 2 | 29 |
| Phil Hartman | 1 | .221 | 13 | 21 |
| Jim Gehardy | 2 | .316 | 29 | 84 |
| Tom Trawick | 2 | .258 | 3 | 51 |
| Eric Redstone | 2 | .305 | 0 | 28 |
The PITCHERS table can be created using the following Transact-SQL statement:
1> use BASEBALL 2> go 1> create table PITCHERS ( 2> NAME char(30), 3> TEAM int, 4> WON int, 5> LOST int, 6> ERA float) 7> go
Enter the data in Table 19.2 into the PITCHERS table.
| Name | Team | Won | Lost | Era |
| Tom Madden | 1 | 7 | 5 | 3.46 |
| Bill Witter | 1 | 8 | 2 | 2.75 |
| Jeff Knox | 2 | 2 | 8 | 4.82 |
| Hank Arnold | 2 | 13 | 1 | 1.93 |
| Tim Smythe | 3 | 4 | 2 | 2.76 |
The TEAMS table can be created using the following Transact-SQL statement:
1> use BASEBALL 2> go 1> create table TEAMS ( 2> TEAM_ID int, 3> CITY char(30), 4> NAME char(30), 5> WON int, 6> LOST int, 7> TOTAL_HOME_ATTENDANCE int, 8> AVG_HOME_ATTENDANCE int) 9> go
Enter the data in Table 19.3 into the TEAMS table.
|
Team_ID |
City | Name | Won | Lost | Total_Home_Attendance | Avg_Home_Attendance |
| 1 | Portland | Beavers | 72 | 63 | 1,226,843 | 19,473 |
| 2 | Washington | Representatives | 50 | 85 | 941,228 | 14,048 |
| 3 | Tampa | Sharks | 99 | 36 | 2,028,652 | 30,278 |
Every programming language enables some method for declaring local (or global) variables that can be used to store data. Transact-SQL is no exception. Declaring a variable using Transact-SQL is an extremely simple procedure. The keyword that must be used is the DECLARE keyword. The syntax looks like this:
declare @variable_name data_type
To declare a character string variable to store players' names, use the following statement:
1> declare @name char(30) 2> go
Note the @ symbol before the variable's name. This symbol is required and is used by the query processor to identify variables.
If you delve further into the Transact-SQL documentation, you will notice that the @@ symbol precedes the names of some system-level variables. This syntax denotes SQL Server global variables that store information.
Declaring your own global variables is particularly useful when using stored procedures. SQL Server also maintains several system global variables that contain information that might be useful to the database system user. Table 19.4 contains the complete list of these variables. The source for this list is the Sybase SQL Server System 10 documentation.
| Variable Name | Purpose |
| @@char_convert | 0 if character set conversion is in effect. |
| @@client_csid | Client's character set ID. |
| @@client_csname | Client's character set name. |
| @@connections | Number of logons since SQL Server was started. |
| @@cpu_busy | Amount of time, in ticks, the CPU has been busy since SQL Server was started. |
| @@error | Contains error status. |
| @@identity | Last value inserted into an identity column. |
| @@idle | Amount of time, in ticks, that SQL Server has been idle since started. |
| @@io_busy | Amount of time, in ticks, that SQL Server has spent doing I/O. |
| @@isolation | Current isolation level of the Transact-SQL program. |
| @@langid | Defines local language ID. |
| @@language | Defines the name of the local language. |
| @@maxcharlen | Maximum length of a character. |
| @@max_connections | Maximum number of connections that can be made with SQL Server. |
| @@ncharsize | Average length of a national character. |
| @@nestlevel | Nesting level of current execution. |
| @@pack_received | Number of input packets read by SQL Server since it was started. |
| @@pack_sent | Number of output packets sent by SQL Server since it was started. |
| @@packet_errors | Number of errors that have occurred since SQL Server was started. |
| @@procid | ID of the currently executing stored procedure. |
| @@rowcount | Number of rows affected by the last command. |
| @@servername | Name of the local SQL Server. |
| @@spid | Process ID number of the current process. |
| @@sqlstatus | Contains status information. |
| @@textsize | Maximum length of text or image data returned with SELECT statement. |
| @@thresh_hysteresis | Change in free space required to activate a threshold. |
| @@timeticks | Number of microseconds per tick. |
| @@total_errors | Number of errors that have occurred while reading or writing. |
| @@total_read | Number of disk reads since SQL Server was started. |
| @@total_write | Number of disk writes since SQL Server was started. |
| @@tranchained | Current transaction mode of the Transact-SQL program. |
| @@trancount | Nesting level of transactions. |
| @@transtate | Current state of a transaction after a statement executes. |
| @@version | Date of the current version of SQL Server. |
The DECLARE keyword enables you to declare several variables with a single statement (although this device can sometimes look confusing when you look at your code later). An example of this type of statement appears here:
1> declare @batter_name char(30), @team int, @average float 2> go
The next section explains how to use variables it to perform useful programming operations.
Variables are available only within the current statement block. To execute a block of statements using the Transact-SQL language, the go statement is executed. (Oracle uses the semicolon for the same purpose.) The scope of a variable refers to the usage of the variable within the current Transact-SQL statement.
You cannot initialize variables simply by using the = sign. Try the following statement and note that an error will be returned.
1> declare @name char(30) 2> @name = "Billy Brewster" 3> go
You should have received an error informing you of the improper syntax used in line 2. The proper way to initialize a variable is to use the SELECT command. (Yes, the same command you have already mastered.) Repeat the preceding example using the correct syntax:
1> declare @name char(30) 2> select @name = "Billy Brewster" 3> go
This statement was executed correctly, and if you had inserted additional statements before executing the go statement, the @name variable could have been used.
Variables often store data that has been retrieved from the database. They can be used with common SQL commands, such as SELECT, INSERT, UPDATE, and DELETE. Example 19.1 illustrates the use of variables in this manner.
This example retrieves the name of the player in the BASEBALL database who has the highest batting average and plays for the Portland Beavers.
1> declare @team_id int, @player_name char(30), @max_avg float 2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland" 3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id 4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg 5> go
This example was broken down into three queries to illustrate the use of variables.
One other useful feature of Transact-SQL is the PRINT command that enables you to print output to the display device. This command has the following syntax:
PRINT character_string
Although PRINT displays only character strings, Transact-SQL provides a number of useful functions that can convert different data types to strings (and vice versa).
Example 19.2 repeats Example 19.1 but prints the player's name at the end.
1> declare @team_id int, @player_name char(30), @max_avg float 2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland" 3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id 4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg 5> print @player_name 6> go
Note that a variable can be used within a WHERE clause (or any other clause) just as if it were a constant value.
Probably the most powerful set of Transact-SQL features involves its capability to control program flow. If you have programmed with other popular languages such as C, COBOL, Pascal, and Visual Basic, then you are probably already familiar with control commands such as IF...THEN statements and loops. This section contains some of the major commands that allow you to enforce program flow control.
Transact-SQL uses the BEGIN and END statements to signify the beginning and ending points of blocks of code. Other languages use brackets ({}) or some other operator to signify the beginning and ending points of functional groups of code. These statements are often combined with IF...ELSE statements and WHILE loops. Here is a sample block using BEGIN and END:
BEGIN statement1 statement2 statement3... END
One of the most basic programming constructs is the IF...ELSE statement. Nearly every programming language supports this construct, and it is extremely useful for checking the value of data retrieved from the database. The Transact-SQL syntax for the IF...ELSE statement looks like this:
if (condition)
begin
(statement block)
end
else if (condition)
begin
statement block)
end
.
.
.
else
begin
(statement block)
end
Note that for each condition that might be true, a new BEGIN/END block of statements was entered. Also, it is considered good programming practice to indent statement blocks a set amount of spaces and to keep this number of spaces the same throughout your application. This visual convention greatly improves the readability of the program and cuts down on silly errors that are often caused by simply misreading the code.
Example 19.3 extends Example 19.2 by checking the player's batting average. If the player's average is over .300, the owner wants to give him a raise. Otherwise, the owner could really care less about the player!
Example 19.3 uses the IF...ELSE statement to evaluate conditions within the statement. If the first condition is true, then specified text is printed; alternative text is printed under any other conditions (ELSE).
1> declare @team_id int, @player_name char(30), @max_avg float 2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland" 3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id 4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg 5> if (@max_avg > .300) 6> begin 7> print @player_name 8> print "Give this guy a raise!" 9> end 10> else 11> begin 12> print @player_name 13> print "Come back when you're hitting better!" 14> end 15> go
This new IF statement enables you to add some programming logic to the simple BASEBALL database queries. Example 19.4 adds an IF...ELSE IF...ELSE branch to the code in Ex- ample 19.3.
1> declare @team_id int, @player_name char(30), @max_avg float 2> select @team_id = TEAM_ID from TEAMS where CITY = "Portland" 3> select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id 4> select @player_name = NAME from BATTERS where AVERAGE = @max_avg 5> if (@max_avg > .300) 6> begin 7> print @player_name 8> print "Give this guy a raise!" 9> end 10> else if (@max_avg > .275) 11> begin 12> print @player_name 13> print "Not bad. Here's a bonus!" 14> end 15> else 16> begin 17> print @player_name 18> print "Come back when you're hitting better!" 19> end 20> go
Transact-SQL also enables you to check for a condition associated with an IF statement. These functions can test for certain conditions or values. If the function returns TRUE, the IF branch is executed. Otherwise, if provided, the ELSE branch is executed, as you saw in the previous example.
The EXISTS keyword ensures that a value is returned from a SELECT statement. If a value is returned, the IF statement is executed. Example 19.5 illustrates this logic.
In this example the EXISTS keyword evaluates a condition in the IF. The condition is specified by using a SELECT statement.
1> if exists (select * from TEAMS where TEAM_ID > 5) 2> begin 3> print "IT EXISTS!!" 4> end 5> else 6> begin 7> print "NO ESTA AQUI!" 8> end
The IF statement can also test the result returned from a SELECT query. Example 19.6 implements this feature to check for the maximum batting average among players.
This example is similar to Example 19.5 in that it uses the SELECT statement to define a condition. This time, however, we are testing the condition with the greater than sign (>).
1> if (select max(AVG) from BATTERS) > .400 2> begin 3> print "UNBELIEVABLE!!" 4> end 5> else 6> print "TED WILLIAMS IS GETTING LONELY!" 7> end
We recommend experimenting with your SQL implementation's IF statement. Think of several conditions you would be interested in checking in the BASEBALL (or any other) database. Run some queries making use of the IF statement to familiarize yourself with its use.
Another popular programming construct that Transact-SQL supports is the WHILE loop. This command has the following syntax:
WHILE logical_expression
statement(s)
The WHILE loop continues to loop through its statements until the logical expression it is checking returns a FALSE. This example uses a simple WHILE loop to increment a local variable (named COUNT).
1> declare @COUNT int 2> select @COUNT = 1 3> while (@COUNT < 10) 4> begin 5> select @COUNT = @COUNT + 1 6> print "LOOP AGAIN!" 7> end 8> print "LOOP FINISHED!"
NOTE: Example 19.7 implements a simple FOR loop. Other implementations of SQL, such as Oracle's PL/SQL, actually provide a FOR loop statement. Check your documentation to determine whether the system you are using supports this useful command.
You can issue the BREAK command within a WHILE loop to force an immediate exit from the loop. The BREAK command is often used along with an IF test to check some condition. If the condition check succeeds, you can use the BREAK command to exit from the WHILE loop. Commands immediately following the END command are then executed. Example 19.8 illustrates a simple use of the BREAK command. It checks for some arbitrary number (say @COUNT = 8). When this condition is met, it breaks out of the WHILE loop.
Notice the placement of the BREAK statement after the evaluation of the first condition in the IF.
1> declare @COUNT int 2> select @COUNT = 1 3> while (@COUNT < 10) 4> begin 5> select @COUNT = @COUNT + 1 6> if (@COUNT = 8) 7> begin 8> break 9> end 10> else 11> begin 12> print "LOOP AGAIN!" 13> end 14> end 15> print "LOOP FINISHED!"
The BREAK command caused the loop to be exited when the @COUNT variable equaled 8.
The CONTINUE command is also a special command that can be executed from within a WHILE loop. The CONTINUE command forces the loop to immediately jump back to the beginning, rather than executing the remainder of the loop and then jumping back to the beginning. Like the BREAK command, the CONTINUE command is often used with an IF statement to check for some condition and then force an action, as shown in Example 19.9.
Notice the placement of the CONTINUE statement after the evaluation of the first condition in the IF.
1> declare @COUNT int 2> select @COUNT = 1 3> while (@COUNT < 10) 4> begin 5> select @COUNT = @COUNT + 1 6> if (@COUNT = 8) 7> begin 8> continue 9> end 10> else 11> begin 12> print "LOOP AGAIN!" 13> end 14> end 15> print "LOOP FINISHED!"
Example 19.9 is identical to Example 19.8 except that the CONTINUE command replaces the BREAK command. Now instead of exiting the loop when @COUNT = 8, it simply jumps back to the top of the WHILE statement and continues.
SQL Server and many other database systems have a special type of object--the cursor--that enables you to scroll through a table's records one record at a time. (Refer to Day 13.) However, some database systems (including SQL Server pre-System 10) do not support the use of scrollable cursors. Example 19.10 gives you an idea of how to use a WHILE loop to implement a rough cursor-type functionality when that functionality is not automatically supplied.
You can use the WHILE loop to scroll through tables one record at a time. Transact-SQL stores the rowcount variable that can be set to tell SQL Server to return only one row at a time during a query. If you are using another database product, determine whether your product has a similar setting. By setting rowcount to 1 (its default is 0, which means unlimited), SQL Server returns only one record at a time from a SELECT query. You can use this one record to perform whatever operations you need to perform. By selecting the contents of a table into a temporary table that is deleted at the end of the operation, you can select out one row at a time, deleting that row when you are finished. When all the rows have been selected out of the table, you have gone through every row in the table! (As we said, this is a very rough cursor functionality!) Let's run the example now.
1> set rowcount 1 2> declare @PLAYER char(30) 3> create table temp_BATTERS ( 4> NAME char(30), 5> TEAM int, 6> AVERAGE float, 7> HOMERUNS int, 8> RBIS int) 9> insert temp_BATTERS 10> select * from BATTERS 11> while exists (select * from temp_BATTERS) 12> begin 13> select @PLAYER = NAME from temp_BATTERS 14> print @PLAYER 15> delete from temp_BATTERS where NAME = @PLAYER 16> end 17> print "LOOP IS DONE!"
Note that by setting the rowcount variable, you are simply modifying the number of rows returned from a SELECT. If the WHERE clause of the DELETE command returned five rows, five rows would be deleted! Also note that the rowcount variable can be reset repeatedly. Therefore, from within the loop, you can query the database for some additional information by simply resetting rowcount to 1 before continuing with the loop.
The concept of using wildcard conditions in SQL was introduced on Day 3, "Expressions, Conditions, and Operators." The LIKE operator enables you to use wildcard conditions in your SQL statements. Transact-SQL extends the flexibility of wildcard conditions. A summary of Transact-SQL's wildcard operators follows.
Transact-SQL also has a mechanism for creating summarized database reports. The command, COMPUTE, has very similar syntax to its counterpart in SQL*Plus. (See Day 20, "SQL*Plus.")
The following query produces a report showing all batters, the number of home runs hit by each batter, and the total number of home runs hit by all batters:
select name, homeruns from batters compute sum(homeruns)
In the previous example, COMPUTE alone performs computations on the report as a whole, whereas COMPUTE BY performs computations on specified groups and the entire report, as the following example shows:
COMPUTE FUNCTION(expression) [BY expression] where the FUNCTION might include SUM, MAX, MIN, etc. and EXPRESSION is usually a column name or alias.
Sybase and Microsoft's SQL Server can insert dates into a table in various formats; they can also extract dates in several different types of formats. This section shows you how to use SQL Server's CONVERT command to manipulate the way a date is displayed.
CONVERT (datatype [(length)], expression, format)
The following date formats are available with SQL Server when using the CONVERT
function:
| Format code | Format picture |
| 100 | mon dd yyyy hh:miAM/PM |
| 101 | mm/dd/yy |
| 102 | yy.mm.dd |
| 103 | dd/mm/yy |
| 104 | dd.mm.yy |
| 105 | dd-mm-yy |
| 106 | dd mon yy |
| 107 | mon dd, yy |
| 108 | hh:mi:ss |
| 109 | mon dd, yyyy hh:mi:ss:mmmAM/PM |
| 110 | mm-dd-yy |
| 111 | yy/mm/dd |
| 112 | yymmdd |
select "PayDate" = convert(char(15), paydate, 107) from payment_table where customer_id = 012845
PayDate --------------- May 1, 1997
The preceding example uses the format code 107 with the CONVERT function. According to the date format table, code 107 will display the date in the format mon dd, yy.
Transact-SQL provides a list of SET commands that enable you to turn on various options that help you analyze Transact-SQL statements. Here are some of the popular SET commands:
Transact-SQL also has the following commands that help to control what is displayed as part of the output from your queries:
NOTE: If you are concerned with tuning your SQL statements, refer to Day 15, "Streamlining SQL Statements for Improved Performance."
Day 19 introduces a number of topics that add some teeth to your SQL programming expertise. The basic SQL topics that you learned earlier in this book are extremely important and provide the foundation for all database programming work you undertake. However, these topics are just a foundation. The SQL procedural language concepts explained yesterday and today build on your foundation of SQL. They give you, the database programmer, a great deal of power when accessing data in your relational database.
The Transact-SQL language included with the Microsoft and Sybase SQL Server database products provide many of the programming constructs found in popular third- and fourth-generation languages. Its features include the IF statement, the WHILE loop, and the capability to declare and use local and global variables.
Keep in mind that Day 19 is a brief introduction to the features and techniques of Transact-SQL code. Feel free to dive head first into your documentation and experiment with all the tools that are available to you. For more detailed coverage of Transact-SQL, refer to the Microsoft SQL Server Transact-SQL documentation.
A Programming constructs such as the FOR loop, the WHILE loop, and the CASE statement are extensions to ANSI SQL. Therefore, the use of these items varies widely among database systems. For instance, Oracle provides the FOR loop, whereas Transact-SQL (SQL Server) does not. Of course, a WHILE loop can increment a variable within the loop, which can simulate the FOR loop.
Q I am developing a Windows (or Macintosh) application in which the user interface consists of Windows GUI elements, such as windows and dialog boxes. Can I use the PRINT statement to issue messages to the user?
A SQL is entirely platform independent. Therefore, issuing the PRINT statement will not pop up a message box. To output messages to the user, your SQL procedures can return predetermined values that indicate success or failure. Then the user can be notified of the status of the queries. (The PRINT command is most useful for debugging because a PRINT statement executed within a stored procedure will not be output to the screen anyway.)
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."
2. True or False: Static SQL is less flexible than Dynamic SQL, although the performance of static SQL can be better.
2. Write a brief set of statements that will check for the existence of some condition. If this condition is true, perform some operation. Otherwise, perform another operation.
© Copyright, Macmillan Computer Publishing. All rights reserved.