📄 ch13.htm
字号:
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">7</TD>
<TD ALIGN="LEFT">5</TD>
<TD ALIGN="LEFT">Come On, Come On</TD>
<TD ALIGN="LEFT">1992</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">7</TD>
<TD ALIGN="LEFT">3</TD>
<TD ALIGN="LEFT">Stones in the Road</TD>
<TD ALIGN="LEFT">1994</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">8</TD>
<TD ALIGN="LEFT">5</TD>
<TD ALIGN="LEFT">Second Piano Concerto</TD>
<TD ALIGN="LEFT">1985</TD>
</TR>
</TABLE>
<H4><FONT COLOR="#000077">Example 13.1</FONT></H4>
<P>You can create a temporary table in the <TT>tempdb</TT> database. After inserting
a dummy record into this table, log out. After logging back into SQL Server, try
to select the dummy record out of the temporary table. Note the results:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>create table #albums (</B>
2> <B>artist char(30),</B>
3> <B>album_name char(50),</B>
4><B> media_type int)</B>
5> <B>go</B>
1> <B>insert #albums values ("The Replacements", "Pleased To Meet Me", 1)</B>
2> <B>go</B>
</FONT></PRE>
<P>Now log out of the SQL Server connection using the <TT>EXIT</TT> (or <TT>QUIT</TT>)
command. After logging back in and switching to the database you last used, try the
following command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> select * from #albums
2> go
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This table does not exist in the current database.
<H4><FONT COLOR="#000077">Example 13.2</FONT></H4>
<P>Now create the table with syntax 2:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>create table tempdb..albums (</B>
2> <B>artist char(30)</B>,
3> <B>album_name char(50),</B>
4> <B>media_type int)</B>
5><B> go</B>
1> <B>insert #albums values ("The Replacements", "Pleased To Meet Me", 1)</B>
2> <B>go</B>
</FONT></PRE>
<P>After logging out and logging back in, switch to the database you were using when
<TT>create table tempdb..albums()</TT> was issued; then issue the following command:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>select * from #albums</B>
2><B> go</B>
</FONT></PRE>
<P>This time, you get the following results:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">artist album_name media_type
_______________________________________________________________________________________
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">The Replacements Pleased To Meet Me 1
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 13.3</FONT></H4>
<P>This example shows a common usage of temporary tables: to store the results of
complex queries for use in later queries.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>create table #temp_info (</B>
2> <B>name char(30),</B>
3> <B>homebase char(40),</B>
4> <B>style char(20),</B>
5> <B>artist_id int)</B>
6> <B>insert #temp_info</B>
7> <B>select * from ARTISTS where homebase = "Nashville"</B>
8> <B>select RECORDINGS.* from RECORDINGS, ARTISTS</B>
9> <B>where RECORDINGS.artist_id = #temp_info.artist_id</B>
10><B> go</B>
</FONT></PRE>
<P>The preceding batch of commands selects out the recording information for all
the artists whose home base is Nashville.</P>
<P>The following command is another way to write the set of SQL statements used in
Example 13.3:</P>
<PRE><FONT COLOR="#0066FF">1><B> select ARTISTS.* from ARTISTS, RECORDINGS where ARTISTS.homebase = "Nashville"</B>
2> <B>go</B>
</FONT></PRE>
<H2><FONT COLOR="#000077">Cursors</FONT></H2>
<P>A database cursor is similar to the cursor on a word processor screen. As you
press the Down Arrow key, the cursor scrolls down through the text one line at a
time. Pressing the Up Arrow key scrolls your cursor up one line at a time. Hitting
other keys such as Page Up and Page Down results in a leap of several lines in either
direction. Database cursors operate in the same way.</P>
<P>Database cursors enable you to select a group of data, scroll through the group
of records (often called a recordset), and examine each individual line of data as
the cursor points to it. You can use a combination of local variables and a cursor
to individually examine each record and perform any external operation needed before
moving on to the next record.</P>
<P>One other common use of cursors is to save a query's results for later use. A
cursor's result set is created from the result set of a <TT>SELECT</TT> query. If
your application or procedure requires the repeated use of a set of records, it is
faster to create a cursor once and reuse it several times than to repeatedly query
the database. (And you have the added advantage of being able to scroll through the
query's result set with a cursor.)</P>
<P>Follow these steps to create, use, and close a database cursor:
<DL>
<DD><B>1.</B> Create the cursor.
<P><B>2. </B>Open the cursor for use within the procedure or application.</P>
<P><B>3.</B> Fetch a record's data one row at a time until you have reached the end
of the cursor's records.</P>
<P><B>4.</B> Close the cursor when you are finished with it.</P>
<P><B>5. </B>Deallocate the cursor to completely discard it.
</DL>
<H3><FONT COLOR="#000077">Creating a Cursor</FONT></H3>
<P>To create a cursor using Transact-SQL, issue the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">declare cursor_name cursor
for select_statement
[for {read only | update [of column_name_list]}]
</FONT></PRE>
<P>The Oracle7 SQL syntax used to create a cursor looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DECLARE cursor_name CURSOR
FOR {SELECT command | statement_name | block_name}
</FONT></PRE>
<P>By executing the <TT>DECLARE cursor_name CURSOR</TT> statement, you have defined
the cursor result set that will be used for all your cursor operations. A cursor
has two important parts: the cursor result set and the cursor position.</P>
<P>The following statement creates a cursor based on the <TT>ARTISTS</TT> table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>create Artists_Cursor cursor</B>
2> <B>for select * from ARTISTS</B>
3> <B>go</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You now have a simple cursor object named <TT>Artists_Cursor</TT> that contains
all the records in the <TT>ARTISTS</TT> table. But first you must open the cursor.
<H3><FONT COLOR="#000077">Opening a Cursor</FONT></H3>
<P>The simple command to open a cursor for use is</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">open cursor_name
</FONT></PRE>
<P>Executing the following statement opens <TT>Artists_Cursor</TT> for use:</P>
<PRE><FONT COLOR="#0066FF">1> <B>open Artists_Cursor</B>
2><B> go
</B></FONT></PRE>
<P>Now you can use the cursor to scroll through the result set.
<H3><FONT COLOR="#000077">Scrolling a Cursor</FONT></H3>
<P>To scroll through the cursor's result set, Transact-SQL provides the following
<TT>FETCH</TT> command.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">fetch cursor_name [into fetch_target_list]
</FONT></PRE>
<P>Oracle SQL provides the following syntax:</P>
<PRE><FONT COLOR="#0066FF">FETCH cursor_name {INTO : host_variable
[[INDICATOR] : indicator_variable]
[, : host_variable
[[INDICATOR] : indicator_variable] ]...
| USING DESCRIPTOR descriptor }
</FONT></PRE>
<P>Each time the <TT>FETCH</TT> command is executed, the cursor pointer advances
through the result set one row at a time. If desired, data from each row can be fetched
into the<TT> fetch_target_list</TT> variables.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Transact-SQL enables the programmer
to advance more than one row at a time by using the following command: <TT>set cursor
rows </TT>number<TT> for cursor_name</TT>. This command cannot be used with the <TT>INTO</TT>
clause, however. It is useful only to jump forward a known number of rows instead
of repeatedly executing the <TT>FETCH</TT> statement.
<HR>
</BLOCKQUOTE>
<P>The following statements fetch the data from the<TT> Artists_Cursor</TT> result
set and return the data to the program variables:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>declare @name char(30)</B>
2> <B>declare @homebase char(40)</B>
3> <B>declare @style char(20)</B>
4> <B>declare @artist_id int</B>
5> <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>
6> <B>print @name</B>
7> <B>print @homebase</B>
8> <B>print @style</B>
9> <B>print char(@artist_id)</B>
10> <B>go</B>
</FONT></PRE>
<P>You can use the <TT>WHILE</TT> loop (see Day 12, "Database Security")
to loop through the entire result set. But how do you know when you have reached
the end of the records?
<H3><FONT COLOR="#000077">Testing a Cursor's Status</FONT></H3>
<P>Transact-SQL enables you to check the status of the cursor at any time through
the maintenance of two global variables: <TT>@@sqlstatus</TT> and <TT>@@rowcount</TT>.</P>
<P>The <TT>@@sqlstatus</TT> variable returns status information concerning the last
executed <TT>FETCH</TT> statement. (The Transact-SQL documentation states that no
command other than the <TT>FETCH</TT> statement can modify the <TT>@@sqlstatus</TT>
variable.) This variable contains one of three values. The following table appears
in the Transact-SQL reference manuals: <BR>
<BR>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP">Status</TD>
<TD ALIGN="LEFT" VALIGN="TOP">Meaning</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP"><TT>0</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP">Successful completion of the <TT>FETCH</TT> statement.</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>1</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP">The <TT>FETCH</TT> statement resulted in an error.</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>2</TT></TD>
<TD VALIGN="TOP">There is no more data in the result set.</TD>
</TR>
</TABLE>
<DL>
<DD>
</DL>
<P>The <TT>@@rowcount</TT> variable contains the number of rows returned from the
cursor's result set up to the previous fetch. You can use this number to determine
the number of records in a cursor's result set.</P>
<P>The following code extends the statements executed during the discussion of the
<TT>FETCH</TT> statement. You now use the <TT>WHILE</TT> loop with the <TT>@@sqlstatus</TT>
variable to scroll the cursor:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1> <B>declare @name char(30)</B>
2> <B>declare @homebase char(40)
</B>3><B> declare @style char(20)</B>
4> <B>declare @artist_id int</B>
5> <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>
6> <B>while (@@sqlstatus = 0)</B>
7> <B>begin</B>
8> <B> print @name</B>
9> <B>print @homebase</B>
10> <B>print @style</B>
11> <B> print char(@artist_id)</B>
12> <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>
13> <B>end</B>
14> <B>go</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Now you have a fully functioning cursor! The only step left is to close the cursor.
<H3><FONT COLOR="#000077">Closing a Cursor</FONT></H3>
<P>Closing a cursor is a very simple matter. The statement to close a cursor is as
follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">close cursor_name
</FONT></PRE>
<P>This cursor still exists; however, it must be reopened. Closing a cursor essentially
closes out its result set, not its entire existence. When you are completely finished
with a cursor, the <TT>DEALLOCATE</TT> command frees the memory associated with a
cursor and frees the cursor name for reuse. The <TT>DEALLOCATE</TT> statement syntax
is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">deallocate cursor cursor_name
</FONT></PRE>
<P>Example 13.4 illustrates the complete process of creating a cursor, using it,
and then closing it, using Transact-SQL.
<H4><FONT COLOR="#000077">Example 13.4</FONT></H4>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1> <B>declare @name char(30)</B>
2> <B>declare @homebase char(40)</B>
3> <B>declare @style char(20)</B>
4> <B>declare @artist_id int</B>
5> <B>create Artists_Cursor cursor</B>
6> <B>for select * from ARTISTS</B>
7> <B>open Artists_Cursor</B>
8> <B>fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>
9> <B>while (@@sqlstatus = 0)</B>
10> <B>begin</B>
11> <B>print @name</B>
12> <B> print @homebase</B>
13> <B> print @style</B>
14> <B> print char(@artist_id)</B>
15> <B> fetch Artists_Cursor into @name, @homebase, @style, @artist_id</B>
16> <B>end</B>
17> <B>close Artists_Cursor</B>
18> <B>deallocate cursor Artists_Cursor</B>
19> <B>go</B>
</FONT></PRE>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The following is sample data only.
<HR>
</P>
</BLOCKQUOTE>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">Soul Asylum Minneapolis Rock 1
Maurice Ravel France Classical 2
Dave Matthews Band Charlottesville Rock 3
Vince Gill Nashville Country 4
Oingo Boingo Los Angeles Pop 5
Crowded House New Zealand Pop 6
Mary Chapin-Carpenter Nashville Country 7
Edward MacDowell U.S.A. Classical 8
</FONT></PRE>
<H3><FONT COLOR="#000077">The Scope of Cursors</FONT></H3>
<P>Unlike tables, indexes, and other objects such as triggers and stored procedures,
cursors do not exist as database objects after they are created. Instead, cursors
have a limited scope of use.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Remember, however, that memory
remains allocated for the cursor, even though its name may no longer exist. Before
going outside the cursor's scope, the cursor should always be closed and deallocated.
<HR>
</BLOCKQUOTE>
<P>A cursor can be created within three regions:
<UL>
<LI>In a session--A session begins when a user logs on. If the user logged on to
an SQL Server and then created a cursor, then cursor_name would exist until the user
logged off. The user would not be able to reuse cursor_name during the current session.
<P>
<LI>Stored procedure--A cursor created inside a stored procedure is good only during
the execution of the stored procedure. As soon as the stored procedure exits, <TT>cursor_name</TT>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -