Oracle PL/SQL Sorguları Ders 1

Oracle PL/SQL çalışma sorguları Ders 1 başlığı altında toplayarak ilerleyen günlerde buna benzer başlıklar açmaya çalışacağım. Data sorgu ve test sırasında işinize yarayacak sorgu cümleciklerini ve kısa yollarını daha çok paylaşacağım. Örnek Database olarak Human Resources HR Schema üzerinden anlattım.

1) PL/SQL'de Tarih Filtreleme (TO_DATE)
SELECT * FROM HR.JOB_HISTORY
WHERE EMPLOYEE_ID > 102
AND START_DATE > TO_DATE('21.09.1997 00:00:00','DD.MM.YYYY HH24:MI:SS')
AND END_DATE < TO_DATE('31.12.2007 00:00:00','DD.MM.YYYY HH24:MI:SS')

Bir kaydı SYSDATE ile bugünün tarihine güncelleyelim;
UPDATE HR.JOB_HISTORY SET START_DATE = SYSDATE WHERE EMPLOYEE_ID=176

2) UPDATE-SET Kullanımı (Data Güncelleme)
UPDATE TableName SET ColumnName='Data' WHERE ColumnNameID=5

Eğer WHERE koşulu kullanmadan sorguyu çalıştırırsanız o kolondaki tüm kayıtlara aynı Datayı basar;
UPDATE TableName SET ColumnName='Data'

Tüm MIN_SALARY kolonuna 1000 Datasını ekledik;

UPDATE HR.JOBS SET MIN_SALARY=1000

JOB_ID=100 Olan kolonun MIN_SALARY Datasını sadece değiştirdik;

UPDATE HR.JOBS SET MIN_SALARY=2000 WHERE JOB_ID='100'

UPDATE HR.JOBS SET MIN_SALARY=MIN_SALARY+100 WHERE JOB_ID='100'

Aynı Satır için birden fazla kolonu UPDATE etmek için;

UPDATE HR.JOBS SET MIN_SALARY=100,MAX_SALARY=900,JOB_TITLE='TEST' WHERE JOB_ID='100'

3) SELECT içinde CASE-WHEN Kullanımı
CASE-WHEN yapısı programlamadaki IF-THEN-ELSE yapısına benzer. Koşulu muza göre istediğimiz sonucu üretebiliriz. Birden fazla CASE-WHEN ilişkisini tek seferde kullanmak için;

SELECT
POSTAL_CODE,
CASE POSTAL_CODE WHEN '6823' THEN '1' WHEN '1689' THEN '2' ELSE '3' END POSTAL_CODE_NEW,
COUNTRY_ID,
CASE COUNTRY_ID WHEN 'IT' THEN 'ITALY' WHEN 'US' THEN 'ABD' ELSE 'COUNTRY' END COUNTRY_ID_NEW,
STATE_PROVINCE,
CASE WHEN STATE_PROVINCE IS NULL THEN NULL ELSE STATE_PROVINCE END STATE_PROVINCE_NEW,
LOCATION_ID,
CASE LOCATION_ID WHEN 1 THEN 'Adres1' WHEN 2 THEN 'Adres2' ELSE LOCATION_ID+50 END LOCATION_ID_NEW
FROM HR.LOCATIONS

* CASE-WHEN yapısı ile LIKE,IN,NOT IN, <, > ,= operatörlerini kullanabiliriz.
* CASE-WHEN yapısı DECODE'a göre daha performanslı çalışır.
* DECODE ile sadece eşitse şart kontrol edilir. Bu yüzden CASE WHEN daha esnek bir yapıya sahiptir.

4) SUBSTR Kullanımı
String verilerde istenilen karakter kadar verinin geri döndürülmesini sağlamak için SUBSTRING fonksiyonu kullanılır.
SELECT LC.LOCATION_ID, SUBSTR(LC.LOCATION_ID,1,3)||'-'||POSTAL_CODE AS NEW_LOCATION_ID FROM HR.LOCATIONS LC

5) İlişkili tablolardan veri silmek
Bir Database'de ana tablo ve onunla ilişkili tablo düşünün. Örnekle kişilerin kayıt olduğu MEMBER adında bir tablomuz olsun ve her kişi için buradaki Unique Data ID kolonunda tutulsun. Diğer tablolarda ise bu ana tablo ile bağlantısı ID=MEMBER_ID kolon ilişkisi ile tutulsun. Siz arayüz yani ekrandan kişiyi sildiğiniz zaman MEMBER tablosundaki veriler gider ama diğer tablolardaki eski veriler kalır ve veri kirliliğine sebep olur. İşte bir kişiye ait Database'de tüm verileri temizlemek için aşağıdaki benzer sorguyu kullanabiliriz. TOAD'da F5 ile komutları sırayla çalıştırabilirsiniz.

DELETE FROM MEMBER WHERE ID='1234-abcd-3456';
DELETE FROM MEMBER_ADDRESS WHERE MEMBER_ID NOT IN (SELECT ID FROM MEMBER_ID);
DELETE FROM MEMBER_FAMILY WHERE MEMBER_ID NOT IN (SELECT ID FROM MEMBER_ID);
DELETE FROM MEMBER_EDUCATION WHERE MEMBER_ID NOT IN (SELECT ID FROM MEMBER_ID);
DELETE FROM MEMBER_MILITARY WHERE MEMBER_ID NOT IN (SELECT ID FROM MEMBER_ID);
DELETE FROM MEMBER_SKILLS WHERE MEMBER_ID NOT IN (SELECT ID FROM MEMBER_ID);
COMMIT;

6) INSERT INTO Kullanımı (Data Ekleme)
Tabloya yeni bir Data ekleyeceğimiz zaman kullandığımız komuttur. DML komutlar arasında yer alır.

Tablodaki tüm kolonlar sırayla biliniyorsa;
INSERT INTO TableName VALUES (value1,value2,value3,...);

Kolonlar belirtilerek eklenmek istenirse;
INSERT INTO TableName (column1,column2,column3,...) VALUES (value1,value2,value3,...);

Örnek verecek olursak;

INSERT INTO HR.DEPARTMENTS VALUES(11,'Mainframe',201,1700)

INSERT INTO HR.DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME) VALUES(12,'IT System')

Başka bir tablodan satır kopyalamak için;
INSERT INTO HR.JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
SELECT DEPARTMENT_NAME,'System',30,80 FROM HR.DEPARTMENTS
WHERE DEPARTMENT_ID=12

7) UNION Kullanımı
Farklı tablolarda aynı kolon isimleri varsa bu kolonlardaki Dataları birleştirerek görüntüleme işlemini UNION sorgusu ile yapıyoruz. Eğer kolonlarda aynı Data'dan birden fazla varsa sadece bir tanesini gösterir DISTINCT mantığı, siz kendini tekrarlayan Datalar da dahil hepsini görmek için UNION ALL kullanmanız gerekir.

Örnek UNION kullanımı;

SELECT DEPARTMENT_ID FROM HR.JOB_HISTORY
UNION
SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS

Örnek UNION ALL kullanımı (Tüm Data çoklayanlar ile birlikte);

SELECT DEPARTMENT_ID FROM HR.JOB_HISTORY
UNION ALL
SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS

UNION kodunun ana mantığı SELECT ve FROM arasındaki kolon isimlerinin aynı olması gerekir, Eğer biri farklı olursa sorgu çalışmayacaktır.

SELECT EMPLOYEE_ID,DEPARTMENT_ID FROM HR.EMPLOYEES
UNION
SELECT EMPLOYEE_ID,DEPARTMENT_ID FROM HR.JOB_HISTORY

8) NOT IN Kullanımı
Listede istemediğiniz (1000,1100,1700) değerleri göstermemek için NOT IN sorgusunu kullanırız;

SELECT * FROM HR.LOCATIONS
WHERE LOCATION_ID NOT IN (1000,1100,1700)