Uklanjanje iz ms sql kursora. Kursori u MySQL pohranjenim procedurama. A. Korištenje jednostavnog kursora i sintakse
Koncept kursora
Upit prema relacijskoj bazi podataka obično vraća više redaka (zapisa) podataka, ali aplikacija obrađuje samo jedan po jedan zapis. Čak i ako se bavi s nekoliko redaka istovremeno (primjerice, prikazuje podatke u obliku proračunskih tablica), njihov broj je još uvijek ograničen. Osim toga, prilikom izmjene, brisanja ili dodavanja podataka, radna jedinica je niz. U ovoj situaciji dolazi do izražaja koncept kursora, au ovom kontekstu kursor je pokazivač na red.
Kursor u SQL-u je područje u memoriji baze podataka koje je dizajnirano za držanje zadnje SQL naredbe. Ako je trenutna izjava upit baze podataka, redak podataka upita koji se naziva trenutna vrijednost ili trenutna linija kursora također se pohranjuje u memoriju. Navedeno područje u memoriji ima naziv i dostupno je aplikacijskim programima.
Obično se pokazivači koriste za odabir podskupa informacija pohranjenih u bazi podataka. U bilo kojem trenutku aplikacijski program može provjeriti jednu liniju kursora. Kursori se često koriste u SQL izjavama ugrađenim u aplikacijske programe napisane u proceduralnim jezicima. Neke od njih implicitno kreira poslužitelj baze podataka, dok druge definiraju programeri.
U skladu sa SQL standardom, pri radu s kursorima mogu se razlikovati sljedeće glavne radnje:
- stvaranje ili deklaracija kursora;
- otvaranje kursora, tj. punjenje podacima koji su pohranjeni u višerazinskoj memoriji;
- izbor iz kursora i mijenjanje redova podataka s njim;
- zatvaranje kursora, nakon čega postaje nedostupan korisničkim programima;
- oslobađanje kursora, tj. brisanje kursora kao objekta jer njegovo zatvaranje ne oslobađa nužno memoriju povezanu s njim.
Definicija pokazivača može malo varirati u različitim implementacijama. Na primjer, ponekad programer mora eksplicitno osloboditi memoriju dodijeljenu kursoru. Nakon otpustite kursor njegova pridružena memorija je također oslobođena. To omogućuje ponovnu upotrebu njegovog imena. U drugim izvedbama kada zatvaranje kursora oslobađanje memorije događa se implicitno. Odmah nakon oporavka, postaje dostupan za druge operacije: otvaranje drugog kursora itd.
U nekim slučajevima korištenje pokazivača je neizbježno. Međutim, ako je moguće, to treba izbjegavati i raditi sa standardnim naredbama za obradu podataka: SELECT, UPDATE, INSERT, DELETE. Osim što kursori ne dopuštaju modifikacijske operacije na cijelom volumenu podataka, brzina izvođenja operacija obrade podataka pomoću kursora je osjetno manja od brzine standardnim sredstvima SQL.
Implementacija kursora u MS SQL Server okruženju
SQL poslužitelj podržava tri vrste kursora:
- SQL pokazivači se prvenstveno koriste unutar okidača, pohranjenih procedura i skripti;
- poslužiteljski kursori rade na poslužitelju i implementiraju sučelje za programiranje aplikacija za ODBC, OLE DB, DB_Library;
- Klijentski kursori su implementirani na samom klijentu. Oni dohvaćaju cijeli skup rezultata redaka s poslužitelja i pohranjuju ga lokalno, što ubrzava obradu podataka smanjenjem izgubljenog vremena utrošenog na mrežne operacije.
Različite vrste višekorisničkih aplikacija zahtijevaju različite vrste paralelnog pristupa podacima. Neke aplikacije zahtijevaju trenutni pristup informacijama o promjenama u bazi podataka. Ovo je tipično za sustave za rezervaciju karata. U drugim slučajevima, kao što su sustavi statističkog izvješćivanja, stabilnost podataka je važna jer ako se stalno mijenjaju, programi neće moći učinkovito prikazati informacije. Različite aplikacije trebaju različite implementacije kursora.
U SQL Serveru vrste pokazivača razlikuju se po mogućnostima koje pružaju. Tip kursora se određuje u fazi njegovog kreiranja i ne može se mijenjati. Neke vrste pokazivača mogu otkriti promjene koje su drugi korisnici napravili u redovima uključenim u skup rezultata. Međutim, SQL Server samo prati promjene u takvim redovima dok se retku pristupa i ne dopušta promjenu promjena nakon što je red već pročitan.
Kursori su podijeljeni u dvije kategorije: sekvencijalno i pomicati. Uzastopno omogućuju odabir podataka samo u jednom smjeru - od početka do kraja. Pokazivači koji se mogu pomicati pružaju veću slobodu djelovanja - moguće je kretati se u oba smjera i skočiti na proizvoljan red skupa rezultata kursora.Ako program može modificirati podatke na koje kursor pokazuje, naziva se pomični i modifikabilni. Govoreći o kursorima, ne treba zaboraviti na izolaciju transakcija. Kada jedan korisnik modificira zapis, drugi ga čita pomoću vlastitog pokazivača, štoviše, on može modificirati isti zapis, zbog čega je potrebno održati integritet podataka.
SQL Server podržava statički, dinamički, sekvencijalno i kontrolira se skupom ključeva.
U shemi sa statički kursor informacije se čitaju iz baze podataka jednom i pohranjuju kao snimka (od nekog trenutka u vremenu), tako da promjene koje je drugi korisnik napravio u bazi nisu vidljive. Neko vrijeme otvaranje kursora poslužitelj postavlja zaključavanje na sve retke uključene u njegov puni skup rezultata. Statički kursor ne mijenja se nakon izrade i uvijek prikazuje skup podataka koji je postojao u trenutku otvaranja.
Ako drugi korisnici promijene podatke uključene u kursor u izvornoj tablici, to neće utjecati na statički kursor.
U statički kursor Promjene nisu moguće pa se uvijek otvara u načinu rada samo za čitanje.
Dinamički kursor održava podatke u "živom" stanju, ali to zahtijeva mrežne i softverske resurse. Korištenje dinamički kursori ne stvara se potpuna kopija izvornih podataka, već se vrši dinamički odabir iz izvornih tablica tek kada korisnik pristupi određenim podacima. Tijekom dohvaćanja, poslužitelj zaključava retke, a sve promjene koje korisnik napravi na punom skupu rezultata kursora bit će vidljive u kursoru. Međutim, ako je drugi korisnik napravio promjene nakon što je kursor dohvatio podatke, one se neće odraziti na kursoru.
Pokazivač kontroliran nizom tipki, nalazi se u sredini između ovih krajnosti. Zapisi se identificiraju u trenutku uzorkovanja i na taj način se prate promjene. Ova vrsta pokazivača korisna je pri implementaciji pomicanja unatrag - tada dodaci i brisanja redaka nisu vidljivi dok se informacije ne ažuriraju i upravljački program ne odabere nova verzija evidencije, ako su u njima napravljene izmjene.
Sekvencijalni kursori ne smiju dohvaćati podatke u obrnutom smjeru. Korisnik može odabrati samo retke od početka do kraja pokazivača. Serijski kursor ne pohranjuje skup svih redaka. Čitaju se iz baze podataka čim se odaberu u kursoru, što omogućuje da se sve promjene koje su korisnici napravili u bazi podataka dinamički odražavaju pomoću naredbi INSERT, UPDATE, DELETE. Kursor prikazuje najnovije stanje podataka.
Statički kursori pružiti stabilan prikaz podataka. Dobri su za sustave "skladišta" informacija: aplikacije za sustave izvještavanja ili za statističke i analitičke svrhe. Osim, statički kursor bolje od drugih nosi s uzorkovanjem velike količine podataka. Suprotno tome, elektronički sustavi kupnje ili rezervacije karata zahtijevaju dinamičku percepciju ažuriranih informacija kako se unose promjene. U takvim slučajevima koristi se dinamički kursor. U tim je aplikacijama količina prenesenih podataka obično mala i pristupa im se na razini retka (pojedinačnog zapisa). Grupni pristup je vrlo rijedak.
Upravljanje kursorom u MS SQL Server okruženju
Kontrola kursora provodi se izvršavanjem sljedećih naredbi:
- PROGLASI - stvaranje odn deklaracija kursora;
- OTVOREN - otvaranje kursora, tj. punjenje podacima;
- DOHVATI izbor iz kursora i mijenjanje redova podataka korištenjem kursora;
- ZATVORITI - zatvaranje kursora;
- DODJELI – oslobađanje kursora, tj. brisanje kursora kao objekta.
Deklaracija kursora
SQL standard pruža sljedeću naredbu za stvaranje kursora:
Korištenje ključne riječi INSENSITIVE stvorit će statički kursor. Promjene podataka nisu dopuštene, osim toga, promjene koje su napravili drugi korisnici nisu prikazane. Ako ključna riječ INSENSITIVE nedostaje, a dinamički kursor.
Kada navedete ključnu riječ SCROLL, kreirani kursor se može pomicati u bilo kojem smjeru, što vam omogućuje korištenje bilo koje naredbe za odabir. Ako je ovaj argument izostavljen, kursor će biti dosljedan, tj. njegovo gledanje bit će moguće samo u jednom smjeru – od početka do kraja.
Izjava SELECT specificira tijelo zahtjeva SELECT, koji određuje rezultirajući skup redaka za kursor.
Navođenje FOR READ_ONLY stvara kursor samo za čitanje i ne dopušta nikakve izmjene podataka. Razlikuje se od statičke, iako potonja također ne dopušta promjenu podataka. Može se deklarirati kao kursor samo za čitanje dinamički kursor, što će omogućiti prikaz promjena koje je napravio drugi korisnik.
Stvaranje kursora s argumentom FOR UPDATE omogućuje vam izvršavanje u kursoru promjena podataka ili u navedenim stupcima ili, u nedostatku argumenta OF column_name, u svim stupcima.
U okruženju MS SQL Servera prihvaća se sljedeća sintaksa za naredbu za kreiranje kursora:
<создание_курсора>::= DEKLARIRAJ cursor_name CURSOR FOR SELECT_statement ]]
Korištenje ključne riječi LOCAL stvorit će lokalni kursor koji je vidljiv samo unutar opsega paketa, okidača, pohranjene procedure ili prilagođena funkcija. Kada se paket, okidač, procedura ili funkcija prekine, kursor se implicitno uništava. Da biste proslijedili sadržaj kursora izvan konstrukcije koja ga je stvorila, morate dodijeliti OUTPUT argument njegovom parametru.
Ako je navedena ključna riječ GLOBAL, stvara se globalni kursor; postoji dok se trenutna veza ne zatvori.
Navođenje FORWARD_ONLY stvara serijski kursor; Podaci se mogu uzorkovati samo u smjeru od prvog reda do posljednjeg.
Navođenje SCROLL stvara kursor koji se može pomicati; Podacima se može pristupiti bilo kojim redoslijedom i u bilo kojem smjeru.
Navođenje STATIC stvara statički kursor.
Određivanjem KEYSET stvara se pokazivač tipke.
Određivanje DYNAMIC stvara dinamički kursor.
Ako navedete argument FAST_FORWARD za kursor READ_ONLY, kreirani kursor bit će optimiziran za brz pristup na podatke. Ovaj se argument ne može koristiti u kombinaciji s argumentima FORWARD_ONLY ili OPTIMISTIC.
Kursor kreiran s argumentom OPTIMISTIC sprječava modificiranje ili brisanje redaka koji su modificirani nakon otvaranje kursora.
Određivanjem argumenta TYPE_WARNING, poslužitelj će obavijestiti korisnika o implicitnoj promjeni tipa kursora ako je nekompatibilan s SELECT upitom.
Otvaranje kursora
Za otvaranje kursora i ispunjavajući ga podacima iz upita SELECT navedenog prilikom kreiranja kursora, upotrijebite sljedeću naredbu:
Nakon otvaranje kursora Izvršava se pridružena naredba SELECT, čiji se izlaz pohranjuje u višerazinsku memoriju.
Dohvaćanje podataka iz kursora
Odmah nakon otvaranje kursora možete odabrati njegov sadržaj (rezultat izvršavanja odgovarajućeg upita) pomoću sljedeće naredbe:
Navođenje FIRST vratit će prvi red kompletnog skupa rezultata kursora, koji postaje trenutni red.
Navođenje LAST vraća zadnji red kursora. Također postaje trenutna linija.
Navođenje NEXT vraća red odmah nakon trenutnog u punom skupu rezultata. Sada to postaje aktualno. Prema zadanim postavkama, naredba FETCH koristi ovu metodu za dohvaćanje redaka.
Ključna riječ PRIOR vraća redak prije trenutnog. Postaje aktualno.
Argument APSOLUTNO (broj_linije | @varijabla_broj_linije) vraća red prema njegovom apsolutnom rednom broju u kompletnom skupu rezultata kursora. Broj retka može se odrediti pomoću konstante ili kao naziv varijable u kojoj je pohranjen broj retka. Varijabla mora biti cjelobrojnog tipa podataka. Prikazane su i pozitivne i negativne vrijednosti. Prilikom navođenja pozitivne vrijednosti niz se broji od početka skupa, dok se negativna vrijednost broji od kraja. Odabrani redak postaje trenutni redak. Ako je navedena nulta vrijednost, ne vraća se nijedan redak.
Argument RELATIVNO (broj redaka | @varijabla broj redaka) vraća redak koji je navedeni broj redaka nakon trenutnog. Ako navedete negativan broj redaka, vratit će se redak koji je navedeni broj redaka prije trenutnog. Određivanje null vrijednosti vratit će trenutni redak. Vraćeni redak postaje trenutni redak.
Do otvoriti globalni kursor, prije naziva morate navesti ključnu riječ GLOBAL. Naziv kursora također se može navesti pomoću varijable.
U dizajnu INTO @variable_name [,...n] naveden je popis varijabli u koje će se pohraniti odgovarajuće vrijednosti stupca vraćenog retka. Redoslijed navođenja varijabli mora odgovarati redoslijedu stupaca u kursoru, a tip podataka varijable mora odgovarati tipu podataka u stupcu kursora. Ako konstrukcija INTO nije navedena, tada će ponašanje naredbe FETCH biti slično ponašanju naredbe SELECT - podaci se prikazuju na ekranu.
Promjena i brisanje podataka
Da biste unijeli promjene pomoću kursora, morate izdati naredbu UPDATE u sljedećem formatu:
Nekoliko stupaca trenutnog reda kursora može se promijeniti u jednoj operaciji, ali svi moraju pripadati istoj tablici.
Za brisanje podataka pomoću pokazivača upotrijebite naredbu DELETE u sljedećem formatu:
Kao rezultat toga, trenutni skup linija u pokazivaču bit će izbrisan.
Zatvaranje kursora
Nakon zatvaranja kursor postaje nedostupan korisnicima programa. Kada je zatvoren, uklanjaju se sve brave instalirane tijekom njegovog rada. Zatvaranje se može primijeniti samo na otvorene kursore. Zatvoreno ali ne oslobođeni kursor može se ponovno otvoriti. Nije dopušteno zatvoriti neotvoreni kursor.
Otpustite kursor
Zatvaranje kursora ne oslobađa nužno memoriju povezanu s njim. Neke implementacije ga moraju eksplicitno poništiti korištenjem izjave DEALLOCATE. Nakon otpustite kursor Memorija se također oslobađa, što omogućuje ponovnu upotrebu naziva kursora.
Za kontrolu je li dosegnut kraj pokazivača, preporučuje se korištenje funkcije: @@FETCH_STATUS
Funkcija @@FETCH_STATUS vraća:
0 ako je dohvaćanje bilo uspješno;
1 ako dohvaćanje nije uspjelo zbog pokušaja dohvaćanja retka izvan kursora;
2 ako dohvaćanje nije uspjelo zbog pokušaja pristupa izbrisanom ili izmijenjenom retku.
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 ISPIS "Shopping list" DECLARE klient_cursor LOCAL FOR SELECT Šifra klijenta, tvrtka, prezime FROM Client WHERE Grad="Moskva" ORDER BY Tvrtka, prezime OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 BEGIN SELECT @message="Klijent "+@fam+ "Company "+ @firm PRINT @message SELECT @message="Product name Purchase.Cost" 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. Product Code=Transaction.Product Code WHERE Transaction.Customer Code=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "Nema kupnje" 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="Ukupni trošak "+ CAST(@s AS CHAR(6)) PRINT @message -- premjestiti na sljedećeg klijenta-- FETCH NEXT FROM klient_cursor U @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor Primjer 13.6. Kursor za prikaz popisa robe koju su kupili klijenti iz Moskve i njihove ukupne cijene.
Primjer 13.7. Razvijte kursor koji se može pomicati za klijente iz Moskve. Ako telefonski broj počinje s 1, izbrišite klijenta s tim brojem i u prvom unosu kursora zamijenite prvu znamenku telefonskog broja s 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "List of clients" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Firma, Prezime, Telefon FROM Client WHERE City ="Moskva" ORDER BY Tvrtka, Prezime ZA AŽURIRANJE OTVORI klient_cursor FETCH NEXT FROM klient_cursor INTO @firma, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Klijent "+@fam+ " Tvrtka "+ @firma " Telefon "+ @tel ISPIŠI @poruku -- ako telefonski broj počinje s 1, -- izbriši klijenta s tim brojem IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- prijeđi na sljedeći klijent FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- u prvom unosu zamijenite prvu znamenku u telefonskom broju s 4 AŽURIRAJ Client SET Phone ='4' + RIGHT(@ tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Klijent "+@fam+" Firm "+ @firm "Phone "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Primjer 13.7. Kursor koji se može pomicati za klijente iz Moskve.
Primjer 13.8. Korištenje kursor kao izlazni parametar procedure. Procedura vraća skup podataka – popis proizvoda.
Pozivanje procedure i ispis podataka iz izlaznog kursora provodi se na sljedeći 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 SELECT @n KRAJ ZATVORI @my_cur DEADOCATE @my_cur
Kursor je poveznica na kontekstualno područje memorije. U nekim implementacijama SQL programskog jezika (Oracle, Microsoft SQL Server) - skup rezultata dobiven prilikom izvršavanja upita i pridruženi pokazivač trenutnog zapisa. Rekao bih da kursor jest virtualni stol koji je alternativna pohrana podataka. U ovom slučaju kursor vam omogućuje pristup njegovim podacima kao da se radi o podacima običnog niza.
Kursori se koriste u pohranjenim procedurama. Dosta teorije, pogledajmo primjer:
Imamo bazu podataka (baza nije dobra, ovo je jedna od mojih laboratorijski rad, ali naš učitelj baze podataka inzistirao je na takvoj strukturi)
/*bankovni podaci*/
KREIRAJ TABLICU `banka` (
`Naziv banke` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
PRIMARNI KLJUČ (`BankId`))MOTOR=InnoDB
SKUP ZNAKOVA "utf8" COLLATE "utf8_bin" ;
/*podaci o depozitima */
STVARAJ TABLICU `bankovna distribucija` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`Iznos doprinosa` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARNI KLJUČ(`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `Id klijenta` (`Id klijenta`),
OGRANIČENJE `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCE `bank` (`BankId`),
OGRANIČENJE `bankdistribution_fk1` STRANI KLJUČ (`Id klijenta`) REFERENCE `klijent` (`Id klijenta`)
)MOTOR=InnoDB
/*podaci o investitorima*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL,
`Prezime` 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 "" ,
`Adresa` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARNI KLJUČ(`Id klijenta`, `Id kreditne kartice`),
KEY `Id klijenta` (`Id klijenta`))MOTOR=InnoDB
AUTO_INCREMENT=11 SKUP ZNAKOVA "utf8" KOLLATE "utf8_bin"
Recimo da trebamo redom primiti svaku banku i izvršiti neke radnje s njom, sljedeći upit bi nam mogao pomoći u tome
Odaberite `banka`.* IZ `banke` OGRANIČENI BROJ_ZAPISA_KOJI NAM JE POTREBAN,1. Stoga, koristeći LIMIT WE NEED_RECORD NUMBER, 1, izvlačimo svaki zapis u ciklusu iz bankovne tablice redom i s njim izvodimo radnje koje su nam potrebne, povećavajući vrijednost WE NEED_RECORD NUMBER za 1. Sada ćemo učiniti isto, ali pomoću pokazivača
Početi
/* varijable gdje izvlačimo podatke */
Deklarirajte vBankId cijeli broj;
Deklarirajte vBankName VARCHAR(50);
Deklarirajte vAddress VARCHAR(50);
Deklarirajte vPhone VARCHAR (50);
/* hadler varijabla - a*/
Proglasi gotovo cijeli broj zadanom 0;
/*Deklaracija kursora*/
Deklarirajte BankCursor Kursor za Odaberite `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` gdje je 1;
/*Svrha HANDLER-a, koja će biti objašnjena u nastavku*/
DEKLARACIJA OBRAĐIVAČA NASTAVKA ZA SQLSTATE "02000" SET done=1;
/* otvori kursor */
Otvorite BankCursor;
/*dohvati podatke*/
DOK je gotovo = 0 UČINITI
poduzimamo radnje koje su nam potrebne
KRAJ DOK ;
/*zatvaranje kursora*/
Zatvori BankCursor;
KRAJ ;* Ovaj izvorni kod je istaknut Markerom izvornog koda.
Pogreška: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Poruka: Nema podataka - nula redaka je dohvaćeno, odabrano ili obrađeno
SQLSTATE: 02000 aktivira se kada se dosegne kraj pokazivača ili kada odabir ili ažuriranje vrati prazan niz.
Sljedeći red smo deklarirali kursor DECLARE cursor_name CURSOR FOR select_statement;
Otvori kursor Otvori cursor_name;
Zatim, dok ne dođemo do kraja kursora (WHILE done = 0 DO), izvlačimo podatke i obrađujemo ih.
Morate zatvoriti pokazivač prije izlaska iz pohranjene procedure. Zatvori cursor_name;
Ne čini se komplicirano. Ali postoje mnoge zamke povezane sa SQLSTATE "02000".
DOK je gotovo = 0 UČINITI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
Odaberite (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution gdje je BankId = vBankId limit 1;
radimo neke radnje
KRAJ DOK ;* Ovaj izvorni kod je istaknut Markerom izvornog koda.
Sve je u redu i ispravno sa gledišta sintakse. Ali s logične točke gledišta, ne. Može se dogoditi da deponenti nemaju otvorene račune u nekoj banci, tada za Select (ContributeAmount) INTO vContributeAmountSUM FROM bank distribuciju gdje BankId = vBankId limit 1; SQLSTATE: 02000 će se pokrenuti, varijabla done bit će postavljena na 1 i while petlja završiti ranije nego što smo očekivali. Ovo se može izbjeći na sljedeći način
DOK je gotovo = 0 UČINITI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* izdvojiti za banku iznos bilo kojeg od njezinih depozita */
if (vContributeAmountSUM > 0) then
/* izdvojiti za banku iznos bilo kojeg od njezinih depozita */
završi ako ;
radimo neke radnje
KRAJ DOK ;* Ovaj izvorni kod je istaknut Markerom izvornog koda.
Kod prvog zahtjeva provjerili smo ima li doprinosa (ako ih nema onda je vContributeAmountSUM == 0) i tek ako ih ima dohvaćamo podatke.
Recimo sada da trebamo ukloniti ukupan iznos na računima u različitim bankama za svakog klijenta
Deklarirajte ClientSummCursor kursor za Odaberite zbroj
Deklarirajte ClientSummCursor Kursor za odabir sume (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` IZ `bankdistribution` Unutarnje spajanje klijenta na (client.ClientId = bankdistribution.`ClientId`) gdje je 1 grupa prema `bankdistribution`. `Id klijenta`;Otvori ClientSummCursor;
DOK je gotovo = 0 UČINITI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* izdvojiti za banku iznos bilo kojeg od njezinih depozita */
Odaberite Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution gdje je BankId = vBankId limit 1;
/* provjeri ima li stvarno depozita u ovoj banci */
if (vContributeAmountSUM > 0) then
/* izdvojiti za banku iznos bilo kojeg od njezinih depozita */
Odaberite ContributeAmount INTO vContributeAmountSUM FROM bankdistribution gdje je BankId = vBankId limit 1;
završi ako ;
radimo neke radnje.
KRAJ DOK ;* Ovaj izvorni kod je istaknut Markerom izvornog koda.
Ista situacija može nastati kada podaci u kursoru ClientSummCursor završe ranije od podataka u BankCursoru, aktivira se SQLSTATE: 02000, varijabla done je postavljena na 1, a petlja while završi ranije nego što smo očekivali. Ovo se može izbjeći na sljedeći način
Otvori ClientSummCursor;
DOK je gotovo = 0 UČINITI
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* izdvojiti za banku iznos bilo kojeg od njezinih depozita */
Odaberite Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution gdje je BankId = vBankId limit 1;
/* provjeri ima li stvarno depozita u ovoj banci */
if (vContributeAmountSUM > 0) then
/* izdvojiti za banku iznos bilo kojeg od njezinih depozita */
Odaberite ContributeAmount INTO vContributeAmountSUM FROM bankdistribution gdje je BankId = vBankId limit 1;
završi ako ;
/* prije izdvajanja podataka iz drugog kursora, zapamtite stanje sqlstate */
SET old_status = gotovo;
/* izdvajamo podatke koji su nam potrebni */
FETCH ClientSummCursor INTO vSum,vClientId;
/* provjeri da li su podaci dohvaćeni i da li sqlstate 0200 nije uspio */
if (učinjeno = 0) then
radimo neke radnje.
završi ako ;
/* prije kraja while-a vrati vrijednost varijable done */
postavi gotovo = stari_status;
KRAJ DOK ;* Ovaj izvorni kod je istaknut Markerom izvornog koda.
Hvala svima koji su dovde pročitali, nadam se da će nekome biti od koristi.
ODNOSI SE NA: SQL Server (od 2008.) Base SQL podaci Azure SQL skladište podataka Paralelno skladište podataka
Definira atribute pokazivača poslužitelja Transact-SQL, kao što su svojstva prikaza i upit koji se koristi za izgradnju skupa rezultata na kojem kursor radi. Izjava DECLARE CURSOR podržava i ISO standardnu sintaksu i sintaksu koja koristi Transact-SQL skup jezičnih proširenja.
ISO sintaksa DECLARE cursor_name [ INOSJETLJIV ] [ SCROLL ] KURSOR ZA select_statement [ FOR ( READ ONLY | UPDATE [ OF column_name [ ,...n ] ] ) ] [;] Transact-SQL proširena sintaksa DECLARE cursor_name CURSOR [ LOCAL | GLOBALNO ] [ SAMO NAPRIJED | POMICI ] [ STATIČNO | SET KLJUČEVA | DINAMIČNO | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIČNO ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
naziv_kursora
naziv_kursora
NEOSJETLJIV
tempdb; stoga se promjene temeljnih tablica ne odražavaju na podatke koje vraćaju odabiri ovog kursora, a ovaj kursor nije promjenjiv. Kada koristite ISO sintaksu, osim ako je navedena opcija INSENSITIVE, odobrena ažuriranja i brisanja napravljena na osnovnim tablicama pojavljuju se u sljedećim odabirima.
SVITAK
Označava da su dostupne sve opcije uzorkovanja (PRVI, POSLJEDNJI, PRETHODNI, SLJEDEĆI, RELATIVNI, APSOLUTNI). Ako izjava ISO DECLARE CURSOR ne navodi opciju SCROLL, podržana je samo NEXT opcija dohvaćanja. Opcija SCROLL ne može se specificirati s opcijom FAST_FORWARD.
select_statement
Standard SELECT izjava, koji definira skup rezultata pokazivača. Ključne riječi FOR BROWSE i INTO nisu dopušteni select_statement deklaracija kursora.
select_statement sukob s kursorom tražene vrste.
SAMO ZA ČITANJE
Ažuriraj ]
naziv_stupca [,
.. .n] naveden, samo navedeni stupci dopuštaju promjene. Ako se izjava UPDATE koristi bez popisa stupaca, ažuriranje je moguće za sve stupce.
naziv_kursora
Transact-SQL naziv određenog kursora poslužitelja. naziv_kursora moraju poštovati pravila za identifikatore.
LOKALNO
Označava da je kursor lokalan za paket, pohranjenu proceduru ili okidač u kojem je kreiran. Ime pokazivača vrijedi samo unutar ovog područja. Kursor se može referencirati lokalnim varijablama paketa, pohranjenim procedurama, okidačima ili izlaznim parametrom pohranjene procedure. Parametar OUTPUT koristi se za prosljeđivanje lokalnog kursora pozivajućem paketu, pohranjenoj proceduri ili okidaču, koji zatim može dodijeliti parametar varijabli kursora za kasniji pristup kursoru nakon završetka pohranjene procedure. Kursor se implicitno otpušta kada serija, pohranjena procedura ili okidač dovrši izvršenje, osim ako je kursor proslijeđen parametru OUTPUT. Ako je kursor proslijeđen parametru OUTPUT, kursor se oslobađa kada se oslobode sve varijable koje se odnose na njega ili kada se izađe iz opsega.
GLOBALNO
Označava da je kursor globalan za vezu. Ime kursora može koristiti bilo koja pohranjena procedura ili paket koji se izvodi na vezi. Kursor se implicitno oslobađa samo ako je veza prekinuta.
SAMO NAPRIJED
Određuje da se pokazivač može vidjeti samo od prvog do posljednjeg retka. Podržana je samo opcija dohvaćanja FETCH NEXT. Ako je FORWARD_ONLY navedeno bez ključnih riječi STATIC, KEYSET ili DYNAMIC, kursor se ponaša kao DYNAMIC kursor. Ako niti argument FORWARD_ONLY niti argument SCROLL nisu navedeni, zadana vrijednost je argument FORWARD_ONLY osim ako nisu prisutne ključne riječi STATIC, KEYSET ili DYNAMIC. Kursori STATIC, KEYSET i DYNAMIC imaju zadanu vrijednost SCROLL. Za razliku od API-ja baze podataka kao što su ODBC i ADO, FORWARD_ONLY način rada podržavaju sljedeći Transact-SQL kursori: STATIC, KEYSET i DYNAMIC.
STATIČKI
Definira kursor koji stvara privremenu kopiju podataka za korištenje od strane kursora. Svi upiti pokazivaču pristupaju navedenoj privremenoj tablici tempdb; stoga se promjene temeljnih tablica ne odražavaju na podatke koje vraćaju odabiri ovog kursora, a ovaj kursor nije promjenjiv.
KEYSET
Označava da je članstvo ili redoslijed redaka u pokazivaču nepromijenjen kada se otvori. Skup ključeva koji jedinstveno identificiraju retke ugrađen je u tablicu tempdb nazvao ključevi.
Promjene ne-ključnih vrijednosti u osnovnim tablicama koje je izvršio vlasnik pokazivača ili izvršio drugi korisnik prikazuju se kada vlasnik pokazivača pogleda. Promjene koje su napravili drugi korisnici se ne odražavaju (promjene se ne mogu izvršiti pomoću kursora Transact-SQL poslužitelja). Ako je redak izbrisan, pokušaj dohvaćanja redaka vraća @@FETCH_STATUS -2. Ažuriranja ključnih vrijednosti zbog granica kursora slična su brisanju starog retka i njegovom umetanju nova linija. Red s novim vrijednostima nije vidljiv i pokušaji dohvaćanja reda sa starim vrijednostima vraćaju @@FETCH_STATUS -2. Ažuriranja su vidljiva odmah ako se izvrše kroz pokazivač pomoću klauzule WHERE CURRENT OF.
DINAMIČAN
Definira kursor koji prikazuje sve promjene podataka napravljene na redovima u skupu rezultata tijekom pregleda ovog kursora. Vrijednosti podataka, redoslijed i članstvo u retku u svakom odabiru mogu se razlikovati. Opciju odabira APSOLUTA ne podržavaju dinamički kursori.
BRZO_NAPRIJED
Označava FORWARD_ONLY, READ_ONLY kursor koji ima omogućenu optimizaciju performansi. Opcija FAST_FORWARD ne može se specificirati s opcijama SCROLL ili FOR_UPDATE.
SAMO ZA ČITANJE
Sprječava promjene napravljene putem ovog kursora. WHERE CURRENT OF klauzula ne može referencirati kursor u UPDATE ili DELETE naredbi. Ova opcija ima prednost nad zadanom značajkom osvježavanja kursora.
SCROLL_LOCKS
Označava da su pozicionirana ažuriranja ili brisanja napravljena pomoću pokazivača zajamčeno uspješna. SQL Server zaključava retke dok se čitaju u kursor kako bi osigurao da su ti redovi dostupni za naknadne promjene. Opcija SCROLL_LOCKS ne može se specificirati s opcijom FAST_FORWARD ili STATIC.
OPTIMISTIČAN
Određuje da pozicionirana ažuriranja ili brisanja napravljena pomoću kursora neće uspjeti ako je red ažuriran otkad je učitan u kursor. SQL Server ne zaključava retke dok se čitaju u kursor. Umjesto toga koriste se usporedbe vremenska oznaka vrijednosti stupaca ili kontrolni zbrojevi ako ih tablica nema vremenska oznaka stupac kako biste utvrdili je li se red promijenio otkako je učitan u kursor. Ako je redak izmijenjen, tada pokušaji pozicioniranog ažuriranja ili brisanja neće uspjeti. Opcija OPTIMISTIC ne može se specificirati s opcijom FAST_FORWARD.
TYPE_WARNING
Određuje da će se upozorenje poslati klijentu ako se kursor implicitno pretvori iz jednog traženog tipa u drugi.
select_statement
Standardna izjava SELECT koja navodi skup rezultata pokazivača. Ključne riječi COMPUTE, COMPUTE BY, FOR BROWSE i INTO nisu dopuštene u select_statement deklaracija kursora.
SQL Server implicitno pretvara kursor u drugu vrstu ako klauzule u select_statement sukob s kursorom tražene vrste. Za više informacija pogledajte Implicitne konverzije kursora.
ZA AŽURIRANJE]
Definira stupce u kursoru koji se ažuriraju. Ako je OF naziv_stupca [,
... n], samo navedeni stupci dopuštaju promjene. Ako se izjava UPDATE koristi bez popisa stupaca, tada je ažuriranje moguće za sve stupce osim ako je navedena opcija istovremenosti READ_ONLY.
Izjava DECLARE CURSOR definira atribute kursora poslužitelja Transact-SQL, kao što su svojstva prikaza i upit koji se koristi za izgradnju skupa rezultata na kojem kursor radi. Naredba OPEN popunjava skup rezultata, a naredba FETCH vraća red iz njega. Izjava CLOSE briše trenutni skup rezultata pridružen kursoru. Naredba DEALLOCATE oslobađa resurse koje koristi kursor.
Prvi oblik naredbe DECLARE CURSOR koristi ISO sintaksu za određivanje parametara kursora. Drugi oblik izjave DECLARE CURSOR koristi proširenja jezika Transact-SQL koja vam omogućuju definiranje pokazivača koristeći iste tipove koji se koriste u funkcijama pokazivača API-ja baze podataka kao što su ODBC i ADO.
Ova dva oblika se ne mogu miješati. Ako navedete SCROLL ili izostavljanje ključnih riječi prije ključne riječi CURSOR, ne možete koristiti ključne riječi između CURSOR-a i također za select_statement ključne riječi. Prilikom navođenja ključnih riječi između KURSORA, kao i za select_statement ključne riječi, ne možete navesti SCROLL ili INSENSITIVE prije ključne riječi CURSOR.
Ako koristite Transact-SQL sintaksu za izjavu DECLARE CURSOR i ne navedete opcije READ_ONLY, OPTIMISTIC ili SCROLL_LOCKS, pretpostavlja se sljedeća zadana vrijednost.
Ako naredba SELECT ne podržava ažuriranja (ili nema dovoljna dopuštenja, ili pristupa udaljenim tablicama koje ne podržavaju ažuriranja, itd.), kursor je postavljen na READ_ONLY.
Kursori STATIC i FAST_FORWARD zadani su na READ_ONLY.
Kursori DYNAMIC i KEYSET zadani su na OPTIMISTIC.
Kursori se mogu pozivati samo na druge Transact-SQL izjave. API funkcije baze podataka ne mogu referencirati pokazivače. Na primjer, nakon što je kursor deklariran, OLE DB, ODBC ili ADO funkcije i metode ne mogu se odnositi na njegovo ime. Redovi kursora ne mogu se odabrati korištenjem odgovarajućih API funkcija i metoda; U tu svrhu morate koristiti Transact-SQL FETCH izjave.
Sljedeće pohranjene procedure mogu se koristiti za definiranje svojstava kursora nakon što je deklariran.
Varijable se mogu koristiti kao dio select_statement, u kojem je kursor deklariran. Vrijednosti kursorskih varijabli ne mijenjaju se nakon deklaracije.
Prema zadanim postavkama, dozvole DECLARE CURSOR dodijeljene su svim korisnicima koji imaju dozvolu SELECT za poglede, tablice i stupce koje koristi kursor.
Ne možete koristiti pokazivače ili okidače na tablici s klasteriranim indeksom columnstore. Ovo ograničenje se ne odnosi na neklasterirane indekse; Možete koristiti pokazivače i okidače na tablici s neklasteriranim indeksom columnstore.
A. Korištenje jednostavnog kursora i sintakse
Skup rezultata stvoren kada otvorite ovaj kursor uključuje sve retke i stupce tablice. Ovaj kursor se može ažurirati, sva ažuriranja i brisanja predstavljena su u odabiru za ovaj kursor. FETCH``NEXT je dohvaćanje samo zato što parametar SCROLL nije naveden.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. Korištenje ugniježđenih kursora za prikaz izvješća
Sljedeći primjer koristi ugniježđene kursore za prikaz složenog izvješća. Za svakog pružatelja deklarira se interni kursor.
POSTAVITE NO COUNT ON ;
DECLARE @vendor_id int, @vendor_name nvarchar (50), @message varchar (80), @product nvarchar (50); ISPIS" -------- Izvješće o proizvodima dobavljača --------";
DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OTVORI vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 POČNI ISPIS " " SELECT @message = "----- Proizvodi od dobavljača: "+ @dobavljač_name ISPIS @poruke -- Deklarirajte temeljen unutarnji kursor
-- na vendor_id s vanjskog kursora. 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 -- Varijabilna vrijednost iz vanjskog kursora OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 ISPIS "<
Može se dogoditi da će odgovor na jednostavan zahtjev klijenta biti uzorak od stotina tisuća redaka, što je za većinu klijenata neprobavljivo. U ovom slučaju, rješenje problema interakcije s klijentima je korištenje kursora kao univerzalnog mehanizma za razmjenu podataka između poslužitelja i klijenta. Kursori rade s rezultatom skupa podataka (rezultat upita), dajući korisnicima dodatne mogućnosti obrade podataka:
Kursori vam omogućuju rad s recima tablice navođenjem njihovog serijskog broja u skupu podataka;
Kursori vam omogućuju implementaciju složenih operacija izmjene podataka, na primjer, kada promjena vrijednosti stupca zahtijeva opetovano pristupanje vrijednostima drugih stupaca.
Životni ciklus kursora:
Stvaranje kursora: PROGLASITI<имя курсора>[ NEOSJETLJIV ] [ POMIKANJE ] KURSOR ZA< SELECT -оператор>ZA (SAMO ČITANJE | AŽURIRANJE)
Ovdje ključna riječ INSENSITIVE znači da će kursor biti statičan (snimka podataka), dok se prema zadanim postavkama kursor stvara dinamički (dohvaćanje se provodi svaki put kada se pristupi retku). Ključna riječ SCROLL znači da se kursor može pomicati u bilo kojem smjeru, inače se kursor kreira kao "sekvencijalni" kursor.
Otvaranje kursora: OTVORENO [GLOBALNO]<имя курсора>. Kursor naveden kao GLOBAL ne briše se automatski kada završi procedura ili paket iz kojeg je pozvan.
Čitanjepodaci : DOHVATI [[ DALJE | PRIJE | PRVI | POSLJEDNJI | APSOLUTNO n | RELATIVNO n ] OD ] [ GLOBALNO ]<имя курсора>[INTO@ime_varijable,...]. SQL Server 2000 omogućuje čitanje samo jednog retka s pokazivača. Ključna riječ FIRST vraća prvi red kursora; LAST – zadnji red kursora; NEXT – sljedeći red nakon trenutnog, vraćeni red postaje trenutni; PRIOR – prethodni prije tekućeg; ABSOLUTE n – vraća red prema njegovom apsolutnom rednom broju u kursoru; RELATIVNO – n redaka nakon trenutnog. Podaci stupca bit će pohranjeni u svakoj od navedenih varijabli redoslijedom kojim su navedeni.
Promjena podataka: izvršava naredbu UPDATE sa sintaksom dizajniranom za rad s kursorima.
Brisanje podataka: izvršava naredbu DELETE sa sintaksom dizajniranom za rad s kursorima.
Zatvaranje kursora: ZATVORI [GLOBALNO]<имя курсора>
Otpustite kursor: POZDRAVI [GLOBALNO]<имя курсора>
Primjer korištenja kursora:
DEKLIRAJTE fo_curs STATIČKI KURSOR ZA
SELECT name_rus iz fo ORDER BY name_rus
DEKLIRAJ @name varchar(50)
FETCH FIRST FROM fo_curs INTO @name
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM fo_curs U @name
DEADOCIJA fo_curs
2.7. Osiguravanje sigurnosti i integriteta podataka u Microsoft SQL Serveru. Upravljanje bazom podataka. Uloge. Dodjeljivanje prava korisnicima (DOPUNI, ODBIJAJ, PONIŠTI). Metode i tehnologije za zaštitu podataka u SQL Serveru.
Sigurnost i administracija SQL Servera. .
Glavna zadaća DBMS-a je osigurati cjelovitost i konzistentnost podataka unutar odabranog predmetnog područja. Jedan od čimbenika koji sprječava sustav u rješavanju ovog problema su radnje korisnika koji slučajno ili namjerno pokušavaju uništiti strukturu podataka ili promijeniti same podatke. Sukladno tome, DBMS mora biti zaštićen ne samo od fizičkih kvarova, već i od korisnika koji su neadekvatni za zadatke koji se provode. Za to je potrebno projektirati i povezati sigurnosni sustav s bazom podataka koji će onemogućavati korisnicima radnje izvan njihovih ovlasti.
Upravljanje bazom podataka
Za izradu baze podataka pomoću TSQL-a koristite naredbu CREATE DATABASE, no obično se za tu svrhu koriste mogućnosti SQL Server Management Studio-a. U SQL poslužitelj Definirano je dosta operacija baze podataka: povećanje (smanjenje) veličine datoteke, promjena konfiguracije (naredba ALTER), pripajanje i odvajanje, prijenos vlasništva, promjena naziva, pregled svojstava i, na kraju, brisanje (DROP DATABASE).
Kao i većina poslužitelja baze podataka, SQL Server ima korisnika s punim administrativnim pravima - to je Administrator sustava ili "sa". Nakon početne instalacije poslužitelja, sa lozinka je prazna. Korisnik koji kreira novu bazu podataka automatski postaje njen vlasnik ('dbo' - Data Base Owner). U trenutku kreiranja baze podataka definiran je i korisnik "gost". Ako korisnički račun nije eksplicitno mapiran na korisnika određenoj bazi podataka, korisniku je dodijeljen implicitni pristup uz korištenje naziva guest guest.Guest je obično zabranjen.
Korisnik koji kreira objekt u bazi automatski postaje njegov vlasnik i nitko, uključujući dbo i sa, ne može koristiti taj objekt dok mu vlasnik ne dodijeli prava na njega. Ali da bi korisnik mogao kreirati objekt, vlasnik baze podataka mora mu najprije dodijeliti odgovarajuća prava.
Uloga omogućuje vam kombiniranje korisnika koji obavljaju iste funkcije kako biste pojednostavili administraciju. Uloge mogu biti ugrađene ili prilagođene. Ugrađene uloge su implementirane na razini poslužitelja i na razini baze podataka. Ispod je tablica ugrađenih uloga baze podataka:
db_vlasnik. Ima sva prava u bazi podataka
Db_accessadmin. Može dodavati ili uklanjati korisnike
Db_securityadmin. Upravlja svim dopuštenjima, objektima, ulogama i korisnicima
Db_ddladmin. Može izvršiti sve DDL naredbe osim GRANT, DENY, REVOKE
Db_backupoperator. Arhivator može izvršavati naredbe. podaci
db_čitač podataka. Možda gledanje. bilo koji podatak u bilo kojoj tablici
db_pisač podataka. Možda modifikacija. bilo koji podatak u bilo kojoj tablici
Db_denydatareader. Zabranjeno pogled ljubav podaci u bilo kojem stolovi
Db_denydatawriter. Zabranite izmjenu podataka u bilo kojoj tablici
Dodjela prava korisnicima. Temelj sigurnosti SQL Servera je (1) Računi(računi); (2) korisnici; (3) uloge; (4) skupine.
Kada se korisnik spoji na SQL Server, radnje koje može izvesti određene su pravima koja su mu dodijeljena kao korisniku i članu uloge. Prava dodjeljuje administrator DBMS-a, vlasnik baze podataka ili vlasnik određenog objekta baze podataka. Prava u bazi podataka mogu se podijeliti u tri kategorije: (1) prava pristupa objektima baze podataka; (2) prava na izvršavanje TSQL naredbi; (3) implicitna prava. Poslužitelj vam omogućuje prijenos vlasništva s jednog korisnika na drugog.
Sljedeće naredbe koriste se za upravljanje korisničkim dopuštenjima za pristup objektima baze podataka:
GRANT(SVE |< вид действия >,…}
( UKLJUČENO (<имя таблицы или представления>} [(<имя столбца>,…)]
| UKLJUČENO (< имя хранимой процедуры >}
| UKLJUČENO (< имя пользовательской функции >}
ZA ( JAVNO |<имя объекта системы безопасности>,…}
[ AS<имя группы> | <имя роли>]
– dodjeljivanje prava korisnicima, Gdje
SVE – korisniku su dodijeljena sva moguća dopuštenja, drugačije odredite
<вид действия>– prava na radnje dostupne korisniku, i to:
SELECT – za pogled, za stupac tablice i za tablicu (pogled)
INSERT – dodati, za tablicu (prikaz) u cjelini
AŽURIRANJE – za promjenu, za stupac tablice i za tablicu (pregled)
DELETE – za brisanje, za tablicu (prikaz) u cjelini
IZVRŠI – za izvršavanje pohranjenih procedura
REFERENCE – mogućnost referiranja na određeni objekt (uključiti ga kao dio stranog ključa).
<имя объекта системы безопасности>– SQL Server računi, korisnici Windows domene; JAVNO – za sve korisnike.
S OPCIJOM DODJELE - omogućuje korisniku kojemu su trenutno dodijeljena prava dodjeljivanje prava pristupa objektu drugim korisnicima.
KAO<имя группы> | <имя роли>– sudjelovanje korisnika u ulozi kojoj je dana mogućnost davanja prava drugim korisnicima.
GRANT SELECT ON autorima javnosti
ODOBRITI UMETANJE, AŽURIRANJE, BRISANJE O autorima Mary, John, Tom
GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION
GRANT SELECT ON Plan_Data TO Jack AS Accounting
Jack nije član uloge računovodstva, ali netko u toj ulozi može dati dopuštenje
PORICI(SVE |< вид действия >,…}
( UKLJUČENO (<имя таблицы или представления>} [(<имя столбца>,…)]
| UKLJUČENO (<имя хранимой процедуры>}
| UKLJUČENO (<имя пользовательской функции>}
ZA ( JAVNO |<имя объекта системы безопасности>,…}
– zabrana pristupa korisnika na objekte baze podataka. CASCADE oduzima prava ne samo od dati korisnik, ali i za sve kojima je dao prava.
Primjer (na zabrana zapovijedi TSQL):
DENY STREATE TABLE TO Jack CASCADE
Tim OPOZVATI koristi se za implicitno zabranjivanje pristupa objektima baze podataka. Sintaksa je ista kao naredba DENY. Implicitno odbijanje je slično odbijanju pristupa, osim što se primjenjuje samo na razini na kojoj je definirano. Primjer: Korisnik Jack, koji je član uloge GoodUsers, dobio je prava pristupa tablici XFiles. Ako je REVOKE odbijen za ulogu GoodUsers da pristupi ovoj tablici, Jack i dalje može pristupiti ovoj tablici jer su njegova prava eksplicitno definirana. Ako koristite REVOKE osobno za njega, on će izgubiti pravo pristupa XFiles.
Dozvole dodijeljene ulogama nasljeđuju njihovi članovi. Ako je korisniku odobren pristup objektu kroz članstvo u jednoj ulozi, ali mu je odbijen u drugoj, tada se sukob pristupa uvijek rješava u korist odbijanja.
Tehnologije zaštite podataka u MS SQL Serveru
1.Mehanizam kontrolne točke– kontrolne točke koje se generiraju nakon ~60 s za pisanje ažuriranih stranica na disk (kontrolna točka se može forsirati naredbom CHECKPOINT).
2. Ugrađeni i vanjski mehanizmi za provjeru integriteta baze podataka (pokreću se automatski ili, poput uslužnog programa DBCC - Database Consistency Checker - ručno).
3. Fizičko umnožavanje (ako je dopušteno) datoteka baze podataka pomoću operativnog sustava (uključujući mehanizam zrcaljenih tvrdih diskova).
4. Sigurnosno kopiranje baza podataka i dnevnika transakcija - pisanjem dumpa baze podataka na uređaj za sigurnosno kopiranje (magnetska traka ili tvrdi disk).
5. Replikacija – mogućnost dupliciranja informacija povremenim (u nekim slučajevima, sinkronim) prijenosom s jednog SQL poslužitelja na drugi.
6. Enkripcija prometa između klijenta i poslužitelja, kao i šifriranje kodova koji se koriste za rad s objektima baze podataka (pohranjene procedure, triggeri, itd.)
1) Pojam kursora
Interaktivni SQL ne razlikuje upite s jednim redom i upite s više reda. Ugrađeni SQL drugačije izvršava ove upite. Jednolinijski upiti vraćaju jedan redak i već smo ih obradili. Kada je rezultat upita više od jednog retka, ugrađeni SQL mora dopustiti aplikaciji dohvaćanje rezultata upita red po red. Za to se koriste kursori. Kursor je varijabla povezana s upitom. Njegova vrijednost je svaki redak koji odgovara upitu. Kao i varijable, kursori moraju biti deklarirani prije nego što se mogu koristiti. Za razliku od pogleda, kursori su dizajnirani za obradu redak po redak.
2) Deklaracija kursora
PROGLASITI [{}] [[NE] SVITAK] KURSOR [{SA|BEZ} DRŽI] ZA [ZA {SAMO ZA ČITANJE|AŽURIRANJE [OD ]}]
3) Ključne riječi
. OSJETLJIV|NEOSJETLJIV|NEOSJETLJIV– vidljive su promjene u skupu rezultata | zabranjeno (fiksno pomoću kopije skupa podataka)|DBMS sam odlučuje hoće li napraviti kopiju (radi prema zadanim postavkama).
. SA|BEZ ZADRŽ– ostavlja otvoreno | zatvara kursor ako se naiđe na naredbu COMMIT.
. SVITAK– [sprečava] izdvajanje redaka rezultata nasumičnim redoslijedom.
. SAMO ZA ČITANJE– definira kursor samo za čitanje.
. ZA AŽURIRANJE– blokira ažuriranje samo navedenih stupaca.
4) Deklariranje kursora u SQL Serveru
PROGLASITI KURSOR [LOKALNO|GLOBALNO] [SAMO NAPRIJED|KROLAJ] [STATIČNO|SKUP TIPKOVA|DINAMIČNO|BRZO_NAPRIJED] [SAMO ZA ČITANJE|SCROLL_LOCKS|OPTIMISTIČNO] ZA [ZA AŽURIRANJE [OD ]]
. STATIČKI– Definira kursor koji stvara privremenu kopiju podataka za korištenje od strane kursora. Svi upiti prema kursoru pristupaju navedenoj privremenoj tablici u bazi podataka tempdb, tako da promjene u osnovnim tablicama ne utječu na podatke koje vraćaju uzorci za taj kursor, a sam kursor ne dopušta izmjene.
. KEYSET– Označava da se članstvo ili redoslijed redaka u pokazivaču ne mijenja nakon što se otvori. Skup ključeva koji jedinstveno identificiraju retke ugrađen je u tablicu u bazi podataka tempdb pod nazivom skup ključeva.
. DINAMIČAN– Definira kursor koji prikazuje sve promjene podataka napravljene u redovima skupa rezultata prilikom pregleda ovog kursora. Vrijednosti podataka, redoslijed i članstvo u retku u svakom odabiru mogu varirati. Opciju odabira APSOLUTA ne podržavaju dinamički kursori.
. BRZO_NAPRIJED– Označava kursor FORWARD_ONLY, READ_ONLY za koji je omogućena optimizacija izvedbe. Opcija FAST_FORWARD ne može se specificirati s opcijama SCROLL ili FOR_UPDATE.
. SCROLL_LOCKS– Označava da će pozicionirana ažuriranja ili brisanja izvršena putem pokazivača zajamčeno uspjeti. SQL Server zaključava retke dok se čitaju u kursor kako bi osigurao da su dostupni za naknadne promjene. Opcija SCROLL_LOCKS ne može se specificirati s opcijom FAST_FORWARD ili STATIC.
. OPTIMISTIČAN– Označava da pozicionirana ažuriranja ili brisanja napravljena putem kursora neće uspjeti ako je red ažuriran od vremena kada je učitan u kursor. SQL Server ne zaključava retke dok se čitaju u kursor. Umjesto toga, radi se usporedba vrijednosti stupca s vremenskom oznakom (ili kontrolnih zbrojeva ako tablica nema stupac s vremenskom oznakom) kako bi se utvrdilo je li se red promijenio otkad je pročitan u pokazivaču. Ako je redak izmijenjen, njegova pozicionirana izmjena ili brisanje nije moguće. Opcija OPTIMISTIC ne može se specificirati s opcijom FAST_FORWARD.
5) Otvaranje kursora
6) Dohvaćanje redaka iz kursora
DOHVATI [{SLJEDEĆA|PRIJA|PRVA|ZADNJA|{APSOLUTNO|RELATIVNO }}]
IZ U
7) Opcije pozicioniranja kursora
. SLJEDEĆA|PRIJA|PRVA|ZADNJA– do sljedećeg|prethodnog|prvog|zadnjeg reda skupa rezultata.
. RODBINA ±N– na liniju s pozitivnim ili negativnim pomakom u odnosu na trenutnu liniju.
. APSOLUTNO ±N– do retka s eksplicitno navedenim apsolutnim brojem pozicije od početka ili kraja pokazivača.
Bilješka: SQL Server dopušta cjelobrojnu varijablu @N umjesto N.
8) Zatvaranje kursora
9) Napomene o kursorima
. Ako kursor sadrži više od jedne linije, potrebno je organizirati petlju za dohvaćanje podataka iz njega, povremeno provjeravajući da li se dolazi do posljednjeg retka.
. Za razliku od tablica i pogleda, retci pokazivača poredani su izričito korištenjem odjeljka NARUČI PO, ili u skladu s konvencijama usvojenim u određenom DBMS-u.
. Kursori se također koriste za odabir grupa redaka iz tablica, koje je moguće ažurirati ili brisati jedan po jedan.
. Da bi se kursor mogao ažurirati, mora ispunjavati iste kriterije kao i prikaz, to jest, ne smije sadržavati odjeljke UNION, ORDER BY, GROUP BY, DISTINCT.
10) Primjer brisanja podataka iz kursora
exec sql deklarirati kursor Cur1 za odabir * iz kupca
gdje Ocjena
// ispis (@f1+’ ‘+pretvori(Varchar(5),@f2))
exec sql brisanje od kupca
gdje je struja Cur1; ) – Uzmite podatke za brisanje iz kursora
nije učinjeno:
exec sql zatvori kursor Cur1; — Zatvori kursor
Izlaz();
11) Primjer za povećanje provizija
exec sql proglasi kursor CurCust za odabir * od SalesPeople
gdje je SNum (odaberite SNum od Kupca gdje je Ocjena=300); — Definirajte kursor
exec sql otvori kursor CurCust; - Izvršite kursor
while (sqlca.sqlcode==0) ( — Stvorite petlju za ažuriranje podataka u tablici
exec sql dohvati CurCust u:Id_num, :SalesPerson, :Loc, :Comm;
exec sql ažuriranje SalesPeople postavlja Comm=Comm+.01 gdje je trenutno
od CurCust; ) – Uzmite podatke za ažuriranje iz kursora
exec sql zatvori kursor CurCust; — Zatvori kursorSELECT 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 POMICI KURSOR ZA 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
OTVORENA Cur1
DOHVATI SLJEDEĆE IZ Cur1
WHILE @@FETCH_STATUS=0
POČETI
DOHVATI SLJEDEĆE IZ Cur1
KRAJ
ZATVORI Cur1
DEADOCAL Cur1
