PL/SQL

Yazar Hanzala

Sunday, 26 December 2004

PL/SQL

Oracle ürünleri başlıklı kısımda genel bilgi olarak anlattığımız PL/SQL’in burada ayrıntılarına ineceğiz.

PL/SQL Bloklarının Yapısı

PL/SQL blok yapılı bir dildir. Her bir blok bir program ünitesini oluşturur. Pl/SQL blokları prosedür, fonksiyon ve normal blok olarak üçe ayrılır. Prosedür ve fonksiyon yapısı ileride ele ele alınmıştır. Şimdi burada normal bir blok yapısı işlenecektir. Bir PL/SQL bloğu seçimlik bir tanımlama bölümü, PL/SQL cümlelerinin yazıldığı bir bölüm ve hata yakalama bölümünden oluşur. Bloklara isim vermek zorunlu değildir. Eğer hazırlanan bir blok yeniden kullanılmak istiyorsa .sql uzantılı dosyalara saklanıp yeniden kullanılabilir.

* “[“ ve “]” işaretleri arasındaki alanların yazılması zorunlu değildir.

[<blok başlığı>]

[DECLARE

<değişkenler>

<imleçler>

<kullanıcı tanımlı hata yakalama isimleri>]

BEGIN

[ EXCEPTION

<hata durumu komutları>]

END;

Blok başlığı PL/SQL bloğunun prosedür, fonksiyon veya bir paket bloğu olup olmadığını belirler. Eğer bir başlık tanımlanmazsa bu isimsiz blok(anonymous) olarak adlandırılır. “Declare” kısmı diğer programlama dillerinde olduğu gibi değişken ve sabitlerin tanımlandığı kısımdır.

PL/SQL blokları içerisinde kullanılan tüm sabitler, değişkenler, imleçler ve kullanıcı tanımlı hata durumları “declare” kısmında tanımlanmalıdır. Burada sabit ve değişkenler şöyle tanımlanabilir:

<değişken adı> [constant] <veri tipi> [not null] [:= <ilk değer>];

SQL’de kullanılan tüm veri tipleri(SQL bölümünde anlatılmıştı) ve Boolean veri tipi burada kullanılabilir. Boolean tipte bir değişken “true”, “false” ve “null” değerlerini alabilir. “not null” yan cümlesi değişkenin mutlaka bir değer alması gerektiğini bildirir. Burada tanımlanan değişkenlere “:=” operatörü ile ilk değer atanabilir. Eğer bir ilk değer atanmazsa değişkenlerin alacağı ilk değer “null” ‘dur. Eğer “constant” ile tanımlama yapılırsa değişkenin değeri değiştirilemez.

DECLARE

isbastar date; /* ilk değeri “null” */

isi varchar2(80) := 'tezgahtar';

isci_bulundu boolean; /* ilk değeri “null” */

maas_artisi constant number(3,2) := 1.5; /* sabit */

BEGIN ... END;

Tanımlama kısmında değişkenlere bir veri tipi vermek yerine bir tablodaki bir alanın veri tipi değişkene aktarılabilir. Örneğin “isci.iscino%TYPE” şeklinde bir tanımlama ilgili değişkenin “isci” tablosundaki “iscino” değişkeni ile aynı veri tipinde olmasını sağlar. Tek bir değişken için tablonun bir alanının veri tipini almak yerine tablonun tüm alanlarının veri tipleri bir değişkene aktarılabilir. Örneğin “isci%ROWTYPE” ile istenen değişkene tablonun yapısı aynen aktarılabilir. Buna tıpkı Pascal’daki gibi “record” veri tipi denir. Burada eğer “tablo isci%ROWTYPE” şeklinde bir tanımlama yapılırsa tablo.iscino ya da tablo.isciadi şeklinde değişkenler kullanılabilir. Bu tür bir tanımlama imleç kullanırken kolaylık sağlar.

PL/SQL blokları içerisinde gerçekleştirilecek işlemler begin..end kelimeleri arasına yazılır. Burada dikkat edilmesi gereken bir husus vardır: PL/SQL blokları içerisinde veri tanımlama dili komutları(yani Create Table, Alter TableSpace, Drop User gibi) ve veri kontrol dili komutları(grant ve revoke gibi) kullanılamaz. Pl/SQL blokları içerisinde veri işleme dili komutları kullanılabilir. (Select, Update, Delete, Insert gibi) .

