mk. praktikum -...

Capaian:

Mahasiswa mampu menerapkan konsep-konsep pemrograman basis data.

Materi:

SQL: select, join, subquery Procedural Language/SQL:

variabel, tipe-data, control-flow statement, cursor, prosedur dan fungsi, trigger

Integrated Development Environment (IDE): koneksi dan recordset, dataset

MK. PRAKTIKUM

Tegar Heru Susilo 2014

Laboratorium Komputer STIMIK STIKOM Surabaya

i

Daftar Isi

SQL (sekuel) ............................................................................................................ 1

Perkenalan .......................................................................................................... 3

SQL Server ...................................................................................................... 4

Struktur Basis Data .................................................................................... 5

SELECT .................................................................................................................. 7

Sintak ................................................................................................................ 7

Select_list ........................................................................................................ 8

Mengambil data dari tabel (data retrieval) .................................... 8

Menyaring data ............................................................................................ 9

Pengurutan data ....................................................................................... 12

Operator Aritmatika ............................................................................... 13

Agregasi dan Pengelompokan Data ................................................ 14

Multiple-table Query ................................................................................... 16

Join .................................................................................................................. 16

Subquery ...................................................................................................... 20

SQL Function ................................................................................................... 21

Transaction ...................................................................................................... 23

Penambahan data .................................................................................... 23

Modifikasi data.......................................................................................... 24

Penghapusan data ................................................................................... 25

Latihan ............................................................................................................... 26

Tipe Data dan Variabel .................................................................................... 27

Script ................................................................................................................... 29

Script Block ...................................................................................................... 30

Tipe Data ........................................................................................................... 30

Character String ....................................................................................... 31

ii

Numeric ........................................................................................................ 31

Temporal ..................................................................................................... 32

Binary ............................................................................................................ 32

Boolean ......................................................................................................... 33

Other .............................................................................................................. 33

Variabel ............................................................................................................. 34

Deklarasi Variabel ................................................................................... 34

Memberi Nilai pada Variabel ............................................................. 35

System Variable ........................................................................................ 36

Operasi pada Variabel ................................................................................ 38

Latihan ............................................................................................................... 39

Control-flow Statement .................................................................................. 41

Percabangan.................................................................................................... 43

Macam-macam Bentuk Percabangan............................................. 45

Perulangan ....................................................................................................... 47

Latihan ............................................................................................................... 53

Cursor ...................................................................................................................... 55

Membuat dan Menggunakan Cursor ................................................... 57

Contoh Penggunaan Cursor ................................................................ 60

Implicit Cursor ............................................................................................... 61

Nested Cursor ................................................................................................. 61

Latihan ............................................................................................................... 65

Prosedur dan Fungsi ........................................................................................ 67

Prosedur ........................................................................................................... 69

Membuat Prosedur ................................................................................. 70

Parameter dalam Prosedur ................................................................ 71

Eksekusi Prosedur .................................................................................. 72

iii

Pemeliharaan Data .................................................................................. 74

Fungsi ................................................................................................................. 75

User-Defined Function .......................................................................... 76

Deterministic Function ......................................................................... 79

Latihan ............................................................................................................... 81

Trigger .................................................................................................................... 83

DML Trigger .................................................................................................... 86

Membuat DML Trigger ............................................................................... 90

Latihan ............................................................................................................... 93

Connection dan Recordset ............................................................................ 95

ADO.NET ........................................................................................................... 97

Memahami Arsitektur ADO.NET ...................................................... 98

Connections .................................................................................................. 100

Bagaimana cara kerjanya .................................................................. 102

Membuat session .................................................................................. 103

Security and Password dalam SqlConnection ........................ 104

Connection String Parameters untuk SqlConnection .......... 104

Recordset ....................................................................................................... 105

Pengambilan Data................................................................................. 106

Manipulasi Data ..................................................................................... 108

Latihan ............................................................................................................ 110

Dataset ................................................................................................................. 113

Dataset ............................................................................................................ 115

Membuat Dataset ....................................................................................... 116

Bagaimana cara kerjanya .................................................................. 118

Manipulasi Data .......................................................................................... 121

Create ......................................................................................................... 121

iv

Read .............................................................................................................122

Update .........................................................................................................123

Delete...........................................................................................................124

Kontrol pada Transaksi ......................................................................124

Pencarian dan Penyortiran Data .........................................................125

DataView....................................................................................................128

Dataset dan Data Source .........................................................................130

DataGridView ..........................................................................................131

Update .........................................................................................................132

Latihan .............................................................................................................133

BAB I

SQL (sekuel)

Sub-Materi: Select Join

Subquery DML

SQL (sekuel)

Praktikum Pemrograman Basis Data pg. 2

(halaman kosong)

SQL (sekuel)


Bab I

SQL (sekuel)

Sub-Materi: Select Join

Subquery DML

Perkenalan

Matakuliah Praktikum Pemrograman Basis Data (PBD) merupakan MK praktek dari matakuliah PBD. Matakuliah ini mengintegrasikan pemrograman di sisi server (basis data) dan pemrograman di sisi client. Karena itulah matakuliah ini mempunyai 2 matakuliah prasyarat, yaitu (1) matakuliah Sistem Basis Data (SBD) dan (2) matakuliah Bahasa Pemrograman (BPro).

Dalam matakuliah SBD, materi yang dibahas meliputi desain dan pembuatan basis data mengikuti normalisasi, penyimpanan data, dan data retrieval (temu kembali data). Penyimpanan data dalam basis data menggunakan sintak-sintak data manipulation language (DML), sedangkan untuk data retrieval menggunakan perintah SELECT. Keduanya dalam dunia programming disebut sebagai CRUD (Create-Read-Update-Delete). Semua materi tersebut merupakan bagian dari pembelajaran dasar structured query language (SQL – baca sekuel), yaitu bahasa native bagi basis data. Native ibarat bahasa inggris bagi orang Amerika, dan bahasa indonesia bagi orang Indonesia.

Pemrograman disisi basis data lebih umum disebut dengan SQL Programming, yaitu bahasa

SQL (sekuel)


pemrograman yang memakai SQL sebagai dasar pemrogramannya. Karena menggunakan SQL, maka dari itu, pembahasan pada pertemuan 1 ini difokuskan pada penyegaran kembali (refresh) materi-materi SQL.

Sedangkan dalam matakuliah BPro, materi yang dibahas meliputi variable, control-flow statement, serta procedure and function. SQL Programming, sesuai dengan namanya, juga menggunakan semua materi dalam matakuliah BPro. Jadi pembelajaran pada matakuliah Praktikum PBD seutuhnya merupakan pemakaian kembali dan kelanjutan dari matakuliah SBD dan BPro.

Selain SQL Programming, juga ada pemrograman di sisi client. Fokus utamanya adalah bagaimana melakukan komunikasi dengan basis data serta menerapkan SQL dan SQL Programming dalam aplikasi client.

SQL Server

Database Management System (DBMS) yang dipakai dalam matakuliah Praktikum PBD adalah SQL Server 2005 Express Edition. Selain ringan dan bebas dipakai,

versi ini juga memiliki beberapa fitur penting seperti keamanan. Namun SQL Server Management Studio, sebuah kakas bantu untuk mengelola lingkungan SQL Server, tidak ada dalam paket instalasi SQL Server 2005 Express Edition. Tool ini harus didownload sendiri di situs resminya. Di laboratorium komputer, telah di-install SQL Server 2005 Express Edition beserta Management Studio-nya.

Langkah pertama yang dilakukan adalah membuat basis data yang akan dipakai selama 8x minggu praktikum. Untuk script pembuatan basis data dapat diunduh di http://192.168.100.3/latihan/132/BDS/create table.sql. Setelah diunduh, ikuti langkah-langkah berikut:

1. Buka SQL Server Management Studio Express Edition (SSMSEE) melalui Start Menu

http://192.168.100.3/latihan/132/BDS/create%20table.sql

SQL (sekuel)


2. Login:

Isi “Server name:” dengan “.\SQLEXPRESS” – pilih mode “Windows Authentication” – klik tombol Connect.

3. Menu File – Open – File atau Ctrl+O. Buka script yang telah

diunduh. Lalu jalankan script melalui tombol 4. Setelah dieksekusi, ada 3 proses yang terjadi:

- Pembuatan basis data beserta file-nya - Pembuatan tabel-tabel beserta constraint-nya - Pengisian data-data kedalam tabel

Struktur Basis Data

Basis data dibangun oleh database schema. Dalam database schema inilah obyek-obyek basis data dibangun. Setiap schema diberikan ke satu atau lebih user (atau login). Dalam matakuliah Praktikum PDB, hanya dibahas obyek-obyek antara lain Table, Function, Stored Procedure dan Trigger. Pertemuan pertama difokuskan pada Table serta bagaimana DML dan SELECT pada Table.

Database schema yang dipakai untuk TUGAS PRAKTIKUM selama 8x pertemuan dapat dilihat pada Gambar 1.1, yaitu Putra Jaya. Sedangkan untuk LATIHAN PRAKTIKUM dapat dilihat pada Gambar 1.2, yaitu PBD . Setiap tabel saling berelasi, karena itulah dinamakan basis data relasional. Tabel yang dihubungkan dengan tanda kunci merupakan tabel yang ber-primary key, atau

SQL (sekuel)


parent table (tabel induk). Sedangkan tabel yang tidak memiliki tanda kunci merupakan tabel yang ber-foreign key, atau child table (tabel anak).

Gambar 1.1 Database schema Putra Jaya

Tabel terdiri dari row (baris) dan column (kolom). Setiap baris merepresentasikan data, sedangkan kolom merepresentasikan struktur data yang disimpan. Struktur ini dibagi menjadi 2 bagian yaitu tipenya dan panjangnya. Sebagai contoh, kolom NAMAKONSUMEN pada tabel KONSUMEN mempunyai tipe VARCHAR untuk menyimpan karakter dengan panjang semaksimalnya 100 karakter. Dengan perbedaan tipe dan panjang, otomatis akan berpengaruh pada cara kita dalam melakukan DML dan SELECT.

SQL (sekuel)


Gambar 1.2 Database schema PBD

SELECT

SELECT merupakan salah satu pondasi dalam SQL Programming. SELECT digunakan untuk menampilkan data, terlebih untuk mencari informasi dalam kumpulan data.

Sintak

SELECT dibagi kedalam 6 komponen, antara lain:

1. SELECT. Diikuti oleh <select_list>, dapat berupa literal_value atau column_list atau asterisk (*).

2. FROM. Diikuti oleh <table_name> sesuai dengan column_list. Jadi jika ada data yang diambil dari kolom tertentu, harus diketahui kolom tersebut diambil dari tabel mana. Tabel pada FROM dapat diikuti dengan alias untuk mempermudah penulisan khususnya ketika join dan subquery.

3. WHERE. Diikuti oleh kondisi secara umum. 4. GROUP BY. Diikuti oleh <select_list>. Bagian ini muncul

ketika ada fungsi-fungsi agregasi. 5. HAVING. Diikuti oleh kondisi hanya untuk fungsi-fungsi

agregasi.

SQL (sekuel)


6. ORDER BY. Diikuti oleh <select_list>.

Kode 1.1

SELECT <select_list>

[FROM <table_name>]

[WHERE <kondisi1> [AND/OR <kondisi2>]]

[GROUP BY <select_list>]

[HAVING <kondisi1> [AND/OR <kondisi2>]]

[ORDER BY <select_list>]

Select_list

Merupakan daftar select. Dapat berupa literal value atau column_list. Perhatikan contoh berikut:

Kode 1.2

SELECT 1, 'STIKOM Surabaya';

Contoh pada Kode 1.2 mengembalikan satu baris data dengan dua kolom. <select_list> dalam contoh tersebut tidak menggunakan data yang tersimpan dalam tabel, dan inilah yang disebut sebagai literal_value. Penggunaan konkrit literal_value umumnya digunakan bersama dengan column_list untuk menghasilkan sebuah expression.

Mengambil data dari tabel (data retrieval)

Fungsi utama dari SELECT adalah untuk pengambilan data (data retrieval) yang tersimpan dalam (beberapa) tabel. Perhatikan contoh berikut:

Kode 1.3

SELECT 1 '1', 'STIKOM Surabaya' STIKOM,

nim, nim + '@stikom.edu' EMAIL

FROM mahasiswa;

Hasil:

1 STIKOM Nim EMAIL

1 STIKOM Surabaya 05390102208 [email protected]


.. … … …


SQL (sekuel)


Contoh pada Kode 1.3 menampilkan seluruh data di tabel Mahasiswa. Tanpa kata kunci FROM, query tersebut menghasilkan error.

Muncul pertanyaan, bagaimana jika ada data yang sama, tetapi ingin ditampilkan satu kali? Misal, kebutuhan untuk mengetahui siapa saja konsumen yang telah melakukan pembelian. Perhatikan contoh berikut:

Kode 1.4

SELECT nim

FROM nilai;

Hasil dari query pada Kode 1.4 tersebut adalah banyak NIM yang sama. Untuk mengeliminasi data-data yang sama, dibutuhkan DISTINCT. Dengan mengimplementasikan DISTINCT, query pada Kode 1.4 diubah menjadi seperti ini:

Kode 1.5

SELECT DISTINCT nim

FROM nilai;

Menyaring data

Tidak semua data yang ada pada tabel, ingin ditampilkan. Terlebih ketika tabel terbagi kedalam banyak kolom dengan jumlah data yang sangat besar. Padahal data yang diambil hanyalah sebuah data, contohnya data mahasiswa milik Tegar Heru Susilo. Untuk efektifitas query, perlu ditambahkan kata kunci WHERE. Perhatikan contoh berikut:

Kode 1.6

SELECT *

FROM mahasiswa

WHERE nama = 'Alif';

Hasil:

nim nama alamat kota jns_kel.. sts_nikah

05410104001 Alif Jl. Jagir 20 Surabaya P B

SQL (sekuel)


Penyaringan data membutuhkan kondisi. Ada 3 bagian dalam penulisan kondisi, antara lain (1) expression, (2) operator pembanding, dan (3) value.

Sedangkan nilai yang dihasilkan dari kondisi ini ada 3 macam, yaitu TRUE, FALSE, dan UNKNOWN. TRUE berarti kondisi menghasilkan nilai benar, FALSE berarti kondisi menghasilkan nilai salah, sedangkan UNKNOWN berkaitan dengan nilai NULL (secara default bernilai FALSE, kecuali menggunakan operator IS).

Dari 3 bagian kondisi, yang perlu diperhatikan adalah operator pembanding. Operator pembanding menentukan nilai akhir kondisi (TRUE, FALSE, atau UNKNOWN). Dalam kode 1.6, operator pembanding yang digunakan adalah ‘sama dengan’ (=). Selain ‘sama dengan’, beberapa operator pembanding lainnya dapat dilihat pada Tabel 1.1.

Tabel 1.1 Operator Pembanding

Operator Keterangan

= Sama dengan

> Lebih besar dari

>= Lebih besar sama dengan

< Kurang dari

<= Kurang dari sama dengan

<> atau != Tidak sama dengan

BETWEEN .. AND .. Diantara 2 nilai

IN (set) Cocok dengan salah satu diantara daftar nilai

LIKE Cocok dengan pola karakter

IS NULL Sama dengan NULL

Beberapa contoh penggunaan operator pembanding dapat dilihat pada query berikut:

Kode 1.7

SELECT *

FROM mahasiswa

WHERE nim BETWEEN '05390102208' AND

'05410104190';

SELECT * FROM mahasiswa

SQL (sekuel)


WHERE nim IN ('05390102208','05410104190');


WHERE naam LIKE '%amb%';


WHERE alamat IS NULL;

Penggunaan operator BETWEEN dan IN pada Kode 1.7 menghasilkan data yang sama. Sedangkan untuk LIKE akan menghasilkan seluruh mahasiswa yang namanya mempunyai kata amb di tengah-tengahnya. Sedikit berbeda dengan penggunaan operator lain yang membutuhkan data yang presisi, LIKE menggunakan wildcards untuk mencari data yang mirip dengan data yang dicari. Wildcards ini dibagi menjadi 2 yaitu (1) % untuk merepresentasikan banyak karakter, dan (2) _ untuk merepresentasikan satu karakter. Contoh penggunaan _ bisa dilihat pada contoh berikut:

Kode 1.8

SELECT *

FROM mahasiswa

WHERE nama LIKE '_e%';

Pada Kode 1.8, query akan menghasilkan seluruh data mahasiswa yang karakter kedua namanya memiliki huruf e.

Bagaimana jika kondisi yang dibutuhkan berjumlah lebih dari satu? Jawabannya adalah dengan menggunakan operator logika untuk memisahkan kondisi-kondisi tersebut. Operator logika memakai tabel kebenaran sebagai acuan dalam pembentukan nilai akhir seluruh kondisi. Perhatikan contoh berikut:

Kode 1.9

SELECT nim, nama, kota

FROM mahasiswa

WHERE kota='Surabaya' and nim='05390102208';

Hasil:

nim nama kota

05390102208 Cinta Surabaya

SQL (sekuel)


Ada 3 macam operator logika yaitu AND, OR dan NOT. Untuk AND, nilai TRUE muncul jika semua kondisi TRUE. Sedangkan untuk OR, nilai FALSE muncul jika semua kondisi FALSE. Sedangkan untuk NOT, merupakan kebalikan dari nilai akhir kondisi.

Ada prioritas dalam penggunaan kondisi seperti yang terlihat pada Tabel 1. 2, namun prioritas ini dapat diabaikan dengan menggunakan parentheses (tanda kurung).

Tabel 1. 2 Prioritas Penggunaan Operator Pembanding

Prioritas Keterangan

1 NOT

2 AND

3 OR

Pengurutan data

Dalam visualisasi informasi, hasil pemrosesan data dapat lebih mudah dibaca ketika data tersebut dapat diurutkan berdasarkan nilai tertentu. Sebagai contoh ketika seorang dosen ingin melihat 10 nilai UAS terbesar. Perhatikan contoh berikut:

Kode 1.10

SELECT nim, uas

FROM nilai

WHERE nid='010103';

Cara seperti ini akan menampilkan data nilai dengan urutan yang sama seperti urutan data ketika dimasukkan. Untuk dapat melihat urutan ranking nilai UAS, cukup dengan menambahkan kata kunci ORDER BY. Pengurutan ini bisa dibentuk dari kecil ke besar (ASCending) atau sebaliknya (DESCending). Sehingga query pada Kode 1.10 berubah menjadi:

Kode 1.11 SELECT nim, kode_mk, uas

FROM nilai

WHERE nid='010103'

ORDER BY uas DESC;

Hasil:

SQL (sekuel)


nim kode_mk uas

05390102208 MK-001 80

06390102666 MK-001 40

07390102802 MK-001 20

Operator Aritmatika

Dalam menampilkan data, ada kalanya data yang ditampilkan merupakan hasil perhitungan missal mencari nilai total penjualan setelah PPn 10%. Untuk mendukung perhitungan ini, diperlukan operator aritmatika. Ada 4 macam operator aritmatika, yang dapat dilihat pada Tabel 1.3.

Tabel 1.3 Operator Aritmatika

Operator Aritmatika Keterangan

+ Untuk operasi penambahan. Jika + ini dipakai untuk tipe data karakter, menjadi operasi penggabungan karakter.

- Untuk operasi pengurangan.

* Untuk operasi perkalian.

/ Untuk operasi pembagian.

Contoh penggunaan operator aritmatika dapat dilihat pada query berikut:

Kode 1.12

SELECT 1 + 1;

SELECT 'Tegar' + ' ' + 'Heru';

SELECT 5 – 1;

SELECT uas * 0.3

FROM nilai;

SELECT tugas / 4

FROM nilai;

SQL (sekuel)


Agregasi dan Pengelompokan Data

Dalam pemrosesan data mentah menjadi data statistik, diperlukan fungsi-fungsi yang dapat meng-agregasi data-data tersebut. Fungsi-fungsi ini meliputi SUM, MIN, MAX, COUNT, dan AVG. SUM untuk menghitung jumlah nilai data, MIN untuk menentukan data paling kecil, MAX untuk menentukan data paling besar, COUNT untuk menghitung jumlah data, dan AVG untuk menghitung rata-rata nilai data.

Dengan melihat pola data yang ada untuk kemudian dilakukan agregasi dan pengelompokan, visualisasi informasi dapat dilakukan dengan mudah. Sebagai contoh, untuk menampilkan rata-rata nilai UAS, nilai UAS terrendah, dan nilai UAS tertinggi dapat dilihat pada query berikut:

Kode 1.13

SELECT AVG(uas) rata_nilai,

MAX(uas) nilai_tertinggi,

MIN(uas) nilai_terendah

FROM nilai;

Hasil:

rata_nilai nilai_tertinggi nilai terendah

49.41 90 0

Penggunaan GROUP BY mampu memilah data yang demikian besar kedalam kelompok-kelompok data untuk mendapatkan informasi-informasi yang lebih spesifik. Sebagai contoh, untuk menampilkan rata-rata nilai UAS, nilai UAS terrendah, dan nilai UAS tertinggi dari seluruh mata kuliah dapat dilihat pada query berikut:

Kode 1.14

SELECT kode_mk, AVG(uas) rata_nilai,



FROM nilai

GROUP BY kode_mk;

SQL (sekuel)


Hasil:

kode_mk rata_nilai nilai_tertinggi nilai terendah

MK-001 46.66 80 20

MK-002 43.33 70 0

MK-003 54.00 90 0

MK-102 65.00 80 50

MK-103 42.00 80 20

Untuk dapat menggunakan kata kunci GROUP BY dengan baik, ada beberapa aturan dalam pemakaian GROUP BY yang harus diperhatikan. Aturan-aturan tersebut antara lain:

1. Jika ada agregasi (SUM, MIN, MAX, COUNT, AVG) dalam <select_list>, kolom tanpa agregasi harus terdaftar dalam GROUP BY.

2. Dengan menggunakan WHERE, kita bisa mengabaikan baris-baris tertentu sebelum dilakukan agregasi dan pengelompokan.

3. Isi dari GROUP BY adalah kolom, bukan alias.

Dari data hasil Kode 1.14, kita bisa melakukan penyaringan data misal hanya untuk mata kuliah yang rata-rata nilai UAS-nya dibawah 50. Hasil perubahan query pada Kode 1.14 untuk proses penyaringan, dapat dilihat pada query berikut:

Kode 1.15




FROM nilai

GROUP BY kode_mk

HAVING AVG(uas)>50;

Hasil:


MK-003 54 90 0

MK-102 65 80 50

SQL (sekuel)


Penyaringan merupakan kondisi. Untuk memberikan kondisi pada fungsi agregasi, dibutuhkan kata kunci HAVING. HAVING mempunyai cara kerja yang sama dengan WHERE. Namun memiliki fungsi yang berbeda. Sehingga jika pada Kode 1.15 diberi kondisi hanya untuk MK-003, maka query menjadi seperti berikut ini:

Kode 1.16




FROM nilai

WHERE kode_mk = 'MK-003'

GROUP BY kode_mk

HAVING AVG(uas)>50;

Hasil:


MK-003 54 90 0

Multiple-table Query

Data-data yang tersimpan dalam basis data, tersebar kedalam beberapa tabel. Tabel-tabel ini dihubungkan dengan yang namanya referential constraint, yaitu hubungan antara foreign key dan primary key.

Karena itulah, untuk mendapatkan informasi yang tersebar, dibutuhkan metode untuk menggabungkan property tabel-tabel tersebut. Metode yang digunakan ada 2 macam, yaitu join dan subquery.

Perbedaannya sederhana, join menggunakan satu SELECT, sedangkan subquery menggunakan dua atau lebih SELECT (umumnya dikatakan sebagai SELECT within a SELECT).

Join

Bentuk join pertama kali adalah menggunakan kata kunci WHERE untuk melakukan penggabungan tabel. Jadi tabel-tabel yang

SQL (sekuel)


ingin digabungkan ditulis dalam kata kunci FROM, sedangkan penggabungannya ditulis dalam WHERE. Sintak untuk join menggunakan WHERE adalah sebagai berikut:

Kode 1.17 SELECT <select_list>

FROM <table1>, <table2> [, ...]

WHERE <table1.PK = table2.FK> [AND ...]

Contoh:

Kode 1.18 SELECT m.nama, n.kode_mk, n.uas

FROM mahasiswa m, nilai n

WHERE m.nim=n.nim;

Hasil:

nama kode_mk uas

Cinta MK-001 80

Cinta MK-002 0

Alif MK-002 70

… … …

Gala MK-103 40

Query pada Kode 1.18 menghasilkan sejumlah baris yang merupakan kombinasi jumlah data antara mahasiswa dan nilai. Dari operator pembanding yang digunakan, sudah jelas bahwa query ini menghasilkan baris yang mempunyai data yang sama diantara dua table (produk dan kategori). Join jenis ini dapat juga disebut dengan equijoin.

Perkembangan SQL ANSI sejak tahun 1990-an, menambahkan model baru dalam join, yaitu menggunakan hanya kata kunci FROM sebagai referensi utama baik untuk tabel maupun untuk penggabungannya. Sintaknya adalah sebagai berikut:

Kode 1.19


FROM <table1> JOIN <table2>

ON < table1.PK = table2.FK> [[AND ...]

JOIN ...];

SQL (sekuel)


Contoh:

Kode 1.20

SELECT m.nama, n.kode_mk, n.uas

FROM mahasiswa m JOIN nilai n

ON m.nim = n.nim;

Antara model lama dengan model baru, tidak ada perbedaan signifikan pada performa dan execution plan untuk data-data sederhana, namun berbeda halnya dengan data-data komplek dengan ruang lingkup yang besar.

Ada 2 tipe join, yaitu inner join yang lebih menekankan pada keberadaan data yang sama, dan outer join.

Inner Join

Tujuan utama dari inner join adalah menyamakan nilai baris pada sebuah tabel dengan tabel lain menggunakan kolom yang sama (tipe dan panjang [jika ada]). Jika salah satu kolom tidak memiliki kesamaan nilai atau tidak mempunyai nilai sama sekali, maka baris ini tidak akan ditampilkan dalam hasil query. Jadi, masih sama dengan equijoin.

Outer Join

Dalam contoh equijoin, data dengan nama mahasiswa Indah, tidak ada. Hal ini dikarenakan Indah memang tidak mempunyai nilai. Cara yang dapat dipakai untuk menampilkan data Indah tersebut tanpa nilainya adalah dengan menggunakan outer join. Pengubahan sintak join menjadi outer join dapat dilihat pada Kode 1.21:

Kode 1.21 SELECT <select_list>

FROM <tabel1 sebagai kiri>

<LEFT/RIGHT> [OUTER] JOIN

<tabel2 sebagai kanan>

ON <table1.PK = table2.FK> [AND ...];

Dalam sintak tersebut, ada LEFT/RIGHT. Penggunaan LEFT akan memproses seluruh data yang ada pada tabel sebelah kiri kemudian dilanjutkan dengan data yang sama pada tabel sebelah

SQL (sekuel)


kanan. Artinya, seluruh data pada tabel kiri ditampilkan semua, baru setelah itu data yang sama di tabel sebelah kanan, ditampilkan. Untuk data yang tidak ada di tabel sebelah kanan, ditampilkan kata NULL. Sedangkan RIGHT bekerja sebaliknya.

Dengan demikian, outer join fokus pada nilai yang tidak sama antara kolom-kolom yang sama (tipe dan panjang [jika ada]) pada tabel yang di-join-kan. Nilai yang tidak sama, direpresentasikan oleh NULL. Sehingga dari contoh inner join, solusinya adalah mengubah query menjadi seperti berikut:

Kode 1.22 SELECT m.nama, n.kode_mk, n.uas

FROM mahasiswa m LEFT JOIN nilai n

ON m.nim = n.nim;

Hasil:

nama kode_mk Uas

Cinta MK-001 80

Cinta MK-002 0

… … …

Indah NULL NULL

Gala MK-003 0

Gala MK-103 40

Non-Equijoin

Berbeda dengan equijoin yang selalu memakai operator ‘sama dengan’, non-equijoin memakai operator selain ‘sama dengan’. Contoh permasalahan misalnya kita ingin mencari mahasiswa dengan angkatan lebih muda dari Entin. Perhatikan query berikut:

Kode 1.23 SELECT entin.nim, m.nim, m.nama

FROM mahasiswa m, mahasiswa entin

WHERE LEFT(m.nim,2) > LEFT(entin.nim,2)

AND entin.nama='Entin';

SQL (sekuel)


Hasil:

Namaproduk hargapersatuan

Kemeja Lengan Panjang 25000.00

Jepit Rambut 10000.00

Subquery

Subquery merupakan query didalam query. Umumnya, subquery ini dipakai untuk mencari data yang belum diketahui. Penggunaan query didalam query ini umumnya menjadi bagian dari kondisi. Sintak subquery adalah sebagai berikut:

Kode 1.24


FROM <tabel>

WHERE <column> =

(SELECT <single_column>

FROM <tabel>

WHERE <kondisi>);

Namun, tidak menutup kemungkinan penggunaan subquery sebagai bagian dari data. Dalam artian query didalam kata kunci SELECT ataupun didalam kata kunci FROM. Query jenis ini biasa disebut sebagai inline view. Sebagai contoh penggunaan subquery, perhatikan contoh berikut:

Kode 1.25

SELECT nim, nama

FROM mahasiswa

WHERE LEFT(nim,2) =

(SELECT LEFT(nim,2)

FROM mahasiswa

WHERE nama='Entin');

Kode 1.25 merupakan versi subquery dari Kode 1.23. Menghasilkan data yang sama namun dengan solusi yang berbeda. Solusi subquery pada Kode 1.25 mencari terlebih dahulu angkatan si Entin. Hasil dari pencarian ini digunakan sebagai acuan untuk main query.

SQL (sekuel)


SQL Function

Fungsi-fungsi dalam SQL (bukan User Defined Function) lebih dikhususkan pada masing-masing penyedia layanan basis data. Secara umum, fungsi-fungsi dalam SQL Server dapat dibagi menjadi:

1. Aggregation Mengembalikan nilai tunggal yang merepresentasikan sebuah agregasi dari sejumlah nilai. Macam: AVG(), MAX(), COUNT(), MIN(), SUM()

2. Conversion Mengubah nilai dari satu tipe data ke tipe data lain. Selain itu konversi digunakan juga untuk mengaplikasikan karakteristik pada format date, time, dan numeric. Macam: CAST(), CONVERT()

3. String Manipulation Digunakan untuk mengubah, mengganti, dan memanipulasi karakter. Macam: LEN(), SUBSTRING(), UPPER(), LOWER(), LEFT(), RIGHT(), REPLACE()

4. Mathematical Digunakan untuk operasi matematis, mulai dari algebra, trigonometri, statistik, peramalan, dan bahkan operasi finansial. Macam: CEILING(), FLOOR(), ROUND(), POWER(), SQRT()

5. Date and Time Mengubah porsi date dan time, komparasi dan manipulasi nilai-nilai date/time. Macam: GETDATE(), DATEPART(), DATEADD(), DATEDIFF()

Beberapa contoh penggunaan SQL Function dapat dilihat pada Kode 1.26 . CONVERT digunakan untuk mengubah nilai 1000 dari bentuk numeric ke bentuk varchar agar bisa digabungkan dengan kata Rupiah. CEILING, FLOOR, dan ROUND digunakan masing-masing untuk pembulatan keatas, pembulatan kebawah, pembulatan berdasarkan nilai dibelakang koma (sesuai dengan tingkat presisi yg dipakai). GETDATE digunakan untuk mengambil

SQL (sekuel)


tanggal sistem. DATEPART digunakan untuk mengambil nilai bagian tertentu dalam tanggal seperti tahun, bulan, hari, jam, menit. DATEADD digunakan untuk menambahkan nilai tertentu pada bagian tertentu dalam tanggal. Sesuai contoh, DATEADD menambahkan nilai 2 pada tahun sekarang, sehingga nilai yang dihasilkan adalah 2016.

Kode 1.26

SELECT CONVERT(VARCHAR, 1000) + ' Rupiah.';

SELECT LEN(nama),

SUBSTRING(nama, 5, 2),

UPPER(nama),

LOWER(nama),

LEFT(nama, 5),

RIGHT(nama, 5)

FROM mahasiswa;

SELECT CEILING(5.25), FLOOR(5.25),

ROUND(5.25, 0), POWER(5,2), SQRT(25);

SELECT GETDATE(),

DATEPART('yyyy', GETDATE()),

DATEADD('yyyy', 2, GETDATE());

Satu lagi fungsi yang bisa dipakai untuk melakukan conditional expression (untuk menggantikan peran IF..ELSE dalam programming), yaitu CASE. Sintak CASE dapat dilihat pada kode berikut:

Kode 1.27

CASE

WHEN <kondisi> THEN <statement>

[WHEN <kondisi> THEN <statement>]

[WHEN <kondisi> THEN <statement>]

...

ELSE <statement>

END

Pada Kode 1.27, kondisi dituliskan dalam kata kunci WHEN dan apa yang dilakukan (jika kondisi bernilai TRUE) dituliskan dalam kata kunci THEN. Jika ada kondisi lain, dituliskan dalam kata kunci ELSE.

SQL (sekuel)


Masih banyak fungsi-fungsi lain yang diberikan oleh SQL Server untuk membantu dalam pengolahan data.

Transaction

Mungkin lebih umum jika disebut sebagai DML atau Data Manipulation Language. Lebih umum lagi jika disebut dalam bentuk insert, update, delete. Bersama dengan SELECT, transaction secara ilmiah dikenal sebagai CRUD (Create – Read – Update – Delete).

Sederhananya, transaksi adalah sebuah mekanisme untuk memastikan bahwa data masuk sesuai dengan tempatnya. Tentunya dengan beberapa aturan. Aturan-aturan ini didefinisikan secara khusus melalui constraint, relationship, dan tipe data. Dari aturan ini, sistem yang menentukan apakah transaksi sukses atau tidak (dengan mengembalikan nilai kesalahan).

Penambahan data

Sebelum melakukan penambahan data, perlu diperhatikan hal-hal sebagai berikut: Kolom mana yang membutuhkan nilai. Kolom mana yang mempunyai constraint. Kolom mana yang diatur oleh basis data melalui fungsi. Kolom mana yang mempunyai nilai default atau yang

memperbolehkan nilai NULL.

Apa tipe data kolom tujuan.

Untuk menambahkan data, diperlukan insert. Sintak insert dapat dilihat pada Kode 1.28.

Kode 1.28

INSERT INTO <tabel>[(<column_list>)]

VALUES (<value_list>);

Pada Kode 1.29, <column_list> tidak diberikan dengan asumsi bahwa data dimasukkan kedalam seluruh kolom. Untuk menambahkan data dengan mengabaikan beberapa kolom tabel tujuan, bisa mengikuti contoh pada Kode 1.30.

SQL (sekuel)


Kode 1.29

INSERT INTO mahasiswa

VALUES ('08410104003', 'Jenny',

'Jl. Krembangan 50', 'Surabaya',

'W', 'B');

Kode 1.30


(nim, nama)

VALUES ('08410104003', 'Jenny');

Bagaimana jika data yang dimasukkan adalah data dari tabel lain? Untuk menjawab pertanyaan ini, SQL Server menggunakan perintah INSERT INTO..SELECT.

Modifikasi data

Modifikasi data diperlukan ketika terjadi perubahan pada data orisinilnya. Untuk memodifikasi data, dibutuhkan perintah update. Perintah ini dapat memodifikasi satu, banyak, atau semua baris data dalam sebuah tabel. Jumlah data yang dimodifikasi bergantung pada kondisi (kriteria) yang diberikan dalam query update. Sintak update dapat dilihat pada kode berikut:

Kode 1.31

UPDATE <tabel>

SET <kolom1> = <nilai1>

[,<kolom2> = <nilai2>]

WHERE <kondisi1>;

Perhatikan contoh berikut:

Kode 1.32

UPDATE mahasiswa

SET sts_nikah = 'M'

WHERE nim = '08410104003';

Kode 1.32 mencoba memodifikasi data mahasiswa. Modifikasi dilakukan untuk mengubah status nikah mahasiswa, dengan nim 08410104003, dari yang awalnya B menjadi M. Jadi bisa dilihat bahwa nilai yang diberikan pada kata kunci SET merupakan nilai baru.

SQL (sekuel)


Penghapusan data

Ada kalanya data yang sudah ada tidak diperlukan lagi, sehingga data tersebut harus dihapus dari tabel. Untuk menghapus data, digunakan perintah delete. Perintah ini mempunyai perlakuan yang sama seperti update. Yang perlu diperhatikan disini adalah adanya referential constraint. Dalam arti kata lain, data tidak dapat dihapus ketika data tersebut dipakai sebagai data acuan dalam tabel lain (melalui fitur foreign key). Sintak delete dapat dilihat pada kode berikut:

Kode 1.33

DELETE <tabel>

WHERE <kondisi1>;

Contoh sederhananya adalah sebagai berikut:

Kode 1.34

DELETE mahasiswa

WHERE nim = '08410104003';

Kode 1.34 mencoba untuk menghapus data mahasiswa dengan nim 08410104003. Penghapusan bisa sukses jika data mahasiswa tersebut tidak dipakai di tabel Nilai.

SQL (sekuel)


Latihan

1. Tampilkan seluruh data mahasiswa. 2. Tampilkan data mahasiswa yang tinggal di kota Surabaya. 3. Tampilkan nama mahasiswa yang mempunyai huruf a atau

huruf A. 4. Tampilkan nama mahasiswa yang huruf kedua terakhir dari

namanya mempunyai huruf n. 5. Tampilkan nim, nama dan nilai tugas mahasiswa. 6. Tampilkan 3 karakter terakhir dari nama mahasiswa. 7. Buat email berdasarkan nama mahasiswa dan gabungkan

dengan @gmail.com. 8. Hitung jumlah mahasiswa masing-masing dosen. Ambil

datanya dari tabel nilai. 9. Tampilkan nama mahasiswa dan nama dosen untuk dosen

dengan nid 010306. 10. Tampilkan mahasiswa yang mempunyai dosen yang sama

dengan Entin. 11. Tampilkan mahasiswa yang tidak memiliki nilai. 12. Masukkan data mahasiswa berikut ini:

NIM: nim Anda Nama: nama lengkap Anda Alamat: alamat rumah/kos Anda Kota: sesuai alamat Jns_kelamin: jenis kelamin Anda Sts_nikah: M

13. Ubah status nikah Anda dari M menjadi B. 14. Hapus data Anda.

BAB II

Tipe Data dan Variabel

Sub-Materi: Script dan Script Block

Tipe Data Variabel



(halaman kosong)



Bab II


Sub-Materi: Script dan Script Block

Tipe Data Variabel

