DECLARE CURSOR-opdracht algemene regels. Cursors in opgeslagen MySQL-procedures Voorbeeld van het gebruik van een cursor in ms sql

Een cursor is een object waarmee u rijen afzonderlijk kunt verwerken uit de resultaatset die wordt geretourneerd door een SELECT-instructie. Vervolgens zullen we kijken naar cursors die worden ondersteund in de Transact-SQL-taal. Dit zijn cursors aan de serverzijde die bestaan ​​als objecten aan de databaseserverzijde. Er zijn ook clientcursors die worden gebruikt om clientdatabasetoepassingen te maken.

In de literatuur wordt opgemerkt dat de rij-voor-rij verwerking van een dataset met behulp van een cursor in de overgrote meerderheid van de gevallen aanzienlijk langzamer is dan vergelijkbare acties die worden uitgevoerd door SQL-tools voor het verwerken van meerdere rijen. Daarom wordt aanbevolen om cursors alleen te gebruiken in gevallen waarin het beschrijven van de vereiste acties door middel van bewerkingen met reeksen rijen duidelijk niet effectief of zelfs onmogelijk is.

Werken met een cursor omvat meestal de volgende stappen:

• cursordeclaratie;
• openingscursor;
• attribuutwaarden uit het eerste cursorrecord in variabelen lezen;
• rond de cursor bewegen (meestal in een lus) en cursorinvoer verwerken;
• sluitcursor;
• geheugen vrijmaken dat aan de cursor is toegewezen.

Een cursor wordt gedeclareerd met behulp van de DECLARE-instructie, waarvan het formaat hieronder wordt weergegeven. Opgemerkt moet worden dat deze operator in SQL Server zowel de syntaxis van de ISO SQL-standaard ondersteunt (de versie van de standaard is niet gespecificeerd in de documentatie) als de syntaxis met behulp van een set Transact-SQL CURSOR-taalextensies

FOR select_statement

Uitgebreide Transact-SQL-syntaxis:

DECLARE cursornaam CURSOR

FOR select_statement

]][;]

Als u het trefwoord GLOBAL opgeeft, betekent dit dat de gedeclareerde cursor beschikbaar is in elke taakbatch, trigger of opgeslagen procedure die wordt uitgevoerd binnen de huidige verbinding met de server. De cursor wordt alleen impliciet vrijgegeven als de verbinding wordt verbroken.

Een "lokale" cursor, ongeacht of deze standaard is gemaakt of door expliciet LOCAL op te geven, is alleen beschikbaar in de taakbatch, opgeslagen procedure of trigger waarin deze is gemaakt. Deze cursor wordt impliciet vrijgegeven wanneer de batch, opgeslagen procedure of trigger de uitvoering voltooit. De uitzondering hierop is wanneer de cursor door een OUTPUT-parameter van een opgeslagen procedure wordt gevoerd. Vervolgens wordt de cursor vrijgegeven wanneer alle variabelen die ernaar verwijzen, worden vrijgegeven of wanneer deze buiten het bereik valt.

FORWARD_ONLY betekent dat u alleen voorwaarts langs de cursor kunt “bewegen” (alleen het FETCH NEXT-commando is beschikbaar, zie hieronder), d.w.z. Elke invoer in de cursor kan maximaal één keer worden verwerkt. Als ALLEEN VOORUIT is opgegeven zonder de trefwoorden STATIC, KEYSET of DYNAMIC, dan werkt de cursor als een DYNAMIC-cursor (zie hieronder). Als noch FORWARD_ONLY noch SCROLL is opgegeven, en als geen van de STATIC-, KEYSET- of DYNAMIC-trefwoorden is opgegeven, is de standaardwaarde FORWARD_ONLY.

SCROLL betekent dat u in elke richting rond de cursor kunt “bewegen” (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE zijn beschikbaar in de FETCH-instructie). De SCROLL-optie kan niet worden opgegeven met de FAST_FORWARD-optie. STATIC-, KEYSET- en DYNAMIC-cursors hebben de standaardwaarde SCROLL.

STATISCH betekent dat de cursor niet kan worden bijgewerkt. De resulterende dataset van zo'n cursor wordt uit de database gehaald en opgeslagen in de database voor tijdelijke objecten tempdb. Wijzigingen aan de tabellen die als basis voor de cursor dienen, worden hierna niet in de cursor weergegeven.

KEYSET – voor dit type cursor wordt een reeks sleutelwaarden die de geselecteerde records identificeren, opgeslagen in een tijdelijke tabel. Terwijl u door de cursor beweegt, worden de waarden van niet-sleutelattributen opgehaald uit de overeenkomstige tabellen, zodat wijzigingen in niet-sleutelkolommen zichtbaar zijn wanneer u de cursor verplaatst. Als de rij die in de cursor staat al uit de tabel is verwijderd op het moment dat deze door de FETCH-operator wordt opgehaald, zal de servicevariabele @@ FETCH_STATUS de waarde -2 retourneren. Rijen die aan tabellen zijn toegevoegd nadat de cursor is geopend, zijn niet zichtbaar in de cursor. Als de query die de cursor genereert ten minste één tabel betreft die geen unieke index heeft, wordt de cursor van het type KEYSET geconverteerd naar het type STATIC.

DYNAMIC is het meest resource-intensieve cursortype dat alle gegevenswijzigingen weergeeft die in de rijen van de resultatenset zijn aangebracht, inclusief nieuw ingevoegde rijen. De gegevenswaarden, volgorde en lidmaatschap van rijen in elke selectie kunnen variëren. FETCH ABSOLUTE kan niet worden gebruikt met dynamische cursors.

FAST_FORWARD is het snelste cursortype, waardoor u alleen “vooruit” van de ene regel naar de andere kunt gaan. Dit is het standaardcursortype (wanneer optionele trefwoorden zijn weggelaten). Het is equivalent aan een cursor die is gedeclareerd met de parameters FORWARD_ONLY en READ_ONLY.

READ_ONLY – definieert een “alleen-lezen” cursor: wijzigingen in de database kunnen niet via een dergelijke cursor worden aangebracht.

SCROLL_LOCKS betekent dat SQL Server rijen vergrendelt wanneer ze in de cursor worden gelezen, zodat ze via dat type cursor kunnen worden bijgewerkt of verwijderd.

Een cursor die is gedeclareerd met het trefwoord OPTIMISTIC vereist geen rijvergrendeling en maakt het mogelijk dat gegevens worden gewijzigd. Als er wijzigingen in de basistabel zijn opgetreden nadat gegevens in de cursor waren ingelezen, resulteert een poging om die gegevens via de cursor te wijzigen in een fout.

TYPE_WARNING specificeert dat wanneer een cursor impliciet wordt geconverteerd van het gevraagde type naar een ander (bijvoorbeeld de hierboven beschreven KEYSET naar STATIC cursorconversie wanneer er geen unieke index in de tabel aanwezig is), er een waarschuwing naar de client wordt verzonden.

Select_statement – ​​​​SELECT-instructie die de resultatenset van de cursor vormt.

De instructie FOR UPDATE specificeert de kolommen in de cursor die moeten worden bijgewerkt. Als OF kolomnaam [, . . . n], dan zijn alleen de vermelde kolommen beschikbaar voor wijzigingen. Als er geen lijst met kolommen is, is bijwerken mogelijk voor alle kolommen, behalve in het geval van een cursordeclaratie met de parameter READ_ONLY.

