SQL Function in Oracle

Posted: April 5, 2009 in Database
Tags: , ,

TOP

SQL Server:

SELECT TOP 10 product, descr, email
FROM products

ORACLE:

SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10
&#91;/sourcecode&#93;

MySQL:

&#91;sourcecode language='sql'&#93;
SELECT product, descr, email
FROM products
LIMIT 10
&#91;/sourcecode&#93;

<span id="more-605"></span>

<em><strong>DISTINCT</strong></em>

Digunakan apabila kita ingin menghilangkan duplikasi dari hasil query (hasil query yang sama ditampilkan sekali)


SELECT Description FROM employee

Hasilnya :

DESCRIPTION
—————
Programmer
Tester
Tester
Manager
Tester
Tester
Manager
Tester

SELECT DISTINCT Description FROM employee

Hasilnya :

DESCRIPTION
—————
Programmer
Manager
Tester


CONCAT

concat adalah fungsi yang memungkinkan untuk menggabungkan dua buah string
Sintaks untuk fungsi concat adalah:

concat (string1, string2)

string1 adalah string pertama untuk digabungkan.
string2 adalah string kedua untuk digabungkan.

concat(‘Tech on’, ‘ the Net’); akan mengembalikan ‘Tech on the Net’.
concat(‘a’, ‘b’) akan mengembalikan ‘ab’.

NVL
NVL adalah fungsi yang dapat digunakan untuk menggantikan nilai ketika nilai null terjadi.

Sintaks untuk fungsi NVL adalah:

NVL (string1, replace_with)

string1 adalah string yang akan diuji untuk nilai null.
replace_with adalah nilai kembali jika string1 adalah null.

Contoh # 1:

select NVL (supplier_city, 'n / a')
from suppliers;

SQL dengan pernyataan di atas akan mengembalikan nilai ‘n/a’ jika supplier_city berisi nilai null. Jika tidak, akan mengembalikan nilai supplier_city.

Contoh # 2:

select supplier_id,
NVL (supplier_desc, supplier_name)
from suppliers;

SQL ini akan mengembalikan supplier_name jika supplier_desc yang berisi nilai null. Jika tidak, akan mengembalikan supplier_desc.

Contoh # 3:

select NVL (commission, 0)
from sales;

SQL akan mengembalikan nilai 0 jika commission bernilai null. Jika tidak, akan mengembalikan nilai commission.

ABS
abs adalah fungsi yang mengembalikan nilai absolut dari angka.

Sintaks untuk fungsi abs adalah:

abs(number)

number adalah nomor dikonversi menjadi nilai absolut

abs(-23) akan mengembalikan 23
abs(-23.6) akan mengembalikan 23.6
abs(-23.65) akan mengembalikan 23.65
abs(23.65) akan mengembalikan 23.65
abs(23.65 * -1) akan mengembalikan 23.65

POWER

POWER adalah fungsi m dipangkatkan n

Sintaks untuk fungsi POWER adalah:

power (m, n)

m adalah dasar.
n adalah eksponen.
Jika m negatif, maka n harus merupakan integer.

power(3, 2) mengembalikan nilai 9
power(5, 3) mengembalikan nilai 125
power(-5, 3) mengembalikan nilai -125
power(6.2, 3) mengembalikan nilai 238.328
power(6.2, 3.5) mengembalikan nilai 593.431934277892

TO_CHAR

Contoh

TO_CHAR(Date, 'Month')
SELECT ID, TO_CHAR(Start_Date, 'Mon') AS "Mon" FROM Employee;
TO_CHAR(Date, 'YYYY')
SELECT ID, TO_CHAR(Start_Date, 'YYYY') AS "Year" FROM Employee;
TO_CHAR(Date,'DAY MONTH DD, YYYY')
SELECT ID, TO_CHAR(Start_Date,'DAY MONTH DD, YYYY') AS "Start Date" FROM Employee;

ADD_MONTHS

Dalam Oracle / PLSQL, fungsi yang add_months kembali ditambah tanggal n bulan.

Sintaks untuk add_months fungsi adalah:

add_months (date1, n)

date1 yang dimulai tanggal (sebelum n bulan telah ditambahkan).

n adalah jumlah tahun ke date1.

add_months(’01-Aug-03′, 3) –> ’01-Nov-03′
add_months(’01-Aug-03′, -3) –> ’01-May-03′
add_months(’21-Aug-03′, -3) –> ’21-May-03′
add_months(’31-Jan-03′, 1) –> ’28-Feb-03′

MONTHS_BETWEEN

months_between adalah fungsi untuk mengembalikan jumlah bulan antara date1 dan date2.

Sintaks untuk months_between fungsi adalah:

months_between (date1, date2)

date1 dan date2 adalah tanggal yang digunakan untuk menghitung jumlah bulan.

Jika pecahan bulan dihitung, fungsi months_between menghitung pecahan berdasarkan bulan 31 hari.

contoh

months_between (to_date (‘2003/01/01’, ‘yyyy/mm/dd’), to_date (‘2003/03/14’, ‘yyyy/mm/dd’) )
akan mengembalikan -2.41935483870968

months_between (to_date (‘2003/07/01’, ‘yyyy/mm/dd’), to_date (‘2003/03/14’, ‘yyyy/mm/dd’) )
akan mengembalikan 3.58064516129032

months_between (to_date (‘2003/07/02’, ‘yyyy/mm/dd’), to_date (‘2003/07/02’, ‘yyyy/mm/dd’) )
akan mengembalikan 0

months_between (to_date (‘2003/08/02’, ‘yyyy/mm/dd’), to_date (‘2003/06/02’, ‘yyyy/mm/dd’) )
akan mengembalikan 2

GROUP BY

Digunakan untuk pengelompokan dari fungsi-fungsi aggregate. Yang penting untuk diperhatikan, kolom-kolom yang disertakan setelah GROUP BY harus sama dengan kolom-kolom yang dipilih pada setelah klausa SELECT yg selain fungsi AGGREGATE

Contoh
untuk menampilkan jumlah anggota perjenis kelamin:

SELECT JK, COUNT(*) FROM anggota
GROUP BY JK

untuk menampilkan rata-rata harga koleksi per kode penerbit

SELECT idPenerbit, AVG(harga)
FROM koleksi
GROUP BY idPenerbit

HAVING

Jika untuk filter query biasa kita menggunakan klause where, maka klausa having digunakan untuk filter fungsi-fungsi aggregate.

Contoh
untuk menampilkan jumlah buku per kode penerbit, tetapi hanya yang jumlahnya >10 saja yang ditampilkan.

SELECT idPenerbit, COUNT(*) FROM koleksi
GROUP BY idPenerbit HAVING COUNT(*)>10

ORDER BY
Kata kunci ORDER BY digunakan untuk mengurutkan hasil yang telah ditetapkan oleh-kolom tertentu.
Kata kunci ORDER BY mengurutkan catatan secara ascending secara default.
Jika ingin menyortir catatan dalam urutan descending, bisa menggunakan desc keyword

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

Contoh

Mengurutkan berdasartkan nama belakang secara ascending

SELECT * FROM Persons
ORDER BY LastName

IN / NOT IN

Ekspresi ini digunakan untuk membandingkan dengan sebuah kumpulan nilai
Kumpulan nilai bisa berupa:
– nilai-nilai yang diisikan
– query tunggal, query yg hasilnya hanya terdapat sebuah kolom saja

Nilai-nilai yang diisikan, maksudnya kita sendiri yang mengisikan nilai-nilai tersebut di dalam query kita.
Contoh
menampilkan koleksi yang jenisnya buku dan majalah (idTipeKoleksi=1 atau 2) :

SELECT * FROM koleksi
WHERE idTipeKoleksi IN (1, 2)

menampilkan anggota yang selain mahasiswa atau dosen (idJenisAnggota selain 2 dan 3)

SELECT * FROM anggota WHERE
IDJenisAnggota NOT IN (1, 3)

Query tunggal, yakni nilai-nilai yang digunakan sebagai pembanding diambil dari sebuah query tunggal yang hasilnya hanya satu kolom saja

Contoh

menampilkan anggota yang belum pernah meminjam buku sama sekali:

SELECT * FROM anggota
WHERE id NOT IN (SELECT DISTINCT idAnggota FROM Peminjaman)

menampilkan koleksi yang sedang dipinjam:

SELECT * FROM koleksi WHERE idKoleksi IN (SELECT idKoleksi
FROM Peminjaman WHERE TglKembali is NULL)

ALL
Sebagai salah satu jenis parameter WHERE

SELECT employee_id, last_name
FROM employees
WHERE salary > ALL
(SELECT high_salary
FROM salary_grades);

Menampilkan daftar employee yang memiliki salary lebih besar dari semua salary tabel gaji lain

ANY
Sebagai salah satu jenis parameter WHERE

SELECT employee_id, last_name
FROM employees
WHERE salary < ANY
(SELECT low_salary
FROM salary_grades);
&#91;/sourcecode&#93;

EMPLOYEE_ID LAST_NAME
----------- ----------
2        Johnson
3        Hobbs
4        Jones

Menampilkan daftar employee yang ada memiliki salary lebih kecil dari tabel gaji lain

<em><strong>LIKE / NOT LIKE</strong></em>

Operasi LIKE (pendekatan pola)
-    Operasi ini, hanya untuk pembandingan nilai bertipe string
-    Digunakan untuk mengenali string-string yang memiliki pola tertentu.
-    Di dalam operasi ini, digunakan simbol-simbol berikut:
-    simbol % artinya mewakili 0 s/d tak terhingga dari sembarang karakter
-    simbol _ artinya mewakili 1 sembarang karakter

Contoh
menampilkan anggota yang nama depannya "Budi":


SELECT * FROM anggota WHERE Nama LIKE "Budi%"

menampilkan Anggota yang huruf ketiga namanya adalah “d”:

SELECT * FROM anggota WHERE Nama LIKE "__d%"

menampilkan koleksi yang judulnya terdapat kata “pemrograman”:

SELECT * FROM koleksi WHERE Judul LIKE "%pemrograman%"

menampilkan koleksi yang judulnya tidak terdapat kata “pemrograman”:

SELECT * FROM koleksi WHERE Judul NOT LIKE "%pemrograman%"

EXIST / NOT EXISTS

SELECT * FROM tabel1 a
WHERE a.kolom1 NOT EXISTS
( SELECT b.kolom1 FROM tabel2 b)

klausa EXISTS dan NOT EXISTS hanya memeriksa keberadaan ada atau tidaknya row pada suatu list

LPAD dan RPAD
lpad adalah fungsi pads sisi kiri dari string tertentu dengan set karakter.

Sintaks untuk lpad fungsi adalah:

lpad (string1, padded_length, [pad_string])

string1 adalah string untuk karakter untuk pad (bagian kiri).

padded_length adalah jumlah karakter untuk dikembalikan. Jika padded_length lebih kecil dari awal string, fungsi lpad akan memotong string untuk ukuran padded_length.

parameter pad_string adalah opsional. pad_string adalah string yang akan padded ke bagian kiri dari string1. Jika parameter ini yang diabaikan, maka akan pad ke kiri-samping string1.

lpad(‘tech’, 7); akan mengembalikan ‘ tech’
lpad(‘tech’, 2); akan mengembalikan ‘te’
lpad(‘tech’, 8, ‘0’); akan mengembalikan ‘0000tech’
lpad(‘tech on the net’, 15, ‘z’); akan mengembalikan ‘tech on the net’
lpad(‘tech on the net’, 16, ‘z’); akan mengembalikan ‘ztech on the net’

rpad adalah fungsi pads sisi kanan dari string tertentu dengan beberapa set karakter.

Sintaks untuk rpad fungsi adalah:

rpad (string1, padded_length, [pad_string])

string1 adalah string untuk karakter untuk pad (di sisi kanan).

padded_length adalah jumlah karakter untuk dikembalikan. Jika padded_length lebih kecil dari awal string, fungsi rpad akan memotong string untuk ukuran padded_length.

pad_string adalah parameter opsional. pad_string adalah string yang akan padded ke sisi kanan dari string1. Jika parameter ini yang diabaikan, maka fungsi akan rpad pad memberikan karakter spasi ke samping kanan string1.

rpad(‘tech’, 7); akan mengembalikan ‘tech ‘
rpad(‘tech’, 2); akan mengembalikan ‘te’
rpad(‘tech’, 8, ‘0’); akan mengembalikan ‘tech0000’
rpad(‘tech on the net’, 15, ‘z’); akan mengembalikan ‘tech on the net’
rpad(‘tech on the net’, 16, ‘z’); akan mengembalikan ‘tech on the netz’

Comments
  1. Rahul says:

    Ahaha. Excellent tip for re-write SQL in different RDBMS. Thanks a lot for sharing such great article. There is one site also available for getting tip how to change SQL in different kind of RDBMS like Oracle,SQL server,DB2,MySQL etc. We need to write our query in any of these RDBMS and site itself formating and shows tips how to re-write same SQL in different RDBMS. I forget about this link. Can you know?

  2. nirwan says:

    great!!!! keep work!!!

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