Basis Data 2 Cursor CURSOR digunakan untuk mengakses setiap row pada suatu table atau dari result set Pada OOP dapat dimisalkan layaknya foreach ID: 534426
Download Presentation The PPT/PDF document "Cursor" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Cursor
Basis Data 2Slide2
Cursor
CURSOR
digunakan
untuk
mengakses
setiap
row
pada
suatu
table
atau
dari
result set
Pada
OOP,
dapat
dimisalkan
layaknya
foreach
(
walaupun
berbeda
),
namun
konsepnya
untuk
mengiterasi
elemen
(row)
pada
suatu
array (table)Slide3
Cursor
Yang
harus
diperhatikan
dan
dibuat
dalam
cursor
antara
lain:
Declaring Cursor
Deklarasi
variabel
CURSOR
Opening Cursor
Membuka
CURSOR
sebelum
fetching data
dari
table
Fetching Cursor
Mengambil
data
dari
table
Closing Cursor
Menutup
CURSOR
Deallocate
Mendealokasikan
CURSOR yang
telah
dideklarasikanSlide4
Declaring CURSOR
Syntax
DECLARE
cursor_name
[
INSENSITIVE
] [
SCROLL
]
CURSOR
FOR
select_statement
[
FOR
{
READ ONLY
|
UPDATE
[
OF
column_name
[,...n
]]}]
Contoh
DECLARE
@
getAccountID
CURSOR
FOR
SELECT
Account_ID
FROM
AccountsSlide5
Opening Cursor
Syntax:
OPEN
{ { [
GLOBAL
]
cursor_name
} |
cursor_variable_name
}
Contoh
:
OPEN
@
getAccountIDSlide6
Fetching Cursor
Syntax:
FETCH
[ [
NEXT
|
PRIOR
|
FIRST
|
LAST
|
ABSOLUTE
{n | @
nvar
}
|
RELATIVE
{n | @
nvar
}
]
FROM
]
{ { [
GLOBAL
]
cursor_name
} | @
cursor_variable_name
}
[
INTO
@
variable_name
[,...n]
]
Contoh
:
FETCH
NEXT
FROM
@
getAccountID
INTO
@
AccountID
FETCH
PRIOR
FROM
@
getAccountID
INTO
@
AccountIDSlide7
Fetching Status
@@
FETCH_STATUS
mengembalikan
nilai
status
dari
hasil
FETCH CURSOR
terakhir
.
Biasanya
digunakan
dalam
WHILE
untuk
mengiterasi
CURSOR
0
sukses
-1
gagal
atau
diluar
result set
-2 row yang
diambil
hilang
Pengambilan
Fetch Status
harus
sebelum
FETCH
Contoh
WHILE
@@FETCH_STATUS = 0
BEGIN
PRINT
@
AccountID
FETCH
NEXT
FROM
@
getAccountID
INTO
@
AccountID
ENDSlide8
Closing Cursor
Syntax:
CLOSE
{ { [
GLOBAL
]
cursor_name
} |
cursor_variable_name
}
Contoh
:
CLOSE
@
getAccountID
Catatan
:
Jika
telah
menutup
kursor
,
tetapi
belum
deallocated
,
maka
dapat
dibuka
kembali
bila
diperlukan
.Slide9
Deallocate Cursor
Syntax:
DEALLOCATE
{ { [
GLOBAL
]
cursor_name
} | @
cursor_variable_name
}
Contoh
:
DEALLOCATE
@
getAccountIDSlide10
CONTOH LENGKAP CURSORSlide11
Simple Cursor
USE
AdventureWorks2008R2;
GO
DECLARE
vend_cursor
CURSOR
FOR
SELECT
BusinessEntityID
, Name,
CreditRating
FROM
Purchasing.Vendor
OPEN
vend_cursor
FETCH
NEXT
FROM
vend_cursor
;Slide12
DECLARE
@
AccountID
INT
DECLARE
@
getAccountID
CURSOR
SET
@
getAccountID
=
CURSOR
FOR
SELECT
Account_ID
FROM
Accounts
OPEN
@
getAccountID
FETCH
NEXT
FROM
@
getAccountID
INTO
@
AccountID
WHILE
@@FETCH_STATUS = 0
BEGIN
PRINT
@
AccountID
FETCH
NEXT
FROM
@
getAccountID
INTO
@
AccountID
END
CLOSE
@
getAccountID
DEALLOCATE
@
getAccountID