által: Daniel Farina | korszerűsített: 2019-09-12 | Hozzászólások (2) | kapcsolódó: több > T-SQL

probléma

mindig hallottam, hogy meg kell kerülni kurzorok a T-SQL kódot, mint egy SQL Server legjobb gyakorlat, merta parancssorok károsak a TELJESÍTMÉNYÉS néha problémákat okoz. De néha szükség van a hurokraegy sor egy időben, így ebben a tippben megnézzük, hogyan kell csinálniegy hurok a kurzor használata nélkül.,

Solution

mindannyian tudjuk, hogy az SQL Server, mint minden relációs adatbázis lehetővé teszi a felhasználó toperform set alapú műveleteket. Továbbá, mint sok adatbázis-gyártó, az SQL Server tartalmazegy eljárási kiterjesztés, amely a T-SQL nyelv. Hozzáteszi konstrukciók talált inprocedural nyelvek, amely lehetővé teszi egy egyszerűbb kódolás a fejlesztők számára. Ezek a konstrukciók okkal kerültek hozzáadásra, és néha ez az egyetlen megközelítés a feladathoz.,

A While Loop helyett kurzorok SQL Server

Ha valaha is dolgozott withcursors, előfordulhat, hogy ez a cím egy kicsit confusingbecause elvégre cursors használ, míg konstrukciók iterálni sorok között. De besideshat, azt akarom mutatni, hogy bizonyos körülmények között, amikor használjuk a kurzort iterateover sorokat tudjuk változtatni, hogy egy darabig hurok. Ilyen esetekben az egyetlen kihívásmeg kell választani a megfelelő kilépési feltételt.,

Előnyök, Hátrányok, a Kurzorok Segítségével, hogy Halad Végig a Táblázat Sor az SQL Server

Nem minden rossz kurzorok, akkor is van néhány advantagesover másik hurok technikák.

  • kurzorok frissíthetők:kurzor létrehozásakor lekérdezést használ a kurzor deklarálása utasítás használatával. A kurzor frissítési opciójának használatávalkreációs nyilatkozat, frissítheti az oszlopokat a kurzoron belül.,
  • a kurzorban előre-hátra mozoghat: a görgetési opció használatával a kurzor deklarálása nyilatkozatban navigálhat a kurzorrekordok közöttmindkét irányban a lekérési opciókkal első, utolsó, előző, következő, relatív andABSOLUTE. Ne feledje, hogy a görgetés opció nem kompatibilis a FORWARD_ONLYand FAST_FORWARD beállításokkal.
  • kurzorok átadhatók a tárolt eljárásoknak: ha a globális opciót használjaa kurzor létrehozásához bármely tárolt eljárásban vagy a kötegelt végrehajtásbanugyanabban a kapcsolatban használható. Ez lehetővé teszi a kurzorok használatát beágyazott tárolt eljárásokon.,
  • a Kurzoroknak sok különböző lehetősége van: a kurzorokkal lehetőséged vanhasználjon különböző lehetőségeket, amelyek befolyásolják, hogyan fognak viselkedni a zárolás tekintetében.
  • a Kurzoroknak nincs szükségük feltételre: a kurzorok használatával rekordként kezeli a sorokat. Ez lehetővé teszi a kurzor mozgatását anélküla logikai állapot szükségessége. Például létrehozhat egy kurzortaz SQL Server példányon lakó adatbázisok nevével, anélkül, hogy helyettesítő kulcsra lenne szükség, hogy tesztállapotként működjön,mint egy WHILE hurok.,

vannak olyan negatív szempontok is, amelyeket tudnia kell, ha más hurkolási lehetőségeket használ.

  • Ha globális kurzorokat használ a kódjában, akkor azzal a kockázattal jár, hogy a kurzort bezárja valamilyen tárolt eljárás, amely a kódjába van ágyazva.
  • általában kurzorok kevesebb teljesítményt, mint egy egyenértékű hurok segítségével WHILEloop vagy CTE.

