laika222
9/20/2017 - 7:16 PM
CURSOR
-- A CURSOR allows you to iterate over a set of data one row at a time, rather than
-- acting on the whole set at once. This is often avoided though since using a cursor
-- can be much more inefficient than using a set-based operation.
/**********************
SIMPLE CURSOR
**********************/
-- declare the cursor
DECLARE my_cursor CURSOR FOR
-- state the code that the cursor will complete
SELECT productID, productname, price FROM products
-- open the cursor
OPEN my_cursor
--
FETCH NEXT FROM my_cursor
-- see if there are any remaining rows to fetch, and if there are, then fetch the next row
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor
END
-- close the cursor
CLOSE my_cursor
DEALLOCATE my_cursor
/*
The result will be that the lines will be lines will be selected one row at a time. This will stop
when there are no more rows to fetch (when @@FETCH_STATUS = -1),also seen at the bottom of the results
where it says 0 rows affected).
productID productname price
----------- -------------------------------------------------- ---------------------
664 bells 25.00
(1 row affected)
productID productname price
----------- -------------------------------------------------- ---------------------
666 rhodes 353.00
(1 row affected)
productID productname price
----------- -------------------------------------------------- ---------------------
(0 rows affected)
*/
/**********************
CURSOR INSERTING VALUES INTO VARIABLES THAT CAN BE USED FOR OTHER PURPOSES
**********************/
/*
You can also declare a cursor into a variable, and use that variable value for updates. For
example, say you have a products table called products_store:
ID productID productname price
----------- ----------- -------------------------------------------------- ---------------------
1 2 guitar 9.25
2 4 keyboard 72.00
3 7 tuba 9.25
4 8 drums 33.00
1002 9 shaker 14.20
Then say you have a second table called products_store_discount.
ID productID productname price
----------- ----------- -------------------------------------------------- ---------------------
1 2 guitarSTRING 880.00
2 4 keyboard 2222.00
3 10 shaker 0.01
4 15 duduk 22525.00
5 23 microphone 25.00
You can update the prices in products_store from products_store_discount.
*/
-- declare variables to hold the ID and price values that the cursor retreives
declare @cursorID INT
declare @cursorprice DECIMAL(22,2)
-- declare the cursor
DECLARE my_cursor CURSOR FOR
-- state the code - the cursor will grab the ID number from products_store
SELECT ID, price FROM products_store_discount
-- open the cursor
OPEN my_cursor
-- fetch the first row value into the variable @ID.
-- in this case, it'll grab the first ID (1) and place that into @ID
FETCH NEXT FROM my_cursor into @cursorID, @cursorprice
-- see if there are any remaining rows to fetch, and if there are, then fetch the next row
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE products_store
SET price = @cursorprice where ID = @cursorID
FETCH NEXT FROM my_cursor into @cursorID, @cursorprice
END
-- close the cursor
CLOSE my_cursor
DEALLOCATE my_cursor
/*
If you now look at products_store, you'll see that the prices have been updated. Unlike a normal
update statement, these updates ocurred one row at a time.
ID productID productname price
----------- ----------- -------------------------------------------------- ---------------------
1 2 guitar 880.00
2 4 keyboard 2222.00
3 7 tuba 0.01
4 8 drums 22525.00
1002 9 shaker 14.20
*/