MariaDB Stored Procedure ve Function: CREATE PROCEDURE, CALL, Parametreler ve Hata Yakalama

Stored procedure ile stored function farkı

Belgelere göre stored procedure'lerin önemli bir kullanım nedeni güvenliktir: bankalar uygulamaların ve kullanıcıların tablolara doğrudan erişmemesi için işlemleri yordamlara koyar. Aynı işlemin farklı dillerde yazılmış birden çok istemci tarafından yapıldığı ortamlarda da mantığın tek yerde durması işe yarar. Stored function ise hesaplama mantığını, örneğin vergi veya indirim hesabını, sorgu içinde yeniden kullanılabilir hale getirir.

DELIMITER: komut satırında rutin yazmanın ön şartı

mariadb istemcisinin varsayılan ifade ayırıcısı noktalı virgüldür. BEGIN ... END bloğu içeren bir rutini satır satır yazdığınızda istemci gövdedeki ilk noktalı virgülü ifadenin sonu sanar ve yarım kalmış tanımı sunucuya gönderir. Belgelerdeki örnekte bu durum 1064 numaralı sözdizimi hatasıyla sonuçlanır. Çözüm, tanımdan önce ayırıcıyı geçici olarak // gibi başka bir karaktere çevirmek, tanımdan sonra geri almaktır.

DELIMITER //

CREATE PROCEDURE ornek()
BEGIN
  SELECT 1;
  SELECT 2;
END //

DELIMITER ;

DELIMITER bir SQL ifadesi değil, istemci komutudur; sunucuya gönderilmez. Yalnızca istemcinin yazdığınız metni ifadelere nasıl böldüğünü değiştirir; bu yüzden tanımı tek bir ifade olarak gönderen araçlarda gerekmeyebilir. İstemcinin diğer komutları için mariadb komut satırı istemcisi yazısına bakın.

CREATE PROCEDURE ve parametreler

CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ OUT | INOUT | IN OUT] param_name type |
    [ IN ] param_name type [DEFAULT value or expression]

Parametre listesinin parantezleri her zaman yazılmalıdır; parametre yoksa boş () kullanılır. Parametre adları büyük-küçük harf duyarsızdır. Her parametre varsayılan olarak IN'dir: değeri yordama taşır, yordam içinde değiştirilse bile çağırana yansımaz. OUT parametresi yordam içinde NULL değerle başlar ve yordam bittiğinde değeri çağırana döner. INOUT parametresini çağıran başlatır, yordam değiştirebilir ve değişiklik çağırana görünür. OUT ve INOUT için CALL ifadesinde bir kullanıcı değişkeni (@a) verilir. IN OUT yazımı belgelere göre yalnızca Oracle modunda çalışır.

DELIMITER //

CREATE PROCEDURE simpleproc (OUT param1 INT)
 BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
 END;
//

DELIMITER ;

CALL simpleproc(@a);

SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+

MariaDB 11.8'den itibaren IN parametrelerine varsayılan bir değer veya ifade tanımlanabilir. Belgeler bunun, kullanımdaki bir yordama yeni parametre eklemek gerektiğinde işe yaradığını belirtir: mevcut çağrılar yeni parametreyi vermeden çalışmaya devam eder. Rutin oluşturulduğunda varsayılan veritabanıyla ilişkilendirilir ve çağrıldığında örtük bir USE yapılır; rutin içinde ayrıca USE yazılamaz.

Rutin adı yerleşik bir SQL fonksiyonuyla aynıysa, tanımlarken ve çağırırken addan sonra bir boşluk bırakmanız gerekir; aksi halde sözdizimi hatası oluşur. Belgeler bu nedenle yerleşik fonksiyon adlarını kendi rutinlerinizde kullanmamayı önerir.

Nitelikler: DETERMINISTIC, veri erişimi, sql_mode

DETERMINISTIC, aynı girdiler için her zaman aynı sonucu üreten fonksiyonları işaretler; varsayılan NOT DETERMINISTIC'tir. Belgelere göre bu nitelik yalnızca fonksiyonlar için anlam taşır, yordamlarda etkisi yoktur. Saklı veriye, sunucu değişkenlerine, rastgele sayılara veya NOW() gibi deterministik olmayan fonksiyonlara bağlı bir fonksiyon deterministik değildir. Deterministik olmayan bir fonksiyonu DETERMINISTIC diye işaretlerseniz yanlış sonuçlar alabilirsiniz; tersini yaparsanız bazı sorgular yavaşlar.

