Programare procedurală a bazelor de date

Programarea procedurală în baza de date combină interogările cu variabile, condiții, bucle și subprograme executate de SGBD. Este utilă pentru operații care trebuie făcute aproape de date, după reguli repetabile. Sintaxa exactă depinde de sistemul ales; PL/pgSQL este un exemplu din PostgreSQL, nu o sintaxă universală SQL.

Programarea procedurală în baza de date combină interogările SQL cu variabile, condiții, bucle și subprograme (funcții și proceduri) executate de SGBD, aproape de date. Este utilă pentru operații repetabile, care urmează reguli și ar cere altfel mai multe cereri trimise din aplicație. Sintaxa depinde de sistemul ales: Oracle folosește PL/SQL, Microsoft SQL Server folosește Transact-SQL, MySQL are propriile subprograme stocate, iar PostgreSQL are PL/pgSQL, folosit în exemplele de mai jos; nu este o sintaxă universală SQL. O funcție întoarce o valoare și se apelează în SELECT; o procedură se apelează cu CALL și poate modifica tabele. Blocul BEGIN...END ordonează pașii, DECLARE introduce variabilele, IF alege între ramuri, iar FOR ... LOOP parcurge înregistrările unei interogări.

Exemplul 1 — o funcție care numără. Tabelul elevi are elevii Ana (clasa 9, media 7), Dan (clasa 9, media 4) și Mara (clasa 10, media 9,5):

CREATE TABLE elevi (id integer PRIMARY KEY, nume text NOT NULL,
                    clasa_id integer NOT NULL, medie numeric(4,2), promovat boolean);
INSERT INTO elevi (id, nume, clasa_id, medie)
VALUES (1, 'Ana', 9, 7), (2, 'Dan', 9, 4), (3, 'Mara', 10, 9.5);

CREATE FUNCTION numar_elevi(p_clasa integer)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE rezultat bigint;
BEGIN
  SELECT COUNT(*) INTO rezultat
  FROM elevi WHERE clasa_id = p_clasa;
  RETURN rezultat;
END;
$$;

SELECT numar_elevi(9);

Funcția declară variabila rezultat, pune în ea numărul rândurilor selectate și îl întoarce. Apelul SELECT numar_elevi(9); întoarce 2, iar numar_elevi(10) ar întoarce 1. Funcția doar citește tabelul și nu îl modifică; COUNT(*) întoarce numărul elevilor, nu rândurile lor.

Exemplul 2 — o procedură care modifică datele. Procedura parcurge elevii unei clase, decide cu IF și actualizează câmpul promovat:

CREATE PROCEDURE marcheaza_promovati(p_clasa integer)
LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT id, medie FROM elevi WHERE clasa_id = p_clasa LOOP
    IF r.medie >= 5 THEN
      UPDATE elevi SET promovat = true WHERE id = r.id;
    ELSE
      UPDATE elevi SET promovat = false WHERE id = r.id;
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  RAISE;
END;
$$;

CALL marcheaza_promovati(9);
SELECT nume, promovat FROM elevi WHERE clasa_id = 9 ORDER BY id;

Pentru cei doi elevi ai clasei 9, cu mediile 7 și 4, rezultatul este (Ana, adevărat) și (Dan, fals). Variabila r este înregistrarea curentă a parcurgerii; blocul EXCEPTION propagă eroarea către apelant. Elevul din clasa 10 nu este modificat, pentru că interogarea din FOR filtrează după clasă.

Exemplul 3 — ramuri multiple. O funcție poate transforma media într-un calificativ prin IF ... ELSIF:

CREATE FUNCTION calificativ(p_medie numeric) RETURNS text
LANGUAGE plpgsql AS $$
BEGIN
  IF p_medie >= 9 THEN RETURN 'FB';
  ELSIF p_medie >= 7 THEN RETURN 'B';
  ELSIF p_medie >= 5 THEN RETURN 'S';
  ELSE RETURN 'I';
  END IF;
END;
$$;

SELECT nume, calificativ(medie) FROM elevi ORDER BY id;

Se obțin perechile (Ana, B), (Dan, I) și (Mara, FB). Ramurile se evaluează în ordine, iar prima condiție adevărată hotărăște rezultatul: pentru media 9,5 se alege FB, deși sunt adevărate și condițiile următoare.

Exemplul 4 — ciclul WHILE și atribuirea :=. În PL/pgSQL valoarea unei variabile se schimbă cu :=, iar WHILE repetă corpul cât timp condiția este adevărată:

CREATE FUNCTION suma_pana_la(n integer) RETURNS integer
LANGUAGE plpgsql AS $$
DECLARE i integer := 1; s integer := 0;
BEGIN
  WHILE i <= n LOOP
    s := s + i;
    i := i + 1;
  END LOOP;
  RETURN s;
END;
$$;

SELECT suma_pana_la(4);

Pentru $n=4$, valorile lui s după fiecare iterație sunt 1, 3, 6, 10, deci apelul întoarce 10. Pentru $n=0$ condiția $1\le 0$ este falsă de la început, corpul nu se execută și funcția întoarce 0, valoarea inițială a lui s.

Exemplul 5 — valoarea NULL într-o condiție. Dacă tabelul elevi ar conține un elev fără medie, de pildă Radu cu medie nulă, procedura marcheaza_promovati ar găsi la el r.medie >= 5. O comparație cu NULL nu este nici adevărată, nici falsă, iar IF execută ramura ELSE atunci când condiția nu este adevărată. Radu ar primi deci promovat = false, deși nu are încă nicio medie. Cazul se tratează explicit, la începutul corpului ciclului: IF r.medie IS NULL THEN CONTINUE; END IF;, care trece la elevul următor fără să modifice rândul.

Funcție sau procedură. Pentru a afla media clasei se folosește o funcție, pentru că rezultatul intră într-un SELECT. Pentru a marca promovații se folosește o procedură, pentru că scopul este modificarea tabelului, nu o valoare întoarsă.

Greșeli frecvente

• Greșit: sintaxa PL/pgSQL este identică în orice SGBD.
  Corect: dialectele procedurale diferă.
• Greșit: COUNT(*) întoarce rândurile elevilor.
  Corect: întoarce numărul lor.
• Greșit: funcția de numărare schimbă tabelul.
  Corect: în exemplu doar îl citește.

Înainte și după