Veri Tabanı Yönetim Sistemleri 2
Ders 5
PL/SQL'e Giriş
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.
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, Using Explicit Cursor Attributes
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;
COMMIT;
END;
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 c1 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 c1 IS
SELECT ename, sal, deptno FROM emp;
...
BEGIN
FOR emp_rec IN c1 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
Genel Hataların İşlenmesi
DECLARE
v_country_name VARCHAR2(40);
v_region_id
NUMBER;
BEGIN
SELECT country_name, region_id
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ış bazı genel hatalar
için sabitler de vardır.
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('Your select statement retrieved
multiple rows. Consider using a cursor.');
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 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 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 VARCHAR2(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;
END;
Parametre olarak aldığı string'teki
karakter sayısını döndüren fonksiyon
CREATE FUNCTION num_characters
(p_string IN VARCHAR2)
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
Package (Paket)
• Birbiriyle ilişkili olan prosedürlerin, fonksiyonların,
değişkenlerin ve diğer yapıların bir bütün haline
getirildiği ve veritabanında saklandığı yapıdır.
• Bir global değişkenin tanımlanıp paket içindeki
herhangi bir prosedürde çağrılabilmesi gibi ekstra
özellikler sağlar.
• Ayrıca paketler bir bütün halinde bir kerede parse
edilip, derlenip, belleğe yüklendiği için performans
artışı da sağlar.
• Paketlerde genellikle tanımlama (specification) ve
gövde (body) olmak üzere iki kısım bulunur.
Paketler hakkında detaylı bilgi için:
Database Programming with PL/SQL, Section 10
Package Örneği
CREATE PACKAGE emp_actions AS -- package specification
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Trigger (Tetikleyici)
• Bir veri tabanında belirli bir tabloda değişiklik
olduğunda otomatik olarak bir işlemi başlatan bir dizi
SQL ifadesidir.
• Bir trigger bir olaydan (insert, delete ya da update
ifadelerin belirtilen tabloda oluşması) ve bir
hareketten (ilgili prosedür) oluşur.
• Örneğin; personel tablosundan bir kaydın silinmesi
olayı gerçekleştiğinde, eski_personel tablosuna
personelin numarasını ve işten ayrılma tarihini
belirtmek için o anki tarihi kaydeden bir prosedür
yazılması ile bir tetikleyici oluşturulabilir.
Trigger Yaratma
CREATE [or REPLACE] TRIGGER TriggerName
[ BEFORE | AFTER ]
[ DELETE | INSERT | UPDATE [of ColumnName ] ]
ON [User.]TableName
[ FOR EACH ROW ] [ WHEN Condition ]
BEGIN
[PL/SQL Block]
END ;
• BEFORE / AFTER: DML ifadesinden önce / sonra tetikle
• FOR EACH ROW: Etkilenen her satır için tekrar tetikle
• WHEN: Tetikleyiciye koşul ekler
Detaylı bilgi için:
Database Programming with PL/SQL, Section 13
Trigger Örneği
Örnek 1
DECLARE
salary
emp.sal%TYPE := 0;
mgr_num
emp.mgr%TYPE;
last_name
emp.ename%TYPE;
starting_empno emp.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;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO temp VALUES (NULL, NULL, 'Not found');
COMMIT;
END;
Örnek 2
PROCEDURE award_bonus (emp_id NUMBER) IS
bonus
REAL;
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...
END award_bonus;
Download

Ders 5 - PL-SQL`e Giriş - Altan MESUT