Använd while-loop med cursors i T-SQL
Förord
Avancerad Transact-SQL – DEL 1 Whileloop med Cursor När man arbetar mot väldigt stora databaser så kan det ibland passa väldigt bra att använda en cursor i TSQL. Det handlar oftast om paging där tabellerna är stora nog för att dra ner responstiden, men det finns många andra områden där det passar sig väldigt bra. Paging ska jag gå in på i nästa del, nu börjar vi lite enklare.Innehåll
»Följande exempel skulle gå att lösa på många andra sett men det är ett lättarbetat scenario.
Låt oss säga att vi har en webbsida med en enorm massa konstverk. Alla kan gå in och ge ett guld till det konstverk som de tycker bäst om. Varje dag ska en column i konstverkstabellen uppdateras med konstverkens ”topplistplacering” baserat på hur många guld de fått.
En lösning vore att bara göra hämta hela tabellen med en SELECT * order by GULD och sedan lösa det i programmet med en loop som uppdaterar varje rad med dagens placering. Detta skulle innebära att hela tabellen och uppdateringar skulle fara mellan programmet och sql servern, vilket skulle innebära en prestanda förlust. Som sagt, detta är ett ganska dåligt exempel för det finns ju andra sätt att lösa det hela på men nu ska vi göra det med en CURSOR.
Här deklarerar vi vår CURSOR med hjälp av en SELECTsats. Själva CURSORN kan vi sedan använda för att gå fram eller tillbaka mellan raderna som returneras. I detta fall ska vi bara loppa framåt genom ”guld-topplistan”.
Som ni lätt utläser här så startar vi vår CURSOR genom OPEN. Sedan flyttas vår CURSOR till första raden med hälp av Fetch Next .
Är står för själva while-loopen och den ska fortsätta tills @@fetch_status blir -1 (dvs, det finns inga rader kvar att hämta)
Här använder vi en vanlig UPDATE för att uppdatera kolumnen ”placering” med @@rowcount -radnumret where current of MINCURSOR - där MINCURSOR befinner sig just nu. Avlutningsvis stänger vi vår CURSOR och reslutaten är nu uppdaterade! Det här var menat som en mjukstart för er som aldrig rört en CURSOR. Nästa gång blir det värre.
TSQL Whileloop med Cursor
Låt oss säga att vi har en webbsida med en enorm massa konstverk. Alla kan gå in och ge ett guld till det konstverk som de tycker bäst om. Varje dag ska en column i konstverkstabellen uppdateras med konstverkens ”topplistplacering” baserat på hur många guld de fått. En lösning vore att bara göra hämta hela tabellen med en SELECT * order by GULD och sedan lösa det i programmet med en loop som uppdaterar varje rad med dagens placering. Detta skulle innebära att hela tabellen och uppdateringar skulle fara mellan programmet och sql servern, vilket skulle innebära en prestanda förlust. Som sagt, detta är ett ganska dåligt exempel för det finns ju andra sätt att lösa det hela på men nu ska vi göra det med en CURSOR.
Declare MINCURSOR Cursor for
SELECT *
FROM Konsverk
order by GULD desc
for Read Only;
Här deklarerar vi vår CURSOR med hjälp av en SELECTsats. Själva CURSORN kan vi sedan använda för att gå fram eller tillbaka mellan raderna som returneras. I detta fall ska vi bara loppa framåt genom ”guld-topplistan”.
Open MINCURSOR;
Fetch Next from firstQuery
WHILE (@@fetch_status <> -1)
BEGIN
UPDATE Konsverk set
placering=@@rowcount
where current of MINCURSOR;
Fetch Next from MINCURSOR;
END;
close MINCURSOR;
deAllocate MINCURSOR;
Som ni lätt utläser här så startar vi vår CURSOR genom OPEN. Sedan flyttas vår CURSOR till första raden med hälp av Fetch Next .
WHILE (@@fetch_status <> -1)
Är står för själva while-loopen och den ska fortsätta tills @@fetch_status blir -1 (dvs, det finns inga rader kvar att hämta)
UPDATE Konsverk set
placering=@@rowcount
where current of MINCURSOR;
Här använder vi en vanlig UPDATE för att uppdatera kolumnen ”placering” med @@rowcount -radnumret where current of MINCURSOR - där MINCURSOR befinner sig just nu. Avlutningsvis stänger vi vår CURSOR och reslutaten är nu uppdaterade! Det här var menat som en mjukstart för er som aldrig rört en CURSOR. Nästa gång blir det värre.
0 Kommentarer