Jika pada Bab 1 membahas tentang query atau yang disebut sebagai SQL (sekuel), maka mulai Bab 2 sampai dengan Bab 6 materi yang dibahas adalah Procedural Language – Structured Query Language (PL/SQL). PL/SQL merupakan pemutakhiran dari SQL untuk melakukan programming (selayaknya bahasa pemrograman seperti basic, dan java) didalam basis data. Dalam SQL Server, istilah PL/SQL digantikan oleh Transact-SQL atau T-SQL.

Script

Sadar atau tidak, selama pembelajaran pada Pertemuan 1, kita sudah membuat script, yaitu satu statement dalam satu script. Hal yang mengasyikkan disini adalah ketika banyak statement disatukan kedalam sebuah script dengan tujuan tunggal (unified goal). Dan inilah definisi script yang sebenarnya dan yang akan kita gunakan selama pembelajaran.

Karena tujuan tunggalnya, script umum dikatakan sebagai sebuah unit proses. Artinya, seluruh perintah dalam script dijalankan atau tidak sama sekali. Script menggunakan fungsi-fungsi (sistem dan user-defined) serta variabel (sistem dan user-defined) dan control-flow untuk melengkapi proses yang terjadi.

Contoh script dapat dilihat pada Kode 2.1. Pada Kode 2.1 dapat dijelaskan beberapa hal berikut ini, antara lain:

1. @nilaimax adalah variabel. Variabel akan dibahas di sub-bab selanjutnya.



2. MAX(iddepartemen) adalah salah satu system function (aggregate function). Function secara umum dibahas di Bab 1, secara mendetil akan dibahas lebih pada Bab 5.

3. @@ROWCOUNT adalah salah satu system variable. System variable akan dibahas lebih lanjut pada sub bab berikutnya.

Kode 2.1 DECLARE @nilai NUMERIC;

SET @nilai = (SELECT MAX(uas) FROM nilai);

SELECT @@ROWCOUNT;

SELECT @nilai;

Script Block

Sebagai satu kesatuan unit, script membutuhkan wadah yang disebut sebagai script block. Penulisan script block diawali dengan kata kunci BEGIN dan diakhiri dengan kata kunci END. Sehingga dari contoh pada Kode 2.1, diubah menjadi:

Kode 2.2

BEGIN;

DECLARE @nilai NUMERIC;

SET @nilai = (SELECT MAX(uas) FROM nilai);

SELECT @@ROWCOUNT;

SELECT @nilai;

END;

Penggunaan kata kunci BEGIN dan END identik dengan penggunaan kurung kurawal {} pada pemrograman JAVA. Tidak ada aturan dalam cara penulisannya, namun penggunaan block ini akan mempengaruhi bagaimana script dibaca, bagaimana script dijalankan, bagaimana variabel diperlakukan, dan lain sebagainya. Penggunaan block akan lebih intens ketika membahas mengenai control-flow, prosedur, fungsi, dan trigger yang semuanya dibahas pada Prakt. PBD.

Tipe Data

Tipe data merupakan bagian dari variabel yang mempengaruhi perilaku variabel. Dengan tipe data ini, bisa ditentukan nilai apa



yang bisa disimpan didalam variabel tersebut. Dan satu variabel hanya bisa diberi satu tipe data.

Ada lima jenis tipe data sesuai dengan SQL-ANSI 1993 yaitu character string, numeric, temporal, binary, dan boolean. Tidak semua provider basis data memiliki seluruh jenis tipe data. SQL Server sendiri mempunyai beberapa tipe data khusus untuk memudahkan dalam administrasi basis datanya.

Character String

Atribut seperti nama dan alamat direpresentasikan oleh character string. Ada 2 macam tipe data untuk merepresentasikan character string, yaitu:

1. CHARACTER(<panjang>) Atau CHAR(<panjang>) menspesifikasikan karakter dengan panjang yang tetap. Sisa karakter yang tidak terpakai umumnya digantikan oleh padding characters (spasi).

2. CHARACTER VARYING(<panjang>) Atau VARCHAR(<panjang>) menspesifikasikan karakter dengan panjang yang fleksibel dan maksimum sesuai dengan <panjang>.

String diapit oleh single-quotes (cont.: ‘Tegar’). Single-quote didalam string dibentuk menggunakan dua single-quote (cont.: ‘Jum’’at’).

SQL Server juga memiliki tipe data khusus untuk menyimpan karakter UNICODE UCS-2. Tipe data ini antara lain nchar dan nvarchar.

Numeric

Data-data seperti usia dan gaji disimpan dalam bentuk angka. Penyimpanan dalam bentuk angka menggunakan tipe data numeric. Ada empat macam tipe data numeric, yaitu:

1. INT, BIGINT, SMALLINT. SMALLINT mempunyai range ±2^15. INT mempunyai range ±2^31. BIGINT mempunyai range ±2^63.



2. NUMERIC(<panjang>, [<presisi>]), DECIMAL(<panjang>, [<presisi>]) Mempunyai panjang karakter dan presisi (angka belakang koma) dengan range ±10^38. Sebagai contoh: NUMERIC (5,2) mempunyai panjang karakter 5 dengan tingkat presisi 2. Jadi nilai 100,52 bisa disimpan tetapi tidak untuk 1000,1. Nilai 0,00001 disimpan menjadi 0,00.

3. REAL. Tingkat presisi tinggi dengan range -3,40E+38 s.d -1,18E-38 dan +1,18E-38 s.d +3,40E+38.

4. FLOAT. Tingkat presisi tinggi dengan range -1,79E+308 s.d -2,23E-308; 0; +2,23E+308 s.d +1,79E+308.

Temporal

Temporal merupakan tipe data yang menyimpan tanggal dan waktu yang disesuikan dengan system-timezone (komputer). Sebagai contoh data temporal adalah data tentang tanggal lahir. Ada dua macam tipe data temporal, yaitu:

1. DATETIME. Tipe data ini menyimpan informasi tanggal, waktu atau bahkan keduanya. Dalam SQL Server, tipe data ini menyimpan dengan tingkat akurasi sampai 3,33 milidetik. Sedangkan untuk SMALLDATETIME hanya sampai 1 menit. Dalam tipe data ini, juga terdapat tipe data TIMESTAMP dengan tingkat akurasi sampai dengan 9 digit.

2. INTERVAL. Umumnya digunakan untuk menyimpan periode seperti garansi. Ada 2 macam yaitu (1) YEAR-MONTH dan (2) DAY-TIME. SQL Server tidak mempunyai tipe data ini.

Binary

Sebenarnya semua data dalam komputer disimpan dalam bentuk biner, tipe data ini didesain untuk menyimpan dijit biner secara sekuensial. Ada tiga macam tipe data biner, yaitu:

1. BIT(<panjang>) Dikenal sebagai BINARY dalam SQL Server. Panjang maksimum yang bisa diatasi oleh tipe data ini adalah 8.000 bytes.



2. BIT VARYING(<panjang>) Dikenal sebagai VARBINARY dalam SQL Server. Panjang maksimum yang bisa diatasi oleh tipe data ini adalah 8.000 bytes.

3. BINARY LARGE OBJECT(<panjang>) Dikenal sebagai VARBINARY (MAX) dalam SQL Server. Panjang maksimum yang bisa diatas oleh tipe data ini adalah 2^31 bytes, sekitar 2 GB (gigabytes).

Biasanya tipe data ini digunakan untuk menyimpan file, mulai dari file dokumen sampai dengan file multimedia. SQL Server mempunyai satu lagi tipe data khusus untuk menyimpan file multimedia yaitu tipe data IMAGE yang dapat menyimpan hingga 2.147.483.647 bytes.

Boolean

Nilai kebenaran dalam SQL direpresentasikan oleh tipe data boolean. Ada tiga macam nilai dalam boolean yaitu: true, false, dan unknown. SQL Server tidak mempunyai tipe data semacam ini. Untuk menggantikan tipe data ini, umumnya para software developer mengganti nilai true dengan angka 1, dan false/unknown dengan angka 0. Sehingga bisa menggunakan tipe data numeric.

Other

Beberapa tipe data lainnya adalah cursor, table, dan xml. Cursor merupakan obyek basis data yang dipakai untuk memanipulasi data dalam basis row-by-row (cursor dibahas lebih detil pada bab lain). Table merupakan obyek basis data untuk menyimpan data dalam bentuk tabular yang umumnya dipakai untuk return value dari table-valued function. Xml digunakan untuk menyimpan dokumen berformat xml.



Variabel

Fungsi utamanya adalah sebagai penyimpan data untuk sementara. Dalam dunia programming, variabel digunakan untuk menyimpan nilai dan sebagai referensi dalam proses.

Deklarasi Variabel

Cara membuat variabel adalah dengan melakukan deklarasi variabel. Setiap bahasa pemrograman mempunyai cara yang berbeda. Dalam SQL cara pembuatan variabel adalah dengan menuliskan kata kunci DECLARE diikuti dengan nama variabel (sebagai identitas) lalu tipe data (sebagai bentuk nilai yang dapat disimpan oleh variabel tersebut). Sebagai pemisah antar variabel, haurs menggunakan koma. Sintak pembuatan variabel dapat dilihat pada kode berikut: DECLARE

Kode 2.3 DECLARE @<nama_variabel> <tipe_data>[,

@<nama_variabel> <tipe_data>[,

@<nama_variabel> <tipe_data>]];

Dengan melakukan deklarasi seperti pada DECLARE

Kode 2.3, nilai variabel adalah NULL, apapun tipe datanya, sampai variabel tersebut diberi nilai. Penggunaan DECLARE dapat juga diulang untuk setiap variabel. Sehingga DECLARE

Kode 2.3 bisa diubah menjadi:

Kode 2.4

DECLARE @<nama_variabel> <tipe_data>;



Contoh:

Kode 2.5

DECLARE @jumlah NUMERIC (18, 0);

DECLARE @nama VARCHAR (50);

DECLARE @tgl DATETIME;

DECLARE @gambar IMAGE;

Pada Kode 2.5, ada empat variabel yang dibentuk yaitu:



1. Variabel @jumlah bertipe data NUMERIK dengan panjang 18 dan tingkat presisi 0.

2. Variabel @nama bertipe data VARCHAR dengan panjang 50. 3. Variabel @tgl bertipe data DATETIME. Tanpa panjang

karena memang tidak diperlukan. 4. Variabel @gambar bertipe data IMAGE. Tanpa panjang

karena sudah memiliki standar panjang maksimum.

Variabel yang dideklarasikan sendiri merupakan user-defined variable. Sebaliknya, variabel yang dibuat oleh sistem dinamakan system variable. Yang perlu diperhatikan disini adalah penulisan nama variabel yang mempunyai aturan yang berbeda dimasing-masing provider basis data. Untuk SQL Server, menggunakan @<nama_variabel>. Sedangkan untuk system variable menggunakan @@ sebelum nama variabelnya. Lebih lengkap mengenai system variables akan dijelaskan pada sub-bab System Variable.

Memberi Nilai pada Variabel

Ada dua cara dalam memberikan nilai pada variabel, yaitu:

1. SET 2. SELECT

Tidak menutup kemungkinan jika pemberian nilai variabel dilakukan ketika deklarasi, namun penggunaan ini hanya bisa dilakukan dalam prosedur. Sehingga untuk pemakaian dalam script pada umumnya, pemberian nilai ini harus dilakukan setelah deklarasi variabel.

Dari contoh pada Kode 2.6, beberapa hal bisa dijelaskan sebagai berikut:

1. Penggunaan kata kunci SET hanya untuk pemberian nilai pada variabel, secara sederhana. Dalam artian, nilai yang diberikan telah diketahui sebelumnya.

2. Kata kunci SELECT hanya digunakan ketika pengisian variabel menggunakan nilai hasil dari query. Dengan



demikian, dalam satu query dapat dilakukan pemberian nilai pada beberapa variabel sekaligus.

3. Berbeda dengan SELECT pada baris ke-8. Untuk mengetahui fungsi SELECT disini, lihat kembali pembahasan pada Bab I. Dapat dikatakan bahwa penggunaan kata kunci SELECT pada baris ini adalah untuk menampilkan data (dari literal_value).

4. PRINT digunakan untuk mencetak tulisan. 5. Beberapa provider basis data memberikan fitur berupa

variable scope. Dalam fitur ini, variabel dapat menjadi variabel lokal atau global dalam ruang lingkup script block. Dalam contoh tersebut, variabel @HARGATERTINGGI merupakan variabel lokal dalam sub-script block (baris 15 s.d baris 21). Sedangkan @VAR bisa dipakai di lokasi manapun dalam script tersebut.

Kode 2.6 BEGIN

DECLARE @var VARCHAR(50);

SET @var = 'STIKOM SURABAYA';

DECLARE @jumlahbaris NUMERIC;

SET @jumlahbaris = (SELECT COUNT(*)

FROM mahasiswa);

SELECT 'Jumlah baris = ' + CONVERT (VARCHAR,

@jumlahbaris);

SET @jumlahbaris = @jumlahbaris + 1;

PRINT 'Data selanjutnya = ' + CONVERT

(VARCHAR, @jumlahbaris);

BEGIN

DECLARE @maxuas NUMERIC;

SELECT @maxuas = MAX(uas) FROM nilai;

PRINT 'UAS tertinggi = ' + CONVERT

(VARCHAR, @maxuas);

END;

END;

System Variable

Variabel ini menyimpan informasi proses yang sedang berjalan dan obyek-obyeknya. Namun, tidak semua provider basis data



menyebutnya demikian. Misalnya SQL Server sekarang lebih menyebutnya sebagai system function daripada predesesornya. Variabel jenis ini bersifat global, artinya dapat dipanggil kapanpun dan oleh siapapun.

Dalam SQL Server, system variable ditandai dengan @@<nama_variabel>, sedangkan untuk Oracle ditandai dengan %<nama_variabel>. Dan pemakaian variabel ini hanya terjadi pada saat-saat spesifik saja.

Beberapa system variables yang sering dipakai, antara lain:

1. @@ROWCOUNT Digunakan untuk menghitung jumlah baris yang dikembalikan oleh query.

2. @@IDENTITY Digunakan untuk mengambil nilai identity dari kolom (field) dengan properti isIdentity = TRUE. Umumnya hal ini dipakai untuk auto increment.

3. @@ERROR Digunakan untuk mengambil nilai error. Jika tidak terdapat error, nilainya adalah 0.

4. @@VERSION Digunakan untuk mengambil versi dari SQL Server beserta tanggal, prosesor, dan arsitektur OS-nya.

Dalam contoh Kode 2.6, ada variabel @JUMLAHBARIS yang digunakan untuk menghitung jumlah baris data yang dikembalikan oleh query. Kode panjang dan kompleks tersebut dapat disederhanakan menggunakan bantuan system variables. Sehingga kode program akan tampak sebagai berikut:

Kode 2.7 SELECT *

FROM mahasiswa;

PRINT 'Jumlah baris = ' + CONVERT (VARCHAR,

@@ROWCOUNT);

@@ROWCOUNT menampung jumlah data hasil dari perintah SELECT * FROM mahasiswa. Yang perlu diperhatikan disini



adalah @@ROWCOUNT hanya menampung sintak query satu statement sebelum pemakaiannya.

Operasi pada Variabel

Secara umum, operasi-operasi ini bisa dilakukan pada variabel, antara lain:

1. Operasi aritmatika. Operasi ini berupa penambahan, pengurangan, perkalian, dan pembagian. Operator aritmatika dan contoh penggunaannya dapat dilihat pada Bab 1.

2. Operasi konkatenasi. Operasi ini berupa penggabungan karakter. Untuk menggabungkan karakter, bisa dibaca kembali Bab 1.

3. Operasi logika. Operasi ini berupa pemakaian variabel untuk kondisi dalam percabangan. Secara query sederhana, materi ini pernah dibahas di Bab 1. Namun untuk percabangan dalam T-SQL akan dibahas pada Bab 3.



Latihan

1. Buat script untuk melakukan masing-masing aritmatika berikut ini: a. Penjumlahan b. Pengurangan

2. Buat variabel untuk menampung alamat dan kota. Lalu tampilkan kedua nilai variabel tersebut dalam sebuah kalimat. Contoh: Alamat: Jl. A. Yani 56 A Kota: Surabaya PRINT: Jl. A. Yani 56 A Surabaya

3. Buat variabel untuk menampung seluruh field dari tabel mahasiswa.

4. Isi variabel yang dibentuk di no.3 dengan data mahasiswa dengan nim 06390102666. Lalu tampilkan seluruh datanya. Cara menampilkan: NIM: 06390102666 Nama: Entin Alamat: Jl. Yos Sudarso 1 Kota: Sidoarjo Jns_kelamin: Wanita (W) Sts_nikah: Belum Menikah (B)

5. Buat variabel untuk menampung data-data berikut lalu masukkan datanya kedalam tabel karyawan. NIM: nim Anda Nama: nama lengkap Anda Alamat: alamat rumah/kos Anda Kota: sesuai alamat Jns_kelamin: jenis kelamin Anda Sts_nikah: M



(halaman kosong)

BAB III

Control-flow Statement

Sub-Materi: Percabangan Perulangan



(halaman kosong)



Bab III


Sub-Materi: Percabangan Perulangan

Alur proses, dalam setiap bahasa pemrograman, adalah sebuah keharusan. Setiap perubahan kontrol terhadap alur proses dapat mempengaruhi output. T-SQL menyediakan beberapa macam cara untuk mengontrol alur proses antara lain (1) IF..ELSE, (2) WHILE, (3) GOTO dan WAITFOR, serta (4) TRY..CATCH. Dalam matakuliah Praktikum PBD, hanya dibahas mengenai percabangan menggunakan IF..ELSE, dan perulangan menggunakan WHILE.

Percabangan

Secara analogi dalam kehidupan sehari-hari, percabangan dapat dilihat ketika seseorang berjalan dan bertemu dengan persimpangan. Disini, ada beberapa pilihan yang bisa dibuat, apakah belok ke kanan, ke kiri, atau lurus? Ada juga pertimbangan-pertimbangan seperti, apa-kah lebih jauh, jalannya rusak, atau yang lainnya dalam setiap pilihan jalan.

Pembuatan program tidak hanya membuat terdiri dari statement yang dijalankan secara urut dari baris pertama sampai terakhir. Ada beberapa statement yang mungkin membutuhkan kondisi tertentu. Kondisi ini didalam program disebut sebagai percabangan.

Dalam T-SQL, hanya ada satu kata kunci untuk percabangan, yaitu IF..ELSE. Cara penulisannya hampir sama seperti cara menuliskan IF..ELSE didalam bahasa pemrograman Visual



Basic .NET, walaupun sebenarnya implementasinya lebih banyak mengikuti bahasa pemrograman C. Sintak dasar IF..ELSE dapat dilihat pada Kode 3.1:

Kode 3.1

IF <kondisi boolean>

SQL | <script block>

[ELSE [IF <kondisi boolean>]

SQL | <script block>]

Setiap percabangan membutuhkan kondisi. Dalam arti kata lain, setiap kata kunci IF diikuti oleh kondisi. Kondisi ini adalah kondisi boolean. Penulisan kondisi boolean pada IF sama seperti penulisan kondisi pada WHERE atau HAVING (SQL).

Ada 2 nilai yang dihasilkan oleh kondisi boolean, yaitu terpenuhi dan tidak terpenuhi. Jika kondisi IF terpenuhi atau bernilai TRUE, maka SQL Server akan menjalankan sebuah perintah SQL atau menjalankan script block (jika banyak perintah) didalam blok IF. Dengan terpenuhinya kondisi, maka program tidak akan menjalankan blok ELSE. Namun jika kondisi IF tidak dipenuhi atau bernilai FALSE, maka SQL Server menjalankan script block didalam blok ELSE. Untuk lebih jelasnya, bisa diperhatikan pada Gambar 3.1:

Gambar 3.1 Alur IF … ELSE

Sebagai contoh, dosen ingin melakukan penyesuaian nilai UAS dengan ketentuan jika bulan ganjil, maka penambahan 20 poin.



Namun jika bulan genap, maka penambahan hanya 10 poin. Hasil query dapat dilihat pada Kode 3.2:

Kode 3.2 DECLARE @poin INT;

IF MONTH(GETDATE())%2=1

SET @poin=20;

ELSE

SET @poin=10;

UPDATE mahasiswa

SET uas=uas+@poin;

Dalam query tersebut, kondisi boolean yang dipakai adalah ganjil atau genapnya bulan, yaitu dengan melihat hasil sisa bagi antara nomor urut bulan dan nilai 2. Jika sisa bagi adalah 1, berarti bulan ganjil. Sehingga variabel @poin diisi dengan nilai 20. Begitu pula sebaliknya untuk sisa bagi 0 atau bulan genap.

Macam-macam Bentuk Percabangan

Percabangan dapat berupa percabangan tunggal. Percabangan ini hanya terdiri dari kata kunci IF saja. Perhatikan contoh berikut:

Kode 3.3

IF YEAR(GETDATE())%4 = 0

BEGIN

PRINT 'Tahun Kabisat';

PRINT '-- hanya bisa dibagi 4';

END;

Pada Kode 3.3, program melakukan pengecekan apakah tahun ini tahun kabisat. Jika tahun ini bisa dibagi 4 (sisa bagi 0), maka program menjalankan perintah didalam blok IF, yaitu (1) mencetak kalimat Tahun Kabisat, dan (2) mencetak kalimat --hanya bisa dibagi 4.

Kondisi boolean dalam percabangan, sama seperti kata kunci WHERE, bisa lebih dari satu kondisi. Perhatikan kode program pada contoh berikut:



Kode 3.4

IF YEAR(GETDATE())%4 = 0 AND

YEAR(GETDATE())%200 != 0

BEGIN


PRINT '-- hanya bisa dibagi 4';

END;

Pada Kode 3.4, program melakukan pengecekan terhadap dua hal, yaitu (1) apakah tahun bisa dibagi 4, dan (2) apakah tahun tidak bisa dibagi 200. Jika kedua kondisi ini terpenuhi, dalam arti hasil operasi AND diantara keduanya bernilai TRUE, maka program akan menjalankan script block.

Selain operasi AND, dapat juga menggunakan operasi OR atau NOT didalam kondisi. Lebih lengkap mengenai kondisi dan operator kondisi, dapat dibaca kembali pada Bab I.

Selain percabangan tunggal, hanya terdiri dari sebuah IF, percabangan juga dapat dilakukan dalam bentuk majemuk. Percabangan jenis ini dapat dilihat kembali pada Kode 3.2.

Jenis lain dari percabangan adalah percabangan bertingkat. Jenis ini mempunyai kompleksitas paling tinggi diantara percabangan lainnya. Ciri percabangan bertingkat adalah adanya percabangan didalam percabangan. Perhatikan contoh berikut:

Kode 3.5

IF YEAR(GETDATE())%4 = 0

BEGIN

PRINT 'PERCABANGAN UTAMA';

IF YEAR(GETDATE())%200 != 0

BEGIN

PRINT 'SUB PERCABANGAN';

PRINT '---------------';


PRINT '-- bisa dibagi 4';

PRINT '-- tidak habis dibagi 200';

END;

END;

Pada Kode 3.5, program melakukan pengecekan apakah bulan habis dibagi 4. Jika hasilnya TRUE, maka program akan:



1. Mencetak PERCABANGAN UTAMA 2. Melakukan pengecekan apakah bulan tidak habis dibagi 200.

Jika dari statement ini bernilai TRUE, maka program akan menampilkan kalimat:

SUB PERCABANGAN

---------------------

Tahun Kabisat

-- bisa dibagi 4

-- tidak habis dibagi 200

Sehingga hasil keseluruhan jika semua kondisi bernilai TRUE, adalah:

Kode 3.6 Hasil jika semua TRUE

PERCABANGAN UTAMA

SUB PERCABANGAN

--------------------------

Tahun Kabisat

-- bisa dibagi 4

-- tidak habis dibagi 200

Jika hanya percabangan utama yang bernilai TRUE, maka hasilnya hanyalah kalimat PERCABANGAN UTAMA.

Perulangan

Ada saatnya kode program mengharuskan programmer untuk menulis kode yang sama berulang-ulang. Misalnya, untuk mencetak angka dari 1 sampai dengan 10. Solusi sederhana untuk masalah ini adalah dengan mengetikkan kode program berikut ini:

Kode 3.7

PRINT '1';

PRINT '2';

PRINT '3';

PRINT '4';

...

PRINT '10';

Kode program pada Kode 3.7 mudah dilakukan tetapi kurang efisien. Efisiensi akan terlihat ketika yang diproses lebih banyak, misal mencetak angka dari 1 sampai dengan 1000.



Untuk dapat mencapai efisiensi dalam pengerjaan kode program, ada sebuah konsep yang disebut dengan perulangan (looping). Dengan perulangan ini, kode program bisa ditulis sekali dan menghasilkan (sesuai contoh pada Kode 3.7) angka 1 sampai dengan 10.

Dalam T-SQL, hanya ada sebuah cara untuk melakukan perulangan yaitu menggunakan kata kunci WHILE. Sintak dasar WHILE dapat dilihat pada kode program berikut ini:

Kode 3.8

WHILE <kondisi boolean>

BEGIN

<script1>

[BREAK]

<script2>

[CONTINUE]

<script3>

END;

Cara kerja WHILE, sesuai dengan Kode 3.8, adalah melakukan pengecekan terlebih dahulu pada kondisi boolean. Selama kondisi bernilai TRUE, maka blok WHILE akan dijalankan. Dan sebaliknya, ketika kondisi bernilai FALSE, maka blok WHILE tidak akan dijalankan. Secara sederhana, alur kerja perulangan dapat dilihat pada Gambar 3.2.

Dalam menjalankan blok WHILE, program melakukan pengecekan terhadap dua buah kata kunci, yaitu (1) BREAK, dan (2) CONTINUE. Kata kunci BREAK digunakan untuk keluar dari perulangan tanpa harus menunggu seluruh blok dijalankan. Hal ini bisa dilakukan dengan menambahkan kondisi sebelum kata kunci BREAK. Dari Kode 3.8, dengan munculnya BREAK, maka <script2> dan <script3> tidak akan dijalankan.

Sedangkan kata kunci CONTINUE digunakan untuk mengembalikan perulangan ke posisi awal blok <script1>. Kata kunci CONTINUE umumnya digunakan untuk re-evaluasi kondisi. Hal ini bisa dilakukan dengan menambahkan kondisi sebelum kata kunci CONTINUE. Dari Kode 3.8, dengan munculnya CONTINUE, maka <script3> tidak akan dijalankan.



Gambar 3.2 Alur WHILE

Perhatikan contoh kode program berikut ini:

Kode 3.9 DECLARE @counter INT;

SET @counter=1;

WHILE @counter<=10

BEGIN

PRINT 'Data ke-'+CONVERT(VARCHAR,@counter);

SET @counter=@counter+1;

END;

Pada Kode 3.9, terdapat sebuah variabel @counter yang dipakai sebagai acuan posisi perulangan. Karena fungsinya, maka @counter diberi nilai 1 dan akan bertambah (1 demi 1) didalam blok WHILE.

Urutan hasil proses dari Kode 3.9 adalah sebagai berikut:

Tabel 3.1 Cara Kerja Perulangan

Nilai @counter Hasil PRINT (didalam blok WHILE)

1 Data ke-1 Nilai @counter = 2




Nilai @counter Hasil PRINT (didalam blok WHILE)


... ...


11 -

Sampai pada nilai @counter 10, kode program didalam blok WHILE mengubah nilai @counter menjadi 11. Ketika dilakukan pengecekan (kondisi boolean) pada kata kunci WHILE, nilai @counter = 11 menghasilkan nilai FALSE. Dari hasil ini, program tidak menjalankan blok WHILE dan mengakhiri perulangan. Sehingga hasil dari Kode 3.9 adalah:

Kode 3.10 Hasil perulangan Data ke-1

Data ke-2

Data ke-3

Data ke-4

...

Data ke-10

Dari Kode 3.10, dapat dilihat bahwa hasil query antara Kode 3.7 dan Kode 3.9 adalah sama. Dengan demikian, dapat diambil kesimpulan bahwa perulangan mampu mengefisiensikan penggunaan kode program.

Untuk penggunaan BREAK dan CONTINUE, perhatikan contoh pada Kode 3.11. Pada contoh tersebut, terdapat variabel @counter yang dipakai sebagai alat bantu perulangan. Perulangan di kondisikan berhenti pada nilai @counter 10. Selama @counter bernilai kurang dari 10, maka ada penambahan nilai +1 pada @counter. Ketika nilai @counter kurang dari 5, program tidak melakukan apa-apa. Hal ini dikarenakan ada kondisi IF @counter < 5. Kondisi ini berisi kata kunci CONTINUE. Ketika nilai @counter diantara 5 sampai dengan 8, maka program melakukan pencetakan nilai @counter.



Hal ini bisa dilihat pada kata kunci ELSE. Ketika nilai @counter diatas 8, program menghentikan perulangan.

Kode 3.11 DECLARE @counter INT;

SET @counter = 0;

WHILE @counter < 10

BEGIN

SET @counter = @counter + 1;

IF @counter < 5

BEGIN

CONTINUE;

PRINT 'Tidak dicetak';

END;

ELSE IF @counter > 8

BREAK;

ELSE

PRINT 'Nilai @counter = ' +

convert(varchar, @counter);

END;

Cara membaca eksekusi Kode 3.11, adalah sebagai berikut:

Tabel 3.2

Nilai @counter Hasil WHILE

0 Nilai @counter = 1 Kondisi @counter < 5 terpenuhi CONTINUE : Program kembali ke baris 1 blok WHILE

1 Nilai @counter = 2 Kondisi @counter < 5 terpenuhi CONTINUE : Program kembali ke baris 1 blok WHILE

… …

4 Nilai @counter = 5 Kondisi @counter < 5 tidak terpenuhi Kondisi @counter > 8 tidak terpenuhi Program mencetak nilai @counter = 5

5 Nilai @counter = 6 Kondisi @counter < 5 tidak terpenuhi Kondisi @counter > 8 tidak terpenuhi Program mencetak nilai @counter = 6

… …

8 Nilai @counter = 9 Kondisi @counter < 5 tidak terpenuhi



Nilai @counter Hasil WHILE

Kondisi @counter > 8 terpenuhi BREAK : Program menghentikan perulangan

Sehingga hasil dari Kode 3.11 adalah sebagai berikut:

Kode 3.12 Hasil perulangan

Nilai @counter = 5

Nilai @counter = 6

Nilai @counter = 7

Nilai @counter = 8

Sebagai tambahan, WHILE juga digunakan untuk melakukan pembacaan data pada cursor. Materi tentang cursor akan dibahas lebih detil pada Bab 4.



Latihan

1. Cetak nim mahasiswa mulai dari no urut 1001 sampai dengan 2000. Ketentuan pembuatan nim adalah sebagai berikut: a. Panjang karakter: 11 digit angka b. 2 angka depan = tahun. Contoh: 2014 menjadi 14 c. 5 angka setelahnya = jurusan. Contoh: 41010 d. 4 angka terakhir = no urut.

2. No urut NIM seharusnya dimulai dari angka 1. Oleh karena itu, ubah no urut pada latihan no.1 menjadi 1 sampai dengan 1000. Contoh hasil akhir: 14410100008

3. Dari 1000 data nim yang telah dibentuk, masukkan datanya kedalam tabel mahasiswa. Untuk kolom lain, berikan data dummy.



(halaman kosong)

BAB IV

Cursor

Sub-Materi: Cursor

Cursor


(halaman kosong)

Cursor


Bab IV

Cursor

Sub-Materi: Cursor

Sebuah query mengembalikan sekumpulan baris yang biasa disebut sebagai resultset. Dalam beberapa studi kasus, program harus memproses satu baris data dalam satu waktu. Sebagai contoh, bagaimana cara untuk membuat keputusan kelulusan pada

data mahasiswa yang diukur dari banyak parameter kelulusan. Untuk permasalahan tersebut, cursor adalah solusi yang tepat. Cursor merupakan sebuah pointer yang dipakai untuk menunjuk posisi tertentu (data) dalam sebuah resultset. Karena cursor bergantung pada recordset, maka setiap perubahan data pada tabel aslinya tidak akan berpengaruh terhadap isi data cursor.

Membuat dan Menggunakan Cursor

Gambar 4.1 Alur penggunaan cursor

Seperti yang terlihat pada Gambar 4.1, ada 4 proses dalam menggunakan Cursor, yaitu:

1. Deklarasi cursor (DECLARE) Pendeklarasian cursor berisi nama cursor dan perintah select yang dipakai dalam cursor tersebut. Karena cursor

Cursor


merupakan variabel khusus, nama cursor tidak diikuti oleh tanda @ seperti pada variabel-variabel bertipe data scalar (seperti VARCHAR, NUMERIC, DATETIME). Sintak pendeklarasian cursor dapat dilihat pada Kode 4.1.

Kode 4.1

DECLARE <nama cursor> CURSOR FOR

<perintah SELECT>;

Contoh sederhana, membuat cursor cMhs untuk menampung seluruh data mahasiswa. Kode program untuk contoh ini dapat dilihat pada Kode 4.2.

Kode 4.2

DECLARE cMhs CURSOR FOR

SELECT *

FROM mahasiswa;

2. Membuka cursor (OPEN) Dengan membuka cursor, kita menjalankan perintah select dalam cursor (mempopulasikan cursor) dan menempatkan cursor pada posisi baris pertama. Populasi ini disimpan secara temporer didalam memory. Sintak untuk membuka cursor dapat dilihat pada Kode 4.3.

Kode 4.3

OPEN <nama cursor>;

Mengambil contoh dari cMhs, maka untuk membuka cursor cMhs dapat dilakukan seperti pada Kode 4.4.

Kode 4.4

OPEN cMhs;

3. Membaca data setiap baris dalam cursor (FETCH NEXT) Setelah data dipopulasikan kedalam cursor, langkah selanjutnya adalah mengambil setiap baris data menggunakan FETCH NEXT. Sintak untuk pembacaan data dapat dilihat pada Kode 4.5.

Kode 4.5

FETCH NEXT FROM <nama cursor>

INTO <target-list>;

Cursor


Target-list merupakan kumpulan variabel yang dipakai untuk menyimpan data masing-masing kolom dalam cursor. Untuk membaca seluruh baris data, dibutuhkan perulangan. Kondisi yang dipakai dalam perulangan diambil dari nilai sebuah variabel global yaitu @@FETCH_STATUS. Jika baris mempunyai data, maka @@FETCH_STATUS mengembalikan nilai 0. @@FETCH_STATUS bernilai -1 jika tidak ada lagi baris yang dapat dibaca. Penggunaan perulangan dengan @@FETCH_STATUS dapat dilihat pada Kode 4.6.

Kode 4.6

WHILE @@FETCH_STATUS=0

BEGIN

...

END;

Menggunakan contoh cMhs, maka untuk membaca seluruh data cMhs serta mencetak nim dan nama mahasiswa, dapat dilihat pada Kode 4.7.

Kode 4.7

FETCH NEXT FROM cMhs INTO

@nim, @nama, @alamat, @kota, @jk, @st;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @nim + ' ' + @nama;

FETCH NEXT FROM cMhs INTO @nim, @nama,

@alamat, @kota, @jk, @st;

END;

4. Menutup cursor (CLOSE) Setelah seluruh data selesai dibaca, sangat penting untuk membersihkan cursor. Untuk melakukan ini, dibutuhkan dua kata kunci yaitu CLOSE untuk menutup cursor dan DEALLOCATE untuk menghapus memory yang digunakan oleh cursor. Sintak untuk menutup cursor dapat dilihat pada Kode 4.8.

Kode 4.8

CLOSE <nama cursor>;

DEALLOCATE <nama cursor>;

Cursor


Menggunakan contoh cMhs, maka untuk menutup cursor cMhs dapat dilihat pada Kode 4.9.

Kode 4.9

CLOSE cMhs;

DEALLOCATE cMhs;

Contoh Penggunaan Cursor

Dari contoh cMhs, keseluruhan kode program akan menjadi seperti berikut ini:

Kode 4.10

-- 1

DECLARE CURSOR cMhs FOR

SELECT *

FROM mahasiswa;

-- 2

OPEN cMhs;

-- 3

DECLARE @nim CHAR(11),

@nama VARCHAR(100),

@alamat VARCHAR(100),

@kota VARCHAR(50),

@jk CHAR(1),

@st CHAR(1);




BEGIN

PRINT @nim + ' ' + @nama;



END;

-- 4

CLOSE cMhs;

DEALLOCATE cMhs;

Cursor


Hasil dari Kode 4.10 adalah sebagai berikut:

05390102208 Cinta

05410104001 Alif

05410104190 Bambang

06390102666 Entin

06390102880 Feri

06410104007 Desi

07390102802 Henny

07390102900 Indah

07410104800 Gala

Dari contoh tersebut, dapat dilihat bahwa ruang kerja (tempat dimana proses sesungguhnya terjadi) berada didalam blok WHILE. Berbagai macam query maupun T-SQL dapat dituliskan disini. Dan bahkan pembuatan cursor lainnya. Kehadiran cursor dalam cursor memberikan fleksibilitas dan skalabilitas dalam pengolahan data. Cursor dalam cursor umum disebut sebagai nested-cursor.

Implicit Cursor

Cursor yang telah dibahas pada sub-bab sebelumnya disebut sebagai expicit cursor. Disebut eksplisit karena cursor tipe ini menyediakan kemampuan processing yang tidak dapat dilakukan oleh tipe cursor yang lain. Karena menyediakan kontrol lebih banyak, tipe eksplisit membutuhkan langkah-langkah yang lebih rumit dalam pengoperasiannya.

Implicit cursor merupakan cursor yang dibentuk secara otomatis oleh perintah DML dan perintah select yang menghasilkan satu baris data. Cursor tipe ini tidak dapat dibuat secara manual.

Nested Cursor

Nested cursor digunakan untuk memproses data dalam struktur yang lebih kompleks. Contoh sederhana implementasi nested cursor adalah untuk pembuatan laporan. Cursor ini mempunyai dua buah cursor, yaitu outer cursor dan inner cursor. Inner cursor di deklarasikan, dibuka, dibaca, dan ditutup didalam blok WHILE outer cursor.

Cursor


Agar lebih jelas implementasinya, sebagai contoh, kepala bagian AAK ingin melihat laporan transkrip mahasiswa. Sebagai solusi, perhatikan script berikut ini:

Kode 4.11

DECLARE cMhs CURSOR FOR

SELECT nim, nama

FROM mahasiswa;

OPEN cMhs;

DECLARE @nim CHAR(11), @nama VARCHAR(100);

FETCH NEXT FROM cMhs INTO @nim, @nama;


BEGIN

PRINT 'Transkrip Mahasiswa';

PRINT '-----------------------------------';

PRINT 'NIM: ' + @nim;

PRINT 'Nama: ' + @nama;

DECLARE cNilai CURSOR FOR

SELECT nama, tugas*0.4+uts*0.3+uas*0.3,

CASE

WHEN tugas*0.4+uts*0.3+uas*0.3 < 65

THEN 'Gagal' ELSE 'Lulus'

END

FROM nilai n JOIN mk

ON n.kode_mk = mk.kode_mk

WHERE nim = @nim;

OPEN cNilai;

DECLARE @namamk VARCHAR(100),

@na NUMERIC(18,2),

@status VARCHAR(10);

DECLARE @counter INT;

SET @counter = 1;

FETCH NEXT FROM cNilai INTO @namamk, @na,

@status;


BEGIN

PRINT CONVERT(VARCHAR,@counter) + '. ' +

@namamk + ' dgn NA = ' +

CONVERT(VARCHAR, @na) +

' --> (' + @status + ')';

Cursor


SET @counter = @counter + 1;

FETCH NEXT FROM cNilai INTO @namamk, @na,

@status;

END;

CLOSE cNilai;

DEALLOCATE cNilai;

PRINT '-----------------------------------';

PRINT '';

FETCH NEXT FROM cMhs INTO @nim, @nama;

END;

CLOSE cMhs;

DEALLOCATE cMhs;

Cursor cMhs telah dibuat sebelumnya. Didalam blok WHILE cursor cMHS, dibuat cursor baru dengan nama cNilai yang berisi nama MK, nilai akhir, dan status kelulusan untuk setiap mahasiswa. Ya, nilai akhir yang diproses adalah untuk setiap mahasiswa karena perulangan yang terjadi di cMhs adalah untuk setiap mahasiswa.

Untuk pencetakan data, ada pembedaan antara data mahasiswa dengan data nilai. Data mahasiswa harus dicetak didalam blok WHILE cursor cMhs, sedangkan data nilai harus dicetak didalam blok WHILE cursor cNilai.

Bisa dilihat dari kode tersebut, bahwa blok pembuatan cursor merupakan satu kesatuan agar cursor tersebut dapat dieksekusi dengan baik oleh program. Oleh karena itu, pembuatan cursor cNilai, didalam cMhs, harus seluruhnya (sampai dengan dealokasi memory cNilai) sebelum cMhs melakukan pembacaan data kembali (FETCH NEXT).

Perhatikan juga pembuatan dan penggunaan variabel. Setiap variabel yang dipakai, dibuat secara lokal untuk memudahkan pemeliharaan kode program. Ya, memang hanya untuk memudahkan saja karena nilai variabel akan selalu berubah setiap cursor melakukan pembacaan data.

Hasil dari Kode 4.11 (penggalan) adalah sebagai berikut:

Cursor


Kode 4.12

Transkrip Mahasiswa

---------------------------------------------

NIM: 05390102208

Nama: Cinta

1. Sistem Basis Data dgn NA = 52.00 -->

(Gagal)

2. Bahasa Pemrograman dgn NA = 47.00 -->

(Gagal)

---------------------------------------------

Transkrip Mahasiswa

---------------------------------------------

NIM: 05410104001

Nama: Alif

1. Bahasa Pemrograman dgn NA = 71.00 -->

(Lulus)

2. Prakt. Bahasa Pemrograman dgn NA = 72.00 -

-> (Lulus)

---------------------------------------------

Cursor


Latihan

1. Dengan menggunakan cursor, cetak mahasiswa siapa saja yang berada di kota Surabaya. Format laporan adalah sebagai berikut:

2. Dengan menggunakan nested-cursor, cetak mahasiswa siapa saja yang berada di tiap-tiap kota. Format laporan adalah sebagai berikut:

Cursor


(halaman kosong)

BAB V

Prosedur dan Fungsi

Sub-Materi: Prosedur Fungsi

Prosedur dan Fungsi


(halaman kosong)

Prosedur dan Fungsi


Bab V

Prosedur dan Fungsi

Sub-Materi: Prosedur Fungsi

Obyek dalam T-SQL dibangun untuk memenuhi kebutuhan terhadap kemudahan pemeliharaan, penyimpanan script, dan penggunaan kembali. Secara umum, ada tiga obyek T-SQL yang dipakai yaitu prosedur, fungsi, dan trigger (pemicu). Seluruh obyek T-SQL mempunyai struktur umum yang sama, yaitu terdiri dari head (kepala) dan body (tubuh). Head berisi seluruh komponen dalam pembentukan obyek, sedangkan body berisi seluruh script yang dipakai untuk proses didalam obyek tersebut.

Head diawali dengan kata kunci data definition language (DDL) yaitu create, update, dan delete. Setelah itu diikuti dengan nama obyek. Setelah nama obyek, ada komponen khusus yang mengikuti masing-masing obyek. Untuk body, tidak ada aturan khusus kecuali dimulai dengan BEGIN dan diakhiri dengan END. Kecuali untuk beberapa jenis obyek, memerlukan kata kunci tertentu sebagai syarat completeness. Pemisah antara head dan body berupa sebuah kata kunci yaitu AS.

Prosedur

Prosedur dalam T-SQL disebut sebagai Stored Procedure (SProc). Dalam istilah yang paling sederhana, prosedur adalah kumpulan dari perintah-perintah SQL yang telah di-compile, yang bisa diakses secara langsung oleh SQL Server dan aplikasi client. Prosedur memberikan fungsionalitas pemrograman dalam skala yang luas, karena prosedur mampu:

Prosedur dan Fungsi


1. Mengimplementasikan view berparameter Prosedur memiliki kemampuan untuk mengembalikan resultset berdasarkan query. Dengan parameter (variabel yang digunakan untuk melempar nilai), proses dalam prosedur mampu mengembalikan bermacam-macam bentuk resultset.

2. Mengembalikan nilai scalar Melalui parameter, prosedur mampu memberikan nilai output. Selain melalui parameter, bisa juga melalui kata kunci RETURN. Dengan adanya RETURN, perintah query setelahnya tidak akan dieksekusi. Umumnya, RETURN digunakan ketika terdapat banyak kondisi dalam prosedur.

3. Pemeliharaan data Prosedur juga bisa digunakan untuk melakukan insert, update, dan delete (DML).

4. Sebagai bagian dari proses bisnis Proses bisnis berkaitan dengan pembuatan keputusan. Pengaturan alur statement menjadi hal yang mutlak disini. Baca kembali Bab 3 mengenai kontrol alur program.

Gambar 5.1 Gambaran umum prosedur

Membuat Prosedur

Pembuatan prosedur menggunakan CREATE PROCEDURE, untuk pengubahannya menggunakan ALTER PROCEDURE, dan untuk penghapusannya menggunakan DROP PROCEDURE. Lebih lengkapnya, bisa dilihat pada Kode 5.1.

Prosedur dan Fungsi


Parameter dalam prosedur diperbolehkan untuk memiliki nilai awal (default). Jadi ketika prosedur dieksekusi, parameter ini tidak perlu diisi.

Kode 5.1

-- membuat prosedur

CREATE PROCEDURE <nama prosedur>

[<@param1> <tipe data> [=<nilai awal>]

[OUT[PUT]], ...]

AS

BEGIN

<SQL> | <script block>

RETURN <nilai>

END;

-- mengubah prosedur

ALTER PROCEDURE <nama prosedur>

[<@param1> <tipe data> [=<nilai awal>]

[OUT[PUT]], ...]

AS

BEGIN


RETURN <nilai>

END;

-- menghapus prosedur

DROP PROCEDURE <nama prosedur>;

Parameter dalam Prosedur

Seperti yang telah dijelaskan sebelumnya, fungsi prosedur dapat dimaksimalkan dengan adanya parameter. Parameter merupakan sebuah variabel yang digunakan sebagai alat bantu untuk memasukkan atau mengeluarkan nilai dari obyek T-SQL. Ada dua jenis parameter dalam prosedur, yaitu IN dan OUT.

Parameter bertipe IN digunakan sebagai parameter input. Maksudnya adalah parameter ini hanya menerima masukan dari luar prosedur untuk kemudian dipakai didalam prosedur. Sedangkan parameter bertipe OUT digunakan sebagai output. Maksudnya adalah parameter ini hanya dipakai untuk mengeluarkan nilai dari dalam prosedur untuk dipakai oleh lingkungan tempat prosedur tersebut digunakan.

Prosedur dan Fungsi


Dari kedua tipe tersebut, didalam prosedur itu sendiri, diperlakukan sama, dan digunakan selayaknya variabel pada umumnya. Secara default, parameter yang ditulis didalam pembentukan prosedur bertipe IN.

Untuk membuat parameter, bisa dilihat kembali pada Kode 5.1. Pada dasarnya, parameter membutuhkan dua hingga empat informasi:

1. Nama parameter 2. Tipe data parameter 3. Nilai default 4. Jenis parameter

Perhatikan penggalan kode program berikut:

Kode 5.2

CREATE PROCEDURE spAmbilMhsPerGender

@gender CHAR(1) = 'P',

@jumlahData NUMERIC OUTPUT

...

Parameter @gender merupakan masukan bagi prosedur untuk memberikan kondisi pada pencariannya. Sedangkan @jumlahData merupakan keluaran. Untuk parameter @gender, ketika prosedur dieksekusi tanpa memberikan nilai pada parameter ini, secara default @gender bernilai P.

Eksekusi Prosedur

Seperti yang telah disebutkan sebelumnya bahwa prosedur bisa menggunakan parameter dan mampu mengembalikan nilai (parameter bertipe OUT, atau menggunakan kata kunci RETURN), maka semua elemen ini harus di-handle ketika akan mengeksekusi prosedur. Perhatikan kode program berikut ini:

Kode 5.3


@gender CHAR(1) = 'P'

AS

BEGIN


WHERE jns_kelamin=@gender;

Prosedur dan Fungsi


RETURN @@ROWCOUNT;

END;

Kode 5.4 -- Eksekusi SProc

BEGIN

DECLARE @jumlahData INT;

EXEC @jumlahData = spAmbilMhsPerGender 'W';

PRINT @jumlahData;

END;

Hasil:


05390102208 Cinta Perum Pondok Nirwana Blok BC-14

Surabaya W B

06390102666 Entin Jl. Yos Sudarso 1

Sidoarjo W B

...

Pada Kode 5.3, prosedur spAmbilMhsPerGender digunakan untuk mengambil data mahasiswa yang disaring berdasarkan jenis kelamin. Karena ada penyaringan, maka prosedur harus mempunyai parameter bertipe IN yaitu @gender yang diberi nilai default P sebagai representasi dari jenis kelamin pria. Body prosedur hanya berisi perintah SELECT, sesuai dengan nama prosedur, dengan WHERE untuk penyaringan sesuai @gender. Nilai balikan (RETURN) dari prosedur ini adalah jumlah data hasil SELECT yang tersimpan dalam @@ROWCOUNT.

Untuk pemanggilan prosedur (Kode 5.4), nilai balikan harus ditampung, oleh karena itu perlu untuk membuat variabel @jumlahData yang bertipe numerik. Dengan adanya kata kunci RETURN, maka pemanggilan prosedur harus didahului dengan variabel penampung nilai RETURN diikuti dengan operator assignment, nama prosedur lalu nilai parameter.

Prosedur dan Fungsi


Ketika menggunakan OUT parameter, sedikit berbeda dengan sebelumnya. Contoh kode programmnya dapat dilihat pada Kode 5.5. Dengan adanya OUT, maka nilai OUT harus ditampung kedalam variabel @jumlahData. Pemanggilan prosedur pun langsung dengan memanggil nama prosedur, diikuti parameter , lalu kata kunci OUTPUT. Untuk pemanggilan prosedur, bisa dilihat pada Kode 5.6.

Kode 5.5


@gender CHAR(1) = 'P',

@jumData INT OUTPUT

AS

BEGIN


WHERE jns_kelamin=@gender;

-- system function tidak dapat

-- dijadikan OUTPUT;

SET @jumData=@@ROWCOUNT;

END;

Kode 5.6

-- Eksekusi SProc

BEGIN

DECLARE @jumlahData INT;

EXEC spAmbilMhsPerGender

@jumData = @jumlahData OUTPUT;

PRINT @jumlahData;

END;

Hasil:


05410104001 Alif Jl. Jagir 20 Surabaya P B

...

Pemeliharaan Data

Contoh-contoh sebelumnya merupakan contoh untuk prosedur sebagai view berparameter dan prosedur yang mengembalikan nilai skalar. Untuk pemeliharaan data, prosedur memberikan kemudahan bagi pengembang aplikasi untuk melakukan

Prosedur dan Fungsi


perubahan data tanpa menggunakan DML secara langsung. Dengan begitu, universalitas kode bisa tercapai.

Namun, semua ini ada pengorbanan untuk pembuatan prosedur yang lumayan panjang. Kenapa? Karena dalam DML, seluruh kolom bisa dimanipulasi. Bagian-bagian yang dimanipulasi ini harus menjadi parameter dalam prosedur. Jadi, kalau ada 50 kolom, maka prosedur memiliki 50 parameter dengan tipe IN.

Berikut ini contoh prosedur untuk memasukkan data kedalam tabel dosen.

Kode 5.7

CREATE PROCEDURE spInsertDosen

@nid CHAR(6),

@nama VARCHAR(100)

AS

BEGIN

INSERT INTO dosen(nid, nama)

VALUES (@nid, @nama);

END;

Kode 5.8

-- Eksekusi SProc

EXEC spInsertDosen '100110', 'Tegar';

Fungsi

Bersama dengan operator, fungsi mampu memberikan kekuatan tersendiri dalam SQL. Salah satu yang sering dipakai adalah aggregate function, yaitu fungsi yang melakukan perhitungan agregasi dan menghasilkan nilai skalar. Fungsi-fungsi agregasi ini antara lain AVG(), MAX(), MIN(), COUNT(), dan SUM().

Pada Bab 1, telah dipelajari mengenai system function dalam sub-bab SQL Function. Selain fungsi agregasi, beberapa fungsi lainnya, antara lain konversi yang menggunakan CAST() dan CONVERT(); manipulasi string dengan fungsi-fungsi kerennya seperti SUBSTRING(),

LEN(), REPLACE(), dll; fungsi matematika seperti CEILING(),

Prosedur dan Fungsi


ROUND(), POWER(), dll; fungsi date and time yang menggunakan GETDATE(), DATEPART(), DATEADD(), dan DATEDIFF(); dan beberapa fungsi lainnya.

User-Defined Function

User-defined Function (UDF) merupakan kumpulan script yang dioptimasi dan di-compile dan dapat dipanggil untuk bekerja sebagai unit tunggal. Dalam buku ini, untuk kemudahan, maka UDF disebut juga sebagai fungsi. Fungsi dapat mengembalikan nilai apapun. Sehingga dari nilai ini, fungsi dapat dibagi menjadi 2 yaitu (1) fungsi yang mengembalikan nilai skalar (scalar-valued Function), dan (2) fungsi yang mengembalikan nilai dalam bentuk tabel (table-valued Function).

Scalar-valued Function Tipe ini merupakan tipe yang paling umum. Seperti yang disebutkan sebelumnya, bahwa fungsi tidak terbatas pada nilai balikan berupa integer, bahkan fungsi pun mampu mengembalikan user-defined datatypes (tipe data yang didefinisikan sendiri oleh programmer). Namun, fungsi tidak mampu mengembalikan tipe data tertentu antara lain BLOB, cursor, dan tipestamp. Sintak untuk membuat fungsi bernilai skalar, dapat dilihat pada Kode 5.9.

Pada Kode 5.9 dapat dilihat bahwa fungsi juga memiliki parameter seperti prosedur. Perbedaannya adalah pada tipe parameter, yang dimiliki oleh fungsi hanyalah parameter sebagai masukan. Setiap fungsi harus mengembalikan nilai, oleh karena itu kata kunci RETURNS dan RETURN harus ada didalam struktur pembuatan fungsi.

Sebagai contoh pembuatan fungsi bernilai skalar, coba lihat kembali contoh permasalahan pada cursor di Bab 4, Kode 4.11. Pada query tersebut, ada pengecekan terhadap status kelulusan mahasiswa. Kita akan coba membuat sebuah fungsi untuk pengecekan ini. Perhatikan Kode 5.10.

Prosedur dan Fungsi


Kode 5.9

-- membuat prosedur

CREATE FUNCTION <nama function>

([<@param1> <tipe data>, ...])

RETURNS <tipe data> AS

BEGIN


RETURN <nilai>

END;

-- membuat prosedur

ALTER FUNCTION <nama function>

(<@param1> <tipe data>, ...)

RETURNS <tipe data> AS

BEGIN


RETURN <nilai>

END;

-- membuat prosedur

DROP FUNCTION <nama function>;

Kode 5.10

CREATE FUNCTION Kelulusan (@nilai INT)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @lulus VARCHAR(100);

IF @nilai>55

SET @lulus = 'Lulus';

ELSE

SET @lulus = 'Gagal';

RETURN @lulus;

END;

Sehingga dari contoh query pada Kode 4.11, kode program untuk membuat cNilai diganti menjadi:

Kode 5.11

DECLARE cNilai CURSOR FOR

SELECT nama, tugas*0.4 + uts*0.3 + uas*0.3,

dbo.Kelulusan(tugas*0.4 + uts*0.3 +

uas*0.3)

FROM nilai n JOIN mk

ON n.kode_mk = mk.kode_mk

WHERE nim = @nim;

Prosedur dan Fungsi


Apa yang bisa disimpulkan tentang penulisan kode program pada Kode 5.11 setelah adanya fungsi?

Ya, benar. Script menjadi lebih sederhana, dan tentunya fungsi yang telah dibuat dapat dipakai kembali di script lainnya. Perhatikan pemanggilan fungsi yang harus diikuti oleh dbo.. Hal ini dikarenakan yang membuat fungsi isLulus adalah user dengan schema dbo..

Table-valued Function Ada beberapa studi kasus yang memerlukan fungsi yang mampu mengembalikan sebuah tabel entah itu hasil query atau melalui proses yang sangat panjang. Pemakaian fungsi seperti ini ibarat sebuah inline view. Untuk dapat mengeksekusi fungsi jenis ini, pemanggilannya harus didalam kata kunci FROM dalam struktur query. Sintak untuk membuat fungsi bernilai tabel, dapat dilihat pada .

Kode 5.12

CREATE FUNCTION <nama function>

(<@param1><tipe data>, ...)

RETURNS TABLE |

<nama var> TABLE (<column list>) AS

BEGIN


RETURN <perintah select>

END;

Tidak ada perbedaan dengan scalar-valued function, kecuali pada bagian RETURNS. Jika pada scalar-valued function nilai RETURNS adalah tipe data, maka pada table-valued function nilai RETURNS adalah juga (sebuah variabel bertipe) tabel.

Kode 5.13

CREATE FUNCTION fnMhs(@kota VARCHAR(50))

RETURNS TABLE

AS

RETURN (SELECT nim, nama, alamat, kota,

jns_kelamin, sts_nikah

FROM mahasiswa

WHERE kota=@kota);

Prosedur dan Fungsi


Sebagai contoh pada Kode 5.13, misal Kabag AAK ingin mengetahui seluruh data mahasiswa per kota sesuai dengan masukan. Karena ada sebuah penyaringan data berdasarkan kota, maka fungsi yang dibuat harus memiliki parameter, setidaknya satu, untuk menampung nilai penyaringan. Sedangkan untuk memanggilannya, perhatikan Kode 5.14:

Kode 5.14 SELECT *

FROM dbo.fnMhs('Surabaya');

Deterministic Function

Deterministic dan non-deterministic ditentukan oleh apa yang dilakukan oleh fungsi. Ketika fungsi diberikan input dan selalu mengembalikan nilai yang sama, maka fungsi tersebut deterministic. Bandingkan dengan GETDATE() yang selalu berubah setiap millisecond. GETDATE() merupakan fungsi non-deterministic.

Agar fungsi dapat disebut sebagai deterministic, ada empat kriteria: 1. Fungsi tersebut harus terikat pada schema tertentu (WITH

SCHEMABINDING). 2. Semua fungsi yang dipanggil oleh fungsi ini harus

deterministic. 3. Penggunaan tabel harus didefinisikan didalam fungsi itu

sendiri. 4. Tidak adanya prosedur extended.

Dari 4 ini, ada sebuah cara (dalam SQL Server) untuk melihat apakah fungsi termasuk deterministic atau tidak, dengan menjalankan perintah berikut:

Kode 5.15

SELECT

OBJECTPROPERTY(OBJECT_ID('isLulus'),

'ISDETERMINISTIC');

Prosedur dan Fungsi


Dari hasil perintah tersebut, fungsi isLulus ternyata non-deterministic. Hal ini dikarenakan poin pertama, yaitu tidak adanya kata kunci WITH SCHEMABINDING pada fungsi tersebut.

Prosedur dan Fungsi


Latihan

1. Buat prosedur untuk menampilkan nim, nama, dan alamat mahasiswa sesuai angkatan. Nilai angkatan ditampung dalam parameter masukan.

2. Buat prosedur untuk menambah data mahasiswa. Seluruh nilai kolom di tabel mahasiswa ditampung dalam parameter masukan.

3. Buat prosedur untuk menghapus data mahasiswa sesuai nim. Nim ditampung dalam parameter masukan.

4. Buat fungsi untuk menghitung umur mahasiswa. Parameter masukan berupa tanggal lahir, sedangkan nilai balikan berupa umur dalam ukuran tahun.

5. Buat fungsi untuk menentukan apakah mahasiswa sudah menikah atau belum dilihat dari standar umur yang ditetapkan pemerintah. Usia minimum pria menikah = 24 tahun. Usia minimum wanita menikah = 21 tahun. Parameter masukan berupa tanggal lahir. Saran: gunakan fungsi yang dibuat pada no.3 untuk menghitung umur. Nilai balikan dari fungsi ini berupa status menikah yaitu B untuk belum menikah, dan M untuk sudah menikah.

Prosedur dan Fungsi


(halaman kosong)

BAB VI

Trigger

Sub-Materi: Pembuatan Trigger

Ujicoba Trigger

Trigger


(halaman kosong)

Trigger


Bab VI

Trigger

Sub-Materi: DMLTrigger

Membuat DML Trigger

Trigger merupakan bentuk khusus dari prosedur yang merespon pada event tertentu. Ada 2 bentuk trigger yaitu (1) DDL Trigger dan (2) DML Trigger.

DDL Trigger melakukan proses ketika terjadi perubahan pada struktur basis data melalui create, alter, drop, dan perintah sejenis.

DML Trigger merupakan sekumpulan kode yang ditempelkan pada tabel atau view. Tidak seperti prosedur, yang dipanggil secara eksplisit, trigger secara otomatis dijalankan ketika terjadi event pada tabel atau view. Pada matakuliah Prakt. PBD, hanya dibahas mengenai DML Trigger.

Gambar 6.1 Trigger dalam basis data

Ada tiga event dalam trigger, sesuai dengan Gambar 6.1, yang disesuaikan dengan tiga tipe DML yaitu insert, update, dan delete. Dari ketiga event ini, trigger juga mampu melakukan hibridisasi, yaitu pencampuran dua event. Event-event ini antara lain:

Trigger


1. INSERT Trigger 2. DELETE Trigger 3. UPDATE Trigger

Seperti yang dijelaskan sebelumnya bahwa trigger merespon pada event tertentu dan trigger menempel pada tabel atau view, maka sintak untuk membuat Trigger sedikit istimewa dibandingkan dengan sintak pembuatan obyek basis data lainnya.

DML Trigger

Masih sama seperti obyek basis data lainnya, trigger dibuat diubah dan dihapus menggunakan perintah DDL. Untuk sintak pembuatannya dapat dilihat pada Kode 6.1.

Kode 6.1

-- membuat trigger

CREATE TRIGGER <nama trigger>

ON [<nama schema>.]<nama tabel>

[WITH ENCRYPTION]

{FOR|AFTER|INSTEAD OF}

<[DELETE][,][INSERT][,] [UPDATE]>

AS

BEGIN


END;

-- mengubah trigger

ALTER TRIGGER <trigger_name>

ON [<nama schema>.]<nama tabel|view>

[WITH ENCRYPTION]

{FOR|AFTER|INSTEAD OF}

<[DELETE][,][INSERT][,] [UPDATE]>

AS

BEGIN


END;

-- menghapus trigger

DROP TRIGGER <nama trigger>;

DML Trigger mempunyai kemungkinan untuk menjalankan trigger lain, trigger ini disebut juga dengan nested-trigger.

Trigger


Sebagai contoh, misal dari tabel mahasiswa mempunyai trigger untuk melakukan modifikasi data pada tabel nilai. Sedangkan tabel nilai sendiri mempunyai trigger untuk melakukan modifikasi data di tabel nilai. Nested-trigger ini hanya mempunyai kapasitas sampai dengan 32 kedalaman (depth). Dalam contoh tersebut, hanya dua kedalaman.

ON Bagian ini digunakan untuk menuliskan lokasi (tabel atau view) trigger akan ditempatkan. Setiap trigger hanya bergantung pada satu tabel.

WITH ENCRYPTION Dengan aktifnya property ini, secara otomatis sistem akan melakukan enkripsi terhadap trigger. Sehingga tidak ada seorang pun, bahkan sang pembuat, yang dapat melihat kode trigger. Sebagai tambahan, property ini juga dapat digunakan pada view dan prosedur. Sebagai contoh penggunaan WITH ENCRYPTION, perhatikan Kode 6.2:

Kode 6.2 CREATE TRIGGER tg_dosen

ON dosen

WITH ENCRYPTION

FOR INSERT

AS

BEGIN

DECLARE @VAR VARCHAR(50);

SET @VAR = 'TEST';

END;

Percobaan membuka kembali teks dalam obyek trigger tg_dosen, hasilnya seperti pada Gambar 6.2.

Gambar 6.2 Galat karena Enkripsi pada Trigger

Trigger


Pada Gambar 6.2, dapat dilihat pada baris kedua terakhir, “The text is encrypted”. Kalimat ini menandakan bahwa tg_dosen telah dienkripsi.

FOR|AFTER vs INSTEAD OF Trigger terjadi karena event tertentu baik sebelum (INSTEAD OF) maupun setelah (FOR/AFTER) event terjadi. FOR Trigger dikerjakan setelah manipulasi data selesai. Sedangkan INSTEAD OF Trigger dikerjakan sebelum manipulasi data dilakukan. Dari dua macam tipe ini, muncul dua model data dalam trigger (dalam bentuk tabel), yaitu: 1. Tabel INSERTED

Tabel ini dibentuk dan datanya ini diperoleh dari event insert. Untuk pengambilan datanya, perhatikan query berikut ini.

Kode 6.3 SELECT *

FROM INSERTED;

2. Tabel DELETED Tabel ini dibentuk dan datanya diperoleh dari event delete. Untuk pengambilan datanya perhatikan query berikut ini.

Kode 6.4 SELECT *

FROM DELETED;

Keduanya diperlakukan sama seperti sebuah tabel dengan satu baris data. Data ini berisi data yang akan dimanipulasi. Sehingga struktur tabel khusus ini mengikuti data yang akan dimanipulasi. Misal dari contoh tg_dosen pada Kode 6.2, hanya akan dibuat tabel INSERTED. Struktur tabel INSERTED dalam tg_dosen, sama seperti tabel dosen.

Untuk proses update, tidak ada UPDATED data. Dalam proses sistem, update adalah proses delete yang dilanjutkan dengan proses insert. Oleh karena itu, dalam kasus update, perlu dipertimbangkan DELETED sebagai data lama (old value) dan INSERTED sebagai data baru (new value).

Trigger


Dalam beberapa permasalahan, kita harus memanipulasi data setelah kondisi terpenuhi. Misal ketika terjadi transaksi penjualan, maka terjadi penambahan data pada tabel penjualan. No transaksi, untuk alasan integritas data dan keamanan, harus dibangkitkan secara otomatis oleh SQL Server. Belum lagi jika terjadi kesalahan entri yang akan mengakibatkan kerusakan data. Jadi ketika user melakukan INSERT INTO, hanya data-data yang terlihat ketika transaksi yang menjadi bagian dari VALUES (misal, nama customer dan barang yang dibeli). Nah, bagaimana cara kita membuat no transaksinya?

INSTEAD OF Trigger memberikan solusi untuk permasalahan ini. Seperti yang telah disebutkan sebelumnya, FOR Trigger merupakan trigger yang dikerjakan setelah manipulasi data selesai. Sebaliknya, INSTEAD OF Trigger memiliki kemampuan untuk mengerjakan proses (didalam trigger) sebelum manipulasi data dilakukan. Sehingga dapat dilakukan pembangkitan no transaksi. Ketika terjadi sesuatu yang menyebabkan kondisi, sebelum data dientrikan, menjadi FALSE, maka SQL Server melakukan pembatalan transaksi.

Gambar 6.3 Alur Proses Trigger

Trigger


INSERT|UPDATE|DELETE Bagian ini menspesifikasikan pada event DML apa, trigger akan dieksekusi. Seperti yang telah disebutkan sebelumnya, event bisa terjadi pada event tunggal atau hybrid. Untuk hybrid, diperlukan koma untuk memisah masing-masing event. Contoh penggunaan trigger untuk sebuah event DML dapat dilihat pada Kode 6.2. Contoh penggunaan trigger untuk dua event DML dapat dilihat pada Kode 6.5.

Kode 6.5 CREATE TRIGGER tg_insupd_nilai

ON nilai

FOR INSERT, UPDATE

AS

BEGIN

UPDATE nilai

SET tugas = 0, uts = 0, uas = 0;

END;

Membuat DML Trigger

Tujuan dari trigger yang akan dibuat ini adalah untuk membuat No Induk Mahasiswa (NIM).

1. Pembuatan NIM terjadi sebelum data diinputkan. Sehingga jenis trigger yang dipakai adalah INSTEAD OF INSERT pada tabel mahasiswa. Perhatikan kode berikut:

Kode 6.6 CREATE TRIGGER tg_bef_ins_mahasiswa

ON mahasiswa

INSTEAD OF INSERT

AS

2. Buat variabel untuk membantu dalam membentuk komponen NIM. Sebagai acuan, NIM terdiri dari tiga komponen yaitu: (1) Tahun angkatan. Dengan panjang dua karakter, berisi

informasi mengenai 2 digit terakhir tahun sekarang. Untuk itu diperlukan beberapa fungsi seperti RIGHT(), CONVERT(), YEAR(), dan GETDATE() untuk membuat tahun angkatan.

Trigger


(2) Jurusan. Dengan panjang 5 karakter, berisi informasi mengenai kode jurusan. Untuk contoh ini, diasumsikan hanya jurusan S1 SI sehingga kode jurusan diisi dengan 41010.

(3) No urut. Dengan panjang 4 karakter, berisi informasi mengenai no urut data dengan ketentuan hanya data yang sesuai dengan poin (1) dan poin (2). Untuk membuatnya, dibutuhkan fungsi COUNT(*). Hasil dari COUNT(*) adalah angka dengan panjang karakter yang dinamis. Untuk dapat membuatnya menjadi statis 4 karakter, diperlukan kondisi untuk mengecek nilai COUNT(*) dan menambahkan karakter 0 didepan nilai tersebut sampai jumlah karakter menjadi 4.

Setelah ketiga komponen tersebut terbentuk, gabungkan kedalam sebuah variabel. Perhatikan kode berikut:

Kode 6.7

DECLARE @tahun CHAR(2);

SET @tahun = RIGHT(CONVERT(VARCHAR,

YEAR(GETDATE())),2);

DECLARE @jurusan CHAR(5);

SET @jurusan = '41010' -- asumsi hanya

jurusan S1 SI

DECLARE @urut INT;

SET @urut =

(SELECT COUNT(*)+1

FROM mahasiswa

WHERE nim like @tahun + @jurusan + '%');

DECLARE @strurut CHAR(4);

IF @urut < 10

SET @strurut='000'+CONVERT(VARCHAR, @urut);

ELSE IF @urut < 100


ELSE IF @urut < 1000


ELSE

SET @strurut=''+CONVERT(VARCHAR, @urut);

DECLARE @nim CHAR(11);

SET @nim = @tahun + @jurusan + @strurut;

Trigger


3. Karena jenis trigger ini adalah INSTEAD OF INSERT, maka dibutuhkan sintak untuk melakukan insert kedalam tabel mahasiswa. Data yang dimasukkan diambil dari tabel INSERTED dengan menampungnya kedalam variabel.

Kode 6.8

DECLARE @nama VARCHAR(100),

@almt VARCHAR(100),

@kota VARCHAR(50),

@jk CHAR(1),

@st CHAR(1);

SELECT @nama = nama, @almt = alamat,

@kota = kota, @jk = jns_kelamin,

@st = sts_nikah

FROM INSERTED;


VALUES (@nim, @nama, @almt, @kota, @jk, @st);

4. Jalankan (eksekusi) trigger untuk membentuk obyek basis datanya. Setelah trigger jadi, saatnya untuk mencoba trigger tersebut. Masukkan data seperti berikut ini kedalam tabel mahasiswa:

Kode 6.9 INSERT INTO mahasiswa (nama, alamat, kota,

jns_kelamin, sts_nikah)

VALUES ('test','test','test','P','B');

Dari perintah query pada Kode 6.9, seharusnya data tidak akan pernah masuk kedalam tabel mahasiswa karena kolom dan nilai primary key tidak disertakan. Tetapi ketika query tersebut dieksekusi, ternyata masuk. Inilah fungsi utama dari trigger. Trigger memvalidasi proses yang terjadi dan memiliki kemampuan untuk membatalkan proses tersebut jika hasil validasi ternyata salah.

Trigger


Latihan

1. Buat trigger setelah insert pada tabel mahasiswa. Trigger ini mempunyai fungsi untuk memasukkan satu data kedalam tabel nilai untuk matakuliah MK-102, dan dosen NN.

2. Buat trigger sebelum delete pada tabel mahasiswa. Trigger ini mempunyai fungsi untuk melakukan pengecekan apakah ada mahasiswa yang sudah mempunyai nilai. Jika sudah ada, lakukan penghapusan data nilai terlebih dahulu, lalu lakukan penghapusan data mahasiswa. Jika belum ada, lakukan penghapusan data mahasiswa

Trigger


(halaman kosong)

BAB VII

Connection dan Recordset

Sub-Materi: ADO.NET

Connection Recordset



(halaman kosong)



Bab VII


Sub-Materi: ADO.NET

Connection Recordset

ADO.NET

Sebelum adanya .NET, tim pengembang aplikasi menggunakan teknologi seperti ODBC, OLE DB, dan ADO untuk melakukan pengaksesan data kedalam basis data. Pengembangan ADO.NET bukan merupakan perbaikan dari ADO, melainkan sebuah paradigma baru dalam akses data. Kenapa? Karena 2 alasan:

1. ADO.NET merupakan bagian integral dari .NET. Berbeda dengan teknologi sebelumnya yang menjadi entitas eksternal, sehingga ada beberapa langkah yang dilakukan sebelum teknologi itu dapat dipakai oleh programmer.

2. ADO.NET bukanlah kumpulan dari komponen ActiveX.

Aplikasi yang dibangun dalam .NET didesain untuk berbagi general model, dimana semua komponennya (library) diintegrasikan kedalam sebuah framework, diorganisir kedalam namespace, dan dideklarasikan sebagai public. Karena itulah, lahir ADO.NET.

Karena ADO.NET merupakan bagian dari .NET, maka semua komponen ADO.NET diletakkan kedalam beberapa namespace.

1. System.Data Class, interface, enumeration yang mendefinisikan dan secara parsial mengimplementasi arsitektur ADO.NET.

2. System.Data.Odbc .NET Framework Data Provider untuk ODBC.



3. System.Data.Sql .NET Framework Data Provider untuk SQL Server.

Masih ada beberapa namespace yang dipakai misalnya untuk provider kedalam OLE DB, dan Oracle, SQL Server Mobile, dan lain-lain.

Memahami Arsitektur ADO.NET

Arsitektur ADO .NET dibagi menjadi 3 kelompok besar, yaitu Data Provider, DataSet dan DataTable.

Gambar 7.1 Arsitektur ADO.NET

ADO.NET punya 2 komponen sentral: data provider dan dataset.

Data provider digunakan untuk mengkoneksikan aplikasi dengan

data source dan mendukung pengaksesan data dan manipulasi.

Sedangkan dataset mendukung data-cache dalam bentuk

disconnected environment, dan melakukan updating data ketika

dibutuhkan. Seperti pada Gambar 7.1, dataset terdiri dari banyak

datatable. Sedangkan datatable mempunyai baris dan kolom

seperti layaknya SQL tables.



Setiap data provider mempunyai namespace-nya sendiri.

Faktanya, setiap data provider secara esensi merupakan

implementasi interface dalam namespace System.Data,

dikhususkan untuk data source tertentu. Sebagai contoh, jika

menggunakan SQL Server sebagai data source, maka penggunaan

System.Data.SqlClient menjadi pilihan utama dalam

pengaksesan data karena performance benefit-nya. Sebagai

gambaran, perhatikan Gambar 7.2.

Gambar 7.2 Perbedaan data provider dalam SQL Server dan OLE DB

Data provider mempunyai 2 fungsi:

1. Menyediakan akses kedalam data menggunakan koneksi

yang aktif dalam data source.

2. Menyediakan transmisi data ke dan dari dataset kedalam

datatable.

Koneksi basis data dilakukan menggunakan connection dari data

provider, misal System.Data.SqlClient.SqlConnection.

Komponen lain seperti data reader, command, dan data adapter

mendukung pengambilan data, eksekusi perintah SQL, dan

membaca atau menulis kedalam dataset atau datatable. Setiap

data provider mempunyai prefiks sesuai dengan data source yang

dipakai. Untuk lebih jelasnya, perhatikan Gambar 7.3.



Karena yang dipelajari didalam Praktikum PBD adalah SQL

Server, maka namespace yang dipakai adalah

System.Data.SqlClient. Secara otomatis, hal ini akan

berdampak pada pemilihan class-class connection, data reader,

command, dan data adapter menjadi SqlConnection,

SqlDataReader, SqlCommand, dan SqlDataAdapter.

Gambar 7.3 Perbedaan prefix dalam data provider

Connections

Seperti yang telah disebutkan sebelumnya, kita tidak bisa melakukan apapun terhadap data source (sebut saja basis data) sebelum membuat sebuah session dengan basis data. Session ini dibuat dengan menggunakan sebuah obyek yaitu connection, yang merupakan instance dari sebuah class yang mengimplementasi interface System.Data.IdbConnection untuk data source tertentu.

Untuk membentuk koneksi kedalam basis data SQL Server Express, bisa dilakukan dengan langkah-langkah berikut.

1. Buat sebuah Windows Application melalui VB.NET. 2. Dalam form yang terbentuk, lakukan navigasi kedalam code

area. Atau dengan cara lain yaitu double click pada form.



