Oracle DBA Daily Experiences

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