Praktikum
8 SIMPLE REPORT Tujuan : 1. Mengetahui dan memahami reporting dalam Oracle 2. Mengerti manfaat dan batasan penggunaan simple report 3. Mampu mengimplementasikan simple report dalam Oracle Alat dan Bahan : 1. Buku literatur mengenai PL/SQL ORACLE, terutama yang menggunakan DBMS ORACLE 10g. 2. Modul praktikum BASIS DATA LANJUT 3. Modul praktikum DATA MINING DAN WAREHOUSING Landasan Teori : 1. Mengatur Dimensi Halaman Dimensi halaman dari keluaran SQL*Plus dapat diatur melalui system variable PAGESIZE dan LINESIZE. System variable PAGESIZE menentukan jumlah baris maksimum yang ditampilkan dalam satu halaman. Sedangkan system variable LINESIZE menentukan jumlah karakter maksimum yang ditampilkan dalam satu baris. Dengan demikian kedua system variable ini dapat mengatur tinggi dan lebar dari halaman keluaran SQL*Plus. Berikut ini ditunjukkan dua perintah SET untuk menentukan nilai system variable PAGESIZE dan LINESIZE pada current environment SQL*Plus. SQL> SET PAGESIZE 12 SQL> SET LINESIZE 60
Dengan environment seperti di atas, coba lakukan sebuah query seperti berikut ini. Query tersebut menampilkan 15 baris string karakter yang masing-masing panjangnya 60 bytes. Data dictionary DICTIONARY digunakan sebagai sumber data hanya karena jumlah baris data yang ada pada view tersebut pasti lebih dari 15 baris. SQL> SELECT 2 ‘*** *$$$$$$$$$$*** *$$$$$$$$$$’||’*** *$$$$$$$$$$’ PAGES 3 FROM DICTIONARY WHERE ROWNUM<=15; PAGES *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ PAGES *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ *$$$$$$$$$$*** *$$$$$$$$$$*** *$$$$$$$$$$ 15 row selected
Perhatikan hasil query di atas. Suatu halaman di mulai dengan satu baris kosong. Setelah itu diikuti oleh judul baris kolom (heading) dan baris garis- bawah judul kolom. Kemudian, dibawahnya ditampilkan baris-baris data. Apabila panjang baris judul kolom (atau baris data) lebih besar dari LINESIZE, baris tersebut ditampilkan dalam dua baris atau lebih. Apabila baris-baris yang ditampilkan tidak cukup ditampung dalam satu halaman, akan dibuat halaman baru yang terdiri atas satu baris kosong, judul kolom, garis-bawah judul kolom, dan baris-baris data. Demikian seterusnya sampai semua data ditampilkan. Penutup dari halaman terkahir adalah sebuah baris kosong, dan atribut-atribut lainnya seperti FEEDBACK dan TIMING. Apabila PAGESIZE diberi nilai 0 (nol), yang ditampilkan hanya baris-baris data saja tanpa awalan baris kosong, judul kolom, garis-bawah judul kolom dan atribut-atribut lainnya. Baris kosong yang berada sebelum baris judul kolom sebenarnya dapat ditentukan jumlahnya dengan mengatur system variable NEWPAGE. Pada keluaran hasil query di atas, nilai NEWPAGE yang digunakan adalah satu (default). Perlu diinformasikan juga bahwa Anda dapat melihat nomor baris yang sedang ditampilkan dari sebuah halaman menggunakan perintah SHOW LNO. Nomor halaman dapat dilihat dengan perintah SHOW PNO. Apabila tidak disebutkan secara khusus, PAGESIZE dan LINESIZE yang digunakan pada pokok-pokok bahasan berikutnya didalam bab ini adalah 50 dan 65. a. Menggunakan BREAK Perintah BREAK dapat digunakkan untuk hal-hal seperti berikut ini. Menampilkan hanya satu nilai untuk beberapa nilai yang sama pada kolom yang ditentukan. Menambahkan baris kosong setiap kali nilai dari kolom yang ditentukan berubah. Mencetak nilai hasil perhitungan untuk kelompok baris dengan nilai kolom yang sama pada kolom yang ditentukan (lihat pokok batasan berikutnya). Bentuk umum perintahnya adalah sebagai berikut. BRE[AK][{ON
[SKIP n|[SKIP] PAGE] [NODUP[LICATES]|DUP[LICATES]]}]
Nama column tidak boleh mengandung schema dan/atau table. Untuk kolomkolom yang menggunakan format “tab_name,col_name” di dalam SQL statementnya, sebaiknya berikan kolom alias pada SQL statement tersebut sehingga nama kolom alias tersebut dapat digunakan pada perintah BREAK. Jika expr digunakan, break tersebut berlaku jika expr terdapat pada SQL statement dan tanpa menggunakan kolom alias. Pilihan ON ROW digunakan untuk break setiap kali baris berikutnya berbeda dengan baris sebelumnya. ON REPORT biasanya digunakan bersamaan dengan kalkulasi untuk keseluruhan data (lihat perintah COMPUTE pada pokok bahasan berikutnya). “SKIP n” dapat digunakan untuk memberikan baris kosong sebanyak n baris setiap masuk ke break berikutnya. Apabila SKIP PAGE digunakan keluaran diceyak di halaman berikutnya setiap kali break terjadi. NODUP[LICATES] digunakan apabila nilai-nilai yang duplikat tida ingin dicetak. Pilihan ini adalah default. DUP[LICATES] digunakan apabila nilai-nilai yang duplikat ingin dicetak. Berikut ini diberikan sebuah contoh penggunaan perintah BREAK untuk kolom bayar. Sebuah baris kosong dicetak setiap kali break terjadi.
SQL> BREAK ON bayar SKIP 1 SQL> SELECT nim,prodi,nama,bayar FROM mahasiswa ORDER BY nim; NIM NAMA PRODI BAYAR 30107001 ANGGI NOVEMBRA MI 2000000 30107003 WIDO KESUMA MI 2000000 30107004 MURDIJAT ARIF PRABOWO MI 15000000 30207005 TK ANUGRAH DWI ARDIANTO TK 8000000 30207006 ANDRE ADRIAN TK 2000000 30207007 ANDI HAKIM ARIF TK 4000000 30307008 SWANDY EFRAIM MANALU KA 10000000 30307009 SITI NURFAUZIYAH KA KA 3500000
Bagaimana jika ingin menggunakan banyak criteria break? Anda dapat menggunakan klausa ON sebanyak criteria yang diinginkan. Contoh-contoh break dapat ditemukan pada pokok-pokok bahasan berikutnya. Apabila perintah BREAK dijalankan tanpa klausa ON, perintah tersebut ditujukan untuk memunculkan perintah BREAK yang terakhir. Sebagai contoh ditunjukkan di bawah ini. SQL> BREAK Break on bayar skip 1 nodup
Perlu diingat bahwa setiap kali perintah BREAK dijalankan, perintah tersebut berlaku sampai perintah BREAK berikutnya dijalankan atau sampai CLEAR BREAKS dijalankan. Perintah CLEAR BREAKS berguna untuk menghapus definisi break yang ada seperti yang ditunjukkan seperti ini. SQL> CLEAR BREAKS Breaks cleared
b. Melakukan Kalkulasi dengan COMPUTE Perintah COMPUTE dapat digunakan untuk melakukan perhitungan terhadap sebuah kolom yang ekspresi dari sekelompok baris data. Bentuk umum perintahnya adalah sebagai berikut. COMPUTE[{function [LAB[EL] text]} OF <expr|column> ON <expr|column|REPORT|ROW> ]
Satu perintah COMPUTE dapat menggunakan lebih dari satu fungsi. dengan urutan pengerjaan COMPUTE. Label text dapat diberikan untuk membuat text menjadi tanda atau cap pada baris hasil COMPUTE. Jika tidak diberikan, default text adalah nama fungsi. Klausa OF berfungsi untuk menentukan expr atau kolom apa yang dikalkulasi. KLAUSA ON berfungsi untuk menentukan Kriteria break untuk satu perhitungan COMPUTE. Sebagai catatan, hasil dari perintah COMPUTE hanya dicetak jika perintah BREAK disertakan. Nama column atau expr pada klausa ON perintah COMPUTE harus ada pada klausa ON perintah BREAK tersebut. Fungsi-fungsi untuk Perintah COMPUTE : Nama Fungsi AVG COU[NT] MIN[MUM] MAX[MUM] NUM[BER] SUM SID VAR[IANCE]
Perhitungan Nilai rata-rata.Baris yang diperhitungkan hanya baris dengan nilai yang tidak NULL Menghitung jumlah baris yang nilai column atau exprnya tidak NULL Nilai minimum Nilai maksimum Menghitung jumlah baris, termasuk yang bernilai NULL Jumlah nilai Standar deviasi dari nilai column atau expr yang tidak NULL Variance dari nilai column atau expr yang tidak NULL
Tipe Data NUMBER Semua NUMBER,CHAR,VARCHAR2 NUMBER,CHAR,VARCHAR2 Semua NUMBER NUMBER NUMBER
Berikut ini diberikan sebuah contoh perhitungan rata-rata, nilai minimum, dan nilai maksimum dari bayar yang dikelompokkan prodi. Pengurutan data (ORDER BY) juga berpengaruh pada keluaran. Perhatikan juga, BREAK dikenakan pada kolom departemen dengan skip satu baris dan pada kolom pos_id tanpa skip. SQL> BREAK ON prodi SKIP 2; SQL> COMPUTE AVG MAX MIN OF bayar ON prodi; SQL> SELECT nim,nama,prodi,bayar FROM mahasiswa ORDER BY prodi; NIM 30307009 30307008
30107003 30107001 30107004
NAMA SITI NURFAUZIYAH SWANDY EFRAIM MANALU
WIDO KESUMA ANGGI NOVEMBRA MURDIJAT ARIF PRABOWO
PRODI KA
BAYAR 3500000 10000000
** avg minimum maximum MI
6750000 3500000 10000000 2000000 2000000 15000000
**
30207007 30207005 30207006
ANDI HAKIM ARIF ANUGRAH DWI ARDIANTO ANDRE ADRIAN
avg minimum maximum TK
** avg minimum maximum
6333333,33 2000000 15000000 4000000 8000000 2000000 4666666,67 2000000 8000000
8 rows selected.
Sebagai catatan tambahan, semua atribut yang telah ditentukan dengan perintah COMPUTE dapat dihapus menggunakan perintah CLEAR COMPUTES. c. Perintah COLUMN Untuk Memformat Kolom Perintah ini digunakan untuk memformat judul kolom maupun nilai kolom. Bentuk umum perintahnya adalah sebagai berikut. COL[UMN] [ {[option]}]
Apabila perintah COLUMN digunakan tanpa column atau expr maka SQL*Plus akan mengeluarkan atribut COLUMN yang sedang berlaku pada session SQl*Plus tersebut (bukan session user database). Apabila perintah COLUMN digunakan dengan menyertakan column atau expr namun tanpa option, SQL*Plus akan mengeluarkan atribut COLUMN untuk kolom atau ekspresi yang disebutkan. Nilai option dapat berupa klausa-klausa yang diperhatikan pada Tabel. Berikut daftar Option pada Perintah COLUMN Klausa ALI[AS] alias
CLE[AR] FOLD_A[FTER]
FOLD_B[EFORE]
Desripsi Memberi nama alias yang dapat digunakan untuk menggantikan nama column pada perintah BREAK, COMPUTE, ataupun COLUMN Mengembalikan atribut colomn ke nilai default sesuai dengan tipe data, panjang data, dan sebagainya Menambahkan karakter baris baru(carriage return) setelah judul column dan setelah nilai column dari setiap baris. Tidak berlaku untuk kolom terakhir pada klausa SELECT Menambahkan karakter baris baru (carriage return) sebelum judul column dan setelah nilai column dari setiap baris. Tidak berlaku untuk kolom pertama pada klausa SELECT
FOR[MAT] format
HEA[DING]
JUS[TIFY]
LIKE[expr|alias]
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT] NUL[L] text OLD_V[ALUE] variable
ON|OFF WRA[PPED] | WOR[D_WRAPPED| TRU[NCATE]
Menentukan format tampilan nilai column. Format untuk kolom karakter berupa “An” dimana n adalah lebar kolom. Format untuk kolom DATE mengikuti NLS_DATE_FORMAT namun lebar kolomnya dapat ditentukan dengan “An”. Format untuk kolom NUMBER sama dengan format yang digunakan pada fungsi TO_CHAR(NUMBER) Menentukan text sebagai judul kolom untuk menggantikan judul kolom yang diberikan di dalam SELECT statement. String karakter text dapat mengandung karakter HEADSEP untuk menampilkan judul kolom dalam dua baris atau lebih. Menentukan apakah judul kolom dan nilai kolom ditampilkan rata kiri, di tengah-tengah. Atau rata kanan. Jika tidak disebutkan maka kolom NUMBER ditampilkan rata kanan dan kolom lainnya rata kiri Menyalin atribut tampilan dari kolom lain. Atribut yang disalin hanya atribut yang tidak ditemukan secara eksplisit. Sama efeknya dengan FOLD_BEFORE, yakni menampilkan baris baru sebelum nilai column ditampilkan. Digunakan untuk membuat variable yang nilainya adalah nilai kolom. Variable tersebut biasanya diguna-kan pada perintah TITLE untuk membuat judul-atas pada laporan master detail. Agar pengaruhnya terlihat, kolom pada perintah ini harus digunakan juga pada perintah “BREAK…SKIP PAGE” Mengatur apakah judul dan nilai column dicetak di layar (ON) atau tidak (OFF). Default adalah OFF Menentukan text sebagai nilai yang dicetak pada kolom yang ditentukan Digunakan untuk membuat variable yang nilainya adalah nilai kolom. Variable tersebut biasanya digunakan pada perintah BTITLE untuk membuat judul-bawah pada laporan master detail. Kolom pada perintah ini harus digunakan juga pada perintah “BREAK…SKIP PAGE” Mengaktifkan (ON) atau menonaktifkan (OFF) atributatribut kolom yang sudah didefinisikan. Perintah ini khusus untuk kolom DATE atau karakter yang lebarnya ditentukan namun nilai kolom tersebut melebihi lebar kolom yang disediakan. Pilihan WRAPPED akan melanjutkan bagian yang tidak mencukupi ke baris yang baru pada kolom yang sama. Pilihan WORD_WRAPPED akan melanjutkan kata-kata yang tidak mencukupi ke baris yang baru pada kolom yang sama. Pilih TRUNCATE akan memotong bagian yang tidak mencukupi.
Selain itu ada tambahan format “DATE” yang bertujuan untuk menampilkan nilai NUMBER sebagai Julian_date dalam format DD/MM/YY. Berikut ini diberikan sebuah contoh performatan kolom-kolom dari data-data mahasiswa. Perintah-perintah tersebut ditulisakan di dalam script “rpt_mahasiswa.sql”.
--rpt_mahasiswa.sql /* Mengatur beberapa nilai system variable*/ SET PAGESIZE 600 SET LINESIZE 950 SET HEADSEP "|" SET RECSEP OFF /*Menghapus BREAKS, COMPUTES, dan COLUMNS*/ CLEAR BREAKS; CLEAR COMPUTES; CLEAR COLUMNS; /*Mengatur format kolom*/ COLUMN nim FORMAT A10 WORD_WRAPPED COLUMN nama FORMAT A20 WORD_WRAPPED COLUMN prodi FORMAT A10 WORD_WRAPPED HEADING "PRODI|JUR" COLUMN bayar FORMAT 99G999G999D99 /*Query untuk menampilkan data-data mahasiswa*/ SELECT nim,nama,prodi,bayar FROM mahasiswa ORDER BY 1,2;
Keluaran dari script diatas ditujukan berikut ini. SQL> @ C\:rpt_mahasiswa.sql NIM NAMA 30107001 30107003 30107004 30207005 30207006 30207007 30307008 30307009
ANGGI NOVEMBRA WIDO KESUMA MURDIJAT ARIF PRABOWO ANUGRAH DWI ANDRE ADRIAN ANDI HAKIM ARIF SWANDY EFRAIM MANALU SITI NURFAUZIYAH
PRODI JUR MI MI MI
BAYAR 2.000.000,00 2.000.000,00 15.000.000,00
TK TK TK KA KA
8.000.000,00 2.000.000,00 4.000.000,00 10.000.000,00 3.500.000,00
8 rows selected.
Perhatikan script dan keluaran di atas. Perhatikan pula bagian-bagian yang diberi latar abu-abu. Lebar kolom prodi ditentukan sebesar 10 karakter. Judulnya menjadi PRODI JUR dan dituliskan dalam dua baris (HEADING “PRODI | JUR”). Lebar kolom nama ditentukan sebesar 20 karakter dan diberikan pilihan WORD_WRAPPED. Dengan demikian maka string posisi yang lebih dari 20 karakter akan dituliskan dalam dua baris. Lihatlah nama “MURDIJAT ARIF PRABOWO” yang nama terakhirnya di-wrap. Kolom bayar diformat dengan pemisah grup (ribuan) dan diberi tanda decimal (tanda titik). Jumlah angka pecahan decimal adalah dua angka. Sebagai catatan tambahan, semua atribut tampilan yang ditentukan oleh perintah COLUMN dapat dihapus dengan menggunakan perintah CLEAR COLUMNS. d. Memberi Judul pada Halaman Judul halaman dapat diberikan dengan menggunakan perintah TTITLE dan BTITLE. TTITLE untuk judul atas, sedangkan BTITLE untuk judul bawah. Bentuk umum kedua perintah ini diperlihatkan di bawah ini. TTITLE{[printspec [text | variable ]]} [ON|OFF] BTITLE{[printspec [text | variable ]]} [ON|OFF]
Nilai printcpec bertujuan untuk menentukan spesifikasi cetak dari text atau variable. Pilihan-pilihan untuk printspec diberikan pada Tabel.
Tabel Spesifikasi Cetak pada Perintah TTITLE dan BTITLE Spesifikasi Cetak Kegunaan COL n Menentukan posisi cetak dimulai dari kolom ke n. S[KIP] [n] Memulai baris baru n berikutnya dari baris sebelumnya. Jika n tidak disertakan, n=1. Jika n=0, text atau variable dicetak pada baris berikutnya. TAB n Menambahkan tab maju sebanyak n.Jika n negative, yang dicetak adalah tab mundur LE[FT] | CE[NTER] | R[IGHT] Menentukan apakah judul dicetak rapat kiri, di tengah , atau rapat kanan BOLD Mencetak dengan huruf tebal. Pada SQL*Plus disimbolkan dengan mencetak text atau variable sebanyak tiga kali. Beberapa system operasi dapat menerjemahkan menjadi cetak tebal apabila dicetak ke printer. FORMAT format Menentukan format. Format yang valid adalah yang berlaku untuk perintah COLUMN. Nilai variable dapat diisi dengan user variable, termasuk variable yang dibuat dengan perintah COLUMN, dan juga dapat diisi dengan nilai-nilai system SQL*Plus. Nilainilai system SQL*Plus dapat berupa salah satu dari daftar berikut ini. SQL.LNO, untuk menampilkan nomor dari current line. SQL.PNO, untuk menampilkan nomor dari current page. SQL.RELEASE, untuk menampilkan nomor release Oracle. SQL.ERRORCODE, untuk menampilkan kode kesalahan. SQL.USER, untuk menampilkan nama current user. Kecuali SQL.ERRORCODE, nilai-nilai pada daftar di atas dapat juga ditampilkan menggunakan perintah SHOW. Namun, perintah SHOW tidak dapat yang sudah pernah diberikan. Pilihan ON atau OFF dapat diberikan untuk mengaktifkan ayau menonaktifkan TTITLE atau BTITLE tanpa harus menghapus definisi yang sudah diberikan. Jika perintah TTITLE atau BTITLE digunakan tanpa klausa apapun, perintah tersebut ditujukan untuk menampilkan definisi dari TTITLE atau BTITLE yang sedang berlaku. e. Menambahkan Report Header dan Footer Perintah untuk menambahkan report header dan report footer hampir sama dengan perintah TTITLE dab BTITLE. Bentuk umum perintahnya adalah sebagai berikut. REPH[EADER][PAGE]{[printspec[text|variable]]}[ON|OFF] REPF[OOTER][PAGE]{[printspec[text|variable]]}[ON|OFF]
Jika pilihan PAGE disertakan, halaman baru akan dibuat setelah report header ditampilkan atau sebelum report footer ditampilkan. Pilihan-pilihan lainnya sama fungsinya dengan fungsi pilihan pada perintah TTITLE atau BTITLE. Tidak seperti judul halaman, report header dan report footer hanya satu kali dicetak untuk satu SELECT statement. Jika SELECT Statement menghasilkan banyak baris data sehingga membutuhkan tempat lebih dari satu halaman, judul halaman (TITILE dan BTITLE) akan dicetak sebanyak halaman yang ada. Sedangkan report header ditampilkan sebelum SELECT statement diproses dan report footer ditampilkan setelah SELECT statement diproses. Jadi, yang dianggap satu report adalah satu SELECT statement. Report header dan report footer digunakan bersama-sama dengan judul-atas dan judul-bawah halaman. f. Menambahkan Tanggal Untuk menambahkan konstanta tanggal, tentunya dapat digunakan perintah TTITLE. Namun, untuk menambahkan tanggal sesuai dengan tanggal script dijalankan, dibutuhkan “kerjasama” antara perintah COLUMN, SQL, statement, dan TTITLE/BTITLE (atau REPHEADER/REPFOOTER). Perintah COLUMN digunakan untuk
menampung nilai user variable. SQL statement digunakan untuk menampung nilai SYSDATE ke dalam user variable. Perintah TTITLE digunakan untuk mencetaknya. Berikut ini diberikan sebuah contoh sederhana. Perintah COLUMN digunakan untuk membuat variable tg_skrg (NEW_VALUE) yang nilainya diambil dari kolom today. Kolom today dipilih pada SELECT statement dan nilainya adalah SYSDATE. Perintah TTITLE ditujukan untuk mencetak nilai dari variable tgl_skrg. 2. Menyimpan dan Mencetak Laporan Seperti yang telah disinggung pada awal bab ini, semua yang dipaparkan di dalam subbab ini berguna untuk membuat sebuah laporan berdasarkan data yang ada di dalam database. Biasanya, perintah-perintah dari sebuah laporan, termasuk pengaturan environment yang berhubungan dengan tampilan, disimpan di dalam file (script) agar dapat digunakan secara berulang. Hasil atau keluaran dari eksekusi file laporan tersebut biasanya juga disimpan di dalam file (soft copy) kemudian dapat dicetak di atas ertas (hard copy). Perintah SPOOL, dapat digunakan untuk mencetak keluaran ke atas kertas. Untuk mengingatkan kembali, perintah untuk memulai dan mengakhiri SPOOL ditampilkan berikut ini. SPOOL file_name[.ext] SPOOL [OFF|OUT]
Pilihan OFF maupun OUT sama-sama bertujuan untuk mengakhiri spooling (semua keluaran dituliskan ke dalam file spool). Khusus untuk OUT, setelah penulisan ke file dilakukan, file tersebut dikirim ke default printer untuk dicetak sebagai hard copy. Hanya saja pada system operasi Windows, perintah SPOOL OUT tidak mendukung fungsi pencetakan tersebut. Pilihan OUT mempunyai efek yang sama dengan OFF. Berikut ini diberikan sebuah script laporan dengan melibatkan perintah SPOOL. Keluaran dari script tersebut disimpan di dalam file yang namanya dapat ditentukan oleh user. Keluaran tidak ditampilkan pada layer SQL*Plus (SET TERMOUT OFF). --rpt_bayar_mahasiswa.sql -- Mengatur beberapa nilai system variable SET PAGESIZE 35 SET LINESIZE 60 SET HEADSEP ON SET RECSEP OFF SET FEEDBACK OFF -- Menghapus BREAKS, COMPUTES, dan COLUMNS CLEAR BREAKS CLEAR COMPUTES CLEAR COLUMNS -- Melakukan perhitungan jumlah pembayaran mahasiswa. BREAK ON prodi SKIP 2 COMPUTE AVG MAX MIN OF bayar ON prodi --Mengatur format kolom COLUMN today NOPRINT NEW_VALUE v_today COLUMN nim FORMAT A10 WORD_WRAPPED COLUMN nama FORMAT A20 WORD_WRAPPED COLUMN prodi FORMAT A10 WORD_WRAPPED HEADING "PRODI|JUR" COLUMN bayar FORMAT 99G999G999D99 --Mengakifkan RepHeader dan RepFooter REPHEADER "--START OF REPORT--" REPFOOTER "--END OF REPORT--"
--Memberi Judul TTITLE CENTER "DAFTAR MAHASISWA" SKIP CENTER"===============================================" SKIP 2 LEFT "User : "SQL.USER SKIP RIGHT" Halaman :"FORMAT 99 SQL.PNO SKIP LEFT" Tanggal :"FORMAT A20 v_today SKIP LEFT "=============================================================" BTITLE LEFT "---------------------------------------------------------------------------------------------" SKIP 2 --Mengaktifkan SPOOL. Nama file dapat diinput oleh user SET DEFINE ON ACCEPT file_name PROMPT "File penampung hasil report: " SPOOL &file_name --Menonaktifkan SET TERMOUT OFF
pencetakan keluaran ke SQL* PLUS
-- Query untuk menampilkan data-data mahasiswa SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')today,nim,nama,prodi,bayar FROM mahasiswa ORDER BY 1,2; --Menghentikan Spool SPOOL OFF --Mengembalikan beberapa nilai system variable ke default SET FEEDBACK ON SET RECSEP WRAPPED SET TERMOUT ON
Apabila script diatas dijalankan, yang ditampilkan hanya teks untuk meminta user memasukkan nama file (dan direktori) tempat penampungan hasil laporan seperti yang ditunjukkan berikut ini. SQL> @ C:\rpt_bayar_mahasiswa.sql File penampung hasil report : C:\BelajarSQL\rpt_hasil.txt
Anda dapat melihat isi file laporan yang baru saja dibuat menggunakan perintah GET atau membukanya dengan sembarang text editor. Apabila Anda meliaht isi file tersebut dengan menggunakan perintah GET, baris-baris kosong tidak akan dimunculkan. Isi file laporan tersebut adalah seperti berikut ini. DAFTAR MAHASISWA User
: DEDY
Tanggal
: 09-AGT-2008
Halaman :
--START OF REPORT-NIM 30107001 30107003 30107004
NAMA ANGGI NOVEMBRA WIDO KESUMA MURDIJAT ARIF PRABOWO
PRODI JUR MI
BAYAR 2.000.000,00 2.000.000,00 15.000.000,00
* -----------------avg 6.333.333,33 minimum 2.000.000,00 maximum 15.000.000,00 30207005 30207006 30207007
ANUGRAH DWI ARDIANTO ANDRE ADRIAN ANDI HAKIM ARIF
TK
8.000.000,00 2.000.000,00 4.000.000,00 * ----------------avg 4.666.666,67 minimum 2.000.000,00 maximum 8.000.000,00
1
30307008 30307009 -
SWANDY EFRAIM MANALU SITI NURFAUZIYAH
KA
10.000.000,00 3.500.000,00
DAFTAR MAHASISWA User : DEDY Halaman : 2 Tanggal : 09-AGT-2008
NIM
NAMA
PRODI JUR
BAYAR * ----------------avg 6.750.000,00 minimum 3.500.000,00 maximum 10.000.000,00
--END OF REPORT--
3. Menciptakan output HTML dengan SQL * Plus Sejak versi 8.1.6 oracle memungkinkan kita untuk menciptakan HTML dengan SQL*Plus. Sangatlah mudah untuk mengubah script dari mode karakter ke html, karena seluruh tag SQL * Plus dapat dikonversi ke tag HTML tanpa mengubah kode. --rpt_bayar_mahasiswa_html.sql --menjalankan output dalam html dan melakukan spool terhadap output SET MARKUP HTML ON SPOOL ON --menentukan file dengan ekstension htm atau html SPOOL file_name.html --Query SELECT * FROM table_name; --mengakhiri file html SPOOL OFF --mengatur kembali ke output normal SET MARKUP HTML OFF SPOOL OFF
Kemudian perintah diatas dieksekusi sbb: SQL> @ C:\rpt_bayar_mahasiswa_html.sql File penampung hasil report : C:\BelajarSQL\rpt_hasil.html
Berikut ini contohnya jika report yang telah dibuat sebelumnya dijadikan file HTML: --Menjalankan output dalam html SET MARKUP HTML ON SPOOL ON -- Mengatur beberapa nilai system variable SET PAGESIZE 35 SET LINESIZE 60 SET HEADSEP ON SET RECSEP OFF SET FEEDBACK OFF -- Menghapus BREAKS, COMPUTES, dan COLUMNS CLEAR BREAKS CLEAR COMPUTES CLEAR COLUMNS -- Melakukan perhitungan jumlah penerimaan pegawai per-tahun. BREAK ON prodi SKIP 2 COMPUTE AVG MAX MIN OF bayar ON prodi
--Mengatur format kolom COLUMN today NOPRINT NEW_VALUE v_today COLUMN nim FORMAT A10 WORD_WRAPPED COLUMN nama FORMAT A20 WORD_WRAPPED COLUMN prodi FORMAT A10 WORD_WRAPPED HEADING "PRODI|JUR" COLUMN bayar FORMAT 99G999G999D99 --Menonakifkan RepHeader dan RepFooter REPHEADER "--START OF REPORT--" REPFOOTER "--END OF REPORT--" --Memberi Judul TTITLE CENTER "DAFTAR MAHASISWA" SKIP CENTER"===============================================" SKIP 2 LEFT "User : "SQL.USER SKIP RIGHT "Halaman :"FORMAT 99 SQL.PNO SKIP LEFT "Tanggal :"FORMAT A20 v_today SKIP LEFT "=============================================================" BTITLE LEFT "---------------------------------------" SKIP 2 --Mengaktifkan SPOOL. Nama file dapat diinput oleh user SET DEFINE ON ACCEPT file_name PROMPT "File penampung hasil report: " SPOOL &file_name --Menonaktifkan SET TERMOUT OFF
pencetakan keluaran ke SQL* PLUS
-- Query untuk menampilkan data-data pegawai SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')today,nim,nama,prodi,bayar FROM mahasiswa ORDER BY 1,2; --Menghentikan Spool SPOOL OFF --Mengembalikan beberapa nilai system variable ke default SET FEEDBACK ON SET RECSEP WRAPPED SET TERMOUT ON SET MARKUP HTML OFF SPOOL OFF;
Hasil dari eksekusi script diatas adalah sbb: DAFTAR MAHASISWA User : DEDY Halaman : 1 Tanggal : 09-AGT-2008 NIM 30107001 30107003 30107004
30207005 30207006 30207007
30307008 30307009
NAMA ANGGI NOVEMBRA WIDO KESUMA MURDIJAT ARIF PRABOWO
ANUGRAH DWI ARDIANTO ANDRE ADRIAN ANDI HAKIM ARIF
SWANDY EFRAIM MANALU SITI NURFAUZIYAH
PRODI JUR MI
* avg minimum maximum TK
BAYAR 2.000.000,00 2.000.000,00 15.000.000,00
6.333.333,33 2.000.000,00 15.000.000,00 8.000.000,00 2.000.000,00 4.000.000,00
* avg minimum maximum KA
4.666.666,67 2.000.000,00 8.000.000,00 10.000.000,00 3.500.000,00
* avg minimum maximum
6.750.000,00 3.500.000,00 10.000.000,00
Tugas Pendahuluan : 1. Jelaskan fungsi dari report. 2. Apa kondisi/alasan yang menyebabkan dibuatnya suatu report? 3. Bagaimana bentuk format date yang benar untuk suatu kolom pada Oracle sehingga data tanggal benar-benar bisa dimasukkan dalam kolom tersebut. Percobaan : 1. Berdasarkan contoh-contoh yang ada buatlah report yang menampilkan seluruh data buku lengkap dengan jenis dan suppliernya berdasarkan pada database yang dibuat pada modul 6 (format report harus sejelas dan selengkap mungkin)! 2. Dari report yang ada di nomor 1 buatlah output HTML!
