Fungsi Single Row dan Multi Row pada Oracle

  • Published on
    14-Jul-2015

  • View
    270

  • Download
    1

Embed Size (px)

Transcript

SVManagerTeam Manager

LAPORAN TERTULIS ON THE JOB TRAINING

I. TANGGAL : 13 17 Oktober 2014 II. TUJUAN: 1. Dapat memahami Fungsi single row dan multi row.2. Dapat membuat contoh penggunaan fungsi pada tabel.3. Dapat menerapkan command pada tabel. SUBJEK PEMBAHASAN :

**************1. Single Row Function Single row function adalah fungsi yang digunakan pada baris tunggal (baris per baris) dan mengembalikan satu hasil per baris tersebut. Yang termasuk dalam single row function yaitu ditunjukkan seperti pada gambar berikut:

a. Fungsi general Yang termasuk fungsi general yaitu: 1. 2. NVL3. NVL2 4. NULLIF5. COALESCE 6. CASE 7. DECODE8. GREATEST9. LEAST

Penggunaan dari tiap fungsi diatas telah dijelaskan pada pembahasan sebelumnya.

b. Fungsi characterYang termasuk fungsi character yaitu:1. 2. ASCII3. ASCIISTR4. CHR5. COMPOSE6. CONCAT7. Concat with ||8. CONVERT9. DECOMPOSE10. DUMP11. INITCAP12. INSTR13. INSTR214. INSTR415. INSTRB16. INSTRC17. LENGTH18. LENGTH219. LENGTH420. LENGTHB21. LENGTHC22. LOWER23. LPAD24. LTRIM25. NCHR26. REPLACE27. RPAD28. RTRIM29. SOUNDEX30. SUBSTRTO_CHAR31. TO_DATE32. TRANSLATE33. TRIM34. UPPER35. VSIZE

Penggunaan dari fungsi yang umum digunakan telah dijelaskan pada pembahasan sebelumnya.

c. Fungsi number Yang termasuk dalam fungsi number (angka) yaitu:

1. ABS2. ACOS3. ASIN4. ATAN5. ATAN26. AVG7. BIN_TO_NUM8. BITAND9. CEIL10. CORR11. COS12. COSH13. COUNT14. COVAR_POP15. COVAR_SAMP16. CUME_DIST17. DENSE_RANK18. EXP19. EXTRACT20. FLOOR21. GREATEST22. LEAST23. LN24. LOG25. MAX26. MEDIAN27. MIN28. MOD29. POWER30. RANK31. REMAINDER32. ROUND (numbers)33. SIGN34. SIN35. SINH36. SQRT37. STDDEV38. SUM39. TAN40. TANH41. TO_NUMBER42. TRUNC (numbers)43. VARIANCE44. VAR_POPVAR_SAMP

Dari beberapa fungsi tersebut, dalam pembahasan ini akan dibahas beberapa fungsi yang sering digunakan yaitu:

1. ABSFungsi ABS merupakan singkatan dari ABSOLUTE yaitu mengambil nilai mutlak dari suatu bilangan. Walaupun nilai yang digunakan adalah bilangan negatif, dengan menggunakan fungsi ABS nilai tersebut bernilai positif. Adapun syntax penulisan dari fungsi ABS yaitu:

ABS( angka )Contoh penggunaan ABS ditunjukkan pada gambar berikut:

2. CEILFungsi CEIL mengembalikan hasil pembulatan angka lebih besar atau sama dengan dari suatu angka. Adapun syntax dari CEIL ini ditunjukkan sebagai berikut:

CEIL( number )Berikut diberikan contoh penggunaan dari CEIL

Terlihat bahwa fungsi CEIL akan membulatkan ke atas nilai yang memiliki digit koma lebih dari 0. Meskipun tidak sama dengan kaidah matematika dimana pembulatan keatas akan dilakukan jika nilai tersebut lebih dari 0.5.

3. FLOORFungsi dari FLOOR adalah untuk membulatkan angka ke bawah atau sama dengan nilai angka tersebut. Berlawanan dengan CEIL, FLOOR akan membulatkan kebawah meskipun nilai digit di belakang koma >=5. Adapun syntax dan contoh penggunaan dari fungsi FLOOR ditunjukkan sebagai berikut: FLOOR( number )

4. MODFungsi MOD dalah modulus, yaitu mengembalikan sisa hasil pembagian dari 2 buah angka. Yang dikembalikan adalah sisa hasil bagi, bukan angka hasil pembagian antara 2 bilangan tersebut. Adapun syntax dan contoh penggunaan fungsi MOD ditunjukkan sebagai berikut:

MOD( m, n )Dimana m = bilangan utama n = bilangan pembagi

5. POWERFungsi POWER adalah pemangkatan dan POWER akan mengembalikan hasil dari pemangkatan kedua nilai tersebut. Adapun contoh dari

POWER( m, n )

Pada gambar terlihat bahwa jika angka dipangkatkan dengan bilangan negatif maka akan menghasilkan bilang berkoma.

6. ROUNDFungi ROUND adalah untuk membulatkan angka sesuai dengan kaedah matematika (jika < 5 pembulatan ke bawah dan jika >= 5 pembulatan ke atas) dan pembulatan berdasarkan sejumlah tertentu. Adapun syntax penulisan dan contoh penggunaan dari fungsi round yaitu sebagai berikut:ROUND( angka, [ jumlah_angka_dibelakang_koma ] )

7. SIGNFungsi SIGN adalah untuk mengembalikan nilai indikator atau tanda dari suatu angka. Dimana : Jika bilangan = 0, maka akan dikembalikan 0 Jika bilangan < 0 , maka akan dikembalikan -1 Jika bilangan > 0, maka dikembalikan 1.

Adapun syntax penulisan dan contoh penggunaan ditunjukkan sebagai berikut:SIGN(bilangan)

8. TRUNC (numbers)Fungsi TRUNC adalah untuk memotong digit pada bilangan desimal suatu bilangan berdasarkan sejumlah angka tertentu. Adapun syntax dan contoh penggunaan TRUNC ditunjukkan sebagai berikut:

TRUNC( angka, [ jumlah_angka_dibelakang_koma] )

Pada gambar bahwa fungsi TRUNC tidak membulatkan angka, fungsi ini hanya memotong jumlah angka di belakang koma sesuai dengan format yang telah di buat.

d. Fungsi date Yang termasuk dalam fungsi date (tanggal) yaitu:

1. ADD_MONTHS2. CURRENT_DATE3. CURRENT_TIMESTAMP4. DBTIMEZONE5. FROM_TZ6. LAST_DAY7. LOCALTIMESTAMP8. MONTHS_BETWEEN9. NEW_TIME10. NEXT_DAY11. ROUND (dates)12. SESSIONTIMEZONE13. SYSDATE14. SYSTIMESTAMP15. TO_CHAR16. TO_DATE17. TO_TIMESTAMP18. TO_TIMESTAMP_TZ19. TO_YMINTERVAL20. TRUNC (dates)21. TZ_OFFSET

e. Fungsi conversionFungsi konversi terbagi menjadi dua jenis, yaitu: a. Implisit b. Eksplisit

1. ImplisitFungsi implisit maksudnya adalah fungsi yang secara tidak terlihat (oleh user) akan mengubah suatu tipe data menjadi tipe data yang lain. Pada suatu kasus akan terjadi dimana ketika penulisan program,user tidak membuat fungsi konversi secara langsung, namun Oracle akan tetap memperkirakan dan mengkonversi tipe data tersebut sehingga bisa dimengerti dan diproses oleh system. Adapun tipe data yang diizinkan dalam fungsi konversi implisit yaitu seperti gambar berikut:

Misal pada ekspresi berikut: hire_date > 01JAN 90 ; maksudnya adalah oracle akan mengubah karakter string dalam tanda menjadi suatu format tanggal yang bersesuaian dengan 1 Januari 1990. salary = 50000 ; maksudnya adalah oracle akan mengubah karakter string dalam tanda menjadi format angka 50000. Pengkonversian akan berhasil jika angka yang digunakan dalam string merupakan angka yang benar dan valid.

2. Eksplisit Fungsi konversi eksplisit maksudnya adalah fungsi konversi yang tipe pengkonversiannya ditentukan oleh User. Yang termasuk dalam fungsi konversi eksplisit yaitu:

1. BIN_TO_NUM2. CAST3. CHARTOROWID4. FROM_TZ5. HEXTORAW6. NUMTODSINTERVAL7. NUMTOYMINTERVAL8. RAWTOHEX9. TO_CHAR10. TO_CLOB11. TO_DATE12. TO_DSINTERVAL13. TO_LOBTO_MULTI_BYTE14. TO_NCLOB15. TO_NUMBER16. TO_SINGLE_BYTE17. TO_TIMESTAMP18. TO_TIMESTAMP_TZ19. TO_YMINTERVAL

