RESUME SQL 1 PERT.3

Menggunakan Single Row Function untuk meng-customize Output

Fungsi fungsi SQL

Fungsi fungsi sangat mendukung fitur pada SQL

 Melakukan perhitungan perhitungan pada data

 Memodifikasi item item data individual

 Memanipulasi keluaran dari kelompok baris baris

 Format untuk tampilan tanggal dan angka angka

 Konversi tipe data tipe data kolom

Ada dua tipe dari fungsi fungsi

 Single rows functions (fungsi fungsi baris tunggal)

 Multiple row functions (fungsi fungsi banyak baris)

Single Row Functions

Fungsi fungsi ini hanya digunakan pada baris baris tunggal dan mengembalikan satu hasil per baris.

Ada tipe tipe berbeda dari singlerow functions. Single row function mencakup hal hal berikut :

 Character (karakter)

 Number (angka)

 Date (tanggal)

 Conversion (konversi)

 General (umum)

Multiple row Functions

Fungsi fungsi dapat memanipulasi kelompok dari baris baris untuk memberi suatu hasil baris baris per kelompok. Fungsi fungsi ini dikenal juga sebagai group functions

Single Rows Functions

Singlerow function digunakan untuk memanipulasi item item data. Singlerow functions menerima satu atau lebih argument argument dan mengembalikan satu nilai untuk setiap baris yang dihasilkan oleh suatu query. Suatu argumen dapat berupa berikut ini :

 Usersupplied constant (Konstanta yang disediakan oleh user)

 Nilai variable

 Nama Kolom

 Ekspresi

Fitur fitur dari singlerow functions mencakup :

 Aksi pada setiap baris yang dikembalikan di dalam query

 Mengembalikan satu hasil per baris

 Memungkinkan pengembalian suatu nilai data dari suatu tipe berbeda daripada satu yang direferensikan.

 Mungkin menerima satu atau lebih argument argumen

 Dapat digunakan didalam klausa klausa SELECT, WHERE, dan ORDER BY;dapat disarangkan(nested)

Dalam sintak :

function_name adalah nama dari fungsi arg1, arg2 adalah setiap argumen yang digunakan oleh fungsi. Hal ini bisa jadi diwakili oleh suatu kolom atau ekspresi.

Cakupan single row function:

 Character Functions : Menerima input karakter dan dapat mengembalikan baik nilai nilai karakter ataupun angka.

 Number Functions : Menerima masukkan angka dan mengembalikan nilai nilai angka

 Date Functions : Bekerja pada nilai nilai dari tipe data DATE (semua date functions

mengembalikan suatu nilai dari tipe data DATE kecuali fungsi MONTHS_BETWEEN, yang mengembalikan suatu angka.)

 Coversion Functions : Mengkonversi suatu nilai dari satu tipe data ke tipe data lainnya.

 General Functions (fungsi fungsi umum) :

NVL

NVL

2

NULLIF

COALESCE

CASE

DECODE

Character Functions

Singlerow character functions menerima data karakter sebagai masukan dan dapat mengembalikan

baik nilai nilai karakter maupun angka. Character functions dapat dibagi menjadi :

 Case manipulation functions

 Characte rmanipulation Functions

LOWER(column/expression) : Mengkonversi nilai nilai karakter huruf menjadi lowercase (huruf kecil)

UPPER(column/expression) : Mengkonversi nilai nilai karakter huruf menjadi uppercase (huruf besar)

INITCAP(column/expression) : Mengkonversi nilai nilai alpha karakter menjadi uppercase untuk huruf pertama dari tiap kata; semua huruf huruf lain lowercase

CONCAT(column1/expressio1,colum2/expression2) : Menggabungkan nilai karakter pertama ke karakter kedua : sama dengan operator penggabungan ( || )

SUBSTR (column/expression,m[,n]) : Menghasilkan karakter karakter tertentu dari nilai karakter

dimulai pada posisi karakter kem,ken panjang karakter (jika m adalah negatif, dihitung mulai dari akhir nilai karakter. Jika n dihilangkan, menghasilkan semua karakter sampai akhir dari rangkaian.)

LENGTH (column/expression) : Mengembalikan jumlah karakter dalam ekspresi

INSTR (column/expression, 'string', [,m], [n]) : Mengembalikan posisi numerik dari suatu rangkaian penamaan. Secara optional, Anda dapat menyediakan suatu posisi kem untuk memulai pencarian, dan yang terjadi din dari suatu rangkaian. m dan n defaultnya 1, artinya pencarian dimulai di awal suatu pencarian dan melaporkan kejadian yang pertama.

LPAD (column /expression, n,'string') : Mengisi nilai karakter perataan kanan (rightjustified) ke suatu lebar total n posisi karakter

RPAD (column /expression, n,'string'): Mengisi nilai karakter perataan kiri (leftjustified) ke suatu lebar total n posisi karakter

TRIM (leading/trailing/both,trim_character FROM trim_source) : Memungkinkan Anda untuk memotong karakter karakter bagian awal atau bagian akhir atau kedua duanya dari suatu rangkaian karakter. Jika trim_character atau trim_source adalah suatu karakter literal, Anda harus mengapitnya dengan tanda petik tunggal. Ini adalah fitur yang ada di Oracle8i dan versi selanjutnya.

REPLACE (text, search_string, replacement_string): Mencari suatu ekspresi teks untuk suatu rangkaian karakter dan, jika ditemukan, digantikan dengan rangkaian yang telah ditentukan.

Contoh:

• LOWER (‘SQL Course’) à sql course
• UPPER (‘SQL Course’) à SQL COURSE
• INITCAP (‘SQL Course’) à Sql Course
• CONCAT(‘Halo’, ‘Dunia’) à HaloDunia
• SUBSTR (‘HaloDunia’,1,4) à Halo
• LENGTH(‘HaloDunia’) à 9
• INSTR(‘HaloDunia’, ‘D’) à 5
• LPAD (salary,10,’*’) à *****24000
• RPAD (salary,10,’*’) à 24000*****
• REPLACE (‘JACK and JUE’, ‘J’, ‘BL’) à BLACK and BLUE
• TRIM(‘H’ FROM ‘HaloDunia’) à aloDunia

ROUND (column | expression, n) : Membulatkan kolom, ekspresi, atau nilai posisi ken desimal atau, jika n dihilangkan, tidak ada posisi desimal ( Jika n adalah negatif, angka angka di kiri dari desimal dibulatkan.)

TRUNC (column | expression, n) : Memotong kolom, ekspresi, atau nilai posisi ken desimal atau, jika n dihilangkan, n defaultnya nol

MOD (m, n) : Mengembalikan sisa dari m yang dibagi oleh n

Contoh:

ROUND (45.926, 2) à 45.93

TRUNC (45.926, 2) à 45.92

MOD (1600, 300) à 100

Format Tanggal Oracle

Database Oracle menyimpan tanggal tanggal dalam suatu format angka sendiri, yang menunjukkan abad, tahun, bulan, hari, jam, menit, dan detik.

Tampilan default dan format inputan untuk setiap tanggal adalah DD-MON-RR.

Tanggal Oracle berlaku antara January 1,4712 B.C. (1 Januari 4712 S.M.) dan December 31, 9999 A.D. (31 Desember 9999 M.).

Contoh:

SELECT last_name, hire_date

FROM employee

WHERE hire_date < ’01-FEB-88’;

Contoh di atas, suatu output kolom HIRE_DATE ditampilkan format default DDMONRR.

Meskipun begitu, tanggal tanggal tidak disimpan dalam database dengan format ini. Semua bagian bagian dari tanggal dan waktu disimpan. Maka, walaupun suatu HIRE_DATE seperti 17-JUN-87 ditampilkan sebagai hari, bulan, dan tahun, ada juga informasi waktu dan abad disatukan dengan date tersebut. Data yang lengkap bisa jadi June 17, 1987, 5:10:43 p.m. (17 Juni 1987, 5:10:43 petang).

Saat suatu record pada suatu kolom tanggal disisipkan ke dalam suatu tabel, suatu informasi abad diambil dari fungsi SYSDATE. Meskipun demikian, ketika suatu kolom tanggal ditampilkan di layar, bagian abad tidak ditampilkan (secara default).

Secara internal tipe data DATE selalu menyimpan informasi tahun sebagai empat digit angka : dua digit untuk abad dan dua digit untuk tahun. Sebagai contoh, database Oracle menyimpan tahun dengan cara 1987 atau 2004, dan tidak seperti 87 atau 04.

Fungsi SYSDATE

SYSDATE adalah fungsi tanggal yang mengembalikan tanggal dan waktu server database saat ini.

Anda dapat menggunakan SYSDATE seperti Anda ingin menggunakan setiap nama kolom lain.

Sebagai contoh, Anda dapat menampilkan tanggal saat ini dengan memilih SYSDATE dari suatu tabel. Biasanya tabel untuk memilih SYSDATE dari suatu tabel contoh disebut tabel DUAL.

Contoh

Menampilkan tanggal saat ini menggunakan tabel DUAL.

SELECT SYSDATE

FROM DUAL;

Aritmatika pada Dates

 Menambah atau mengurangkan suatu angka ke atau dari suatu tanggal yang menghasilkan nilai date.

 Mengurangkan dua tanggal untuk mencari angka dari hari hari antara tanggal tanggal tersebut.

 Menambah jam ke suatu tanggal dengan membagi suatu angka dari jam dengan 24.

Dates Functions

Date function bekerja pada tanggal –tanggal Oracle. Semua date function mengembalikan suatu nilai dengan tipe data DATE kecuali MONTHS_BETWEEN,yang mengembalikan nilai numerik.

 MONTHS_BETWEEN (date1, date2) : Mencari jumlah bulan diantara date1 dan

date2. Hasilnya bisa jadi positif atau negatif. Jika date1 lebih awal daripada date2,

hasilnya adalah positif ; jika date1 lebih awal daripada date2, hasilnya adalah negatif .

Sebagian dari hasil bukan bilangan bulat (noninteger) menunjukkan suatu bagian dari bulan.

 ADD_MONTHS (date, n) : Menambahkan n jumlah suatu bulan kalender ke date. Nilai dari n harus bilangan bulat (integer) dan bisa negatif.

 NEXT_DATE (date, ’char’) : (‘char’) setelah date menemukan suatu tanggal

dari suatu hari tertentu pada suatu minggu. Nilai dari char bisa angka yang mewakili suatu hari atau suatu karakter string.

 LAST_DAY (date) : Mencari hari terakhir dari suatu tanggal dalam suatu bulan yang

berisi date.

 ROUND (date[,’fmt’] ) : Mengembalikan pembulatan date ke suatu unit yang

ditentukan oleh model format fmt. Jika model format fmt dihilangkan, date dibulatkan

ke hari terdekat.

 TRUNC (date[,’fmt’] ) : Mengembalikan date dengan bagian suatu waktu dari suatu

hari yang dipotong ke unit yang ditentukan oleh model format fmt. Jika model format fmt

dihilangkan , date dipotong ke hari terdekat.

Conversion Functions

Disamping tipe data tipe data Oracle, kolom kolom dari table table di dalam Oracle dapat

didefinisikan menggunakan tipe data tipe data ANSI, DB2, dan SQL/DS. Meskipun demikian, server Oracle secara internal merubah tipe data tipe data yang sama dengan tipe data tipe data Oracle.

Dalam beberapa kasus, server Oracle menggunakan data dari suatu tipe data dimana server Oracle memperkirakan data dari suatu tipe data yang lain. Ketika ini terjadi, server Oracle dapat secara otomatis mengubah suatu data ke tipe data yang diperkirakan. Perubahan tipe data ini bisa jadi dilakukan secara implisit (implicitly) oleh server Oracle atau secara eksplisit (explicitly) oleh user.

Konversi konversi tipe data implisit bekerja menurut aturan aturan yang dijelaskan dalam slide berikutnya.

Konversi konversi tipe data eksplisit dilakukan dengan menggunakan conversion functions.

Conversion functions merubah suatu nilai dari suatu tipe data ke tipe data lain. Umumnya, bentuk dari nama nama fungsi mengikuti konvensi (kesepakatan) data type TO data type. Tipe data yang pertama adalah tipe data input;tipe data yang kedua adalah output.

Catatan : Meskipun disediakan konversi tipe data implisit, Anda diijinkan melakukan konversi tipe data eksplisit untuk memastikan kebenaran pernyataan SQL Anda.

Konversi Tipe Data Implisit

Untuk penugasan penugasan, server Oracle dapat secara otomatis mengkonversi berikut ini :

Dari varchar2 ke number

Dari varchar2 ke date

Dari number ke varchar2

Dari date ke varchar2