Hata durumları ya da aykırı durumlar olarak adlandırılan “exceptions” kısmında PL/SQL blokları arasında gerçekleşen bazı hataları kullanıcıya yansıtmadan kontrol etme ve gerekli işlemleri yapma olanağı vardır. Oracle tarafından tanımlanmış hata durumları olduğu gibi programcılar da hata durumları tanımlayabilirler.

PL/SQL Akış Kontrolü

Pl/SQL blokları içerisinde kosullu-koşulsuz dallanmalar ve döngüler kullanılabilir. Bu amaçla kullanılan yapıları burada inceleyeceğiz. PL/SQL’de iki tip kontrol yapısı vardır. Brincisi “IF” kontrol yapısı ve diğer “LOOP “ kontrol yapısı.

IF Kontrol Yapısı

PL/SQL içerisinde üç tip “IF” yapısı kullanılır:

1-) IF şart THEN 2-)IF şart THEN 3-)IF şart THEN

Komutlar komutlar komutlar

END IF ELSE ELSEIF şart

Şartın gerçekleşmemesi komutlar

halindeki komutlar ENDIF

END IF

LOOP Kontrol Yapısı

PL/SQL içerisinde temel LOOP, FOR ve WHILE döngüleri vardır.

LOOP

Komut1

.....

KomutN

GOTO etiket adı

EXIT [WHEN şart]

END LOOP

FOR sayac IN [REVERSE] başlangıç..bitiş

LOOP

Komut1

...

KomutN

EXIT [WHEN şart]

END LOOP

WHILE şart LOOP

Komut1

...

KomutN

END LOOP

İmleçler

Birden fazla kaydın hafızaya getirilme işlemlerine imleç(cursor) açma denir. İmleç açma, özellikle veritabanındaki tablolardan kayıtların teker teker getirilmesinde faydalı olmaktadır. Kayıtlar teker teker getirilerek üzerinde işlemler yapılıp tekrar veritabanına kaydedilebilmektedir. Oracle, hafızada bu tip işlemleri yapabilmek için yer ayırmaktadır. İki çeşit imleç vardır:

Kapalı İmleçler(Implicit Cursors): Yazılan her SELECT, INSERT, UPDATE ve DELETE komutları için veritabanı tarafından otomatik olarak açılan imleçlerdir. Yazılan her SQL için, SQL’in yazım kontrollerini yapmak ve SQL'i çalıştırmak için hafızadan bir yer ayrılır. Bu ayrılan yer için standart olarak tanımlanan imlece kapalı imleç denir.PL/SQL blokları arasında yazılan SQL komutları için, tanımlanan kapalı imlece ait özellikler geçerli olmaktadır. Yazılan her SQL için tanımlanan kapalı imleçlerin şu özellikleri kullanıma açıktır:

SQL%ISOPEN : SQL sonucu eğer imleç açık ise “true” değeri, kapalı ise “false” değeri döndürür.

SQL%ROWCOUNT : SQL cümlesi tarafında işlem gören kayıt sayısnı görüntüler.

SQL%FOUND : SQL sonucu en az bir kayıt işlem görmüşse “true”, hiç kayıt işlem görmemişse “false” döndürür.

SQL%NOTFOUND : SQL sonucu eğer hiçbir kayıt işlem görmemişse “true”, en az bir kayıt işlem görmüşse “false” değeri dödürür.

DECLARE

Silinen_kayit_sayisi number(5);

BEGIN

delete from isci

where bolum=10;

silinen_kayit_sayisi:=SQL%ROWCOUNT;

END;

DECLARE

delete from isci

where bolum=10;

if SQL%FOUND then

commit;

else

rollback;

END;

Açık İmleçler(Explicit Cursor): Kullanıcı tarafından belirli bir işi yapabilmek için açılan imleçlerdir. Özellikle fazla sayıda kayıtların bulunduğu tablolarda, silme, güncelleme ve benzer işlemlerde çok kullanışlı program parçalarıdır.

DECLARE

CURSOR <imleç adı> [(<parametre listesi>)] IS

SQL cümlesi

Kayıt_tipi_değişkeni <imleç adı>%ROWTYPE

Değişken_1 NUMBER;

Değişken_2 NUMBER;

BEGIN

OPEN <imleç adı>

LOOP

FETCH <imleç adı> INTO kayıt_tipdeğişkeni;

EXIT WHEN <imleç adı>%NOTFOUND;

........

komutlar;

END LOOP;

CLOSE <imleç adı>;

END;

Burada imleç adı PL/SQL blokları içerisinde başka bir değişken adı olarak kullanılıyor olmamalıdır. Parametreler “<parametre adı> <veri tipi>” şeklinde bildirilmelidir. PL/SQL içerisinde kullanılan tüm veri tipleri parametreler için kullanılabilir.(char, varchar2, number, date, boolean ya da number veri tipinin integer, real gibi alt veri tipleri). Aşağıda farklı şekillerde açılmış imleç örnekleri verilmiştir:

CURSOR c1 IS SELECT iscino, isciadi, isi, ucret FROM isci WHERE ucret > 2000;

CURSOR c2 RETURN bolum%ROWTYPE IS SELECT * FROM bolum WHERE bolumno = 10;

CURSOR c3 (verilen_tarih DATE) IS

SELECT iscino, ucret FROM isci WHERE isbastar > verilen_tarih;

Eğer bir imleç açıldığında kayıtlar üzerinde değişiklik yapılacaksa, imleç tanımından sonra “for update[(<sütun(lar)>)]” şeklinde tanımlama yapılmalıdır. Böyle bir tanımlama yapıldığında imleç içerisindeki kayıtlar kilitlenir ve “commit” komutu uygulanana kadar diğer kullanıcılar bu kayıtlara erişemez.

Hata Durumları

Hata durumları normalin haricindeki durumlarda nelerin yapılacağının tanımlandığı bölümdür. Belirtilen özel durum oluştuğunda Oracle, PL/SQL komutlarını çalıştırmaya devam etmemekte, onun yerine o özel durumda yapılması tanımlanan işlemleri yapmaya çalışmaktadır. Hata durumları iki türlüdür: Oracle tarafından önceden tanımlanmış hata durumları ve kullanıcı tanımlı hata durumları.

EXCEPTION

WHEN <önceden tanımlanmış hata durumu> THEN

Komutlar

Oracle tarafından tanımlı çok kullanılan hata durumları:

DUP_VAL_ON_INDEX	Tekil olması gereken bir alana bu durumu ihlal eden bir kayıt eklenmeye çalışıldığında ortaya çıkar.
INVLAID_CURSOR	“OPEN” komutu ile açılmamış bir imleç ile işlem yapılmaya çalışıldığında ortaya çıkar.
INVALID_NUMBER	Değişkenin tanımından daha büyük bir sayı değişkene atanmak istendiğinde ortaya çıkar.
NO_DATA_FOUND	SQL sonucu kayıt dönmediği zaman ortaya çıkar.
ZERO_DIVIDE	Sıfıra bölme işleminde ortaya çıkan durumdur.
TOO_MANY_ROWS	Bir kayıt dönmesi gereken SQL ‘den birden fazla kayıt döndüğünde ortaya çıkan durumdur.
VALUE_ERROR	Numerik veya karakter tipli bir değişkenin diğerinin yerine kullanılmaya çalışıldığı durumdur.
CURSOR_ALREADY_OPEN	Açık bir imlecin tekrar açılmaya çalışıldığı durumdur.
LOGIN_DENIED	Veritabanına yanlı kullanıcı adı ve şifre ile bağlanılmaya çalışıldığı durumdur.
NOT_LOGGED_ON	Veritabanına bağlanmadan SQL cümlesi çalıştırıldığında ortaya çıkan durumdur.
OTHERS	EXCEPTION bölümünde yazılan hata durumlarından hiç biri oluşan hatayla eşleşmediğinde bu durum işleme girer.

Kullanıcıların tanımladığı hata durumları da önceden tanımlı hata durumları ile benzerdir. Farklı olarak kullanıcı bir prosedür çağırıyormuş gibi bu hata durumlarını da çağırmalıdır. Çağırmak için “RAISE” komutu kullanılır.

DECLARE

......

BEGIN

......

IF ucret<2000 THEN

RAISE dusuk_maas;

END IF;

......

EXCEPTION

WHEN dusuk_maas THEN

Message(‘Düşük maaşlı işçi’);

END;