Menggunakan Fungsi Konversi

Selain fungsi utilitas SQL, pustaka fungsi bawaan Oracle berisi fungsi konversi tipe. Mungkin ada skenario di mana kueri mengharapkan input dalam tipe data tertentu, tapi menerimanya dalam tipe data yang berbeda. Dalam kasus tersebut, Oracle secara implisit mencoba untuk mengubah nilai yang tidak diharapkan menjadi tipe data yang kompatibel yang dapat diganti di tempat dan kontinuitas aplikasi tidak terganggu. Konversi tipe dapat dilakukan secara implisit oleh Oracle atau secara eksplisit dilakukan oleh programmer.

Konversi tipe data implisit bekerja berdasarkan matriks yang menampilkan dukungan Oracle untuk casting tipe internal. Selain aturan ini, Oracle menawarkan fungsi konversi jenis yang dapat digunakan dalam kueri untuk konversi dan pemformatan eksplisit. Faktanya, disarankan untuk melakukan konversi eksplisit daripada mengandalkan kecerdasan perangkat lunak. Meskipun konversi implisit bekerja dengan baik, tetapi untuk menghilangkan peluang miring di mana input yang buruk bisa jadi sulit untuk diketik secara internal.

Konversi Tipe Data Implisit

Nilai VARCHAR2 atau CHAR dapat secara implisit dikonversi ke nilai tipe NOMOR atau DATE oleh Oracle. Demikian pula, nilai tipe NOMOR atau DATA dapat secara otomatis dikonversi ke data karakter oleh server Oracle. Perhatikan bahwa interkonversi impicit hanya terjadi jika karakter masing-masing mewakili nilai nomor atau jenis tanggal yang valid.

Misalnya, periksa kueri SELECT di bawah ini. Kedua kueri tersebut akan memberikan hasil yang sama karena Oracle secara internal memperlakukan 15000 dan '15000' sebagai sama.

Kueri-1

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Kueri-2

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Konversi Tipe Data Eksplisit

Fungsi Konversi SQL adalah fungsi baris tunggal yang mampu menghitung nilai kolom, literal, atau ekspresi. TO_CHAR, TO_NUMBER dan TO_DATE adalah tiga fungsi yang melakukan modifikasi silang pada tipe data.

Fungsi TO_CHAR

Fungsi TO_CHAR digunakan untuk memasukkan input numerik atau tanggal ke tipe karakter dengan model format (opsional).

Sintaksis

TO_CHAR(number1, [format], [nls_parameter])

Untuk konversi angka ke karakter, parameter nls dapat digunakan untuk menentukan karakter desimal, pemisah grup, model mata uang lokal, atau model mata uang internasional. Ini adalah spesifikasi opsional - jika tidak tersedia, setelan nls tingkat sesi akan digunakan. Untuk konversi tanggal ke karakter, parameter nls dapat digunakan untuk menentukan nama hari dan bulan, sebagaimana berlaku.

Tanggal dapat diformat dalam berbagai format setelah diubah menjadi tipe karakter menggunakan fungsi TO_CHAR. Fungsi TO_CHAR digunakan agar Oracle 11g menampilkan tanggal dalam format tertentu. Model format peka huruf besar / kecil dan harus diapit dalam tanda kutip tunggal.

Pertimbangkan kueri SELECT di bawah ini. Query memformat kolom HIRE_DATE dan SALARY dari tabel EMPLOYEES menggunakan fungsi TO_CHAR.

SELECT first_name,
       TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
	   TO_CHAR (salary, '$99999.99') Salary FROM employees WHERE rownum < 5; FIRST_NAME HIRE_DATE SALARY -------------------- ------------------ ---------- Steven JUNE 17, 2003 $24000.00
Neena                SEPTEMBER 21, 2005  $17000.00 Lex JANUARY 13, 2001 $17000.00
Alexander            JANUARY   03, 2006   $9000.00

TO_CHAR pertama digunakan untuk mengonversi tanggal perekrutan ke format tanggal. HH BULAN, YYYY yaitu bulan dieja dan diisi dengan spasi, diikuti oleh dua digit hari dari bulan tersebut, dan kemudian tahun empat digit. Jika Anda lebih suka menampilkan nama bulan dalam huruf campuran (yaitu, "Desember"), cukup gunakan kasus ini dalam argumen format: ('Bulan DD, YYYY').

Fungsi TO_CHAR kedua pada Gambar 10-39 digunakan untuk memformat SALARY untuk menampilkan tanda mata uang dan dua posisi desimal.

Oracle menawarkan serangkaian model format yang lengkap. Tabel di bawah ini menunjukkan daftar model format yang dapat digunakan untuk mengetik tanggal dan nilai angka sebagai karakter menggunakan TO_CHAR.

Model Format	Deskripsi
,(koma)	Ini mengembalikan koma di posisi yang ditentukan. Anda dapat menentukan beberapa koma dalam model format angka. Batasan: Elemen koma tidak dapat memulai model format angka. Koma tidak dapat muncul di sebelah kanan karakter desimal atau titik dalam model format angka.
.(Titik)	Mengembalikan titik desimal, yang merupakan titik (.) Di posisi yang ditentukan. Batasan: Anda hanya dapat menentukan satu titik dalam model format angka
$	Mengembalikan nilai dengan tanda dolar utama
0	Menampilkan angka nol di depan. Menampilkan nol di belakangnya.
9	Mengembalikan nilai dengan jumlah digit yang ditentukan dengan spasi di depan jika positif atau dengan minus di depan jika negatif. Nol di depan kosong, kecuali untuk nilai nol, yang mengembalikan nol untuk bagian bilangan bulat dari bilangan titik tetap.
B	Mengembalikan kosong untuk bagian bilangan bulat dari bilangan titik tetap jika bagian bilangan bulat adalah nol (terlepas dari "0" dalam model format).
C	Mengembalikan posisi yang ditentukan simbol mata uang ISO (nilai parameter NLS_ISO_CURRENCY saat ini).
D	Mengembalikan pada posisi yang ditentukan karakter desimal, yang merupakan nilai parameter NLS_NUMERIC_CHARACTER saat ini. Defaultnya adalah titik (.). Batasan: Anda hanya dapat menentukan satu karakter desimal dalam model format angka.
EEE	Mengembalikan nilai yang digunakan dalam notasi ilmiah.
FM	Mengembalikan nilai tanpa awalan atau akhiran kosong.
G	Mengembalikan pemisah grup di posisi yang ditentukan (nilai saat ini dari parameter NLS_NUMERIC_CHARACTER). Anda dapat menentukan beberapa pemisah grup dalam model format angka. Batasan: Pemisah grup tidak dapat muncul di sebelah kanan karakter desimal atau titik dalam model format angka
L	Mengembalikan posisi yang ditentukan simbol mata uang lokal (nilai saat ini dari parameter NLS_CURRENCY).
MI	Mengembalikan nilai negatif dengan tanda minus (-). Mengembalikan nilai positif dengan tanda kosong. Batasan: Elemen format MI hanya dapat muncul di posisi terakhir model format angka.
PR	Mengembalikan nilai negatif dalam. Ini hanya dapat muncul di akhir model format angka.
RN, rm	Mengembalikan nilai sebagai angka Romawi dalam huruf besar. Mengembalikan nilai sebagai angka Romawi dalam huruf kecil. Nilai dapat berupa bilangan bulat antara 1 dan 3999.
S	Mengembalikan nilai negatif dengan tanda minus di depan atau di belakangnya (-). Mengembalikan nilai positif dengan tanda plus di depan atau di belakangnya (+). Batasan: Elemen format S hanya dapat muncul di posisi pertama atau terakhir dari model format angka.
TM	"Teks minimum". Mengembalikan (dalam keluaran desimal) jumlah karakter sekecil mungkin. Elemen ini peka huruf besar / kecil.
U	Mengembalikan dalam posisi yang ditentukan simbol mata uang ganda "Euro" (atau lainnya) (nilai saat ini dari parameter NLS_DUAL_CURRENCY).
V.	Mengembalikan nilai dikalikan dengan 10n (dan jika perlu, bulatkan ke atas), di mana n adalah jumlah 9 setelah "V".
X	Mengembalikan nilai heksadesimal dari jumlah digit yang ditentukan.

Fungsi TO_NUMBER

Fungsi TO_NUMBER mengonversi nilai karakter menjadi tipe data numerik. Jika string yang dikonversi berisi karakter nonnumerik, fungsi mengembalikan kesalahan.

Sintaksis

TO_NUMBER (string1, [format], [nls_parameter])

Tabel di bawah ini menunjukkan daftar model format yang dapat digunakan untuk mencetak nilai karakter sebagai angka menggunakan TO_NUMBER.

Model Format	Deskripsi
CC	Abad
SCC	Century BC diawali dengan -
YYYY	Tahun dengan 4 angka
SYYY	Tahun SM diawali dengan -
IYYY	Tahun ISO dengan 4 angka
Y Y	Tahun dengan 2 angka
RR	Tahun dengan 2 angka dengan kompatibilitas Y2k
TAHUN	Tahun dalam karakter
SYEAR	Tahun dalam karakter, BC diawali dengan -
SM	Indikator BC / AD
Q	Kuartal dalam angka (1,2,3,4)
MM	Bulan tahun 01, 02 ... 12
BULAN	Bulan dalam karakter (yaitu Januari)
MON	JAN, FEB
WW	Nomor minggu (yaitu 1)
W	Nomor minggu dalam sebulan (yaitu 5)
IW	Nomor minggu dalam tahun dalam standar ISO.
DDD	Hari dalam tahun dalam angka (yaitu 365)
DD	Hari dalam sebulan dalam angka (yaitu 28)
D	Hari dalam seminggu dalam angka (yaitu 7)
HARI	Hari dalam seminggu dalam karakter (yaitu Senin)
FMDAY	Hari dalam seminggu dalam karakter (yaitu Senin)
DY	Hari dalam seminggu dengan deskripsi karakter singkat (mis. SUN)
J	Julian Day (jumlah hari sejak 1 Januari 4713 SM, di mana 1 Januari 4713 SM adalah 1 di Oracle)
HH, H12	Jumlah jam dalam sehari (1-12)
HH24	Jumlah jam dalam sehari dengan notasi 24 Jam (0-23)
AM, PM	AM atau PM
RINDU	Jumlah menit dan detik (yaitu 59),
SSSSS	Jumlah detik hari ini.
DS	Format tanggal pendek. Tergantung pada pengaturan NLS. Gunakan hanya dengan stempel waktu.
DL	Format tanggal panjang. Tergantung pada pengaturan NLS. Gunakan hanya dengan stempel waktu.
E	Nama era disingkat. Berlaku hanya untuk kalender: Kekaisaran Jepang, Pejabat Republik Rakyat Tiongkok, Buddha Thailand.
EE	Nama era lengkapnya
FF	Detik pecahan. Gunakan dengan stempel waktu.
FF1..FF9	Detik pecahan. Gunakan dengan stempel waktu. Digit mengontrol jumlah digit desimal yang digunakan untuk pecahan detik.
FM	Mode Isi: menyembunyikan kekosongan dalam keluaran dari konversi
FX	Format Exact: membutuhkan pencocokan pola yang tepat antara data dan format model.
IYY ATAU IY ATAU I	3,2,1 digit terakhir tahun standar ISO. Output saja
RM	Representasi angka Romawi untuk bulan (I .. XII)
RR	2 digit terakhir tahun ini.
RRRR	2 digit terakhir tahun ini saat digunakan untuk keluaran. Menerima tahun fout-digit saat digunakan untuk input.
SP	Format dieja. Dapat muncul dari akhir elemen angka. Hasilnya selalu dalam bahasa Inggris. Misalnya bulan 10 dalam format MMSP mengembalikan "sepuluh"
SPTH	Format dieja dan ordinal; 1 hasil pertama.
TH	Mengonversi angka menjadi format ordinalnya. Misalnya 1 becom 1st.
TS	Format waktu singkat. Tergantung pada pengaturan NLS. Gunakan hanya dengan stempel waktu.
TZD	Nama zona waktu yang disingkat. yaitu PST.
TZH, TZM	Perpindahan jam / menit zona waktu.
TZR	Wilayah zona waktu
X	Karakter radix lokal. Di Amerika ini adalah periode (.)

Kueri SELECT di bawah menerima angka sebagai input karakter dan mencetaknya mengikuti penentu format.

SELECT  TO_NUMBER('121.23', '9G999D99') 
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

SELECT  TO_NUMBER('1210.73', '9999.99') 
FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
                       1210.73

Fungsi TO_DATE

Fungsi ini mengambil nilai karakter sebagai masukan dan mengembalikan format tanggal yang setara dengan yang sama. Fungsi TO_DATE memungkinkan pengguna untuk memasukkan tanggal dalam format apa pun, dan kemudian mengubah entri menjadi format default yang digunakan oleh Oracle 11g.

Sintaksis:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Argumen format_mask terdiri dari serangkaian elemen yang mewakili persis seperti apa tampilan datanya dan harus dimasukkan dalam tanda kutip tunggal.

Model Format	Deskripsi
TAHUN	Tahun, terbilang
YYYY	Tahun 4 digit
YYY, YY, Y	3, 2, atau 1 digit terakhir tahun ini.
IYY, IY, I	3, 2, atau 1 digit terakhir tahun ISO.
IYYY	Tahun 4 digit berdasarkan standar ISO
RRRR	Menerima tahun 2 digit dan mengembalikan tahun 4 digit.
Q	Kuartal tahun (1, 2, 3, 4; JAN-MAR = 1).
MM	Bulan (01-12; JAN = 01).
MON	Nama bulan yang disingkat.
BULAN	Nama bulan, diisi blanko dengan panjang 9 karakter.
RM	Bulan angka romawi (I-XII; JAN = I).
WW	Minggu dalam tahun (1-53) di mana minggu 1 dimulai pada hari pertama tahun itu dan berlanjut hingga hari ketujuh dalam setahun.
W	Minggu dalam sebulan (1-5) di mana minggu 1 dimulai pada hari pertama setiap bulan dan berakhir pada hari ketujuh.
IW	Minggu dalam setahun (1-52 atau 1-53) berdasarkan standar ISO.
D	Hari dalam seminggu (1-7).
HARI	Nama hari.
DD	Hari dalam sebulan (1-31).
DDD	Hari dalam setahun (1-366).
DY	Nama hari yang disingkat.
J	Hari Julian; jumlah hari sejak 1 Januari 4712 SM.
HH12	Jam dalam sehari (1-12).
HH24	Jam dalam sehari (0-23).
RINDU	Menit (0-59).
SSSSS	Detik lewat tengah malam (0-86399).
FF	Detik pecahan. Gunakan nilai dari 1 hingga 9 setelah FF untuk menunjukkan jumlah digit dalam pecahan detik. Misalnya, 'FF4'.
AM, PM	Indikator meridian
AD, BC	AD, indikator BC
TZD	Informasi tabungan siang hari. Misalnya, 'PST'
TZH, TZM, TZR	Jam / menit / wilayah zona waktu.

Contoh berikut mengubah string karakter menjadi tanggal:

SELECT TO_DATE('January 15, 1989, 11:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

Fungsi Umum

Fungsi umum digunakan untuk menangani nilai NULL dalam database. Tujuan dari fungsi penanganan NULL secara umum adalah untuk mengganti nilai NULL dengan nilai alternatif. Kami akan secara singkat melihat melalui fungsi-fungsi di bawah ini.

NVL

Fungsi NVL menggantikan nilai alternatif untuk nilai NULL.

Sintaksis:

NVL( Arg1, replace_with )

Dalam sintaks, kedua parameter tersebut bersifat wajib. Perhatikan bahwa fungsi NVL berfungsi dengan semua jenis tipe data. Dan juga bahwa tipe data dari string asli dan penggantinya harus dalam keadaan yang kompatibel yaitu sama atau secara implisit dapat diubah oleh Oracle.

Jika arg1 adalah nilai karakter, maka oracle mengonversi string pengganti ke tipe data yang kompatibel dengan arg1 sebelum membandingkannya dan mengembalikan VARCHAR2 dalam kumpulan karakter expr1. Jika arg1 adalah numerik, maka Oracle menentukan argumen dengan prioritas numerik tertinggi, secara implisit mengonversi argumen lain ke tipe data tersebut, dan mengembalikan tipe data tersebut.

Pernyataan SELECT di bawah ini akan menampilkan 'n / a' jika seorang karyawan belum ditugaskan ke pekerjaan apa pun, yaitu JOB_ID adalah NULL. Jika tidak, ini akan menampilkan nilai JOB_ID yang sebenarnya.

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

Sebagai peningkatan dari NVL, Oracle memperkenalkan fungsi untuk menggantikan nilai tidak hanya untuk nilai kolom NULL tetapi juga untuk kolom NOT NULL. Fungsi NVL2 dapat digunakan untuk menggantikan nilai alternatif untuk nilai NULL dan juga non NULL.

Sintaksis:

NVL2( string1, value_if_NOT_null, value_if_null )

Pernyataan SELECT di bawah ini akan menampilkan 'Bench' jika JOB_CODE untuk karyawan adalah NULL. Untuk nilai JOB CODE yang pasti bukan null, itu akan menunjukkan nilai konstan 'Job Assigned'.

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

Fungsi NULLIF membandingkan dua argumen expr1 dan expr2. Jika expr1 dan expr2 sama, ia mengembalikan NULL; lain, itu mengembalikan expr1. Tidak seperti fungsi penanganan null lainnya, argumen pertama tidak boleh NULL.

Sintaksis:

NULLIF (expr1, expr2)

Perhatikan bahwa argumen pertama bisa menjadi ekspresi yang mengevaluasi ke NULL, tetapi tidak bisa menjadi NULL literal. Kedua parameter tersebut wajib untuk menjalankan fungsi.

Kueri di bawah ini mengembalikan NULL karena kedua nilai input, 12 sama.

SELECT	NULLIF (12, 12)
FROM DUAL;

Demikian pula, kueri di bawah ini mengembalikan 'SUN' karena kedua string tersebut tidak sama.

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

BERSATU

Fungsi COALESCE, bentuk NVL yang lebih umum, mengembalikan ekspresi non-null pertama dalam daftar argumen. Dibutuhkan minimal dua parameter wajib tetapi argumen maksimum tidak memiliki batas.

Sintaksis:

COALESCE (expr1, expr2, ... expr_n )

Pertimbangkan kueri SELECT di bawah ini. Ini memilih nilai nol pertama yang dimasukkan ke dalam bidang alamat untuk seorang karyawan.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Menariknya, fungsi COALESCE mirip dengan konstruk IF..ELSIF..ENDIF. Kueri di atas dapat ditulis ulang sebagai -

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Fungsi Bersyarat

Oracle menyediakan fungsi bersyarat DECODE dan CASE untuk memaksakan kondisi bahkan dalam pernyataan SQL.

Fungsi DECODE

Fungsinya adalah persamaan SQL dari pernyataan prosedural bersyarat IF..THEN..ELSE. DECODE bekerja dengan nilai / kolom / ekspresi dari semua tipe data.

Sintaksis:

DECODE (expression, search, result [, search, result]... [, default])

Fungsi DECODE membandingkan ekspresi terhadap setiap nilai pencarian secara berurutan. Jika persamaan ada antara ekspresi dan argumen pencarian, maka itu mengembalikan hasil yang sesuai. Jika tidak ada kecocokan, nilai default dikembalikan, jika ditentukan, NULL. Jika ada ketidakcocokan jenis kompatibilitas, oracle secara internal melakukan kemungkinan konversi implisit untuk mengembalikan hasil.

Faktanya, Oracle menganggap dua null setara saat bekerja dengan fungsi DECODE.

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Jika ekspresi null, maka Oracle mengembalikan hasil pencarian pertama yang juga null. Jumlah maksimal komponen dalam fungsi DECODE adalah 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

Ekspresi CASE

Ekspresi CASE bekerja pada konsep yang sama seperti DECODE tetapi berbeda dalam sintaks dan penggunaan.

Sintaksis:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Pencarian Oracle dimulai dari kiri dan bergerak ke kanan hingga menemukan kondisi sebenarnya, dan kemudian mengembalikan ekspresi hasil yang terkait dengannya. Jika tidak ada kondisi yang ditemukan benar, dan klausa ELSE ada, maka Oracle mengembalikan hasil yang ditentukan dengan else. Jika tidak, Oracle mengembalikan null.

Jumlah maksimum argumen dalam ekspresi CASE adalah 255. Semua ekspresi dihitung dalam batas ini, termasuk ekspresi awal dari ekspresi CASE sederhana dan ekspresi ELSE opsional. Setiap WHEN ... THEN pasangan dihitung sebagai dua argumen. Untuk menghindari melebihi batas ini, Anda bisa melapiskan ekspresi CASE sehingga return_expr itu sendiri adalah ekspresi CASE.

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM  	CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1