Email Validation On Constraint Level Using Regular Expressions In Oracle

Email artık hayatın vazgeçilmezlerinden biri oldu. Birçok uygulama en basitinden üye kayıt için email doğrulaması gerektiriyor. Bu da uygulama geliştiricilerin bu adresler ile olan ilişkisini daha bir arttırdı.
Genelde business layer tarafında yapılan doğrulama işlemlerini data layer tarafına da kaydırmak yapılagelen bir durumdur. NULL olmama gibi kontroller veritabanı düzeyinde sıkça yapılmasına rağmen daha karmaşık kısıtlar için veritabanını kullanmaz birçok geliştirici.
Oracle, kendi içinde barındırdığı regular expression desteği ile artık birçok karmaşık text tabanlı işlemi yapılabilmektedir. Bunun nasıl yapıldığını aşağıdaki örnekte basitçe anlatmaya çalıştım.
Email kontrolü için yazılan doğrulama, oldukça basittir. Sadece @ ve . karakterlerinden önce ve sonra bir veya birden fazla karakterin gelmesi gerektiğini belirtmektedir.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> create table email_test( id number, email varchar2(128));
Table created
SQL> alter table email_test add constraint email_validate_ck check( regexp_like( email, '^.+[@].+[\.].+$' ));
Table altered
SQL> INSERT INTO  email_test VALUES(1, 'waa@aaa.com.tr');
1 row inserted
SQL> INSERT INTO  email_test VALUES(1, 'waaaaa.com');
INSERT INTO  email_test VALUES(1, 'waaaaa.com')
ORA-02290: check constraint (SYS.EMAIL_VALIDATE_CK) violated
SQL>
Regular Expression, Oracle'ye 10G ile gelen bir özelliktir.

Düz PL/SQL ile ise

DECLARE
  email VARCHAR2(32);
  i     NUMBER;
  k     NUMBER;
  part1 VARCHAR2(32);
  part2 VARCHAR2(32);
BEGIN
  email := 'mennan.mennan.men.na';
  dbms_output.put_line('Email is ' || email);
 
  i     := instr(email, '@');
  part1 := substr(email, 1, i - 1);
  email := substr(email, i + 1);
 
  k     := instr(email, '.', 1);
  part2 := substr(email, 1, k - 1);
  email := substr(email, k + 1);
 
  IF i * k = 0 THEN
    dbms_output.put_line('Invalid -->> (@) or (.) not found');
  ELSIF nvl(length(part1), 0) * nvl(length(part2), 0) *
        nvl(length(email), 0) = 0 THEN
    dbms_output.put_line('Invalid -->> Length problem');
  ELSE
    dbms_output.put_line('Email suppose to be valid...');
  END IF;
 
END;

Çıktı ise

Email is mennan@mennan.men.na
Email suppose to be valid...
 
Email is mennan@.men.na
Invalid -->> Length problem
 
Email is mennan.mennan.men.na
Invalid -->> (@) or (.) not found

Oracle Set Operators

Oracle küme operatörleri 4 tanedir. union, union all, minus ve intersect. Oracle küme işlemleri yapılırken select cümlelerinin aynı tip ve sayıda kolon içermelerine dikkat edilmelidir. Eğer bir kümede bulunan kolon diğer kümede yok ise, o kolon NULL ile gösterilmelidir. Sütün isimlerini aynı olmasına gerek yoktur. Set operatörleri ile işlem yaparken ORDER BY varsayılan olarak olarak ORDER BY 1 ASC'dir. Yani ilk kolon artan olarak sıralanacaktır.

Bunları bir örnek ile anlatmaya çalışalım. Öncelikle iki küme oluşturup işlemlerimizi onlar üzerinde yapalım.

create table a( x number);
create table b( x number);

BEGIN
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (3);
INSERT INTO a VALUES (4);
INSERT INTO b VALUES (3);
INSERT INTO b VALUES (4);
INSERT INTO b VALUES (5);
INSERT INTO b VALUES (6);
INSERT INTO b VALUES (7);
COMMIT;
END;

SELECT * FROM a
--1,2,3,4

SELECT * FROM b
--3,4,5,6,7
union : İki kümede tekrar etmeyen verileri getirir. Matematiksel karşılığı (a+b)'dir

-- (a + b)
--No duplicates
SELECT x
FROM a
UNION
SELECT x FROM b
--1,2,3,4,5,6,7
union all : İki kümede verilerin tamamını getirir. Matematiksel karşılığı (a)+(b)'dir

-- (a) + (b)
--Duplicates
SELECT x
FROM a
UNION ALL
SELECT x FROM b
--1,2,3,4,3,4,5,6,7
minus : Sadece ilk kümede olan elemanları getirir. Matematiksel karşılığı (a) - (b)'dir

-- (a) - (b)
--Difference
SELECT x
FROM a
MINUS
SELECT x FROM b
--1,2
intersect : İki kümede kesişen elemanları getirir. Matematiksel karşılığı (a) + (b) - ( a + b)'dir

-- ( ( (a) + (b) ) - ( a + b) )
--intersect
SELECT x
FROM a
INTERSECT
SELECT x FROM b
--3,4

Oracle Single Row Character SQL Functions

Oracle, standart SQL üzerinde bir takım geliştirmeler içine yaparak kullanıcıların sıkça kullanabilecekleri fonksiyonları bütünleştirmiştir. Bunlar starndart SQL cümlerinin yapamadığı fonksiyonaliteleri kolayca sağlarlar. Genel olarak iki tek ve çoklu satır fonksiyonları olmak üzere iki gruba ayrılabilir:

Oracle Tek Satır SQL Fonksiyonları(Oracle Single Row SQL Functions)

Bunlar bir sorgu içinde yazıldıklarında her satır için ayrı bir değer döndürürler. Kendi içinde bunları karakter, sayısal ve dönüştürme olarak bölümlendirebiliriz. 

Karakter Fonksiyonları:

Karakter fonksiyonları kendilerine gelen string bir ifadeyi işleyip sonucunda ya string ya da sayısal bir ifade döndürürler. 

UPPER: Gelen stringin tamamını büyük harfe çevirir.

SELECT upper('My name is Oracle') result_value FROM dual--MY NAME IS ORACLE 

LOWER: Gelen stringin tamamını küçük harfe çevirir. 

SELECT lower('MY NAME IS ORACLE') result_value FROM dual--my name is oracle 

INITCAP: Gelen stringin içindeki her kelimenin ilk harfini büyük, diğerlerini küçük harfe çevirir. 

SELECT initcap('my name is oracle') result_value FROM dual--My Name Is Oracle

CONCAT : İki stringi birleştirir. Aynı işlevi || de gösterir. 

SELECT concat('My name ', ' is Oracle' ) result_value FROM dual--My name  is Oracle
SELECT 'My name ' || ' is Oracle' result_value FROM dual--My name  is Oracle

SUBSTR : Bir stringi, verilen karakterden sayısından itibaren, belirtilen karakter kadar alt stringe böler 

SELECT substr('My name is Oracle',12,6 ) result_value FROM dual--Oracle

LENGTH : Birçok programalama dilinde olduğu gibi verilen stringin boyutunu belirtir. Sayısal değer döndürür. 

SELECT length('Oracle' ) result_value FROM dual--6

INSTR : Bir string içinde belirtilen stringin geçip geçmediğini belirtir. Geçiyorsa, bulunduğu noktayı gösterir. Yoksa 0 döndürür. 

SELECT instr('My name is Oracle', 'Oracle', 1, 1) result_value FROM dual--12

LPAD : Sola hizalar. Tabular formatta veri hazırlamak için kullanılabilir. 

SELECT lpad('My name is Oracle', 20, '#') result_value FROM dual--###My name is Oracle

RPAD : LPAD gibidir. Tek farkı sağa hizalar. 

SELECT rpad('My name is Oracle', 20, '#') result_value FROM dual--My name is Oracle###

TRIM : Verilen string içinden belirli bir karakteri(boşluk) baştan ve sondan siler. 

SELECT trim('    My name is Oracle    ') result_value FROM dual--My name is Oracle

REPLACE : Verilen string içinden bir stringi verilen başka bir string ile değiştirir. 

SELECT replace('My name is Database', 'Database', 'Oracle') result_value FROM dual--My name is Oracle

Bu fonksiyonlar birlikte kullanıldığında çok işe yarar. Örnek olarak tablodaki bir sütunun değeri “128596a;6659842d” gibi bir ifade olsun. Son karakterin a veya d olması aktif veya deaktif olduğunu belirtsin. Bu durumda aktif olan kayıtları bulmak için: 

SELECT upper( substr('128596a;6659842d',-1,1)) result_value FROM dual--D