Dönüştürme İşlevlerini Kullanma

SQL yardımcı program işlevlerinin yanı sıra, Oracle dahili işlev kitaplığı tür dönüştürme işlevlerini içerir. Sorgunun belirli bir veri türünde girdi beklediği, ancak farklı bir veri türünde aldığı senaryolar olabilir. Bu gibi durumlarda Oracle, dolaylı olarak beklenmedik değeri, yerinde değiştirilebilecek uyumlu bir veri türüne dönüştürmeye çalışır ve uygulama sürekliliği tehlikeye atılmaz. Tür dönüştürme, Oracle tarafından dolaylı olarak veya programcı tarafından açıkça yapılabilir.

Örtülü veri türü dönüşümü, Oracle'ın dahili tür dönüştürme desteğini gösteren bir matrise dayalı olarak çalışır. Oracle, bu kuralların yanı sıra, sorgularda açık dönüştürme ve biçimlendirme için kullanılabilen tür dönüştürme işlevleri sunar. Nitekim, yazılım zekasına güvenmek yerine açık dönüştürme yapılması önerilir. Örtük dönüştürme iyi çalışsa da, kötü girdilerin dahili olarak yazılmasının zor olabileceği çarpık olasılıkları ortadan kaldırmak için.

Örtük Veri Türü Dönüşümü

Bir VARCHAR2 veya CHAR değeri, Oracle tarafından dolaylı olarak NUMBER veya DATE tipi değerine dönüştürülebilir. Benzer şekilde, bir NUMARA veya VERİ türü değeri, Oracle sunucusu tarafından otomatik olarak karakter verilerine dönüştürülebilir. Impicit interconversion'ın yalnızca karakter sırasıyla geçerli bir sayı veya tarih türü değerini temsil ettiğinde gerçekleştiğine dikkat edin.

Örneğin, aşağıdaki SEÇME sorgularını inceleyin. Her iki sorgu da aynı sonucu verecektir çünkü Oracle dahili olarak 15000 ve "15000" i aynı şekilde değerlendirir.

Sorgu-1

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

Sorgu-2

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

Açık Veri Türü Dönüşümü

SQL Dönüştürme işlevleri, sütun değerini, değişmez değeri veya bir ifadeyi yazabilen tek satırlı işlevlerdir. TO_CHAR, TO_NUMBER ve TO_DATE, veri türlerinin çapraz modifikasyonunu gerçekleştiren üç işlevdir.

TO_CHAR işlevi

TO_CHAR işlevi, bir biçim modeliyle (isteğe bağlı) karakter türüne sayısal veya tarih girdisi yazmak için kullanılır.

Sözdizimi

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

Sayıdan karaktere dönüştürme için, nls parametreleri, ondalık karakterleri, grup ayırıcıyı, yerel para birimi modelini veya uluslararası para birimi modelini belirtmek için kullanılabilir. Bu isteğe bağlı bir özelliktir - mevcut değilse, oturum seviyesi nls ayarları kullanılacaktır. Tarih-karakter dönüşümü için nls parametresi, uygun olduğu şekilde gün ve ay adlarını belirtmek için kullanılabilir.

TO_CHAR işlevi kullanılarak karakter türlerine dönüştürüldükten sonra tarihler birden çok biçimde biçimlendirilebilir. TO_CHAR işlevi, Oracle 11g'nin tarihleri ​​belirli bir formatta görüntülemesini sağlamak için kullanılır. Biçim modelleri büyük / küçük harfe duyarlıdır ve tek tırnak içine alınmalıdır.

Aşağıdaki SELECT sorgusunu göz önünde bulundurun. Sorgu, TO_CHAR işlevini kullanarak EMPLOYEES tablosunun HIRE_DATE ve SALARY sütunlarını biçimlendirir.

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

İlk TO_CHAR, işe alma tarihini AY GG, YYYY tarih biçimine dönüştürmek için kullanılır, yani ayın yazılması ve boşluklarla doldurulması, ardından ayın iki basamaklı günü ve ardından dört basamaklı yıl. Ay adını karışık harflerle (yani "Aralık") görüntülemeyi tercih ediyorsanız, şu durumu biçim bağımsız değişkeninde kullanın: ('GG Ayı, YYYY').

Şekil 10-39'daki ikinci TO_CHAR işlevi, para birimi işaretini ve iki ondalık konumu görüntülemek için MAAŞ'ı biçimlendirmek için kullanılır.

Oracle, kapsamlı format modelleri seti sunar. Aşağıdaki tablo, TO_CHAR kullanarak karakter olarak döküm tarih ve sayı değerlerini yazmak için kullanılabilecek format modellerinin listesini gösterir.

Biçim Modeli	Açıklama
,(virgül)	Belirtilen konumda virgül döndürür. Bir sayı biçimi modelinde birden çok virgül belirtebilirsiniz. Kısıtlamalar: Bir virgül öğesi, bir sayı biçimi modeline başlayamaz. Sayı biçimi modelinde bir ondalık karakterin veya noktanın sağında virgül görünemez.
. (nokta)	Belirtilen konumda nokta (.) Olan bir ondalık nokta döndürür. Kısıtlama: Sayı biçimi modelinde yalnızca bir nokta belirtebilirsiniz
$	Önde gelen dolar işaretiyle değer verir
0	Baştaki sıfırları döndürür. Sondaki sıfırları döndürür.
9	Pozitifse başında bir boşlukla veya negatifse başında eksi ile belirtilen basamak sayısıyla değer döndürür. Baştaki sıfırlar, sabit noktalı sayının tamsayı kısmı için sıfır döndüren sıfır değeri dışında boştur.
B	Tam sayı bölümü sıfır olduğunda (biçim modelinde "0" lardan bağımsız olarak), sabit noktalı sayının tam sayı bölümü için boşluklar döndürür.
C	Belirtilen konumda ISO para birimi sembolünü (NLS_ISO_CURRENCY parametresinin geçerli değeri) döndürür.
D	Belirtilen konumda, NLS_NUMERIC_CHARACTER parametresinin geçerli değeri olan ondalık karakteri döndürür. Varsayılan, nokta (.). Sınırlama: Bir sayı biçimi modelinde yalnızca bir ondalık karakter belirtebilirsiniz.
EEE	Bilimsel gösterimde kullanarak bir değer döndürür.
FM	Başında veya sonunda boşluk olmayan bir değer döndürür.
G	Belirtilen konumda grup ayırıcısını (NLS_NUMERIC_CHARACTER parametresinin geçerli değeri) döndürür. Bir sayı biçimi modelinde birden çok grup ayırıcısı belirtebilirsiniz. Sınırlama: Grup ayırıcı, sayı biçimi modelinde ondalık karakterin veya noktanın sağ tarafında görünemez
L	Belirtilen konumda yerel para birimi sembolünü (NLS_CURRENCY parametresinin geçerli değeri) döndürür.
Mİ	Sonunda eksi işareti (-) olan negatif bir değer döndürür. Sonunda bir boşluk bırakarak pozitif bir değer döndürür. Kısıtlama: MI biçim öğesi, bir sayı biçimi modelinin yalnızca son konumunda görünebilir.
PR	Negatif değeri içinde döndürür. Yalnızca sayı biçimi modelinin sonunda görünebilir.
RN, rm	Büyük harfle Roma rakamları olarak bir değer döndürür. Küçük harfli Roma rakamları olarak bir değer döndürür. Değer, 1 ile 3999 arasında bir tam sayı olabilir.
S	Başında veya sonunda eksi işareti (-) olan negatif bir değer döndürür. Başında veya sonunda artı işaretiyle (+) pozitif bir değer döndürür. Sınırlama: S biçimli öğe, bir sayı biçimi modelinin yalnızca ilk veya son konumunda görünebilir.
TM	"Minimum metin". Olası en az sayıda karakteri döndürür (ondalık çıktı olarak). Bu öğe büyük / küçük harfe duyarlı değildir.
U	Belirtilen pozisyonda "Euro" (veya başka) ikili para birimi sembolünü (NLS_DUAL_CURRENCY parametresinin geçerli değeri) döndürür.
V	10n ile çarpılan bir değer döndürür (ve gerekirse yukarı yuvarlar); burada n, "V" den sonraki 9 sayısıdır.
X	Belirtilen basamak sayısının onaltılık değerini döndürür.

TO_NUMBER işlevi

TO_NUMBER işlevi, bir karakter değerini sayısal bir veri türüne dönüştürür. Dönüştürülen dizge sayısal olmayan karakterler içeriyorsa, işlev bir hata döndürür.

Sözdizimi

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

Aşağıdaki tablo, karakter değerlerini TO_NUMBER kullanarak sayı olarak yazmak için kullanılabilecek biçim modellerinin listesini gösterir.

Biçim Modeli	Açıklama
CC	Yüzyıl
SCC	Yüzyıl MÖ -
YYYY	4 rakamlı yıl
SYYY	BC yılı başında -
IYYY	4 rakamlı ISO Yılı
YY	2 rakamlı yıl
RR	Y2k uyumlu 2 rakamlı yıl
YIL	Karakterlerde yıl
SYEAR	Karakter cinsinden yıl, BC önünde -
M.Ö	BC / AD Göstergesi
Q	Sayılarla çeyrek (1,2,3,4)
MM	01, 02 ... 12 yılının ayı
AY	Karakter cinsinden ay (yani Ocak)
PZT	OCAK, ŞUBAT
WW	Hafta numarası (yani 1)
W	Ayın hafta numarası (yani 5)
IW	ISO standardında yılın hafta numarası.
DDD	Sayılarla yılın günü (yani 365)
DD	Rakamlarla ayın günü (yani 28)
D	Sayılarla haftanın günü (yani 7)
GÜN	Karakter olarak haftanın günü (yani Pazartesi)
FMDAY	Karakter olarak haftanın günü (yani Pazartesi)
DY	Kısa karakter açıklamasında haftanın günü (yani SUN)
J	Jülyen Günü (MÖ 1 Ocak 4713'ten bu yana geçen gün sayısı, MÖ 1 Ocak 4713, Oracle'da 1'dir)
HH, H12	Günün saat numarası (1-12)
HH24	24Hours gösterimiyle günün saat numarası (0-23)
AM, PM	AM veya PM
ÖZLEMEK	Dakika ve saniye sayısı (yani 59),
SSSSS	Bu günkü saniye sayısı.
DS	Kısa tarih biçimi. NLS ayarlarına bağlıdır. Yalnızca zaman damgasıyla kullanın.
DL	Uzun tarih biçimi. NLS ayarlarına bağlıdır. Yalnızca zaman damgasıyla kullanın.
E	Kısaltılmış çağ adı. Yalnızca takvimler için geçerlidir: Japanese Imperial, ROC Official, Thai Buddha.
EE	Tam çağ adı
FF	Kesirli saniye. Zaman damgası ile kullanın.
FF1..FF9	Kesirli saniye. Zaman damgası ile kullanın. Rakam, kesirli saniyeler için kullanılan ondalık basamakların sayısını kontrol eder.
FM	Doldurma Modu: dönüşümden elde edilen çıktıdaki boşlukları bastırır
FX	Biçim Tam: veri ve biçim modeli arasında tam kalıp eşleşmesi gerektirir.
IYY OR IY OR I	ISO standart yılının son 3,2,1 hanesi. Yalnızca çıktı
RM	Ayın Roma rakamıyla gösterimi (I .. XII)
RR	Yılın son 2 rakamı.
RRRR	Çıktı için kullanıldığında yılın son 2 rakamı. Giriş için kullanıldığında sıfır basamaklı yılları kabul eder.
SP	Yazım biçimi. Bir sayı öğesinin sonunda görünebilir. Sonuç her zaman ingilizcedir. Örneğin, MMSP biçimindeki 10. ay "on" döndürür
SPTH	Yazım ve sıralı biçim; İlk olarak 1 sonuç.
TH	Bir sayıyı sıra biçimine dönüştürür. Örneğin 1, 1 olur.
TS	Kısa zaman biçimi. NLS ayarlarına bağlıdır. Yalnızca zaman damgasıyla kullanın.
TZD	Kısaltılmış saat dilimi adı. ör. PST.
TZH, TZM	Saat dilimi saat / dakika değişimi.
TZR	Saat dilimi bölgesi
X	Yerel taban karakteri. Amerika'da bu bir dönemdir (.)

Aşağıdaki SEÇME sorguları, sayıları karakter girişi olarak kabul eder ve bunları biçim belirleyicisine göre yazdırır.

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

TO_DATE işlevi

İşlev, karakter değerlerini girdi olarak alır ve aynı şekilde biçimlendirilmiş tarih eşdeğerini döndürür. TO_DATE işlevi, kullanıcıların herhangi bir biçimde bir tarih girmesine izin verir ve ardından girişi Oracle 11g tarafından kullanılan varsayılan biçime dönüştürür.

Sözdizimi:

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

Bir format_mask argümanı, verinin tam olarak neye benzemesi gerektiğini temsil eden ve tek tırnak işareti ile girilmesi gereken bir dizi öğeden oluşur.

Biçim Modeli	Açıklama
YIL	Yıl, hecelendi
YYYY	4 basamaklı yıl
YYY, YY, Y	Yılın son 3, 2 veya 1 rakamı.
IYY, IY, ben	ISO yılının son 3, 2 veya 1 rakamı.
IYYY	ISO standardına göre 4 basamaklı yıl
RRRR	2 basamaklı bir yılı kabul eder ve 4 basamaklı bir yıl döndürür.
Q	Yılın çeyreği (1, 2, 3, 4; OCAK-MAR = 1).
MM	Ay (01-12; OCA = 01).
PZT	Ayın kısaltılmış adı.
AY	9 karakter uzunluğunda boşluklarla doldurulmuş ayın adı.
RM	Roma rakamıyla ay (I-XII; JAN = I).
WW	Yılın ilk günü 1. haftanın başladığı ve yılın yedinci gününe kadar devam ettiği yılın haftası (1-53).
W	1. haftanın ayın ilk günü başladığı ve yedinci günü bittiği ayın haftası (1-5).
IW	ISO standardına göre yılın haftası (1-52 veya 1-53).
D	Haftanın günü (1-7).
GÜN	Günün adı.
DD	Ayın günü (1-31).
DDD	Yılın günü (1-366).
DY	Günün kısaltılmış adı.
J	Jülyen günü; 1 Ocak 4712'den bu yana geçen gün sayısı.
HH12	Günün saati (1-12).
HH24	Günün saati (0-23).
ÖZLEMEK	Dakika (0-59).
SSSSS	Gece yarısını geçen saniye sayısı (0-86399).
FF	Kesirli saniye. Kesirli saniyelerdeki basamak sayısını belirtmek için FF'den sonra 1 ile 9 arasında bir değer kullanın. Örneğin, 'FF4'.
AM, PM	Meridyen göstergesi
AD, BC	AD, BC göstergesi
TZD	Gün ışığından yararlanma bilgileri. Örneğin, "PST"
TZH, TZM, TZR	Saat dilimi saat / dakika / bölge.

Aşağıdaki örnek, bir karakter dizesini tarihe dönüştürür:

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

Genel Fonksiyonlar

Veritabanındaki NULL değerleri işlemek için genel işlevler kullanılır. Genel NULL işleme işlevlerinin amacı, NULL değerlerini alternatif bir değerle değiştirmektir. Aşağıda bu işlevleri kısaca göreceğiz.

NVL

NVL işlevi, NULL değeri için alternatif bir değerin yerini alır.

Sözdizimi:

NVL( Arg1, replace_with )

Sözdiziminde her iki parametre de zorunludur. NVL işlevinin tüm veri türleriyle çalıştığını unutmayın. Ve ayrıca orijinal dizgenin veri türü ve yerine koyma uyumlu durumda olmalıdır, yani Oracle tarafından aynı veya dolaylı olarak dönüştürülebilir olmalıdır.

Arg1 bir karakter değeriyse, oracle onları karşılaştırmadan önce değiştirme dizesini arg1 ile uyumlu veri türüne dönüştürür ve ifade1'in karakter kümesinde VARCHAR2'yi döndürür. Arg1 sayısal ise, Oracle en yüksek sayısal önceliğe sahip bağımsız değişkeni belirler, diğer bağımsız değişkeni örtük olarak bu veri türüne dönüştürür ve bu veri türünü döndürür.

Aşağıdaki SELECT deyimi, bir çalışan henüz herhangi bir işe atanmamışsa, yani JOB_ID NULL ise 'n / a' gösterecektir. Aksi takdirde, gerçek JOB_ID değerini görüntüleyecektir.

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

NVL2

NVL üzerinde bir geliştirme olarak Oracle, yalnızca NULL sütun değerleri için değil, aynı zamanda NOT NULL sütunlar için de değeri ikame edecek bir işlev getirmiştir. NVL2 işlevi, NULL yerine alternatif bir değerin yanı sıra NULL olmayan değeri değiştirmek için kullanılabilir.

Sözdizimi:

NVL2( string1, value_if_NOT_null, value_if_null )

Bir çalışanın JOB_CODE değeri NULL ise, aşağıdaki SELECT ifadesi "Bench" i görüntüler. İŞ KODU'nun boş olmayan kesin bir değeri için, 'Görev Atanmış' sabit değerini gösterecektir.

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

NULLIF

NULLIF işlevi iki bağımsız değişkeni ifade1 ve ifade2'yi karşılaştırır. İfade1 ve ifade2 eşitse, NULL döndürür; aksi takdirde ifade1 döndürür. Diğer boş işleme fonksiyonunun aksine, ilk argüman NULL olamaz.

Sözdizimi:

NULLIF (expr1, expr2)

İlk bağımsız değişkenin NULL olarak değerlendirilen bir ifade olabileceğini, ancak değişmez NULL olamayacağını unutmayın. Fonksiyonun çalışması için her iki parametre de zorunludur.

Aşağıdaki sorgu, her iki giriş değeri 12 eşit olduğu için NULL döndürür.

SELECT	NULLIF (12, 12)
FROM DUAL;

Benzer şekilde, her iki dizge de eşit olmadığından aşağıdaki sorgu 'SUN' döndürür.

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

KÖMÜR

Daha genel bir NVL biçimi olan COALESCE işlevi, bağımsız değişken listesindeki ilk boş olmayan ifadeyi döndürür. En az iki zorunlu parametre alır, ancak maksimum bağımsız değişkenlerin sınırı yoktur.

Sözdizimi:

COALESCE (expr1, expr2, ... expr_n )

Aşağıdaki SELECT sorgusunu göz önünde bulundurun. Bir çalışan için adres alanlarına beslenen ilk boş olmayan değeri seçer.

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

İlginç bir şekilde, COALESCE işlevinin çalışması IF..ELSIF..ENDIF yapısına benzer. Yukarıdaki sorgu şu şekilde yeniden yazılabilir -

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;

Koşullu İşlevler

Oracle, SQL deyiminde bile koşulları uygulamak için koşullu işlevler DECODE ve CASE sağlar.

DECODE işlevi

İşlev, IF..THEN..ELSE koşullu prosedür ifadesinin SQL eşdeğeridir. DECODE, tüm veri türlerinin değerleri / sütunları / ifadeleri ile çalışır.

Sözdizimi:

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

DECODE işlevi, ifadeyi sırayla her arama değeriyle karşılaştırır. İfade ve arama argümanı arasında eşitlik varsa, karşılık gelen sonucu döndürür. Eşleşme olmaması durumunda, tanımlanmışsa varsayılan değer döndürülür, aksi takdirde NULL. Herhangi bir tür uyumsuzluğu durumunda, oracle dahili olarak sonuçları döndürmek için olası örtük dönüştürme yapar.

Aslında Oracle, DECODE işlevi ile çalışırken iki boşun eşdeğer olduğunu düşünür.

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

DECOD
-----
EQUAL

İfade null ise, Oracle yine boş olan ilk aramanın sonucunu döndürür. DECODE işlevindeki maksimum bileşen sayısı 255'tir.

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

CASE ifadesi

CASE ifadeleri, DECODE ile aynı kavram üzerinde çalışır ancak sözdizimi ve kullanım açısından farklılık gösterir.

Sözdizimi:

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

Oracle araması soldan başlar ve gerçek bir koşul bulana kadar sağa doğru hareket eder ve ardından onunla ilişkili sonuç ifadesini döndürür. Hiçbir koşul doğru bulunmazsa ve bir ELSE yan tümcesi varsa, Oracle else ile tanımlanan sonucu döndürür. Aksi takdirde, Oracle null döndürür.

Bir CASE ifadesindeki maksimum argüman sayısı 255'tir. Basit bir CASE ifadesinin ilk ifadesi ve isteğe bağlı ELSE ifadesi dahil tüm ifadeler bu sınıra dahil edilir. Her WHEN ... THEN çifti iki bağımsız değişken olarak sayılır. Bu sınırın aşılmasını önlemek için, CASE ifadelerini, return_expr'in kendisi bir CASE ifadesi olacak şekilde iç içe yerleştirebilirsiniz.

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