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

h – Left
l – Right
k – Up
j – Down
w – Move forward to beginning of next word
e – Move end of the word
b – Move backward to beginning of the previous word
0 – First position on the line
$ – Last position on the line
^ – First non-blank position on the line
+ or <Return> – First non-blank character on the next line
- – First non-blank character on the previous line
n| – Column n of the current line
H – Top line of screen
L – Bottom line of screen
M – Middle line of screen