Hoe maak je correct subprogramma's (Procedure/Function) aan en gebruik je ze in SQL voor de implementatie van herbruikbare bedrijfslogica, en wat zijn de nuances van parameter- en returnwaardebeheer?

Antwoord.

Subprogramma's in SQL zijn van twee soorten:

• Procedures (Stored Procedures): voeren acties uit (invoegen, bijwerken, verwijderen), kunnen OUT-parameters of resultsets retourneren (via SELECT). Worden aangeroepen met CALL/EXEC en worden niet altijd gebruikt in de select-laag.
• Functies (User Defined Functions, UDF): retourneren een waarde (scalair of tabelvormig), kunnen direct in SELECT, WHERE, ORDER BY en andere constructies worden gebruikt.

Belangrijk:

• Procedures kunnen NIET in een gewone SELECT worden gebruikt, alleen via een oproep met EXEC/CALL.
• Functies kunnen slechts één waarde (scalair) of een tabel (tabelvormige functies) retourneren, en kunnen in elke expressie worden gebruikt.
• Documenteer altijd parameters: IN (voor invoer), OUT (voor het retourneren van de resultaten), INOUT (tweezijdig).

Voorbeeld van een scalair functie (PostgreSQL):

CREATE FUNCTION get_tax(amount NUMERIC, rate NUMERIC DEFAULT 0.13) RETURNS NUMERIC AS $$
BEGIN
  RETURN amount * rate;
END;
$$ LANGUAGE plpgsql;

-- gebruik:
SELECT *, get_tax(price) AS tax FROM product;

Voorbeeld van een stored procedure (SQL Server):

CREATE PROCEDURE add_employee(@name NVARCHAR(100), @salary INT, @emp_id INT OUTPUT)
AS
BEGIN
  INSERT INTO employees (name, salary) VALUES (@name, @salary);
  SET @emp_id = SCOPE_IDENTITY();
END;

DECLARE @id INT;
EXEC add_employee 'John', 100000, @id OUTPUT;

Vraag met een strikvraag.

Kan een stored procedure rechtstreeks in SELECT worden gebruikt?

Vaak antwoorden mensen "ja", maar dat is onjuist.

Antwoord:

• Nee: standaardprocedures worden alleen apart aangeroepen (EXEC/CALL), terwijl functies in SELECT kunnen worden gebruikt.

---

Voorbeelden van echte fouten door onbekendheid met de nuances van het onderwerp.

---

Verhaal

Project: Systeem voor primaire boekhouding, implementatie van rapporten. Fout: In plaats van een functie is er een procedure geschreven voor het berekenen van de som - SELECT werkte niet, de gehele logica van de gebruikersrapporten moest herschreven worden.

---

Verhaal

Project: ERP-systeem met externe parameters. Fout: Voor de procedure is er geen OUT-key opgegeven, waardoor de klant de ID van de toegevoegde record niet kon weten, integratie "kapot" ging.

---

Verhaal

Project: Financiële dienst met belastingberekeningen volgens verschillende regels. Fout: Een scalair functie is in een massaal verzoek gebruikt zonder prestatie-test - de aanvraag vertraagde de verwerking van de tabel door de regel-voor-regel oproep (niet-geoptimaliseerd plan).