Saklı Yordamlar ve İşlevler

Buraya kadar işlemin sınırını, hata durumunda ne yapılacağını ve yeniden denemeyi hep uygulama kodu belirledi; veritabanı yalnız gönderilen deyimleri aldı. Bu mantığın bir kısmı veritabanının içinde de tanımlanabilir. “Ödünç verme” kuralı bir yordama konursa uygulama iki deyim değil, tek bir çağrı gönderir; kural veriyle aynı yerde durur.

Saklı yordam (stored procedure) ve saklı işlev (stored function), sunucu tarafında saklanan ve adıyla çağrılan kod birimleridir. Bu ders ikisinin standart biçimini, ayrımını ve getirdiği ödünleşimleri ele alır.

Yordam ve İşlev

İkisini ayıran, nasıl çağrıldıkları ve ne döndürdükleridir.

Yordam, CALL deyimiyle çağrılır. Bir değer döndürmek zorunda değildir; girdi, çıktı ve giriş-çıkış parametreleri alabilir ve gövdesinde veri değiştiren deyimler bulunabilir.

İşlev, bir değer döndürür ve bir ifadenin geçebildiği her yerde — SELECT listesinde, WHERE koşulunda — kullanılabilir. Bu yüzden yan etkisiz olması beklenir.

Standart SQL’in yordamsal alt kümesi, ödünç verme kuralını şöyle yazar:

CREATE PROCEDURE odunc_ver(IN p_kitap_id INTEGER, IN p_uye_id INTEGER)
LANGUAGE SQL
BEGIN ATOMIC
  INSERT INTO odunc(kitap_id, uye_id, alis)
    VALUES (p_kitap_id, p_uye_id, CURRENT_DATE);
  UPDATE kitap SET rafta = 0 WHERE id = p_kitap_id;
END;

Çağrısı tek satırdır:

CALL odunc_ver(1, 4);

Gecikme hesabı ise bir işlevdir; değer döndürür ve sorguda kullanılır:

CREATE FUNCTION gecikme_gunu(p_alis DATE, p_iade DATE)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
RETURN CASE
         WHEN (COALESCE(p_iade, CURRENT_DATE) - p_alis) - 14 > 0
         THEN (COALESCE(p_iade, CURRENT_DATE) - p_alis) - 14
         ELSE 0
       END;

Bu üç blok çalıştırılmamıştır; biçimi göstermek için yazılmıştır. Nedeni, yordamsal alt kümenin desteklenmesinin motora göre değişmesidir: bazı motorlar standarda yakın bir dil sunar, bazıları kendi yordam dilini kullanır, bazıları — bu kursun gözlem ortamı dâhil — saklı yordamı hiç desteklemez. Değişmeyen, CREATE PROCEDURE ile CREATE FUNCTION ayrımı ve CALL ile çağırma biçimidir.

BEGIN ATOMIC bloğu, gövdedeki deyimlerin bölünmez bir birim olduğunu söyler: biri başarısız olursa hiçbiri uygulanmaz. İşlem denetimi dersindeki atomiklik burada yordam tanımının parçası hâline gelir.

Uygulamada Tanımlanan İşlev

Saklı yordam desteklemeyen bir motorda işlevin karşılığı, bağlantıya kayıtlı bir uygulama işlevidir. Kod uygulamada durur, ama sorgunun içinde çalışır — motor her satır için onu çağırır:

cat > islev.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE odunc(id INTEGER PRIMARY KEY, uye_id INT, alis TEXT, iade TEXT);
  INSERT INTO odunc VALUES (1,1,'2024-03-01','2024-03-15'),(2,1,'2024-03-04','2024-03-18'),
    (3,2,'2024-03-06','2024-03-25'),(4,2,'2024-03-11',NULL),(5,7,'2024-03-18','2024-03-24');
`);

const GUN = 86400000;
db.function('gecikme_gunu', { deterministic: true }, (alis, iade) => {
  const bitis = iade === null ? Date.parse('2024-03-31') : Date.parse(iade);
  const gun = Math.round((bitis - Date.parse(alis)) / GUN);
  return Math.max(0, gun - 14);
});

for (const s of db.prepare(
  `SELECT id, uye_id, alis, COALESCE(iade, '(acik)') AS iade,
          gecikme_gunu(alis, iade) AS gecikme
   FROM odunc WHERE gecikme_gunu(alis, iade) > 0 ORDER BY id`).all()) {
  console.log(JSON.stringify(s));
}
JS
node islev.mjs

{"id":3,"uye_id":2,"alis":"2024-03-06","iade":"2024-03-25","gecikme":5}
{"id":4,"uye_id":2,"alis":"2024-03-11","iade":"(acik)","gecikme":6}

Beş kayıttan ikisi on dört günlük süreyi aştı. Kural tek yerde yazıldı ve hem SELECT listesinde hem WHERE koşulunda kullanıldı; aynı hesabın iki yerde kopyalanmasına gerek kalmadı. Saklı işlevin sağladığı da tam olarak budur — fark, kodun nerede saklandığıdır.

Belirlenimcilik

İşlev tanımındaki DETERMINISTIC sözcüğü bir söz verir: aynı argümanlarla çağrıldığında her zaman aynı sonucu döndürür. Bu söz, planlayıcıya iki izin verir — sonucu önbelleğe alıp tekrar hesaplamamak ve işlevin sonucunu bir dizinde saklamak.

İkinci izin ölçülebilir. Aşağıdaki betik iki işlev tanımlar; ikisi de aynı gövdeye sahiptir, yalnız biri belirlenimci olduğunu bildirir. Her ikisi üzerinde dizin oluşturulmaya çalışılır:

cat > belirlenimci.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`CREATE TABLE odunc(id INTEGER PRIMARY KEY, alis TEXT, iade TEXT);`);

db.function('sabit_sure', { deterministic: true },
            (alis, iade) => (iade === null ? -1 : 1));
db.function('degisken_sure', (alis, iade) => (iade === null ? -1 : 1));

for (const [ad, islev] of [['sabit_sure', 'sabit_sure'],
                           ['degisken_sure', 'degisken_sure']]) {
  try {
    db.exec(`CREATE INDEX dizin_${ad} ON odunc(${islev}(alis, iade))`);
    console.log(`${ad}: dizin oluşturuldu`);
  } catch (hata) {
    console.log(`${ad}: ${hata.message}`);
  }
}
JS
node belirlenimci.mjs

sabit_sure: dizin oluşturuldu
degisken_sure: non-deterministic functions prohibited in index expressions

Gerekçe basittir: dizin, işlevin sonucunu saklar. Sonuç zamanla değişebiliyorsa dizindeki değer veriyle uyumsuz hâle gelir ve sorgular yanlış yanıt verir. Motor bu tehlikeyi baştan keser.

Aynı gerekçe, belirlenimci olmayan bir işlevin sorgu içinde her satır için yeniden çağrılmasını da açıklar. Geçerli zamanı, rastgele sayıyı ya da oturum bilgisini okuyan bir işlev belirlenimci ilan edilirse, motorun yaptığı eniyilemeler sessiz hatalara dönüşür. Bildirim bir söz olduğu için sorumluluk yazarındadır.

Gidiş Dönüş Maliyeti

Sunucu tarafı mantığın en somut yararı, uygulama ile motor arasındaki tur sayısını azaltmasıdır. Bu, iş miktarından bağımsız bir maliyettir: her deyim ayrı bir istek, ayrı bir yanıt demektir.

Aşağıdaki betik iki yüz kitabı ödünç verir. Önce her kitap için iki deyim gönderir, sonra aynı işi iki küme deyimiyle yapar:

cat > gidis.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE kitap(id INTEGER PRIMARY KEY, rafta INT NOT NULL);
  CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT);
`);
for (let i = 1; i <= 200; i += 1) db.exec(`INSERT INTO kitap VALUES (${i}, 1)`);

let deyim = 0;
const calistir = (sql, ...arg) => { deyim += 1; db.prepare(sql).run(...arg); };

deyim = 0;
db.exec('BEGIN');
for (let i = 1; i <= 200; i += 1) {
  calistir('INSERT INTO odunc(kitap_id, uye_id, alis) VALUES (?, 4, ?)',
           i, '2024-03-11');
  calistir('UPDATE kitap SET rafta = 0 WHERE id = ?', i);
}
db.exec('COMMIT');
console.log('satır satır  : gönderilen deyim =', deyim,
            '| ödünç kaydı =', db.prepare('SELECT COUNT(*) AS n FROM odunc').get().n);

db.exec('DELETE FROM odunc; UPDATE kitap SET rafta = 1;');
deyim = 0;
db.exec('BEGIN');
calistir(`INSERT INTO odunc(kitap_id, uye_id, alis)
          SELECT id, 4, '2024-03-11' FROM kitap WHERE rafta = 1`);
calistir('UPDATE kitap SET rafta = 0 WHERE rafta = 1');
db.exec('COMMIT');
console.log('küme tabanlı : gönderilen deyim =', deyim,
            '| ödünç kaydı =', db.prepare('SELECT COUNT(*) AS n FROM odunc').get().n);
JS
node gidis.mjs

satır satır  : gönderilen deyim = 400 | ödünç kaydı = 200
küme tabanlı : gönderilen deyim = 2 | ödünç kaydı = 200

Aynı iki yüz kayıt, 400 deyim yerine 2 deyimle üretildi. Burada motor ile uygulama aynı süreçte olduğu için farkın etkisi küçüktür; ağ üzerinden konuşan bir uygulamada 400 deyim 400 tur demektir ve her turun gecikmesi işin kendisinden büyük olabilir.

Saklı yordamın kazandırdığı budur: döngü sunucu tarafında kalır, uygulama tek çağrı yapar. Ancak ölçüm ikinci bir şey daha söyler — turu azaltmanın yordam gerektirmediği. Küme tabanlı yazım, saklı yordam olmadan aynı kazancı sağladı. Yordam, ancak küme deyimiyle ifade edilemeyen bir denetim akışı gerektiğinde bu gerekçeyle savunulabilir.

Görünümle Karşılaştırma

Sunucu tarafına yalnız yordam konmaz. Görünüm (view), bir sorguya ad verip şemada saklar; parametre almaz ama tekrar eden birleştirme ve süzme mantığını tek yerde toplar:

sqlite3 -box -header <<'SQL'
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube TEXT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO uye VALUES (1,'Ayse','Kadikoy'),(2,'Burak','Kadikoy'),(7,'Gokhan','Kadikoy');
INSERT INTO odunc VALUES (1,1,1,'2024-03-01','2024-03-15'),(4,7,1,'2024-03-11',NULL),
  (9,8,2,'2024-03-21',NULL),(23,2,7,'2024-03-11','2024-03-16');

CREATE VIEW acik_odunc AS
  SELECT o.id, u.ad, o.alis
  FROM odunc o JOIN uye u ON u.id = o.uye_id
  WHERE o.iade IS NULL;

SELECT * FROM acik_odunc ORDER BY id;
SELECT COUNT(*) AS acik_sayisi FROM acik_odunc;
SQL

┌────┬───────┬────────────┐
│ id │  ad   │    alis    │
├────┼───────┼────────────┤
│ 4  │ Ayse  │ 2024-03-11 │
│ 9  │ Burak │ 2024-03-21 │
└────┴───────┴────────────┘
┌─────────────┐
│ acik_sayisi │
├─────────────┤
│ 2           │
└─────────────┘

“Açık ödünç” tanımı tek yerde durdu ve iki sorgu da onu kullandı. Tanım değişirse — örneğin iptal edilmiş kayıtlar da dışlanacaksa — tek bir yer güncellenir. Görünüm, ortak tablo ifadesinden farklı olarak kalıcıdır ve şemanın parçasıdır.

Sunucu tarafına kod koymanın bedeli de vardır. Yordam gövdeleri sürüm denetimine ve sınama düzenine uygulama kodu kadar kolay girmez; her motorun yordam dili farklı olduğundan taşınabilirlik kaybolur; hata ayıklama araçları uygulama diline göre sınırlıdır. Ölçü, konan mantığın niteliğine bakılarak verilir: veri bütünlüğünü koruyan kurallar veriye yakın durmaktan kazanır, iş akışı kararları uygulamada kalmaktan.

Özet

• Yordam CALL ile çağrılır ve veri değiştirebilir; işlev değer döndürür ve ifade içinde kullanılır.
• Standart yordamsal alt küme CREATE PROCEDURE ve CREATE FUNCTION ile tanımlanır; desteklenme derecesi motora göre değişir.
• DETERMINISTIC bildirimi planlayıcıya önbellekleme ve dizinleme izni verir; belirlenimci olmayan bir işlev dizin ifadesinde kullanılamaz.
• Sunucu tarafı mantık gidiş-dönüş sayısını azaltır; ölçümde 400 deyimlik iş 2 deyime indi, ancak aynı kazanç küme tabanlı yazımla yordamsız da sağlandı.
• Görünüm, parametresiz bir sunucu tarafı soyutlamadır ve tekrar eden sorgu mantığını şemada tek yerde tutar.

Sonraki Adım

Bu derste sunucu tarafındaki kod hep çağrıldı: yordam CALL ile, işlev sorgu içinde adıyla. Çağrıldığı yerden okunduğu için ne yaptığı görünürdü. Veritabanı bir de çağrılmadan çalışan kod tanımlar: bir tabloya yazıldığında kendiliğinden devreye giren tetikleyiciler. Sonraki ders bunların nasıl tanımlandığını, ne işe yaradığını ve çağrılmadan çalışan kodun neden özel bir dikkat gerektirdiğini gösterecek.