SQL Function - Query Menjumlahkan Isi Kolom Numeric

Kita bisa menjumlahkan isi kolom yang ada dalam tabel dengan memanfaatkan query database. Sehingga dapat memudahkan kita dalam membuat sebuah aplikasi yang membutuhkan hasil total, seperti aplikasi penjualan dan lain-lain. Kita bisa mengetahui total penjualan dengan menggunakan SQL atau biasa disebut query.

Secara umum, query penjumlahan dirumuskan seperti berikut:

SELECT SUM(kolom_numeric) FROM nama_tabel

atau

SELECT SUM(kolom1 + kolom2 + kolom3) 
FROM nama_tabel1, nama_tabel2, nama_tabel3

Studi Kasus 1

Misalkan kita mempunyai tabel seperti berikut:

Kita ingin tau berapa total penjualan yang telah dilakukan.
Gunakanlah query berikut untuk menjumlahkan isi dalam satu kolom tabel

SELECT sum(harga) FROM penjualan

Sehingga didapatkan hasil seperti berikut:

Studi Kasus 2

Misalkan kita memiliki 3 tabel penjualan, yaitu penjualan_flashdisk, penjualan_harddisk, dan penjualan_mouse.

Dan kita ingin mengetahui jumlah total penjualan dari semua tabel tersebut.
Gunakan query berikut:

SELECT SUM( harga_fd + harga_hd + harga_mouse ) 
FROM penjualan_flashdisk, penjualan_hd, penjualan_mouse

Dan hasilnya adalah seperti berikut:

Database Link Oracle dan MySQL

Jika sudah mempelajari database link antara oracle-oracle, kali ini kita mempelajari bagaimana konfigurasi database link antara Oracle-MySQL.

Pada dasarnya, konsep database link adalah remote database yang bisa dilakukan melalui localhost maupun host yang berbeda.

Untuk tutorial kali ini, kita akan mencoba melakukan konfigurasi database link Oracle-MySQL melalui host yang berbeda.



Contoh kasus: Oracle sebagai master/server yang meremote data yang ada di MySQL, dan MySQL sebagai client/slave sebagai penyedia data, sehingga kita bisa melakukan insert, update, delete pada MySQL melalui Oracle. Host MySQL memiliki IP address: 172.16.16.3 dan MySQL memiliki database dengan nama student.


KONFIGURASI PADA HOST CLIENT/SLAVE (MYSQL)

1. Lakukan konfigurasi secara console melalui CommandPrompt (windows) dan terminal (linux).
2. Login ke MySQL menggunakan username MySQL anda (secara default, username=root)
   

   mysql -u root
   

   
   Jika MySQL anda memiliki password, tambahkan opsi -p untuk memasukkan password user MySQL anda
   

   mysql -u root -p
   

3. Jika anda telah berhasil masuk ke mysql anda, lakukan konfigurasi berikut:
   

   grant usage on *.* to root@localhost with grant option;
   

   Yaitu untuk memberi grant agar user root anda dapat diakses oleh host lain.
   
4. Memanggil nama database yang akan digunakan untuk database link ini
   

   use student;

   Pada kasus ini, kita menggunakan database student.
   
5. Kemudian mendaftarkan IP dan user yang dibolehkan untuk mengakses data
   

   grant all on student.* to 'studentuser'@'172.16.16.9' identified by 'studentpass';
   

   Untuk code di atas, yang diperbolehkan untuk mengakses database student ada IP 172.16.16.9 dengan username=studentuser dan password=studentpass
   Username dan password tersebut kita deklarasikan sendiri.
   Perintah di atas itu memberikan privileges untuk semua akses insert, update, delete sehingga host server (172.16.16.9) bisa mengakses seluruh tabel yang ada di database student (student.*).
   
6. Jalankan perintah berikut untuk mengijinkan privileges tersebut di atas
   

   flush privileges;
   

KONFIGURASI ODBC DRIVER PADA HOST SERVER/MASTER (ORACLE)
Setelah host MySQL selesai dikonfigurasikan, sekarang saatnya melakukan konfigurasi pada host Oracle. Untuk konfigurasi koneksi ke MySQL, perlu menggunakan MySQL ODBC Driver. Secara default, windows tidak menyediakan ODBC Driver untuk MySQL, sehingga harus diinstal terlebih dahulu. Silahkan download MySQL ODBC Driver.

1. Jalankan ODBC Driver (Start -> Control Panel -> Administrative Tool -> Pilih Data Sources ODBC).
2. Setelah menampilkan jendela ODBC Data Souce Administrator, pilih tab System DSN. 
3. Buatlah sebuah data source baru dengan meng-klik tombol Add, kemudian pilihlah MySQL ODBC 5.1 Driver. Kemudian klik tomboh Finish. 
4. Isi field seperti berikut:
• Data Source Name = mysqlstudent.
• Description = MySQL ODBC 5.1 Driver
• Server = 172.16.16.3 (IP ini merupakan IP dari host yang menggunakan MySQL)
• User = studentuser (username yang telah didaftarkan oleh host MySQL tersebut)
  
• Password = studentpass (password yang telah didaftarkan oleh host MySQL tersebut)
  
• Database = pilih database student  
• Kemudian tes koneksi dengan menekan tombol Test. Jika sukses, silahkan lanjutkan ke langkah selanjutnya.

KONFIGURASI NET SERVICE UNTUK KONEKSI ANTARA ORACLE DAN MYSQL

1. Buatlah script menggunakan notepad untuk koneksi datasource yang telah dibuat di atas menggunakan script berikut:
   

   # HS init parameters
   # ODBC data source name
   
    HS_FDS_CONNECT_INFO = mysqlstudent
    HS_FDS_TRACE_LEVEL = 0
   #---------------------------------------------

   mysqlstudent adalah nama Data Source. Kemudian simpan dengan nama initmysqlstudent.ora dan kemudian letakkan pada alamat directory C:\oracle\product\10.2.0\db_1\hs\admin
   
2. Pada alamat direktori C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN edit file listener.ora menggunakan notepad. Akan tetapi alangkah baiknya jika file listener.ora anda backup terlebih dahulu untuk mewaspadai jika terjadi error. Kemudian tambahkan script pada SID_NAME = mysqlstudent pada listener.ora. Gunakan script berikut:
   

   (SID_DESC=
    (SID_NAME=mysqlstudent)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    (PROGRAM = hsodbc)
   )

   Lihat gambar berikut untuk penempatan syntax di atas:
   
   
3. Pada service, restart TNSListener (Run -> ketikkan services.msc -> restart service OracleOraDb10g_home1TNSListener).
4. Pada alamat directory C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN, edit script pada file tnsname.ora menggunakan notepad editor, tetapi alangkah baiknya jika file tersebut di backup terlebih dahulu. Tambahkan script berikut:

   MYSQLSTUDENT=
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SID = mysqlstudent)
       )
       (HS = OK)
     )

5. Untuk cek tnsname yang telah ditambahkan tersebut, jalankan Net Manager kemudian pilih Oracle Net Configuration -> Local -> Service Naming. Silahkan apakah nama MYSQLSTUDENT telah muncul di Net Manager tersebut? jika sudah ada, silahkan melanjutkan ke langkah selanjutnya, apabila belum muncul, ulangi langkah di atas.

MEMBUAT DATABASE LINK PADA ORACLE UNTUK MEMANGGIL DATA DI MYSQL

1. Untuk mencoba koneksi mysqlstudent, buatlah sebuah database link dengan nama mysql_student. Gunakan perintah berikut:
   

   create database link mysql_student using 'mysqlstudent'

2. Silahkan lihat data yang ada di MySQL menggunakan query berikut:

   select * from student@mysql_student

3. Untuk insert data ke MySQL, gunakan query berikut:
   

   insert into student@mysql_student values ('07560242', 'Mudafiq Riyan P', 'Malang')

4. Jika sudah berhasil, maka kitapun bisa melakukan insert, update, delete, and view data yang ada di MySQL.

Membuat Kolom Auto Increment Pada Oracle

Dalam membuat kolom auto increment pada MySQL dan pada Oracle memiliki cara yang berbeda. Jika menggunakan MySQL, kita dimudahkan untuk pembuatan auto increment data, karena bisa langsung menggunakan fungsi auto_increment, sedangkan pada Oracle tidak semudah itu. 

Mari kita bahas bagaimana membuat kolom auto increment pada oracle mulai dari pembuatan table sampai insert data.



Create Table
Misalkan kita mempunyai schema table seperti yang dibuat berikut:

create table buku_telepon(
id number,
no_hp varchar2(20),
nama varchar2(50),
alamat varchar2(100),
email varchar2(100)
)

Dan pada id merupakan data yang di auto increment.


Create Sequence
Pada oracle, untuk membuat auto increment itu di atur melalui sequence. Gunakan query berikut:

create sequence buku_telepon_id_seq
start with 1
increment by 1

maksudnya adalah kita membuat sequence dengan nama buku_telepon_id_seq (nama tersebut bebas) yang nilainya dimulai dari angka 1 dan peningkatannya 1 kali. Jadi ketika data diinputkan, maka angka awal untuk kolom auto increment adalah angka 1 dan meningkat 1 kali tiap ada inputan data baru.

Insert Data
Berikut contoh insert data:

insert into buku_telepon values (buku_telepon_id_seq.nextval, '085612345667', 'Dhafiq Sagara', 'Jember', 'mudafiq.riyan@yahoo.com');

Maka akan muncul data tersebut dengan id = 1

Akan tetapi dengan cara tersebut sungguh sangat rumit, karena untuk menginputkan data, kita harus memanggil nama sequence (buku_telepon_id_seq.nextval). Sebenarnya kita pun bisa menyederhanakan caranya dengan memanfaatkan fungsi trigger.

Menggunakan Fungsi Trigger
Berikut syntax trigger untuk membuat auto increment secara otomatis pada oracle.

CREATE OR REPLACE TRIGGER auto_telepon
BEFORE INSERT
ON buku_telepon
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT buku_telepon_id_seq.nextval INTO :NEW.ID FROM dual;
END;

Pada fungsi trigger di atas, id akan secara otomatis di tambahkan ketika data baru ditambahkan.

Database Link Oracle

Konsep utama dari sistem basisdata distribusi adalah database link. Database Link adalah suatu koneksi antara dua database fisik yang memungkinkan client untuk mengakses datanya sebagai satu databaselogic.


Database link merupakan sebuah pointer yang didefinisikan sebagai satu-arah jalur komunikasi dari server Oracle Database kepada server database lainnya. Koneksi database link memungkinkan user-user lokal untuk mengakses data pada database remote. Agar koneksi ini berhasil, setiap database pada sistem distribusi harus memiliki global database name yang unik pada domain jaringan.


Database link bisa bersifat private (pribadi) atau public (umum). Jika bersifat private, maka hanya user yang telah membuatnya memiliki akses dan jika link adalah public maka semua user database bisa menggunakannya untuk mengakases. Satu perbedaan signifikan mengenai database link adalah bagaimana caranya koneksi dengan database remote.

Silahkan download artikel yang saya tulis di ilmukomputer.com: Mudafiq-DatabaseLinkOracle

Partisi Index dan Partisi Tabel Pada Database Oracle

Sebenarnya inti dari partisi itu adalah membagi ruang untuk data-data tertentu. Jika dikaitkan dengan DBMS,Partisi tabel adalah membagi data suatu tabel menjadi beberapa bagian/kriteria. Misal tabel mahasiswa, dipartisi/dibagi berdasarkan NIM. Sehingga dapat diketahui angkatan mahasiswa berdasarkan NIMnya. Dan konsep partisi ini dapat dikembangkan sebagai DSS (Decision Support System) untuk pengambilan keputusan tertentu.

Sedangkan index digunakan untuk mempercepat perncarian data didalam tabel database relasional. Sehingga dengan adanya index partition, memungkinkan untuk meningkatkan performa pencarian data.

Berikut adalah konsep umum partisi tabel:

Dari suatu data yang besar, dipecah-pecah lagi per-bagian berdasarkan kriteria-kriteria tertentu. Pada gambar di atas, dari suatu data dibagi berdasarkan bulan.

Manfaat lain dari partitioning adalah tiap-tiap segment (partisi atau subpartisi) bisa ditaruh di tablespace yang berbeda, sehingga kita mendapat manfaat dari spreading (menyebar) tablespace, yaitu penyebaran I/O dan mengurangi resiko loss data karena tablespace corrupt.

Ada 3 metode utama partisi:

1. Range partition
   Range partition adalah pembagian suatu tabel ke dalam beberapa bagian berdasarkan range (rentang) nilai tertentu. Range partition ini cocok digunakan pada kolom yang memiliki distribusi nilai yang merata.
2. List partition
   Konsep pada list partition adalah data dikelompokkan berdasarkan nilai datanya. Cocok untuk kolom yang variasi nilainya tidak banyak. Misal data kota berdasarkan wilayah provinsi. List: Jember, Malang, Surabaya pada partisi Jawa Timur; Bandung, Cirebon pada partisi Jawa Barat. Jadi list partition ini berdasarkan list dari suatu segmen, sehingga data-datanya di list terlebih dahulu.
3. Hash partition
   Penggunaan hash partition ini jika tidak cocok dengan RANGE ataupun LIST Partition. Penentuan “nilai mana di taruh di partisi mana” itu diatur secara internal oleh Oracle (berdasarkan hash value). Kenapa harus memaksakan memakai partisi sementara tidak cocok dengan RANGE ataupun LIST? Jika ingin mendapat manfaat dari filosofi PARTITIONING yang sebenarnya di mana data disebar ke segment-segment yang berbeda.

Dan 2 metode composite (gabungan):

1. Composite range-list partition
   Konsep composite range-list partition ini menggabungkan partisi range dan list. Jika pada tabel mahasiswa, NIM dipartisi secara range, sedangkan untuk mendapatkan NIM sekian yang tinggal di wilayah provinsi Jawa Timur itu apa saja, maka menggunakan list. Jadi dipartisi kemudian di list berdasarkan kriteria tertentu.
2. Composite range-hash partition
   Composite range-hash partition merupakan konsep menggabungkan range partition dan hash partition. Sehingga partisi-partisi yang telah dibuat berdasarkan range/rentang akan dipartisi lagi ke dalam beberapa bagian berdasarkan hash.

Untuk lebih memahami mengenai konsep partisi index dan tabel, berikut saya share mengenai percobaan-percobaan menggunakan Oracel. Silahkan download dan pelajari mengenai Mudafiq-PartisiPadaOracle.pdf

Trigger Pada Oracle 10g

Trigger adalah blok PL/SQL yang disimpan dalam database dan akan diaktivasi ketika kita melakukan statement-statement SQL (DELETE, UPDATE, dan INSERT) pada sebuah tabel.

Aktivasi trigger didasarkan pada event yang terjadi di dalam tabel tersebut sehingga trigger dapat membantu dalam menjaga integritas dan konsistensi data.

Implementasi trigger yang sering ditemui dalam dunia nyata adalah untuk meng-set dan mengubah nilai kolom dalam suatu tabel sehingga validasi nilai dari tabel tersebut akan terjaga.

Adanya trigger dalam database akan meringankan kita dalam pembuatan aplikasi karena di dalam aplikasi yang kita buat, kita tidak perlu lagi untuk melakukan validasi data.

Misalkan pada kasus untuk dosen wali mahasiswa. Data yang ada pada dosen adalah jumlah mahasiwa yang menjadi wali dosen tersebut. Sedangkan pada mahasiswa, hanya menyantumkan NIP dari dosen walinya. Sehingga ketika ada mahasiswa yang ditambahkan, tentunya dosen wali yang bersangkutan akan menambahkan pula jumlah mahasiswa yang di walikan. Tentu butuh adanya trigger agar data yang diinputkan tidak satu untuk mahasiswa, satu lagi untuk dosen.
Untuk lebih memahami mengenai trigger, silahkan download e-book penggunaan trigger pada oracle 10g Mudafiq-TriggerOracle.pdf

Koneksi Oracle Pada Java (Netbeans)

Nah, ni merupakan pembelajaranku di kuliah. Pada mata kuliah database, saya mempelajari database yang dikoneksikan dengan Java, editor yang dipakai adalah netbeans. Nah, setelah dapat ilmu itu, langsung saya posting disini deh...

Ok langsung saja ya bagaimana mengatur settingan netbeans untuk dikoneksikan dengan database oracle.

Disini saya menggunakan Oracle XE (Express Edition).

Spoiler for open artikel bersangkutan:

Membuat Aplikasi Java Sederhana Menggunakan Database Oracle (Tahap 1)
Membuat Aplikasi Java Sederhana Menggunakan Database Oracle (Tahap 2)
Menambahkan Fungsi Search Pada Aplikasi Java Desktop
Fungsi Pencarian Dengan Pilihan ComboBox Pada Aplikasi Java Desktop

Koneksi OracleXE Pada Java Menggunakan Netbeans :

1. Buatlah project baru pada netbeans
2. copy folder lib yang berisi file library jdbc di directory C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib pada project netbeans yang tadi dibuat
3. pada project tersebut, klik kanan - properties
4. pilih Libraries pada list Properties
5. add Library
6. pilih Java DB Driver
7. add Library
8. add JAR/Folder
9. browse file pada lib yang ada pada project tadi
10. ambil file ojdbc14.jar (pilih semua file tidak masalah)
11. kemudian open
12. Klik OK
13. Coba lihat diproject netbeans - Libraries seperti gambar dibawah ini:
14. Disitu sudah tertanam driver Java DB dan ojdbc.jar
15. selesai
16. silahkan dicoba

Berikut contoh syntax Java koneksi ke OracleXE:

/* coonnect.java */
import java.sql.*;

public class coonnect {

    String db = "jdbc:oracle:thin:@localhost:1521:XE";
    String user = "poliklinik";
    String pass = "254";

    Statement stm = null;
    Connection co = null;


    public coonnect(){}

    public Statement sambung(){

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            }
        catch (ClassNotFoundException e) {

            e.printStackTrace();
        }

        try {
            co = DriverManager.getConnection(db,user,pass);
            }
        catch (SQLException e) {

            e.printStackTrace();
        }

        try {
            stm = co.createStatement();
            }
        catch (SQLException e) {

            e.printStackTrace();
        }

        return stm;
    }
}

Untuk contoh source code dari java yang terkoneksi dengan oracleXE dapat anda download CobaPoliklinik.zip sebuah project untuk database poliklinik.


Semoga bermanfaat,
DHAFIQ-SAN