Veri Taban Ynetim Sistemleri 2 Ders 5 PLSQLe

Veri Tabanı Yönetim Sistemleri 2 Ders 5 PL/SQL'e Giriş, Prosedür ve Fonksiyonlar Yrd. Doç. Dr. Altan MESUT Trakya Üniversitesi Bilgisayar Mühendisliği Bölümü

PL/SQL nedir? • Oracle İVTYS üzerinde kullanılmak üzere, ADA programlama dili temel alınarak geliştirilen standart SQL'e prosedürel dil özellikleri eklenerek oluşturulmuş dildir.

SQL ile PL/SQL’in arasındaki temel fark: • SQL declarative ve 4 GL bir dildir. Yani doğal dile yakındır. Kullanıcı ne yapmak istediğini yazar, nasıl yapılacağının adımlarını ayrıntılı olarak vermez. – Ahmet SOLMAZ isimli personelin maaşını bulmak için basit bir SELECT ifadesi yazmak yeterlidir. • PL/SQL ise C/C++ gibi 3 GL bir dildir. Doğal dile daha uzaktır. İşlemin adımları açıkça yazılmalıdır. – Ahmet SOLMAZ isimli personelin maaşını bulmak için C dilinde önce bir dosyayı açıp, dosya sonuna kadar bir döngü ile bu personeli arayıp, ilgili kaydı bulduğunuzda maaş alanını döndürmek için uzun bir yazarsınız. SQLkod bir sorgulama dili, PL/SQL ise bir programlama dili olarak nitelendirilebilir.

Neden PL/SQL Kullanırız? • Bazı durumlarda işlemin «nasıl» yapılacağını vermek gerekebilir. * • Birçok SQL komutunu bir blok içinde toplayarak tek seferde veritabanına göndermek performans artışı sağlayabilir. • İşletim sisteminden ve platformdan bağımsız olmak üzere Oracle sunucusunun olduğu her ortamda çalışabilmesi nedeniyle PL/SQL saklı yordamlarını kullanmak esneklik sağlar. • «Exception Handling» mekanizmasına sahip olduğu için hata durumlarında yapılacak işlemleri tanımlayabiliriz. * SQL’in hangi durumda yetersiz kalabileceği hakkında bir örnek için: Database Programming with PL/SQL, Section 1, Lesson 1, Slayt 8 -13

PL/SQL Bloğu • Bir PL/SQL bloğu BEGIN ile başlar ve END; ile biter. • Blok içinde değişkenler kullanılacak ise BEGIN öncesinde DECLARE ile başlayan bir tanımlama kısmında tanımlanmalıdırlar. • Bloğun sonunda eğer gerekli ise EXCEPTION ile başlayan bir hata işleme kısmı bulunabilir (END; öncesinde verilmelidir).

Blok Tipleri Anonim Prosedür Fonksiyon

Prosedürel Yapılar • PL/SQL'de aşağıdaki prosedürel yapılar kullanılır: – Değişkenler, sabitler ve tanımlamalı veri tipleri – Kontrol yapıları (If-Else, Case-When) – Döngüler (For ve While) – Bir defa yazılıp birçok defa kullanılabilen programlama elemanları (Fonksiyonlar ve Prosedürler)

Değişken Tanımlama Sentaks: Örnekler: Değişkene blok içinde değer atarken de : = operatörü kullanılır. Veri Tipleri hakkında detaylı bilgi için: Database Programming with PL/SQL, Section 2, Lesson 3, Recognizing Data Types

PL/SQL Bloğu Örnekleri • Sistem tarihini gösterme: BEGIN DBMS_OUTPUT. PUT_LINE('Bugün = ' || SYSDATE); END; • Değişkene atayarak sistem tarihini gösterme: DECLARE v_date DATE : = SYSDATE; BEGIN DBMS_OUTPUT. PUT_LINE('Bugün = ' || v_date); END;

%TYPE ve %ROWTYPE • Eğer bir değişkenin tanımı verilirken, veritabanı tablolarından birindeki bir niteliğin veri türünde olması isteniyorsa %TYPE kullanılır: değişken_adı tablo_adı. sütun_adı%TYPE; last_name emp. ename%TYPE; • Eğer bir nitelik değil de bir tablo yada cursor’daki bir kayıt söz konusu ise %ROWTYPE kullanılır: dept_rec dept%ROWTYPE; • Kaydın alanlarına ulaşmak için nokta notasyonu kullanılır: my_deptno : = dept_rec. deptno; %TYPE hakkında bilgi için: Section 2, Lesson 4, Using Scalar Data Types %ROWTYPE hakkında bilgi için: Section 5, Lesson 2 & Section 6, Lesson 1

