Home > Oracle > Dasar Membuat Report dan Perintahnya dalam SQL*Plus

Dasar Membuat Report dan Perintahnya dalam SQL*Plus

SQL*Plus biasanya digunakan sebagai alat untuk memanipulasi data dan melakukan query ad hoc, ia juga dapat difungsikan sebagai alat untuk menuliskan laporan secara interaktif. Dia akan menggunakan SQL untuk mengambil informasi dari database Oracle lalu menyediakan kemudahan bagi kita untuk menyusun laopran berupa kemudahan pembuatan judul laporan, judul kolom, menghitung sub total dan total, merubah format tampilan data dan sebagainya.

Kita dapat langsung menuliskan perintah-perintah untuk menyusun laporan ke dalam SQL*Plus. Tapi cara ini mempunyai 1 kelemahan, yaitu saat kita keluar dari SQL*Plus semua perintah yang sudah susah payah kita ketikan akan hilang dan tidak dapat terpakai lagi. Untuk mengatasi hal itu kita dapat menggunakan cara penyusunan laporan yang kedua, yaitu menuliskan perintah-perintahnya ke dalam file dengan ekstensi .sql, kemudian file tersebut kita jalankan dari SQL*Plus. Cara ini lebih efektif karena kita dapat mengedit script tersebut setiap saat bila dibutuhkan.

Ada beberapa perintah dalam SQL*Plus yang kita gunakan untuk penyusunan laporan, berikut penjelasannya:

  • remark memberitahukan SQL*Plus bahwa baris tersebut dianggap sebagai komentar
  • set headsep pemisah yang menandakan bahwa SQL*Plus harus memisahkannya menjadi dua baris atau lebih
  • ttitle mengatur judul yang berada di awal laporan
  • btitle mengatur judul yang berada di akhir laporan
  • column mengatur format tanpilan kolom seperti judul kolom, lebar kolom, dan lain sebagainya
  • break on memberitahukan kepada SQL*Plus untuk meletakan baris kosong diantara bagian dalam report atau antara sub total dan total
  • compute sum menghitung sub total
  • set linesize mengatur jumlah karakter dalam satu baris
  • set pagesize mengatur banyaknya baris per halaman
  • set newpage mengatur banyaknya baris kosong di awal halaman
  • spool melempar keluaran laporan ke dalam file sehingga bisa diprint
  • /**/ untuk komentar banyak baris
  • — sama seperti remark, untuk komentar
  • set pause membuat tampilan berhenti untuk tiap halaman
  • save menyimpan sintaks sql yang sudah kita ketikan ke dalam file
  • host mengirimkan perintah kepada komputer host
  • start atau @ menjalankan perintah-perintah yang kita simpan ke dalam file
  • edit keluar sementara dari SQL*Plus dan masuk ke dalam editor teks yang sudah kita pilih
  • define_editor memberitahukan kepada SQL*Plus nama editor teks yang kita gunakan
  • exit atau quit mengakhiri SQL*Plus

Gambar di atas merupakan alur proses pembuatan laporan, secara garis besar dapat dijelaskan sebagai berikut:
Buka editor teks yang ingin kita gunakan. Ketikan perintah-perintah pembuatan laporan di situ, setelah selesai simpan dengan ekstensi .sql, misal script.sql.

Lalu dalam SQL*Plus kita jalankan file tersebut dengan perintah

SQL> start nama_file.sql

Atau

SQL> @nama_file.sql

Setelah kita cukup dengan teori, sekarang kita akan mempraktekan membuat laporan sederhana. Laporan ini akan memberikan informasi kepada kita nama-nama karyawan dalam satu departemen dan besarnya gaji mereka, lalu kita juga akan menghitung rata-rata gaji yang dikeluarkan tiap departemen. Saya menggunakan data yang diambil dari database sample Oracle, jika anda belum punya maka anda harus membuatnya terlebih dahulu. Silahkan ikuti langkahnya di sini.

Berikut merupakan cuplikan kode yang saya buat, saya simpan dengan nama script.sql

rem Jumlah Pegawai report /*No 1*/
set headsep ! /*No 2*/

ttitle ‘Laporan Jumlah Pegawai per Departemen’ /*No 3*/
btitle ‘Dari Tabel Employees’ /*No 4*/

column Departemen format a15 word_wrapped /*No 5*/
column Nama format a20 word_wrapped /*No 6*/
column Gaji format 999999.99 /*No 7*/

break on Departemen skip 1 on report /*No 8*/
compute avg of Gaji on Departemen /*No 9*/

set linesize 80 /*No 10*/
set pagesize 60 /*No 11*/
set newpage 0 /*No 12*/
set feedback off /*No 13*/

SELECT d.department_name AS Departemen, e.first_name || ‘ ‘ || e.last_name AS Nama,
salary AS Gaji FROM departments d, employees e WHERE e.department_id = d.department_id
ORDER BY d.department_name; /*No 14*/

