Oracle DBA Daily Experiences

http://msdn.microsoft.com/en-us/library/ms186256.aspx

STATIC – data is scrollable, it require space on tempdb and changes to data are not visible right away. It does not allow modification.

CREATE TABLE #temp (k1 INT IDENTITY, c1 int)

INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES

– SELECT * FROM #temp

DECLARE c CURSOR STATIC FOR SELECT k1, c1 FROM #temp

OPEN c
FETCH c
UPDATE #temp SET c1=2 WHERE k1 = 2
FETCH c
– FETCH PRIOR FROM c
SELECT * FROM #temp

CLOSE c
DEALLOCATE c

DROP TABLE #temp

Output:
k1 c1
———– ———–
1 NULL

k1 c1
———– ———–
2 NULL

k1 c1
———– ———–
1 NULL
2 2
3 NULL
4 NULL

CREATE TABLE #temp (k1 INT IDENTITY, c1 int)

INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES

SELECT * FROM #temp

– FORWARD_ONLY – default cursor returs rows sequentially and doesn’t require space on tempdb and changes to data is visible right away
– The cursor is not scrollable
– DECLARE c CURSOR FORWARD_ONLY FAST_FORWARD FOR SELECT k1, c1 FROM #temp
DECLARE c CURSOR FOR SELECT k1, c1 FROM #temp

OPEN c
FETCH c
UPDATE #temp SET c1=2 WHERE k1 = 2
FETCH c
SELECT * FROM #temp

CLOSE c
DEALLOCATE c

DROP TABLE #temp

Output:
k1 c1
———– ———–
1 NULL

k1 c1
———– ———–
2 2

k1 c1
———– ———–
1 NULL
2 2
3 NULL
4 NULL