NetSet: Svět e-business na dlani

Na Microsoft SQL Serveru 2000 byl uvedený do života objekt typu FUNCTION. Funkce mohou být skalární (vracejí jednu hodnotu, třeba výsledek výpočtu) nebo tabulkové. Ty druhé nás budou v tomto článku enormně zajímat, protože tabulkové funkce se chovají trochu jako view (vracejí virtuální sadu záznamů, používají se v klauzuli FROM příkazu SELECT), ale na rozdíl od view umějí přijmout parametry. Ve verzi MS SQL 2000 byl problém s tím, že funkce, kterou jsme chtěli spojit s tabulkou v klauzuli FROM, musela vracet jako jednu z položek výsledkové sady kritérium spojení. Napoví příklad:

Představme si jednoduché datové shéma "člověk a jeho pes".

Clovek(Id, Jmeno)

Pes(Id, ClovekId, Jmeno, Rasa, DatumNarozeni, DatumUhynu)

Vazba Clovek : Pes je 1:N.

Dejme tomu, že bychom chtěli tabulkovou funkcí vracet seznam žijících psů pro daného člověka. Funkce by mohla vypadat takto:

CREATE FUNCTION dbo.fnPsiCloveka(@ClovekId int)

RETURNS TABLE

AS

RETURN

(SELECT Jmeno as JmenoPsa, Rasa, DatumNarozeni FROM Pes

WHERE DatumUhynu IS NULL AND ClovekId = @ClovekId)

A teď pojďme vyřešit zadaní "vytvořte seznam lidí a jejich psů". To znamená, že potřebujeme spojit tabulku Clovek s funkcí dbo.fnPsiCloveka. Díky tomu, že MS SQL Server 2000 neznal jiné operátory spojení, než JOIN (a UNION, ale ten nás teď nezajímá), nemohli bychom zadání vyřešit, protože funkce nevrací kritérium spojení, a parametr funkce (@ClovekId) nám přímo PŘEKÁŽÍ! Funkci bychom museli přepsat takto:

CREATE FUNCTION dbo.fnPsiCloveka(@ClovekId int)

RETURNS TABLE

AS

RETURN

(SELECT Jmeno as JmenoPsa, Rasa, DatumNarozeni, ClovekId FROM Pes

WHERE DatumUhynu IS NULL AND ClovekId = ISNULL(@ClovekId, ClovekId))

Pak bychom mohli napsat:

SELECT ... FROM Clovek INNER JOIN dbo.fnPsiCloveka(NULL) AS fn ON Clovek.Id = fn.ClovekId

Ale v tomto řešení se úplně vytrácí smysl parametru, a v případě, že by funkce nebyla tak triviální, bychom samozřejmě zvyšovali i režii dotazu. Pojďme se podívat na řešení a lá SQL 2005 (a výše).

Teď už jsme v prostředí MS SQL 2005 (nebo 2008), máme k dispozici tabulku Clovek a funkci dbo.fnPsiCloveka s parametrem @ClovekId, a potřebujeme opět řešit stejné zadání, jaké jsem uvedl v předchozím odstavci. Napíšeme tedy tohle:

SELECT ... FROM Clovek CROSS APPLY dbo.fnPsiCloveka(Clovek.Id)

Všimněme si, že díky operátoru CROSS APPLY můžeme spojit tabulku s funkcí přes formální parametr funkce. Jak prosté!

Pokud bychom řešili stav, kdy někteří  lidé nebudou mít žádného žijícího psa, pak místo CROSS APPLY (což je ekvivalent operátoru INNER JOIN) uvedeme operátor OUTER APPLY (ekvivalent LEFT OUTER JOIN).