120 likes | 300 Views
Cursor. Basis Data 2. 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). Cursor.
E N D
Cursor Basis Data 2
Cursor • CURSOR digunakanuntukmengaksessetiap row padasuatu table ataudari result set • Pada OOP, dapatdimisalkanlayaknyaforeach(walaupunberbeda), namunkonsepnyauntukmengiterasielemen (row) padasuatu array (table)
Cursor • Yang harusdiperhatikandandibuatdalam cursor antara lain: • Declaring Cursor • Deklarasivariabel CURSOR • Opening Cursor • Membuka CURSOR sebelum fetching data dari table • Fetching Cursor • Mengambil data dari table • Closing Cursor • Menutup CURSOR • Deallocate • Mendealokasikan CURSOR yang telahdideklarasikan
Declaring CURSOR • Syntax • DECLAREcursor_name [INSENSITIVE] [SCROLL] CURSORFORselect_statement [FOR {READ ONLY | UPDATE [OFcolumn_name[,...n]]}] • Contoh • DECLARE @getAccountIDCURSORFOR SELECT Account_IDFROMAccounts
Opening Cursor • Syntax: • OPEN { { [GLOBAL] cursor_name } | cursor_variable_name} • Contoh: • OPEN @getAccountID
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: • FETCHNEXTFROM @getAccountIDINTO @AccountID • FETCHPRIORFROM @getAccountIDINTO @AccountID
Fetching Status • @@FETCH_STATUS mengembalikannilai status darihasil FETCH CURSOR terakhir. Biasanyadigunakandalam WHILE untukmengiterasi CURSOR • 0 sukses • -1 gagalataudiluar result set • -2 row yang diambilhilang • Pengambilan Fetch Status harussebelum FETCH • Contoh • WHILE @@FETCH_STATUS = 0BEGINPRINT @AccountIDFETCHNEXTFROM @getAccountIDINTO @AccountIDEND
Closing Cursor • Syntax: • CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name} • Contoh: • CLOSE @getAccountID • Catatan:Jikatelahmenutupkursor, tetapibelumdeallocated, makadapatdibukakembalibiladiperlukan.
Deallocate Cursor • Syntax: • DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name} • Contoh: • DEALLOCATE @getAccountID
Simple Cursor USE AdventureWorks2008R2; GO DECLAREvend_cursorCURSOR FOR SELECT BusinessEntityID, Name, CreditRatingFROMPurchasing.Vendor OPENvend_cursor FETCHNEXTFROMvend_cursor;
DECLARE @AccountIDINTDECLARE @getAccountIDCURSORSET @getAccountID = CURSORFOR SELECTAccount_IDFROM AccountsOPEN @getAccountIDFETCHNEXTFROM @getAccountIDINTO @AccountIDWHILE @@FETCH_STATUS = 0BEGINPRINT @AccountIDFETCHNEXTFROM @getAccountIDINTO @AccountIDENDCLOSE @getAccountIDDEALLOCATE @getAccountIDDECLARE @AccountIDINTDECLARE @getAccountIDCURSORSET @getAccountID = CURSORFOR SELECTAccount_IDFROM AccountsOPEN @getAccountIDFETCHNEXTFROM @getAccountIDINTO @AccountIDWHILE @@FETCH_STATUS = 0BEGINPRINT @AccountIDFETCHNEXTFROM @getAccountIDINTO @AccountIDENDCLOSE @getAccountIDDEALLOCATE @getAccountID