Använda markörer och loopar i Transact-SQL. Markörer i MySQL lagrade procedurer Markörer i sql server
Kommandot DECLARE CURSOR låter dig hämta poster från en tabell rad för rad för manipulering. Detta möjliggör rad-för-rad-bearbetning istället för den traditionella datauppsättningsbearbetning som SQL gör.
Som en allra första approximation används följande steg när du arbetar med markören.
Markören skapas med kommandot DECLARE. Markören öppnas med kommandot OPEN.
Operationer med markören utförs med kommandot FETCH. Markören stängs med kommandot CLOSE.
Kommandot DECLARE CURSOR specificerar en SELECT-sats. Varje rad som returneras av SELECT-satsen kan hämtas och bearbetas individuellt. Följande Oracle-exempel deklarerar en markör i ett deklarationsblock tillsammans med flera andra variabler. Efter detta, i det efterföljande BEGIN...END-blocket, öppnas markören, ett val görs från den och markören stängs.
CURSOR title_price_cursor ÄR SELECT titel, pris FRÅN titlar
DÄR priset INTE ÄR NULL; title_price_val title_price_cursor ROWTYPE; nytt_pris NUMMER(10.2);
OPEN title_price_Cursor;
HÄMTA title_price_cur-sor INTO title_price_val;
new_price:= "title_price_val.price" * 1,25 INSERT I new_title_price VALUES
(title_price_val.title, new_price) STÄNG title_price_cursor; SLUTET;
Eftersom det här exemplet använder PL/SQL kommer vi inte att förklara mycket av koden i den här boken. Dock visar DECLARE-blocket tydligt markördeklarationen. I ett körbart PL/SQL-block initieras markören med OPEN-kommandot, värdena hämtas med FETCH-kommandot och slutligen stängs markören med CLOSE-kommandot.
SELECT-satsen är grunden för markören, så det är bra att testa det noggrant innan du inkluderar det i DECLARE CURSOR-satsen. SELECT-satsen kan fungera på den underliggande tabellen eller vyn. Därför kan skrivskyddade markörer fungera med icke-uppdateringsbara vyer. En SELECT-sats kan innehålla satser som ORDER BY, GROUP BY och HAVING så länge dessa satser inte uppdaterar källtabellen. Om markören är definierad som FOR UPDATE, rekommenderas det att ta bort sådana satser från SELECT-satsen.
Lokala markörer används ofta som utdataparametrar till lagrade procedurer. Därför kan du definiera och fylla i en markör i en lagrad procedur och skicka den till batchjobbet eller lagrad procedur som anropade den.
I följande enkla DB2-exempel kommer vi att deklarera en markör som slår upp avdelningsnummer, avdelningsnamn och chefsnummer i admin_gruppen "XO1".
DECLARE dept_cursor CURSOR
FÖR SELECT dept_nbr, dept_name, mgr_nbr
WHERE admin_group="X01"
BESTÄLL AV d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;
Följande Microsoft SQL Server-exempel deklarerar och öppnar en markör för utgivartabellen. Markören väljer den första posten som matchar SELECT-satsen från utgivartabellen och infogar den i en annan tabell. Den går sedan vidare till nästa post, sedan nästa, tills alla poster har behandlats. Slutligen stängs markören och minnet frigörs (kommandot DEALLOCATE används endast i Microsoft SQL Server).
DECLARE @publisher_name VARCHAR(20)
DECLARE pub_cursor CURSOR FÖR SELECT pub_name FRÅN publicister WHERE land "USA"
HÄMTA NÄSTA FRÅN pub_cursor INTO förlagsnamn
WHILE @s>FETCH_STATUS=0
INSERT INTO foreign_publishers VALUES("j>publisher_name)
CLOSE pub_cursor DEALLOCATE pub_cursor
I det här exemplet kan du se markören röra sig genom en uppsättning poster. (Detta exempel är endast avsett att demonstrera idén, eftersom det i verkligheten finns Det bästa sättet lösning på detta problem, nämligen INSERT, SELECT-instruktionen.)
DET GÄLLER: SQL Server (sedan 2008) Base SQL-data Azure SQL Data WarehouseParallel Data Warehouse
Definierar attribut för en Transact-SQL-servermarkör, såsom vyegenskaper och frågan som används för att bygga resultatuppsättningen som markören arbetar på. DECLARE CURSOR-satsen stöder både ISO-standardsyntax och syntax som använder Transact-SQL-språktilläggsuppsättningen.
ISO syntax DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FÖR (LÄS ENDAST | UPPDATERA [AV kolumnnamn [,...n]])] [;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOKAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATISK | NYCKELSET | DYNAMISK | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FÖR select_statement [ FÖR UPPDATERING [ AV kolumnnamn [ ,...n ] ] ] [;]
cursor_name
cursor_name
OKÄNSLIG
tempdb; sålunda återspeglas inte ändringar i de underliggande tabellerna i data som returneras av denna markörs val, och den här markören kan inte ändras. När du använder ISO-syntax, såvida inte alternativet INSENSITIVE är specificerat, visas bekräftade uppdateringar och raderingar gjorda av bastabellerna i efterföljande val.
SKROLLA
Indikerar att alla samplingsalternativ är tillgängliga (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Om ISO DECLARE CURSOR-satsen inte anger ett SCROLL-alternativ, stöds endast alternativet NEXT fetch. Alternativet SCROLL kan inte specificeras med alternativet FAST_FORWARD.
select_statement
En standard SELECT-sats som anger resultatuppsättningen för en markör. Nyckelorden FOR Browse och INTO är inte tillåtna i select_statement markördeklaration.
select_statement konflikt med en markör av den begärda typen.
LÄS ENDAST
Uppdatering ]
kolumnnamn [,
.. .n] anges, tillåter endast de listade kolumnerna ändringar. Om UPDATE-satsen används utan en lista med kolumner, är uppdateringen möjlig för alla kolumner.
cursor_name
Transact-SQL-namnet för den specifika servermarkören. cursor_name måste följa reglerna för identifierare.
LOKAL
Indikerar att markören är lokal för paketet, den lagrade proceduren eller triggern där den skapades. Markörnamnet är endast giltigt inom detta område. En markör kan refereras av lokala paketvariabler, lagrade procedurer, triggers eller utdataparametern för en lagrad procedur. OUTPUT-parametern används för att skicka en lokal markör till det anropande paketet, den lagrade proceduren eller triggern, som sedan kan tilldela parametern till en markörvariabel för efterföljande åtkomst till markören efter att den lagrade proceduren är klar. Markören släpps implicit när batchen, den lagrade proceduren eller triggern slutför exekveringen, såvida inte markören skickades till parametern OUTPUT. Om markören skickades till parametern OUTPUT, släpps markören när alla variabler som refererar till den frigörs eller när scopet lämnas.
GLOBAL
Indikerar att markören är global för anslutningen. Markörnamnet kan användas av alla lagrade förfaranden eller paket som körs på anslutningen. Markören släpps implicit endast om anslutningen bryts.
FORWARD_ONLY
Anger att markören endast kan ses från första till sista raden. Endast hämtningsalternativet FETCH NEXT stöds. Om FORWARD_ONLY anges utan nyckelorden STATIC, KEYSET eller DYNAMIC, beter sig markören som en DYNAMISK markör. Om varken FORWARD_ONLY-argumentet eller SCROLL-argumentet anges, är standardargumentet FORWARD_ONLY såvida inte nyckelorden STATIC, KEYSET eller DYNAMIC finns. STATIC, KEYSET och DYNAMIC markörer har ett standardvärde på SCROLL. Till skillnad från databas-API:er som ODBC och ADO, stöds FORWARD_ONLY-läget av följande Transact-SQL-markörer: STATIC, KEYSET och DYNAMIC.
STATISK
Definierar en markör som skapar en tillfällig kopia av data för användning av markören. Alla frågor till markören kommer åt den angivna temporära tabellen i tempdb; sålunda återspeglas inte ändringar i de underliggande tabellerna i data som returneras av denna markörs val, och den här markören kan inte ändras.
KEYSET
Indikerar att medlemskapet eller ordningen för raderna i markören är oförändrad när den öppnas. En uppsättning nycklar som unikt identifierar rader är inbyggd i tabellen tempdb kallad nycklar.
Ändringar av icke-nyckelvärden i bastabeller gjorda av markörägaren eller utförda av andra användare visas när markörägaren visar den. Ändringar gjorda av andra användare återspeglas inte (ändringar kan inte göras med hjälp av Transact-SQL-servermarkören). Om en rad raderas, returnerar ett försök att hämta rader @@FETCH_STATUS -2. Uppdateringar av nyckelvärden över markörgränserna liknar att ta bort en gammal rad och sedan infoga en ny rad. Raden med de nya värdena är inte synlig och försöker hämta raden med de gamla värdena returnerar @@FETCH_STATUS -2. Uppdateringar är synliga omedelbart om de görs genom markören med hjälp av WHERE CURRENT OF-satsen.
DYNAMISK
Definierar en markör som visar alla dataändringar som gjorts på rader i resultatuppsättningen medan du tittar på den här markören. Datavärden, ordning och radmedlemskap i varje val kan variera. Alternativet ABSOLUT stöds inte av dynamiska markörer.
SNABBSPOLA
Indikerar en FORWARD_ONLY, READ_ONLY markör som har prestandaoptimering aktiverad. Alternativet FAST_FORWARD kan inte anges med alternativen SCROLL eller FOR_UPDATE.
READ_ONLY
Förhindrar ändringar som görs med den här markören. WHERE CURRENT OF-satsen kan inte referera till en markör i en UPDATE- eller DELETE-sats. Det här alternativet har företräde framför standardfunktionen för marköruppdatering.
SCROLL_LOCKS
Indikerar att positionerade uppdateringar eller raderingar som görs med markören garanterat kommer att lyckas. SQL Server låser rader när de läses in i markören för att säkerställa att dessa rader är tillgängliga för efterföljande ändringar. Alternativet SCROLL_LOCKS kan inte specificeras med alternativet FAST_FORWARD eller STATIC.
OPTIMISTISK
Anger att positionerade uppdateringar eller borttagningar som görs med markören kommer att misslyckas om raden har uppdaterats sedan den lästes in i markören. SQL Server låser inte rader när de läses in i markören. Jämförelser används istället tidsstämpel kolumnvärden eller kontrollsummor, om inte i tabellen tidsstämpel kolumn för att avgöra om raden har ändrats sedan den lästes in i markören. Om raden har ändrats kommer försök till en positionerad uppdatering eller radering att misslyckas. Alternativet OPTIMISTISK kan inte specificeras med alternativet FAST_FORWARD.
TYPE_WARNING
Anger att en varning kommer att skickas till klienten om markören implicit konverteras från en begärd typ till en annan.
select_statement
En standard SELECT-sats som anger resultatuppsättningen för en markör. Nyckelorden COMPUTE, COMPUTE BY, FOR Browse och INTO är inte tillåtna i select_statement markördeklaration.
SQL Server konverterar implicit markören till en annan typ om satserna in select_statement konflikt med en markör av den begärda typen. För mer information, se Implicita marköromvandlingar.
FÖR UPPDATERING ]
Definierar kolumnerna i markören som ska uppdateras. Om OF kolumnnamn [,
... n] tillhandahålls, endast de listade kolumnerna tillåter ändringar. Om UPDATE-satsen används utan en kolumnlista är uppdateringen möjlig för alla kolumner om inte samtidighetsalternativet READ_ONLY har angetts.
DECLARE CURSOR-satsen definierar attribut för en Transact-SQL-servermarkör, såsom vyegenskaper och frågan som används för att bygga resultatuppsättningen som markören arbetar på. OPEN-satsen fyller i resultatuppsättningen och FETCH-satsen returnerar en rad från den. CLOSE-satsen rensar den aktuella resultatuppsättningen som är kopplad till markören. DEALLOCATE-satsen frigör resurser som används av markören.
Den första formen av DECLARE CURSOR-satsen använder ISO-syntax för att specificera markörparametrar. Den andra formen av DECLARE CURSOR-satsen använder tillägg till Transact-SQL-språket som låter dig definiera markörer med samma typer som de som används i markörfunktionerna för databas-API:er som ODBC och ADO.
Dessa två former kan inte blandas. Om du anger SCROLL eller utelämnar nyckelord före nyckelordet CURSOR, kan du inte använda nyckelord mellan CURSOR och även för select_statement nyckelord. När du anger nyckelord mellan CURSOR, såväl som för select_statement nyckelord kan du inte ange SCROLL eller INSENSITIVE före nyckelordet CURSOR.
Om du använder Transact-SQL-syntax för DECLARE CURSOR-satsen och inte anger alternativen READ_ONLY, OPTIMISTIC eller SCROLL_LOCKS, antas följande standardvärde.
Om SELECT-satsen inte stöder uppdateringar (eller har otillräckliga behörigheter, eller har åtkomst till fjärrtabeller som inte stöder uppdateringar, etc.), ställs markören in på READ_ONLY.
STATIC och FAST_FORWARD markörer är som standard READ_ONLY.
DYNAMIC och KEYSET-markörer är som standard OPTIMISTISK.
Markörer kan endast refereras av andra Transact-SQL-satser. Databas API-funktioner kan inte referera till markörer. Till exempel, när en markör har deklarerats kan OLE DB-, ODBC- eller ADO-funktioner och -metoder inte referera till dess namn. Markörrader kan inte väljas med motsvarande API-funktioner och metoder; För detta ändamål måste du använda Transact-SQL FETCH-satser.
Följande lagrade procedurer kan användas för att definiera egenskaperna för en markör efter att den har deklarerats.
Variabler kan användas som en del select_statement, där markören deklareras. Värdena på markörvariabler ändras inte efter att de har deklarerats.
Som standard ges DECLARE CURSOR-behörigheter till alla användare som har SELECT-behörighet för de vyer, tabeller och kolumner som används av markören.
Du kan inte använda markörer eller utlösare i en tabell med ett klustrat kolumnlagerindex. Denna begränsning gäller inte för icke-klustrade index; Du kan använda markörer och utlösare på en tabell med ett icke-klustrat kolumnlagerindex.
A. Använda en enkel markör och syntax
Resultatuppsättningen som skapas när du öppnar den här markören inkluderar alla rader och kolumner i tabellen. Denna markör kan uppdateras, alla uppdateringar och raderingar representeras i valet för denna markör. FETCH``NEXT hämtas endast eftersom parametern SCROLL inte specificerades.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor HÄMTA NÄSTA FRÅN vend_cursor;
B. Använda kapslade markörer för att visa en rapport
Följande exempel använder kapslade markörer för att visa en komplex rapport. En intern markör deklareras för varje leverantör.
STÄLL IN NOCOUNT PÅ ;
DECLARE @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 ); SKRIVA UT" -------- Rapport om leverantörsprodukter --------";
DECLARE vendor_cursor CURSOR FÖR VÄLJ leverantörs-ID, namn FRÅN Inköp.Vendor WHERE PreferredVendorStatus = 1 BESTÄLLNING EFTER leverantörs-ID;ÖPPNA vendor_cursor HÄMTA NÄSTA FRÅN vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BÖRJA UTSKRIFT " " VÄLJ @meddelande = "----- Produkter från leverantören: "+ @leverantörsnamn PRINT @meddelande -- Deklarera en inre markör baserad
-- på vendor_id från den yttre markören. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variabelt värde från den yttre markören OPEN product_cursor HÄMTA NÄSTA FRÅN product_cursor INTO @product IF @@FETCH_STATUS<>0 SKRIV UT "<
Markören är en länk till det kontextuella minnesområdet. I vissa implementeringar av SQL-programmeringsspråket (Oracle, Microsoft SQL Server) - resultatuppsättningen som erhålls när en fråga körs och den aktuella postpekaren som är associerad med den. Jag skulle säga att en markör är en virtuell tabell som representerar en alternativ datalagring. I det här fallet låter markören dig komma åt dess data som om det vore data från en vanlig array.
Markörer används i lagrade procedurer. Nog med teori, låt oss titta på ett exempel:
Vi har en databas (databasen är lite dålig, det här är en av mina laboratoriearbete, men vår databaslärare insisterade på en sådan struktur)
/*Bank Information*/
SKAPA TABELL "bank" (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
PRIMÄRNYCKEL ('BankId'))ENGINE=InnoDB
TECKENSET "utf8" SAMLA "utf8_bin" ;
/*data om insättningar */
SKAPA TABELL "bankdistribution" (
`BankId` INTEGER (11) INTE NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) INTE NULL ,
`ClientId` INTEGER (11) INTE NULL ,
PRIMARY KEY(`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCER `bank` (`BankId`),
BEGRÄNSNING `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCER `client` (`ClientId`)
)ENGINE=InnoDB
/*data om investerare*/
SKAPA TABELL "klient" (
`ClientId` INTEGER (3) INTE NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Efternamn` VARCHAR (50) COLLATE utf8_bin INTE NULL DEFAULT "" ,
`Namn` VARCHAR (50) COLLATE utf8_bin INTE NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin INTE NULL DEFAULT "" ,
`Telefon` VARCHAR (50) COLLATE utf8_bin INTE NULL DEFAULT "" ,
`Adress` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) INTE NULL ,
PRIMARY KEY(`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 TECKENSET "utf8" SAMLA "utf8_bin"
Låt oss säga att vi behöver ta emot varje bank i tur och ordning och utföra några åtgärder med den, följande fråga kan hjälpa oss med detta
Välj `bank`.* FRÅN `bank` LIMIT NUMBER OF THE_RECORD_WE NEED,1. Med hjälp av LIMIT WE NEED_RECORD NUMBER, 1 extraherar vi alltså varje post i en loop från banktabellen och utför de åtgärder vi behöver med den, samtidigt som vi ökar värdet på WE NEED_RECORD NUMBER med 1. Nu kommer vi att göra samma sak men med en markören
Börja
/* variabler där vi extraherar data */
Deklarera vBankId heltal ;
Deklarera vBankName VARCHAR(50);
Deklarera vAddress VARCHAR(50);
Deklarera vPhone VARCHAR (50);
/* hadler variabel - a*/
Deklarera gjort heltal standard 0;
/*Markördeklaration*/
Deklarera BankCursor Cursor för Välj `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` där 1;
/*HANDLER syfte, vilket kommer att förklaras nedan*/
DEKLARERA FORTSÄTT HANDLER FÖR SQLSTATE "02000" SET done=1;
/* öppna markören */
Öppna BankCursor;
/*hämta data*/
MEDAN gjort = 0 DO
vi vidtar de åtgärder vi behöver
AVSLUTA MED ;
/*stänger markören*/
Stäng BankCursor;
SLUTET ;* Den här källkoden har markerats med Source Code Highlighter.
Fel: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Meddelande: Inga data - noll rader hämtade, markerade eller bearbetade
SQLSTATE: 02000 aktiveras när slutet av markören nås, eller när select eller update returnerar en tom sträng.
Nästa rad deklarerade vi markören DECLARE cursor_name CURSOR FOR select_statement;
Öppna markören Öppna cursor_name;
Sedan, tills vi når slutet av markören (MEDAN gjort = 0 DO), extraherar vi data och bearbetar den.
Du måste stänga markören innan du avslutar den lagrade proceduren. Stäng cursor_name;
Det verkar inte komplicerat. Men det finns många fallgropar förknippade med SQLSTATE "02000".
MEDAN gjort = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
Välj (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution där BankId = vBankId limit 1;
vi gör några åtgärder
AVSLUTA MED ;* Den här källkoden har markerats med Source Code Highlighter.
Allt är bra och korrekt ur syntaxsynpunkt. Men ur en logisk synvinkel, nej. Det kan hända att insättare inte har öppnat konton i någon bank, då för Välj (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution där BankId = vBankId limit 1; SQLSTATE: 02000 avfyras, done-variabeln sätts till 1 och while-loopen slutar tidigare än vi förväntat oss. Detta kan undvikas genom att göra följande
MEDAN gjort = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extrahera för banken beloppet för någon av dess insättningar */
if (vContributeAmountSUM > 0) då
/* extrahera för banken beloppet för någon av dess insättningar */
sluta om ;
vi gör några åtgärder
AVSLUTA MED ;* Den här källkoden har markerats med Source Code Highlighter.
Med den första begäran kontrollerade vi om det finns bidrag (om det inte finns några, då vContributeAmountSUM == 0) och endast om det finns några, hämtar vi data.
Låt oss nu säga att vi måste ta bort det totala beloppet på konton i olika banker för varje kund
Deklarera ClientSummCursor Cursor för Välj summa
Deklarera ClientSummCursor Cursor för Välj summa (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FRÅN `bankdistribution` Inner Join client på (client.ClientId = bankdistribution.`ClientId`) där 1 grupp efter `bankdistribution`. `ClientId`;Öppna ClientSummCursor;
MEDAN gjort = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extrahera för banken beloppet för någon av dess insättningar */
Välj Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution där BankId = vBankId limit 1;
/* kontrollera om det verkligen finns insättningar på denna bank */
if (vContributeAmountSUM > 0) då
/* extrahera för banken beloppet för någon av dess insättningar */
Välj ContributeAmount INTO vContributeAmountSUM FROM bankdistribution där BankId = vBankId limit 1;
sluta om ;
vi gör några åtgärder.
AVSLUTA MED ;* Den här källkoden har markerats med Source Code Highlighter.
Samma situation kan uppstå när data i ClientSummCursor-markören slutar tidigare än data i BankCursor, SQLSTATE: 02000 triggas, done-variabeln sätts till 1 och while-loopen slutar tidigare än vi förväntat oss. Detta kan undvikas genom att göra följande
Öppna ClientSummCursor;
MEDAN gjort = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extrahera för banken beloppet för någon av dess insättningar */
Välj Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution där BankId = vBankId limit 1;
/* kontrollera om det verkligen finns insättningar på denna bank */
if (vContributeAmountSUM > 0) då
/* extrahera för banken beloppet för någon av dess insättningar */
Välj ContributeAmount INTO vContributeAmountSUM FROM bankdistribution där BankId = vBankId limit 1;
sluta om ;
/* innan du extraherar data från den andra markören, kom ihåg sqlstate-tillståndet */
SET old_status = klar;
/* extrahera data vi behöver */
FETCH ClientSummCursor INTO vSum,vClientId;
/* kontrollera om data hämtades och om sqlstate 0200 misslyckades */
om (klar = 0) då
vi gör några åtgärder.
sluta om ;
/* före slutet av stunden, återställ värdet för den klara variabeln */
set done = old_status;
AVSLUTA MED ;* Den här källkoden har markerats med Source Code Highlighter.
Tack till alla som har läst så här långt, jag hoppas att detta kommer att vara användbart för någon.
Implementeringen av en markör i en databas liknar en Java-klass som har en uppsättning data och metoder för att bearbeta den. Vart i sql-markör använder data som en vanlig array. Markörer kan användas i triggers, lagrade procedurer och funktioner.
I enlighet med SQL-standarden, när du arbetar med markörer, utförs följande grundläggande åtgärder:
- markördeklaration;
- öppna en markör med läsning av data;
- rad för rad sampling av data från markören;
- ändra raddata med hjälp av markören;
- stänger markören, varefter den blir otillgänglig;
- släpper markören, dvs. att ta bort en markör från minnet eftersom att stänga den inte nödvändigtvis frigör minnet som är associerat med det.
I olika implementeringar definitionen markören kan ha vissa skillnader. Till exempel, ibland är det nödvändigt att explicit frigöra minnet som tilldelats för en markör. När markören är fri, frigörs också minnet som är associerat med den. Detta gör det möjligt att återanvända markörnamnet. I andra implementeringar, när markören är stängd, frigörs minne implicit.
I vissa fall kan du inte göra utan att använda en markör. Om möjligt bör du dock undvika att använda en markör och arbeta med vanliga databearbetningskommandon: SELECT, UPDATE, INSERT, DELETE. Detta beror på det faktum att markörer inte tillåter modifieringsoperationer på hela datavolymen och att hastigheten för att utföra databehandlingsoperationer med hjälp av en markör är märkbart lägre än för standardmedel SQL.
Om ett program kan ändra data som laddas in i markören, kallas det modifierbart. När vi pratar om markörer bör vi inte glömma transaktionsisolering. En användare ändrar en post med hjälp av en markör, medan en annan användare läser posten med sin egen markör. Dessutom kan han ändra samma post, vilket gör det nödvändigt att upprätthålla dataintegriteten.
Deklarerar en markör
Markörer måste deklareras innan de kan användas. SQL-standarden använder följande syntax för att skapa en markör:
Deklarera cursor_name cursor för select_statement ])]
Detta uttryck deklarerar en markör deklarera markören med namnet "cursor_name".
OKÄNSLIG en statisk markör skapas som inte tillåter ändringar att göras. Dessutom visas inte ändringar som gjorts av andra användare. Om nyckelordet INSENSITIVE saknas skapas en dynamisk markör.
När du använder ett nyckelord SKROLLA den skapade markören kan rullas i valfri riktning, så att du kan använda valfri valkommandon. Om detta argument utelämnas kommer markören att vara sekventiell, dvs. dess visning kommer bara att vara möjlig i en riktning - från början till slut.
Uttryck select_statement indikerar en struktur för att läsa information som välj ... från ... . Den får inte innehålla operatören in i, eftersom markören har sin egen operator hämta för att fylla variabler med markördata.
När du anger ett argument FÖR READ_ONLY en skrivskyddad markör kommer att skapas och inga ändringar av data kommer att tillåtas. En dynamisk markör kan deklareras som en skrivskyddad markör, vilket gör att ändringar som gjorts av en annan användare kan visas.
Skapa en markör med ett argument FÖR UPPDATERING låter dig göra ändringar i data i markören antingen i specificerade kolumner eller, i frånvaro av ett argument OF kolumnnamn, i alla kolumner.
Du kan deklarera flera markörer i en subrutin. Men varje markör måste ha ett unikt namn. För att öppna en markör måste du använda operatorn öppen som öppnar den tidigare deklarerade markören:
Markören öppen
SQL definierar följande syntax för att öppna en markör "markör öppen":
Öppna cursor_name;
Hämtar data från en markör, markörhämtning
Syntaxen för att läsa data från en markör till vissa variabler är följande:
Hämta markörnamn till var_namn [, var_namn] ...;
Operatör hämta väljer öppna markördata till variabler som finns efter in i och flyttar markören till nästa position.
Stäng markören
Operatör stänga stänger markören. Om operatören inte är explicit specificerad stängs markören automatiskt när motsvarande programblock stängs.
Stäng cursor_name;
Efter stängning blir markören oåtkomlig. Vid stängning släpps alla lås som installerats medan markören kördes. Endast öppna markörer kan stängas. En stängd men inte släppt markör kan öppnas igen. Det är inte tillåtet att stänga en oöppnad markör.
Varje DBMS har sina egna särdrag för att använda en markör.
Funktioner för att använda markörer i Oracle
Det finns fyra markörattribut i PL/SQL %HITTADES, %HITTADES INTE, %ÄR ÖPPEN Och %ROWCOUNT. Markörattribut deklareras som operatorerna %TYPE och %ROWTYPE, till höger om markörnamnet.
%FOUND-attribut
%NOTFOUND attribut
Attributet %NOTFOUND är raka motsatsen till %FOUND.
%ISOPEN-attribut
Attributet %ISOPEN indikerar bara om markören är öppen eller inte.
%ROWCOUNT attribut
Attribut %ROWCOUNTär ett numeriskt attribut som returnerar antalet rader som läses av markören vid en viss tidpunkt.
Exempel på en SQL-markör i ett Oracle DBMS
Deklarera v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs-markör för välj id, namn, summa(comm) som komm. från chefer där data mellan "2014-11-01" och "2014-11-30" grupperas efter id, namn; börja öppna crs; loop EXIT NÄR crs%NOTFOUND; FETCH crs till v_id, v_name, v_comm; infoga i bonus(id, namn, comm) värden (crs.id, crs.name, crs.comm); ändslinga; begå; stäng crs; slutet;
Funktioner för att använda markörer i SQL-server
Markörer som används i MSSQL kan vara sekventiella eller rullningsbara. Sequential låter dig välja data i endast en riktning - från början till slut. Rullbara markörer tillåter rörelse i båda riktningarna och låter dig hoppa till en godtycklig rad i markörens resultatuppsättning.
SQL Server stöder statiska, dynamiska, sekventiella och nyckeluppsättningsdrivna markörer.
I en statisk markördesign lagras information som en ögonblicksbild vid någon tidpunkt. Därför är ändringar som gjorts i databasen av en annan användare inte synliga. Medan markören öppnas sätter servern ett lås på alla rader som ingår i dess fullständiga resultatuppsättning. En statisk markör ändras inte efter skapandet och visar alltid den datauppsättning som fanns när den öppnades. Om andra användare ändrar data som ingår i markören i källtabellen kommer detta inte att påverka den statiska markören. Det är omöjligt att göra ändringar i en statisk markör, så den öppnas alltid i skrivskyddat läge.
En dynamisk markör kräver ytterligare nätverkskostnader och mjukvaruresurser. Vid användning av dynamiska markörer skapas inte en fullständig kopia av data, utan val från källtabellerna görs endast när användaren kommer åt viss data. Under hämtningen låser servern raderna och alla ändringar som användaren gör i markörens fullständiga resultatuppsättning kommer att synas i markören. Men när markören har hämtat data, kommer ändringar gjorda av en annan användare inte längre att återspeglas i markören.
En markör som styrs av en uppsättning nycklar har egenskaper mellan statisk och dynamisk. Poster identifieras vid tidpunkten för provtagningen och på så sätt spåras ändringar. Den här typen av markör är användbar när du implementerar bakåtrullning. I det här fallet är datatillägg och raderingar inte synliga förrän informationen uppdateras och markören väljer ny version register om ändringar har gjorts i dem.
Statiska markörer används bäst för informationsbehandlingssystem, d.v.s. för rapporteringssystem eller för statistiska och analytiska ändamål. En statisk markör är bättre på att hämta stora mängder data. I system för elektroniska köp eller reservationer av objekt (platser, biljetter) är det nödvändigt att dynamiskt uppfatta uppdaterad information när ändringar görs. I sådana fall används en dynamisk markör. I dessa applikationer är mängden data som överförs vanligtvis liten och nås på individuell postnivå.
Sekventiella markörer tillåter inte att data hämtas i motsatt riktning, bara från början till slutet av markören. En sekventiell markör lagrar inte en uppsättning av alla datarader. De läses från databasen så snart ett val görs i markören, vilket gör det möjligt att dynamiskt återspegla alla ändringar som gjorts av användare i databasen med kommandona INSERT, UPDATE, DELETE. Markören läser det senaste datatillståndet.
Markördeklaration
Deklarera cursor_name cursor för SELECT_statement ]]
När du använder ett nyckelord LOKAL En lokal markör skapas som endast är synlig inom blocket, triggern, lagrad procedur eller användardefinierad funktion. Nyckelord GLOBAL, definierar en global markör som finns tills den aktuella anslutningen stängs.
Operatör FORWARD_ONLY definierar en sekventiell markör som gör att data endast kan hämtas i riktningen från den första raden till den sista. När du använder operatören SKROLLA en rullningsbar markör skapas som gör att data kan nås i valfri ordning och i vilken riktning som helst.
Markörtypen bestäms av operatorerna:
- STATIC - skapa en statisk markör;
- DYNAMISK - skapa en dynamisk markör;
- KEYSET - skapar en tangentmarkör.
Om för markören READ_ONLY ange argument SNABBSPOLA, då kommer den skapade markören att optimeras för snabb åtkomst till datan. Detta argument kan inte användas tillsammans med argument FORWARD_ONLY Och OPTIMISTISK.
Om markören skapas med operatören OPTIMISTISK, då är det förbjudet att ändra eller ta bort rader som ändrades efter att markören öppnades.
När du anger ett argument TYPE_WARNING servern kommer att rapportera en implicit förändring av markörtyp om den är inkompatibel med SELECT-frågan.
Hämtar data från en markör, hämta
Omedelbart efter att du har öppnat markören kan du få dess innehåll med följande kommando:
När du använder operatören FÖRST den första raden i markörens resultatuppsättning kommer att returneras, vilket blir den aktuella raden. När specificerat SISTA den sista raden på markören kommer att returneras. Det blir också den nuvarande linjen.
När du anger en operatör NÄSTA raden omedelbart efter den nuvarande i resultatuppsättningen kommer att returneras. Denna linje blir den aktuella linjen. Standardkommando HÄMTA använder exakt denna metod för att hämta rader.
När du anger en operatör TIDIGARE raden före den nuvarande kommer att returneras. Denna linje blir den aktuella linjen.
Operatör ABSOLUT (line_number | @line_number_variable) returnerar en rad med dess absoluta ordningsnummer i markörens fullständiga resultatuppsättning. Radnumret kan anges med en konstant eller som namnet på en variabel i vilken radnumret är lagrat. Variabeln måste vara en heltalsdatatyp. Både positiva och negativa värden anges. När du anger ett positivt värde räknas strängen från början av uppsättningen, medan ett negativt värde räknas från slutet. Den valda raden blir den aktuella raden. Om ett nollvärde anges returneras ingen rad.
Argument RELATIV (antal rader | @variabelt antal rader) returnerar linjeförskjutningen det angivna antalet rader efter den aktuella. Om du anger ett negativt antal rader kommer raden som är det angivna antalet rader före den nuvarande att returneras. Om du anger ett nollvärde returneras den aktuella raden. Den returnerade raden blir den aktuella raden.
För att öppna en global markör måste du ange ett nyckelord före dess namn GLOBAL. Markörens namn kan också anges med en variabel.
I uttryck INTO @variabelnamn [,...n] en lista med variabler definieras där motsvarande kolumnvärden för den returnerade raden kommer att lagras. Variablernas ordning måste matcha kolumnernas ordning i markören, och datatypen för variabeln måste matcha datatypen i markörkolumnen.
Ändra och radera data med hjälp av en markör
För att ändra data med hjälp av en markör måste du utfärda ett UPDATE-kommando i följande format:
I en operation kan värdena för flera kolumner i den aktuella markörraden ändras, men de måste alla tillhöra samma tabell.
För att radera data med hjälp av en markör, använd kommandot DELETE i följande format:
Som ett resultat kommer den aktuella raden i markören att raderas.
Frigör minne, deallokera
För att ta bort en markör från minnet, använd kommandot
Avallokera cursor_name;
@@FETCH_STATUS attribut
För att bestämma närvaron av rader i markören bör du använda en global variabel @@FETCH_STATUS, som tar ett värde som inte är noll om det inte finns fler rader i markören. Om uppsättningen rader ännu inte är slut, är @@FETCH_STATUS lika med noll.
Exempel på en markör i SQL-server
Deklarera @företag varchar(50), @manager varchar(50), @meddelande varchar(256); deklarera crs_clients markör lokal för valt företag, chef från kunder där stad = "Moskva" beställ efter företag, chef; print "Lista över klienter"; öppna crs_clients; hämta nästa från crs_clients till @företag, @manager; medan @@FETCH_STATUS = 0 start välj @message = "Företag " + @företag + " manager " + @manager; skriv ut @meddelande; -- flytta till nästa post hämta nästa från crs_clients till @företag, @manager; slutet; stäng crs_clients; deallokera crs_clients;
Definitionen av markören är given. En beskrivning av dess typer och beteende tillhandahålls: statiska, dynamiska, sekventiella och nyckelmarkörer. Principerna för markörkontroll beskrivs: skapa och öppna en markör, läsa data, stänga en markör. Exempel på markörprogrammering ges.Markör koncept
En fråga mot en relationsdatabas returnerar vanligtvis flera rader (poster) med data, men applikationen bearbetar bara en post åt gången. Även om det handlar om flera rader samtidigt (till exempel visa data i form av kalkylblad), är deras antal fortfarande begränsat. Dessutom, när du ändrar, tar bort eller lägger till data, är arbetsenheten serien. I denna situation kommer begreppet markör i förgrunden, och i detta sammanhang är markören en pekare till en rad.
En markör i SQL är ett område i databasminnet som är utformat för att hålla den sista SQL-satsen. Om den aktuella satsen är en databasfråga, lagras också en rad med frågedata som kallas det aktuella värdet, eller den aktuella markörraden, i minnet. Det angivna området i minnet namnges och är tillgängligt för applikationsprogram.
Vanligtvis används markörer för att välja från en databas en delmängd av informationen som lagras i den. Vid varje given tidpunkt kan en markörrad kontrolleras av applikationsprogrammet. Markörer används ofta i SQL-satser, inbyggd i applikationsprogram skrivna på procedurspråk. Vissa av dem skapas implicit av databasservern, medan andra definieras av programmerare.
I enlighet med SQL-standarden, när du arbetar med markörer, kan följande huvudåtgärder urskiljas:
- skapande eller markördeklaration;
- öppningsmarkören, dvs. fylla den med data som är lagrad i flernivåminne;
- val från markören och ändra datarader med den;
- stänger markören, varefter den blir otillgänglig för användarprogram;
- frigör markören, dvs. ta bort markören som ett objekt eftersom att stänga den inte nödvändigtvis frigör minnet som är associerat med det.
Definitionen av en markör kan variera något mellan olika implementeringar. Till exempel, ibland måste en utvecklare explicit frigöra minnet som tilldelats för en markör. Efter släpp markören dess tillhörande minne frigörs också. Detta gör det möjligt att återanvända hans namn. I andra implementeringar när stänger markören frigöring av minne sker implicit. Omedelbart efter återställning blir den tillgänglig för andra operationer: öppnar en annan markör etc.
I vissa fall är det oundvikligt att använda en markör. Om möjligt bör detta dock undvikas och arbeta med vanliga databearbetningskommandon: SELECT, UPDATE, INSERT, DELETE. Förutom det faktum att markörer inte tillåter modifieringsoperationer på hela datavolymen, är hastigheten för att utföra databearbetningsoperationer med hjälp av en markör märkbart lägre än för vanliga SQL-verktyg.
Implementering av markörer i MS SQL Server-miljö
SQL Server stöder tre typer av markörer:
- SQL-markörer används främst inom triggers, lagrade procedurer och skript;
- servermarkörer arbetar på servern och implementerar applikationsprogrammeringsgränssnittet för ODBC, OLE DB, DB_Library;
- Klientmarkörer implementeras på själva klienten. De hämtar hela resultatuppsättningen med rader från servern och lagrar den lokalt, vilket påskyndar databehandlingen genom att minska tidsspillan som spenderas på nätverksdrift.
Olika typer av fleranvändarapplikationer kräver olika typer av parallell åtkomst till data. Vissa applikationer kräver omedelbar tillgång till information om ändringar i databasen. Detta är typiskt för biljettbokningssystem. I andra fall, såsom statistiska rapporteringssystem, är datastabilitet viktig eftersom om den ständigt modifieras kommer program inte att kunna visa information effektivt. Olika applikationer behöver olika implementeringar av markörer.
I SQL Server varierar markörtyperna i de funktioner de tillhandahåller. Markörtypen bestäms när den skapas och kan inte ändras. Vissa typer av markörer kan upptäcka ändringar som gjorts av andra användare på rader som ingår i resultatuppsättningen. SQL Server spårar dock bara ändringar av sådana rader medan raden används och tillåter inte ändringar att ändras när raden redan har lästs.
Markörerna är indelade i två kategorier: sekventiell och rullningsbar. I följd låter dig välja data i endast en riktning - från början till slut. Bläddringsbara markörer ge större handlingsfrihet - det är möjligt att röra sig i båda riktningarna och hoppa till en godtycklig rad av markörens resultatuppsättning Om programmet kan modifiera de data som markören pekar på kallas det rullbar och modifierbar. På tal om markörer, vi bör inte glömma transaktionsisolering. När en användare ändrar en post, läser en annan den med sin egen markör, och dessutom kan han modifiera samma post, vilket gör det nödvändigt att upprätthålla dataintegriteten.
SQL Server stöder statisk, dynamisk, sekventiell och styrs av en uppsättning nycklar.
I schemat med statisk markör information läses från databasen en gång och lagras som en ögonblicksbild (vid någon tidpunkt), så ändringar som gjorts i databasen av en annan användare är inte synliga. Ett tag öppnar markören servern sätter ett lås på alla rader som ingår i dess fullständiga resultatuppsättning. Statisk markörändras inte efter skapandet och visar alltid den datauppsättning som fanns när den öppnades.
Om andra användare ändrar data som ingår i markören i källtabellen kommer detta inte att påverka statisk markör.
I statisk markör Det går inte att göra ändringar, så det öppnas alltid i skrivskyddat läge.
Dynamisk markör upprätthåller data i ett "live" tillstånd, men detta kräver nätverks- och mjukvaruresurser. Använder sig av dynamiska markörer en fullständig kopia av källdata skapas inte, utan ett dynamiskt urval görs från källtabellerna endast när användaren kommer åt vissa data. Under hämtningen låser servern raderna och alla ändringar som användaren gör i markörens fullständiga resultatuppsättning kommer att synas i markören. Men om en annan användare har gjort ändringar efter att markören har hämtat data, kommer de inte att återspeglas i markören.
Markören styrs av en uppsättning tangenter, är mitt emellan dessa ytterligheter. Poster identifieras vid tidpunkten för provtagning, och på så sätt spåras ändringar. Den här typen av markör är användbar när man implementerar rullning bakåt - då är tillägg och raderingar av rader inte synliga förrän informationen uppdateras, och föraren väljer en ny version av posten om ändringar har gjorts i den.
Sekventiella markörer får inte hämta data i motsatt riktning. Användaren kan bara välja rader från början till slutet av markören. Seriell markör lagrar inte en uppsättning av alla rader. De läses från databasen så snart de väljs i markören, vilket gör att alla ändringar som görs av användarna i databasen kan reflekteras dynamiskt med kommandona INSERT, UPDATE, DELETE. Markören visar det senaste tillståndet för data.
Statiska markörer ge en stabil bild av data. De är bra för informations-"lager"-system: applikationer för rapporteringssystem eller för statistiska och analytiska ändamål. Förutom, statisk markör klarar bättre än andra med att sampla stora datamängder. Däremot kräver elektroniska inköps- eller biljettbokningssystem dynamisk uppfattning om uppdaterad information när ändringar görs. I sådana fall används det dynamisk markör. I dessa applikationer är mängden data som överförs vanligtvis liten och nås på radnivå (individuell post). Gruppåtkomst är mycket sällsynt.
Markörhantering i MS SQL Server-miljö
Markörkontroll implementeras genom att utföra följande kommandon:
- DEKLARE - skapande eller markördeklaration;
- ÖPPEN - öppningsmarkören, dvs. fylla den med data;
- HÄMTA val från markören och ändra datarader med hjälp av markören;
- STÄNGA - stänger markören;
- AVDELA – frigör markören, dvs. radera markören som ett objekt.
Markördeklaration
SQL-standarden tillhandahåller följande kommando för att skapa en markör:
Användning av nyckelordet INSENSITIVE kommer att skapa statisk markör. Data ändrasär inte tillåtna, dessutom visas inte ändringar gjorda av andra användare. Om nyckelordet INSENSITIVE saknas, a dynamisk markör.
När du anger nyckelordet SCROLL kan den skapade markören rullas i valfri riktning, så att du kan använda valfri valkommandon. Om detta argument utelämnas kommer markören att vara konsekvent, dvs. dess visning kommer bara att vara möjlig i en riktning - från början till slut.
SELECT-satsen anger kroppen för SELECT-begäran, som bestämmer den resulterande uppsättningen rader för markören.
Att specificera FOR READ_ONLY skapar en skrivskyddad markör och tillåter inga ändringar av data. Den skiljer sig från statisk, även om den senare inte heller tillåter att data ändras. Kan deklareras som en skrivskyddad markör dynamisk markör, vilket gör att ändringar gjorda av en annan användare kan visas.
Genom att skapa en markör med ett FOR UPDATE-argument kan du köra i markören dataändring antingen i de angivna kolumnerna eller, i avsaknad av argumentet OF kolumnnamn, i alla kolumner.
I MS SQL Server-miljön accepteras följande syntax för kommandot för att skapa markör:
<создание_курсора>::= DECLARE cursor_name CURSOR FOR SELECT_statement ]]
Genom att använda nyckelordet LOCAL skapas en lokal markör som endast är synlig inom ramen för paketet, triggern, lagrad procedur eller användardefinierad funktion som skapade den. När ett paket, utlösare, procedur eller funktion avslutas, förstörs markören implicit. För att skicka innehållet i markören utanför konstruktionen som skapade den, måste du tilldela ett OUTPUT-argument till dess parameter.
Om nyckelordet GLOBAL anges skapas en global markör; den existerar tills den aktuella anslutningen stängs.
Om du anger FORWARD_ONLY skapas seriell markör; Data kan endast samplas i riktningen från den första raden till den sista.
Genom att specificera SCROLL skapas rullningsbar markör; Data kan nås i valfri ordning och i vilken riktning som helst.
Ange STATIC skapar statisk markör.
Genom att specificera KEYSET skapas en tangentmarkör.
Ange DYNAMIC skapar dynamisk markör.
Om du anger FAST_FORWARD-argumentet för en READ_ONLY-markör, kommer den skapade markören att optimeras för snabb dataåtkomst. Detta argument kan inte användas tillsammans med argumenten FORWARD_ONLY eller OPTIMISTIC.
En markör som skapats med argumentet OPTIMISTIC förhindrar modifiering eller radering av rader som modifierades efter öppnar markören.
Genom att ange argumentet TYPE_WARNING kommer servern att informera användaren om en implicit ändring av markörtypen om den är inkompatibel med SELECT-frågan.
Öppnar markören
För öppnar markören och fyll den med data från SELECT-frågan som angavs när markören skapades, använd följande kommando:
Efter öppnar markören Den associerade SELECT-satsen exekveras, vars utdata lagras i flernivåminne.
Hämtar data från en markör
Direkt efter öppnar markören du kan välja dess innehåll (resultatet av att köra motsvarande fråga) med följande kommando:
Om du anger FIRST returneras den allra första raden av markörens kompletta resultatuppsättning, som blir den aktuella raden.
Om du anger LAST returneras den senaste raden på markören. Det blir också den nuvarande linjen.
Om du anger NEXT returneras raden omedelbart efter den nuvarande i hela resultatuppsättningen. Nu blir det aktuellt. Som standard använder FETCH-kommandot den här metoden för att hämta rader.
Nyckelordet PRIOR returnerar raden före det aktuella. Det blir aktuellt.
Argument ABSOLUT (line_number | @line_number_variable) returnerar en rad med dess absoluta ordningsnummer i markörens fullständiga resultatuppsättning. Radnumret kan anges med en konstant eller som namnet på en variabel i vilken radnumret är lagrat. Variabeln måste vara en heltalsdatatyp. Både positiva och negativa värden anges. När du anger ett positivt värde räknas strängen från början av uppsättningen, medan ett negativt värde räknas från slutet. Den valda raden blir den aktuella raden. Om ett nollvärde anges returneras ingen rad.
Argument RELATIV (antal rader | @variabelt antal rader) returnerar raden som är det angivna antalet rader efter den aktuella. Om du anger ett negativt antal rader kommer raden som är det angivna antalet rader före den nuvarande att returneras. Om du anger ett nollvärde returneras den aktuella raden. Den returnerade raden blir den aktuella raden.
Till öppna global markör, måste du ange nyckelordet GLOBAL före dess namn. Markörens namn kan också anges med en variabel.
I design INTO @variabelnamn [,...n] en lista med variabler specificeras där motsvarande kolumnvärden för den returnerade raden kommer att lagras. Ordningen för att specificera variabler måste matcha ordningen på kolumnerna i markören, och datatypen för variabeln måste matcha datatypen i markörkolumnen. Om INTO-konstruktionen inte är specificerad, kommer beteendet för FETCH-kommandot att likna beteendet för SELECT-kommandot - data visas på skärmen.
Ändra och radera data
För att göra ändringar med hjälp av en markör måste du utfärda ett UPDATE-kommando i följande format:
Flera kolumner i den aktuella markörraden kan ändras i en operation, men alla måste tillhöra samma tabell.
För att radera data med hjälp av en markör, använd kommandot DELETE i följande format:
Som ett resultat kommer linjeuppsättningsströmmen i markören att raderas.
Stänger markören
Efter stängning blir markören oåtkomlig för programanvändare. När den är stängd tas alla lås som installerats under driften bort. Stängning kan endast tillämpas på öppna markörer. Stängt men inte frigjord markör kan öppnas igen. Det är inte tillåtet att stänga en oöppnad markör.
Släpp markören
Stänger markören frigör inte nödvändigtvis minnet som är associerat med det. Vissa implementeringar måste uttryckligen avallokera det med hjälp av DEALLOCATE-satsen. Efter släpp markören Minnet frigörs också, vilket gör det möjligt att återanvända markörnamnet.
För att kontrollera om slutet av markören har nåtts, rekommenderas att använda funktionen: @@FETCH_STATUS
Funktionen @@FETCH_STATUS returnerar:
0 om hämtningen lyckades;
1 om hämtningen misslyckades på grund av ett försök att hämta en rad utanför markören;
2 om hämtningen misslyckades på grund av ett försök att komma åt en raderad eller ändrad rad.
DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Inköpslista" DECLARE klient_cursor CURSOR LOCAL FÖR VALD klientkod, företag, efternamn FRÅN klient WHERE City="Moscow" BESTÄLLNING EFTER Företag, Efternamn ÖPPNA klient_cursor HÄMTA NÄSTA FRÅN klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 BÖRJA VÄLJ @message="Klient "+@fam+ "Företag "+ @firm PRINT @message SELECT @message="Produktnamn Inköpsdatum Kostnad" SKRIV UT @message DEKLARERA tovar_cursor CURSOR FOR SELECT Product.Name, Transaction.Date, Product .Pris* Transaktionskvantitet SOM kostnad FRÅN Produkt INNER JOIN Transaktion PÅ Produkt. Produktkod=Transaktion.Produktkod VAR Transaktion.Kundkod=@id_kl ÖPPNA tovar_cursor HÄMTA NÄSTA FRÅN tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 SKRIV UT "Inga köp" MEDAN @@FETCH_STATUS=0 BÖRJA VÄLJ @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p HÄMTA NÄSTA FRÅN tovar_cursor INTO @nam, @d, @p AVSLUTA STÄNG tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Total kostnad "+ CAST(@s AS CHAR(6)) PRINT @message -- flytta till nästa klient-- HÄMTA NÄSTA FRÅN klient_cursor INTO @id_kl, @firm, @fam AVSLUTA STÄNG klient_cursor DEALLOCATE klient_cursor Exempel 13.6. Markör för att visa en lista över varor köpta av kunder från Moskva och deras totala kostnad.
Exempel 13.7. Utveckla en rullningsbar markör för kunder från Moskva. Om telefonnumret börjar med 1, radera klienten med det numret och i den första markörposten ersätt den första siffran i telefonnumret med 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "Lista of clients" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Firm, Efternamn, Phone FROM Client WHERE City ="Moscow" BESTÄLLNING EFTER Företag, Efternamn FÖR UPPDATERING ÖPPNA klient_cursor HÄMTA NÄSTA FRÅN klient_cursor INTO @firm, @fam, @tel MEDAN @@FETCH_STATUS=0 BÖRJA VÄLJ @message="Client "+@fam+ " Företag "+ @firm " Phone "+ @tel SKRIV UT @meddelande -- om telefonnumret börjar med 1, -- radera klienten med det numret OM @tel LIKE '1%' RADERA klient DÄR AKTUELL AV klient_cursor ANNAT -- gå till nästa klient HÄMTA NÄSTA FRÅN klient_cursor INTO @firm, @fam, @tel AVSLUTA HÄMTA ABSOLUTE 1 FRÅN klient_cursor INTO @firm, @fam, @tel -- i den första posten, ersätt den första siffran i telefonnumret med 4 UPPDATERA Client SET Phone ='4' + RIGHT(@ tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm "Phone "+ @tel PRINT @message STÄNG klient_cursor DEALLOCATE klient_cursor Exempel 13.7. Scrollbar markör för klienter från Moskva.
Exempel 13.8. Användande markören som en utdataparameter för proceduren. Proceduren returnerar en datamängd - en lista över produkter.
Att anropa proceduren och skriva ut data från utmatningsmarkören utförs enligt följande:
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT HÄMTA NÄSTA FRÅN @my_cur INTO @n VÄLJ @n MEDAN (@@FETCH_STATUS=0) BÖRJA HÄMTA NÄSTA FRÅN @my_cur INTO @n SELECT AVSLUTA STÄNG @my_cur DEALLOCATE @my_cur
