Mevlüt Becerikli

Soru :

Veritabanımızda bir tablo sütununda farklı (aşağıdaki gibi) formatlarda telefon numaraları var.  Bizden bu verileri tek tip formata çevirmemiz isteniyor.

Mevcut Formatlar :

+1 123 456 7890
+1-123-456-7890
+1-123/456 7890

İstenen :

+1.123.4567890

Çözüm :

SELECT TRANSLATE(’+1 123 456 7890′, ‘ /-’,'…’) FROM DUAL
SELECT TRANSLATE(’+1-123-456-7890′, ‘ /-’,'…’) FROM DUAL
SELECT TRANSLATE(’+1-123/456 7890 ‘, ‘ /-’,'…’) FROM DUAL

Açıklama :

TRANSLATE komutunun kullanımı aşağıdaki gibidir;

TRANSLATE( ilgili_metin , degistirilecekler, eklenecekler )
ilgili_metin : İçeriği değiştirilecek metin
degistirilecekler : İçeriği değiştirilecek metin içerisinden değişecek karakterlerin dizisi
eklenecekler : degiceşekler kısmında her bir karakterin yerine gelecek karakterleriden oluşan dizi

Bu açıklamadan sonra gelelim bizim çözümümüze. Bu komut ile ilgili_metin içinde yer alan ve değiştirmeyi istediğimiz karakterleri sırası ile degistirilecekler kısmına yazıyoruz. Her bir karakterin yerine gelmesini istediğimiz karakterleri gene aynı sırada olmak kaydıyla eklenecekler kısmına yazıyoruz.

Burada önemli; komutun tek bir karakter bazında çalışması (Yani “12″ yerine “34″ yapamıyoruz, “1″ yerine “3″ ve “2″ yerine “4″ olacak şekilde çalışıyor) ve degistirilecekler ile eklenecekler dizilerindeki karakterlerin sıralarının aynı olmasıdır.

NOT : Aynı soruna içiçe REPLACE komutu ile de çözüm üretebiliriz. Ama  TRANSLATE komutu sanırım bu okuması-yazması karışık ve hataya açık durumdan kurtulmak için geliştirilmiş;

SELECT REPLACE(REPLACE(REPLACE(’+1 123 456 7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL
SELECT REPLACE(REPLACE(REPLACE(’+1-123-456-7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL
SELECT REPLACE(REPLACE(REPLACE(’+1-123/456 7890′, ‘ ‘,’.'),’/',’.'),’-',’.') FROM DUAL

İyi Çalışmalar…

Bu yazıda klasik insert ifadesinden biraz uzaklaşıp nasıl çoklu veri girişi işlemi yapacağımızı göreceğiz. Konunun detayını bende ihtiyaç doğrultusunda öğrendim. Bu özellik Oracle 9i den sonra gelmiş. Farklı varyasyonları var;

1. Koşulsuz çoklu giriş
2. Koşullu çoklu giriş
3. Koşullu ama koşula ilk uyan verinin girişi
4. Pivot Tablo yapısında çoklu veri girişi

1. KOŞULSUZ ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

INSERT ALL
INTO <tablo_ad> VALUES <sutun_degerleri)
INTO <tablo_ad> VALUES <sutun_degerleri)
…
SELECT <sutun_isimleri> FROM <tablo_ad>;

Örnek :

INSERT ALL
INTO tbl_tip VALUES (tip_id)
INTO tbl_marka VALUES (marka_id)
SELECT marka_id, tip_id FROM tbl_arabalar;

SELECT ifadesinden gelen verileri INSERT ALL kısmında ki tablolara ekliyoruz.

2. KOŞULLU ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

INSERT ALL
WHEN (<condition>) THEN
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
WHEN (<condition>) THEN
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
ELSE
INTO <tablo_ad> (<sutun_isimleri>)
VALUES (<deger_listesi>)
SELECT <sutun_isimleri> FROM <tablo_ad>;

Örnek :

INSERT ALL
WHEN (marka_id = 1) THEN
INTO tbl_bmw VALUES (plaka)
WHEN (marka_id = 2) THEN
INTO tbl_audi VALUES (plaka)
SELECT marka_id, plaka FROM tbl_arabalar;

SELECT ifadesinden gelen verileri INSERT ALL kısmında ki WHEN koşuluna göre ilgili tablolara ekliyoruz. Burda ilk şarta uysa bile tüm koşullara bakılıyor.

3. KOŞULLU AMA KOŞULA İLK UYAN VERİNİN GİRİŞİ ( INSERT FIRST)

Syntax:

Koşullu INSERT ALL ile aynıdır sadece INSERT ALL yerine INSERT FIRST ifadesi kullanılır. Farkı; ilgili koşulları sırası ile değerlendirir ve istenen şartı ilk sağlayan koşul da veri girişini yapar ve sıradaki diğer şartlara bakmaz.

Örnek:

INSERT FIRST
WHEN (model < 1980) THEN
INTO tbl_hurda VALUES (plaka)
WHEN (model < 2000) THEN
INTO tbl_normal VALUES (plaka)
WHEN (model > 2010) THEN
INTO tbl_lux VALUES (plaka)
SELECT marka_id, plaka FROM tbl_arabalar;

Bu örnekte, araçları modellerine göre ayırıyor ve buna göre farklı tablolara atıyoruz. Burda şartı sağlayan ilk koşulda işlem yapılıp diğer koşullar es geçiliyor.

4. PİVOT TABLO YAPISINDA ÇOKLU VERİ GİRİŞİ ( INSERT ALL )

Syntax:

Koşulsuz veri girişi ile aynıdır. Burda asıl amaç ilişkisel veritabanı mantığına aykırı olan ve yatay olarak genişliğe sahip bir tablonun ilişkisel hale getirilmesi işidir (Belki başka amaç içinde kullanılabilir).

Örnek :

INSERT ALL
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PAZARTESI’,tut_pazartesi)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’SALI’,tut_sali)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CARSAMBA’,tut_carsamba)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PERSEMBE’,tut_persembe)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CUMA’,tut_cuma)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’CUMARTESI’,tutCumartesi)
INTO HAFTALIK_SATIS (id,urun_ad,gun,tutar) VALUES (id,urun_ad,’PAZAR’,tut_cumartesi)
SELECT satis_id, urun_ad, tut_pazartesi, tut_sali, tut_carsamba, tut_persembe, tut_cuma, tut_cumartesi, tut_pazar
FROM satis;

Bu örnekte “satis_id / urun_ad / tut_pazartesi / tut_sali / tut_carsamba / tut_persembe / tut_cuma / tut_cumartesi / tut_pazar” alanlarını sahip bir tabloyu “id / urun_ad /gun / tutar” alanlarına sahip bir tabloya atmış oluyoruz

İyi Çalışmalar…

SQL *Plus’ ta bir SQL script çalıştırdım. Gayet güzel çalıştı. Ama script dosyası içeriğini okuması kolay olsun diye formatladım. Tekrar çalıştırdığımda hata verdi…

SP2-0042: unknown command “FROM” - rest of line ignored.
SP2-0734: unknown command beginning “SELECT…” - rest of line ignored.

Sorunun kaynağının değişiklik yaparken eklenen YENİ SATIR KARAKTERİ olduğu anlaşıldı. SQL* Plus ortamında bunun da çözümü mevcut. Script i çalıştırmadan önce ;

SQL> set sqlblanklines on

ifadesini çalıştırın…

İyi Çalışmalar…

ORACLE veritabanımızdaki mevcut bir kullanıcıya, mevcut bir şemadaki tüm tablolara erişim yetkisi (SELECT, UPDATE,INSERT,DELETE …vs.) verilmesini istiyoruz. Bunun tek bir GRANT komutu ile yapamayız. TOAD gibi araçlar ile kolayca yapabilsekte bunu tek tek yapmak da sıkıcı olsa gerek ?
Bu iş için bir PLSQL bloğu yapabiliriz…

EGITIM şemasındaki tabloları için MEVLUT kullanıcısını yetkilendirelim;

FOR x IN (SELECT * FROM dba_tables WHERE owner = ‘EGITIM’)
LOOP
EXECUTE IMMEDIATE ‘GRANT SELECT, UPDATE, DELETE, INSERT ON ‘ || x.owner || ‘.’ || x.table_name || ‘ TO MEVLUT’;
END LOOP;

NOT : Tabiki bu script çalıştırıldıktan sonra veritabanında EGITIM şemasında oluşturulan tablolara MEVLUT kullanıcısının yetkisi olmayacaktır. Script kaydedilip düzenli olarak çalıştırılabilir.

Şu an sadece tablo için ve manual olarak konuşuyorum. Ama bunu DDL ifadelerine endeksli bir TRIGGER ile otomatik olarak da yapmamız mümkün. Mesela bir tablo yaratıldığında MEVLUT kullanıcısına SELECT yetkisi verilmesini sağlayan bir TRIGGER yazalım;

CREATE OR REPLACE TRIGGER do_grant
AFTER CREATE ON SCHEMA
DECLARE
l_str varchar2(255);
l_job number;
BEGIN
IF ( ora_dict_obj_type = ‘TABLE’ ) THEN
l_str := ‘EXECUTE IMMEDIATE “GRANT SELECT ON ‘ || ORA_DICT_OBJ_NAME || ‘ TO mevlut”;’;
DBMS_JOB.SUBMIT( l_job, replace(l_str,’”‘,””) );
END IF;
END;

NOT :Burada  ORA_DICT_OBJ_NAME ifadesi, bir DDL  ifadesi (System Event) çalıştırıldığında  oluşan nesne tipidir (Dictionary  Object Type). Burada oluşan tabloyu verir. Bunun gibi bir çok nesne tipi vardır. Bunlar için aşağıdaki linkleri incelemenizi öneririm.

http://www.java2s.com/Tutorial/Oracle/0560__Trigger/ORADICTOBJTYPE.htm
http://psoug.org/reference/system_events.html

İyi Çalışmalar…

SORU :

Tablomuz (TEST_TABLE)

COL1   COL2
1          A
1          B
1          C
2          A
2          C

1       A, B, C
2       A, C

ÇÖZÜMLER :

1. YOL (CONNECT BY) :

SELECT COL1,
SUBSTR(SYS_CONNECT_BY_PATH(COL2, ‘,’), 2) LISTE
FROM (select COL2,
COL1,
COUNT(*) OVER (PARTITION BY COL1) cnt,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) seq
FROM (SELECT * FROM TEST_TABLE))
WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR COL1 = COL1;

2. YOL (XML) :

SELECT COL1,
RTRIM(XMLAGG(XMLELEMENT(c, COL2 ||’, ‘)).EXTRACT(’//text()’), ‘, ‘) LISTE
FROM TEST_TABLE
GROUP BY COL1;

3. YOL (WM_CONCAT) :

SELECT COL1,
WM_CONCAT(COL2) LISTE
FROM TEST_TABLE
GROUP BY COL1;

İyi Çalışmalar…

Bu yazımızda da JOIN, SUBQUERY ve INLINE QUERY ifadelerinin yapısal farklılıklarını birer örnek ile ele alacağız. Aşağıdaki gibi 4 tablomuz olduğunu düşünelim.

Şimdi 1800 numaralı (JOB_ID) işe için çalışan, departman, iş ve lokasyon bilgilerini bu 4 tablodan çekmek isteyelim.

JOIN : İlk aklımıza gelen ilgili anahtar alanların birbirine eşitlik durumu ile JOIN işlemi olacaktır.

SELECT d.d_name, l.loc_name,  j.job_title,  e.salary
FROM employees e,  departments d, location l, job j
WHERE e.d_id = d.d_id
AND e.job_id = j.job_id
AND d.loc_id = l.loc_id
AND e.job_id = 1800

SUBQUERY : Genel itibarı ile kendisindan başka bir SQL ifadesinin içinde parantezler içinde olan SQL ifadesidir. Bir SQL ifadesinin ya SELECT kısmında yada WHERE kısmında olur. SUBQUERY, her tablo için sadece bir kere değerlendirilir.

SELECT d.d_name, (SELECT l.loc_name FROM location l where l.loc_id = d.loc_id) , j.job_title, e.salary
FROM employees e , departments d , job j
WHERE e.d_id = d.d_id
AND e.job_id = j.job_id
AND e.job_id = 1800

INLINE QUERY (INLINE VIEW) : Eğer SUBQUERY ifadesi, ana SQL ifadesinin FROM kısmında ise buna INLINE QUERY (INLINE VIEW) diyoruz. Burada amaç kompleks bir JOIN işleminin yerine daha sade bir SQL ifade elde etmek yada birbirinden ayrık SQL ifadelerini bir SQL ifadesi içinde elde etmektir.

SELECT e.salary, j.job_title , s.d_name , s.loc_name
FROM employees e ,job j,
(SELECT d.d_name, l.loc_name, d.d_id FROM departments d , location l WHERE d.loc_id = l.loc_id ) s
WHERE e.d_id = s.d_id
AND e.job_id = j.job_id
AND e.job_id = 1800

İyi Çalışmalar…

Farklar :

• DECODE, IF-THEN-ELSE in basit versiyonudur.
• DECODE sadece eşitlik operatörünü kullanırken, IF-THEN-ELSE ifadesi tüm  koşul operatörlerini (=, !=, <, <=, > , >=, LIKE, NOT LINKE, IS NULL, IS NOT NUL … vs) kullanır.
• DECODE,  tek bir koşulu içerirken, IF-THEN-ELSE ifadesi AND/OR operatörleri yardımı ile birden çok koşulu bir arada birleşik içerebilir.
• DECODE bir veritabanı fonksiyonu olup tekbir ifade döndürdüğü için bir SQL cümleciği içinde kullanılabilirken, IF-THEN-ELSE ifadesi, bir PL/SQL ifadesi olup sadece PL/SQL bloklarının içinde kullanılabilir (SQL ifadeleri içinde kullanılan bir diğer koşul ifadesi de CASE’ dir).

İyi Çalışmalar…

Bir forumda şöyle bir soru sulmuştu. 2 tarih arasındaki tarihlerin listesini nasıl alabilirim. Önce WHERE ifadesine koyacaksa neden istiyor, BETWEEN kullansın dedim ama yazışmamalar devam ettikçe şunu gördüm ki test datası oluşturmak içinmiş. Mesela 2/12/2006 ile 5/12/2006 arasındaki günleri istediğimde sonuç olarakşunu bekliyordu.

2/12/2006
3/12/2006
4/12/2006
5/12/2006

Aklıma bir önceki yazımda ki CONNECT BY ifadesini kullanamak geldi desem yalan olur

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM<5;

Başka bir üye de yukarıdaki SQL in sonuçlarından yola çıkarak aşağıdaki SQL ile gayet güzel çözüm üretti :

SELECT (date ‘2006-12-02′) + ROWNUM - 1
FROM dual
CONNECT BY ROWNUM <= ((date ‘2006-12-05′) - (date ‘2006-12-03′) + 1)

Tamam! Adamı kutlamak lazım. Test datası oluşturmak için güzel bir yöntem. Ama aklıma bir soru takıldı :

DUAL tablosunda kaç tane kayıt var ???

Bu ünlü tablonun içeriğine baktım :

SELECT * FROM dual;

DUMMY
—–
X

SELECT ROWNUM FROM dual;

ROWNUM
——
1

Kayıtları çoklayalım

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM<5;

ROWNUM
———-
1
2
3
4
5

Eee! Sadece 1 kayıt vardı. Şimdide maksimum ROWNUM‘ ı soralım.

SELECT MAX(ROWNUM) FROM dual CONNECT BY ROWNUM <5;

MAX(ROWNUM)
———–
5

Umarım olay anlaşılmıştır….

İyi Çalışmalar…

Eğer tablomuz hiyerarşik bir veri yapısına (Örneğin Tree yapısı) sahip ise, verinizi hiyerarşisindeki sırasına göre getirmeniz mümkün. Aşağıdaki gibi bir veri yapımız oldugunu düşünelim.

İsterseniz SQL ifadeleri üzerinden gidelim ve sonuçlarını inceleyelim. Yukarıdaki yapıyı Oracle’ daki bir tabloda şu şekilde tutalım.

SQL 1 :
SELECT name, id, parent FROM test_table

Görüldüğü gibi veri yapımızı soyağacı yapımıza göre tasarladık…

SQL 2 :
SELECT name, id, parent
FROM test_table
CONNECT BY PRIOR id = parent;

CONNECT BY PRIOR ile her veri satırına ait olası tüm ağaç ve alt ağaç yapılarını veren bir sonuç kümesi mevcut. Bu sonuç biraz karmaşık gelebilir. Bir başlangıç noktası belirtmek istersek;

SQL 3 :
SELECT name, id, parent
FROM test_table
CONNECT BY PRIOR id = parent
START WITH parent = 0

START WITH ifadesi ile “0″ yani ağacımızın en tepesinde bulunan Büyükannemize ait alt kırılımları görüyoruz. Bu sonuç kümesini daraltmış olsada karmaşıklıgı ortadan kaldırmadıgını varsayalım ve verimizi biraz daha anlandırmak adına Oracle ın nimetlerinden faydalanalım;

SQL 4 :
SELECT name, id, parent, SYS_CONNECT_BY_PATH(name, ‘ > ‘) AS yol
FROM test_table
CONNECT BY PRIOR id = parent
START WITH parent = 0;

Yol sütunumuzdan da anlaşılacagı gibi Ayşe büyükannemizin kendinden ufak olan aile fertlerini soyagacımız da ki yapıya uygun olacak şekilde anlamlaştırdık. Peki PRIOR ifadesinin yerini değiştirirsek ne olur ?

SQL 5 :
SELECT name, id, parent, SYS_CONNECT_BY_PATH(name, ‘ > ‘) AS yol
FROM test_table
CONNECT BY id = PRIOR parent
START WITH parent = 0;

Demek ki PRIOR ifadesi CONNECT BY ile verilen şartta kendinden önceki ağaç seviyelerini baz alacagını bize veriyor. Burada da PARENT=0 diye bir başlangıç noktası verdiğimiz  ve PARENT alanını baz aldıgımız için kendi ve kendinden öncesini getirmiş oldu. Sql ifademizi biraz daha renklendirelim;

SQL 6 :
SELECT name, id, parent, CONNECT_BY_ISLEAF AS yaprak, LEVEL AS seviye
FROM test_table t
START WITH parent = 0
CONNECT BY PRIOR id = parent

Bu son ifade de yeralan CONNECT_BY_ISLEAF ifadesi ile ilgili kaydın ağaç yapımızda ki son veri (Bir ağaçtaki son uzuv yaprak olmasından feyz alınarak) ise de “1″ değerini, kendine ait alt bir verisi varsa da “0″ degerini döndürür. Bir diğer ifade olan LEVEL ile de büyükannemize olan birim uzaklık değerini alıyoruz.

Var mı sorusu olan ????

İyi Çalışmalar…

LAG : Bir oracle veritabanı tablosundaki kayıt için, kendinden önceki kayda ait bilgiye ulaşmamızı sağlar. Hemde herhangi bir join  (SELF JOIN) işlemine tabi tutmadan. Kullanımı;

LAG( expression [, offset [, default] ] )
OVER ( [ query_partition_clause ] order_by_clause )

LEAD: Bir oracle veritabanı tablosundaki kayıt için, kendinden sonraki kayda ait bilgiye ulaşmamızı sağlar. Hemde herhangi bir join  (SELF JOIN) işlemine tabi tutmadan. Kullanımı;

LEAD ( expression [, offset [, default] ] )
OVER ( [ query_partition_clause ] order_by_clause )

expression : sütun ismi belirtiyoruz.
offset : kaç kayıt öncesini istiyorsak onu bildiriyoruz.
default : ilk kayıt için önceki kayıt olmadııg için bu durumda gelecek ifadeyi veriyoruz.

Örnek Kullanım :

SELECT LAG(IL_KODU, 1, ‘-’) OVER (ORDER BY a.IL_KODU ASC) AS ONCEKI_KOD,
LAG(IL_ADI, 1, ‘-’) OVER (ORDER BY a.IL_KODU ASC) AS ONCEKI_AD,
a.IL_KODU AS MEVCUT_KOD,
a.IL_ADI AS MEVCUT_AD,
LEAD(IL_KODU, 1, ‘-’) OVER (ORDER BY a.IL_KODU ASC) AS SONRAKI_KOD,
LEAD(IL_ADI, 1, ‘-’) OVER (ORDER BY a.IL_KODU ASC) AS SONRAKI_AD
FROM IL a;
Ekran Çıktısı :

İyi Çalışmalar…