3. Jika double click, maka secara otomatis masuk kedalam sebuah prosedur yang secara khusus disebut dengan event. Sebagai informasi, VB.NET merupakan aplikasi pengembang yang menggunakan konsep event-driven programming. Setiap program dikerjakan melalui event dari control-nya. Dan setiap control mempunyai default event. Seperti pada contoh yang kita lihat bersama, Form_Load. Jadi, kode program dalam event ini akan dikerjakan ketika form pertama kali di-load oleh komputer.

4. Didalam code area, lingkungannya sama seperti bahasa pemrograman lain. Ada class, ada variabel beserta scope-nya, ada prosedur dan fungsi, ada event, dan ada access modifier.

5. Buat kode program berikut ini:

Kode 7.1

Imports System.Data.SqlClient

Public Class Form1

Dim connectionString As String = _

"server=.\sqlexpress; " & _

"database=pbd; " & _

"integrated security=true"

Dim conn As SqlConnection

Private Sub Form1_Load(…) Handles …

conn = New SqlConnection _

(connectionString)

Try

conn.Open()

MessageBox.Show("Koneksi dibuka.")

Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

conn.Close()

MessageBox.Show("Koneksi ditutup.")

End Try

End Sub

End Class

6. Jika sudah, coba jalankan aplikasi dengan menekan tombol F5. Akan terlihat sebuah pesan yang mengatakan bahwa Koneksi dibuka.. Dan jika diklik tombol OK, muncul pesan



lagi yang mengatakan Koneksi ditutup.. Jika terjadi error, maka aka muncul pesan yang mengatakan apa error-nya.

Bagaimana cara kerjanya

Pertama, dilakukan imports terhadap namespace SQL Server Data Provider melalui kode program berikut:

Kode 7.2

Imports System.Data.SqlClient

Lalu dibuat connection string yang berisi parameter-parameter (dalam bentuk pasangan {key = value} dan dipisahkan oleh titik koma) yang menspesifikasikan informasi koneksi. Beberapa parameter bisa sama untuk semua data provider, dan beberapa khusus untuk data provider tertentu.

Kode 7.3 Dim connectionString As String = _




Untuk SQL Server, parameter yang dipakai umumnya adalah 3 yaitu:

1. server = .\sqlexpress Parameter ini digunakan untuk menspesifikasikan instance dari SQL Server yang dipakai. Instance ini umumnya dibentuk dari nama komputer. Khusus untuk SQL Server Express Edition, nama komputer diikuti dengan \sqlexpress.

2. database = pbd Parameter ini digunakan untuk menspesifikasikan basis data dalam SQL Server yang akan dipakai untuk melakukan pengambilan dan manipulasi data.

3. integrated security = true Parameter ini mengindikasikan bahwa login yg dilakukan kedalam lingkungan SQL Server menggunakan user credential milik sistem operasi (dalam contoh ini adalah



Windows). Jika nilainya false, maka user credential yang dipakai adalah milik SQL Server.

Setelah connection string dibentuk, dibuat obyek connection sesuai dengan namespace yang kita pakai.

Kode 7.4 Dim conn As SqlConnection

Setelah connection dibuat, sekarang saatnya melakukan konstruksi connection dengan kode berikut:

Kode 7.5

conn = New SqlConnection(connectionString)

Konstruksi ini dibentuk dengan memasukkan nilai connection string kedalam parameter. Sampai ditahap ini, obyek connection sudah dibuat untuk digunakan dalam session.

Membuat session

Session dibuat ketika terjadi open connection. Cara ini bisa ditempuh dengan memanggil metode open dari obyek connection yang telah dibuat sebelumnya. Perhatikan kode berikut:

Kode 7.6

Try

conn.Open()

MessageBox.Show("Koneksi dibuka.")

Setelah koneksi dibuka, aplikasi menampilkan pesan Koneksi dibuka.. Namun pesan ini akan dijalankan setelah conn.Open() sukses dijalankan. Pada tahap ini, secara normal bisa dilakukan transaksi pada data source, dengan menggunakan query atau dengan memanggil obyek basis data (tertentu) secara langsung.

Jika conn.Open() tidak berhasil dijalankan, maka aplikasi menangkap error-nya dalam baris berikut:

Kode 7.7





Setiap data provider mempunyai exception class untuk error handling; SqlException adalah class untuk SQL Server Data Provider. Informasi spesifik tentang error dapat dilihat dari exception yang dikeluarkan.

Setelah selesai, koneksi harus ditutup menggunakan perintah berikut:

Kode 7.8

Finally

conn.Close()

MessageBox.Show("Koneksi ditutup.")

End Try

Setelah ditutup, kita menampilkan pesan Koneksi ditutup.. Penutupan ini dilakukan di blok Finally untuk memastikan bahwa penutupan koneksi selalu dilakukan.

Security and Password dalam SqlConnection

Telah dijelaskan sebelumnya bahwa dengan menggunakan integrated security=false (yang merupakan nilai default), login dilakukan menggunakan user dan password yang disediakan oleh SQL Server. Sehingga parameter koneksi harus diubah menjadi seperti pada Kode 7.9.

Kode 7.9




"user id=sa;" & _

"password=123456; " & _

Connection String Parameters untuk SqlConnection

Beberapa parameter lain yang dapat dipakai untuk membuat obyek connection dapat dilihat pada Tabel 7.1:

Tabel 7.1 Parameter dalam Connection String

Name Default Value

Allowed Value

Description

Application Name

String Nama aplikasi

Data provider



Name Default Value

Allowed Value

Description

Connect Timeout

15 0-32767 Detik untuk menunggu untuk konek

Server - Nama server

Nama dari SQL Server instance

Encrypt false true, false, no, yes

Penggunaan enkripsi SSL

Database - Nama basis data di server

Nama basis data

Integrated security

false true, false, yes, no, sspi

Mode otentikasi

Packet size 8192 Multiple of 512

Ukuran paket data dalam jaringan (bytes)

Password - String Password jika tidak menggunakan otentikasi Windows

Persist Security

false true, false, no, yes

Pengembalian informasi sensitif setelah koneksi.

User ID - String User name jika tidak menggunakan otentikasi Windows

Workstation ID

local name String Workstation yang konek ke SQL Server

Recordset

Pada sub-bab sebelumnya, telah dipelajari mengenai obyek connection sebagai jalur untuk akses dan komunikasi dengan data source. Setelah connection dibuat, hal selanjutnya adalah interaksi dengan basis data yang terdiri dari pengambilan kembali (retrieval) data/informasi, dan manipulasi data.

Apapun jenis interaksinya, dibutuhkan command (atau statement). Dalam VB.NET, command merupakan sebuah obyek. Dan class yang dipakai (dalam ADO.NET) untuk dapat berinteraksi dengan data source adalah SqlCommand.

Dalam berinteraksi dengan basis data, tetap digunakan bahasa native yaitu query. Query inilah yang nantinya menjadi parameter kunci dalam setiap konstruksi command.



Pengambilan Data

Pengambilan kembali (retrieval) data/informasi menggunakan perintah SELECT. Setiap hasil yang dikembalikan oleh basis data yang merespon perintah SELECT disebut sebagai resultset. Resultset ini ditampung kedalam obyek data reader yaitu SqlDataReader. Untuk prosedur dalam pengambilan kembali data/informasi, dapat dilihat pada Gambar 7.4.

SqlCommandDatabase

SqlDataReaderForm

Gambar 7.4 Prosedur Pengambilan Kembali Data/Informasi

SqlCommand Sebagai contoh pemakaian command, perhatikan kode program berikut ini:

Kode 7.10 Dim comm As SqlCommand

…

Try

conn.Open()

'mengambil data mhs

comm = New SqlCommand()

comm.Connection = conn

comm.CommandType = CommandType.Text

comm.CommandText = _

"SELECT * FROM DOSEN"



Finally

conn.Close()

End Try

…



Pertama-tama, deklarasi variabel comm sebagai variabel global bertipe data SqlCommand. Setelah itu, dalam event Form_Load (pada contoh), lakukan konstruksi command menggunakan comm = New SqlCommand().

Setelah itu, pastikan bahwa SqlCommand memakai koneksi yang telah dibuat melalui comm.Connection. Atur agar SqlCommand hanya menerima query melalui comm.CommandType. Langkah terakhir adalah memasukkan query kedalam SqlCommand melalui comm.CommandText.

Langkah selanjutnya adalah menjalankan (eksekusi) command. Untuk menjalankan command yang berisi perintah SELECT, diperlukan sebuah fungsi dalam command yaitu comm.ExecuteReader. Karena query tersebut mengembalikan resultset, maka perlu dibuat terlebih dahulu SqlDataReader.

SqlDataReader SqlDataReader berasosiasi langsung dengan SqlCommand ketika melakukan eksekusi melalui comm.ExecuteReader. Sehingga pembuatan SqlDataReader sedikit berbeda dengan obyek lainnya. Perhatikan kode program berikut ini:

Kode 7.11

'tampung

Dim reader As SqlDataReader

reader = comm.ExecuteReader

Letakkan pecahan kode tersebut setelah selesai membuat SqlCommand. Dengan kode tersebut, selesai sudah proses penampungan resultset.

Setelah resultset berhasil ditampung, selanjutnya adalah menampilkan resultset kedalam form. Ada beberapa hal yang perlu dipahami mengenai sifat dari SqlDataReader. Sifat SqlDataReader adalah read-only dan forward-only. Maksudnya adalah, read-only berarti hanya bisa dibaca, tidak bisa ditulis; sedangkan forward-only berarti pembacaan dilakukan dari baris pertama menuju ke baris terakhir, tidak dapat dibalik.



Karena sifat forward-only, maka setiap baris hanya dapat dibaca satu kali. Oleh karena itu, dibutuhkan perulangan. Dalam setiap perulangan, data harus ditampilkan kedalam form. Sebagai contoh, data dari hasil resulset ditampilkan satu-per-satu kedalam MessageBox.

Kode 7.12

'tampilkan

While reader.Read

MessageBox.Show(reader(0) &

";" & reader(1))

End While

Letakkan pecahan kode tersebut setelah SqlDataReader. Perulangan dilakukan selama SqlDataReader dapat dibaca melalui reader.Read. Didalam blok While, lakukan proses terhadap data, yang dalam kasus ini dicoba untuk ditampilkan dalam MessageBox.

Perlu diketahui bahwa struktur SqlDataReader mengikuti hasil resultset. Oleh karena itu, untuk meyakinkan diri bagaimana struktur SqlDataReader, jalankan query dalam lingkungan SQL Server terlebih dahulu.

Karena strukturnya yang mengikuti resultset, maka dalam contoh, NID berada pada kolom pertama (index ke-0) dan nama berada pada kolom kedua (index ke-1). Sehingga untuk mengambil data tersebut dalam SqlDataReader, hanya diperlukan index kolom, menjadi reader(0) untuk NID dan reader(1) untuk nama.

Manipulasi Data

Manipulasi data meliputi insert, update, dan delete. Ketiga hal ini disebut sebagai bahasa manipulasi data (data manipulation language – DML). Sedikit berbeda dengan pengambilan data, untuk manipulasi, server tidak mengembalikan resultset. Server hanya mengembalikan berapa jumlah data yang terpengaruh oleh proses manipulasi. Oleh karena itu, tidak diperlukan SqlDataReader. Tetapi hanya dibutuhkan sebuah variabel yang mampu menampung angka (contoh: integer).



SqlCommandDatabase

Gambar 7.5 Prosedur Manipulasi Data

Prosedur yang dipakai untuk melakukan manipulasi data lebih sederhana dibandinkan dengan prosedur untuk pengembalian data.

Untuk pembuatan command, masih sama seperti pengambilan data. Hanya saja ada sedikit perbedaan isi untuk fungsi comm.CommandText. Jika pada pengambilan data menggunakan perintah SELECT, maka untuk manipulasi data menggunakan perintah INSERT, UPDATE, atau DELETE. Perhatikan kode program berikut ini:

Kode 7.13

comm.CommandText = _

"INSERT INTO DOSEN " & _

"VALUES ('110011','Bunga')"

Setelah comm.CommandText siap, jalankan query tersebut. Ada perbedaan fungsi yang dipakai untuk menjalankan query. Jika pada pengambilan data menggunakan ExecuteReader, maka pada manipulasi data menggunakan ExecuteNonQuery. Perhatikan kode program berikut ini:

Kode 7.14

'tampung

Dim affectedrows As Integer

affectedrows = comm.ExecuteNonQuery()

Setelah ditampung, nilai dari affectedrows adalah jumlah baris yang dipengaruhi oleh sintaks DML, yang dalam hal ini adalah INSERT, yang nilainya adalah 1.



Latihan

1. Buat aplikasi untuk mengelola data mahasiswa. 2. Buat form seperti dibawah ini:

Kontrol: - 4 buah Textbox: txtNIM, txtNama, txtAlamat, txtKota - 2 buah Radiobutton Jenis Kelamin: rbtPria, rbtWanita - 2 buah Radiobutton Status Nikah: rbtMenikah,

rbtBelumMenikah - 4 buah Button: btnCari, btnBatal, btnSimpan, btnKeluar Variabel: - statusPencarian as Boolean

3. btnCari digunakan untuk melakukan pencarian data berdasarkan NIM yang diinputkan pada txtNIM.

4. Jika data yang dicari ada, maka tampilkan nama pada txtNama, alamat pada txtAlamat, dan kota pada txtKota. Jika jenis kelamin mahasiswa terisi P maka rbtPria harus dipilih, dan sebaliknya. Jika status nikah mahasiswa terisi M maka rbtMenikah harus dipilih, dan sebaliknya. Isi statusPencarian dengan nilai TRUE.



5. Jika data tidak ada, maka tampilkan pesan:

Isi statusPencarian dengan nilai FALSE.

6. btnBatal digunakan untuk menghapus isi seluruh Textbox dan mengembalikan posisi centang Radiobutton pada rbtPria dan rbtMenikah.

7. btnSimpan digunakan untuk menyimpan data. Untuk proses simpan ini, perhatikan statusPencarian. Jika statusPencarian bernilai FALSE, maka btnSimpan berisi perintah untuk melakukan INSERT INTO. Jika statusPencarian bernilai TRUE, maka btnSimpan berisi perintah untuk melakukan update. NB: Perhatikan juga pilihan centang pada Radiobutton.

8. btnKeluar digunakan untuk keluar dari aplikasi.



(halaman kosong)

BAB VIII

Dataset

Sub-Materi: Dataset

Membuat Dataset Manipulasi Data

Pencarian dan Penyortiran Data Dataset dan Data Source

Dataset


(halaman kosong)

Dataset


Bab VIII

Dataset

Sub-Materi: Dataset

Membuat Dataset Manipulasi Data

Pencarian dan Penyortiran Data Dataset dan Data Source

Dataset

Pada Bab sebelumnya dibutuhkan SqlDataReader, dengan sifatnya yang read-only dan forward-only, untuk menampung resultset. Penggunaan SqlDataReader tidak dapat memberikan kemampuan dalam eksplorasi data. Pada aplikasi yang membutuhkan tingkat proses data yang besar dengan tetap memperhitungkan performa, mobilitas, dan portabilitas, diperlukan sebuah obyek yang tidak hanya mampu menampung tetapi juga melakukan manipulasi di lingkungan lokal (aplikasi).

Gambar 8. 1 Struktur Dataset

Dataset


Untuk menjawab tantangan ini, peran SqlDataReader harus digantikan oleh dataset. Dataset dibentuk sebagai basis data temporer dalam aplikasi. Struktur dataset dapat dilihat pada Gambar 8. 1. Seperti dalam struktur basis data, dataset juga dibentuk oleh (banyak) tabel, yang dalam hal ini adalah datatable, dan oleh relasi. Setiap tabel disusun oleh kolom (atribut/field) dan baris (data) yang masing-masing direpresentasikan oleh datacolumn dan datarow.

Ada banyak cara dalam membuat dataset, antara lain:

1. Dibuat dari datatables dan relations, lalu mempopulasikan datanya.

2. Dibuat dengan cara menyalin dari basis data (data provider) menggunakan data adapter, yang dalam hal ini adalah SQLDataAdapter.

3. Membaca dari file XSD dan/atau XML. 4. Membuat dari command dan data reader.

Membuat Dataset

Perhatikan kode program berikut ini:

Kode 8.1 'variabel untuk membentuk dataset PBD

Dim dsPBD As DataSet

'variabel untuk membentuk tabel Mahasiswa

Dim dtMahasiswa As DataTable

Dim colNIM As DataColumn

Dim colNama As DataColumn

Dim colAlamat As DataColumn

Dim colKota As DataColumn

Dim colJnsKel As DataColumn

Dim colStsNkh As DataColumn

Dim pkMhs() As DataColumn 'untuk membentuk

'primary key

Private Sub Form1_Load(ByVal sender As

System.Object, ByVal e As System.EventArgs)

Handles MyBase.Load

'pembentukan basis data PBD

dsPBD = New DataSet("PBD")

Dataset


'pembentukan tabel Mahasiswa

dtMahasiswa = New DataTable("Mahasiswa")

'pembentukan kolom untuk tabel Mahasiswa

colNIM = New DataColumn("NIM")

colNIM.DataType = Type.GetType("String")

colNIM.MaxLength = 11

colNama = New DataColumn("Nama")

colNama.DataType = Type.GetType("String")

colNama.MaxLength = 100

colNama.AllowDBNull = False

colAlamat = New DataColumn("Alamat")

colAlamat.DataType = Type.GetType("String")

colAlamat.MaxLength = 100

colKota = New DataColumn("Kota")

colKota.DataType = Type.GetType("String")

colKota.MaxLength = 50

colJnsKel = New DataColumn("Jns_Kelamin")

colJnsKel.DataType = Type.GetType("String")

colJnsKel.MaxLength = 1

colJnsKel.AllowDBNull = False

colStsNkh = New DataColumn("Sts_Nikah")

colStsNkh.DataType = Type.GetType("String")

colStsNkh.MaxLength = 1

colStsNkh.AllowDBNull = False

'memasukkan kolom kedalam tabel Mahasiswa

dtMahasiswa.Columns.Add(colNIM)

dtMahasiswa.Columns.Add(colNama)

dtMahasiswa.Columns.Add(colAlamat)

dtMahasiswa.Columns.Add(colKota)

dtMahasiswa.Columns.Add(colJnsKel)

dtMahasiswa.Columns.Add(colStsNkh)

'pembentukan primary key pada tabel Mhs

pkMhs = New DataColumn() {colNIM}

dtMahasiswa.PrimaryKey = pkMhs

'memasukkan tabel kedalam dataset

dsPBD.Tables.Add(dtMahasiswa)

End Sub

Dataset


Bagaimana cara kerjanya

Pertama, buat variabel-variabel yang akan dibutuhkan dalam pembentukan dataset. Variabel-variabel ini mengikuti struktur dataset, antara lain DataSet, DataTable, dan DataColumn.

Kode 8.2

'variabel untuk membentuk dataset PBD

Dim dsPBD As DataSet

'variabel untuk membentuk tabel Mahasiswa

Dim dtMahasiswa As DataTable

Dim colNIM As DataColumn

Dim colNama As DataColumn

Dim colAlamat As DataColumn

Dim colKota As DataColumn

Dim colJnsKel As DataColumn

Dim colStsNkh As DataColumn

Dim pkMhs() As DataColumn 'untuk membentuk

'primary key

Ada variabel pkMhs bertipe array of DataColumn. Variabel ini digunakan untuk membentuk primary key. Primary key dapat berupa singular atau composite. Oleh karena itu, pkMhs berbentuk array.

Sesuai dengan struktur dataset, didalam Form_Load lakukan pengkonstruksian dataset. Konstruksi dataset hanya membutuhkan nama dataset.