előnyök és hátrányok egy While hurok használatával az SQL Server

táblázat sorain keresztül történő Iterációhoz a Kurzorhoz képest egy WHILE hurok használata is előnyös.,

  • míg a hurkok gyorsabbak, mint a kurzorok.
  • míg a hurkok kevesebb zárat használnak, mint a kurzorok.
  • kevesebb használata Tempdb: míg hurkok nem hoz létre egy másolatot az adatok intempdb, mint a kurzor nem. Ne feledje, hogy a kurzorok, attól függően, hogy milyen lehetőségek vannaka létrehozásukhoz a temp táblázatok létrehozását okozhatja.

a következő lista részletezi a hurkok negatív aspektusait.

  • az előre vagy hátra mozgás összetett: az aloop-ban előre vagy hátra történő mozgatáshoz dinamikusan meg kell változtatnia az iterációs állapotot a hurok belsejében.,Ez extra gondosságot igényel; különben végtelen hurokba kerülhet.
  • egy végtelen hurok kockázata: a Kurzorhoz képest nincsegy rögzített adatkészlet a hurokhoz (azaz a kiválasztott állapot által visszaküldött adatoka kurzor nyilatkozatban), ehelyett when A WHILE loop használatakor meg kell határoznia egy korlátotegy olyan kifejezéssel, amelyet true vagy false értékelnek.

A tesztkörnyezet kialakítása a Kurzorokhoz és hurkokhoz

ennek teszteléséhez egy táblázatot fogok használni, amely egy identitás oszlopot (CursorTestID),egy varchar oszlopot (Filler) és egy bigint oszlopot (RunningTotal) tartalmaz.,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO

az ötlet az, hogy hurok keresztül a táblázat sorai által megrendelt Cursorttestid oszlopés frissítse a RunningTotal oszlop összege CursorTestID oszlop értékés az érték a RunningTotal oszlop az előző sorban.

de az indítás előtt először létre kell hoznunk néhány tesztsort a következő szkripttel.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

a fenti szkripten észre fogod venni, hogy csak egyetlen beszúrási nyilatkozatot használtam, és kihasználtam a kötegelt elválasztót (a GO 500000 parancs), mint rövidethogy végrehajtsam ezt a beszúrási nyilatkozatot 500000 alkalommal. Többet olvashat erről a módszerrőlhogy ismételje meg a kötegelt végrehajtást ezen a tippen: T-SQL köteg végrehajtása többször a GO használatával.

példa egy alapvető kurzorra, amely az SQL Server

Táblázatsorokon keresztül hurok, hozzon létre egy kurzort a RunningTotal oszlop kitöltéséhez. Vegye figyelembe a nextscript-en, hogy a kurzort a fast_forward opcióval deklaráltam., Ez a kurzor teljesítményének növelése érdekében történik, mivel a Microsoft theFAST_FORWARD argumentuma szerint “egy FORWARD_ONLY, READ_ONLY kurzort ad meg a performanceoptimizations engedélyezve”. Más szóval, az SQL Server használatát utasítjukegy csak olvasható kurzor, amely csak az elsőtől haladhat előre, és az utolsó sorból görgethető.

DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

a következő kép egy képernyőfelvétel, amely a szkript végrehajtását mutatja above.As három perc és öt másodperc kellett a tesztasztal 500 000 sorának frissítéséhez.,

példa egy alapvető While Loop ciklusra az SQL Server

Táblázatsorokon keresztül most átírom az előző szkriptet, elkerülve a kurzor használatát. Nem fogja észrevenni, hogy tartalmaz Egy While hurkot, amely majdnem megegyezik a thecursor szkriptben lévővel. Ez, ahogy korábban mondtam, mert még akkor is, ha kurzorokkal dolgozikegy iteratív vezérlőszerkezetet kell használnia.

