HR Şeması İle Uygulamalı SQL(Şema Nesneleri)

Merhaba Arkadaşlar!

Bu yazıda tablolardan sonraki diğer şema nesneleriyle ilgileneceğiz. Böylelikle SQL konusunu bitirmiş oluyoruz. Ancak daha sertifika sınavları sadece SQL'den ibaret değil. Bu sınavlar için PL/SQL'in bazı konularını daha bilmeniz gerekiyor. Bunları da mümkün oldukça burada işleyeceğiz.

1. Görüntüler(Views)
Görüntüler hayali, aslında fiziksel olarak var olmayan tablolardır. Sanal bir tablo olarak düşünebilirsiniz. Bu tabloları bir veya birden fazla gerçek yani fiziksel olarak var olan tabloların birleşiminden oluşturursunuz. Böylelikle aşağıdaki avantajları yakalarsınız:

• Çok sık kullanılan ve aynı tabloda bulunmayan verilerin bir tabloda birleşimi ile kullanım kolaylığı sağlar.
• Veriyi tablodan bağımsız hale getirir ve kullanım esnekliği sağlar.
• Erişimini kısıtlamak istediğiniz verileri bu yöntemle kısıtlarsınız.
• Karmaşık sorguların (çok fazla JOIN içeren v.b.) kolaylaştırılmasını sağlar.

Bu faydalarla görüntüler işinizi gerçekten kolaylaştıracaktır. Tablo oluştururken kullandığımız DDL komutları bütün şema nesneleri için kullanılır. Oluşturmak için CREATE VIEW, değiştirmek için CREATE OR REPLACE VIEW, düşürmek/kaldırmak için DROP VIEW şeklinde kullanılır.

Görüntüler alt sorgularla oluşturulur. Alt sorguda görüntünün içermesini istediğiniz sütunları SELECT cümlesi ile çekersiniz. Alt sorgunuz karmaşık olabilir. Eğer görüntüdeki sütun isimlerini siz belirlemek istiyorsanız alt sorgudan dönecek sütunlara takma isimler vererek bunu gerçekleştirebilirsiniz. Genel olarak bir görüntü oluştururken aşağıdaki yapıyı kullanırız:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW goruntuismi

  [(takmaisim[, takmaisim]...)]

 AS altsorgu

[WITH CHECK OPTION [CONSTRAINT kisiti]]

[WITH READ ONLY [CONSTRAINT kisiti]];

Şimdi HR şeması ile hazır olarak gelen EMP_DETAILS_VIEW görüntüsünün DDL'ini inceleyelim. Bu arada hazır olarak gelen bu nesnelerin DDL'lerini görmek için üzerine çift tıklayıp sağ kısımdan SQL sekmesi altında izleyebilirsiniz.

  CREATE OR REPLACE FORCE VIEW "HR"."EMP_DETAILS_VIEW" ("EMPLOYEE_ID", "JOB_ID", "MANAGER_ID", "DEPARTMENT_ID", "LOCATION_ID", "COUNTRY_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "COMMISSION_PCT", "DEPARTMENT_NAME", "JOB_TITLE", "CITY", "STATE_PROVINCE", "COUNTRY_NAME", "REGION_NAME") AS 

 SELECT   e.employee_id,   e.job_id,  e.manager_id,  e.department_id,  d.location_id,  l.country_id,  e.first_name,  e.last_name,  e.salary,  e.commission_pct,  d.department_name,  j.job_title,  l.city,  l.state_province,  c.country_name,  r.region_name
FROM  employees e,  departments d,  jobs j,  locations l,  countries c,  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY;

Açıklama: Gördüğünüz gibi biraz uzunca bir DDL. :) Şirket çalışanlarına ait neredeyse bütün bilgileri tek bir tabloda bize sunan bir görüntü. Gerçekten kullanışlı. Çünkü hatırlarsanız bir çalışanın hangi ülkede görevli olduğunu bulmak için 3 adet JOIN işlemi yapıyorduk. Bu sadece 1 örnek. Burada bütün bilgileri tek tabloda bulmaktan söz ediyoruz. Şimdi siz üç adet JOIN yapmayı mı tercih edersiniz, yoksa tek bir görüntü üzerinden aradığınız bilgiye erişmeyi mi? 

İlk satırda CREATE OR REPLACE yada sadece CREATE ifadesini kullanabilirsiniz. Eğer CREATE OR REPLACE kullanırsanız ve görüntü zaten mevcutsa oluşturmak yerine belirttiğiniz değişiklikleri görüntüye işleyecektir. 

Devamında şema nesnesinin ismini EMP_DETAILS_VIEW olarak; amacını belirtir şekilde veriyoruz. Başında HR. olmasının sebebi şema nesnesinin ait olduğu kullanıcı uzayını belirttiğindendir. Kendi şemanız içerisinde görüntü oluştururken bu söz dizimini kullanmanız gerekmez.

Ardından parantezler arasında çift tırnaklarla alt sorgudan dönen sütunlara görüntüdeki yansıma ismini belirliyoruz. Elbette buradaki sıralamanın alt sorgudan dönen değerlerle aynı olması ve veri tiplerinin uyuşması gerektiğini unutmayın.

Daha sonraki AS SELECT kısmında da alt sorgumuzu yazıyoruz ki aslında INNER JOIN içeren bir sorgu olduğunu görüyorsunuz. Yani çok da karmaşık bir sorgu değil. Bu kısmı istediğiniz kadar karmaşık hale getirebilirsiniz. Fonksiyonlar kullanabilirsiniz, GROUP BY ile gruplamalar yapabilirsiniz...

En sonda ise WITH READ ONLY ile bu görüntünün sadece "okuma" amaçlı kullanabileceğimizi belirliyoruz. Yani diğer kullanıcılar yada siz bu görüntü üzerinde DML komutu işletemezsiniz demektir. Aksi halde görüntüler üzerinde değişimler yapabilirsiniz. Yaptığınız değişimler tablolardaki "asıl" veriyi etkiler. Yani değişimleriniz hem görüntüde hem de asıl tablolarda sağlanır. Diğer önemli nokta ise Oracle'da eğer siz asıl tabloları silerseniz, ilgili görüntü halen veritabanında yerini korur. Ancak çalışmaz. Çalışabilmesi için aynı tabloyu tekrar oluşturmanız gerekir yada görüntüyü tamamen silersiniz.

Görüntüyü kaldırmak için şu komutu çalıştırırsınız:

DROP VIEW EMP_DETAILS_VIEW;

Görüntüler Üzerinde DML İşlemleri
Görüntüler üzerinde DML işlemleri yapabildiğinizi belirttik. Ancak bunu yaparken hiç de öyle özgür değilsiniz. Karmaşık alt sorgu içeren görüntülerde DML çalıştıramazsınız. Örneğin şu durumlarda görüntülere veri ekleyip, görüntülerdeki verileri silip-düzenleyemezsiniz:

• Grup fonksiyonları içeren görüntüler.
• GROUP BY ile gruplanan görüntüler.
• DISTINCT içeren görüntüler.
• ROWNUM anahtar kelimesi içeren görüntüler.

WITH CHECK OPTION Kullanımı
Oluşturduğunuz görüntüleri özelleştirmek istiyorsanız bu opsiyonu kullanabilirsiniz. Örneğin sadece Amerika'da çalışanlara ait bir görüntü oluşturmak isterseniz ve diğer ülkelerde çalışan herhangi birini bu görüntüye dahil etmek isteyen birine hata mesajı verilmesini isterseniz bu opsiyon işinizi görür. 

Örneğin sadece job_id'si 'IT_PROG' olan çalışanlar için bir görüntü hazırlayıp daha sonra bu görüntüyü bu seçeneğin kullanarak kısıtlayalım. 


CREATE OR REPLACE VIEW EMP_JOBID_ITPROG 
AS SELECT employee_id AS isciID, first_name || ' ' || last_name AS isim, salary AS maas, job_id AS isID
FROM employees
WHERE job_id = 'IT_PROG'
WITH CHECK OPTION CONSTRAINT emp_jobid_itprog_ck;
CREATE OR REPLACE VIEW succeeded.

