Prosedyrer, triggere, rekursjon

Tre ting deklarativ SQL ikke gjør

Vanlig SQL er sett-orientert og uten side-effekter. Det dekker det aller meste av spørringer, men det finnes tre situasjoner der du må gå utenfor:

CREATE FUNCTION

En funksjon tar inn parametre, gjør en beregning, og returnerer et resultat. To smaker:

Skalar-funksjon — returnerer én verdi

CREATE FUNCTION dept_count (dept VARCHAR(20))
  RETURNS INT
  BEGIN
    DECLARE n INT;
    SELECT COUNT(*) INTO n
      FROM instructor
      WHERE dept_name = dept;
    RETURN n;
  END;

Kalles som en hvilken som helst innebygd funksjon:

SELECT dept_name, dept_count(dept_name) AS n
FROM department;

Tabellfunksjon — returnerer en relasjon

CREATE FUNCTION instructors_of (dept VARCHAR(20))
  RETURNS TABLE (
    ID VARCHAR(5),
    name VARCHAR(20),
    salary NUMERIC(8, 2)
  )
  RETURN
    SELECT ID, name, salary
    FROM instructor
    WHERE dept_name = dept;

-- Brukes i FROM:
SELECT * FROM TABLE(instructors_of('CS'));

CREATE PROCEDURE

Forskjellen fra funksjoner: prosedyrer kan ha side-effekter (INSERT/UPDATE/DELETE), kan ha flere ut-parametre, og kalles med CALL i stedet for å brukes som uttrykk.

CREATE PROCEDURE dept_count_proc (
  IN  dept VARCHAR(20),
  OUT n   INT
)
BEGIN
  SELECT COUNT(*) INTO n
    FROM instructor
    WHERE dept_name = dept;
END;

-- Kall:
CALL dept_count_proc('CS', @res);
SELECT @res;

Parametertyper

• IN — leses inn (default).
• OUT — settes av prosedyren.
• INOUT — leses inn og settes ut.

IF, WHILE, FOR — i SQL

CREATE PROCEDURE raise_if_low ()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE id  VARCHAR(5);
  DECLARE sal NUMERIC(8, 2);

  DECLARE c CURSOR FOR
    SELECT ID, salary FROM instructor;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c;
  read_loop: LOOP
    FETCH c INTO id, sal;
    IF done THEN LEAVE read_loop; END IF;

    IF sal < 50000 THEN
      UPDATE instructor
        SET salary = salary * 1.10
        WHERE ID = id;
    END IF;
  END LOOP;
  CLOSE c;
END;

UPDATE instructor SET salary = salary * 1.10 WHERE salary < 50000;

Konstrukter du har

• IF … THEN … ELSEIF … ELSE … END IF
• WHILE … DO … END WHILE
• REPEAT … UNTIL … END REPEAT
• LOOP … END LOOP med LEAVE label
• CURSOR FOR <query> for radvis traversering

CREATE TRIGGER — automatiske reaksjoner

En trigger er en prosedyre som automatisk kjøres når en hendelse skjer på en tabell. Triggere lever inni databasen — applikasjonen vet ikke at de finnes.

De fire dimensjonene

Eksempel: hold totalsum oppdatert

CREATE TRIGGER upd_total
AFTER UPDATE OF credits ON takes
REFERENCING NEW ROW AS nrow,
            OLD ROW AS orow
FOR EACH ROW
WHEN (nrow.grade IS NOT NULL AND orow.grade IS NULL)
BEGIN
  UPDATE student
    SET tot_cred = tot_cred + nrow.credits
    WHERE ID = nrow.ID;
END;

Trigger-en kjører automatisk hver gang en student går fra «ingen karakter» til «en karakter» i et fag.

Hvorfor triggere er omstridt

Triggere er kraftige, men er beryktet for å produsere uventede produksjonsproblemer. De viktigste fellene:

1. Skjulte effekter. En INSERT kan utløse en trigger som UPDATE-er en annen tabell, som utløser en ny trigger … Lett å miste oversikt.
2. Trigger-kaskader. Trigger A oppdaterer tabell B; en trigger på B oppdaterer tabell C; en trigger på C oppdaterer A → endeløs løkke.
3. Ytelse. FOR EACH ROW kjører én gang per påvirket rad. En batch-INSERT av 100 000 rader gir 100 000 trigger-kall.
4. Replikering / restore. Hvis en trigger bare finnes på master, blir slaver inkonsistente. Hvis trigger gjør ting basert på dato, kan en restore «replaye» ting i feil rekkefølge.
5. Testbarhet. Logikken er gjemt i databasen, ikke i appen. Vanskelig å enhetsteste.

Spørringer som refererer til seg selv

Før SQL:1999 kunne ikke standardspråket uttrykke transitive lukninger — ting som «forfedre», «alle naboer i X hopp», «alle delene en sammensetning består av». Nå har vi WITH RECURSIVE.

Generell struktur

WITH RECURSIVE r AS (
  -- 1. Anker (basistilfelle): ikke-rekursiv SELECT
  SELECT …
  FROM base_table
  WHERE …
  UNION ALL
  -- 2. Rekursivt ledd: refererer til r selv
  SELECT …
  FROM base_table b JOIN r ON …
)
SELECT * FROM r;

Algoritmen (mental modell)

1. Evaluer anker-leddet. Det blir første «runde».
2. Evaluer rekursivt ledd ved å bruke forrige rundes resultat som r. Resultatet blir neste runde.
3. Gjenta steg 2 inntil rekursivt ledd produserer ingen nye rader.
4. Returnér unionen av alle rundene.

Eksempel: forutsetninger for et emne

Tabellen prereq(course_id, prereq_id) sier at course_id har prereq_id som direkte forutsetning. Vi vil finne alle forutsetninger, transitivt.

WITH RECURSIVE rec_prereq(course_id, prereq_id) AS (
  -- Anker: direkte forutsetninger
  SELECT course_id, prereq_id
  FROM prereq
  UNION ALL
  -- Rekursivt: forutsetningenes forutsetninger
  SELECT r.course_id, p.prereq_id
  FROM rec_prereq r
  JOIN prereq p ON r.prereq_id = p.course_id
)
SELECT DISTINCT prereq_id
FROM rec_prereq
WHERE course_id = 'CS-347';

Andre klassiske eksempler

• Forfedre: person(id, parent_id) → finn alle forfedre til person X.
• Bill of materials: part(part_id, sub_id, qty) → hvor mye råstoff trengs for å lage X?
• Korteste vei (BFS-liknende): ved å holde styr på «hopp» som en kolonne.

Det du skal kunne

1. Skille funksjon fra prosedyre, og kjenne IN/OUT/INOUT-parametrene.
2. Skrive en BEFORE-trigger som validerer en INSERT.
3. Si når FOR EACH ROW vs. FOR EACH STATEMENT er rett valg.
4. Liste de 5 vanligste feilene man gjør med triggere — og når de likevel er riktig.
5. Skrive en WITH RECURSIVE-spørring med anker + rekursivt ledd.
6. Forklare hva en transitiv lukning er og hvilken klasse av problemer den løser.

Du har nå lest alle fire delene av kapittel 3. Tilbake til kapitteloversikten for å ta hovedquizen.