Kode 8.3 'pembentukan basis data PBD

dsPBD = New DataSet("PBD")

Setelah itu, lakukan pengkonstruksian tabel. Konstruksi tabel hanya membutuhkan nama tabel. Umumnya, tabel berada dalam dataset. Namun pada kode program ini tabel terpisah dari dataset. Proses memasukkan tabel kedalam dataset dapat dilakukan setelah struktur tabel selesai dibuat.

Kode 8.4

'pembentukan tabel Mahasiswa

dtMahasiswa = New DataTable("Mahasiswa")

Dataset


Setelah tabel dibuat, populasikan kolom-kolom sebagai atribut tabel.

Kode 8.5 'pembentukan kolom untuk tabel Mahasiswa

colNIM = New DataColumn("NIM")

colNIM.DataType = _

Type.GetType("System.String")

colNIM.MaxLength = 11

colNama = New DataColumn("Nama")

colNama.DataType = _


colNama.MaxLength = 100

colNama.AllowDBNull = False

colAlamat = New DataColumn("Alamat")

colAlamat.DataType = _


colAlamat.MaxLength = 100

colKota = New DataColumn("Kota")

colKota.DataType = _


colKota.MaxLength = 50

colJnsKel = New DataColumn("Jns_Kelamin")

colJnsKel.DataType = _


colJnsKel.MaxLength = 1

colJnsKel.AllowDBNull = False

colStsNkh = New DataColumn("Sts_Nikah")

colStsNkh.DataType = _


colStsNkh.MaxLength = 1

colStsNkh.AllowDBNull = False

Pembentukan kolom dilakukan satu per satu. Dalam setiap pembentukan kolom, ditentukan juga tipe data dan panjang kolom. Selain itu, dapat ditentukan juga constraint yang membentuk nilai kolom tersebut. Seperti pada pembentukan kolom colNama yang harus diisi. Sehingga diperlukan properti AllowDBNull dan diisi dengan False. Beberapa constraint lain dapat juga diberikan pada kolom, seperti AutoIncrement untuk

Dataset


pembentukan sequence, DefaultValue untuk pemberian nilai default, dan Unique sebagai aturan dalam pemberian nilai.

Untuk pembentukan primary key constraint, diperlukan beberapa langkah. Yang pertama adalah membuat variabel array of DataColumn yang telah dibahas pada pembuatan variabel. Yang kedua adalah membentuk instance pkMhs() dan mengisinya dengan kolom-kolom yang dipakai untuk membentuk primary key. Dalam contoh, hanya memakai kolom NIM. Untuk banyak kolom, dapat dilakukan dengan menggunakan kode (contoh) {colNIM, colNama,

colAlamat}. Dengan menggunakan properti PrimaryKey, pkMhs menjadi constraint dalam tabel dtMahasiswa.

Kode 8.6

pkMhs = New DataColumn() {colNIM}

dtMahasiswa.PrimaryKey = pkMhs

Sebagai langkah terakhir, masukkan tabel kedalam dataset.

Kode 8.7

dsPBD.Tables.Add(dtMahasiswa)

Dengan selesainya langkah terakhir, struktur yang didapatkan dari seluruh baris kode pada Kode 8.1 menjadi seperti berikut ini:

Gambar 8.2 Struktur dsPBD

Database: PBD (dsPBD)

Tabel: Mahasiswa (dtMahasiswa)

• Kolom: NIM (colNIM)

• Kolom: Nama (colNama)

• Kolom: Alamat (colAlamat)

• Kolom: Kota (colKota)

• Kolom: Jns_Kelamin (colJnsKel)

• Kolom: Sts_Nikah (colStsNkh)

Dataset


Akses terhadap data, dapat dilakukan melalui tabel dtMahasiswa ataupun dari dataset dsPBD.Tables("Mahasiswa"). Sedangkan untuk populasi kolomnya, dapat dilakukan melalui colNIM dan sejenisnya ataupun dari dtMahasiswa.Columns("NIM") dan sejenisnya. Lebih lanjut mengenai data dapat dilihat pada sub-bab Populasi Data dalam Dataset.

Manipulasi Data

Sama seperti basis data, dataset juga memiliki kemampuan untuk CRUD (Create-Read-Update-Delete) data. Sesuai dengan struktur dataset, data hasil CRUD disimpan didalam datatable, yang dalam hal ini adalah dtMahasiswa. Sedangkan segala bentuk operasi terhadap data, harus menggunakan obyek datarow. Dalam mengoperasikannya, setiap datarow akan memiliki field (kolom) sesuai dengan tabelnya, dan dengan urutan yang sama.

Create

Operasi create menambahkan data kedalam datatable. Untuk proses create, perhatikan kode program berikut ini:

Kode 8.8

Dim rowMahasiswa As DataRow = _

dtMahasiswa.NewRow

rowMahasiswa("NIM") = "10410104057"

rowMahasiswa("Nama") = "Rahmatulloh"

rowMahasiswa("Alamat") = _

"Perum. Perak Barat 50A"

rowMahasiswa("Kota") = "Surabaya"

rowMahasiswa("Jns_Kelamin") = "P"

rowMahasiswa("Sts_Nikah") = "B"

dtMahasiswa.Rows.Add(rowMahasiswa)

Pada Kode 8.8, sebelum dapat memasukkan data, harus dibuat dulu rowMahasiswa dari dtMahasiswa. Dengan demikian,

Dataset


rowMahasiswa terdiri dari satu buah baris kosong dengan struktur kolom dan constraint yang sama dengan dtMahasiswa.

Langkah selanjutnya adalah memasukkan nilai-nilai pada setiap kolom dalam rowMahasiswa. Untuk memasukkan nilai-nilai ini, dibutuhkan zero-based columnIndex atau nama kolom.

Setelah seluruh nilai dimasukkan kedalam rowMahasiswa, langkah terakhir adalah memasukkan rowMahasiswa kedalam tabel dtMahasiswa. Hal ini dapat dilakukan dengan menggunakan metode Add.

Selain menggunakan cara entri satu per satu, populasi data dalam dataset dapat dilakukan dengan cara mengambil data dari basis data dan dimasukkan kedalam dataset. Untuk cara ini, dapat dilihat pada sub-bab Dataset dan Data Source.

Read

Operasi read melakukan pembacaan data dari datatable. Untuk proses read, perhatikan kode program berikut ini:

Kode 8.9


dtMahasiswa.Rows(0)

txtNIM.Text = rowMahasiswa("NIM")

txtNama.Text = rowMahasiswa("Nama")

txtAlamat.Text = rowMahasiswa("Alamat")

txtKota.Text = rowMahasiswa("Kota")

If rowMahasiswa("Jns_Kelamin") = "P" Then

txtJns_Kelamin.Text = "Pria"

Else

txtJns_Kelamin.Text = "Wanita"

End If

If rowMahasiswa("Sts_Nikah") = "M" Then

txtSts_Nikah.Text = "Menikah"

Else

txtSts_Nikah.Text = "Belum Menikah"

End If

Dataset


Pada Kode 8.9, rowMahasiswa diisi dengan baris pertama dari dtMahasiswa. Untuk mengambil data baris lainnya, hanya perlu mengganti nilai zero-based rowIndex pada dtMahasiswa.Rows. Dengan demikian, rowMahasiswa mempunyai struktur kolom, constraint, dan data yang sama dengan baris pertama dtMahasiswa.

Langkah selanjutnya adalah menampilkan setiap nilai didalam rowMahasiswa kedalam control-control didalam form. Untuk kolom-kolom NIM, nama, alamat, dan kota dapat ditampilkan langsung kedalam Textbox. Sedangkan untuk menampilkan nilai jenis kelamin kedalam Radiobutton, diperlukan pre-processing untuk mengecek nilainya dan memadukan nilai tersebut ke Radiobutton yang sesuai.

Zero-based rowIndex selain digunakan untuk operasi read, juga digunakan pada operasi update dan delete. Hal ini menjadi penting mengingat tidak semua data harus di-update atau di-delete.

Update

Operasi update mengubah data didalam datatable. Untuk proses update, perhatikan kode program berikut ini:

Kode 8.10


dtMahasiswa.Rows(0)

rowMahasiswa.BeginEdit()

rowMahasiswa("Nama") = "Rahmatulloh"

rowMahasiswa("Alamat") = "Jl. A. Yani 123"

rowMahasiswa("Kota") = "Malang"

rowMahasiswa("Jns_Kelamin") = "P"

rowMahasiswa("Sts_Nikah") = "B"

rowMahasiswa.EndEdit()

Pada Kode 8.10, pengubahan dilakukan pada data pertama didalam datatable. Oleh karena itu, digunakan zero-based

Dataset


rowIndex untuk mengambil data tersebut dan mengisikannya kedalam rowMahasiswa.

Langkah selanjutnya adalah mengubah nilai pada setiap kolom dalam rowMahasiswa. Perubahan dianjurkan dilakukan hanya pada kolom-kolom selain primary key. Sebelum mengubah nilai, status rowMahasiswa harus diubah kedalam mode edit. Pengubahan ini dilakukan dengan metode BeginEdit. Setelah mengubah nilai, perubahan harus disimpan menggunakan metode EndEdit.

Delete

Operasi delete menghapus data didalam datatable. Untuk proses delete, perhatikan kode program berikut ini:

Kode 8.11


dtMahasiswa.Rows(0)

dtMahasiswa.Rows.Remove(rowMahasiswa)

Pada Kode 8.11, penghapusan dilakukan pada data pertama didalam datatable. Oleh karena itu, digunakan zero-based rowIndex untuk mengambil data tersebut dan mengisikannya kedalam rowMahasiswa.

Langkah selanjutnya adalah menghapus baris data pada dtMahasiswa yang sesuai dengan rowMahasiswa. Penghapusan ini dilakukan secara langsung melalui metode Remove.

Kontrol pada Transaksi

Setiap data yang ada didalam datatable mempunyai 8 status yang menyatakan antara lain, data hasil penambahan, data hasil modifikasi, data hasil penghapusan, dll. Oleh karena itu, untuk meyakinkan bahwa seluruh data disimpan, dibutuhkan commit. Perhatikan kode program berikut ini:

Kode 8. 12 dsPBD.Tables(0).AcceptChanges()

Dataset


Sedangkan jika dari seluruh perubahan data yang terjadi didalam datatable tidak ingin di-commit, dapat dilakukan rollback. Perhatikan kode program berikut ini:

Kode 8. 13

dt.RejectChanges()

Pencarian dan Penyortiran Data

Pada dasarnya, proses pencarian dilakukan dengan bantuan primary key. Namun hal tersebut terkadang tidak berlaku untuk beberapa kasus. Dari tiga cara dalam pencarian data, metode Find dan Contains membutuhkan bantuan primary key, sedangkan metode Select tidak.

Proses yang diperlukan untuk melakukan Find dan Contains adalah SAMA. Yang berbeda hanyalah hasil dari keduanya. Metode Find menghasilkan DataRow sedangkan metode Contains menghasilkan Boolean.

Find Find dengan primary key tunggal:

Kode 8.14

Dim row As DataRow = _

dt.Rows.Find("05410104001")

If Not IsNothing(row) Then 'datanya ketemu

MessageBox.Show("Data Ada.")

Else

MessageBox.Show("Data Tidak Ada.")

End If

Find dengan primary key jamak:

Kode 8.15

Dim kriteria(2) As Object

kriteria(0) = "05410104001"

kriteria(1) = "Alif"

Dim row As DataRow = dt.Rows.Find(kriteria)

If Not IsNothing(row) Then

...

Dataset


Dataset


Contains Contains dengan primary key tunggal:

Kode 8.16 Dim statusCari as Boolean = _

dt.Rows.Contains("05410104001")

If statusCari = True Then


Else


End If

Contains dengan primary key jamak:

Kode 8.17

Dim kriteria(2) As Object

kriteria(0) = "05410104001"

kriteria(1) = "Alif"

Dim statusCari As Boolean = _

dt.Rows.Contains(kriteria)

If statusCari = True Then


Else


End If

Select Untuk metode Select, hasil berupa array of DataRow:

Kode 8.18 Dim row() As DataRow = _

dt.Select("KOTA LIKE 'S%' AND STS_NIKAH='M'")

If row.Length > 0 Then


Else


End If

Penggunaan metode SELECT pada DataTable sama seperti penggunaan kata kunci WHERE pada perintah SELECT (PL/SQL).

Dataset


DataView

DataView sama seperti View dalam obyek basis data (selain Table, User, Function, dll). Keuntungan dari penggunaan DataView adalah pencarian dan pengurutan yang langsung ter-binding dengan data bound control (contoh: DataGridView).

Kita bisa membuat DataView dari DataTable dengan menggunakan kode program berikut:

Kode 8.19

Dim dv As DataView = dt.DefaultView

Pencarian data pada DataView bisa menggunakan RowFilter, Find dan FindRows. Sedangkan untuk pengurutan data menggunakan Sort.

Gambar 8.3 DataView dalam DataTable

RowFilter Kode 8.20

dv.RowFilter = "Kota = 'Surabaya'"

Untuk Find dan FindRows, DataView harus diurutkan (sort) terlebih dahulu. Metode Find menghasilkan nilai Integer (index baris atau -1 jika tidak ada), sedangkan FindRows menghasilkan array of DataRowView.

Sort Kode 8.21 dv.Sort = "NIM ASC"

Penggunaan SORT pada DataView sama seperti penggunaan kata kunci ORDER BY pada perintah SELECT (PL/SQL).

Dataset


Find Find dengan kriteria tunggal:

Kode 8.22 Dim hasilCari As Integer = _

dv.Find("05410104001")

If hasilCari >= 0 Then


Else


End If

Find dengan kriteria jamak, DataView harus diurutkan berdasarkan kriteria:

Kode 8.23

dv.Sort = "NIM ASC, Kota ASC"

Dim kriteria(2) As String

kriteria(0) = "01410100077"

kriteria(1) = "Surabaya"

Dim hasilCari As Integer = dv.Find(kriteria)

If hasilCari >= 0 Then


Else


End If

FindRows Sebelum melakukan FindRows, dilakukan pengurutan sesuai kriteria yang akan di FindRows.

Kode 8.24

dv.Sort = "Kota"

Dim rowView() As DataRowView

rowView() = dv.FindRows("Surabaya")

If rowView.Length > 0 Then


Else


End If

Dataset


Dataset dan Data Source

Selain membuat dataset dan mempopulasikan datanya secara manual, dataset juga dapat dibentuk menggunakan data adapter. Cara ini dapat dilakukan jika ada tabel (dalam sebuah basis data, yg dalam praktikum ini adalah basis data relasional) yang ingin disalin (copy) struktur dan datanya, dan disimpan (paste) didalam dataset.

Untuk pembentukan dataset melalui data adapter, diperlukan koneksi. Oleh karena itu, dibuat koneksi kedalam basis data. Jika pada Bab VII sub-bab Connections, disebutkan bahwa koneksi perlu dibuka dan ditutup. Pada data adapter, buka dan tutup koneksi dilakukan secara otomatis didalam data adapter. Sehingga tidak perlu dituliskan conn.Open() dan conn.Close().

Setelah koneksi dibuat, langkah selanjutnya adalah membuat data adapter. Data adapter, agar dapat digunakan, dibentuk seminimalnya oleh dua hal, yaitu kueri SELECT dan koneksi. Pada Kode 8.25, kueri yang akan dijalankan oleh data adapter adalah kueri untuk mengambil seluruh data mahasiswa.

Kode 8.25





Dim conn As SqlConnection

conn = New SqlConnection(connectionString)

Dim adapter As SqlDataAdapter

Dim strQuery As String = _

"SELECT * FROM MAHASISWA"

adapter = New SqlDataAdapter(strQuery, conn)

adapter.Fill(dsPBD, "Mahasiswa")

adapter.Dispose()

Data adapter menjalankan kueri menggunakan metode Fill. Metode ini menjalankan dua proses. Pertama adalah eksekusi kueri, dan kedua adalah menampung recordset kedalam dataset.

Dataset


Penampungan recordset kedalam dataset seperti pada Kode 8.25 akan membuat datatable bernama Mahasiswa. Dengan demikian, datatable Mahasiswa akan mempunyai struktur kolom dan data sama seperti hasil kueri SELECT. Sesuai dengan contoh, datatable Mahasiswa mempunyai struktur kolom dan data yang sama seperti pada tabel Mahasiswa didalam basis data PBD (SQL Server).

Model pemrograman basis data yang menggunakan data adapter seperti ini biasa disebut dengan lingkungan pemrograman terputus (disconnected environment). Pada model ini, data yang sudah diambil dari basis data disalin kedalam dataset. Pengolahan dilakukan dilingkungan dataset tanpa mengubah data yang ada didalam basis data. Untuk pengolahan data seperti create, update, dan delete dapat dilihat pada sub-bab Manipulasi Data.

DataGridView

Datagridview menyediakan antar muka data secara visual. Datagridview merupakan cara terbaik untuk menampilkan dan melakukan pengubahan terhadap data. Datagridview berbentuk tabel yang dapat dimodifikasi sesuai dengan kebutuhan tampilan data. Datagridview memiliki kemampuan binding kedalam data. Sehingga create, update, dan delete data dapat dilakukan tanpa bantuan kode program sama sekali.

Untuk dapat menampilkan (bind) data kedalam datagridview, perhatikan kode program berikut ini:

Kode 8.26 DataGridView1.DataSource = dsPBD.Tables(0)

Pada Kode 8.26, disebutkan bahwa untuk dapat melakukan binding data, hanya membutuhkan metode DataSource pada datagridview. Sesuai contoh, yang dapat dibinding oleh datagridview adalah datatable.

Dataset


Update

Untuk menyimpan perubahan dataset/datatable kedalam basis data, kita hanya perlu metode Update dari object data adapter. Namun sebelumnya, kita harus membuat InsertCommand, UpdateCommand dan DeleteCommand. Perhatikan kode program berikut ini:

Kode 8. 27

Dim cb As New SqlCommandBuilder(adapter)

adapter.Update(dtMahasiswa)

Namun untuk dapat membentuk command-command tersebut, datatable harus memiliki primary key. Oleh karena itu, sebelum melakukan Fill, tuliskan kode program berikut ini:

adapter.MissingSchemaAction = _

MissingSchemaAction.AddWithKey

Untuk langkah-langkah detil dari proses Update itu sendiri sebenarnya ada 3 proses. Perhatikan kode program berikut ini:

Kode 8. 28 'Delete

da.Update(dt.Select(Nothing, Nothing, _

DataViewRowState.Deleted))

'Update


DataViewRowState.ModifiedCurrent))

'Insert


DataViewRowState.Added))

Dataset


Latihan

1. Buat aplikasi untuk mengelola data dosen. 2. Buat form seperti dibawah ini:

Kontrol: - 1 buah Datagridview: dgvDosen - 2 buah Textbox: txtNID, txtNama - 3 buah Button: btnTambah, btnUbah, btnKeluar Variabel: - dtDosen as DataTable

3. Ketika form di-load, ambil data dosen dari basis data PBD dan masukkan kedalam dtDosen. Tampilkan dtDosen kedalam dgvDosen.

4. btnTambah digunakan untuk menambahkan data kedalam dtDosen.

5. btnUbah digunakan untuk mengubah data kedalam dtDosen. Sebelum dilakukan penambahan, lakukan pencarian data dosen berdasarkan NID, kedalam dtDosen. Anggap data selalu ada.

6. btnKeluar digunakan untuk keluar dari aplikasi.

mk. praktikum -...

Documents