Назад в раздел
The PITCHERS TableThe PITCHERS table can be created using the following Transact-SQL statement: INPUT: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. Table 19.2. Data for the PITCHERS table.
The TEAMS TableThe TEAMS table can be created using the following Transact-SQL statement: INPUT: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. Table 19.3. Data for the TEAMS table.
Declaring Local VariablesEvery 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: SYNTAX: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. Declaring Global VariablesIf 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. Table 19.4. SQL Server global variables.
Using VariablesThe 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. Using Variables to Store DataVariables 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. INPUT: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: INPUT: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. Retrieving Data into Local VariablesVariables 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. Example 19.1This example retrieves the name of the player in the BASEBALL database who has the highest batting average and plays for the Portland Beavers. INPUT: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 ANALYSIS:This example was broken down into three queries to illustrate the use of variables. The PRINT CommandOne 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: 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.2Example 19.2 repeats Example 19.1 but prints the player's name at the end. INPUT: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. Flow ControlProbably 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. BEGIN and END StatementsTransact-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: SYNTAX:BEGIN statement1 statement2 statement3... END IF...ELSE StatementsOne 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: SYNTAX: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.3Example 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). INPUT: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 Example 19.4This 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. INPUT: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 ConditionThe 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. Example 19.5In this example the EXISTS keyword evaluates a condition in the IF. The condition is specified by using a SELECT statement. INPUT: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 Testing a Query's ResultThe 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. Example 19.6This 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 (>). INPUT: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. The WHILE LoopAnother popular programming construct that Transact-SQL supports is the WHILE loop. This command has the following syntax: SYNTAX:WHILE logical_expression
statement(s)
Example 19.7The 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). INPUT: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!"
The BREAK CommandYou 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. Example 19.8Notice the placement of the BREAK statement after the evaluation of the first condition in the IF. INPUT: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!" ANALYSIS:The BREAK command caused the loop to be exited when the @COUNT variable equaled 8. The CONTINUE CommandThe 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. Example 19.9Notice the placement of the CONTINUE statement after the evaluation of the first condition in the IF. INPUT: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!" ANALYSIS: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. Using the WHILE Loop to Scroll Through a TableSQL 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. Example 19.10You 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. INPUT: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!" ANALYSIS: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. Transact-SQL Wildcard OperatorsThe 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.
Creating Summarized Reports Using COMPUTETransact-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: INPUT:select name, homeruns from batters compute sum(homeruns) ANALYSIS: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: SYNTAX:COMPUTE FUNCTION(expression) [BY expression] where the FUNCTION might include SUM, MAX, MIN, etc. and EXPRESSION is usually a column name or alias. Date ConversionsSybase 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. SYNTAX:CONVERT (datatype [(length)], expression, format) The following date formats are available with SQL Server when using the CONVERT
function:
INPUT:select "PayDate" = convert(char(15), paydate, 107) from payment_table where customer_id = 012845 OUTPUT:PayDate --------------- May 1, 1997 ANALYSIS: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. SQL Server Diagnostic Tools--SET CommandsTransact-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:
SummaryDay 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. Q&A
WorkshopThe 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." Quiz
Exercises
   
© Copyright, Macmillan Computer Publishing. All rights reserved. |
 |
|