Açıklama: Öncelike şunu hatırlayalım: Bir önceki görüntüde yani EMP_DETAILS_VIEW görüntüsünde takma isimleri SELECT içinde vermemiştik. Görüntü isminden sonra parantez içinde tanımlamıştık. Şimdi ise SELECT içinde vererek aynı işlemi sağladık. Yani görüntü sütun isimleri SELECT içerisinde verilen takma isimleri olarak belirlenir ve sorgulama işlemlerini de bu isimlerle yapmanız gerekir. 

Bakıyoruz ki burada herşey normal. WHERE içerisinde istediğimiz şartı belirtip sorgumuzu hazırlıyoruz. Ancak bu şimdilik bir kısıt oluşturmuyor. Bu kısıtı tanımlamak için "WITH CHECK OPTION CONSTRAINT kisitismi" ifadesi ile bu işlemi bir kısıt haline getiriyoruz. Artık IT_PROG dışındaki çalışanları bu görüntüye ekleme INSERT yada görüntüde var olan IT_PROG çalışanlarıyla ilgili UPDATE işlemleri gerçekleşmeyecektir.

Mesela görüntünün sonuçlarını görüntüden veri çekerek görelim:

SELECT * FROM emp_jobıd_ıtprog;

ISCIID       ISIM                MAAS        ISID       
------------ ------------------- ----------- ---------- 
103          Alexander Hunold    9000        IT_PROG    
104          Bruce Ernst         6000        IT_PROG 

...
5 rows selected

Görüntüyü Düzenleme:
Basit bir işlem olup aynı sorguyu bu kez değişiklik yapmak istediğiniz şekliyle yazarsınız. Hemen yukarıdaki görüntüde verdiğimiz sütun isimlerini bu kez SELECT içerisinde vererek değiştirelim:


CREATE OR REPLACE VIEW EMP_JOBID_ITPROG  
(ID, "isim soyisim", "zamlı maaş", "iş")
AS SELECT employee_id, first_name || ' ' || last_name , salary + 200 , job_id 
FROM employees
WHERE job_id = 'IT_PROG'
WITH CHECK OPTION CONSTRAINT emp_jobid_itprog_ck;

CREATE OR REPLACE VIEW succeeded

Bu kadar basit!

2. Sekanslar (Sequences)
Sekansları birer sayı üreteci olarak düşünebilirsiniz. Otomatik olarak gerekli durumlarda tekil sayılar üretmenizi sağlar. Örneğin her defasında bir çalışana siz el ile ID vermek istemeyebilirsiniz. Bu durumda sekanslar devreye girer. Zaten genelde ID (birincil anahtar-primary key) oluşturma amacıyla kullanılırlar. Eğer bir tabloya birden fazla kişinin erişim hakkı söz konusuysa, yani ortak olarak INSERT işlemleri yapılıyorsa, sekanslar ortak olarak kullanılabilirler. Böylece aynı değerin oluşturulmasının önüne geçilir.

Genel olarak bir sekansı oluştururken aşağıdaki söz dizimini kullanırız:

CREATE SEQUENCE sekansadi
[INCREMENT BY artismiktari]
[START WITH baslangicdegeri]
[{MAXVALUE maksimumdeger | NOMAXVALUE}]
[{MINVALUE minimumdeger | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Buradaki değerler tam sayı olarak verilirler. Yukarıdaki isimlerden hangi komutun ne için kullanılacağı açıkça görülüyor. CYCLE|NOCYCLE kısmı sekansın maksimum veya minimum değere ulaştıktan sonra tekrar başa dönüp dönmeyeceğini bildirmenizi sağlar. Bunların yanı sıra bir sekansı nasıl kullanmanız gerektiğini bilmeniz gerekiyor:

• NEXTVAL: Kullanılabilir bir sonraki sayıyı döndürür. Tüm kullanıcılar için her defada tekil-eşsiz bir değer oluşturur.
• CURRVAL: O andaki sekans değerini döndürür.

Şimdi departman tablosuna yeni eklemelerde otomatik olarak bir ID değeri üretecek bir sekans oluşturalım:

CREATE SEQUENCE dept_deptid_seq

                INCREMENT BY 5

                START WITH 270

                MAXVALUE 9999

                NOCACHE

                NOCYCLE;

CREATE SEQUENCE succeeded

Amacı belirtir bir isimle isimlendirtikten sonra artış miktarını 5 olarak belirledik. 270'ten başlatmamızın nedeni son departman ID'sinin 270 olması. Maksimum değer olarak 9999 değerini alacak. ID değerini temsil ettiği için döngüsel olmasına izin vermedik. Ve değerler bellekte saklanmayacak (NOCACHE). Şunu da belirtelim ki eğer değerleri bellekte saklayacak olursak sekans değerlerine ulaşmanız daha hızlı sağlanır.

Şimdi sekansı kullanarak bu tabloya bir INSERT işlemi yapalım:

INSERT INTO departments(department_id, 

            department_name, location_id)

VALUES      (dept_deptid_seq.NEXTVAL, 

            'Halkla İlişkiler', 2100);

Error report:

SQL Error: ORA-00001: benzersiz kural (HR.DEPT_ID_PK) ihlal edildi

Hata vermesinin sebebi sekansın ilk değerini tabloda var olan 270 değerinden başlatmamız oldu. Zaten var olan değeri bir kez daha üretti. İkinci kez SQL'i çalıştırırsanız bu kez hata vermez. Hatta istediğiniz kadar çalıştırın hata vermez. Ben iki kere aynı SQL ile INSERT işlemi yaptım:

1 rows inserted.

1 rows inserted.

Sekans değerini CACHE ile hafızaya kaydederseniz sayı üretme olayınız hızlanır demiştik. Bu işlemde şu durumlarda bazı sıkıntılar oluşabilir:

• Sistem çökerse,
• Transaction ROLLBACK yapılırsa,
• Aynı sekansı alakasız bir tabloda amacı dışında kullanılırsa (örneğin departmana özel tasarlanan sekansın eployee_id için kullanılması gibi)

Bu durumların oluşmaması için sekanslara kullanım amacını belirten isimler vermek en doğrusudur.

Sekansı Düzenleme ve Silme:

ALTER ve DROP komutları işinizi görecektir. Sekansı düzenlerken aynen görüntüyü düzenler gibi aynı kodu tekrar düzenleyeceğiniz yerleri değiştirerek yazarsınız. Mesela yukarıdaki sekansımızın bazı değerlerini değiştirelim:

ALTER SEQUENCE dept_deptid_seq

               INCREMENT BY 25

               MAXVALUE 99999

               NOCACHE

               NOCYCLE;

 ALTER SEQUENCE dept_deptid_seq succeeded.

Silmek için ise;

DROP SEQUENCE dept_deptid_seq;

DROP SEQUENCE dept_deptid_seq succeeded.

Diğer önemli ayrıntılar:

• Sekansları düzenleyebilmeniz için bununla ilgili ayrıcalığınız olmalı (ya sahibi olursunuz yada ALTER hakkınız olur)
• Eğer sekansı farklı bir sayı ile başlatmak istiyorsanız sekansı ALTER komutu ile değiştiremezsiniz. DROP edip tekrar istediğiniz değerle başlayan aynı sekansı oluşturmanız gerekir.

3. İndeksler

İndeksler verilere erişim hızınızı artıran önemli bir veritabanı nesnesidir. Ancak burada kısaca anlatıp geçilecek kadar kolay bir konu olduğu söylenemez. Sadece ana hatlarıyla genel olarak işlemeye bakacağız.

İndeksler sunucu (Oracle) tarafından kontrol edilen şema nesneleridir. Yani bakımlarıyla kullanıcı ilgilenmez. Bir tablo güncellendiğinde indeks de otomatik olarak güncellenir. Bu açıdan kullanıcı sadece indeksi oluşturmakla ve kullanmakla görevlidir. Hatta şu durumlarda indeksler otomatik olarak sunucu tarafından oluşturulur:

• Kullanıcı bir tablo tanımlarken, bir sütunu PRIMARY KEY olarak belirlerse
• Kullanıcı bir tablo tanımlarken, bir sütunu UNIQUE olarak  kısıtlarsa.

Otomatik olarak sunucu tarafından oluşturulan indeksler benzersiz yani unique'dir. Kullanıcılar ise manüel olarak indeks oluştururken benzersiz olmayan indeksler oluştururlar. 

İndeksler aynen bir kitabın arkasındaki indeks bilgisi mantığıyla çalışırlar. Çok sık erişilen satırların bulunduğu sütunlar bu mantıkla indekslenerek erişimlerin hızlanması amaçlanır. Fakat abartarak önümüze gelen her sütuna bir indeks oluşturmamız avantajdan çok dezavantaj doğurur. Bu bakımdan indeks oluşturmada sağlam kararlar almak gerekir. Genelde aşağıdaki durumlarda indeks oluşturmanız önerilir:

• Tablo normal tablolardan daha büyükse,
• Tablodaki sütunlardan birkaçı sıkça WHERE şartında belirtiliyorsa,
• Tablodaki sütunlar JOIN işlemine sık olarak tabi tutuluyorsa,
• Bir sütun çok fazla NULL değer içeriyorsa,
• Sütun çok çeşitli veriler içeriyorsa,
• Sorgular tablodaki verilerin genellikle %2si ile %4ü arasında veri çekiyorsa

indeks oluşturmanız doğaldır. Ancak şu durumlarda da indeks oluşturulması tavsiye edilmez:

• Tablo çok sık güncelleniyorsa,
• Sütunlar sıkça kullanılmıyorsa,
• Sorgularda, verilerin %2si ile %4 ünden fazlasının çekilmesi öngörülüyorsa.

İndeksler oluşturuldukları tablolardan bağımsız olarak çalışırlar. İndeksler programlamadaki pointer (işaretçi) benzeri yapılardır. Hatırlarsak işaretçiler verilerin bulunduğu register'ların adreslerini tutan yapılardı. 

İndeks Oluşturma ve Kaldırma:

Aşağıdaki ifade indeks oluşturmanın genel yoludur:

CREATE [UNIQUE][BITMAP]INDEX indeksadi
ON table (sütunadi[, digersütunlar]...);

İndeksin benzersiz olmasını isterseniz UNIQUE anahtar kelimesini kullanırsınız. İndeksin BITMAP olmasını isterseniz de bu anahtar kelimeyi kullanmalısınız. Bitmap indeksler başlı başına bir konudur; burada değinmeyeceğiz. İndeksi kaldırmak için de aşağıdaki ifade kullanılır:

DROP INDEX indeksadi;

İndeksler de diğer şema nesneleri gibi veri sözlüğünde tutulurlar. Kaldırma komutunu kullandığınızda da veri sözlüğünden kaldırılırlar. Fakat bir indeksi kaldırabilmeniz için bununla ilgili haklara sahip olmalı veya indeksin sahibi olmalısınız. Şimdi countries tablosunda country_name sütunu için bir indeks oluşturalım.

CREATE INDEX cou_country_name_idx

ON countries(country_name);

CREATE INDEX succeeded.

DROP INDEX cou_country_name_idx;

DROP INDEX succeeded.

4. Eş Anlamlılar (Synonyms)

İsminden de anlaşılacağı gibi herhangi bir nesne için alternatif bir isim oluşturmanızı sağlar. Böylece nesneye erişim kolaylaşır. Hem de nesne isimlerini bu yolla kısaltabilirsiniz. Örneğin görüntü veya indeks isimlerini uzun uzun yazıyoruz. Bunun gibi sık kullanılan nesneleri kısa isimlerle anmak isteyebilirsiniz. Az önce tasarladığımız indeks için bir eş anlamlı oluşturalım:

CREATE SYNONYM ulke_ix

FOR cou_country_name_idx;

CREATE SYNONYM succeeded.

DROP SYNONYM ulke_ix;

DROP SYNONYM ulke_ix succeeded.

Bu konumuzun da sonuna gelmiş olduk. SQL bundan ibaret. Hatta anlattıklarımızın içerisinde baya bir PL/SQL bilgisi de mevcut. Bundan sonra sertifika sınavları için ihtiyaç duyacağımız bir kaç PL/SQL konusunu inceleyeceğiz. Kolay gelsin!