IF-THEN-ELSIF-ELSE • Koşula göre işlem yapmak için IF-THEN-ELSE kullanılabilir: NOT: "ELSE IF" yazımı ELSIF şeklindedir. Detaylı bilgi için: Section 4, Lesson 1, Conditional Control: IF Statements

Örnek DECLARE qty_on_hand NUMBER(5); Değişkene SELECT sorgusu ile BEGIN değer atama INTO ile yapılır SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity; Tek satır açıklamalarda --, çok IF qty_on_hand > 0 THEN -- check quantity satır açıklamalarda ise UPDATE inventory SET quantity = quantity - 1 /* ve */ kullanılır WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; NOT: Normalde SELECT ile yapılan sorgular veriyi kilitlemez iken, COMMIT; sorguda kullanılan FOR UPDATE OF quantity; satırı sayesinde sorgu END; sonrasında quantity alanı kilitlenir. UPDATE + COMMIT sonrasında kilit kaldırılır. (bak: SQL Fundamentals I – Les 09 – Slayt 43 -44)

CASE WHEN • Bir değişkene atanacak değeri belirli bir koşula göre belirlemek için CASE WHEN kullanılabilir: Detaylı bilgi için: Section 4, Lesson 2, Conditional Control: Case Statements

FOR Döngüsü • Tüm programlama dillerinde en çok kullanılan döngü ifadesi olan For döngüsünün, PL/SQL’de kullanımı aşağıdaki gibidir: FOR değer IN baslangiç. . bitiş LOOP … işlemler … END LOOP; • Örnek: FOR num IN 1. . 500 LOOP INSERT INTO roots VALUES (num, SQRT(num)); END LOOP; Detaylı bilgi için: Section 4, Lesson 4, Iterative Control: WHILE and FOR Loops

WHILE Döngüsü WHILE koşul LOOP … işlemler … [EXIT WHEN koşul] END LOOP; • EXIT-WHEN ifadesi FOR döngüsünde de kullanılabilir. • İstenirse FOR veya WHILE yazılmadan, düz bir LOOP … END LOOP döngüsü yaratılabilir. Bu döngüden çıkmak için EXIT-WHEN kullanımı şarttır. Detaylı bilgi için: Section 4, Lesson 4, Iterative Control: WHILE and FOR Loops

Cursor • Bir sorgu sonucunda birçok kayıt elde edilmişse, bu kayıtları sıra ile işlemek için Cursor yapısı kullanılır. • Programlama dillerinde kullanılan recordset (resultset) yapısına benzerdir. DECLARE CURSOR c 1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20; Detaylı bilgi için: Database Programming with PL/SQL, Section 5

FOR Döngülerinin Cursor ile kullanımı DECLARE CURSOR c 1 IS SELECT ename, sal, deptno FROM emp; . . . BEGIN FOR emp_rec IN c 1 LOOP. . . sal_total : = sal_total + emp_rec. sal; END LOOP; END;

Hata İşleme DECLARE. . . comm_missing EXCEPTION; --declare exception BEGIN. . . IF commission IS NULL THEN RAISE comm_missing; --raise exception END IF; bonus : = (salary * 0. 10) + (commission * 0. 15); EXCEPTION WHEN comm_missing THEN. . . --process the exception END; Detaylı bilgi için: Database Programming with PL/SQL, Section 7

Örnek DECLARE e_invalid_department EXCEPTION; v_name VARCHAR 2(20): ='Accounting'; v_deptno NUMBER : = 27; BEGIN UPDATE departments SET department_name = v_name WHERE department_id = v_deptno; IF SQL%NOTFOUND THEN RAISE e_invalid_department; END IF; COMMIT; EXCEPTION WHEN e_invalid_department THEN DBMS_OUTPUT. PUT_LINE('No such department id. '); ROLLBACK; END;

Genel Hataların İşlenmesi DECLARE v_country_name VARCHAR 2(40); Bu kısım silinirse tüm ülkeler seçileceği için v_region_id NUMBER; değişkenlere atanacak BEGIN değerlere karar verilemez SELECT country_name, region_id ve EXCEPTION’a gidilir. INTO v_country_name, v_region_id FROM countries WHERE country_id='CA'; DBMS_OUTPUT. PUT_LINE ('The country name is: ' ||v_country_name||' and is located in ' ||v_region_id||'. ') ; EXCEPTION veri tipi ile tanımlanmamış ve EXCEPTION RAISE ile çağrılmamış genel hatalar için tanımlanan sabitlerden biridir. WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT. PUT_LINE ('Your select statement retrieved multiple rows. Consider using a cursor. '); END; Bak: Predefined Oracle Server Errors Database Programming with PL/SQL, Section 7, Slayt 11 -14

Örnek DECLARE salary emp. sal%TYPE : = 0; mgr_num emp. mgr%TYPE; last_name emp. ename%TYPE; starting_empno empno%TYPE : = 7499; BEGIN SELECT mgr INTO mgr_num FROM emp WHERE empno = starting_empno; WHILE salary <= 2500 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, salary, last_name); COMMIT; genel hatalar için tanımlanan sabitlerden bir başkası EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES (NULL, 'Not found'); COMMIT; END;

Stored Procedure (Saklı Yordam) • Stored Procedure’ler veritabanında saklanan ve gerektiğinde ismi ile çağrılabilen PL/SQL bloklarıdır. • Değer döndüren prosedürlere fonksiyon (function) denir. • Önceden derlenmiş olduğu için, normal kullandığımız bir SQL sorgusunun tekrar çalıştırılmasına nazaran daha fazla performans elde etmemizi sağlarlar. • Server üzerinde tutulduğundan, yükü istemci tarafına değil de, sunucu tarafına yükleyerek programımızın performansını arttırır. (Elbette ki bu bir seçimdir. Sunucunuz güçlüyse bu seçimi tercih edebilirsiniz. ) Prosedürler hakkında detaylı bilgi için: Database Programming with PL/SQL, Section 8

Stored Procedure (Devam …) • Bir kez yazılıp, tekrar kullanıldığı için modüler bir yapıda program geliştirmiş oluruz. • Aynı PL/SQL ifadesini birden fazla yerde kullanacağımız zaman, bunu bir saklı yordam haline getirerek, kullanımını sadece ismini çağırma ile gerçekleştirebiliriz. • Belirli girdi ve çıktı parametreleri olduğu için, saklı yordamların kullanımı ile güvenlik açısından kendimizi sağlama almış oluruz. • Ağ trafiğini azaltır. İstemci tarafından birçok satıra sahip SQL komutunun sunucuya gitmesindense, sadece saklı yordamın adının sunucuya gitmesi ağı daha az meşgul etmiş olur.

Parametresiz Stored Procedure CREATE PROCEDURE print_date IS v_date VARCHAR 2(30); IS yerine AS de yazılabilir BEGIN SELECT TO_CHAR(SYSDATE, 'Mon DD, YYYY') INTO v_date Tarihi karaktere dönüştüren TO_CHAR fonksiyonunun ikinci parametresi formatı belirler. FROM DUAL; DBMS_OUTPUT. PUT_LINE(v_date); END; Built-in ORACLE fonksiyonları hakkında detaylı bilgi için: Section 2, Lesson 5, Writing PL/SQL Executable Statements

Parametreli Stored Procedure CREATE PROCEDURE para_yatirma (hes_no IN NUMBER, miktar IN NUMBER) AS BEGIN UPDATE hesaplar SET bakiye = bakiye + miktar WHERE hesap_no = hes_no; EXEC para_yatirma(123, 1000); END; Prosedürler SQL Plus veya SQL Developer altında EXEC (veya EXECUTE) ile çağrılabilir. Fakat Web tabanlı «Oracle Application Express» altında begin/end bloğu içinde yazılarak çağrılırlar. BEGIN para_yatirma(123, 1000); END;

Örnek CREATE PROCEDURE award_bonus (emp_id NUMBER) IS bonus NUMBER(10, 2); comm_missing EXCEPTION; BEGIN SELECT comm * 0. 15 INTO bonus FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE comm_missing; ELSE UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; END IF; EXCEPTION WHEN comm_missing THEN DBMS_OUTPUT. PUT_LINE('HATA: BONUS hesaplanamadı'); END award_bonus;

Fonksiyon • Birçok programlama dilinde olduğu gibi PL/SQL’de de dile özgün hazır fonksiyonlar haricinde, kullanıcıya fonksiyon tanımlama olanağı sağlanır. • Fonksiyonlar prosedürlerden farklı olarak RETURN komutu ile çağrıldıkları yere değer döndürürler. • Döndüreceği veritipi ve değişken ismi BEGIN öncesinde tanımlanır. • Geriye değer döndürmeyen prosedürler EXECUTE komutu ile çağrılırken, fonksiyonlar bir sorgu veya bir PL/SQL bloğu içinden çağrılabilir.

Parametre olarak aldığı string'teki karakter sayısını döndüren fonksiyon CREATE FUNCTION num_characters (p_string IN VARCHAR 2) RETURN INTEGER IS num_chars INTEGER; BEGIN SELECT LENGTH(p_string) INTO num_chars FROM DUAL; RETURN num_chars; END; Fonksiyonlar hakkında detaylı bilgi için: Database Programming with PL/SQL, Section 9