Bu bölümde SQL kodları ile oluşturulan veritabanı nesneleri olan ve daha sonra çalıştırılmak üzere sunucuya kaydedilen stored programları ve view'ları incelemeye çalışacağız.
Stored programlar aşağıdaki nesneleri içerebilir:
View'lar kullanıldıklarında bir sonuç seti oluşturan depolanmış sorgulamalardır. Bir view sanal bir tablo gibi davranır.
Her stored program SQL komutları içeren bir yapıdır. Bu yapıda noktalı virgül (;) işareti ile ayrılan birden fazla SQL komutundan oluşabilir. Aşağıdaki satırlar stored bir procedure oluşturur:
CREATE PROCEDURE tekrarla (id1 INT) BEGIN SET @id2 = 0; REPEAT SET @id2 = @id2 + 1; UNTIL @id2 > id1 END REPEAT; END;
Yukarıdaki procedure BEGIN ... END bloğu içinde yer alan bir SET komutundan ve bir döngüden oluşur.
Eğer içinde noktalı virgül işareti (;) bulunan stored program tanımlamak için mysql programını kullanırsanız, mysql programı ; işaretini komut sonu belirleyicisi olarak kullandığından, mysql programının stored programını tek bir komut olarak sunucuya aktarmasını sağlamak için, komut sonu belirleyicisini geçici olarak değiştirmeniz gerekir.
mysql komut sonu belirleyicisini yeniden tanımlamak için, delimiter komutunu kullanabilirsiniz. Komut sonu belirleyicisi // olarak değiştirildiğinde tüm procedure içeriği sunucuya tek bir komut olarak gönderilir ve procedure kullanılmadan önce tekrar ; işareti devreye sokulur. Bu sayede ; işareti mysql programı tarafından işlem görmez, sunucuya aktarılarak procedure kod bloğu içinde kullanılır.
mysql> delimiter // mysql> CREATE PROCEDURE tekrarla (id1 INT) -> BEGIN -> SET @id2 = 0; -> REPEAT SET @id2 = @id2 + 1; UNTIL @id2 > id1 END REPEAT; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL tekrarla(100); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x; +------+ | @x | +------+ | 101 | +------+ 1 row in set (0.00 sec)
Komut sonu belirleyicisini bir veya daha fazla karakterden oluşan bir karakter dizisi olarak tanımlayabilirsiniz. "\" karakteri MySQL tarafından escape karakteri olarak işlem gördüğünden kullanmayın.
Aşağıdaki kodlar bir fonksiyon tanımlanmasını ve kullanılmasını göstermektedir:
mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
Stored rutinler (procedure ve fonksiyon) sunucuya yüklenebilen SQL komutlar bloğudur ve MySQL 5.6 sürümünde eklenmiştir. Sunucuya yükleme işlemi yapıldıktan sonra, rutin içinde yer alan SQL komutlarını tek tek kullanmak yerine sadece rutin adı çağrılarak tüm komutlar gerçekleştirilir.
Stored rutinlerin kullanılması mysql veritabanında proc tablosu bulunmalıdır. Bu tablo MySQL 5.6 kurulumu esnasında oluşturulur.
Stored rutin'ler belirli durumlarda özellikle fayda sağlar:
* Farklı dillerde yazılmış veya farklı platformlarda çalışan birden fazla uygulamanın aynı veritabanı işlemlerini yapması gerektiğinde.
* Güvenliğin çok önemli olduğu durumlarda. Stored rutinlerin kullanılması her işlemin uygun bir şekilde yapıldığı istikrarlı ve güvenilir bir ortam sağlar. Bu tür bir kurulumda, program ve kullanıcıların veritabanında yer alan tablolara direk olarak erişim sağlamak yerine sadece stored rutinleri çalıştırabilirler.
Stored rutinler sunucu ve kullanıcı arasında daha az bilgi iletişimi sağladığından performansı artırır. Bu durumda, sunucuda daha fazla iş yapılacağından, veritabanı sunucusunun yükünü artırır ve program içinde ise daha az işlem yapılır.
Stored rutinler kullanıcının veritabanı sunucusunda fonksiyon kütüphaneleri bulundurmasını sağlar.
Stored rutin yazımı
Stored rutin, procedure veya fonksiyon olabilir. Stored rutinler CREATE PROCEDURE ve CREATE FUNCTION komutları ile oluşturulur. Procedure CALL komutu ile çalıştırılır ve sadece değişkenler kullanarak geri değer verir. Fonksiyon ise bir komut içinden adı kullanılarak çalıştırılır ve normal bir değer geri verir.
Stored rutinşer DROP PROCEDURE ve DROP FUNCTION komutları ile silinir ve ALTER PROCEDURE ve ALTER FUNCTION komutları ile değiştirilir.
Stored procedure veya fonksiyon bir veritabanı ile ilişkilendirilmelidir. Bu durumda:
Bir rutin çalıştırıldığında, USE db_adi komutu uygulanır ve sona erdiğinde ise veritabanı kullanımı sona erer. Stored rutinlerde USE komutu yer almaz.
Rutin adlarını veritabanı adı ile birlikte tanımlayarak aktif veritabanında olmayan rutinleri kullanabilirsiniz. db_adi.procedure_adi() veya db_adi.fonksiyon_adi().
Veritabanı silindiğnde, ilgili tüm stored rutinlerde silinir.
Stored fonksiyonlar kendi kendini çağıramaz.
Stored procedure'ler kendi kendini çağırabilir, ancak ön tanımlı olarak devre dışı bırakılmıştır. Özelliği etkinleştirmek için, max_sp_recursion_depth sunucu sistem değişkenine 0'dan büyük bir değer verilmesi gerekir.
Stored Rutin ve MySQL Ayrıcalıkları
MySQL stored rutinler ile ilgili ayrıcalıkları aşağıdaki şekilde belirler:
Stored rutinler oluşturmak için CREATE ROUTINE ayrıcalığı gerekir.
Stored rutinleri silmek veya değiştirmek için ALTER ROUTINE ayrıcalığı gerekir. Eğer gerekirse, bu ayrıcalık rutini oluşturana otomatik olarak verilir, rutin silindiğinde devre dışı kalır.
Stored rutinleri çalıştırmak için EXECUTE ayrıcalığı gerekir. Eğer gerekirse, bu ayrıcalık rutini oluşturana otomatik olarak verilir, rutin silindiğinde devre dışı kalır. Aynı zamanda, bir rutin için ön tanımlı SQL SECURITY özelliği olan DEFINER, rutinin ilişkili olduğu veritabanına giriş yetkisi olan kullanıcıların rutini çalıştırmasını sağlar.
Eğer automatic_sp_privileges sistem değişkeni 0 ise, EXECUTE ve ALTER ROUTINE ayrıcalıkları otomatik olarak verilmez ve rutin oluşturucusundan bu ayrıcalık alınır.
Bir rutin oluşturucusu, rutin için kullanılan CREATE komutunun oluşturulduğu hesaptır. Bu hesap, rutin tanımlamasında DEFINER olarak tanımlanan hesap ile aynı olmayabilir.
Stored rutin verileri
Stored rutinler ile ilgili veriler aşağıdaki şekilde elde edilebilir:
1. INFORMATION_SCHEMA veritabanının ROUTINES tablosunu sorgulayarak.
2. Rutin tanımlamalarını görmek için SHOW CREATE PROCEDURE ve SHOW CREATE FUNCTION komutlarını kullanarak.
3. Rutin özelliklerini görmek için SHOW PROCEDURE STATUS ve SHOW FUNCTION STATUS komutlarını kullanarak.
Stored Procedure, fonksiyon, trigger ve LAST_INSERT_ID()
Stored rutin (procedure veya fonksiyon) veya trigger kod bloğu içinde, LAST_INSERT_ID() değeri değiştirildiğinde:
Eğer stored procedure içindeki komutlarla LAST_INSERT_ID() değeri değiştirilirse, değiştirilen değer procedure çağrısından sonra kullanılan komutlar tarafından da aynı şekilde değerlendirilir.
Stored fonksiyon ve trigger kodları ile değiştirilen değer ise fonksiyon veya trigger sona erdiğinde eski haline döner.
Bir tablo için oluşturulmuş bir veritabanı nesnesi olup, tablo için yeni kayıt ekleme, kayıt değişikliği ve benzeri işlemler yapıldığında otomatik olarak çalıştırılır. Trigger'ları bir tabloya eklenen değerlerin kontrolünü yapmak veya güncellemede kullanılan tablo değerlerini hesaplamak için kullanabilirsiniz.
Trigger, ilgili olduğu tablo için INSERT, DELETE veya UPDATE komutları kullanıldığında çalıştırılmak üzere tanımlanır. Trigger belirtilen komutlardan önce veya sonra çalıştırılabilecek şekilde ayarlanabilir.
MySQL'de trigger'lar sadece SQL komutları tarafından çalıştırılabilir. Trigger'lar view'larda yapılan değişiklikler ve tablolara SQL komutlarını MySQL sunucuya aktarmayan API'ler tarafından yapılan değişiklikler tarafından çalıştırılamazlar. Bu nedenle, Trigger'lar INFORMATION_SCHEMA veya performance_schema tables üzerinde yapılan değişiklikler tarafından çalıştırılamazlar, çünkü bu tablolar aslında bir view'dur
Trigger yazımı
Trigger oluşturmak veya silmek için, CREATE TRIGGER veya DROP TRIGGER komutunu kullanılır.
Aşağıdaki örnek, INSERT komutları ile çalıştırılmak üzere bir tablo için bir trigger oluşturur. Trigger, tablonun bir sütununda yer alan değerlerin toplamını alır.
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec)