MK. PRAKTIKUM. Capaian: Mahasiswa mampu menerapkan konsep-konsep pemrograman basis data

Capaian: Mahasiswa mampu menerapkan konsep-konsep pemrograman basis data. Materi:

MK. PRAKTIKUM

Tegar Heru Susilo 2014 Laboratorium Komputer STIMIK STIKOM Surabaya

 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

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 i

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 ii

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 iii

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

iv

BAB I

SQL (sekuel) Sub-Materi:  Select  Join  Subquery  DML

SQL (sekuel)

(halaman kosong)

Praktikum Pemrograman Basis Data

pg. 2

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 Praktikum Pemrograman Basis Data

pg. 3

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


pg. 4

SQL (sekuel) 2.

3. 4.

Login:

Isi “Server name:” dengan “.\SQLEXPRESS” – pilih mode “Windows Authentication” – klik tombol Connect. Menu File – Open – File atau Ctrl+O. Buka script yang telah diunduh. Lalu jalankan script melalui tombol 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 Praktikum Pemrograman Basis Data

pg. 5

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.


pg. 6

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

3. 4. 5.

SELECT.

Diikuti oleh <select_list>, dapat berupa literal_value atau column_list atau asterisk (*). FROM. Diikuti oleh 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. WHERE. Diikuti oleh kondisi secara umum. GROUP BY. Diikuti oleh <select_list>. Bagian ini muncul ketika ada fungsi-fungsi agregasi. HAVING. Diikuti oleh kondisi hanya untuk fungsi-fungsi agregasi.


pg. 7

SQL (sekuel) 6.