Supaya dapat lebih memahami sekarang kita akan bahas maksud perintah-perintah tersebut baris demi baris.

Pada perintah no 1 kita menggunakan perintah rem, singkatan dari remark. Baris yang dimulai dengan perintah ini akan dihiraukan oleh SQL*Plus. Biasanya kita meletakan dokumentasi laporan yang dibuat di sini.

Tanda baca setelah perintah set headsep pada perintah no 2 akan memberitahukan SQL*Plus dimana harus memecah baris menjadi dua. Misalnya kita memiliki nama kolom ‘Nama!Kolom’, karena kita meletakan tanda ‘!’ setelah perintah set headsep maka nama kolom tersebut akan tercetak menjadi dua baris, break akan dilakukan pada tanda ‘!’.

Baris 3 dan 4 menentukan judul laporan di awal (ttitle) dan judul laporan di bawah (btitle). Judul harus diapit dengan tanda petik satu, jika judul kita mengandung tanda petik satu maka supaya dapat tercetak kita menambahkan petik satu lagi di kanannya (sebagai escape karakter). SQL*Plus akan selalu membuat ttitle berada di tengah dengan memperhitungkan nilai pagesize.

Perintah no 5-7 akan mendefinisikan bagaimana kolom ditampilkan. Perhatikan nama kolom Departemen, Nama, dan Gaji sama dengan nama kolom yang dihasilkan oleh perintah SQL pada baris 14. Untuk SQL ini saya menggunakan nama alias sebagai nama kolom (supaya mudah dibaca). Untuk kolom Departemen kita format dengan format a15 dan word_wrap. a15 berarti kita memperlakukan kolom tersebut sebagai alphanumerik dengan lebar 15 karakter, lalu word_wrap akan melakukan wraping jika data yang harus ditampilkan lebih dari 15 (dipecah jadi 2 baris). Sedangkan untuk kolom Gaji kita berikan format numerik 999999.99, kolom ini akan menampung data numerik sebanyak 8 digit dengan 2 tempat desimal. Kalau kita perhatikan di hasil yang ditampilkan, jumlah digit yang disediakan adalah 9, lebih satu dari yang kita definisikan. Penambahan satu digit ini untuk tempat tanda negatif.

Pada baris ke 8 saya menggunakan perintah break on untuk kolom Departemen. Hasil dari perintah ini sebetulnya mirip dengan perintah SQL GROUP BY. Perintah break on akan selalu berpadanan dengan perintah SQL ORDEY BY. Kita lihat hasil yang didapat, seolah-olah laporan kira terkelompok berdasarkan Departemen bukan. Dengan menggunakan perintah break on pada tabel Departemen maka kita memberitahukan kapada SQL*Plus untuk memperhatikan nilai dari tabel tersebut saat ditampilkan, apabila baris sebelumnya memiliki nilai yang sama maka baris yang bersangkutan tidak akan ditampilkan nilainya. Jika bertemu baris baru yang bernilai beda maka nilai kolom Departemen pada baris tersebut akan ditampilkan dengan sebelumnya menambahkan baris kosong. Untuk melakukan break satu laporan penuh maka kita gunakan perintah break on report (bisanya untuk menulis total). Tapi perlu diperhatikan, jika kita menuliskan perintah break dalam dua baris terpisah maka perintah break tersebut akan saling override, yang dijalankan adalah perintah terakhir. Oleh karena itu perintah break dijadikan satu seperti baris 8.

Untuk baris ke 9 kita menghitung rata-rata (dengan fungsi avg) pada kolom Gaji. Perintah ini selalu berdampingan dengan perintah break on. Dalam hal ini akan menghitung rata-rata gaji per departemen.

Setiap perintah break on berhubungan dengan ORDER BY
Setiap perintah compute avg harus berhubungan dengan perintah break on

Selain avg (rata-rata) ada pula fungsi sum (jumlah), count (banyak data), max (nilai maksimum).

Baris 10 kita memberitahukan kepada SQL*Plus bahwa banyak karakter dalam satu baris adalah 80 karakter, sedangkan di baris 11 kita nyatakan bahwa banyak baris dalam satu halaman adalah 60 baris (dihitung termasuk ttitle, btiitle, nama kolom dsb). Lalu perintah set new page akan menentukan berapa banyak baris kosong pada awal halaman yang baru. set newpage 0 bukan berarti baris kosongnya ada nol atau tidak ada, ini akan memberitahukan SQL*Plus untuk menghasilkan karakter top-of-form (biasanya hex 13) sehingga total baris dalam halaman itu sama dengan perintah set pagesize (dalam hal ini 60). Sangat berguna pada saat akan mencetak laporan.

Laporan yang kita buat ini masih dalam bentuk sangat sederhana, namun kita sudah mencakup fungsi-fungsi penting untuk membuat laporan. Saya akan mencontohkan pembuatan laporan dengan format yang lebih rumit pada tutorial-tutorial berikutnya.

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: