Odstranitev iz ms sql kazalca. Kazalci v shranjenih procedurah MySQL. A. Uporaba preprostega kazalca in sintakse
Koncept kazalca
Poizvedba po relacijski bazi podatkov običajno vrne več vrstic (zapisov) podatkov, vendar aplikacija obdeluje samo en zapis naenkrat. Tudi če obravnava več vrstic hkrati (na primer prikaz podatkov v obliki preglednic), je njihovo število še vedno omejeno. Poleg tega je pri spreminjanju, brisanju ali dodajanju podatkov delovna enota serija. V tej situaciji pride v ospredje koncept kazalca in v tem kontekstu je kazalec kazalec na vrstico.
Kazalec v SQL je območje v pomnilniku baze podatkov, ki je zasnovano za shranjevanje zadnjega stavka SQL. Če je trenutni stavek poizvedba po bazi podatkov, je v pomnilnik shranjena tudi vrstica podatkov poizvedbe, imenovana trenutna vrednost ali trenutna vrstica kazalca. Določeno področje v pomnilniku je poimenovano in dostopno uporabniškim programom.
Običajno se kazalci uporabljajo za izbiro podnabora informacij, shranjenih v bazi podatkov. V danem trenutku lahko aplikacijski program preveri eno vrstico kurzorja. Kazalci se pogosto uporabljajo v stavkih SQL, vdelanih v aplikacijske programe, napisane v proceduralnih jezikih. Nekatere od njih implicitno ustvari strežnik baze podatkov, druge pa definirajo programerji.
V skladu s standardom SQL lahko pri delu s kazalci ločimo naslednja glavna dejanja:
- ustvarjanje oz deklaracija kurzorja;
- odpiranje kazalca, tj. polnjenje s podatki, ki so shranjeni v večnivojskem pomnilniku;
- izbor iz kazalca in z njim spreminjanje podatkovnih vrstic;
- zapiranje kazalca, po kateri postane nedostopen za uporabniške programe;
- sprostitev kazalca, tj. brisanje kazalca kot objekta, ker ni nujno, da se z njim zapre, sprosti pomnilnik.
Definicija kazalca se lahko med implementacijami nekoliko razlikuje. Na primer, včasih mora razvijalec izrecno sprostiti pomnilnik, dodeljen kazalcu. Po spustite kazalec sprosti se tudi njegov povezani pomnilnik. To omogoča ponovno uporabo njegovega imena. V drugih izvedbah, ko zapiranje kazalca sprostitev pomnilnika se zgodi implicitno. Takoj po okrevanju postane na voljo za druge operacije: odpiranje drugega kazalca itd.
V nekaterih primerih je uporaba kazalca neizogibna. Če pa je le mogoče, se temu izogibajte in delajte s standardnimi ukazi za obdelavo podatkov: SELECT, UPDATE, INSERT, DELETE. Poleg tega, da kazalci ne omogočajo spreminjanja celotnega obsega podatkov, je hitrost izvajanja operacij obdelave podatkov s kazalcem opazno nižja kot pri standardna sredstva SQL.
Implementacija kurzorjev v okolju MS SQL Server
SQL Server podpira tri vrste kazalcev:
- Kazalci SQL se uporabljajo predvsem znotraj sprožilcev, shranjenih procedur in skriptov;
- strežniški kazalci delujejo na strežniku in implementirajo aplikacijski programski vmesnik za ODBC, OLE DB, DB_Library;
- Odjemalski kazalci so implementirani na samem odjemalcu. Iz strežnika pridobijo celoten niz rezultatov vrstic in ga shranijo lokalno, kar pospeši obdelavo podatkov z zmanjšanjem izgubljenega časa, porabljenega za omrežne operacije.
Različne vrste večuporabniških aplikacij zahtevajo različne vrste vzporednega dostopa do podatkov. Nekatere aplikacije zahtevajo takojšen dostop do informacij o spremembah v bazi podatkov. To je značilno za sisteme za rezervacijo vstopnic. V drugih primerih, kot so sistemi za statistično poročanje, je stabilnost podatkov pomembna, ker če se nenehno spreminjajo, programi ne bodo mogli učinkovito prikazati informacij. Različne aplikacije potrebujejo različne izvedbe kazalcev.
V strežniku SQL Server se vrste kazalcev razlikujejo glede na zmogljivosti, ki jih ponujajo. Tip kurzorja je določen na stopnji njegovega ustvarjanja in ga ni mogoče spremeniti. Nekatere vrste kazalcev lahko zaznajo spremembe, ki so jih drugi uporabniki naredili v vrsticah, vključenih v nabor rezultatov. Vendar SQL Server sledi spremembam takšnih vrstic samo med dostopom do vrstice in ne dovoli spreminjanja sprememb, ko je vrstica že prebrana.
Kazalci so razdeljeni v dve kategoriji: zaporedno in se lahko premikate. Zaporedna omogočajo izbiro podatkov samo v eno smer – od začetka do konca. Drsni kazalci zagotavljajo večjo svobodo delovanja - mogoče se je premikati v obe smeri in skočiti na poljubno vrstico niza rezultatov kazalca.Če je program sposoben spreminjati podatke, na katere kaže kazalec, se imenuje pomični in spremenljivi. Ko že govorimo o kazalcih, ne smemo pozabiti na izolacijo transakcij. Ko en uporabnik spreminja zapis, ga drugi prebere s svojim kazalcem, poleg tega pa lahko isti zapis spreminja, zaradi česar je potrebno ohraniti celovitost podatkov.
SQL Server podpira statično, dinamično, zaporedno in nadzorovan z nizom ključev.
V shemi z statični kazalec informacije se iz baze podatkov preberejo enkrat in shranijo kot posnetek (od neke točke v času), tako da spremembe, ki jih je v bazo podatkov naredil drug uporabnik, niso vidne. Za nekaj časa odpiranje kazalca strežnik zaklene vse vrstice, vključene v njegov celoten nabor rezultatov. Statični kazalec se po ustvarjanju ne spremeni in vedno prikaže nabor podatkov, ki je obstajal ob njegovem odprtju.
Če drugi uporabniki spremenijo podatke, vključene v kazalec v izvorni tabeli, to ne bo vplivalo na statični kazalec.
IN statični kazalec Spreminjati ni mogoče, zato se vedno odpre v načinu samo za branje.
Dinamični kazalec vzdržuje podatke v stanju »v živo«, vendar to zahteva omrežne in programske vire. Uporaba dinamični kazalci ne ustvari se popolna kopija izvornih podatkov, ampak se izvede dinamična selekcija iz izvornih tabel šele, ko uporabnik dostopa do določenih podatkov. Med pridobivanjem strežnik zaklene vrstice in vse spremembe, ki jih uporabnik naredi v celotnem nizu rezultatov kazalca, bodo vidne v kazalcu. Če pa je drug uporabnik naredil spremembe, potem ko je kazalec pridobil podatke, se te ne bodo odražale v kazalcu.
Kazalec, ki ga upravlja niz tipk, je na sredini med tema skrajnostma. Zapisi so identificirani v času vzorčenja in tako sledijo spremembam. Ta vrsta kazalca je uporabna pri uporabi drsenja nazaj - takrat dodajanje in brisanje vrstic ni vidno, dokler se informacije ne posodobijo in gonilnik ne izbere nova različica evidenc, če so bile v njih opravljene spremembe.
Zaporedni kazalci ne smejo pridobivati podatkov v obratni smeri. Uporabnik lahko izbira samo vrstice od začetka do konca kazalca. Serijski kazalec ne shrani nabora vseh vrstic. Preberejo se iz baze podatkov takoj, ko so izbrani v kazalcu, kar omogoča, da se vse spremembe, ki jih uporabniki naredijo v bazi podatkov, dinamično odražajo z ukazi INSERT, UPDATE, DELETE. Kazalec prikazuje zadnje stanje podatkov.
Statični kazalci zagotoviti stabilen pogled na podatke. Primerni so za sisteme za "skladiščenje" informacij: aplikacije za sisteme poročanja ali za statistične in analitične namene. Poleg tega statični kazalec bolje kot drugi obvlada vzorčenje velikih količin podatkov. Nasprotno pa sistemi za elektronsko nakupovanje ali rezervacijo vozovnic zahtevajo dinamično zaznavanje posodobljenih informacij, ko pride do sprememb. V takih primerih se uporablja dinamični kazalec. V teh aplikacijah je količina prenesenih podatkov običajno majhna in dostopna na ravni vrstice (posamezni zapis). Skupinski dostop je zelo redek.
Upravljanje kazalcev v okolju MS SQL Server
Nadzor kazalca implementiran z izvajanjem naslednjih ukazov:
- DECLARE - ustvarjanje oz deklaracija kurzorja;
- ODPRTO - odpiranje kazalca, tj. polnjenje s podatki;
- PRINESI izbor iz kazalca in spreminjanje podatkovnih vrstic z uporabo kazalca;
- ZAPRTI – zapiranje kazalca;
- DODELITEV – sprostitev kazalca, tj. brisanje kazalca kot predmeta.
Deklaracija kazalca
Standard SQL ponuja naslednji ukaz za ustvarjanje kazalca:
Uporaba ključne besede INSENSITIVE bo ustvarila statični kazalec. Spremembe podatkov niso dovoljene in spremembe drugih uporabnikov niso prikazane. Če ključna beseda INSENSITIVE manjka, a dinamični kazalec.
Ko podate ključno besedo SCROLL, se lahko po ustvarjenem kazalcu premikate v katero koli smer, kar vam omogoča uporabo poljubnih izbirnih ukazov. Če je ta argument izpuščen, bo kazalec dosledno, tj. njegov ogled bo možen le v eno smer – od začetka do konca.
Stavek SELECT podaja telo zahteve SELECT, ki določa nastali niz vrstic za kazalec.
Podajanje FOR READ_ONLY ustvari kazalec samo za branje in ne dovoljuje nobenih sprememb podatkov. Razlikuje se od statičnega, čeprav tudi slednji ne omogoča spreminjanja podatkov. Lahko se deklarira kot kazalec samo za branje dinamični kazalec, ki bo omogočil prikaz sprememb, ki jih je naredil drug uporabnik.
Ustvarjanje kazalca z argumentom FOR UPDATE vam omogoča izvajanje v kazalcu sprememba podatkov bodisi v podanih stolpcih ali, če ni argumenta OF column_name, v vseh stolpcih.
V okolju MS SQL Server je sprejeta naslednja sintaksa za ukaz za ustvarjanje kazalca:
<создание_курсора>::= NAJAVI kursor_name CURSOR FOR SELECT_statement ]]
Z uporabo ključne besede LOCAL boste ustvarili lokalni kazalec, ki je viden le v obsegu paketa, sprožilca, shranjene procedure ali funkcija po meri. Ko se paket, sprožilec, procedura ali funkcija konča, je kazalec implicitno uničen. Če želite prenesti vsebino kazalca izven konstrukta, ki ga je ustvaril, morate njegovemu parametru dodeliti argument OUTPUT.
Če je podana ključna beseda GLOBAL, se ustvari globalni kazalec; obstaja, dokler trenutna povezava ni zaprta.
Določanje FORWARD_ONLY ustvari serijski kazalec; Podatke je mogoče vzorčiti samo v smeri od prve vrstice do zadnje.
Določanje SCROLL ustvari drsni kazalec; Do podatkov je mogoče dostopati v poljubnem vrstnem redu in v kateri koli smeri.
Določanje STATIC ustvari statični kazalec.
Če navedete KEYSET, ustvarite kazalec tipke.
Določanje DYNAMIC ustvarja dinamični kazalec.
Če podate argument FAST_FORWARD za kazalec READ_ONLY, bo ustvarjeni kazalec optimiziran za hiter dostop do podatkov. Tega argumenta ni mogoče uporabiti v povezavi z argumentoma FORWARD_ONLY ali OPTIMISTIC.
Kazalec, ustvarjen z argumentom OPTIMISTIC, preprečuje spreminjanje ali brisanje vrstic, ki so bile spremenjene po odpiranje kazalca.
S podajanjem argumenta TYPE_WARNING bo strežnik obvestil uporabnika o implicitni spremembi tipa kazalca, če ta ni združljiv s poizvedbo SELECT.
Odpiranje kazalca
Za odpiranje kazalca in ga napolnite s podatki iz poizvedbe SELECT, podane pri ustvarjanju kazalca, uporabite naslednji ukaz:
Po odpiranje kazalca Izvede se povezan stavek SELECT, katerega izhod je shranjen v večnivojskem pomnilniku.
Pridobivanje podatkov iz kazalca
Takoj po odpiranje kazalca njegovo vsebino (rezultat izvajanja ustrezne poizvedbe) lahko izberete z naslednjim ukazom:
Če navedete FIRST, boste vrnili prvo vrstico celotnega nabora rezultatov kazalca, ki postane trenutna vrstica.
Podajanje LAST vrne zadnjo vrstico kazalca. Postane tudi trenutna vrstica.
Podajanje NEXT vrne vrstico takoj za trenutno v celotnem nizu rezultatov. Zdaj postaja aktualno. Ukaz FETCH privzeto uporablja to metodo za pridobivanje vrstic.
Ključna beseda PRIOR vrne vrstico pred trenutno. Postane aktualno.
Prepir ABSOLUTNO (številka_vrstice | @spremenljivka_številke_vrstice) vrne vrstico po njeni absolutni redni številki v celotnem nizu rezultatov kazalca. Številko vrstice lahko podate s konstanto ali kot ime spremenljivke, v kateri je shranjena številka vrstice. Spremenljivka mora biti celoštevilski podatkovni tip. Navedene so tako pozitivne kot negativne vrednosti. Pri podajanju pozitivne vrednosti se niz šteje od začetka niza, medtem ko se negativna vrednost šteje od konca. Izbrana vrstica postane trenutna vrstica. Če je podana ničelna vrednost, ni vrnjena nobena vrstica.
Prepir RELATIVNO (število vrstic | @spremenljivo število vrstic) vrne vrstico, ki je podano število vrstic za trenutno. Če podate negativno število vrstic, bo vrnjena vrstica, ki je podano število vrstic pred trenutno. Če navedete ničelno vrednost, bo vrnjena trenutna vrstica. Vrnjena vrstica postane trenutna vrstica.
Za odprite globalni kazalec, morate pred njegovim imenom podati ključno besedo GLOBAL. Ime kazalca lahko podate tudi s spremenljivko.
V oblikovanju INTO @ime_spremenljivke [,...n] je določen seznam spremenljivk, v katerem bodo shranjene ustrezne vrednosti stolpca vrnjene vrstice. Vrstni red podajanja spremenljivk se mora ujemati z vrstnim redom stolpcev v kazalcu, podatkovni tip spremenljivke pa se mora ujemati s podatkovnim tipom v stolpcu kazalca. Če konstrukcija INTO ni navedena, bo vedenje ukaza FETCH podobno vedenju ukaza SELECT - podatki so prikazani na zaslonu.
Spreminjanje in brisanje podatkov
Če želite narediti spremembe s kazalcem, morate izdati ukaz UPDATE v naslednji obliki:
V eni operaciji je mogoče spremeniti več stolpcev trenutne vrstice kazalca, vendar morajo vsi pripadati isti tabeli.
Za brisanje podatkov s kazalcem uporabite ukaz DELETE v naslednji obliki:
Posledično bo trenutni nabor vrstic v kazalcu izbrisan.
Zapiranje kazalca
Po zaprtju kazalec postane uporabnikom programa nedostopen. Ko je zaprt, se odstranijo vse ključavnice, nameščene med njegovim delovanjem. Zapiranje je mogoče uporabiti samo za odprte kazalce. Zaprto pa ne osvobojeni kazalec se lahko ponovno odpre. Neodprtega kazalca ni dovoljeno zapreti.
Spustite kazalec
Zapiranje kazalca ni nujno, da sprosti pomnilnik, povezan z njim. Nekatere izvedbe ga morajo eksplicitno sprostiti z uporabo stavka DEALLOCATE. Po spustite kazalec Sprosti se tudi pomnilnik, kar omogoča ponovno uporabo imena kazalca.
Če želite nadzorovati, ali je bil dosežen konec kazalca, je priporočljivo uporabiti funkcijo: @@FETCH_STATUS
Funkcija @@FETCH_STATUS vrne:
0, če je bil prenos uspešen;
1, če pridobivanje ni uspelo zaradi poskusa pridobivanja vrstice zunaj kazalca;
2, če pridobivanje ni uspelo zaradi poskusa dostopa do izbrisane ali spremenjene vrstice.
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 "Nakupovalni seznam" DECLARE klient_cursor CURSOR LOCAL FOR SELECT Koda stranke, podjetje, priimek FROM Client WHERE Mesto="Moskva" ORDER BY Podjetje, priimek OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 BEGIN SELECT @message="Odjemalec "+@fam+ "Podjetje "+ @firm PRINT @message SELECT @message="Ime izdelka Datum nakupa Cena" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Product.Name, Transaction.Date, Product .Price* Transaction.Quantity AS Cost FROM Product INNER JOIN Transaction ON Product. Koda izdelka=Transakcija.Koda izdelka WHERE Transaction.Customer Code=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "Brez nakupov" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Skupni stroški "+ CAST(@s AS CHAR(6)) PRINT @message -- premakni se na naslednjega odjemalca-- PRINESI NASLEDNJEGA IZ klient_cursor V @id_kl, @firm, @fam KONEC ZAPRI klient_cursor DEALLOCATE klient_cursor Primer 13.6. Kazalec za prikaz seznama blaga, ki so ga kupile stranke iz Moskve, in njihovih skupnih stroškov.
Primer 13.7. Razvijte drsni kazalec za stranke iz Moskve. Če se telefonska številka začne z 1, izbrišite stranko s to številko in v prvem vnosu kurzorja zamenjajte prvo številko telefonske številke s 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "Seznam strank" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Firma, Last name, Phone FROM Client WHERE City ="Moskva" ORDER BY Podjetje, Priimek ZA POSODOBITEV ODPRI klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+ " Company "+ @firm " Telefon "+ @tel NATISNI @sporočilo -- če se telefonska številka začne z 1, -- izbriši stranko s to številko IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- premakni se na naslednjo odjemalec FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- pri prvem vnosu prvo številko v telefonski številki zamenjajte s 4 UPDATE Client SET Phone ='4' + RIGHT(@ tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm "Phone "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Primer 13.7. Drsni kazalec za stranke iz Moskve.
Primer 13.8. Uporaba kazalec kot izhodni parameter postopka. Postopek vrne nabor podatkov – seznam izdelkov.
Priklic postopka in tiskanje podatkov iz izhodnega kazalca se izvede na naslednji način:
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n IZBERI @n KONEC ZAPRI @my_cur DODELJI @my_cur
Kazalec je povezava do kontekstualnega pomnilniškega območja. V nekaterih izvedbah programskega jezika SQL (Oracle, Microsoft SQL Server) - nabor rezultatov, dobljen pri izvajanju poizvedbe, in povezan kazalec trenutnega zapisa. Rekel bi, da kazalec je virtualna miza ki je alternativna shramba podatkov. V tem primeru vam kazalec omogoča dostop do njegovih podatkov, kot da bi šlo za podatke navadne matrike.
Kazalci se uporabljajo v shranjenih procedurah. Dovolj teorije, poglejmo primer:
Imamo bazo podatkov (baza ni dobra, ta je ena mojih laboratorijsko delo, vendar je naš učitelj zbirke podatkov vztrajal pri takšni strukturi)
/*bančni podatki*/
USTVARI TABELO `banka` (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
PRIMARNI KLJUČ (»BankId«))ENGINE=InnoDB
NABOR ZNAKOV "utf8" COLLATE "utf8_bin" ;
/*podatki o depozitih */
USTVARI TABELO `bankdistribution` (
`BankId` INTEGER (11) NI NULL,
`Persent` INTEGER (11) DEFAULT NULL,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL,
PRIMARNI KLJUČ(`BankId`, `ClientId`),
KLJUČ `BankId` (`BankId`),
KLJUČ `Id odjemalca` (`Id odjemalca`),
OMEJITEV `bankdistribution_fk` TUJI KLJUČ (`BankId`) REFERENCE `banka` (`BankId`),
OMEJITEV `bankdistribution_fk1` TUJI KLJUČ (`ClientId`) REFERENCE `client` (`ClientId`)
)ENGINE=InnoDB
/*podatki o vlagateljih*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL,
`Priimek` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Ime` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Ime` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Telefon` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Naslov` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARNI KLJUČ (`Id stranke`, `Id kreditne kartice`),
KLJUČ `Id odjemalca` (`Id odjemalca`))ENGINE=InnoDB
AUTO_INCREMENT=11 NIZ ZNAKOV "utf8" ZBIRANJE "utf8_bin"
Recimo, da moramo prejeti vsako banko po vrsti in z njo izvesti nekaj dejanj, pri tem nam lahko pomaga naslednja poizvedba
Izberite `banka`.* IZ `banke` OMEJITEV ŠTEVILA_ZAPISA_KI POTREBUJEMO,1. Tako z uporabo LIMIT WE NEED_RECORD NUMBER, 1, izvlečemo vsak zapis v zanki iz bančne tabele in izvedemo dejanja, ki jih potrebujemo z njim, medtem ko povečamo vrednost WE NEED_RECORD NUMBER za 1. Sedaj bomo naredili isto, vendar z uporabo kazalec
Začeti
/* spremenljivke, kjer ekstrahiramo podatke */
Deklariraj vBankId celo število;
Deklarirajte vBankName VARCHAR(50);
Deklarirajte vAddress VARCHAR(50);
Razglasite vPhone VARCHAR (50);
/* hadlerjeva spremenljivka - a*/
Razglasi dokončano celo število privzeto 0;
/*Deklaracija kazalca*/
Razglasite kazalec BankCursor za Izberite `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` kjer je 1;
/*Namen HANDLER, ki bo razložen spodaj*/
DEKLARACIJO NADALJEVALNEGA OPRAVLJALCA ZA SQLSTATE "02000" SET done=1;
/* odpri kazalec */
Odprite BankCursor;
/*pridobi podatke*/
WHILE done = 0 NAREDI
ukrepamo, kar potrebujemo
END WHILE ;
/*zapiranje kazalca*/
Zapri BankCursor;
KONEC ;* Ta izvorna koda je bila označena z označevalnikom izvorne kode.
Napaka: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Sporočilo: Ni podatkov - nič vrstic pridobljenih, izbranih ali obdelanih
SQLSTATE: 02000 se sproži, ko je dosežen konec kazalca ali ko select ali update vrne prazen niz.
V naslednji vrstici smo razglasili kazalec DECLARE cursor_name CURSOR FOR select_statement;
Odprite kazalec Odprite cursor_name;
Nato, dokler ne dosežemo konca kazalca (MED KONČANJEM = 0 DO), ekstrahiramo podatke in jih obdelamo.
Preden zapustite shranjeno proceduro, morate zapreti kazalec. Zapri cursor_name;
Ne zdi se zapleteno. Toda s SQLSTATE "02000" je povezanih veliko pasti.
WHILE done = 0 NAREDI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
Izberite (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution, kjer je BankId = vBankId limit 1;
naredimo nekaj dejanj
END WHILE ;* Ta izvorna koda je bila označena z označevalnikom izvorne kode.
S sintaksnega vidika je vse v redu in pravilno. Ampak z logičnega vidika ne. Lahko se zgodi, da vlagatelji nimajo odprtih računov v kakšni banki, potem za Izberite (ContributeAmount) INTO vContributeAmountSUM FROM bank distribucijo, kjer BankId = vBankId limit 1; SQLSTATE: 02000 se bo sprožil, spremenljivka done bo nastavljena na 1 in medtem ko zanka konec prej, kot smo pričakovali. Temu se lahko izognete tako, da storite naslednje
WHILE done = 0 NAREDI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* za banko izpiši znesek katerega koli od njenih depozitov */
if (vContributeAmountSUM > 0) then
/* za banko izpiši znesek katerega koli od njenih depozitov */
konec če ;
naredimo nekaj dejanj
END WHILE ;* Ta izvorna koda je bila označena z označevalnikom izvorne kode.
Pri prvi zahtevi smo preverili, ali so prispevki (če jih ni, potem vContributeAmountSUM == 0) in šele če so, pridobimo podatke.
Zdaj pa recimo, da moramo za vsako stranko odstraniti skupni znesek na računih v različnih bankah
Razglasite kazalec ClientSummCursor za Izberite vsoto
Razglasi kazalec ClientSummCursor za Select sum (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` IZ `bankdistribution` Notranja pridružitev odjemalca na (client.ClientId = bankdistribution.`ClientId`), kjer je 1 skupina po `bankdistribution`. `Id odjemalca`;Odprite ClientSummCursor;
WHILE done = 0 NAREDI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* za banko izpiši znesek katerega koli od njenih depozitov */
Izberite Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution, kjer je BankId = vBankId limit 1;
/* preveri, ali so v tej banki res depoziti */
if (vContributeAmountSUM > 0) then
/* za banko izpiši znesek katerega koli od njenih depozitov */
Izberite ContributeAmount INTO vContributeAmountSUM FROM bankdistribution, kjer je BankId = vBankId limit 1;
konec če ;
naredimo nekaj dejanj.
END WHILE ;* Ta izvorna koda je bila označena z označevalnikom izvorne kode.
Ista situacija se lahko pojavi, ko se podatki v kazalcu ClientSummCursor končajo prej kot podatki v BankCursorju, se sproži SQLSTATE: 02000, spremenljivka done je nastavljena na 1 in se zanka while konča prej, kot smo pričakovali. Temu se lahko izognete tako, da storite naslednje
Odprite ClientSummCursor;
WHILE done = 0 NAREDI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* za banko izpiši znesek katerega koli od njenih depozitov */
Izberite Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution, kjer je BankId = vBankId limit 1;
/* preveri, ali so v tej banki res depoziti */
if (vContributeAmountSUM > 0) then
/* za banko izpiši znesek katerega koli od njenih depozitov */
Izberite ContributeAmount INTO vContributeAmountSUM FROM bankdistribution, kjer je BankId = vBankId limit 1;
konec če ;
/* pred ekstrahiranjem podatkov iz drugega kazalca si zapomnite stanje sqlstate */
SET old_status = končano;
/* izvlečemo podatke, ki jih potrebujemo */
FETCH ClientSummCursor INTO vSum,vClientId;
/* preveri, ali so bili podatki pridobljeni in ali sqlstate 0200 ni uspel */
če (končano = 0), potem
naredimo nekaj dejanj.
konec če ;
/* pred koncem časa obnovi vrednost spremenljivke done */
set done = old_status;
END WHILE ;* Ta izvorna koda je bila označena z označevalnikom izvorne kode.
Hvala vsem, ki ste prebrali do tukaj, upam, da bo komu koristilo.
VELJA ZA: SQL Server (od 2008) Base Podatki SQL Azure SQL Data WarehouseParallel Data Warehouse
Definira atribute kurzorja strežnika Transact-SQL, kot so lastnosti pogleda in poizvedba, uporabljena za izgradnjo niza rezultatov, na katerem deluje kurzor. Stavek DECLARE CURSOR podpira standardno sintakso ISO in sintakso, ki uporablja nabor jezikovnih razširitev Transact-SQL.
ISO sintaksa DECLARE cursor_name [ NEOBČUTLJIVO ] [ SCROLL ] CURSOR FOR select_statement [ FOR ( READ ONLY | UPDATE [ OF column_name [ ,...n ] ] ) ] [;] Razširjena sintaksa Transact-SQL DECLARE ime_kurzorja CURSOR [ LOKALNO | GLOBALNO ] [ SAMO NAPREJ | POMIKANJE ] [ STATIČNO | KEYSET | DINAMIČNO | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIČNO ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
ime_kurzorja
ime_kurzorja
NEOBČUTLJIV
tempdb; tako se spremembe osnovnih tabel ne odražajo v podatkih, ki jih vrnejo izbire tega kazalca, in tega kazalca ni mogoče spremeniti. Če uporabljate sintakso ISO, razen če je podana možnost INSENSITIVE, se odobrene posodobitve in izbrisi osnovnih tabel prikažejo v naslednjih izbirah.
POMIKANJE
Označuje, da so na voljo vse možnosti vzorčenja (PRVI, ZADNJI, PREJŠNJI, NASLEDNJI, RELATIVNO, ABSOLUTNO). Če stavek ISO DECLARE CURSOR ne podaja možnosti SCROLL, je podprta le možnost pridobivanja NEXT. Možnosti SCROLL ni mogoče določiti z možnostjo FAST_FORWARD.
select_statement
Standardno Izjava SELECT, ki definira niz rezultatov kazalca. Ključne besede FOR BROWSE in INTO nista dovoljena select_statement deklaracija kurzorja.
select_statement v nasprotju s kazalcem zahtevane vrste.
LE ZA BRANJE
Nadgradnja ]
ime_stolpca [,
.. .n] določeno, samo navedeni stolpci dovoljujejo spremembe. Če je stavek UPDATE uporabljen brez seznama stolpcev, je posodobitev mogoča za vse stolpce.
ime_kurzorja
Ime Transact-SQL določenega kazalca strežnika. ime_kurzorja morajo upoštevati pravila za identifikatorje.
LOKALNO
Označuje, da je kazalec lokalno glede na paket, shranjeno proceduro ali sprožilec, v katerem je bil ustvarjen. Ime kazalca je veljavno samo v tem območju. Na kazalec se lahko sklicujejo lokalne spremenljivke paketa, shranjene procedure, sprožilci ali izhodni parameter shranjene procedure. Parameter OUTPUT se uporablja za posredovanje lokalnega kazalca klicnemu paketu, shranjeni proceduri ali sprožilcu, ki lahko nato parameter dodeli spremenljivki kazalca za kasnejši dostop do kazalca po zaključku shranjene procedure. Kazalec se implicitno sprosti, ko se paket, shranjena procedura ali sprožilec zaključi z izvajanjem, razen če je bil kazalec posredovan parametru OUTPUT. Če je bil kazalec posredovan parametru OUTPUT, se kazalec sprosti, ko so osvobojene vse spremenljivke, ki se sklicujejo nanj, ali ko se zapusti obseg.
GLOBALNO
Označuje, da je kazalec globalen za povezavo. Ime kazalca lahko uporablja katera koli shranjena procedura ali paket, ki se izvaja v povezavi. Kazalec se implicitno sprosti le, če je povezava prekinjena.
SAMO NAPREJ
Podaja, da je kazalec mogoče videti le od prve do zadnje vrstice. Podprta je samo možnost pridobivanja FETCH NEXT. Če je FORWARD_ONLY podan brez ključnih besed STATIC, KEYSET ali DYNAMIC, se kazalec obnaša kot kazalec DYNAMIC. Če nista podana niti argument FORWARD_ONLY niti argument SCROLL, je privzeti argument FORWARD_ONLY, razen če so prisotne ključne besede STATIC, KEYSET ali DYNAMIC. Kazalci STATIC, KEYSET in DYNAMIC imajo privzeto vrednost SCROLL. Za razliko od API-jev baze podatkov, kot sta ODBC in ADO, način FORWARD_ONLY podpirajo naslednji kazalci Transact-SQL: STATIC, KEYSET in DYNAMIC.
STATIČNO
Definira kazalec, ki ustvari začasno kopijo podatkov za uporabo s kazalcem. Vse poizvedbe do kazalca imajo dostop do podane začasne tabele v tempdb; tako se spremembe osnovnih tabel ne odražajo v podatkih, ki jih vrnejo izbire tega kazalca, in tega kazalca ni mogoče spremeniti.
KEYSET
Označuje, da je članstvo ali vrstni red vrstic v kazalcu nespremenjen, ko ga odprete. Nabor ključev, ki enolično identificirajo vrstice, je vgrajen v tabelo tempdb klical ključi.
Spremembe neključnih vrednosti v osnovnih tabelah, ki jih je naredil lastnik kazalca ali so jih odobrili drugi uporabniki, so prikazane, ko si jih ogleda lastnik kazalca. Spremembe, ki jih naredijo drugi uporabniki, se ne odražajo (spremembe ni mogoče izvesti s kazalcem strežnika Transact-SQL). Če je vrstica izbrisana, poskus pridobivanja vrstic vrne @@FETCH_STATUS -2. Posodobitve ključnih vrednosti zaradi meja kazalca so podobne brisanju stare vrstice in njenemu nato vstavljanju nova vrstica. Vrstica z novimi vrednostmi ni vidna in poskus pridobitve vrstice s starimi vrednostmi vrne @@FETCH_STATUS -2. Posodobitve so vidne takoj, če so narejene prek kazalca z uporabo člena WHERE CURRENT OF.
DINAMIČNO
Definira kazalec, ki prikazuje vse spremembe podatkov, narejene v vrsticah v naboru rezultatov med ogledom tega kazalca. Podatkovne vrednosti, vrstni red in članstvo v vrstici v vsakem izboru se lahko razlikujejo. Dinamični kazalci ne podpirajo izbirne možnosti ABSOLUTE.
HITRO NAPREJ
Označuje FORWARD_ONLY, READ_ONLY kazalec, ki ima omogočeno optimizacijo delovanja. Možnosti FAST_FORWARD ni mogoče določiti z možnostma SCROLL ali FOR_UPDATE.
LE ZA BRANJE
Prepreči spremembe, izvedene prek tega kazalca. Klavzula WHERE CURRENT OF se ne more sklicevati na kazalec v stavku UPDATE ali DELETE. Ta možnost ima prednost pred privzeto funkcijo osveževanja kazalca.
SCROLL_LOCKS
Označuje, da so pozicionirane posodobitve ali brisanja, izvedena s kazalcem, zagotovljeno uspešne. SQL Server zaklene vrstice, ko so prebrane v kazalec, da zagotovi, da so te vrstice na voljo za nadaljnje spremembe. Možnosti SCROLL_LOCKS ni mogoče določiti z možnostjo FAST_FORWARD ali STATIC.
OPTIMISTIČNO
Podaja, da pozicionirane posodobitve ali brisanja, izvedena s kazalcem, ne bodo uspele, če je bila vrstica posodobljena, odkar je bila prebrana v kazalec. SQL Server ne zaklene vrstic, ko so prebrane v kazalec. Namesto tega se uporabljajo primerjave časovni žig vrednosti stolpcev ali kontrolne vsote, če jih tabela nima časovni žig stolpec, da ugotovite, ali se je vrstica spremenila, odkar je bila prebrana v kazalec. Če je bila vrstica spremenjena, bodo poskusi pozicionirane posodobitve ali brisanja neuspešni. Možnosti OPTIMISTIC ni mogoče določiti z možnostjo FAST_FORWARD.
TYPE_WARNING
Podaja, da bo odjemalcu poslano opozorilo, če je kazalec implicitno pretvorjen iz enega zahtevanega tipa v drugega.
select_statement
Standardni stavek SELECT, ki podaja niz rezultatov kazalca. Ključne besede COMPUTE, COMPUTE BY, FOR BROWSE in INTO niso dovoljene v select_statement deklaracija kurzorja.
SQL Server implicitno pretvori kazalec v drugo vrsto, če so klavzule v select_statement v nasprotju s kazalcem zahtevane vrste. Za več informacij glejte Implicitne pretvorbe kazalcev.
ZA POSODOBITEV]
Določa stolpce v kazalcu, ki jih je treba posodobiti. Če OF ime_stolpca [,
... n] je na voljo, samo navedeni stolpci dovoljujejo spremembe. Če je stavek UPDATE uporabljen brez seznama stolpcev, je posodobitev možna za vse stolpce, razen če je podana možnost sočasnosti READ_ONLY.
Stavek DECLARE CURSOR definira atribute kurzorja strežnika Transact-SQL, kot so lastnosti pogleda in poizvedba, uporabljena za gradnjo nabora rezultatov, na katerem deluje kazalec. Stavek OPEN zapolni niz rezultatov, stavek FETCH pa vrne vrstico iz njega. Stavek CLOSE počisti trenutni niz rezultatov, povezan s kazalcem. Stavek DEALLOCATE sprosti vire, ki jih uporablja kazalec.
Prva oblika stavka DECLARE CURSOR uporablja sintakso ISO za podajanje parametrov kazalca. Druga oblika izjave DECLARE CURSOR uporablja razširitve jezika Transact-SQL, ki vam omogočajo, da definirate kazalce z enakimi tipi, kot so tisti, ki se uporabljajo v funkcijah kazalcev API-jev baze podatkov, kot sta ODBC in ADO.
Teh dveh oblik ni mogoče mešati. Če podate SCROLL ali izpustitev ključnih besed pred ključno besedo CURSOR, ne morete uporabiti ključnih besed med CURSOR in tudi za select_statement ključne besede. Pri določanju ključnih besed med KURSORJEM, kot tudi za select_statement ključne besede, ne morete podati SCROLL ali INSENSITIVE pred ključno besedo CURSOR.
Če uporabljate sintakso Transact-SQL za stavek DECLARE CURSOR in ne podate možnosti READ_ONLY, OPTIMISTIC ali SCROLL_LOCKS, se predpostavlja naslednja privzeta vrednost.
Če stavek SELECT ne podpira posodobitev (ali nima zadostnih dovoljenj ali dostopa do oddaljenih tabel, ki ne podpirajo posodobitev itd.), je kazalec nastavljen na READ_ONLY.
Kazalca STATIC in FAST_FORWARD sta privzeto nastavljena na READ_ONLY.
Kazalci DYNAMIC in KEYSET so privzeto nastavljeni na OPTIMISTIC.
Na kazalce se lahko sklicujejo samo drugi stavki Transact-SQL. Funkcije API baze podatkov se ne morejo sklicevati na kazalce. Na primer, ko je kazalec deklariran, se funkcije in metode OLE DB, ODBC ali ADO ne morejo sklicevati na njegovo ime. Vrstic kurzorja ni mogoče izbrati z uporabo ustreznih funkcij in metod API-ja; V ta namen morate uporabiti stavke FETCH Transact-SQL.
Naslednje shranjene procedure lahko uporabite za definiranje lastnosti kazalca, potem ko je bil deklariran.
Spremenljivke se lahko uporabljajo kot del select_statement, v katerem je deklariran kazalec. Vrednosti kurzorskih spremenljivk se po deklaraciji ne spremenijo.
Privzeto so dovoljenja DECLARE CURSOR dodeljena vsem uporabnikom, ki imajo dovoljenje SELECT za poglede, tabele in stolpce, ki jih uporablja kazalec.
Kazalcev ali sprožilcev ne morete uporabiti v tabeli z indeksom columnstore v gručah. Ta omejitev ne velja za negručne indekse; Kazalce in sprožilce lahko uporabite v tabeli z negručenim indeksom columnstore.
A. Uporaba preprostega kazalca in sintakse
Nabor rezultatov, ustvarjen, ko odprete ta kazalec, vključuje vse vrstice in stolpce tabele. Ta kazalec je mogoče posodobiti, vse posodobitve in izbrisi so predstavljeni v izboru za ta kazalec. FETCH``NEXT je samo pridobivanje, ker parameter SCROLL ni bil podan.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. Uporaba ugnezdenih kazalcev za prikaz poročila
Naslednji primer uporablja ugnezdene kazalce za prikaz kompleksnega poročila. Za vsakega ponudnika je deklariran notranji kazalec.
SET NOCOUNT ON ;
DECLARE @vendor_id int, @vendor_name nvarchar (50), @message varchar (80), @product nvarchar (50); NATISNI" -------- Poročilo o izdelkih dobavitelja --------";
DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; ODPRTI vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 ZAČNI TISKATI " " IZBERI @message = "----- Izdelki od prodajalca: "+ @vendor_name PRINT @message -- Razglasite notranji kazalec, ki temelji
-- na vendor_id iz zunanjega kazalca. 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 -- Spremenljiva vrednost iz zunanjega kazalca OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 NATISNI "<
Prav lahko se zgodi, da bo odgovor na preprosto zahtevo stranke vzorec več sto tisoč vrstic, ki je za večino strank neprebavljiv. V tem primeru je rešitev problema interakcije s strankami uporaba kazalcev kot univerzalnega mehanizma za izmenjavo podatkov med strežnikom in stranko. Kazalci delujejo z nizom rezultatov podatkov (rezultat poizvedbe), kar uporabnikom daje dodatne zmožnosti obdelave podatkov:
Kazalci vam omogočajo delo z vrsticami tabele tako, da v naboru podatkov podate njihovo zaporedno številko;
Kazalci vam omogočajo izvajanje zapletenih operacij spreminjanja podatkov, na primer, ko spreminjanje vrednosti stolpca zahteva večkratni dostop do vrednosti drugih stolpcev.
Življenjski cikel kazalca:
Ustvarjanje kazalca: IZJAVA<имя курсора>[NEOBČUTLJIV] [DRSENJE] KAZALO ZA< SELECT -оператор>ZA (SAMO BRANJE | POSODOBITEV)
Tu ključna beseda INSENSITIVE pomeni, da bo kazalec statičen (posnetek podatkov), privzeto pa je kazalec ustvarjen dinamično (pridobivanje se izvede ob vsakem dostopu do vrstice). Ključna beseda SCROLL pomeni, da se kazalec lahko premika v kateri koli smeri, sicer se kazalec ustvari kot "zaporedni" kazalec.
Odpiralni kazalec: ODPRTO [GLOBALNO]<имя курсора>. Kazalec, podan kot GLOBAL, se ne izbriše samodejno, ko se konča postopek ali paket, iz katerega je bil poklican.
Branjepodatke : PRINESI [[ NASLEDNJI | PREJ | PRVI | ZADNJI | ABSOLUTNO n | RELATIVNO n ] OD ] [ GLOBALNO ]<имя курсора>[INTO@ime_spremenljivke,...]. SQL Server 2000 vam omogoča branje samo ene vrstice s kazalca. Ključna beseda FIRST vrne prvo vrstico kazalca; ZADNJA – zadnja vrstica kazalca; NEXT – naslednja vrstica za trenutno, vrnjena vrstica postane trenutna; PRIOR – prejšnji pred trenutnim; ABSOLUTE n – vrne vrstico po absolutni zaporedni številki v kazalcu; RELATIVNO – n vrstic za trenutno. Podatki stolpcev bodo shranjeni v vsaki od navedenih spremenljivk v vrstnem redu, kot so navedene.
Spremeni podatke: izvede ukaz UPDATE s sintakso, zasnovano za delo s kazalci.
Brisanje podatkov: izvede ukaz DELETE s sintakso, zasnovano za delo s kazalci.
Zapiranje kazalca: ZAPRI [GLOBALNO]<имя курсора>
Spustite kazalec: DODELITEV [GLOBALNO]<имя курсора>
Primer uporabe kazalca:
PRIJAVITE fo_curs STATIČNI KAZALEC ZA
IZBERITE name_rus iz fo ORDER BY name_rus
PRIJAVITE @name varchar(50)
FETCH FIRM FROM fo_curs INTO @name
WHILE @@FETCH_STATUS=0
PRINESI NASLEDNJE IZ fo_curs V @name
DODELJI fo_curs
2.7. Zagotavljanje varnosti in integritete podatkov v Microsoft SQL Server. Upravljanje baz podatkov. Vloge. Dodeljevanje pravic uporabnikom (GRANT, DENY, REVOKE). Metode in tehnologije za zaščito podatkov v SQL Server.
Varnost in skrbništvo strežnika SQL Server. .
Glavna naloga DBMS je zagotoviti celovitost in konsistentnost podatkov znotraj izbranega predmetnega področja. Eden od dejavnikov, ki preprečuje sistemu, da bi rešil to težavo, so dejanja uporabnikov, ki po nesreči ali namerno poskušajo uničiti podatkovno strukturo ali spremeniti same podatke. Posledično je treba DBMS zaščititi ne le pred fizičnimi okvarami, ampak tudi pred uporabniki, ki niso primerni za naloge, ki se izvajajo. Za to je potrebno zasnovati in na bazo podatkov povezati varnostni sistem, ki bo uporabnikom onemogočal dejanja, ki presegajo njihova pooblastila.
Upravljanje baze podatkov
Če želite ustvariti bazo podatkov s pomočjo TSQL, uporabite ukaz CREATE DATABASE, vendar se običajno za ta namen uporabljajo zmogljivosti SQL Server Management Studio. IN SQL strežnik Definiranih je kar nekaj operacij baze podatkov: povečevanje (zmanjševanje) velikosti datoteke, spreminjanje konfiguracije (ukaz ALTER), pripenjanje in odklapljanje, prenos lastništva, spreminjanje imena, ogled lastnosti in na koncu brisanje (DROP DATABASE).
Kot večina strežnikov baz podatkov ima tudi SQL Server uporabnika s polnimi skrbniškimi pravicami - to je Sistemski skrbnik ali "sa". Po začetni namestitvi strežnika je geslo sa prazno. Uporabnik, ki ustvari novo zbirko podatkov, samodejno postane njen lastnik ('dbo' - Data Base Owner). Ob izdelavi baze podatkov je definiran tudi uporabnik "gost". Če uporabniški račun ni izrecno preslikan na uporabnika določeni bazi podatkov, je uporabniku odobren implicitni dostop z uporabo imena gosta guest.Guest je običajno prepovedan.
Uporabnik, ki ustvari objekt v bazi podatkov, samodejno postane njegov lastnik in nihče, vključno z dbo in sa, ne more uporabljati tega predmeta, dokler mu lastnik ne dodeli pravic do njega. Da pa lahko uporabnik ustvari objekt, mu mora lastnik baze podatkov najprej podeliti ustrezne pravice.
Vloga vam omogoča združevanje uporabnikov, ki izvajajo iste funkcije, da poenostavite administracijo. Vloge so lahko vgrajene ali prilagojene. Vgrajene vloge so implementirane na ravni strežnika in na ravni baze podatkov. Spodaj je tabela vlog vgrajene zbirke podatkov:
db_owner. Ima vse pravice v bazi podatkov
Db_accessadmin. Lahko doda ali odstrani uporabnike
Db_securityadmin. Upravlja vsa dovoljenja, objekte, vloge in uporabnike
Db_ddladmin. Lahko izvede vse ukaze DDL razen GRANT, DENY, REVOKE
Db_backupoperator. Arhivar lahko izvaja ukaze. podatke
db_datareader. Mogoče ogled. vse podatke v kateri koli tabeli
db_datawriter. Mogoče modifikacija. vse podatke v kateri koli tabeli
Db_denydatareader. Prepovedano pogled ljubezen podatkov v kateri koli mize
Db_denydatawriter. Prepovedati spreminjanje kakršnih koli podatkov v kateri koli tabeli
Dodeljevanje pravic uporabnikom. Osnova varnosti strežnika SQL je (1) Računi(računi); (2) uporabniki; (3) vloge; (4) skupine.
Ko se uporabnik poveže s strežnikom SQL, so dejanja, ki jih lahko izvaja, določena s pravicami, ki so mu podeljene kot uporabniku in članu vloge. Pravice podeljuje skrbnik DBMS, lastnik baze podatkov ali lastnik določenega objekta baze podatkov. Pravice v bazi podatkov lahko razdelimo v tri kategorije: (1) pravice do dostopa do objektov baze podatkov; (2) pravice za izvajanje ukazov TSQL; (3) implicitne pravice. Strežnik omogoča prenos lastništva z enega uporabnika na drugega.
Za upravljanje uporabniških dovoljenj za dostop do objektov zbirke podatkov se uporabljajo naslednji ukazi:
GRANT(VSI |< вид действия >,…}
( VKLOPLJENO (<имя таблицы или представления>} [(<имя столбца>,…)]
| VKLOP(< имя хранимой процедуры >}
| VKLOP(< имя пользовательской функции >}
ZA ( JAVNO |<имя объекта системы безопасности>,…}
[AS<имя группы> | <имя роли>]
– dodeljevanje pravic uporabnikom, Kje
VSE – uporabniku so dodeljena vsa možna dovoljenja, drugače določite
<вид действия>– pravice do dejanj, ki so na voljo uporabniku, in sicer:
IZBERI – za pogled, za stolpec tabele in za tabelo (pogled)
INSERT – dodati, za tabelo (pogled) kot celoto
POSODOBITEV – za spremembo, za stolpec tabele in za tabelo (pogled)
DELETE – za brisanje, za tabelo (pogled) kot celoto
EXECUTE – za izvajanje shranjenih procedur
REFERENCE – zmožnost sklicevanja na določen objekt (vključitev kot del tujega ključa).
<имя объекта системы безопасности>– računi SQL Server, uporabniki domene Windows; JAVNO – za vse uporabnike.
Z MOŽNOSTJO PODELITVE - omogoča uporabniku, ki ima trenutno podeljene pravice, dodelitev pravic dostopa do objekta drugim uporabnikom.
AS<имя группы> | <имя роли>– sodelovanje uporabnika v vlogi, ki ima možnost podeljevanja pravic drugim uporabnikom.
GRANT SELECT ON avtorjem javnosti
ODOBRITE VSTAVITEV, POSODOBITEV, IZBRIŠITE O avtorjih Mary, Johnu, Tomu
GRANT SELECT ON Plan_Data TO Accounting Z MOŽNOSTJO GRANT
GRANT SELECT ON Plan_Data TO Jack AS Accounting
Jack ni član vloge računovodstva, vendar lahko nekdo v tej vlogi podeli dovoljenje
ZANIKATI(VSI |< вид действия >,…}
( VKLOPLJENO (<имя таблицы или представления>} [(<имя столбца>,…)]
| VKLOP(<имя хранимой процедуры>}
| VKLOP(<имя пользовательской функции>}
ZA ( JAVNO |<имя объекта системы безопасности>,…}
– zavrnitev dostopa uporabnikov v objekte baze podatkov. CASCADE odvzame pravice ne samo od danega uporabnika, ampak tudi za vse, ki jim je podelil pravice.
Primer (na ukazna prepoved TSQL):
ZAVRNI USTVARJANJE TABELE ZA Jack CASCADE
Ekipa PREKLIC uporablja za implicitno zavrnitev dostopa do objektov baze podatkov. Sintaksa je enaka ukazu DENY. Implicitna zavrnitev je podobna zavrnitvi dostopa, le da velja le na ravni, na kateri je definirana. Primer: uporabniku Jacku, ki je član vloge GoodUsers, so dodeljene pravice dostopa do tabele XFiles. Če je REVOKE zavrnjen za vlogo GoodUsers za dostop do te tabele, lahko Jack še vedno dostopa do te tabele, ker so pravice zanj izrecno določene. Če uporabite REVOKE osebno zanj, bo izgubil pravico do dostopa do XFiles.
Dovoljenja, podeljena vlogam, podedujejo njihovi člani. Če je uporabniku odobren dostop do objekta prek članstva v eni vlogi, zavrnjen pa v drugi, se konflikt dostopa vedno razreši v korist zavrnitve.
Tehnologije za zaščito podatkov v MS SQL Server
1.Mehanizem kontrolne točke– kontrolne točke, ki se generirajo po ~60 s za pisanje posodobljenih strani na disk (kontrolno točko je mogoče vsiliti z ukazom CHECKPOINT).
2. Vgrajeni in zunanji mehanizmi za preverjanje celovitosti baze podatkov (zaženejo se samodejno ali kot pripomoček DBCC - Database Consistency Checker - ročno).
3. Fizično podvajanje (če je dovoljeno) datotek baze podatkov z uporabo operacijskega sistema (vključno z mehanizmom zrcaljenih trdih diskov).
4. Varnostno kopiranje baz podatkov in dnevnikov transakcij - s pisanjem izpisa baze podatkov na napravo za varnostno kopiranje (magnetni trak ali trdi disk).
5. Replikacija – zmožnost podvajanja informacij s periodičnim (v nekaterih primerih sinhrono) prenašanjem z enega strežnika SQL na drugega.
6. Šifriranje prometa med odjemalcem in strežnikom, kot tudi šifriranje kod, ki se uporabljajo za delo z objekti baze podatkov (shranjene procedure, sprožilci itd.)
1) Koncept kazalca
Interaktivni SQL ne razlikuje med enovrstičnimi in večvrstičnimi poizvedbami. Vgrajeni SQL izvaja te poizvedbe drugače. Enovrstične poizvedbe vrnejo eno vrstico in smo jih že obravnavali. Če je rezultat poizvedbe več kot ena vrstica, mora vdelan SQL omogočiti aplikaciji, da pridobi rezultate poizvedbe vrstico za vrstico. Za to se uporabljajo kazalci. Kazalec je spremenljivka, povezana s poizvedbo. Njegova vrednost je vsaka vrstica, ki se ujema s poizvedbo. Tako kot spremenljivke je treba tudi kazalce deklarirati, preden jih lahko uporabimo. Za razliko od pogledov so kazalci zasnovani za obdelavo vrstic za vrsticami.
2) Deklaracija kurzorja
IZJAVA [{}] [[št] POMIKANJE] KAZALEC [{Z | BREZ} PRIDRŽI] ZA [ZA {SAMO ZA BRANJE | POSODOBITEV [OF ]}]
3) Ključne besede
. OBČUTLJIV|NEOBČUTLJIV|NEOBČUTLJIV– spremembe v nizu rezultatov so vidne | prepovedano (popravljeno z uporabo kopije nabora podatkov)|DBMS se sam odloči, ali bo naredil kopijo (deluje privzeto).
. Z|BREZ ZADRŽANJA– listi odprti | zapre kazalec, če naleti na stavek COMMIT.
. POMIKANJE– [preprečuje] ekstrahiranje vrstic rezultatov v naključnem vrstnem redu.
. SAMO ZA BRANJE– definira kazalec samo za branje.
. ZA POSODOBITEV– blokira posodobitev samo določenih stolpcev.
4) Deklaracija kazalca v SQL Serverju
IZJAVA KAZALEC [LOKALNO|GLOBALNO] [SAMO NAPREJ|DRKANJE] [STATIČNO|NASTAVITEV TIPK|DINAMIČNO|HITRO_NAPREJ] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIČNO] ZA [ZA POSODOBITEV [OF ]]
. STATIČNO– Definira kazalec, ki ustvari začasno kopijo podatkov za uporabo s kazalcem. Vse poizvedbe proti kazalcu dostopajo do navedene začasne tabele v bazi podatkov tempdb, tako da spremembe osnovnih tabel ne vplivajo na podatke, ki jih vrnejo vzorci za ta kazalec, sam kazalec pa ne dovoljuje spreminjanja.
. KEYSET– Označuje, da se članstvo ali vrstni red vrstic v kazalcu ne spremeni, ko ga odprete. Nabor ključev, ki enolično identificirajo vrstice, je vgrajen v tabelo v bazi podatkov tempdb, imenovano nabor ključev.
. DINAMIČNO– Definira kazalec, ki prikaže vse spremembe podatkov, narejene v vrsticah niza rezultatov, ko si ogledujete ta kazalec. Vrednosti podatkov, vrstni red in članstvo v vrsticah v vsakem izboru se lahko razlikujejo. Dinamični kazalci ne podpirajo izbirne možnosti ABSOLUTE.
. HITRO NAPREJ– Označuje kazalec FORWARD_ONLY, READ_ONLY, za katerega je omogočena optimizacija delovanja. Možnosti FAST_FORWARD ni mogoče določiti z možnostma SCROLL ali FOR_UPDATE.
. SCROLL_LOCKS– Označuje, da so pozicionirane posodobitve ali brisanja, izvedena s kazalcem, zagotovljeno uspešne. SQL Server zaklene vrstice, ko so prebrane v kazalec, da zagotovi, da so na voljo za nadaljnje spremembe. Možnosti SCROLL_LOCKS ni mogoče določiti z možnostjo FAST_FORWARD ali STATIC.
. OPTIMISTIČNO– Označuje, da pozicionirane posodobitve ali brisanja, izvedena prek kazalca, ne bodo uspele, če je bila vrstica posodobljena od trenutka, ko je bila prebrana v kazalec. SQL Server ne zaklene vrstic, ko so prebrane v kazalec. Namesto tega se izvede primerjava vrednosti stolpca s časovnim žigom (ali kontrolnih vsot, če tabela nima stolpca s časovnim žigom), da se ugotovi, ali se je vrstica spremenila, odkar je bila prebrana v kazalec. Če je bila vrstica spremenjena, njeno pozicionirano spreminjanje ali brisanje ni mogoče. Možnosti OPTIMISTIC ni mogoče določiti z možnostjo FAST_FORWARD.
5) Odpiranje kazalca
6) Pridobivanje vrstic iz kazalca
PRINESI [{NASLEDNJA|PREJŠNJA|PRVA|ZADNJA|{ABSOLUTNO|RELATIVNO }}]
OD INTO
7) Možnosti pozicioniranja kazalca
. NASLEDNJA|PREJŠNJA|PRVA|ZADNJA– na naslednjo|prejšnjo|prvo|zadnjo vrstico nabora rezultatov.
. RELATIVNO ±N– na vrstico s pozitivnim ali negativnim odmikom glede na trenutno vrstico.
. ABSOLUTNO ±N– v vrstico z izrecno določeno absolutno številko položaja od začetka ali konca kazalca.
Opomba: SQL Server dovoljuje celoštevilsko spremenljivko @N namesto N.
8) Zapiranje kazalca
9) Opombe o kazalcih
. Če kazalec vsebuje več kot eno vrstico, je treba organizirati zanko za pridobivanje podatkov iz nje, občasno preverjanje, da doseže zadnjo vrstico.
. Za razliko od tabel in pogledov so vrstice kurzorja razvrščene izrecno z uporabo odseka NAROČI PO, ali v skladu s konvencijami, sprejetimi v določenem DBMS.
. Kazalci se uporabljajo tudi za izbiro skupin vrstic iz tabel, ki jih je mogoče posodobiti ali izbrisati eno za drugo.
. Da je kazalec mogoče posodobiti, mora izpolnjevati enaka merila kot pogled, to je, da ne sme vsebovati razdelkov UNION, ORDER BY, GROUP BY, DISTINCT.
10) Primer brisanja podatkov s kazalca
exec sql deklarira kazalec Cur1 za izbiro * iz stranke
kjer Ocena
// natisni (@f1+’ ‘+pretvori(Varchar(5),@f2))
exec sql izbriši iz stranke
kjer je tok Cur1; ) – Vzemite podatke za brisanje iz kazalca
nisem končal:
exec sql zapri kazalec Cur1; — Zaprite kazalec
izhod();
11) Primer povečanja provizij
exec sql deklarira kazalec CurCust za izbiro * iz SalesPeople
kjer je SNum (izberite SNum od stranke, kjer je Rating=300); — Določite kazalec
exec sql odpri kazalec CurCust; - Izvedite kazalec
while (sqlca.sqlcode==0) ( — Ustvarite zanko za posodobitev podatkov v tabeli
exec sql pridobi CurCust v:Id_num, :SalesPerson, :Loc, :Comm;
exec sql update SalesPeople nastavi Comm=Comm+.01 kjer je trenutno
od CurCust; ) – Vzemite podatke za posodobitev iz kazalca
exec sql zapri kazalec CurCust; — Zaprite kazalecSELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
DECLARE Cur1 SCROLL CURSOR FOR SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
OPEN Cur1
PRINESI NASLEDNJE IZ Cur1
WHILE @@FETCH_STATUS=0
ZAČETI
PRINESI NASLEDNJE IZ Cur1
KONEC
ZAPRI Cur1
DODELITEV Cur1
