Jak prawidłowo tworzyć i wykorzystywać podprogramy (Procedure/Function) w SQL do realizacji wielokrotnego użytku logiki biznesowej, oraz jakie są niuanse zarządzania parametrami i wartościami zwracanymi?

Odpowiedź.

Podprogramy w SQL dzielą się na dwa rodzaje:

• Procedury (Stored Procedures): wykonują działania (wstawianie, aktualizowanie, usuwanie), mogą zwracać OUT-parametry lub zestawy wierszy (poprzez SELECT). Są wywoływane za pomocą CALL/EXEC i nie zawsze są używane w warstwie select.
• Funkcje (User Defined Functions, UDF): zwracają wartość (skalarna lub tabelaryczna), mogą być używane bezpośrednio w SELECT, WHERE, ORDER BY i innych konstrukcjach.

Ważne:

• Procedury NIE mogą być używane w zwykłym SELECT, tylko poprzez wywołanie z EXEC/CALL.
• Funkcje mogą zwracać tylko jedną wartość (skalarne) lub tabelę (funkcje tabelaryczne), i mogą być używane w każdym wyrażeniu.
• Zawsze dokumentuj parametry: IN (do wejścia), OUT (do zwrócenia wyniku), INOUT (dwustronne).

Przykład funkcji skalarnej (PostgreSQL):

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

-- użycie:
SELECT *, get_tax(price) AS tax FROM product;

Przykład procedury składowanej (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;

Pytanie podchwytliwe.

Czy można używać procedury składowanej bezpośrednio w SELECT?

Często odpowiadają "tak", ale to nieprawda.

Odpowiedź:

• Nie można: standardowe procedury są wywoływane tylko osobno (EXEC/CALL), podczas gdy funkcje — można używać w SELECT.

---

Przykłady rzeczywistych błędów z powodu nieznajomości niuansów tematu.

---

Historia

Projekt: System wstępnej ewidencji, realizacja raportów. Błąd: Zamiast funkcji napisano procedurę do obliczania sumy — SELECT nie działał, trzeba było przepisane całą logikę raportów użytkowników.

---

Historia

Projekt: System ERP z zewnętrznymi parametrami. Błąd: Dla procedury nie określono klucza OUT, w wyniku czego klient nie mógł poznać ID dodanego rekordu, integracja "się zepsuła".

---

Historia

Projekt: Usługa finansowa z obliczeniami podatku według różnych zasad. Błąd: Użyto funkcji skalarnej w masowym zapytaniu bez testu wydajności — zapytanie spowolniło przetwarzanie tabeli z powodu wywołania wiersz po wierszu (nieoptymalny plan).