?? ch19.htm
字號:
operations.<H4><FONT COLOR="#000077">Using Variables to Store Data</FONT></H4><P>Variables are available only within the current statement block. To execute ablock of statements using the Transact-SQL language, the <TT>go</TT> statement isexecuted. (Oracle uses the semicolon for the same purpose.) The scope of a variablerefers to the usage of the variable within the current Transact-SQL statement.</P><P>You cannot initialize variables simply by using the <TT>=</TT> sign. Try the followingstatement and note that an error will be returned.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @name char(30)</B>2> <B>@name = "Billy Brewster"</B>3><B> go</B></FONT></PRE><P>You should have received an error informing you of the improper syntax used inline 2. The proper way to initialize a variable is to use the <TT>SELECT</TT> command.(Yes, the same command you have already mastered.) Repeat the preceding example usingthe correct syntax:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @name char(30)</B>2> <B>select @name = "Billy Brewster"</B>3> <B>go</B></FONT></PRE><P>This statement was executed correctly, and if you had inserted additional statementsbefore executing the <TT>go</TT> statement, the <TT>@name</TT> variable could havebeen used.<H4><FONT COLOR="#000077">Retrieving Data into Local Variables</FONT></H4><P>Variables often store data that has been retrieved from the database. They canbe used with common SQL commands, such as <TT>SELECT</TT>, <TT>INSERT</TT>, <TT>UPDATE</TT>,and <TT>DELETE</TT>. Example 19.1 illustrates the use of variables in this manner.<H4><FONT COLOR="#000077">Example 19.1</FONT></H4><P>This example retrieves the name of the player in the <TT>BASEBALL</TT> databasewho has the highest batting average and plays for the Portland Beavers.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @team_id int, @player_name char(30), @max_avg float</B>2> <B>select @team_id = TEAM_ID from TEAMS where CITY = "Portland"</B>3> <B>select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id</B>4> <B>select @player_name = NAME from BATTERS where AVERAGE = @max_avg</B>5> <B>go</B></FONT></PRE><H5>ANALYSIS:</H5><P>This example was broken down into three queries to illustrate the use of variables.<H3><FONT COLOR="#000077">The PRINT Command</FONT></H3><P>One other useful feature of Transact-SQL is the <TT>PRINT</TT> command that enablesyou to print output to the display device. This command has the following syntax:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">PRINT character_string</FONT></PRE><P>Although <TT>PRINT</TT> displays only character strings, Transact-SQL providesa number of useful functions that can convert different data types to strings (andvice versa).<H4><FONT COLOR="#000077">Example 19.2</FONT></H4><P>Example 19.2 repeats Example 19.1 but prints the player's name at the end.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @team_id int, @player_name char(30), @max_avg float</B>2> <B>select @team_id = TEAM_ID from TEAMS where CITY = "Portland"</B>3> <B>select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id</B>4> <B>select @player_name = NAME from BATTERS where AVERAGE = @max_avg</B>5> <B>print @player_name</B>6> <B>go</B></FONT></PRE><P>Note that a variable can be used within a <TT>WHERE</TT> clause (or any otherclause) just as if it were a constant value.<H2><FONT COLOR="#000077">Flow Control</FONT></H2><P>Probably the most powerful set of Transact-SQL features involves its capabilityto control program flow. If you have programmed with other popular languages suchas C, COBOL, Pascal, and Visual Basic, then you are probably already familiar withcontrol commands such as <TT>IF...THEN</TT> statements and loops. This section containssome of the major commands that allow you to enforce program flow control.<H3><FONT COLOR="#000077">BEGIN and END Statements</FONT></H3><P>Transact-SQL uses the <TT>BEGIN</TT> and <TT>END</TT> statements to signify thebeginning and ending points of blocks of code. Other languages use brackets (<TT>{}</TT>)or some other operator to signify the beginning and ending points of functional groupsof code. These statements are often combined with <TT>IF...ELSE</TT> statements and<TT>WHILE</TT> loops. Here is a sample block using <TT>BEGIN</TT> and <TT>END</TT>:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">BEGIN statement1 statement2 statement3...END</FONT></PRE><H3><FONT COLOR="#000077">IF...ELSE Statements</FONT></H3><P>One of the most basic programming constructs is the <TT>IF...ELSE</TT> statement.Nearly every programming language supports this construct, and it is extremely usefulfor checking the value of data retrieved from the database. The Transact-SQL syntaxfor the <TT>IF...ELSE</TT> statement looks like this:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">if (condition)begin (statement block)endelse if (condition)begin statement block)end...elsebegin (statement block)end</FONT></PRE><P>Note that for each condition that might be true, a new <TT>BEGIN/END</TT> blockof statements was entered. Also, it is considered good programming practice to indentstatement blocks a set amount of spaces and to keep this number of spaces the samethroughout your application. This visual convention greatly improves the readabilityof the program and cuts down on silly errors that are often caused by simply misreadingthe code.<H4><FONT COLOR="#000077">Example 19.3</FONT></H4><P>Example 19.3 extends Example 19.2 by checking the player's batting average. Ifthe player's average is over .300, the owner wants to give him a raise. Otherwise,the owner could really care less about the player!</P><P>Example 19.3 uses the <TT>IF...ELSE</TT> statement to evaluate conditions withinthe statement. If the first condition is <TT>true</TT>, then specified text is printed;alternative text is printed under any other conditions (<TT>ELSE</TT>).</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @team_id int, @player_name char(30), @max_avg float</B>2> <B>select @team_id = TEAM_ID from TEAMS where CITY = "Portland"</B>3> <B>select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id</B>4> <B>select @player_name = NAME from BATTERS where AVERAGE = @max_avg</B>5> <B>if (@max_avg > .300)</B>6> <B>begin</B>7> <B> print @player_name</B>8> <B> print "Give this guy a raise!"</B>9> <B>end</B>10> <B>else</B>11> <B>begin</B>12> <B> print @player_name</B>13> <B>print "Come back when you're hitting better!"</B>14> <B>end</B>15><B> go</B></FONT></PRE><H4><FONT COLOR="#000077">Example 19.4</FONT></H4><P>This new <TT>IF</TT> statement enables you to add some programming logic to thesimple <TT>BASEBALL</TT> database queries. Example 19.4 adds an <TT>IF...ELSE IF...ELSE</TT>branch to the code in Ex- ample 19.3.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @team_id int, @player_name char(30), @max_avg float</B>2><B> select @team_id = TEAM_ID from TEAMS where CITY = "Portland"</B>3> <B>select @max_avg = max(AVERAGE) from BATTERS where TEAM = @team_id</B>4> <B>select @player_name = NAME from BATTERS where AVERAGE = @max_avg</B>5> <B>if (@max_avg > .300)</B>6> <B>begin</B>7> <B> print @player_name</B>8> <B> print "Give this guy a raise!"</B>9> <B>end</B>10> <B>else if (@max_avg > .275)</B>11> <B>begin</B>12> <B>print @player_name</B>13> <B> print "Not bad. Here's a bonus!"</B>14> <B>end</B>15> <B>else</B>16><B> begin</B>17> <B>print @player_name</B>18> <B>print "Come back when you're hitting better!"</B>19> <B>end</B>20><B> go</B></FONT></PRE><P>Transact-SQL also enables you to check for a condition associated with an <TT>IF</TT>statement. These functions can test for certain conditions or values. If the functionreturns <TT>TRUE</TT>, the <TT>IF</TT> branch is executed. Otherwise, if provided,the <TT>ELSE</TT> branch is executed, as you saw in the previous example.<H3><FONT COLOR="#000077">The EXISTS Condition</FONT></H3><P>The <TT>EXISTS</TT> keyword ensures that a value is returned from a <TT>SELECT</TT>statement. If a value is returned, the <TT>IF</TT> statement is executed. Example19.5 illustrates this logic.<H4><FONT COLOR="#000077">Example 19.5</FONT></H4><P>In this example the <TT>EXISTS</TT> keyword evaluates a condition in the <TT>IF</TT>.The condition is specified by using a <TT>SELECT</TT> statement.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1><B> if exists (select * from TEAMS where TEAM_ID > 5)</B>2> <B>begin</B>3> <B> print "IT EXISTS!!"</B>4> <B>end</B>5> <B>else</B>6><B> begin</B>7> <B> print "NO ESTA AQUI!"</B>8> <B>end</B></FONT></PRE><H3><FONT COLOR="#000077">Testing a Query's Result</FONT></H3><P>The <TT>IF</TT> statement can also test the result returned from a <TT>SELECT</TT>query. Example 19.6 implements this feature to check for the maximum batting averageamong players.<H4><FONT COLOR="#000077">Example 19.6</FONT></H4><P>This example is similar to Example 19.5 in that it uses the <TT>SELECT</TT> statementto define a condition. This time, however, we are testing the condition with thegreater than sign (<TT>></TT>).</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>if (select max(AVG) from BATTERS) > .400</B>2> <B>begin</B>3> <B>print "UNBELIEVABLE!!"</B>4> <B>end</B>5> <B>else</B>6> <B> print "TED WILLIAMS IS GETTING LONELY!"</B>7> <B>end</B></FONT></PRE><P>We recommend experimenting with your SQL implementation's <TT>IF</TT> statement.Think of several conditions you would be interested in checking in the <TT>BASEBALL</TT>(or any other) database. Run some queries making use of the <TT>IF</TT> statementto familiarize yourself with its use.<H3><FONT COLOR="#000077">The WHILE Loop</FONT></H3><P>Another popular programming construct that Transact-SQL supports is the <TT>WHILE</TT>loop. This command has the following syntax:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">WHILE logical_expression statement(s)</FONT></PRE><H4><FONT COLOR="#000077">Example 19.7</FONT></H4><P>The <TT>WHILE</TT> loop continues to loop through its statements until the logicalexpression it is checking returns a <TT>FALSE</TT>. This example uses a simple <TT>WHILE</TT>loop to increment a local variable (named <TT>COUNT</TT>).</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @COUNT int</B>2> <B>select @COUNT = 1</B>3> <B>while (@COUNT < 10)</B>4> <B>begin</B>5> <B>select @COUNT = @COUNT + 1</B>6> <B> print "LOOP AGAIN!"</B>7><B> end</B>8> <B>print "LOOP FINISHED!"</B></FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Example 19.7 implements a simple <TT>FOR</TT> loop. Other implementations of SQL, such as Oracle's PL/SQL, actually provide a <TT>FOR</TT> loop statement. Check your documentation to determine whether the system you are using supports this useful command. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">The BREAK Command</FONT></H3><P>You can issue the <TT>BREAK</TT> command within a <TT>WHILE</TT> loop to forcean immediate exit from the loop. The <TT>BREAK</TT> command is often used along withan <TT>IF</TT> test to check some condition. If the condition check succeeds, youcan use the <TT>BREAK</TT> command to exit from the <TT>WHILE</TT> loop. Commandsimmediately following the <TT>END</TT> command are then executed. Example 19.8 illustratesa simple use of the <TT>BREAK</TT> command. It checks for some arbitrary number (say<TT>@COUNT = 8</TT>). When this condition is met, it breaks out of the <TT>WHILE</TT>loop.<H4><FONT COLOR="#000077">Example 19.8</FONT></H4><P>Notice the placement of the <TT>BREAK</TT> statement after the evaluation of thefirst condition in the <TT>IF</TT>.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @COUNT int</B>2> <B>select @COUNT = 1</B>3> <B>while (@COUNT < 10)</B>4> <B>begin</B>5> <B>select @COUNT = @COUNT + 1</B>6> <B> if (@COUNT = 8)</B>7> <B> begin</B>8> <B> break</B>9> <B> end</B>10> <B> else</B>11> <B> begin</B>12> <B>print "LOOP AGAIN!"</B>13> <B>end</B>14> <B>end</B>15><B> print "LOOP FINISHED!"</B></FONT></PRE><H5>ANALYSIS:</H5><P>The <TT>BREAK</TT> command caused the loop to be exited when the <TT>@COUNT</TT>variable equaled <TT>8</TT>.<H3><FONT COLOR="#000077">The CONTINUE Command</FONT></H3><P>The <TT>CONTINUE</TT> command is also a special command that can be executed fromwithin a <TT>WHILE</TT> loop. The <TT>CONTINUE</TT> command forces the loop to immediatelyjump back to the beginning, rather than executing the remainder of the loop and thenjumping back to the beginning. Like the <TT>BREAK</TT> command, the <TT>CONTINUE</TT>command is often used with an <TT>IF</TT> statement to check for some condition andthen force an action, as shown in Example 19.9.<H4><FONT COLOR="#000077">Example 19.9</FONT></H4><P>Notice the placement of the <TT>CONTINUE</TT> statement after the evaluation ofthe first condition in the <TT>IF</TT>.</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">1> <B>declare @COUNT int</B>2> <B>select @COUNT = 1</B>3> <B>while (@COUNT < 10)</B>4> <B>begin</B>5> <B> select @COUNT = @COUNT + 1</B>6> <B> if (@COUNT = 8)</B>7> <B> begin</B>8> <B> continue</B>9> <B> end</B>10> <B>else</B>11> <B>begin</B>12> <B> print "LOOP AGAIN!"</B>13> <B> end</B>14> <B>end</B>15> <B>print "LOOP FINISHED!"</B></FONT></PRE><H5>ANALYSIS:</H5><P>Example 19.9 is identical to Example 19.8 except that the <TT>CONTINUE</TT> commandreplaces the <TT>BREAK</TT> command. Now instead of exiting the loop when <TT>@COUNT= 8</TT>, it simply jumps back to the top of the <TT>WHILE</TT> statement and continues.<H3><FONT COLOR="#000077">Using the WHILE Loop to Scroll Through a Table</FONT></H3><P>SQL Server and many other database systems have a special type of object--thecursor--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?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -