Oracle SQL ile Güvenli Sayı Dönüşümü

Bir metin sütununda sayılar şu biçimlerde tutuluyorsa:

12 adet:12 adet: 12 12,5 1.250 bilinmiyor

toplam hesaplamak yalnızca bir veri tipi dönüşümü değildir. Önce metnin hangi dil bilgisine göre yorumlanacağı, geçersiz değerin ne anlama geldiği ve dönüştürülemeyen kayıtların toplamı nasıl etkileyeceği belirlenmelidir.

Oracle'da bu tür bir alan için kullanılan şu ifade ilk bakışta güvenli ve yeterli görünür:

ROUND( SUM( TO_NUMBER( CASE WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:' THEN SUBSTR(ACIKLAMA, 6) ELSE ACIKLAMA END DEFAULT 0 ON CONVERSION ERROR ) ) ) AS N

İfade, "adet:" ön ekini kaldırır, kalan metni sayıya dönüştürür, dönüşüm başarısızsa sıfır kullanır ve bütün değerleri toplar. Ancak davranışın güvenilirliği, "NULL", boş string, ondalık ayırıcı, binlik ayırıcı, gereksiz boşluk, büyük-küçük harf ve veri kalitesi politikası gibi ayrıntılara bağlıdır.

İfadenin Gerçek Anlamı

İçteki "CASE" bölümü şu dönüşümü gerçekleştirir:

ACIKLAMA = "adet:12" → "12" ACIKLAMA = "12" → "12" ACIKLAMA = "Adet:12" → "Adet:12" ACIKLAMA = " adet:12" → " adet:12"

"SUBSTR(ACIKLAMA, 1, 5)" ilk beş karakteri alır. Karşılaştırma sağlanırsa "SUBSTR(ACIKLAMA, 6)" altıncı karakterden metnin sonuna kadar olan bölümü döndürür. "SUBSTR", uzunluğu belirtilmediğinde başlangıç konumundan metnin sonuna kadar ilerler.

Bu nedenle ön ek temizleme kuralı dardır:

yalnızca ilk beş karakter tam olarak "adet:" ise ön eki kaldır

Şu değerler farklı davranır:

adet:12 → 12 adet: 12 → " 12" Adet:12 → "Adet:12" ADET:12 → "ADET:12" adet :12 → "adet :12" adet:12 → " adet:12"

Bu davranış yanlış olmak zorunda değildir. Kaynak sistem kesin olarak yalnız "adet:" biçimini üretiyorsa sade ve hızlıdır. Ancak alan insan girişi, eski uygulama çıktısı veya farklı üreticilerden gelen kirli veri içeriyorsa gerçek veri sözleşmesini karşılamayabilir.

Sonraki aşamadaki:

TO_NUMBER(expr DEFAULT 0 ON CONVERSION ERROR)

ifadesi, "expr" sayıya çevrilemezse "0" döndürür. Oracle bu söz diziminde varsayılan değeri yalnızca dönüşüm hatası oluştuğunda kullanır, "expr" hesaplanırken oluşan başka bir hata bu mekanizma tarafından yakalanmaz. Ayrıca varsayılan değer de hedef veri tipine dönüştürülebilir olmalıdır.

Bu ayrım önemlidir:

ifadenin değerlendirilmesi başarılı, sayıya dönüşüm başarısız → DEFAULT uygulanır

ifadenin kendisi değerlendirilirken hata → DEFAULT uygulanmaz

Mevcut "CASE" ve "SUBSTR" kullanımı normal karakter verisinde düşük risklidir. Fakat daha karmaşık kullanıcı fonksiyonları, JSON işlemleri veya hataya açık matematiksel ifadeler "TO_NUMBER" içine yerleştirilirse "DEFAULT ON CONVERSION ERROR" genel bir hata yakalama mekanizması gibi değerlendirilmemelidir.

NULL, Boş Değer ve Geçersiz Metin

"DEFAULT 0 ON CONVERSION ERROR", "NULL" değerini zorunlu olarak sıfıra dönüştürmez. "TO_NUMBER(NULL)" bir dönüşüm hatası üretmez, sonuç "NULL" olur.

Oracle, sıfır uzunluklu karakter değerlerini güncel davranışında "NULL" olarak ele alır. Ayrıca "NULL" ile sayısal sıfırın eşdeğer olmadığı özellikle belirtilir.

Buna göre:

ACIKLAMA = NULL → TO_NUMBER(NULL) → NULL ACIKLAMA = "" → Oracle açısından NULL → NULL ACIKLAMA = "abc" → dönüşüm hatası → 0 ACIKLAMA = "0" → 0

ortaya çıkar.

"SUM" gibi Oracle aggregate fonksiyonları "NULL" değerleri yok sayar. Grup içinde hiç satır bulunmazsa veya bütün argümanlar "NULL" ise "SUM" sonucu da "NULL" olur.

Bu nedenle mevcut ifade şu semantiği taşır:

geçersiz metin → toplamda sıfır katkı NULL değer → toplam dışında bırakılır gerçek sıfır → toplamda sıfır katkı

Sayısal toplam açısından üç durum aynı sonuca yakın görünür. Veri kalitesi açısından ise aynı değildir:

0 → ölçülen gerçek değer NULL → bilinmeyen veya bulunmayan değer "abc" → biçimsel olarak geçersiz kayıt

Bütün geçersiz değerleri sıfıra çevirmek raporu kesintisiz üretir, ancak veri bozulmasını görünmez hale getirebilir. Örneğin binlerce kaydın "adet:12" yerine yanlışlıkla "adett:12" biçiminde yazılması durumunda sorgu hata vermek yerine bu kayıtların tamamını sıfır kabul eder.

Bu nedenle güvenli dönüşüm ile veri kalitesi denetimi ayrı ihtiyaçlardır.

NLS Ayarları Sayının Anlamını Değiştirir

"TO_NUMBER" için açık bir format modeli verilmezse Oracle karakter dizisini oturumun sayısal biçim kurallarına göre yorumlar. Ondalık ve grup ayırıcıları "NLS_NUMERIC_CHARACTERS" değerinden etkilenir. Bu ayar iki karakter içerir: ilk karakter ondalık ayırıcıyı, ikincisi grup ayırıcıyı tanımlar. Oturum değeri istemci veya JDBC ayarları tarafından başlangıç değerinin üzerine yazılabilir.

Örneğin:

NLS_NUMERIC_CHARACTERS = '.,'

ise:

1.25 → bir tam yüzde yirmi beş 1,250 → bin iki yüz elli

biçiminde yorumlanabilir.

Şu ayarda ise:

NLS_NUMERIC_CHARACTERS = ',.'

anlam tersine döner:

1,25 → bir tam yüzde yirmi beş 1.250 → bin iki yüz elli

Bu nedenle aynı SQL, farklı JDBC istemcisi veya oturum ayarında farklı sayı üretebilir. En tehlikeli durum her zaman dönüşüm hatası değildir. Bazı metinler her iki ayarda da geçerli olabilir fakat farklı değere dönüşebilir:

"1.234"

şu iki anlama gelebilir:

ondalık ayırıcı nokta ise → 1,234 grup ayırıcı nokta ise → 1234

Bu durumda "DEFAULT 0 ON CONVERSION ERROR" hiçbir koruma sağlamaz, çünkü dönüşüm başarısız olmamıştır. Yalnızca yanlış semantik başarıyla uygulanmıştır.

Format kesin biliniyorsa dönüşüm açık biçimde tanımlanmalıdır:

TO_NUMBER( value DEFAULT 0 ON CONVERSION ERROR, '999999999D999999', 'NLS_NUMERIC_CHARACTERS = '',.''' )

Oracle sayı format modellerinde "D", tanımlanan ondalık karakteri, "G", grup ayırıcısını temsil eder. "TO_NUMBER" üçüncü parametre aracılığıyla dönüşüme özel NLS davranışı alabilir.

Yalnızca tamsayı bekleniyorsa veri sözleşmesi daha dar kurulmalıdır. Serbest "TO_NUMBER" çağrısı, bilimsel gösterim, işaret, ondalık değer veya oturuma bağlı başka biçimleri kabul edebilir. Alanın anlamı "adet" ise şu soru açıkça cevaplanmalıdır:

12,7 adet geçerli midir?

1E3 değeri 1000 olarak kabul edilmeli midir?

Dönüşümün teknik olarak başarılı olması, iş kuralının sağlandığı anlamına gelmez.

DEFAULT ve VALIDATE_CONVERSION

Amaç yalnızca rapor üretmekse geçersiz değeri sıfıra çevirmek yeterli olabilir:

TO_NUMBER(value DEFAULT 0 ON CONVERSION ERROR)

Amaç veri kalitesini ölçmekse geçerliliğin ayrı bir sütunda korunması gerekir. Oracle'ın "VALIDATE_CONVERSION" fonksiyonu, bir ifadenin belirtilen veri tipine dönüştürülüp dönüştürülemeyeceğini denetlemek için kullanılabilir.

Örneğin:

CASE WHEN VALIDATE_CONVERSION(value AS NUMBER) = 1 THEN TO_NUMBER(value) ELSE NULL END

Bu yapı geçersiz metni sıfıra dönüştürmek yerine "NULL" olarak ayırır. Aynı veri kümesinde hem toplam hem hata sayısı üretilebilir:

SELECT SUM( TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR) ) AS TOPLAM, SUM( CASE WHEN value IS NOT NULL AND VALIDATE_CONVERSION(value AS NUMBER) = 0 THEN 1 ELSE 0 END ) AS GECERSIZ_KAYIT FROM...

Böylece rapor şu iki bilgiyi birbirinden ayırır:

geçerli sayıların toplamı dönüştürülemeyen kayıtların sayısı

Kritik raporlama sistemlerinde sessiz sıfırlaştırma yerine bu ayrım daha güvenlidir. Çünkü aşağıdaki iki durum aynı toplamı üretebilir:

100 geçerli sıfır 100 bozuk metin

Toplamın sıfır olması, verinin doğru olduğu anlamına gelmez.

Ancak "VALIDATE_CONVERSION" ve ardından "TO_NUMBER" kullanımı aynı dönüşüm işinin iki kez değerlendirilmesine yol açabilir. Büyük veri kümelerinde yalnızca toplam gerekiyorsa:

TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR)

daha sade olabilir. Veri kalitesi metriği de gerekiyorsa dönüştürülmüş değerin bir alt sorguda bir kez hesaplanması değerlendirilebilir:

SELECT SUM(NUMERIC_VALUE) AS TOPLAM, SUM( CASE WHEN RAW_VALUE IS NOT NULL AND NUMERIC_VALUE IS NULL THEN 1 ELSE 0 END ) AS GECERSIZ_KAYIT FROM ( SELECT RAW_VALUE, TO_NUMBER( RAW_VALUE DEFAULT NULL ON CONVERSION ERROR ) AS NUMERIC_VALUE FROM... )

Ancak gerçek yürütme planında Oracle'ın ifadeyi kaç kez değerlendirdiği yalnızca sorgunun görsel yapısından kesin olarak çıkarılmamalıdır. Performans kararı yürütme planı ve gerçek ölçüm üzerinden verilmelidir.

Açık Bir Ayrıştırma Sözleşmesi

Alan yalnızca şu iki biçimi taşıyorsa:

12 adet:12

mevcut "CASE" yaklaşımı yeterince sade ve deterministiktir. Boşluk toleransı isteniyorsa açıkça eklenebilir:

TRIM( CASE WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:' THEN SUBSTR(ACIKLAMA, 6) ELSE ACIKLAMA END )

Büyük-küçük harf farkı da kabul edilecekse:

CASE WHEN LOWER(SUBSTR(ACIKLAMA, 1, 5)) = 'adet:' THEN SUBSTR(ACIKLAMA, 6) ELSE ACIKLAMA END

kullanılabilir. Fakat sütun üzerinde "LOWER", "TRIM", regex veya başka fonksiyonlar kullanıldıkça sorgu maliyeti artar. Özellikle yüz binlerce veya milyonlarca satırda düzenli ifade ile serbest metin temizlemek, kaynak veriyi doğru modellemekten daha pahalıdır.

Daha güçlü sürüm şu biçimde kurulabilir:

ROUND( SUM( TO_NUMBER( TRIM( CASE WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:' THEN SUBSTR(ACIKLAMA, 6) ELSE ACIKLAMA END ) DEFAULT 0 ON CONVERSION ERROR ) ) ) AS N

Bu ifade şu davranışı sağlar:

"adet:12" → 12 "adet: 12" → 12 " 12 " → 12 "abc" → 0 NULL → NULL

Tamsayı zorunluluğu bulunuyorsa dönüşüm sonrasında "ROUND" kullanmak semantik açıdan ayrıca değerlendirilmelidir. Dıştaki:

ROUND(SUM(...))

önce ondalıklı değerleri toplar, sonra toplamı yuvarlar.

Bu işlem:

SUM(ROUND(...))

ile eşdeğer değildir.

Örneğin:

0,6 + 0,6

için:

ROUND(0,6 + 0,6) = 1 ROUND(0,6) + ROUND(0,6) = 2

olur.

Alan gerçekten adet içeriyorsa her satırın tamsayı olması beklenebilir. Bu durumda ondalıklı girdiyi toplam sonunda yuvarlamak, bozuk veriyi örtük biçimde kabul eder. İş kuralı şu üç seçenekten biri olmalıdır:

ondalıklı adet reddedilir ondalıklı adet satır bazında yuvarlanır ondalıklı değerler toplanır ve yalnız çıktı biçiminde yuvarlanır

Bunlar farklı sonuçlar üretir, SQL ifadesi iş kuralının yerini alamaz.

Metin Sütunundaki Sayı Teknik Borçtur

Sayısal bir değerin "VARCHAR2" alanında tutulması, her sorguda şu maliyetleri tekrar üretir:

ön ek ayrıştırma boşluk temizleme NLS yorumlama dönüşüm hatası yönetimi veri kalitesi belirsizliği fonksiyon maliyeti indeksleme güçlüğü

İdeal modelde sayı ve açıklama ayrı alanlarda tutulur:

ADET NUMBER ACIKLAMA VARCHAR2

Kaynak şema değiştirilemiyorsa sanal sütun, materialized view veya ETL sırasında üretilmiş temiz bir sayısal alan değerlendirilebilir:

ADET_SAYI GENERATED ALWAYS AS ( TO_NUMBER( TRIM( CASE WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:' THEN SUBSTR(ACIKLAMA, 6) ELSE ACIKLAMA END ) DEFAULT NULL ON CONVERSION ERROR ) ) VIRTUAL

Bu yaklaşımın uygulanabilirliği Oracle sürümüne, ifade kısıtlarına ve şema değiştirme yetkisine bağlıdır. Şema değiştirilemiyorsa aynı mantık sorgu katmanında korunabilir, ancak dönüşüm tek bir merkezi SQL üretim noktasında tanımlanmalıdır. Aynı alanın farklı raporlarda farklı biçimde yorumlanması, toplamların birbirinden ayrışmasına neden olur.

Buradaki temel mühendislik problemi "TO_NUMBER" çağrısının hata vermeden çalışması değildir. Asıl mesele, metin ile sayı arasındaki geçişin açık bir veri sözleşmesine sahip olmasıdır:

hangi metinler geçerlidir? ondalık ve grup ayırıcıları nedir? NULL ne anlama gelir? bozuk kayıt sıfır mı, eksik mi, hata mı sayılır? negatif değer kabul edilir mi? ondalık değer kabul edilir mi?

"DEFAULT 0 ON CONVERSION ERROR", operasyonel süreklilik sağlar, fakat veri doğruluğunu tek başına garanti etmez. Sessizce üretilen bir toplam, açıkça başarısız olan sorgudan daha tehlikeli olabilir. Dönüşüm hatası görünmez hale getirilecekse onun yerine veri kalitesi görünür hale getirilmelidir.

Güvenilir SQL, yalnızca geçerli veriyi doğru hesaplayan SQL değildir. Geçersiz verinin hangi anlamla sisteme katıldığını da kesin biçimde tanımlayan SQL'dir.