ch19.htm
来自「21精通SQL」· HTM 代码 · 共 1,211 行 · 第 1/4 页
HTM
1,211 行
10) do not support the use of scrollable cursors. Example 19.10 gives you an idea
of how to use a <TT>WHILE</TT> loop to implement a rough cursor-type functionality
when that functionality is not automatically supplied.
<H4><FONT COLOR="#000077">Example 19.10</FONT></H4>
<P>You can use the <TT>WHILE</TT> loop to scroll through tables one record at a time.
Transact-SQL stores the <TT>rowcount</TT> 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 <TT>rowcount
</TT>to <TT>1</TT> (its default is <TT>0</TT>, which means unlimited), SQL Server
returns only one record at a time from a <TT>SELECT</TT> 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.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>set rowcount 1</B>
2><B> declare @PLAYER char(30)</B>
3> <B>create table temp_BATTERS (</B>
4> <B>NAME char(30),</B>
5> <B>TEAM int,</B>
6> <B>AVERAGE float,</B>
7> <B>HOMERUNS int,</B>
8> <B>RBIS int)</B>
9><B> insert temp_BATTERS</B>
10> <B>select * from BATTERS</B>
11> <B>while exists (select * from temp_BATTERS)</B>
12> <B>begin</B>
13> <B>select @PLAYER = NAME from temp_BATTERS</B>
14> <B> print @PLAYER</B>
15> <B> delete from temp_BATTERS where NAME = @PLAYER</B>
16> <B>end</B>
17> <B>print "LOOP IS DONE!"</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Note that by setting the <TT>rowcount</TT> variable, you are simply modifying
the number of rows returned from a <TT>SELECT</TT>. If the <TT>WHERE</TT> clause
of the <TT>DELETE</TT> command returned five rows, five rows would be deleted! Also
note that the <TT>rowcount</TT> variable can be reset repeatedly. Therefore, from
within the loop, you can query the database for some additional information by simply
resetting <TT>rowcount</TT> to <TT>1</TT> before continuing with the loop.
<H2><FONT COLOR="#000077">Transact-SQL Wildcard Operators</FONT></H2>
<P>The concept of using wildcard conditions in SQL was introduced on Day 3, "Expressions,
Conditions, and Operators." The <TT>LIKE</TT> 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.
<UL>
<LI>The underscore character (<TT>_</TT>)represents any one individual character.
For example, <TT>_MITH</TT> tells the query to look for a five-character string ending
with <TT>MITH</TT>.
<P>
<LI>The percent sign (<TT>%</TT>) represents any one or multiple characters. For
example, <TT>WILL%</TT> returns the value <TT>WILLIAMS</TT> if it exists. <TT>WILL%</TT>
returns the value <TT>WILL</TT>.
<P>
<LI>Brackets (<TT>[ ]</TT>) allow a query to search for characters that are contained
within the brackets. For example, <TT>[ABC]</TT> tells the query to search for strings
containing the letters <TT>A</TT>, <TT>B</TT>, or <TT>C</TT>.
<P>
<LI>The <TT>^</TT> character used within the brackets tells a query to look for any
characters that are not listed within the brackets. For example, <TT>[^ABC]</TT>
tells the query to search for strings that do not contain the letters <TT>A</TT>,
<TT>B</TT>, or <TT>C</TT>.
</UL>
<H2><FONT COLOR="#000077">Creating Summarized Reports Using COMPUTE</FONT></H2>
<P>Transact-SQL also has a mechanism for creating summarized database reports. The
command, <TT>COMPUTE</TT>, has very similar syntax to its counterpart in SQL*Plus.
(See Day 20, "SQL*Plus.")</P>
<P>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:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>select name, homeruns
from batters
compute sum(homeruns)</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In the previous example, <TT>COMPUTE</TT> alone performs computations on the report
as a whole, whereas <TT>COMPUTE BY</TT> performs computations on specified groups
and the entire report, as the following example shows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">COMPUTE FUNCTION(expression) [BY expression]
where the FUNCTION might include SUM, MAX, MIN, etc. and
EXPRESSION is usually a column name or alias.
</FONT></PRE>
<H2><FONT COLOR="#000077">Date Conversions</FONT></H2>
<P>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 <TT>CONVERT</TT> command to manipulate the way a date
is displayed.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CONVERT (datatype [(length)], expression, format)
</FONT></PRE>
<P>The following date formats are available with SQL Server when using the <TT>CONVERT</TT>
function: <BR>
</P>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Format code</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Format picture</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>100</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>mon dd yyyy hh:miAM/PM</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>101</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>mm/dd/yy</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>102</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>yy.mm.dd</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>103</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd/mm/yy</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>104</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd.mm.yy</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>105</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd-mm-yy</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>106</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>dd mon yy</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>107</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>mon dd, yy</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>108</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>hh:mi:ss</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>109</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>mon dd, yyyy hh:mi:ss:mmmAM/PM</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>110</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>mm-dd-yy</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>111</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>yy/mm/dd</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>112</TT></TD>
<TD VALIGN="TOP"><TT>yymmdd</TT></TD>
</TR>
</TABLE>
</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">select "PayDate" = convert(char(15), paydate, 107)
from payment_table
where customer_id = 012845</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF">PayDate
---------------
May 1, 1997
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding example uses the format code <TT>107</TT> with the <TT>CONVERT</TT>
function. According to the date format table, code <TT>107</TT> will display the
date in the format <TT>mon dd, yy</TT>.
<H2><FONT COLOR="#000077">SQL Server Diagnostic Tools--SET Commands</FONT></H2>
<P>Transact-SQL provides a list of <TT>SET</TT> commands that enable you to turn
on various options that help you analyze Transact-SQL statements. Here are some of
the popular <TT>SET</TT> commands:
<UL>
<LI><TT>SET STATISTICS IO ON</TT> tells the server to return the number of logical
and physical page requests.
<P>
<LI><TT>SET STATISTICS TIME ON</TT> tells the server to display the execution time
of an SQL statement.
<P>
<LI><TT>SET SHOWPLAN ON</TT> tells the server to show the execution plan for the
designated query.
<P>
<LI><TT>SET NOEXEC ON</TT> tells the server to parse the designated query, but not
to execute it.
<P>
<LI><TT>SET PARSONLY ON</TT> tells the server to check for syntax for the designated
query, but not to execute it.
</UL>
<P>Transact-SQL also has the following commands that help to control what is displayed
as part of the output from your queries:
<UL>
<LI><TT>SET ROWCOUNT </TT>n tells the server to display only the first n records
retrieved from a query.
<P>
<LI><TT>SET NOCOUNT ON</TT> tells the server not to report the number of rows returned
by a query.
</UL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you are concerned with tuning
your SQL statements, refer to Day 15, "Streamlining SQL Statements for Improved
Performance."
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>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.</P>
<P>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 <TT>IF</TT> statement, the <TT>WHILE</TT> loop,
and the capability to declare and use local and global variables.</P>
<P>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.
<H2><FONT COLOR="#000077">Q&A</FONT></H2>
<DL>
<DD><B>Q Does SQL provide a <TT>FOR</TT> loop?</B>
<P><B>A</B> Programming constructs such as the <TT>FOR</TT> loop, the <TT>WHILE</TT>
loop, and the <TT>CASE</TT> statement are extensions to ANSI SQL. Therefore, the
use of these items varies widely among database systems. For instance, Oracle provides
the <TT>FOR</TT> loop, whereas Transact-SQL (SQL Server) does not. Of course, a <TT>WHILE</TT>
loop can increment a variable within the loop, which can simulate the <TT>FOR</TT>
loop.</P>
<P><B>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
</B>PRINT<B> statement to issue messages to the user?</B></P>
<P><B>A</B> SQL is entirely platform independent. Therefore, issuing the <TT>PRINT</TT>
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 <TT>PRINT</TT> command
is most useful for debugging because a <TT>PRINT</TT> statement executed within a
stored procedure will not be output to the screen anyway.)
</DL>
<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>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."
<H3><FONT COLOR="#000077">Quiz</FONT></H3>
<DL>
<DD><B>1.</B> True or False: The use of the word SQL in Oracle's PL/SQL and Microsoft/Sybase's
Transact-SQL implies that these products are fully compliant with the ANSI standard.
<P><B>2. </B>True or False: Static SQL is less flexible than Dynamic SQL, although
the performance of static SQL can be better.
</DL>
<H3><FONT COLOR="#000077">Exercises</FONT></H3>
<DL>
<DD><B>1. </B>If you are not using Sybase/Microsoft SQL Server, compare your product's
extensions to ANSI SQL to the extensions mentioned today.
<P><B>2. </B>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.
</DL>
<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="ch18.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch18.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch20.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch20.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A><BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>
<P>© <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?