ORDER BY. Diikuti oleh <select_list>. Kode 1.1 SELECT [FROM [WHERE [GROUP BY [HAVING [ORDER BY

<select_list> ] [AND/OR ]] <select_list>] [AND/OR ]] <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 FROM

1 '1', 'STIKOM Surabaya' STIKOM, nim, nim + '@stikom.edu' EMAIL mahasiswa;

Hasil: 1 1 1 .. 1

STIKOM STIKOM Surabaya STIKOM Surabaya … STIKOM Surabaya

Nim 05390102208 05410104001 … 07410104800


EMAIL [email protected] [email protected] … [email protected]

pg. 8

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 FROM

nim 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 FROM

DISTINCT nim 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 WHERE

* mahasiswa nama = 'Alif';

Hasil: nim 05410104001

nama Alif

alamat Jl. Jagir 20


kota Surabaya

jns_kel.. P

sts_nikah B

pg. 9

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 = > >= < <= <> atau != BETWEEN .. AND .. IN (set) LIKE IS NULL

Keterangan Sama dengan Lebih besar dari Lebih besar sama dengan Kurang dari Kurang dari sama dengan Tidak sama dengan Diantara 2 nilai Cocok dengan salah satu diantara daftar nilai Cocok dengan pola karakter Sama dengan NULL

Beberapa contoh penggunaan operator pembanding dapat dilihat pada query berikut: Kode 1.7 SELECT FROM WHERE SELECT

* mahasiswa nim BETWEEN '05390102208' AND '05410104190'; * FROM mahasiswa


pg. 10

SQL (sekuel) WHERE

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

SELECT WHERE

* FROM mahasiswa naam LIKE '%amb%';

SELECT WHERE

* FROM mahasiswa 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 WHERE

* mahasiswa 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 FROM WHERE

nim, nama, kota mahasiswa kota='Surabaya' and nim='05390102208';

Hasil: nim 05390102208

nama Cinta

kota Surabaya


pg. 11

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 1 2 3

Keterangan NOT AND 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 FROM WHERE ORDER BY

nim, kode_mk, uas nilai nid='010103' uas DESC;

Hasil:


pg. 12

SQL (sekuel) nim 05390102208 06390102666 07390102802

kode_mk MK-001 MK-001 MK-001

uas 80 40 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 FROM

uas * 0.3 nilai;

SELECT FROM

tugas / 4 nilai;


pg. 13

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 FROM

AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai;

Hasil: rata_nilai 49.41

nilai_tertinggi 90

nilai terendah 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 FROM GROUP BY

kode_mk, AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai kode_mk;


pg. 14

SQL (sekuel) Hasil: kode_mk MK-001 MK-002 MK-003 MK-102 MK-103

rata_nilai 46.66 43.33 54.00 65.00 42.00

nilai_tertinggi 80 70 90 80 80

nilai terendah 20 0 0 50 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.

2. 3.

Jika ada agregasi (SUM, MIN, MAX, COUNT, AVG) dalam <select_list>, kolom tanpa agregasi harus terdaftar dalam GROUP BY. Dengan menggunakan WHERE, kita bisa mengabaikan barisbaris tertentu sebelum dilakukan agregasi dan pengelompokan. 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 SELECT FROM GROUP BY HAVING

kode_mk, AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai kode_mk AVG(uas)>50;

Hasil: kode_mk MK-003 MK-102

rata_nilai 54 65

nilai_tertinggi 90 80


nilai terendah 0 50

pg. 15

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 SELECT FROM WHERE GROUP BY HAVING

kode_mk, AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai kode_mk = 'MK-003' kode_mk AVG(uas)>50;

Hasil: kode_mk MK-003

rata_nilai 54

nilai_tertinggi 90

nilai terendah 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 Praktikum Pemrograman Basis Data

pg. 16

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 , [, ...] WHERE [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 Cinta Cinta Alif … Gala

kode_mk MK-001 MK-002 MK-002 … MK-103

uas 80 0 70 … 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 SELECT <select_list> FROM JOIN ON < table1.PK = table2.FK> [[AND ...] JOIN ...];


pg. 17

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 [OUTER] JOIN ON [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 Praktikum Pemrograman Basis Data

pg. 18

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 Cinta Cinta … Indah Gala Gala

kode_mk MK-001 MK-002 … NULL MK-003 MK-103

Uas 80 0 … NULL 0 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';


pg. 19

SQL (sekuel) Hasil: Namaproduk Kemeja Lengan Panjang Jepit Rambut

hargapersatuan 25000.00 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 SELECT <select_list> FROM WHERE = (SELECT <single_column> FROM WHERE );

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 Menghasilkan data yang sama berbeda. Solusi subquery pada dahulu angkatan si Entin. Hasil sebagai acuan untuk main query.


subquery dari Kode 1.23. namun dengan solusi yang Kode 1.25 mencari terlebih dari pencarian ini digunakan

pg. 20

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.

2.

3.

4.

5.

Aggregation Mengembalikan nilai tunggal yang merepresentasikan sebuah agregasi dari sejumlah nilai. Macam: AVG(), MAX(), COUNT(), MIN(), SUM() 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() String Manipulation Digunakan untuk mengubah, mengganti, dan memanipulasi karakter. Macam: LEN(), SUBSTRING(), UPPER(), LOWER(), LEFT(), RIGHT(), REPLACE() Mathematical Digunakan untuk operasi matematis, mulai dari algebra, trigonometri, statistik, peramalan, dan bahkan operasi finansial. Macam: CEILING(), FLOOR(), ROUND(), POWER(), SQRT() 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 masingmasing untuk pembulatan keatas, pembulatan kebawah, pembulatan berdasarkan nilai dibelakang koma (sesuai dengan tingkat presisi yg dipakai). GETDATE digunakan untuk mengambil Praktikum Pemrograman Basis Data

pg. 21

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

FROM

LEN(nama), SUBSTRING(nama, 5, 2), UPPER(nama), LOWER(nama), LEFT(nama, 5), RIGHT(nama, 5) 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 THEN <statement> [WHEN THEN <statement>] [WHEN 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.


pg. 22

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 halhal 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 [()] VALUES ();

Pada Kode 1.29, 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. Praktikum Pemrograman Basis Data

pg. 23

SQL (sekuel) Kode 1.29 INSERT INTO mahasiswa VALUES ('08410104003', 'Jenny', 'Jl. Krembangan 50', 'Surabaya', 'W', 'B'); Kode 1.30 INSERT INTO mahasiswa (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 SET = [, = ] WHERE ;

Perhatikan contoh berikut: Kode 1.32 UPDATE SET WHERE

mahasiswa sts_nikah = 'M' 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. Praktikum Pemrograman Basis Data

pg. 24

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 WHERE ;

Contoh sederhananya adalah sebagai berikut: Kode 1.34 DELETE WHERE

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


pg. 25

SQL (sekuel)

Latihan 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

13. 14.

Tampilkan seluruh data mahasiswa. Tampilkan data mahasiswa yang tinggal di kota Surabaya. Tampilkan nama mahasiswa yang mempunyai huruf a atau huruf A. Tampilkan nama mahasiswa yang huruf kedua terakhir dari namanya mempunyai huruf n. Tampilkan nim, nama dan nilai tugas mahasiswa. Tampilkan 3 karakter terakhir dari nama mahasiswa. Buat email berdasarkan nama mahasiswa dan gabungkan dengan @gmail.com. Hitung jumlah mahasiswa masing-masing dosen. Ambil datanya dari tabel nilai. Tampilkan nama mahasiswa dan nama dosen untuk dosen dengan nid 010306. Tampilkan mahasiswa yang mempunyai dosen yang sama dengan Entin. Tampilkan mahasiswa yang tidak memiliki nilai. 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 Ubah status nikah Anda dari M menjadi B. Hapus data Anda.


pg. 26

BAB II

Tipe Data dan Variabel Sub-Materi:  Script dan Script Block  Tipe Data  Variabel

Tipe Data dan Variabel

(halaman kosong)


pg. 28

Tipe Data dan Variabel 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 TSQL.

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 fungsifungsi (sistem dan user-defined) serta variabel (sistem dan userdefined) 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. Praktikum Pemrograman Basis Data

pg. 29

Tipe Data dan Variabel 2. 3.

MAX(iddepartemen) adalah salah satu system function

(aggregate function). Function secara umum dibahas di Bab 1, secara mendetil akan dibahas lebih pada Bab 5. @@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 Praktikum Pemrograman Basis Data

pg. 30

Tipe Data dan Variabel 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. Praktikum Pemrograman Basis Data

pg. 31

Tipe Data dan Variabel 2.

3. 4.

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. REAL. Tingkat presisi tinggi dengan range -3,40E+38 s.d 1,18E-38 dan +1,18E-38 s.d +3,40E+38. 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.

2.

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. 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. Praktikum Pemrograman Basis Data

pg. 32

Tipe Data dan Variabel 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.


pg. 33


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 @ [, @ [, @ ]];

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 @ ; DECLARE @ ; DECLARE @ ;

Contoh: Kode 2.5 DECLARE DECLARE DECLARE DECLARE

@jumlah NUMERIC (18, 0); @nama VARCHAR (50); @tgl DATETIME; @gambar IMAGE;

Pada Kode 2.5, ada empat variabel yang dibentuk yaitu: Praktikum Pemrograman Basis Data

pg. 34

Tipe Data dan Variabel 1. 2. 3. 4.

Variabel @jumlah bertipe data NUMERIK dengan panjang 18 dan tingkat presisi 0. Variabel @nama bertipe data VARCHAR dengan panjang 50. Variabel @tgl bertipe data DATETIME. Tanpa panjang karena memang tidak diperlukan. 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 dimasingmasing provider basis data. Untuk SQL Server, menggunakan @. 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. 2.

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

Penggunaan kata kunci SET hanya untuk pemberian nilai pada variabel, secara sederhana. Dalam artian, nilai yang diberikan telah diketahui sebelumnya. Kata kunci SELECT hanya digunakan ketika pengisian variabel menggunakan nilai hasil dari query. Dengan


pg. 35


3.

4. 5.

demikian, dalam satu query dapat dilakukan pemberian nilai pada beberapa variabel sekaligus. 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). PRINT digunakan untuk mencetak tulisan. 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 Praktikum Pemrograman Basis Data

pg. 36

Tipe Data dan Variabel 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 @@, sedangkan untuk Oracle ditandai dengan %. Dan pemakaian variabel ini hanya terjadi pada saat-saat spesifik saja. Beberapa system variables yang sering dipakai, antara lain: 1.

@@ROWCOUNT

Digunakan untuk menghitung dikembalikan oleh query. 2.

jumlah

baris

yang

@@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 PRINT

* mahasiswa; 'Jumlah baris = ' + CONVERT (VARCHAR, @@ROWCOUNT);

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

pg. 37

Tipe Data dan Variabel 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.

2. 3.

Operasi aritmatika. Operasi ini berupa penambahan, pengurangan, perkalian, dan pembagian. Operator aritmatika dan contoh penggunaannya dapat dilihat pada Bab 1. Operasi konkatenasi. Operasi ini berupa penggabungan karakter. Untuk menggabungkan karakter, bisa dibaca kembali Bab 1. 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.


pg. 38


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


pg. 39


(halaman kosong)


pg. 40

BAB III

Control-flow Statement Sub-Materi:  Percabangan  Perulangan

Control-flow Statement

(halaman kosong)


pg. 42

Control-flow Statement 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 seharihari, 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, apakah 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 Praktikum Pemrograman Basis Data

pg. 43

Control-flow Statement 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 SQL | <script block> [ELSE [IF ] 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. Praktikum Pemrograman Basis Data

pg. 44

Control-flow Statement 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 SET

mahasiswa 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:


pg. 45

Control-flow Statement Kode 3.4 IF YEAR(GETDATE())%4 = 0 AND YEAR(GETDATE())%200 != 0 BEGIN PRINT 'Tahun Kabisat'; 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 'Tahun Kabisat'; 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: Praktikum Pemrograman Basis Data

pg. 46

Control-flow Statement 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 PRINT PRINT PRINT ... PRINT

'1'; '2'; '3'; '4'; '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. Praktikum Pemrograman Basis Data

pg. 47

Control-flow Statement 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 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. Praktikum Pemrograman Basis Data

pg. 48


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 1 2

Hasil PRINT (didalam blok WHILE) Data ke-1 Nilai @counter = 2 Data ke-2 Nilai @counter = 3


pg. 49

Control-flow Statement Nilai @counter 3 ... 10 11

Hasil PRINT (didalam blok WHILE) Data ke-3 Nilai @counter = 4 ... Data ke-10 Nilai @counter = 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.


pg. 50

Control-flow Statement 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 0 1 … 4

5

… 8

Hasil WHILE Nilai @counter = 1 Kondisi @counter < 5 terpenuhi CONTINUE : Program kembali ke baris 1 blok WHILE Nilai @counter = 2 Kondisi @counter < 5 terpenuhi CONTINUE : Program kembali ke baris 1 blok WHILE … Nilai @counter = 5 Kondisi @counter < 5 tidak terpenuhi Kondisi @counter > 8 tidak terpenuhi Program mencetak nilai @counter = 5 Nilai @counter = 6 Kondisi @counter < 5 tidak terpenuhi Kondisi @counter > 8 tidak terpenuhi Program mencetak nilai @counter = 6 … Nilai @counter = 9 Kondisi @counter < 5 tidak terpenuhi


pg. 51

Control-flow Statement 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.


pg. 52


Latihan 1.

2. 3.

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. 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 Dari 1000 data nim yang telah dibentuk, masukkan datanya kedalam tabel mahasiswa. Untuk kolom lain, berikan data dummy.


pg. 53


(halaman kosong)


pg. 54

BAB IV

Cursor Sub-Materi:  Cursor

Cursor

(halaman kosong)


pg. 56

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


pg. 57

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 CURSOR FOR ;

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 ;

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 INTO ;


pg. 58

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 ; DEALLOCATE ;


pg. 59

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);

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 CLOSE cMhs; DEALLOCATE cMhs;


pg. 60

Cursor Hasil dari Kode 4.10 adalah sebagai berikut: 05390102208 05410104001 05410104190 06390102666 06390102880 06410104007 07390102802 07390102900 07410104800

Cinta Alif Bambang Entin Feri Desi Henny Indah 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. Praktikum Pemrograman Basis Data

pg. 61

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; WHILE @@FETCH_STATUS = 0 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; WHILE @@FETCH_STATUS = 0 BEGIN PRINT CONVERT(VARCHAR,@counter) + '. ' + @namamk + ' dgn NA = ' + CONVERT(VARCHAR, @na) + ' --> (' + @status + ')';


pg. 62

Cursor SET @counter = @counter + 1; FETCH NEXT

FROM cNilai INTO @namamk, @na, @status;

END; CLOSE cNilai; DEALLOCATE cNilai; PRINT '-----------------------------------'; PRINT ''; FETCH NEXT END;

FROM cMhs INTO @nim, @nama;

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: Praktikum Pemrograman Basis Data

pg. 63

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


pg. 64

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:


pg. 65

Cursor

(halaman kosong)


pg. 66

BAB V

Prosedur dan Fungsi Sub-Materi:  Prosedur  Fungsi

Prosedur dan Fungsi

(halaman kosong)


pg. 68

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:


pg. 69

Prosedur dan Fungsi 1.

2.

3. 4.

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. 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. Pemeliharaan data Prosedur juga bisa digunakan untuk melakukan insert, update, dan delete (DML). 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. Praktikum Pemrograman Basis Data

pg. 70

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 [<@param1> [=] [OUT[PUT]], ...] AS BEGIN <SQL> | <script block> RETURN END; -- mengubah prosedur ALTER PROCEDURE [<@param1> [=] [OUT[PUT]], ...] AS BEGIN <SQL> | <script block> RETURN END; -- menghapus prosedur DROP PROCEDURE ;

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. Praktikum Pemrograman Basis Data

pg. 71

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

Nama parameter Tipe data parameter Nilai default 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 CREATE PROCEDURE spAmbilMhsPerGender @gender CHAR(1) = 'P' AS BEGIN SELECT * FROM mahasiswa WHERE jns_kelamin=@gender;


pg. 72

Prosedur dan Fungsi RETURN @@ROWCOUNT; END; Kode 5.4 -- Eksekusi SProc BEGIN DECLARE @jumlahData INT; EXEC @jumlahData = spAmbilMhsPerGender 'W'; PRINT @jumlahData; END;

Hasil: nim 05390102208

nama Cinta

06390102666

Entin

alamat Perum Pondok Nirwana Blok BC-14 Jl. Yos Sudarso 1

kota Surabaya

jns_kel.. W

sts_nikah B

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.


pg. 73

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 CREATE PROCEDURE spAmbilMhsPerGender @gender CHAR(1) = 'P', @jumData INT OUTPUT AS BEGIN SELECT * FROM mahasiswa 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: nim 05410104001 ...

nama Alif

alamat Jl. Jagir 20

kota Surabaya

jns_kel.. P

sts_nikah 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 Praktikum Pemrograman Basis Data

pg. 74

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(), Praktikum Pemrograman Basis Data

pg. 75

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.


pg. 76

Prosedur dan Fungsi Kode 5.9 -- membuat prosedur CREATE FUNCTION ([<@param1> , ...]) RETURNS AS BEGIN <SQL> | <script block> RETURN END; -- membuat prosedur ALTER FUNCTION (<@param1> , ...) RETURNS AS BEGIN <SQL> | <script block> RETURN END; -- membuat prosedur DROP 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;


pg. 77

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 (<@param1>, ...) RETURNS TABLE | TABLE () AS BEGIN <SQL> | <script block> RETURN 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);


pg. 78

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 nondeterministic. 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');


pg. 79

Prosedur dan Fungsi Dari hasil perintah tersebut, fungsi isLulus ternyata nondeterministic. Hal ini dikarenakan poin pertama, yaitu tidak adanya kata kunci WITH SCHEMABINDING pada fungsi tersebut.


pg. 80

Prosedur dan Fungsi

Latihan 1. 2. 3. 4. 5.

Buat prosedur untuk menampilkan nim, nama, dan alamat mahasiswa sesuai angkatan. Nilai angkatan ditampung dalam parameter masukan. Buat prosedur untuk menambah data mahasiswa. Seluruh nilai kolom di tabel mahasiswa ditampung dalam parameter masukan. Buat prosedur untuk menghapus data mahasiswa sesuai nim. Nim ditampung dalam parameter masukan. Buat fungsi untuk menghitung umur mahasiswa. Parameter masukan berupa tanggal lahir, sedangkan nilai balikan berupa umur dalam ukuran tahun. 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.


pg. 81

Prosedur dan Fungsi

(halaman kosong)


pg. 82

BAB VI

Trigger Sub-Materi:  Pembuatan Trigger  Ujicoba Trigger

Trigger

(halaman kosong)


pg. 84

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: Praktikum Pemrograman Basis Data

pg. 85

Trigger 1. 2. 3.

INSERT Trigger DELETE Trigger 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 ON [.] [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} <[DELETE][,][INSERT][,] [UPDATE]> AS BEGIN <SQL> | <script block> END; -- mengubah trigger ALTER TRIGGER ON [.] [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} <[DELETE][,][INSERT][,] [UPDATE]> AS BEGIN <SQL> | <script block> END; -- menghapus trigger DROP TRIGGER ;

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

pg. 86

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


pg. 87

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

2.

* INSERTED;

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). Praktikum Pemrograman Basis Data

pg. 88

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


pg. 89

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.


pg. 90

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 SET @strurut='00'+CONVERT(VARCHAR, @urut); ELSE IF @urut < 1000 SET @strurut='0'+CONVERT(VARCHAR, @urut); ELSE SET @strurut=''+CONVERT(VARCHAR, @urut);

DECLARE @nim CHAR(11); SET @nim = @tahun + @jurusan + @strurut;


pg. 91

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 FROM

@nama = nama, @almt = alamat, @kota = kota, @jk = jns_kelamin, @st = sts_nikah INSERTED;

INSERT INTO mahasiswa 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.


pg. 92

Trigger

Latihan 1. 2.

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


pg. 93

Trigger

(halaman kosong)


pg. 94

BAB VII

Connection dan Recordset Sub-Materi:  ADO.NET  Connection  Recordset

Connection dan Recordset

(halaman kosong)


pg. 96

Connection dan Recordset 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.

2.

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


pg. 97

Connection dan Recordset 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.


pg. 98

Connection dan Recordset 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. 2.

Menyediakan akses kedalam data menggunakan koneksi yang aktif dalam data source. 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. Praktikum Pemrograman Basis Data

pg. 99

Connection dan Recordset 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. 2.

Buat sebuah Windows Application melalui VB.NET. Dalam form yang terbentuk, lakukan navigasi kedalam code area. Atau dengan cara lain yaitu double click pada form.


pg. 100


4. 5.

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


pg. 101

Connection dan Recordset lagi yang mengatakan Koneksi ditutup.. Jika terjadi error, maka aka muncul pesan yang mengatakan apa errornya. 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 = _ "server=.\sqlexpress; " & _ "database=pbd; " & _ "integrated security=true"

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


pg. 102

Connection dan Recordset 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 konstruksi connection dengan kode berikut:

melakukan

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 Catch ex As Exception MessageBox.Show(ex.Message)


pg. 103

Connection dan Recordset 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 Dim connectionString As String = _ "server=.\sqlexpress; " & _ "database=pbd; " & _ "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 Application Name

Default Value String

Allowed Value Nama aplikasi


Description Data provider

pg. 104

Connection dan Recordset Name Connect Timeout Server

Default Value 15

Allowed Value 0-32767

-

Nama server true, false, no, yes Nama basis data di server true, false, yes, no, sspi Multiple of 512 String

Encrypt

false

Database

-

Integrated security Packet size

false

Password

-

Persist Security User ID

false -

true, false, no, yes String

Workstation ID

local name

String

8192

Description Detik untuk menunggu untuk konek Nama dari SQL Server instance Penggunaan enkripsi SSL Nama basis data Mode otentikasi Ukuran paket data dalam jaringan (bytes) Password jika tidak menggunakan otentikasi Windows Pengembalian informasi sensitif setelah koneksi. User name jika tidak menggunakan otentikasi Windows 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. Praktikum Pemrograman Basis Data

pg. 105

Connection dan Recordset 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. Database

SqlCommand

Form

SqlDataReader

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" Catch ex As Exception MessageBox.Show(ex.Message) Finally conn.Close() End Try …


pg. 106

Connection dan Recordset 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 berasosiasi langsung dengan SqlCommand ketika melakukan eksekusi melalui comm.ExecuteReader. Sehingga pembuatan SqlDataReader sedikit berbeda dengan obyek lainnya. Perhatikan kode program berikut ini: SqlDataReader

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.


pg. 107

Connection dan Recordset 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). Praktikum Pemrograman Basis Data

pg. 108

Connection dan Recordset Database

SqlCommand

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.


pg. 109


Latihan 1. 2.

3. 4.

Buat aplikasi untuk mengelola data mahasiswa. 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 btnCari digunakan untuk melakukan pencarian data berdasarkan NIM yang diinputkan pada txtNIM. 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.


pg. 110


6. 7.

8.

Jika data tidak ada, maka tampilkan pesan:

Isi statusPencarian dengan nilai FALSE. btnBatal digunakan untuk menghapus isi seluruh Textbox dan mengembalikan posisi centang Radiobutton pada rbtPria dan rbtMenikah. 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. btnKeluar digunakan untuk keluar dari aplikasi.


pg. 111


(halaman kosong)


pg. 112

BAB VIII

Dataset Sub-Materi:  Dataset  Membuat Dataset  Manipulasi Data  Pencarian dan Penyortiran Data  Dataset dan Data Source

Dataset

(halaman kosong)


pg. 114

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


pg. 115

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

Dibuat dari datatables dan relations, lalu mempopulasikan datanya. Dibuat dengan cara menyalin dari basis data (data provider) menggunakan data adapter, yang dalam hal ini adalah SQLDataAdapter. Membaca dari file XSD dan/atau XML. 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")


pg. 116

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


pg. 117

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")


pg. 118

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 = _ Type.GetType("System.String") colNama.MaxLength = 100 colNama.AllowDBNull = False colAlamat = New DataColumn("Alamat") colAlamat.DataType = _ Type.GetType("System.String") colAlamat.MaxLength = 100 colKota = New DataColumn("Kota") colKota.DataType = _ Type.GetType("System.String") colKota.MaxLength = 50 colJnsKel = New DataColumn("Jns_Kelamin") colJnsKel.DataType = _ Type.GetType("System.String") colJnsKel.MaxLength = 1 colJnsKel.AllowDBNull = False colStsNkh = New DataColumn("Sts_Nikah") colStsNkh.DataType = _ Type.GetType("System.String") 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 Praktikum Pemrograman Basis Data

pg. 119

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:

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)

Gambar 8.2 Struktur dsPBD


pg. 120

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,


pg. 121

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 Dim rowMahasiswa As DataRow = _ 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


pg. 122

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 didelete. Update Operasi update mengubah data didalam datatable. Untuk proses update, perhatikan kode program berikut ini: Kode 8.10 Dim rowMahasiswa As DataRow = _ 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


pg. 123

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 Dim rowMahasiswa As DataRow = _ 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()


pg. 124

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


pg. 125

Dataset


pg. 126

Dataset Contains Contains dengan primary key tunggal: Kode 8.16 Dim statusCari as Boolean = _ dt.Rows.Contains("05410104001") If statusCari = True Then MessageBox.Show("Data Ada.") Else MessageBox.Show("Data Tidak Ada.") 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 MessageBox.Show("Data Ada.") Else MessageBox.Show("Data Tidak Ada.") 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 MessageBox.Show("Data Ada.") Else MessageBox.Show("Data Tidak Ada.") End If

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


pg. 127

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 terbinding dengan data bound control (contoh: DataGridView). Kita bisa membuat DataView dari menggunakan kode program berikut:

DataTable

dengan

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). Praktikum Pemrograman Basis Data

pg. 128

Dataset Find Find dengan kriteria tunggal: Kode 8.22 Dim hasilCari As Integer = _ dv.Find("05410104001") If hasilCari >= 0 Then MessageBox.Show("Data Ada.") Else MessageBox.Show("Data Tidak Ada.") 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 MessageBox.Show("Data Ada.") Else MessageBox.Show("Data Tidak Ada.") 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 MessageBox.Show("Data Ada.") Else MessageBox.Show("Data Tidak Ada.") End If


pg. 129

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 connectionString As String = _ "server=.\sqlexpress; " & _ "database=pbd; " & _ "integrated security=true" 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. Praktikum Pemrograman Basis Data

pg. 130

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.


pg. 131

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 da.Update(dt.Select(Nothing, Nothing, _ DataViewRowState.ModifiedCurrent)) 'Insert da.Update(dt.Select(Nothing, Nothing, _ DataViewRowState.Added))


pg. 132

Dataset

Latihan 1. 2.

3. 4. 5.

6.

Buat aplikasi untuk mengelola data dosen. 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 Ketika form di-load, ambil data dosen dari basis data PBD dan masukkan kedalam dtDosen. Tampilkan dtDosen kedalam dgvDosen. btnTambah digunakan untuk menambahkan data kedalam dtDosen. btnUbah digunakan untuk mengubah data kedalam dtDosen. Sebelum dilakukan penambahan, lakukan pencarian data dosen berdasarkan NID, kedalam dtDosen. Anggap data selalu ada. btnKeluar digunakan untuk keluar dari aplikasi.


pg. 133

MK. PRAKTIKUM. Capaian: Mahasiswa mampu menerapkan konsep-konsep pemrograman basis data

Recommend Documents