Dari fungsi-fungsi di atas, adapun fungsi yang dibahas dalam laporan ini yaitu1. TO_CHAR2. TO_DATE3. TO_NUMBER

Untuk memudahkan mengingat bagaimana penggunaan fungsi konversi diatas maka contohnya ditunjukkan pada gambar berikut:

TO_CHARFungsi dari TO_CHAR adalah untuk mengkonversi nilai suatu bilangan (angka) atau tanggal menjadi suatu karakter string VARCHAR2. Adapun syntax penulisan dari TO_CHAR ditunjukkan seperti berikut:

TO_CHAR( nilai, [format])

Untuk penulisan format, biasanya pada TOC_AHR dibuat berdasarkan elemen yang digunakan. Berikut dijelaskan tentang elemen pada format dan contoh penggunaan TO_CHAR.

TO_NUMBERFungsi dari TO_NUMBER adalah untuk mengubah karakter string menjadi karatker bilangan (angka). Fungsi TO_NUMBER akan berhasil jika pada karakter string,merupakan angka yang benar, angka yang valid, dan tidak bercampur dengan karakter huruf, dll.

TO_DATEFungsi dari TO_DATE adalah untuk mengubah format karakter string menjadi format tanggal berdasarkan format yang telah ditentukan. Jika format tidak ditulis, maka format yang digunakan adalah DDMONYY. Dalam penggunaan TO_DATE, format penulisan harus dituliskan, dan berikut dijelaskan format tanggal yang digunakan

Berikut contoh penggunaaan pada TO_DATE.

2. Multi Rows Function Multi row function adalah fungsi yang digunakan pada beberapa baris, berdasarkan group, kategori ataupun keseluruhan baris yang ada pada tabel dan mengembalikan satu buah hasil. Multi row function disebut juga aggeragate function. Adapun yang termasuk dalam aggregate function ini adalah :

1. AVG2. COLLECT3. CORR4. CORR_*5. COUNT6. COVAR_POP7. COVAR_SAMP8. CUME_DIST9. DENSE_RANK10. FIRST11. GROUP_ID12. GROUPING13. GROUPING_ID14. LAST15. MAX16. MEDIAN17. MIN18. PERCENTILE_CONT19. PERCENTILE_DISC20. PERCENT_RANK21. RANK22. REGR_ (Linear Regression) Functions23. STATS_BINOMIAL_TEST24. STATS_CROSSTAB25. STATS_F_TEST26. STATS_KS_TEST27. STATS_MODE28. STATS_MW_TEST29. STATS_ONE_WAY_ANOVA30. STATS_T_TEST_*31. STATS_WSR_TEST32. STDDEV33. STDDEV_POP34. STDDEV_SAMP35. SUM36. VAR_POP37. VAR_SAMP38. VARIANCE

Dari fungsi-fungsi diatas, dalam pembahasan ini akan dibahas beberapa fungsi yang sering digunakan yaitu: 1. AVGFungsi AVG ini akan mengembalikan nilai rata-rata dari beberapa baris (sesuai dengan pengkondisian atau syarat yang digunakan) atau keseluruhan baris dari suatu kolom. Oleh karena itu penggunaan fungsi AVG ini digunakan pada tipe data yang berupa angka (bilangan) yang valid dan menolak tipe data lain. Adapun syntax penulisan dari AVG dijelaskan sebagai berikut: SELECT AVG(nama_kolom)FROM nama_tableWHERE kondisi_syarat; -- ini opsional (bisa digunakan bisa juga tidak)Penggunaan kondisi WHERE tergantung pada kebutuhan akan nilai yang ingin dihasilkan. Adapun contoh penggunaan AVG ditunjukkan sebagai berikut:

2. COUNTFungsi COUNT adalah untuk menghitung jumlah baris yang terdapat pada suatu kolom. Adapun syntax dari fungsi COUNT ditunjukkan sebagai berikut: SELECT COUNT( ekspresi_nama_kolom )FROM tabelWHERE kondisi; -- ini opsional (bisa digunakan bisa juga tidak)Adapun contoh penggunaan COUNT ditunjukkan sebagai berikut:

Terlihat bahwa dengan menggunakan perintah COUNT (*) ki