CURSORS
Cursor is a logical area, which is used to retrieve a
particular nth record. Selecting a particular nth record is not possible
through physical area (Table). In such situations one logical area (Cursor) we
can create and then we can select a particular nth record. Cursors are used to
store transaction information temporarily.
Types of Cursors:
- STATIC CURSOR
- DYNAMIC CURSOR
- KEYSET CURSOR
- FORWARD_ONLY CURSOR
DEPT
DEPTNO DNAME LOC
10 SALES HYD
20 HR CHE
30 IR BAN
40 A/C MUM
Syntax to declare the Cursor:
DECLARE CURSORNAME CURSOR CURSORTYPE
FOR SELECT * FROM TABLENAME
Syntax to open the Cursor:
OPEN CURSORNAME
Syntax to fetch the records from Cursor:
FETCH {FIRST/NEXT/PRIOR/LAST/ABSOLUTE N/RELATIVE N}
FROM CURSORNAME
Syntax to close the Cursor:
CLOSE CURSORNAME
Syntax to de allocates the Cursor:
DEALLOCATE CURSORNAME
FIRST: Fetches first record from the cursor
NEXT: Fetches next record from the current position
of the cursor
PRIOR: Fetches previous record from the current
position of the cursor
LAST: Fetches last record from the cursor
ABSOLUTE N: Fetches nth record from the top of the
cursor if n is positive fetches the nth record from bottom of the cursor if n
is negative. Where n is an integer
RELATIVE N: Fetches nth next record from current
position of the cursor if n is positive fetches nth previous record from the
current position of the cursor if n is negative where n is an integer.
1. STATIC CURSOR:
This is the logical area in which dynamic updations not
possible. If we want those updations in logical area we close the cursor and we
need to reopen it.
E.g.:
DECLARE SC CURSOR STATIC FOR SELECT * FROM DEPT
OPEN SC
FETCH FIRST FROM SC
10 SALES HYD
UPDATE DEPT SET DEPTNO=15 WHERE DEPTNO=10
FETCH FIRST FROM SC
10 SALES HYD
CLOSE SC
OPEN SC
FETCH FIRST FROM SC
15 SALES HYD
FETCH NEXT FROM SC
20 HR CHE
FETCH PRIOR FROM SC
15 SALES HYD
FETCH LAST FROMSC
40 A/C MUM
FETCH ABSOLUTE 2 FROM SC
20 HR CHE
FETCH ABSOLUTE –2 FROM SC
30 IR BAN
FETCH FIRST FROM SC
15 SALES HYD
FETCH RELATIVE 2 FROM SC
30 IR BAN
FETCH RELATIVE –2 FROM SC
15 SALES HYD
CLOSE SC
DEALLOCATE SC
2. DYNAMIC CURSOR:
This is the logical area in which dynamic updations
possible. We need to close and reopen the cursor for the modified values
E.g.:
DECLARE DC CURSOR DYNAMIC FOR SELECT * FROM DEPT
OPEN SC
FETCH FIRST FROM DC
15 SALES HYD
UPDATE DEPT SET DEPTNO=10 WHERE DEPTNO=15
FETCH FIRST FROM DC
10 SALES HYD
CLOSE DC
DEALLOCATE DC
Note: ABSOLUTE N will not be supported by the dynamic cursor
because dynamic updations are possible.
KEYSET CURSOR:
This is the logical area, which
is useful only when there is a primary key in the table. This logical area
holds only Primary key column values. Based on the key column values in the
logical area (Cursor) the rest column values are coming from physical area
(Table).
E.g.:
DECLARE KC CURSOR KEYSET FOR
SELECT * FROM DEPT
OPEN KC
FETCH FIRST FROM KC
10 SALES HYD
UPDATE DEPT SET LOC=’SRNAGAR’ WHERE
LOC=’HYD’
FETCH FIRST FROM KC
10 SALES SRNAGAR
UPDATE DEPT SET DEPTNO=15 WHERE
DEPTNO=10
FETCH FIRST FROM KC
0 NULL NULL
CLOSE KC
OPEN KC
FETCH FIRST FROM KC
15 SALES HYD
CLOSE KC
DEALLOCATE KC
Note: Dynamic updations are not
possible on key column of the keyset cursor, we have to close the cursor and we
need to reopen it for the modified values.
FORWARD_ONLY CURSOR:
This is the most unused logical
area because it supports only NEXT operation.
E.g.:
DECLARE FC CURSOR FORWARD_ONLY
FOR SELECT * FROM DEPT
OPEN FC
FETCH FIRST FROM FC
Error Message
FETCH NEXT FROM FC
15 SALES HYD
CLOSE SC
DEALLOCATE FC
NOTE
If the cursor does not found any
value in its searching process then it will display the corresponding column
values.
E.g.: BATCH TO FETCH MULTIPLE
RECORDS FROM STATIC CURSOR
Step1: DECLARE SC CURSOR STATIC
FOR SELECT * FROM DEPT
Step2:
DECLARE @N INT
OPEN SC
SET @N=2
WHILE
(@N<=4)
BEGIN
FETCH ABSOLUTE
@N FROM SC
SET @N=@N+1
END
Step3: CLOSE SC
DEALLOCATE
SC
OUTPUT:
DEPTNO DNAME LOC
20 HR CHE
DEPTNO DNAME LOC
30 IR BAN
DEPTNO DNAME LOC
40 A/C MUM
No comments:
Post a Comment