1 / 12

Cursor

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.

cayla
Télécharger la présentation

Cursor

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Cursor Basis Data 2

  2. Cursor • CURSOR digunakanuntukmengaksessetiap row padasuatu table ataudari result set • Pada OOP, dapatdimisalkanlayaknyaforeach(walaupunberbeda), namunkonsepnyauntukmengiterasielemen (row) padasuatu array (table)

  3. 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

  4. Declaring CURSOR • Syntax • DECLAREcursor_name [INSENSITIVE] [SCROLL] CURSORFORselect_statement [FOR {READ ONLY | UPDATE [OFcolumn_name[,...n]]}] • Contoh • DECLARE @getAccountIDCURSORFOR SELECT Account_IDFROMAccounts

  5. Opening Cursor • Syntax: • OPEN { { [GLOBAL] cursor_name } | cursor_variable_name} • Contoh: • OPEN @getAccountID

  6. 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

  7. 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

  8. Closing Cursor • Syntax: • CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name} • Contoh: • CLOSE @getAccountID • Catatan:Jikatelahmenutupkursor, tetapibelumdeallocated, makadapatdibukakembalibiladiperlukan.

  9. Deallocate Cursor • Syntax: • DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name} • Contoh: • DEALLOCATE @getAccountID

  10. CONTOH LENGKAP CURSOR

  11. Simple Cursor USE AdventureWorks2008R2; GO DECLAREvend_cursorCURSOR FOR SELECT BusinessEntityID, Name, CreditRatingFROMPurchasing.Vendor OPENvend_cursor FETCHNEXTFROMvend_cursor;

  12. 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

More Related