a következő kép a fenti szkript végrehajtásának képernyőképe. Ez tookless ideje futtatni a while hurok, mint a kurzor.,

egy másik SQL Server kurzor példa

vegyük például a kurzort a tipStandardize SQL Server adatok szöveges lookup and replace funkció. Egy szó ofadvice, annak érdekében, hogy futtatni ezt a kódot, akkor kövesse a lépéseket a tipp, hogy létrehozzaa tesztkörnyezet.

és itt van a kurzor kódja:

Ha ezt a kódot boncoljuk, láthatjuk, hogy van egy kurzor, amely átmegyaz alábbiakban lemásolt táblázatos termékek.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products

SQL Server Cursor példa átalakítva egy While Loop

annak érdekében, hogy cserélje ki ezt a kurzort egy WHILE LOOP, meg kell, hogy hozzon létre egy temporarytable, hogy végre egy tally tábla. Azok számára, akik nem tudják, mi az a tallytable, táblázatként definiálhatjuk, amely egy kulcsból és annak értékéből álló oszlopot tartalmaz. A mi konkrét esetben fogjuk használni a szekvenciális integerkey kezdve 1, így tudjuk használni, mint egy iterátor. Ezt a kulcsot a Terméktáblából származó termékhez társítják.,

először, mivel a Terméktáblán a ProductID kulcs van meghatározva, mint egy identityakkor lehet, hogy a kísértés, hogy megkerülje ezt a lépést, de figyelembe kell vennie, hogy egy valós esetbenegy sor törölhető lett volna, ezért nem fogja tudni használni az identitycolumn mint iterátor. Ezenkívül egy sor törölhető a kód futtatása közben, ami végrehajtási hibákhoz vezethet. Ennek elkerülése érdekében hozzá fogjuk adni apry-CATCHblock-ot. Tovább folytatom.

a WHILE hurok indítása előtt be kell állítanunk a Start és stop állapotot., Ehhez hozzáadtam két új egész változót, a @Iterator-t és a @MaxIterator-t.A @MaxIterator változót arra használjuk, hogy a #TallyTabletable-ben megtartsuk az elemek számát, és csak egyszer állítjuk be az értékét a hurok indítása előtt. A @Iterator variabilis inicializálva 1, ahogy definiáltuk, mint a kiindulási szám a szekvencia ésminden iterációnál növelni fogjuk az értékét.

következő lépések
  • új vagy a kurzoroknál, és szükséged van némi gyakorlásra? A nextip-ben talál egy magyarázatot, egy könnyen érthető kurzorpéldát és többajánlott olvasást: SQL Server kurzor példa.,
  • ha azt szeretnénk, hogy konvertálni a meglévő kurzorok a kódot beállítani alapú lekérdezések, vessen egy pillantást a chapterSQL Server Convert Cursorto Set alapján theSQL Server Database Design legjobb gyakorlatok bemutató.
  • szüksége van egy másik példára a While loop használatához? Vessen egy pillantást erre a tipprehogy megmutatja, hogyan lehet felosztani a DML-nyilatkozatokat tételekben: optimalizálja a nagy SQL ServerInsert-t, frissítse és törölje a folyamatokat tételekkel.
  • abban az esetben, ha nem tudja, hogyan kell használni TRY…CATCH exceptionhandling, nézd meg ezt a tippet: SQL Server próbálja elkapni Kivétel kezelése.,
  • stay tuned to Thesql Server T-SQL Tipscategory hogy minél több kódolási ötletek.

Utolsó frissítése: 2019-09-12

a szerzőről
Daniel Farina-ben született Buenos Airesben, Argentínában. Önképzettség, gyermekkora óta szenvedélyt mutatott a tanulás iránt.
tekintse meg az összes tippemet
kapcsolódó források

  • további adatbázis-Fejlesztő tippek…

Vélemény, hozzászólás?

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöltük