구글와이드(336x280)_상단 2개


(영문) Cursors with SQL 2000 DataBase(MS SQL)

October 1, 2004
Cursors with SQL 2000 Part 1

By Don Schlichting
This series of articles will examine the purposes, uses, and optimization of cursors in SQL 2000.

Introduction

SQL languages are designed so groups of records, or sets, can be manipulated easily and quickly. The speed at which groups of data can be altered, updated and deleted, demonstrates why working with sets is the preferred method. A traditional programmatic database connection, using ADO or ODBC, to manipulate large groups of records one at a time, will be magnitudes slower than executing a single SQL statement that will alter all the records as one giant set. But what happens when data must be organized, or adjusted on a record-by-record basis? Sometimes multiple processing steps are required on each individual record before moving on to the next. In these cases, the result set cannot be created by traditional SQL set statements. Enter Cursors. Cursors are special programming constructs that allow data to be manipulated on a row-by-row basis, similar to other structured programming languages. They are declared like a variable, and then move one record at a time using a loop for control. This article will examine several aspects of Cursor creation, use, and behavior.

Example One

In this first example, the task will be to alter the pubs titles database. Book prices need to be adjusted in such a way that any book with a price under $20 be raised by 10%. While books currently $20 or more, are raised 5%. If the following two simple set statements were used, incorrect results would occur.
UPDATE titles
SET price = price * (price * .1)
WHERE price < 20


UPDATE titles
SET price = price * (price * .05)
WHERE price >= 20
With these statements, any book with a price of $19.95 will be raised 10%, then on the next statement, because the new price is over $20, it will be raised an additional 5%. One solution would be to use a Cursor. (In the real world, a cursor would not be the best solution for this task, but it clearly demonstrates the topic. The Optimization section contains a preferred method for solving this example.) We will work through this example in several steps. To begin with, we will create a simple select to demonstrate the Cursor commands. Afterwards, we will continue to expand on it until the actual updates are completed. From Query Analyzer, enter the following TSQL:
USE pubs
GO

DECLARE get_price CURSOR FOR
 SELECT price FROM titles

OPEN get_price

FETCH NEXT FROM get_price

WHILE @@FETCH_STATUS = 0
 FETCH NEXT FROM get_price

CLOSE get_price
DEALLOCATE get_price
Executing this will produce a list of prices like the following:
Compare the above result with the result of just running the SELECT by itself, when it is not nested inside a Cursor.
SELECT price FROM titles
The point worth noting is that in the first example, using the Cursor, multiple result sets are being returned. There are eighteen records in the titles table, so the Cursor will create eighteen independent result sets, each containing only one price. Running the select alone, not inside the Cursor, returns one result set with eighteen records. This demonstrates that the Cursor is processing the table one record at a time, rather than working the table as one set.

DECLARE CURSOR FOR

The DECLARE Cursor statement is used for new cursor creation. Like other creating statements, we give the cursor a name, and then define how it will be used.
DECLARE get_price CURSOR FOR  SELECT price FROM titles
The name of the Cursor, "get_price" in this example, can be any typical SQL identifier. In the actual select statement, only the field needing updating will be requested. The select can include all the usual TSQL commands such as WHERE, GROUP BY, and ORDER BY.

OPEN

OPEN get_price
The OPEN key word allocates memory for the new Cursor and does any other setup that might be required. While at the moment, it does not look like the OPEN statement is doing much, later, when Scrollable and Insensitive cursors are discussed, it is the OPEN statement that accomplishes all the housekeeping tasks required for them.

FETCH

FETCH NEXT FROM get_price
The purpose of FETCH is to actually retrieve a single record and load it into the cursor. In later examples, when Scrollable cursors are covered, we will have the option of replacing the NEXT keyword with direction pointers like PRIOR, FIRST, and LAST.

@@FETCH_STATUS

Cursors are usually controlled with a WHILE loop. The loop allows each record to be fetched one at a time. @@FETCH_STATUS is our loop control. There are three possible status values. 0 if the fetch was successful, -1 for an error, -2 for no records returned. So our loop will continue until the next fetch fails. The WHILE works like a traditional structured programming loop, fetching, checking the last status, repeat.
Cursors can also be nested, having one cursor call another. Alternatively, one cursor calls a stored procedure that opens another cursor. If this is the case, refer to Microsoft Books On Line keyword "@@FETCH_STATUS", because this function is global to the connection, not local to the loop. Care must be taken to insure you are checking the correct fetch status.

CLOSE

When records are fetched into a cursor, a cursor lock is issued. The lock is not released until a CLOSE is issued. After a CLOSE, the Cursor can be reopened. All the structure of the cursor is still in place.

DEALLOCATE

The last step in removing a Cursor is to release any memory its using, and destroy the variable and identifier name association. DEALLOCATE does all of this in one command. After this step, all of the cursor's structure is deleted.

Conclusion

With Cursors, TSQL statements can be created that mimic traditional structured programming, working on one record at a time, rather than working with sets. There will be a performance price to pay for this, but sometimes it may be required. In future articles, this cursor will be greatly expanded to include features like Scrolling (The ability to move forwards and backwards in a cursor), Insensitivity (using a copy of the select data, rather than the real data), and the use of Locking options, as well as discussions on cursor optimization.
November 5, 2004
Cursors with SQL 2000 Part 2

By Don Schlichting

Introduction

This is the second article in the Cursors with SQL 2000 series. In the preceding article, cursor use and basic syntax were covered. A select cursor was created demonstrating the keywords DECLARE, OPEN, FETCH, @@FETCH_STATUS, and DEALLOCATE. These keywords guide basic cursor execution by assigning a TSQL statement to the cursor, moving records into the cursor, retrieving a specific record from the cursor, working with retrieved record, and then closing the cursor. The sample cursor demonstrated that SQL could act on one record at a time, as opposed to its usual method of acting on sets of data. In this article, the sample cursor will be expanded upon to include data modification and record positioning.
Following is the sample cursor from the first article. This example demonstrates the minimum statements required to create and use a cursor.
USE pubs
GO

DECLARE get_price CURSOR FOR
        SELECT price FROM titles

OPEN get_price

FETCH NEXT FROM get_price

WHILE @@FETCH_STATUS = 0
        FETCH NEXT FROM get_price

CLOSE get_price
DEALLOCATE get_price

SELECT INTO

The purpose of the this cursor was to lay the ground work for a statement that will change books priced under $20 to be raised by 10%, while books currently $20 or more, will be raised 5%. The above cursor will now be modified so prices returned from titles under twenty dollars will print "Under 20", while titles twenty dollars or more will print the price. Modify the cursors to:
DECLARE @price money
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
     SELECT price FROM titles

OPEN @get_price
        
FETCH NEXT FROM @get_price INTO @price

WHILE (@@FETCH_STATUS = 0)      
BEGIN
     IF @Price < 20 
        SELECT 'Under 20'
     ELSE
        SELECT @Price

     FETCH NEXT FROM @get_price INTO @price
END

CLOSE @get_price
DEALLOCATE @get_price
One of the first differences to notice is the use of variables for the cursor and the price. Now that the cursor is a variable, it is SET to a SQL statement. The SQL statement itself has not changed. The cursor is opened like before, but the FETCH will move the field price into the variable @price. If more than one column were needed, say price and title, we would declare two variables, then SELECT INTO both as follows:
DECLARE @Price money, @Title varchar(50), @get_price CURSOR

SET @get_price = CURSOR FOR
     SELECT Price, Title FROM titles

OPEN @get_price

FETCH NEXT FROM @get_price INTO @Price, @Title
The WHILE loop is still controlled with @@FETCH_STATUS, the use of variables does not alter this. Once fetched, @price can be used like a normal variable as demonstrated by "IF @Price < 20". Because the WHILE loop has more than one action line, it uses BEGIN and END tags. Once we are done working with this record, the next is fetched. At end of file, the loop exits and the cursor is closed and destroyed.
Running the cursor will produce the expected list of "Under," price, and nulls, depending on the value found in the titles table.

WHERE CURRENT OF

With the cursor running as expected, it can be easily modified to update the values rather than display them. Modify the cursor to include the following update statements:
DECLARE @price money
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
     SELECT price FROM titles

OPEN @get_price
 
FETCH NEXT FROM @get_price INTO @price

SELECT price FROM titles

WHILE (@@FETCH_STATUS = 0) 
BEGIN
 IF @Price < 20 
  UPDATE titles SET price = 
   (@price + (@price * .1)) 
  WHERE CURRENT OF @get_price
 ELSE
  UPDATE titles SET price = 
   (@price + (@price * .05)) 
  WHERE CURRENT OF @get_price

 FETCH NEXT FROM @get_price INTO @price
END

SELECT price FROM titles

CLOSE @get_price
DEALLOCATE @get_price
The selects show the expected results. Prices over twenty dollars have been raised by five percent while prices fewer than twenty dollars have been raised ten percent.
In the TSQL, the two new lines:
SELECT price FROM titles
were added only to show our changes. They are not needed for the update. The new key phrase "WHERE CURRENT OF" is now being used. The phrase tells SQL to work with the record at the current cursor position. Behind the scenes, SQL is using a dynamic cursor with optimistic locking on our cursor.

Cursor Types

There are three types of cursors, DYNAMIC, STATIC, and KEYSET. Dynamic cursors will show changes made on the base table as you scroll through the cursor. Static cursors copy the base table, "titles" in our case, to the tempdb. The cursor then reads from the tempdb, so any changes happening on the base table will not be reflected in the cursors scrolling. Keysets are in between Dynamic and Static cursors. A keyset will copy the base table's selected records keys into the tempdb, so the cursor will select the rows from the tempdb, but the data from the base table. So change to the base table will be seen, but new record inserts will not be.

Lock Types

There are also three types of locks for cursors, READ ONLY, SCROLL LOCKS, and OPTIMISTIC. A Read Only lock prevents any updates. It also cannot be used with the WHERE CURRENT OF statement. A scroll lock issues a hard lock on the underlining records. Any updates made by the cursor are guaranteed to succeed. Optimistic locking will look at the underlying record for any changes since the cursor fetched it, if the base tables record has changed, your update will fail. If not, the cursors update will succeed.

Optimization

The first rule of cursor optimization is not to use them if possible. In our above example, two update statements could have been used rather than a cursor. One statement would select books priced over twenty dollars, while the other selects books priced fewer than twenty dollars. This will usually out perform a cursor. If you are not going to be doing DML, use a read only or fast forward cursor rather than a dynamic scrollable cursor. Also, close and destroy the cursor when finished with it.

Conclusion

Sometimes the usual TSQL method of working with sets of records does not fit the application. If so, a cursor can be used to move SQL on a record-by-record basis. Cursors support a wide and useful array of commands to control everything from locks to visibility. Used in their correct format, cursors are reliable and invaluable.

null



바보들의 영문법 카페(클릭!!)

오늘의 메모....

시사평론-정론직필 다음 카페
http://cafe.daum.net/sisa-1

바보들의 영문법 다음 카페
http://cafe.daum.net/babo-edu/

티스토리 내 블로그
http://earthly.tistory.com/

내 블로그에 있는 모든 글들과 자료에 대한 펌과 링크는 무제한 허용됩니다.
(단, 내 블로그에 덧글쓰기가 차단된 자들에게는 펌, 트랙백, 핑백 등이 일체 허용되지 않음.)

그리고 내 블로그 최근글 목록을 제목별로 보시려면....
바로 아래에 있는 이전글 목록의 최근달을 클릭하시면 됩니다.
그러면 제목을 보고 편하게 글을 골라 보실 수 있습니다.

그리고 내 블로그내 글을 검색하시려면 아래 검색버튼을 이용하시면 됩니다.


가가챗창

flag_Visitors

free counters