Gebruik de opdracht om de cursor te openen en te vullen

OPEN ((cursornaam) I @cursor_variabele)

Bij het openen kan de cursor worden opgegeven op naam (cursor_name) of via een variabele van het type CURSOR (@cursor_variable). De GLOBAL-parameter geeft aan dat cursornaam een ​​globale cursor is.

Om door de dataset van een cursor te bewegen en de gegevens op te halen als variabele waarden, gebruikt u de FETCH-instructie:

OPHALEN [

((cursornaam] I @cursor_variabele]

De commando's die de bewegingsrichting langs de cursor bepalen, worden beschreven in de tabel. 10.10. Zoals eerder opgemerkt, zijn, afhankelijk van het type cursor, sommige opdrachten voor een bepaalde cursor mogelijk niet van toepassing.

Het is belangrijk op te merken dat als de cursor net is geopend, de eerste uitvoering van FETCH NEXT resulteert in een sprong naar het eerste record van de cursor.

Tabel 10.10

Navigeren door een cursorgegevensset

Met de globale variabele @@FETCH_STATUS kunt u het resultaat achterhalen van de laatste uitvoering van de FETCH-instructie:

O – actie succesvol afgerond;

• -1 – de uitvoering van de operator is mislukt, of de regel viel buiten de resultatenset (de cursor eindigde);
• -2 – de geselecteerde rij ontbreekt, bijvoorbeeld als tijdens het werken met een “veranderingsgevoelige” cursor het huidige record uit de database is verwijderd.

De CLOSE-instructie sluit een open cursor en maakt het geheugen vrij dat wordt gebruikt om de dataset op te slaan. Het ophalen van gegevens en het verplaatsen van een gesloten cursor is onmogelijk; deze moet opnieuw worden geopend.

SLUITEN ((cursor_naam)|@cursor_variabele )

De DEALLOCATE-instructie verwijdert de associatie tussen een cursor en zijn naam of variabele. Als dit de achternaam of variabele is die naar de cursor verwijst, wordt de cursor zelf verwijderd en worden alle bronnen die hij gebruikt vrijgegeven:

DEALLOCATE (( cursor_name] | @cursor_variable) Laten we een eenvoudig voorbeeld bekijken van het gebruik van een cursor. Hier worden auteurs en titels van boeken die niet eerder dan 2000 zijn gepubliceerd uit de tabel geselecteerd, waarna de gegevens in een lus worden uitgevoerd naar SELECT-instructies - telkens één record met een eigen titel. Aanvullende uitleg wordt gegeven door opmerkingen in de code:

/*variabelen declareren*/

VERKLAREN @auth varchar(50), @title varchar(50)

WAAR >= 2000

/*open de cursor en “ren” er doorheen, waarbij de auteur en titel worden weergegeven met behulp van een afzonderlijke SELECT-instructie*/

FETCH VOLGENDE VAN cursorl IN @auth, @title

TERWIJL SSFETCH_STATUS = 0

FETCH VOLGENDE VAN cursorl INTO @auth, Stijl

/*sluit de cursor en laat hem los*/

DEALLOCATE cursorl

Zoals hierboven opgemerkt, kan een variabele van het type CURSOR worden gebruikt in plaats van de cursornaam. Hieronder vindt u vergelijkbare code die deze variabelen gebruikt:

VERKLAREN Zuid-varchar(50), Stijltitel varchar(50)

/*declareer een variabele van het type cursor*/

VERKLAREN Scurl-CURSOR

DECLARE cursorl CURSOR FAST_FORWARD

SELECTEER auteur, titel VAN dbo.Bookl

WAAR >= 2000

/*wijs een waarde toe aan een variabele van het type cursor*/

SET Krul = cursor

TERWIJL SSFETCH_STATUS = 0

HALEN VOLGENDE VAN Scurl NAAR Zuid, Sttitle

/*bank informatie*/
MAAK TABEL `bank` (

`Banknaam` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,


PRIMAIRE SLEUTEL (`BankId`)

)MOTOR=InnoDB
TEKENSET "utf8" VERZAMELEN "utf8_bin" ;
/*gegevens over stortingen */
MAAK TABEL `bankverdeling` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) STANDAARD NULL ,
`ContributeAmount` DECIMAAL (10,0) NIET NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMAIRE SLEUTEL(`BankId`, `ClientId`),
SLEUTEL `BankId` (`BankId`),
SLEUTEL `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN SLEUTEL (`BankId`) REFERENTIES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN SLEUTEL (`ClientId`) REFERENTIES `client` (`ClientId`)
)MOTOR=InnoDB
/*gegevens over investeerders*/
MAAK TABEL `klant` (
`ClientId` INTEGER (3) NIET NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Achternaam` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Naam` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Voornaam` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Telefoon` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Adres` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NIET NULL ,
PRIMAIRE SLEUTEL(`ClientId`, `CreditCardId`),
SLEUTEL `ClientId` (`ClientId`)

)MOTOR=InnoDB
AUTO_INCREMENT=11 TEKENSET "utf8" VERZAMELEN "utf8_bin"

Laten we zeggen dat we elke bank om de beurt moeten ontvangen en er enkele acties mee moeten uitvoeren. De volgende vraag zou ons hierbij kunnen helpen

Selecteer 'bank'.* VAN 'bank' LIMIET AANTAL VAN DE_RECORD_WE NODIG hebben,1

Beginnen
/* variabelen waar we gegevens extraheren */
Declareer vBankId geheel getal ;
Declareer vBanknaam VARCHAR (50);
Declareer vAddress VARCHAR(50);
Verklaar vPhone VARCHAR (50);
/* hadler variabele - a*/
Verklaar voltooid geheel getal standaard 0;
/*Cursordeclaratie*/
Declareer BankCursor Cursor voor Selecteer `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` waarbij 1;
/*HANDLER-doel, dat hieronder wordt uitgelegd*/
DECLARE CONTINUE HANDLER VOOR SQLSTATE "02000" SET done=1;
/* cursor openen */
Open BankCursor;
/*data ophalen*/
TERWIJL gedaan = 0 DOEN

we ondernemen de acties die we nodig hebben
EINDE TERWIJL;
/*de cursor sluiten*/
Sluit BankCursor;
EINDE ;

* Deze broncode is gemarkeerd met Source Code Highlighter.

Fout: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Bericht: Geen gegevens - nul rijen opgehaald, geselecteerd of verwerkt

SQLSTATE: 02000 wordt geactiveerd wanneer het einde van de cursor wordt bereikt, of wanneer selecteren of bijwerken een lege tekenreeks retourneert.

Op de volgende regel declareerden we de cursor DECLARE cursor_name CURSOR FOR select_statement;
Open de cursor Open cursornaam;
Vervolgens extraheren we de gegevens en verwerken we deze totdat we het einde van de cursor bereiken (WHILE done = 0 DO).
U moet de cursor sluiten voordat u de opgeslagen procedure afsluit. Sluit cursornaam;

Het lijkt niet ingewikkeld. Maar er zijn veel valkuilen verbonden aan SQLSTATE "02000".

TERWIJL gedaan = 0 DOEN
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;

Selecteer (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution waarbij BankId = vBankId-limiet 1;
we doen wat acties
EINDE TERWIJL;

* Deze broncode is gemarkeerd met Source Code Highlighter.

TERWIJL gedaan = 0 DOEN
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* uittreksel voor de bank van het bedrag van haar deposito's */


if (vContributeAmountSUM > 0) dan
/* uittreksel voor de bank van het bedrag van haar deposito's */

stop als ;
we doen wat acties
EINDE TERWIJL;

* Deze broncode is gemarkeerd met Source Code Highlighter.

Laten we nu zeggen dat we voor elke klant het totale bedrag op rekeningen bij verschillende banken moeten verwijderen
Declareer ClientSummCursor Cursor voor Selecteer som

Declareer ClientSummCursor Cursor voor Select sum (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join-client op (client.ClientId = bankdistribution.`ClientId`) waarbij 1 groep op `bankdistribution` staat. `ClientID`;

Open ClientSummCursor;
TERWIJL gedaan = 0 DOEN
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* uittreksel voor de bank van het bedrag van haar deposito's */
Selecteer Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution waarbij BankId = vBankId-limiet 1;
/* controleer of er daadwerkelijk tegoeden bij deze bank staan ​​*/
if (vContributeAmountSUM > 0) dan
/* uittreksel voor de bank van het bedrag van haar deposito's */
Selecteer ContributeAmount INTO vContributeAmountSUM FROM bankdistribution waarbij BankId = vBankId-limiet 1;
stop als ;


we doen wat acties.
EINDE TERWIJL;

* Deze broncode is gemarkeerd met Source Code Highlighter.

Dezelfde situatie kan zich voordoen wanneer de gegevens in de ClientSummCursor-cursor eerder eindigen dan de gegevens in de BankCursor, SQLSTATE: 02000 wordt geactiveerd, de done-variabele wordt ingesteld op 1 en de while-lus eerder eindigt dan we hadden verwacht. Dit kan worden vermeden door het volgende te doen

Open ClientSummCursor;
TERWIJL gedaan = 0 DOEN
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* uittreksel voor de bank van het bedrag van haar deposito's */
Selecteer Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution waarbij BankId = vBankId-limiet 1;
/* controleer of er daadwerkelijk tegoeden bij deze bank staan ​​*/
if (vContributeAmountSUM > 0) dan
/* uittreksel voor de bank van het bedrag van haar deposito's */
Selecteer ContributeAmount INTO vContributeAmountSUM FROM bankdistribution waarbij BankId = vBankId-limiet 1;
stop als ;
/* Onthoud de sqlstate-status voordat u gegevens uit de tweede cursor extraheert */
SET oude_status = klaar;
/* extraheer de gegevens die we nodig hebben */
FETCH ClientSummCursor INTO vSum,vClientId;
/* controleer of de gegevens zijn opgehaald en of sqlstate 0200 is mislukt */
als (klaar = 0) dan
we doen wat acties.
stop als ;
/* herstel vóór het einde van de while de waarde van de voltooide variabele */
set klaar = oude_status;
EINDE TERWIJL;

* Deze broncode is gemarkeerd met Source Code Highlighter.

Bedankt aan iedereen die tot nu toe heeft gelezen, ik hoop dat dit voor iemand nuttig zal zijn.

Het kan heel goed gebeuren dat het antwoord op een eenvoudig klantverzoek een steekproef van honderdduizenden rijen zal zijn, wat voor de meeste klanten onverteerbaar is. In dit geval is de oplossing voor het probleem van interactie met clients het gebruik van cursors als universeel mechanisme voor het uitwisselen van gegevens tussen de server en de client. Cursors werken met de resultatenset van gegevens (het resultaat van een zoekopdracht), waardoor gebruikers extra krijgen:

Met cursors kunt u met tabelrijen werken door hun serienummer in de gegevensset op te geven;

Met cursors kunt u complexe gegevenswijzigingsbewerkingen implementeren, bijvoorbeeld wanneer het wijzigen van de waarde van een kolom herhaaldelijk toegang tot de waarden van andere kolommen vereist.

Cursorlevenscyclus:

Een cursor maken: VERKLAREN<имя курсора>[ ONGEVOELIG ] [ SCROLL ] CURSOR VOOR< SELECT -оператор>VOOR (ALLEEN LEZEN | UPDATE)

Hier betekent het trefwoord INSENSITIVE dat de cursor statisch zal zijn (een momentopname van de gegevens), terwijl de cursor standaard dynamisch wordt gemaakt (elke keer dat de rij wordt geopend, wordt er een selectie gemaakt). Het trefwoord SCROLL betekent dat de cursor in elke richting kan worden gescrolld, anders wordt de cursor gemaakt als een "sequentiële" cursor.

Openingscursor: OPEN [GLOBAAL]<имя курсора>. Een cursor die is opgegeven als GLOBAL, wordt niet automatisch verwijderd wanneer de procedure of het pakket waaruit deze is aangeroepen, eindigt.

Lezinggegevens : OPHALEN [[ VOLGENDE | VOORAF | EERSTE | LAATSTE | ABSOLUTE n | RELATIEF n ] VAN ] [ GLOBAL ]<имя курсора>[INTO@variabele_naam,...]. Met SQL Server 2000 kunt u slechts één rij van een cursor lezen. Het FIRST sleutelwoord is om de eerste rij van de cursor terug te geven; LAST – laatste regel van de cursor; VOLGENDE – de volgende regel na de huidige, de geretourneerde regel wordt de huidige; PRIOR – vorige vóór huidige; ABSOLUTE n – retourneert een regel met het absolute volgnummer in de cursor; RELATIEF – n regels na de huidige. De kolomgegevens worden opgeslagen in elk van de opgegeven variabelen in de volgorde waarin ze worden vermeld.

Gegevens wijzigen: voert een UPDATE-opdracht uit met een syntaxis die is ontworpen voor het werken met cursors.

Gegevens verwijderen: voert een DELETE-opdracht uit met een syntaxis die is ontworpen om met cursors te werken.

De cursor sluiten: SLUITEN [GLOBAAL]<имя курсора>

Laat de cursor los: TOEWIJZING [GLOBAAL]<имя курсора>

Een voorbeeld van het gebruik van een cursor:

VERKLAREN fo_curs CURSOR STATISCH VOOR

SELECTEER naam_rus uit ORDER OP naam_rus

VERKLAREN @naam varchar(50)

HAAL EERST VAN fo_curs INTO @name

TERWIJL @@FETCH_STATUS=0

HAAL VOLGENDE VAN fo_curs INTO @name

VERWIJDEREN voor_curs

2.7. Zorgen voor gegevensbeveiliging en integriteit in Microsoft SQL Server. Database management. Rollen. Rechten toekennen aan gebruikers (GRANT, DENY, REVOKE). Methoden en technologieën voor gegevensbescherming in SQL Server.

SQL Server-beveiliging en -beheer. .

De hoofdtaak van een DBMS is het waarborgen van de integriteit en consistentie van gegevens binnen het geselecteerde vakgebied. Een van de factoren die het systeem ervan weerhouden dit probleem op te lossen, zijn de acties van gebruikers die per ongeluk of opzettelijk proberen de datastructuur te vernietigen of de gegevens zelf te wijzigen. Bijgevolg moet het DBMS niet alleen worden beschermd tegen fysieke storingen, maar ook tegen gebruikers die niet geschikt zijn voor de taken die worden uitgevoerd. Om dit te doen, is het noodzakelijk om een ​​beveiligingssysteem te ontwerpen en met de database te verbinden dat voorkomt dat gebruikers acties uitvoeren die buiten hun bevoegdheid vallen.

Database management

Om een ​​database aan te maken met TSQL gebruik je de opdracht CREATE DATABASE, maar hiervoor worden meestal de mogelijkheden van SQL Server Management Studio gebruikt. Er zijn nogal wat databasebewerkingen gedefinieerd in SQL Server: het vergroten (verkleinen) van de bestandsgrootte, het wijzigen van de configuratie (ALTER-opdracht), koppelen en ontkoppelen, eigendom overdragen, naam wijzigen, eigenschappen bekijken en ten slotte verwijderen (DROP DATABASE).

Zoals de meeste databaseservers heeft SQL Server een gebruiker met volledige beheerdersrechten Systeembeheerder of 'sa'. Na de initiële serverinstallatie is het sa-wachtwoord leeg. De gebruiker die een nieuwe database aanmaakt, wordt automatisch de eigenaar ervan ('dbo' - Database-eigenaar). Op het moment dat de database wordt aangemaakt, wordt ook de gebruiker "gast" gedefinieerd een specifieke database, krijgt de gebruiker impliciete toegang met het gebruik van de gastnaam gast is meestal verboden.

De gebruiker die een object in de database aanmaakt, wordt automatisch de eigenaar ervan, en niemand, inclusief dbo en sa, kan dat object gebruiken totdat de eigenaar hem er rechten aan heeft toegewezen. Maar voordat een gebruiker een object kan maken, moet de database-eigenaar hem eerst de juiste rechten verlenen.

Rol Hiermee kunt u gebruikers combineren die dezelfde functies uitvoeren om het beheer te vereenvoudigen. Rollen kunnen ingebouwd of aangepast zijn. Ingebouwde rollen worden geïmplementeerd op serverniveau en op databaseniveau. Hieronder vindt u een tabel met ingebouwde databaserollen:

db_eigenaar. Heeft alle rechten in de database

Db_accessadmin. Kan gebruikers toevoegen of verwijderen

Db_securityadmin. Beheert alle machtigingen, objecten, rollen en gebruikers

Db_ddladmin. Kan alle DDL-opdrachten uitvoeren behalve GRANT, DENY, REVOKE

Db_backupoperator. De archivaris kan opdrachten uitvoeren. gegevens

db_datareader. Misschien bekijken. alle gegevens in elke tabel

db_datawriter. Misschien een wijziging. alle gegevens in elke tabel

Db_denydatareader. Verboden weergave Liefde gegevens in elk tafels

Db_denydatawriter. Verbied het wijzigen van gegevens in tabellen

Rechten toekennen aan gebruikers. De basis van SQL Server-beveiliging is (1) accounts; (2) gebruikers; (3) rollen; (4) groepen.

Wanneer een gebruiker verbinding maakt met SQL Server, worden de acties die hij kan uitvoeren bepaald door de rechten die hem als gebruiker en lid van een rol zijn verleend. Rechten worden verleend door de DBMS-beheerder, de database-eigenaar of de eigenaar van een specifiek databaseobject. Rechten in de database kunnen worden onderverdeeld in drie categorieën: (1) rechten op toegang tot databaseobjecten; (2) rechten om TSQL-opdrachten uit te voeren; (3) impliciete rechten. Met de server kunt u het eigendom van de ene gebruiker naar de andere overdragen.

De volgende opdrachten worden gebruikt om gebruikersmachtigingen voor toegang tot databaseobjecten te beheren:

STUDIEBEURS(ALLE |< вид действия >,…}

( OP (<имя таблицы или представления>} [(<имя столбца>,…)]

| OP(< имя хранимой процедуры >}

| OP(< имя пользовательской функции >}

AAN (OPENBAAR |<имя объекта системы безопасности>,…}

[ ALS<имя группы> | <имя роли>]

– het toekennen van rechten aan gebruikers, Waar

ALLES – de gebruiker krijgt alle mogelijke rechten, geef anders op

<вид действия>– rechten op acties waarover de gebruiker beschikt, te weten:

SELECT – voor weergave, voor tabelkolom en voor tabel (weergave)

INSERT – om toe te voegen, voor de tabel (weergave) als geheel

UPDATE – voor wijziging, voor een tabelkolom en voor een tabel (view)

DELETE – om te verwijderen, voor de tabel (weergave) als geheel

UITVOEREN – om opgeslagen procedures uit te voeren

REFERENTIES – de mogelijkheid om naar een gespecificeerd object te verwijzen (opnemen als onderdeel van een externe sleutel).

<имя объекта системы безопасности>– SQL Server-accounts, Windows-domeingebruikers; OPENBAAR – voor alle gebruikers.

MET VERLENINGOPTIE - hiermee kan de gebruiker aan wie momenteel rechten zijn verleend, toegangsrechten tot het object aan andere gebruikers toewijzen.

ALS<имя группы> | <имя роли>– deelname van een gebruiker in een rol die de mogelijkheid krijgt om rechten te verlenen aan andere gebruikers.

SUBSIDIE SELECTEER OP auteurs AAN publiek

SUBSIDIE INVOEGEN, UPDATEN, VERWIJDEREN OP auteurs AAN Mary, John, Tom

SUBSIDIE SELECTEREN OP Plan_Data NAAR Accounting MET SUBSIDIEOPTIE

VERLEEN SELECTIE OP Plan_Data AAN Jack AS Accounting

Jack is geen lid van de rol Accounting, maar iemand in die rol kan toestemming verlenen

ONTKENNEN(ALLE |< вид действия >,…}

( OP (<имя таблицы или представления>} [(<имя столбца>,…)]

| OP(<имя хранимой процедуры>}

| OP(<имя пользовательской функции>}

AAN (OPENBAAR |<имя объекта системы безопасности>,…}

– toegang weigeren gebruikers naar databaseobjecten. CASCADE trekt niet alleen de rechten in van deze gebruiker, maar ook van iedereen aan wie hij rechten heeft verleend.

Voorbeeld (op bevel verbod TSQL):

WEIGEREN TAFEL MAKEN AAN Jack CASCADE

Team HERROEPEN gebruikt om impliciet de toegang tot databaseobjecten te weigeren. De syntaxis is hetzelfde als de opdracht DENY. Impliciete weigering is vergelijkbaar met het weigeren van toegang, behalve dat deze alleen van toepassing is op het niveau waarop deze is gedefinieerd. Voorbeeld: De gebruiker Jack, die lid is van de rol GoodUsers, krijgt toegangsrechten tot de XFiles-tabel. Als REVOKE wordt geweigerd voor de rol GoodUsers om toegang te krijgen tot deze tabel, heeft Jack nog steeds toegang tot deze tabel omdat de rechten voor hem expliciet zijn gedefinieerd. Als u REVOKE persoonlijk voor hem gebruikt, verliest hij het recht op toegang tot XFiles.

Machtigingen die aan rollen worden verleend, worden overgenomen door hun leden. Als een gebruiker toegang krijgt tot een object via lidmaatschap van de ene rol, maar wordt geweigerd in een andere rol, wordt het toegangsconflict altijd opgelost ten gunste van weigering.

Technologieën voor gegevensbescherming in MS SQL Server

1. Mechanisme controlepunten– controlepunten die na ~60 s worden gegenereerd om bijgewerkte pagina's naar schijf te schrijven (een controlepunt kan worden geforceerd door de CHECKPOINT-opdracht).

2. Ingebouwde en externe mechanismen voor het controleren van de integriteit van de database (automatisch gestart of, zoals het DBCC-hulpprogramma - Database Consistency Checker - handmatig).

3. Fysieke duplicatie (indien toegestaan) van databasebestanden met behulp van het besturingssysteem (inclusief het mechanisme van gespiegelde harde schijven).

4. Een back-up maken van databases en transactielogboeken - door een databasedump naar een back-upapparaat (magneetband of harde schijf) te schrijven.

5. Replicatie – de mogelijkheid om informatie te dupliceren door deze periodiek (in sommige gevallen synchroon) over te dragen van de ene SQL-server naar de andere.

6. Versleuteling van verkeer tussen de client en server, evenals versleuteling van codes die worden gebruikt om met databaseobjecten te werken (opgeslagen procedures, triggers, enz.)

In mijn T-SQL-code gebruik ik altijd set-gebaseerde bewerkingen. Er is mij verteld dat SQL Server is ontworpen om dit soort bewerkingen te verwerken en dat het sneller zou moeten zijn dan seriële verwerking. Ik weet dat er cursors bestaan, maar ik weet niet zeker hoe ik ze moet gebruiken. Kunt u enkele cursorvoorbeelden geven? Kunt u enig advies geven over wanneer u cursors moet gebruiken? Ik neem aan dat Microsoft ze om een ​​reden in SQL Server heeft opgenomen, dus ze moeten een plek hebben waar ze op een efficiënte manier kunnen worden gebruikt.

Oplossing

In sommige kringen worden de cursors nooit gebruikt, in andere zijn ze een laatste redmiddel en in andere groepen worden ze regelmatig gebruikt. In elk van deze kampen hebben ze verschillende redenen voor hun standpunt over cursorgebruik. Ongeacht jouw standpunt over cursors, zullen ze waarschijnlijk Het komt dus neer op uw begrip van de coderingstechniek en uw begrip van het probleem dat voorhanden is om een ​​beslissing te nemen over de vraag of cursorgebaseerde verwerking al dan niet geschikt is ' doe het volgende:

• Kijk naar een voorbeeldcursor
• Verdeel de componenten van de cursor
• Geef aanvullende cursorvoorbeelden
• Analyseer de voor- en nadelen van cursorgebruik

Hoe u een SQL Server-cursor maakt

Het maken van een SQL Server-cursor is een consistent proces, dus zodra u de stappen onder de knie heeft, kunt u deze eenvoudig dupliceren met verschillende sets logica om door gegevens te bladeren. Laten we de stappen eens doorlopen:

1. Eerst declareert u de variabelen die u nodig hebt in de logica.
2. Ten tweede declareert u de cursor met een specifieke naam die u in de hele logica zult gebruiken. Dit wordt onmiddellijk gevolgd door het openen van de cursor.
3. Ten derde haalt u een record op van de cursor om de gegevensverwerking te starten.
4. Ten vierde is er het dataproces dat uniek is voor elke set logica. Dit kan het invoegen, bijwerken, verwijderen, enz. zijn. voor elke rij met gegevens die is opgehaald. Dit is de belangrijkste reeks logica tijdens dit proces dat op elke rij wordt uitgevoerd.
5. Ten vijfde haalt u het volgende record op van de cursor zoals u deed in stap 3 en vervolgens wordt stap 4 opnieuw herhaald door de geselecteerde gegevens te verwerken.
6. Ten zesde: zodra alle gegevens zijn verwerkt, sluit u de cursor.
7. Als laatste en belangrijke stap moet u de toewijzing van de cursor ongedaan maken om alle interne bronnen die SQL Server vasthoudt vrij te geven.

Bekijk vanaf hier de onderstaande voorbeelden om aan de slag te gaan met weten wanneer u SQL Server-cursors moet gebruiken en hoe u dit moet doen.

Voorbeeld SQL Server-cursor

Hier is een voorbeeldcursor uit tip Eenvoudig script om een ​​back-up te maken van alle SQL Server-databases waarbij back-ups op seriële wijze worden uitgegeven:

DECLARE @naam VARCHAR(50) -- databasenaam DECLARE @pad VARCHAR(256) -- pad voor back-upbestanden DECLARE @bestandsnaam VARCHAR(256) -- bestandsnaam voor back-up DECLARE @fileDate VARCHAR(20) -- gebruikt voor bestandsnaam SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT naam FROM MASTER.dbo.sysdatabases WHERE naam NIET IN ("master", "model ","msdb","tempdb") OPEN db_cursor FETCH VOLGENDE VAN db_cursor IN @name WHILE @@FETCH_STATUS = 0 BEGIN INSTELLEN @fileName = @path + @name + "_" + @fileDate + ".BAK" BACK-UP DATABASE @ naam NAAR SCHIJF = @bestandsnaam FETCH VOLGENDE VAN db_cursor INTO @naam EINDE SLUITEN db_cursor DEALLOCATE db_cursor

Cursorcomponenten van SQL Server

Gebaseerd op het bovenstaande voorbeeld bevatten cursors deze componenten:

• DECLARE-instructies - Declareer variabelen die in het codeblok worden gebruikt
• SET\SELECT-instructies - Initialiseer de variabelen naar een specifieke waarde
• DECLARE CURSOR-instructie - Vul de cursor in met waarden die worden geëvalueerd
  • OPMERKING - Er zijn evenveel variabelen in de DECLARE CURSOR FOR-instructie als in de SELECT-instructie. Dit kunnen 1 of meerdere variabelen en bijbehorende kolommen zijn.
• OPEN-instructie - Open de cursor om de gegevensverwerking te starten
• FETCH NEXT-instructies - Wijs de specifieke waarden van de cursor toe aan de variabelen
  • OPMERKING - Deze logica wordt gebruikt voor de initiële populatie vóór de WHILE-instructie en vervolgens opnieuw tijdens elke lus in het proces als een deel van de WHILE-instructie
• WHILE-instructie: voorwaarde om de gegevensverwerking te starten en voort te zetten
• BEGIN...END-instructie: begin en einde van het codeblok
  • OPMERKING - Op basis van de gegevensverwerking kunnen meerdere BEGIN...END-instructies worden gebruikt
• Gegevensverwerking - In dit voorbeeld is deze logica het maken van een back-up van een database naar een specifiek pad en een specifieke bestandsnaam, maar dit kan vrijwel elke DML of administratieve logica zijn
• CLOSE-instructie - Geeft de huidige gegevens en bijbehorende vergrendelingen vrij, maar staat toe dat de cursor opnieuw wordt geopend
• DEALLOCATE-instructie - Vernietigt de cursor

Aanbevolen lectuur

Meer informatie over SQL Server Cursors en alternatieven:

Aanvullende voorbeelden van SQL Server-cursors

In het bovenstaande voorbeeld worden back-ups gemaakt via een cursor. Bekijk ook deze andere tips die gebruik maken van op cursor gebaseerde logica:

• Script om opdrachten te maken voor het uitschakelen, inschakelen, verwijderen en opnieuw creëren van Foreign Key-beperkingen in SQL Server

SQL Server-cursoranalyse

De onderstaande analyse is bedoeld om inzicht te geven in verschillende scenario's waarin cursorgebaseerde logica al dan niet nuttig kan zijn:

• Online transactieverwerking (OLTP) - In de meeste OLTP-omgevingen is op SET gebaseerde logica het meest zinvol voor korte transacties. Ons team is een applicatie van derden tegengekomen die cursors gebruikt voor alle verwerking, wat problemen heeft veroorzaakt, maar dit is zelden voorgekomen. Normaal gesproken is op SET gebaseerde logica meer dan haalbaar en zijn cursors zelden nodig.
• Rapportage - Op basis van het ontwerp van de rapporten en het onderliggende ontwerp zijn cursors doorgaans niet nodig. Ons team is echter rapportagevereisten tegengekomen waarbij referentiële integriteit niet bestaat in de onderliggende database en het noodzakelijk is een cursor te gebruiken om de rapportagewaarden correct te berekenen. We hebben dezelfde ervaring gehad bij het aggregeren van gegevens voor downstream-processen; een cursorgebaseerde aanpak werd snel ontwikkeld en op een acceptabele manier uitgevoerd om aan de behoefte te voldoen.
• Geserialiseerde verwerking - Als u een proces op geserialiseerde wijze moet voltooien, zijn cursors een haalbare optie.
• Administratieve taken - Veel administratieve taken moeten op een seriële manier worden uitgevoerd, wat mooi past in de op cursor gebaseerde logica, maar er bestaan ​​ook andere systeemgebaseerde objecten om aan deze behoefte te voldoen. In sommige van deze omstandigheden worden cursors gebruikt om het proces te voltooien.
• Grote datasets - Bij grote datasets kun je een of meer van de volgende problemen tegenkomen:
  • Op cursor gebaseerde logica kan mogelijk niet worden geschaald om aan de verwerkingsbehoeften te voldoen.
  • Bij grote setgebaseerde bewerkingen op servers met een minimale hoeveelheid geheugen kunnen de gegevens worden gepagineerd of de SQL Server worden gemonopoliseerd, wat tijdrovend is en tot conflicten en geheugenproblemen kan leiden. Als zodanig kan een cursorgebaseerde aanpak aan de behoefte voldoen.
  • Sommige tools cachen de gegevens inherent in een bestand onder de covers, dus het verwerken van de gegevens in het geheugen kan wel of niet daadwerkelijk het geval zijn.
  • Als de gegevens kunnen worden verwerkt in een gefaseerde SQL Server-database, zijn de gevolgen voor de productieomgeving pas van kracht wanneer de definitieve gegevens zijn verwerkt. Alle bronnen op de staging-server kunnen worden gebruikt voor de ETL-processen, waarna de definitieve gegevens kunnen worden geïmporteerd.
  • SSIS ondersteunt het batchen van datasets, wat de algehele noodzaak kan wegnemen om een ​​grote dataset op te splitsen in beter beheersbare formaten en beter te presteren dan een rij-voor-rij-benadering met een cursor.
  • Afhankelijk van hoe de cursor of SSIS-logica is gecodeerd, kan het mogelijk zijn om opnieuw op te starten op het punt van de fout op basis van een
  • Herhaal een batch met het GO-commando

  Volgende stappen

  • Wanneer u wordt geconfronteerd met een beslissing over gegevensverwerking, bepaal dan waar u staat met het cursorgebruik van SQL Server. Ze hebben al dan niet een plaats in uw applicatie of operationele processen. Er zijn veel manieren om een ​​taak te voltooien, dus het gebruik van een cursor kan een redelijk alternatief zijn, of niet. Jij mag het beoordelen.
  • Als u problemen ondervindt met een andere codeertechniek en snel iets gedaan wilt krijgen, kan het gebruik van een cursor een haalbaar alternatief zijn. Het kan langer duren om de gegevens te verwerken, maar de codeertijd kan veel korter zijn. Als u een eenmalig proces of een nachtelijke verwerking heeft, kan dit voldoende zijn.
  • Als cursors in uw omgeving worden gemeden, zorg er dan voor dat u een ander haalbaar alternatief kiest. Zorg ervoor dat het proces geen andere problemen veroorzaakt. Als er bijvoorbeeld een cursor wordt gebruikt en miljoenen rijen worden verwerkt, zal dit dan mogelijk alle gegevens uit de cache verwijderen en verdere conflicten veroorzaken? Of worden de gegevens bij een grote dataset naar schijf gepagineerd of naar een tijdelijke map geschreven?
  • Terwijl u een cursorgebaseerde aanpak evalueert met andere alternatieven, moet u een eerlijke vergelijking maken van de technieken in termen van tijd, strijd en benodigde middelen. Hopelijk zullen deze factoren je naar de juiste techniek drijven.

De implementatie van een cursor in een database lijkt op een Java-klasse die een reeks gegevens en methoden heeft om deze te verwerken. Waarin sql-cursor gebruikt de gegevens als een reguliere array. Cursors kunnen worden gebruikt in triggers, opgeslagen procedures en functies.

In overeenstemming met de SQL-standaard worden bij het werken met cursors de volgende basisacties uitgevoerd:

• cursordeclaratie;
• een cursor openen met leesgegevens;
• regel voor regel bemonstering van gegevens van de cursor;
• rijgegevens wijzigen met behulp van de cursor;
• het sluiten van de cursor, waarna deze ontoegankelijk wordt;
• de cursor loslaten, d.w.z. het verwijderen van een cursor uit het geheugen, omdat het sluiten ervan niet noodzakelijkerwijs het bijbehorende geheugen vrijmaakt.

In verschillende implementaties is de definitie cursor kan enkele verschillen hebben. Soms is het bijvoorbeeld nodig om expliciet het geheugen vrij te maken dat is toegewezen aan een cursor. Zodra de cursor is vrijgegeven, wordt ook het bijbehorende geheugen vrijgegeven. Dit maakt het mogelijk om de cursornaam opnieuw te gebruiken. In andere implementaties wordt, wanneer de cursor gesloten is, impliciet geheugen vrijgemaakt.

In sommige gevallen kunt u niet zonder een cursor. Indien mogelijk moet u echter het gebruik van een cursor vermijden en werken met standaardcommando's voor gegevensverwerking: SELECT, UPDATE, INSERT, DELETE. Dit komt door het feit dat cursors geen wijzigingsbewerkingen op het volledige gegevensvolume toestaan, en dat de snelheid van het uitvoeren van gegevensverwerkingsbewerkingen met behulp van een cursor merkbaar lager is dan die van standaard SQL-tools.

Als een programma de gegevens die in de cursor zijn geladen, kan wijzigen, wordt het wijzigbaar genoemd. Als we het over cursors hebben, mogen we transactie-isolatie niet vergeten. De ene gebruiker wijzigt een record met behulp van een cursor, terwijl een andere gebruiker dat record leest met zijn eigen cursor. Bovendien kan het hetzelfde record wijzigen, wat het noodzakelijk maakt om de gegevensintegriteit te behouden.

Een cursor declareren

Cursors moeten worden gedeclareerd voordat ze kunnen worden gebruikt. De SQL-standaard gebruikt de volgende syntaxis om een ​​cursor te maken:

Declareer cursornaam cursor voor select_statement ])]

Deze expressie declareert een cursor cursor declareren met de naam "cursornaam".

ONGEVOELIG er wordt een statische cursor gemaakt waarmee geen wijzigingen kunnen worden aangebracht. Bovendien worden wijzigingen die door andere gebruikers zijn aangebracht, niet weergegeven. Als het trefwoord INSENSITIVE ontbreekt, wordt er een dynamische cursor gemaakt.

Bij gebruik van een trefwoord ROL de gemaakte cursor kan in elke richting worden gescrolld, zodat u alle selectieopdrachten kunt toepassen. Als dit argument wordt weggelaten, zal de cursor opeenvolgend zijn, d.w.z. het bekijken ervan zal slechts in één richting mogelijk zijn: van begin tot eind.

Uitdrukking selecteer_verklaring geeft een structuur aan voor het lezen van informatie, zoals select ... from ... . Het mag de operator niet bevatten naar binnen, omdat de cursor een eigen operator heeft ophalen om variabelen te vullen met cursorgegevens.

Bij het opgeven van een argument VOOR ALLEEN LEZEN er wordt een alleen-lezen cursor gemaakt en er zijn geen wijzigingen aan de gegevens toegestaan. Een dynamische cursor kan worden gedeclareerd als een alleen-lezen cursor, waardoor wijzigingen die door een andere gebruiker zijn aangebracht, kunnen worden weergegeven.

Een cursor maken met een argument VOOR UPDATE Hiermee kunt u wijzigingen aanbrengen in de gegevens in de cursor, hetzij in opgegeven kolommen, hetzij, als er geen argument is OF kolomnaam, in alle kolommen.

U kunt meerdere cursors declareren in een subroutine. Maar elke cursor moet een unieke naam hebben. Om een ​​cursor te openen moet u de operator gebruiken open waarmee de eerder gedeclareerde cursor wordt geopend:

Cursor geopend

SQL definieert de volgende syntaxis voor het openen van een cursor "cursor open":

Open cursornaam;

Gegevens ophalen van een cursor, cursor ophalen

De syntaxis voor het lezen van gegevens van een cursor in sommige variabelen is als volgt:

Haal cursornaam op in var_name [, var_name] ...;

Exploitant ophalen selecteert open cursorgegevens in variabelen die zich erna bevinden naar binnen en verplaatst de cursor naar de volgende positie.

Cursor sluiten

Exploitant dichtbij sluit de cursor. Als de operator niet expliciet is opgegeven, wordt de cursor automatisch gesloten wanneer het bijbehorende programmablok wordt gesloten.

Sluit cursornaam;

Na het sluiten is de cursor ontoegankelijk. Bij het sluiten worden alle sloten vrijgegeven die waren geïnstalleerd terwijl de cursor liep. Alleen open cursors kunnen worden gesloten. Een gesloten maar niet vrijgegeven cursor kan opnieuw worden geopend. Het is niet toegestaan ​​een ongeopende cursor te sluiten.

Elk DBMS heeft zijn eigen bijzonderheden bij het gebruik van een cursor.

Kenmerken van het gebruik van cursors in Oracle

Er zijn vier cursorattributen in PL/SQL %GEVONDEN, %NIET GEVONDEN, %IS GEOPEND En %AANTAL RIJEN. Cursorkenmerken worden gedeclareerd zoals de operatoren %TYPE en %ROWTYPE, rechts van de cursornaam.

%FOUND-kenmerk

%NOTFOUND-kenmerk

Het %NOTFOUND attribuut is precies het tegenovergestelde van %FOUND.

%ISOPEN-kenmerk

Het %ISOPEN attribuut geeft alleen aan of de cursor open is of niet.

%ROWCOUNT kenmerk

Attribuut %AANTAL RIJEN is een numeriek attribuut dat het aantal rijen retourneert dat door de cursor op een bepaald tijdstip is gelezen.

Voorbeeld van een SQL-cursor in een Oracle DBMS

Declareer v_id managers.id %TYPE; v_naam managers.naam%TYPE; v_comm managers.comm%TYPE; crs-cursor voor selecteer id, naam, sum(comm) als comm van managers waarbij gegevens tussen "01-11-2014" en "30-11-2014" worden gegroepeerd op id, naam; begin crs te openen; lus EXIT WANNEER crs%NOFOUND; FETCH crs in v_id, v_name, v_comm; invoegen in bonus(id, name, comm) waarden (crs.id, crs.name, crs.comm); eindlus; verbinden;

sluit crs; einde;

Kenmerken van het gebruik van cursors in SQL Server

Cursors die in MSSQL worden gebruikt, kunnen opeenvolgend of schuifbaar zijn. Met Sequential kunt u gegevens in slechts één richting selecteren: van begin tot eind. Met schuifbare cursors kunt u in beide richtingen bewegen en kunt u naar een willekeurige rij in de resultatenset van de cursor springen.

Bij een statisch cursorontwerp wordt informatie op een bepaald moment opgeslagen als een momentopname. Wijzigingen die door een andere gebruiker in de database zijn aangebracht, zijn daarom niet zichtbaar. Terwijl de cursor wordt geopend, stelt de server een vergrendeling in op alle rijen in de volledige resultatenset. Een statische cursor verandert niet na het aanmaken en geeft altijd de dataset weer die bestond op het moment dat deze werd geopend. Als andere gebruikers de gegevens in de cursor in de brontabel wijzigen, heeft dit geen invloed op de statische cursor. Het is onmogelijk om wijzigingen aan te brengen in een statische cursor, dus deze wordt altijd geopend in de modus Alleen-lezen.

Een dynamische cursor vereist extra netwerkoverhead en softwarebronnen. Bij het gebruik van dynamische cursors wordt er geen volledige kopie van de gegevens gemaakt, maar worden selecties uit de brontabellen alleen uitgevoerd wanneer de gebruiker toegang heeft tot bepaalde gegevens. Tijdens het ophalen vergrendelt de server de rijen, en alle wijzigingen die de gebruiker aanbrengt in de volledige resultatenset van de cursor zullen zichtbaar zijn in de cursor. Zodra de cursor echter gegevens heeft opgehaald, worden wijzigingen die door een andere gebruiker zijn aangebracht, niet langer in de cursor weergegeven.

Een cursor die wordt bestuurd door een reeks toetsen heeft eigenschappen tussen statisch en dynamisch. Gegevens worden geïdentificeerd op het moment van de bemonstering en zo worden wijzigingen bijgehouden. Dit type cursor is handig bij het implementeren van achterwaarts scrollen. In dit geval zijn gegevenstoevoegingen en -verwijderingen pas zichtbaar als de informatie is bijgewerkt en de cursor de nieuwe versie van het record selecteert als er wijzigingen in zijn aangebracht.

Statische cursors kunnen het beste worden gebruikt voor informatieverwerkingssystemen, d.w.z. voor rapportagesystemen of voor statistische en analytische doeleinden. Een statische cursor is beter in het ophalen van grote hoeveelheden gegevens. In systemen voor elektronische aankopen of reserveringen van objecten (zitplaatsen, tickets) is het noodzakelijk om bijgewerkte informatie dynamisch waar te nemen wanneer er wijzigingen worden aangebracht. In dergelijke gevallen wordt een dynamische cursor gebruikt. Bij deze toepassingen is de hoeveelheid overgedragen gegevens doorgaans klein en toegankelijk op individueel recordniveau.

Met opeenvolgende cursors kunnen gegevens niet in de omgekeerde richting worden opgehaald, alleen vanaf het begin tot het einde van de cursor. Een sequentiële cursor slaat niet een set van alle gegevensrijen op. Ze worden uit de database gelezen zodra er een selectie in de cursor wordt gemaakt, waardoor alle wijzigingen die door gebruikers in de database zijn aangebracht dynamisch kunnen worden weergegeven met behulp van de opdrachten INSERT, UPDATE, DELETE. De cursor leest de meest recente gegevensstatus.

Cursorverklaring

Declareer cursornaam cursor voor SELECT_statement ]]

