SQLServerCh16
Gửi bởi: Khoa CNTT - HCEM 24 tháng 2 2021 lúc 9:37:58 | Được cập nhật: 24 tháng 3 lúc 12:03:03 Kiểu file: PPTX | Lượt xem: 194 | Lượt Download: 1 | File size: 0.900693 Mb
Nội dung tài liệu
Tải xuống
Link tài liệu:
Các tài liệu liên quan
Có thể bạn quan tâm
Thông tin tài liệu
Chapter 16
How to work
with cursors
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
Given the specifications for a database problem that can be solved
by using Transact-SQL cursors, write a script that solves it.
Knowledge
Describe the use of cursors.
Describe the two ways in which cursors can be implemented for a
SQL Server database.
Describe these types of cursors in terms of whether they are
scrollable and sensitive: dynamic, keyset-driven, static, and
forward-only.
Describe local scope and global scope as they apply to cursors.
Describe these concurrency options for Transact-SQL cursors:
optimistic, scroll-locks, and read-only.
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 2
Objectives (cont.)
Describe the way rows are updated or deleted when you use
cursors.
Given a script, stored procedure, user-defined function, or trigger
that uses a cursor, explain what each statement does.
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 3
How a cursor works
A result set with a cursor pointing to the first row
Cursor
The first row retrieved through the cursor
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 4
The two implementations of SQL Server cursors
Cursors implemented through standard database APIs
Cursors implemented through Transact-SQL
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 5
Two common uses for Transact-SQL cursors
For your own use in scripts and procedures
For use in procedures that provide database access to application
programs that can’t use standard database APIs
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 6
When to use Transact-SQL cursors
For administrative scripts and procedures that manipulate
database objects using dynamic SQL or system stored procedures
For scripts or procedures that need to do something different to
each row in a result set based on criteria that are more complex
than can be stated in a WHERE clause
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 7
The seven types of SQL Server cursors
Standard API
cursor type
Dynamic
Keyset-driven
Transact-SQL
cursor keyword
DYNAMIC
KEYSET
Static
Forward-only
(none)
STATIC
FORWARD_ONLY
FORWARD_ONLY
KEYSET
Yes
No
No
(none)
FORWARD_ONLY
No
Sensitive to
database
changes
Yes
Yes, for updates and
deletes, but not for
insertions
No
Yes
Yes, for updates and
deletes, but not for
insertions
No
(none)
FAST_FORWARD
No
No
Murach's SQL Server 2012, C16
Scrollable
Yes
Yes
© 2012, Mike Murach & Associates, Inc.
Slide 8
The SQL statements for cursor processing
DECLARE CURSOR
OPEN
FETCH
CLOSE
DEALLOCATE
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 9
A SQL script that declares and uses a cursor
DECLARE Vendors_Cursor CURSOR
STATIC
FOR
SELECT VendorID, VendorName
FROM Vendors
ORDER BY VendorName;
OPEN Vendors_Cursor;
FETCH NEXT FROM Vendors_Cursor;
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM Vendors_Cursor;
CLOSE Vendors_Cursor;
DEALLOCATE Vendors_Cursor;
The response from the system
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 10
The syntax of the DECLARE CURSOR statement
DECLARE cursor_name CURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[FAST_FORWARD|STATIC|KEYSET|DYNAMIC]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
FOR select_statement
[FOR UPDATE [OF column_name [, ...]]]
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 11
A cursor that uses the default options
DECLARE Invoices_Cursor CURSOR
FOR
SELECT * FROM Invoices;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 12
A dynamic local cursor
DECLARE VendorInvoice_Cursor CURSOR
LOCAL DYNAMIC
FOR
SELECT VendorName, InvoiceDate, InvoiceTotal
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 13
A cursor for updating specific columns
DECLARE VendorUpdate_Cursor CURSOR
GLOBAL SCROLL DYNAMIC SCROLL_LOCKS
FOR
SELECT * FROM Vendors
FOR UPDATE OF VendorName, VendorAddress1,
VendorAddress2, VendorCity,
VendorState, VendorZipCode,
VendorPhone;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 14
Terms
Cursor scrollability
Cursor sensitivity
Scope of a cursor
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 15
The syntax of the FETCH statement
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
[INTO @variable_name [, ...]]
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 16
A DECLARE CURSOR statement
DECLARE Vendor_Cursor CURSOR
STATIC
FOR SELECT VendorID, VendorName FROM Vendors;
FETCH statements for retrieving rows
from the cursor
-- Retrieves the next row
FETCH FROM Vendor_Cursor;
-- Retrieves the next row
FETCH NEXT FROM Vendor_Cursor;
-- Retrieves the previous row
FETCH PRIOR FROM Vendor_Cursor;
-- Retrieves the first row
FETCH FIRST FROM Vendor_Cursor;
-- Retrieves the last row
FETCH LAST FROM Vendor_Cursor;
-- Retrieves the third row
FETCH ABSOLUTE 3 FROM Vendor_Cursor;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 17
FETCH statements for retrieving rows
from the cursor (continued)
-- Retrieves the tenth row after the current row
FETCH RELATIVE 10 FROM Vendor_Cursor;
-- Retrieves the row two rows before the current row
FETCH RELATIVE –2 FROM Vendor_Cursor;
-- Retrieves the current row again
FETCH RELATIVE 0 FROM Vendor_Cursor;
-- Retrieves the next row and assigns the values
-- to two local variables
FETCH FROM Vendor_Cursor
INTO @VendorIDVar,@VendorNameVar;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 18
@@FETCH_STATUS system function values
Value
0
-1
-2
Description
The FETCH was successful.
The FETCH was unsuccessful because
it reached the end of the result set.
The FETCH was unsuccessful because
the row was deleted.
Warning
@@FETCH_STATUS is global to all of the cursors open on the
current connection. So if you’re using multiple cursors, don’t code
other statements between Fetch and @@FETCH_STATUS.
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 19
A WHILE loop that steps forward
through a result set
FETCH FIRST FROM Vendor_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
...
FETCH NEXT FROM Vendor_Cursor;
END;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 20
How to work
with cursors
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
Given the specifications for a database problem that can be solved
by using Transact-SQL cursors, write a script that solves it.
Knowledge
Describe the use of cursors.
Describe the two ways in which cursors can be implemented for a
SQL Server database.
Describe these types of cursors in terms of whether they are
scrollable and sensitive: dynamic, keyset-driven, static, and
forward-only.
Describe local scope and global scope as they apply to cursors.
Describe these concurrency options for Transact-SQL cursors:
optimistic, scroll-locks, and read-only.
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 2
Objectives (cont.)
Describe the way rows are updated or deleted when you use
cursors.
Given a script, stored procedure, user-defined function, or trigger
that uses a cursor, explain what each statement does.
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 3
How a cursor works
A result set with a cursor pointing to the first row
Cursor
The first row retrieved through the cursor
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 4
The two implementations of SQL Server cursors
Cursors implemented through standard database APIs
Cursors implemented through Transact-SQL
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 5
Two common uses for Transact-SQL cursors
For your own use in scripts and procedures
For use in procedures that provide database access to application
programs that can’t use standard database APIs
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 6
When to use Transact-SQL cursors
For administrative scripts and procedures that manipulate
database objects using dynamic SQL or system stored procedures
For scripts or procedures that need to do something different to
each row in a result set based on criteria that are more complex
than can be stated in a WHERE clause
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 7
The seven types of SQL Server cursors
Standard API
cursor type
Dynamic
Keyset-driven
Transact-SQL
cursor keyword
DYNAMIC
KEYSET
Static
Forward-only
(none)
STATIC
FORWARD_ONLY
FORWARD_ONLY
KEYSET
Yes
No
No
(none)
FORWARD_ONLY
No
Sensitive to
database
changes
Yes
Yes, for updates and
deletes, but not for
insertions
No
Yes
Yes, for updates and
deletes, but not for
insertions
No
(none)
FAST_FORWARD
No
No
Murach's SQL Server 2012, C16
Scrollable
Yes
Yes
© 2012, Mike Murach & Associates, Inc.
Slide 8
The SQL statements for cursor processing
DECLARE CURSOR
OPEN
FETCH
CLOSE
DEALLOCATE
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 9
A SQL script that declares and uses a cursor
DECLARE Vendors_Cursor CURSOR
STATIC
FOR
SELECT VendorID, VendorName
FROM Vendors
ORDER BY VendorName;
OPEN Vendors_Cursor;
FETCH NEXT FROM Vendors_Cursor;
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM Vendors_Cursor;
CLOSE Vendors_Cursor;
DEALLOCATE Vendors_Cursor;
The response from the system
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 10
The syntax of the DECLARE CURSOR statement
DECLARE cursor_name CURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[FAST_FORWARD|STATIC|KEYSET|DYNAMIC]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
FOR select_statement
[FOR UPDATE [OF column_name [, ...]]]
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 11
A cursor that uses the default options
DECLARE Invoices_Cursor CURSOR
FOR
SELECT * FROM Invoices;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 12
A dynamic local cursor
DECLARE VendorInvoice_Cursor CURSOR
LOCAL DYNAMIC
FOR
SELECT VendorName, InvoiceDate, InvoiceTotal
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 13
A cursor for updating specific columns
DECLARE VendorUpdate_Cursor CURSOR
GLOBAL SCROLL DYNAMIC SCROLL_LOCKS
FOR
SELECT * FROM Vendors
FOR UPDATE OF VendorName, VendorAddress1,
VendorAddress2, VendorCity,
VendorState, VendorZipCode,
VendorPhone;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 14
Terms
Cursor scrollability
Cursor sensitivity
Scope of a cursor
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 15
The syntax of the FETCH statement
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
[INTO @variable_name [, ...]]
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 16
A DECLARE CURSOR statement
DECLARE Vendor_Cursor CURSOR
STATIC
FOR SELECT VendorID, VendorName FROM Vendors;
FETCH statements for retrieving rows
from the cursor
-- Retrieves the next row
FETCH FROM Vendor_Cursor;
-- Retrieves the next row
FETCH NEXT FROM Vendor_Cursor;
-- Retrieves the previous row
FETCH PRIOR FROM Vendor_Cursor;
-- Retrieves the first row
FETCH FIRST FROM Vendor_Cursor;
-- Retrieves the last row
FETCH LAST FROM Vendor_Cursor;
-- Retrieves the third row
FETCH ABSOLUTE 3 FROM Vendor_Cursor;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 17
FETCH statements for retrieving rows
from the cursor (continued)
-- Retrieves the tenth row after the current row
FETCH RELATIVE 10 FROM Vendor_Cursor;
-- Retrieves the row two rows before the current row
FETCH RELATIVE –2 FROM Vendor_Cursor;
-- Retrieves the current row again
FETCH RELATIVE 0 FROM Vendor_Cursor;
-- Retrieves the next row and assigns the values
-- to two local variables
FETCH FROM Vendor_Cursor
INTO @VendorIDVar,@VendorNameVar;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 18
@@FETCH_STATUS system function values
Value
0
-1
-2
Description
The FETCH was successful.
The FETCH was unsuccessful because
it reached the end of the result set.
The FETCH was unsuccessful because
the row was deleted.
Warning
@@FETCH_STATUS is global to all of the cursors open on the
current connection. So if you’re using multiple cursors, don’t code
other statements between Fetch and @@FETCH_STATUS.
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 19
A WHILE loop that steps forward
through a result set
FETCH FIRST FROM Vendor_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
...
FETCH NEXT FROM Vendor_Cursor;
END;
Murach's SQL Server 2012, C16
© 2012, Mike Murach & Associates, Inc.
Slide 20