SQL Cyklus (Loop) – Umíte použít WHILE nebo rekurzivní CTE?

Poslední aktualizace:

Jako Loop můžeme označit opakované volání části kódu s různými parametry a většinou je spojen s nějakou DML operací typu INSERT nebo UPDATE. Cyklus voláme po předem stanovený počet opakování a můžeme ho v SQL sestavit několika různými způsoby. Na příkladu s kalendářem si ukážeme všechny způsoby.

Jak na Generování kalendáře v SQL pomocí cyklu?

Dejme tomu, že bychom si chtěli vyrobit v databázi tabulku s kalendářem od 1.1.1980 do 31.12.2050. Kalendář se dá do databáze pochopitelně založit i elegantnějšími způsoby než použitím cyklu, berme to jako cvičení.

Založení prázdné tabulky s kalendářem jako příprava:

CREATE TABLE #Calendar(
   [Datum] [Date] PRIMARY KEY,
   [DenRoku]     AS (DATEPART(DAYOFYEAR,[Datum])),
   [DenTydne]    AS (DATEPART(WEEKDAY,[Datum])),
   [Mesic]       AS (DATENAME(MONTH,DATEADD(MONTH,DATEPART(MONTH,[Datum]),(0))-(1))),
   [Kvartal]     AS (CONCAT(DATEPART(QUARTER,[Datum]),'Q')),
   [Rok]         AS (DATEPART(YEAR,[Datum]))
);

Tempovou tabulku pro účely příkladu zakládáme s calculated fieldy abychom nemuseli opakovaně vkládat celý seznam polí, ale pouze [Datum]. Po založení tabulky tímto způsobem stačí vkládat jednotlivé datumy a zbytek polí se posléze automaticky dopočítá.

1. Způsob – GOTO metoda

Tento způsob není příliš doporučován, protože je pomalejší než ostatní. Spočívá v definici label (např. “SQL_CYKLUS_START:”) ke kterému se prostřednictvím příkazu GOTO v jednotlivých iteracích vracíme.

DECLARE @datum_plovouci AS SMALLDATETIME = '1980-01-01';
DECLARE
@datum_zarazka AS SMALLDATETIME = '2050-12-31';

SQL_CYKLUS_START:

   INSERT INTO #Calendar ([datum])
   VALUES(@datum_plovouci)
   SET @datum_plovouci = @datum_plovouci+1

IF @datum_plovouci < @datum_zarazka
GOTO SQL_CYKLUS;

SELECT * FROM #Calendar;

2. WHILE/BEGIN/END Cyklus

WHILE příkazem provádíme opakované volání kódu následujícího po while (většinou odděleného BEGIN/END) a to do doby dokud platí podmínka následující po WHILE

/*promažeme záznamy v naší tabulce a vložíme datumy jinám zpusobem*/

TRUNCATE TABLE #Calendar;

DECLARE @datum_plovouci AS SMALLDATETIME = '1980-01-01'
DECLARE @datum_zarazka AS SMALLDATETIME = '2050-12-31'

WHILE @datum_plovouci< @datum_zarazka
BEGIN
INSERT INTO
#Calendar ([datum])
  VALUES(@datum_plovouci)
SET @datum_plovouci=@datum_plovouci+1
END;

3 Rekurzivní CTE (Common table expression)

Rekurzivní CTE jsou zdaleka nejrychlejším způsobem jak dosáhnout výsledku:

/*promažeme záznamy v naší tabulce a vložíme datumy jinám zpusobem*/
TRUNCATE TABLE #Calendar;

DECLARE @datum_plovouci AS SMALLDATETIME = '1980-01-01';
DECLARE @datum_zarazka AS SMALLDATETIME = '2050-12-31'

;WITH [CTE_Date] ([Datum]) AS (
   SELECT @datum_plovouci AS DATUM
     UNION ALL
   SELECT [DATUM]+1 AS DATUM
   FROM [CTE_Date]
   WHERE [DATUM] + 1<@datum_zarazka
)
INSERT INTO #Calendar
SELECT * FROM [CTE_Date] OPTION (MAXRECURSION 32767);

Více se o rekurzivních dotazech můžete dozvěděl z webu Microsoftu

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: SQL příkazy Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.