Bij gebruik van een trefwoord LOKAAL Er wordt een lokale cursor gemaakt die alleen zichtbaar is binnen het blok, de trigger, de opgeslagen procedure of de door de gebruiker gedefinieerde functie. Trefwoord GLOBAAL, definieert een globale cursor die bestaat totdat de huidige verbinding wordt gesloten.

Exploitant FORWARD_ONLY definieert een sequentiële cursor waarmee gegevens alleen in de richting van de eerste tot de laatste rij kunnen worden opgehaald. Bij gebruik van de operator ROL Er wordt een schuifbare cursor gemaakt waarmee gegevens in elke volgorde en in elke richting toegankelijk zijn.

Het cursortype wordt bepaald door de operators:

• STATISCH - een statische cursor maken;
• DYNAMISCH - een dynamische cursor creëren;
• KEYSET - een sleutelcursor maken.

Als voor cursor ALLEEN LEZEN argument opgeven SNEL VOORUIT, dan wordt de gemaakte cursor geoptimaliseerd voor snelle toegang tot gegevens. Dit argument kan niet worden gebruikt in combinatie met argumenten FORWARD_ONLY En OPTIMISTISCH.

Als de cursor is gemaakt met de operator OPTIMISTISCH, dan is het verboden rijen te wijzigen of te verwijderen die zijn gewijzigd nadat de cursor werd geopend.

Bij het opgeven van een argument TYPE_WAARSCHUWING de server rapporteert een impliciete wijziging van het cursortype als deze niet compatibel is met de SELECT-query.

Gegevens ophalen van een cursor, fetch

Onmiddellijk nadat u de cursor hebt geopend, kunt u de inhoud ervan opvragen met behulp van de volgende opdracht:

Bij gebruik van de operator EERST de eerste rij van de resultatenset van de cursor wordt geretourneerd, wat de huidige rij wordt. Wanneer gespecificeerd LAATST de laatste regel van de cursor wordt geretourneerd. Het wordt ook de huidige lijn.

Bij het opgeven van een operator VOLGENDE de rij onmiddellijk na de huidige in de resultatenset wordt geretourneerd. Deze lijn wordt de huidige lijn. Standaardopdracht OPHALEN gebruikt precies deze methode voor het ophalen van rijen.

Bij het opgeven van een operator VOORAFGAAND de regel vóór de huidige wordt geretourneerd. Deze lijn wordt de huidige lijn.

Exploitant ABSOLUTE (regelnummer | @regelnummer_variabele) retourneert een rij met het absolute rangtelwoord in de volledige resultatenset van de cursor. Het regelnummer kan worden opgegeven met behulp van een constante of als de naam van een variabele waarin het regelnummer is opgeslagen. De variabele moet een geheel getal-gegevenstype zijn. Zowel positieve als negatieve waarden worden aangegeven. Wanneer u een positieve waarde opgeeft, wordt de tekenreeks vanaf het begin van de set geteld, terwijl een negatieve waarde vanaf het einde wordt geteld. De geselecteerde regel wordt de huidige regel. Als er een nulwaarde is opgegeven, wordt er geen rij geretourneerd.

Argument RELATIEF (aantal rijen | @variabel aantal rijen) retourneert de lijn die het opgegeven aantal lijnen na de huidige lijn verplaatst. Als u een negatief aantal rijen opgeeft, wordt de rij geretourneerd die het opgegeven aantal rijen vóór de huidige bevat. Als u een nulwaarde opgeeft, wordt de huidige rij geretourneerd. De geretourneerde rij wordt de huidige rij.

Om een ​​globale cursor te openen, moet u een trefwoord opgeven vóór de naam ervan GLOBAAL. De cursornaam kan ook worden opgegeven met behulp van een variabele.

In expressie INTO @variabele_naam [,...n] er wordt een lijst met variabelen gedefinieerd waarin de overeenkomstige kolomwaarden van de geretourneerde rij worden opgeslagen. De volgorde van de variabelen moet overeenkomen met de volgorde van de kolommen in de cursor, en het gegevenstype van de variabele moet overeenkomen met het gegevenstype in de cursorkolom.

Gegevens wijzigen en verwijderen met behulp van een cursor

Om gegevens met behulp van een cursor te wijzigen, moet u een UPDATE-opdracht in het volgende formaat geven:

In één handeling kunnen de waarden van meerdere kolommen van de huidige cursorrij worden gewijzigd, maar ze moeten allemaal tot dezelfde tabel behoren.

Om gegevens te verwijderen met behulp van een cursor, gebruikt u de DELETE-opdracht in het volgende formaat:

Als gevolg hiervan wordt de huidige regel in de cursor verwijderd.

Geheugen vrijmaken, toewijzing ongedaan maken

Gebruik de opdracht om een ​​cursor uit het geheugen te verwijderen

De toewijzing van cursornaam ongedaan maken;

@@FETCH_STATUS-kenmerk

Om de aanwezigheid van rijen in de cursor te bepalen, moet u een globale variabele gebruiken @@FETCH_STATUS, die een waarde krijgt die niet nul is als er geen rijen meer in de cursor staan. Als de reeks rijen nog niet is uitgeput, is @@FETCH_STATUS gelijk aan nul.

Voorbeeld van een cursor in SQL-server

Declareer @company varchar(50), @manager varchar(50), @message varchar(256); declareer crs_clients cursor lokaal voor geselecteerd bedrijf, manager van klanten waarbij stad = "Moskou" sorteer per bedrijf, manager; print "Lijst met klanten"; open crs_clients; haal volgende op van crs_clients naar @company, @manager; terwijl @@FETCH_STATUS = 0 begin selecteer @message = "Bedrijf " + @bedrijf + " manager " + @manager; @bericht afdrukken; -- ga naar de volgende record, haal de volgende op van crs_clients naar @company, @manager; einde; sluit crs_clients; toewijzing van crs_clients ongedaan maken;