ch19.htm
来自「21精通SQL」· HTM 代码 · 共 1,211 行 · 第 1/4 页
HTM
1,211 行
operations.
<H4><FONT COLOR="#000077">Using Variables to Store Data</FONT></H4>
<P>Variables are available only within the current statement block. To execute a
block of statements using the Transact-SQL language, the <TT>go</TT> 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.</P>
<P>You cannot initialize variables simply by using the <TT>=</TT> sign. Try the following
statement 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 in
line 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 using
the 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 statements
before executing the <TT>go</TT> statement, the <TT>@name</TT> variable could have
been 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 can
be 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> database
who 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 enables
you 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 provides
a number of useful functions that can convert different data types to strings (and
vice 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 other
clause) 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 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 <TT>IF...THEN</TT> statements and loops. This section contains
some 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 the
beginning 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 groups
of 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 useful
for checking the value of data retrieved from the database. The Transact-SQL syntax
for the <TT>IF...ELSE</TT> statement looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">if (condition)
begin
(statement block)
end
else if (condition)
begin
statement block)
end
.
.
.
else
begin
(statement block)
end
</FONT></PRE>
<P>Note that for each condition that might be true, a new <TT>BEGIN/END</TT> 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.
<H4><FONT COLOR="#000077">Example 19.3</FONT></H4>
<P>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!</P>
<P>Example 19.3 uses the <TT>IF...ELSE</TT> statement to evaluate conditions within
the 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 the
simple <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 function
returns <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. Example
19.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 average
among 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> statement
to define a condition. This time, however, we are testing the condition with the
greater 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> statement
to 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 logical
expression 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 force
an immediate exit from the loop. The <TT>BREAK</TT> command is often used along with
an <TT>IF</TT> test to check some condition. If the condition check succeeds, you
can use the <TT>BREAK</TT> command to exit from the <TT>WHILE</TT> loop. Commands
immediately following the <TT>END</TT> command are then executed. Example 19.8 illustrates
a 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 the
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> 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 from
within a <TT>WHILE</TT> loop. The <TT>CONTINUE</TT> 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 <TT>BREAK</TT> command, the <TT>CONTINUE</TT>
command is often used with an <TT>IF</TT> statement to check for some condition and
then 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 of
the 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> command
replaces 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--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
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?