CONTAINS SQL, NO SQL, READS SQL DATA ve MODIFIES SQL DATA yalnızca bilgilendirme amaçlıdır; MariaDB bunların doğruluğunu hiçbir şekilde denetlemez ve hiçbiri yazılmazsa CONTAINS SQL varsayılır. Daha önemli bir ayrıntı sql_mode ile ilgilidir: MariaDB rutinin oluşturulduğu andaki sql_mode değerini saklar ve rutini her zaman o modla çalıştırır, çağrıldığı oturumun modu farklı olsa bile. Karakter seti ve karşılaştırma kuralı belirtilmeyen parametreler de oluşturma anındaki veritabanı varsayılanlarını alır; veritabanı varsayılanı sonradan değişirse rutini silip yeniden oluşturmanız gerekir.

Gövde: değişkenler, koşullar ve döngüler

Birden fazla ifade içeren gövde BEGIN ... END bloğuna yazılır. Yerel değişkenler DECLARE ad tip [DEFAULT değer] ile tanımlanır; DEFAULT yoksa başlangıç değeri NULL'dır. Değişkenler tanımlandıkları blokta ve iç bloklarda geçerlidir ve belgelere göre koşul, imleç (cursor) ve handler tanımlarından önce gelmelidir. Akış kontrolü için IF ... THEN ... ELSEIF ... ELSE ... END IF, CASE, WHILE ... DO ... END WHILE, REPEAT, LOOP ve döngüden çıkmak için LEAVE kullanılır. DECLARE x INT DEFAULT 10; gibi bir tanım, bir sütunun tipine bağlanmak istenirse TYPE OF ile de yazılabilir.

DELIMITER //

CREATE PROCEDURE stok_dusur(IN p_urun INT, IN p_adet INT, OUT p_durum VARCHAR(20))
MODIFIES SQL DATA
BEGIN
  DECLARE v_stok INT DEFAULT 0;

  SELECT stok INTO v_stok FROM urun_stok WHERE urun_id = p_urun;

  IF v_stok >= p_adet THEN
    UPDATE urun_stok SET stok = stok - p_adet WHERE urun_id = p_urun;
    SET p_durum = 'tamam';
  ELSE
    SET p_durum = 'yetersiz';
  END IF;
END //

DELIMITER ;

CALL stok_dusur(7, 2, @durum);
SELECT @durum;

Bu örnekte okuma ile güncelleme arasında başka bir oturum stoku değiştirebilir. Gerçek bir uygulamada okuma ve güncellemeyi bir transaction içine alıp satırı kilitlemek (SELECT ... FOR UPDATE) gerekir. Belgelere göre yordamlar START TRANSACTION, COMMIT ve ROLLBACK içerebilir; ancak rutin içinde BEGIN yazmak transaction başlatmaz, bir BEGIN ... END bloğu açar. Ayrıntılar için transaction ve izolasyon seviyeleri yazısına bakın.

Hata yakalama: DECLARE HANDLER ve SIGNAL

Rutin içinde bir hata oluştuğunda ne yapılacağını DECLARE ... HANDLER belirler. CONTINUE handler'dan sonra rutin kaldığı yerden devam eder; EXIT handler'dan sonra handler'ın tanımlandığı BEGIN ... END bloğu sonlanır. Handler'ı olmayan bir koşul oluşursa varsayılan davranış EXIT'tir. Koşul bir MariaDB hata kodu, beş karakterlik bir SQLSTATE, adlandırılmış bir koşul ya da SQLWARNING, NOT FOUND, SQLEXCEPTION sınıflarından biri olabilir. Birden fazla handler aynı hatayı yakalayabiliyorsa hata kodu en yüksek, SQLSTATE ikinci, sınıf en düşük önceliğe sahiptir.

CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));

DELIMITER //

CREATE PROCEDURE handlerdemo ( )
     BEGIN
       DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
       SET @x = 1;
       INSERT INTO test.t VALUES (1);
       SET @x = 2;
       INSERT INTO test.t VALUES (1);
       SET @x = 3;
     END;
     //

DELIMITER ;

CALL handlerdemo( );

SELECT @x;
+------+
| @x   |
+------+
|    3 |
+------+

İkinci INSERT yinelenen anahtar hatası verir, ama CONTINUE handler bu hatayı yakaladığı için yordam son satıra kadar çalışır ve @x 3 olur. Kendi hatanızı üretmek için SIGNAL kullanılır; belgeler özel hatalar için SQLSTATE '45000' değerini önerir ve MESSAGE_TEXT, MYSQL_ERRNO gibi özellikler atanabilir. Yakalanan bir hatayı yeniden fırlatmak için RESIGNAL, ayrıntısını okumak için GET DIAGNOSTICS vardır.

IF p_adet <= 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Adet pozitif olmalıdır';
END IF;

CREATE FUNCTION ile saklı fonksiyon

Saklı fonksiyon RETURNS ile dönüş tipini bildirir ve gövdesinde bir RETURN içermek zorundadır; RETURN çalıştığı anda değer döner ve sonraki ifadeler yok sayılır. Tek satırlık fonksiyonlarda BEGIN ... END gerekmez, bu yüzden DELIMITER da gerekmez:

CREATE FUNCTION hello (s CHAR(20))
    RETURNS CHAR(50) DETERMINISTIC
    RETURN CONCAT('Hello, ',s,'!');

SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+

RETURN ifadesinin döndürdüğü değer bildirilen tipe uymuyorsa ne olacağı, fonksiyon oluşturulurken geçerli olan SQL_MODE'a bağlıdır: katı modda (STRICT_ALL_TABLES veya STRICT_TRANS_TABLES) 1366 hatası oluşur, değilse değer uygun tipe zorlanır. Gövde parantez içinde tek değer döndüren bir SELECT olabilir; birden çok sütun dönerse 1241, birden çok satır dönerse 1242 hatası alınır, bu yüzden LIMIT kullanmak gerekir.

Belgelere göre MariaDB 10.8'den itibaren fonksiyonlar da OUT ve INOUT parametre alabilir, ancak bu fonksiyonlar yalnızca SET ile çağrılabilir; SELECT içinde çağrılırsa 4186 hatası oluşur. Ayrıca Community Server 12.0'dan itibaren Oracle modunda bir fonksiyon SYS_REFCURSOR tipinde imleç de döndürebilir; bu, Oracle'dan taşınan kodlar içindir ve Oracle modu sözdizimi gerektirir.

Saklı fonksiyonların ek kısıtları vardır: sonuç kümesi döndüren ifadeler (düz SELECT) kullanılamaz ama SELECT ... INTO kullanılabilir; açık veya örtük commit ya da rollback yapan ifadeler, FLUSH ve özyineleme yasaktır; fonksiyonu çağıran ifadenin kullandığı bir tabloyu değiştiremez. 12.3'te hazırlanmış ifadeler (PREPARE, EXECUTE) de fonksiyon içinde kullanılamaz; belgeler bunun sınırlı biçimde 13.2.1'de açıldığını belirtir.

İkili günlük, replikasyon ve DETERMINISTIC zorunluluğu

İkili günlük açık, binlog_format değeri STATEMENT ve log_bin_trust_function_creators varsayılan değeri olan OFF ise saklı fonksiyonlar için iki kural devreye girer. Fonksiyon DETERMINISTIC, NO SQL veya READS SQL DATA olarak bildirilmelidir, aksi halde oluşturulurken hata verir. Ayrıca fonksiyonu oluşturmak veya değiştirmek için normal yetkilere ek olarak SUPER yetkisi gerekir. Belgelere göre bu sınırlamalar yordamlara ve event'lere uygulanmaz; satır tabanlı günlükte ise birincil sunucu ile replikanın ayrışma riski yoktur. Günlük biçimleri için MariaDB binary log yazısına bakın.

Yetkiler ve güvenlik bağlamı

Rutin oluşturmak için CREATE ROUTINE, değiştirmek veya silmek için ALTER ROUTINE, çalıştırmak için EXECUTE yetkisi gerekir. MariaDB rutini oluşturan hesaba ALTER ROUTINE ve EXECUTE yetkilerini otomatik verir; bu davranış automatic_sp_privileges değişkeniyle (varsayılan 1) yönetilir. Başka bir hesabı DEFINER olarak yazmak için ek yetki gerekir.

SQL SECURITY DEFINER (varsayılan) ile rutin, tanımlayan hesabın yetkileriyle çalışır; INVOKER ile çağıranın yetkileriyle. Belgelerdeki örnek bunun gücünü gösterir: roger kullanıcısının employees tablosunda yalnızca name ve dept sütunlarına yetkisi vardır, ama DEFINER bağlamında çalışan max_salary fonksiyonu üzerinde EXECUTE yetkisi verilirse departmanın en yüksek maaşını görebilir, tek tek maaşları göremez. Aynı güç dikkatsiz kullanıldığında risk de yaratır; belgeler INVOKER'ın genellikle daha az riskli olduğunu belirtir.

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

Yetki sözdiziminin ayrıntıları için GRANT ve REVOKE ile yetkiler yazısına bakın. Kullanıcıdan gelen değerleri rutin içinde birleştirip dinamik SQL üretiyorsanız SQL injection önleme yazısındaki ilkeler burada da geçerlidir.

Listeleme, değiştirme ve silme

Sunucudaki yordamları SHOW PROCEDURE STATUS, fonksiyonları SHOW FUNCTION STATUS ile listeleyebilir ya da INFORMATION_SCHEMA.ROUTINES tablosunu ROUTINE_TYPE sütununa göre sorgulayabilirsiniz. Tanımı görmek için SHOW CREATE PROCEDURE ve SHOW CREATE FUNCTION kullanılır. ALTER PROCEDURE ve ALTER FUNCTION yalnızca nitelikleri değiştirir; parametreleri veya gövdeyi değiştirmek için rutini yeniden oluşturmanız gerekir. CREATE OR REPLACE bu iş için uygundur, çünkü belgelere göre DROP ve CREATE çiftinden farklı olarak rutine verilmiş yetkileri korur.

Yedek alırken de rutinleri unutmayın: belgelere göre mariadb-dump saklı yordamları ve fonksiyonları varsayılan olarak dökmez, bunun için --routines seçeneği gerekir. Ayrıntılar mariadb-dump ile yedekleme yazısındadır.

SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER, SECURITY_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'sirket';

SHOW CREATE PROCEDURE stok_dusur;

DROP PROCEDURE IF EXISTS stok_dusur;
DROP FUNCTION IF EXISTS hello;

Rutinlerde kullanılamayan ifadeler

Belgelerde tüm saklı programlar (fonksiyon, yordam, event ve tetikleyici) için ortak bir yasak listesi bulunur: ALTER VIEW (yerine CREATE OR REPLACE VIEW), LOAD DATA, CHANGE MASTER TO, LOCK TABLES ve UNLOCK TABLES kullanılamaz; rutin içindeki hazırlanmış ifadeler yerel değişkenlere başvuramaz (kullanıcı değişkenleri kullanılmalıdır). Özyinelemeli yordam çağrıları max_sp_recursion_depth ile sınırlıdır ve bu değişkenin varsayılanı 0 olduğu için özyineleme başlangıçta kapalıdır. Bir rutin başka bir rutini çağırırsa çağıranın kısıtlarını devralır; örneğin bir fonksiyondan çağrılan yordam sonuç kümesi döndüremez.

Sık yapılan hatalar

• DELIMITER kullanmamak: Komut satırında BEGIN ... END içeren tanımlar 1064 hatası verir. Tanımdan önce DELIMITER //, sonra DELIMITER ; yazın.
• Rutin içinde BEGIN ile transaction başlatmaya çalışmak: BEGIN blok açar; transaction için START TRANSACTION kullanın.
• Fonksiyona NOW() koyup DETERMINISTIC demek: Belgelere göre yanlış sonuçlara yol açabilir. Deterministik olmayan fonksiyonları öyle işaretlemeyin.
• İfade tabanlı günlükte fonksiyon oluşturamamak: Fonksiyonu DETERMINISTIC, NO SQL veya READS SQL DATA olarak bildirin ya da günlük biçimini gözden geçirin.
• OUT parametresine sabit değer vermek: CALL p(5) değil, CALL p(@sonuc) yazın ve ardından SELECT @sonuc ile okuyun.
• DROP ve CREATE ile yeniden oluşturup yetkileri kaybetmek: Rutine verilmiş yetkileri korumak için CREATE OR REPLACE kullanın.

Sık Sorulan Sorular

Stored procedure mi, uygulama kodu mu?

Belgeler yordamların güvenlik ve çok dilli istemci ortamlarında öne çıktığını belirtir. İş mantığının tamamını veritabanına taşımak zorunda değilsiniz; tablolara doğrudan erişimi kısıtlamak veya birden çok istemcinin aynı işlemi aynı biçimde yapmasını sağlamak istediğiniz yerlerde yordam kullanmak mantıklıdır.

Fonksiyon içinde SELECT ile sonuç döndürebilir miyim?

Hayır. Saklı fonksiyonlar sonuç kümesi döndüremez; değeri SELECT ... INTO degisken ile alıp RETURN ile döndürmeniz gerekir. Sonuç kümesi gerekiyorsa yordam kullanın.

Yordam parametrelerine varsayılan değer verebilir miyim?

Evet, belgelere göre MariaDB 11.8'den itibaren IN parametreleri DEFAULT ile varsayılan bir değer veya ifade alabilir. 12.3 bu özelliği içerir.

Rutin hangi veritabanında çalışır?

Rutin oluşturulduğu veritabanına bağlıdır ve çağrıldığında o veritabanı varsayılan yapılır. Başka bir veritabanındaki tabloya erişmek için tablo adını db.tablo biçiminde nitelendirin.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · MariaDB trigger · Event Scheduler · Transaction ve izolasyon seviyeleri · GRANT ve REVOKE · MariaDB view oluşturma · SQL injection önleme