Kursoreiden ja silmukoiden käyttäminen Transact-SQL:ssä. Kohdistimet MySQL:ssä tallennetut toiminnot Kohdistimet sql-palvelimessa
DECLARE CURSOR -komennon avulla voit noutaa tietueita taulukosta rivi riviltä käsittelyä varten. Tämä mahdollistaa rivi-rivikäsittelyn perinteisen tietojoukon käsittelyn sijaan, jota SQL tekee.
Aivan ensimmäisenä likiarvona käytetään seuraavia vaiheita työskennellessäsi kursorin kanssa.
Kursori luodaan DECLARE-komennolla. Kursori avataan OPEN-komennolla.
Toiminnot kohdistimen kanssa suoritetaan FETCH-komennolla. Kohdistin suljetaan CLOSE-komennolla.
DECLARE CURSOR -komento määrittää SELECT-käskyn. Jokainen SELECT-käskyn palauttama rivi voidaan hakea ja käsitellä yksitellen. Seuraava Oracle-esimerkki ilmoittaa kursorin ilmoituslohkossa useiden muiden muuttujien kanssa. Tämän jälkeen seuraavassa BEGIN...END-lohkossa kursori avataan, siitä tehdään valinta ja kohdistin suljetaan.
CURSOR title_price_cursor IS SELECT otsikko, hinta FROM nimikkeistä
JOS hinta EI OLE TYHJÄ; otsikon_hinnan_arvo otsikon_hinnan_kursori ROWTYPE; uusi_hinta NUMERO(10.2);
AVAA otsikkohinta_kohdistin;
FETCH title_price_cur-sor INTO title_price_val;
new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price ARVOT
(title_price_val.title, new_price) SULJE otsikkohinnan_kursori; END;
Koska tämä esimerkki käyttää PL/SQL:ää, emme selitä paljon tämän kirjan koodista. DECLARE-lohko näyttää kuitenkin selvästi kohdistimen ilmoituksen. Suoritettavassa PL/SQL-lohkossa kohdistin alustetaan OPEN-komennolla, arvot haetaan FETCH-komennolla ja lopuksi kohdistin suljetaan CLOSE-komennolla.
SELECT-lause on kursorin perusta, joten se on hyvä testata perusteellisesti ennen kuin se sisällytetään DECLARE CURSOR -käskyyn. SELECT-käsky voi toimia taustalla olevassa taulukossa tai näkymässä. Siksi vain luku -kohdistimet voivat toimia ei-päivitettävien näkymien kanssa. SELECT-käsky voi sisältää lauseita, kuten ORDER BY, GROUP BY ja HAVING, kunhan nämä lausekkeet eivät päivitä lähdetaulukkoa. Jos kohdistin on määritetty muodossa FOR UPDATE, on suositeltavaa poistaa tällaiset lauseet SELECT-käskystä.
Paikallisia kohdistimia käytetään usein tallennettujen proseduurien lähtöparametreina. Siksi voit määrittää ja täyttää kohdistimen tallennettuun toimintosarjaan ja välittää sen kutsuvaan erätyöhön tai tallennettuun toimintosarjaan.
Seuraavassa yksinkertaisessa DB2-esimerkissä määritetään kohdistin, joka etsii osastonumerot, osastojen nimet ja johtajanumerot admin_group "XO1" -ryhmästä.
ILMOITTAA dept_cursor CURSOR
FOR SELECT dept_nbr, osaston_nimi, hallinton_nbr
WHERE admin_group="X01"
ORDER BY d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;
Seuraava Microsoft SQL Server -esimerkki ilmoittaa ja avaa julkaisijataulukon kohdistimen. Kursori valitsee ensimmäisen tietueen, joka vastaa julkaisijataulukon SELECT-lausetta, ja lisää sen toiseen taulukkoon. Sitten se siirtyy seuraavaan tietueeseen, sitten seuraavaan, kunnes kaikki tietueet on käsitelty. Lopuksi kohdistin suljetaan ja muisti vapautetaan (DEALLOCATE-komentoa käytetään vain Microsoft SQL Serverissä).
ILMOITTAA @julkaisijan_nimi VARCHAR(20)
ILMOITA pub_cursor CURSOR FOR SELECT pub_name FROM julkaisijat WHERE maa "USA"
HAE SEURAAVA PUB_kursorista julkaisijan_nimeen
WHILE @s>FETCH_STATUS=0
INSERT INTO Foreign_publishers VALUES("j>julkaisijan_nimi)
SULJE pub_cursor DEALLOCATE pub_cursor
Tässä esimerkissä voit nähdä kohdistimen liikkuvan tietueiden joukossa. (Tämä esimerkki on tarkoitettu vain havainnollistamaan ideaa, koska todellisuudessa on Paras tapa ratkaisu tähän ongelmaan, nimittäin INSERT, SELECT -käsky.)
SE KÄSITTELY: SQL Server (vuodesta 2008) Base SQL-tiedot Azure SQL Data WarehouseParallel Data Warehouse
Määrittää Transact-SQL-palvelimen kohdistimen attribuutit, kuten näkymän ominaisuudet ja kyselyn, jota käytetään luomaan tulosjoukko, jolla kohdistin toimii. DECLARE CURSOR -käsky tukee sekä ISO-standardin mukaista syntaksia että syntaksia, joka käyttää Transact-SQL-kielilaajennusjoukkoa.
ISO-syntaksi ILMOITTAA kohdistimen_nimi [ MITÄÄN ] [ SCROLL ] KOHDISTIN select_lausekkeelle [ FOR ( VAIN LUKU | PÄIVITYS [ OF sarakkeen_nimi [ ,...n ] ] ) ] [;] Transact-SQL:n laajennettu syntaksi ILMOITTAA kursorin_nimi KURSORI [ PAIKALLINEN | MAAILMANLAAJUINEN ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAAMINEN | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYYPPI_VAROITUS ] FOR select_lauseke [ FOR UPDATE [ OF sarakkeen_nimi [ ,...n ] ] ] [;]
kohdistimen_nimi
kohdistimen_nimi
SENSITIIVINEN
tempdb; Näin ollen muutokset taustalla oleviin taulukoihin eivät näy tämän kohdistimen valintojen palauttamissa tiedoissa, eikä tämä kohdistin ole muutettavissa. Käytettäessä ISO-syntaksia, ellei INSENSITIVE-vaihtoehtoa ole määritetty, perustaulukoihin tehdyt päivitykset ja poistot näkyvät myöhemmissä valinnoissa.
SCROLL
Osoittaa, että kaikki näytteenottovaihtoehdot ovat käytettävissä (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Jos ISO DECLARE CURSOR -käsky ei määritä SCROLL-vaihtoehtoa, vain NEXT fetch -asetusta tuetaan. SCROLL-vaihtoehtoa ei voi määrittää FAST_FORWARD-vaihtoehdon kanssa.
select_lauseke
Tavallinen SELECT-käsky, joka määrittää kohdistimen tulosjoukon. FOR BROWSE- ja INTO-avainsanat eivät ole sallittuja select_lauseke kohdistimen ilmoitus.
select_lauseke ristiriidassa pyydetyn tyypin kohdistimen kanssa.
LUE AINOASTAAN
Päivitä ]
sarakkeen_nimi [,
.. .n] on määritetty, vain luetellut sarakkeet sallivat muutokset. Jos UPDATE-käskyä käytetään ilman sarakeluetteloa, päivitys on mahdollista kaikille sarakkeille.
kohdistimen_nimi
Tietyn palvelimen kohdistimen Transact-SQL-nimi. kohdistimen_nimi on noudatettava tunnisteita koskevia sääntöjä.
PAIKALLINEN
Osoittaa, että kohdistin on paikallinen paketissa, tallennetussa toimintosarjassa tai triggerissä, jossa se luotiin. Kohdistimen nimi on voimassa vain tällä alueella. Kohdistimeen voidaan viitata paketin paikallisten muuttujien, tallennettujen proseduurien, triggerien tai tallennetun toimintosarjan lähtöparametrin avulla. OUTPUT-parametria käytetään paikallisen kohdistimen välittämiseen kutsuvalle paketille, tallennettuun proseduuriin tai liipaisuun, joka voi sitten määrittää parametrin kohdistinmuuttujalle, jotta kohdistinta voidaan käyttää myöhemmin tallennetun toiminnon päätyttyä. Kohdistin vapautetaan epäsuorasti, kun erä, tallennettu toimintosarja tai liipaisin suorittaa suorituksen, ellei kohdistinta ole siirretty OUTPUT-parametriin. Jos kohdistin siirrettiin OUTPUT-parametriin, kursori vapautetaan, kun kaikki siihen viittaavat muuttujat vapautetaan tai kun laajuus poistuu.
GLOBAALI
Osoittaa, että kohdistin on yhteydelle yleinen. Kohdistimen nimeä voidaan käyttää missä tahansa tallennetussa toimintosarjassa tai paketissa, joka suoritetaan yhteydessä. Kohdistin vapautetaan implisiittisesti vain, jos yhteys katkeaa.
VAIN_ ETEENPÄIN
Määrittää, että kohdistinta voi tarkastella vain ensimmäiseltä riviltä viimeiseen. Vain FETCH NEXT -hakuvaihtoehto tuetaan. Jos FORWARD_ONLY on määritetty ilman STATIC-, KEYSET- tai DYNAAMIC-avainsanoja, kohdistin toimii DYNAAMISENA kohdistimena. Jos FORWARD_ONLY-argumenttia tai SCROLL-argumenttia ei ole määritetty, oletusarvo on FORWARD_ONLY-argumentti, ellei STATIC-, KEYSET- tai DYNAAMIC-avainsanoja ole olemassa. STATIC-, KEYSET- ja DYNAAMIC-kursorien oletusarvo on SCROLL. Toisin kuin tietokantasovellusliittymiä, kuten ODBC ja ADO, seuraavat Transact-SQL-kursorit tukevat FORWARD_ONLY-tilaa: STATIC, KEYSET ja DYNAMIC.
STAATTINEN
Määrittää kohdistimen, joka luo tiedoista väliaikaisen kopion kohdistimen käytettäväksi. Kaikki kohdistimen kyselyt käyttävät määritettyä väliaikaista taulukkoa tempdb; Näin ollen muutokset taustalla oleviin taulukoihin eivät näy tämän kohdistimen valintojen palauttamissa tiedoissa, eikä tämä kohdistin ole muutettavissa.
KEYSET
Osoittaa, että kohdistimen rivien jäsenyys tai järjestys ei muutu, kun se avataan. Taulukkoon on sisäänrakennettu joukko avaimia, jotka yksilöivät rivit tempdb nimeltään avaimet.
Kohdistimen omistajan tekemät tai muiden käyttäjien tekemät muutokset ei-avainarvoihin perustaulukoissa näkyvät, kun kohdistimen omistaja katselee sitä. Muiden käyttäjien tekemät muutokset eivät näy (muutoksia ei voi tehdä Transact-SQL-palvelinkohdistimella). Jos rivi poistetaan, rivien noutoyritys palauttaa @@FETCH_STATUS -2. Avainarvojen päivitykset kohdistimen rajojen yli ovat samanlaisia kuin vanhan rivin poistaminen ja sitten uuden rivin lisääminen. Uusilla arvoilla varustettu rivi ei ole näkyvissä ja yrittää noutaa rivin vanhoilla arvoilla palauttaa @@FETCH_STATUS -2. Päivitykset näkyvät välittömästi, jos ne tehdään kursorin kautta WHERE CURRENT OF -lausekkeen avulla.
DYNAAMINEN
Määrittää kohdistimen, joka näyttää kaikki tietomuutokset, jotka on tehty tulosjoukon riveille tätä kohdistinta tarkasteltaessa. Tietojen arvot, järjestys ja rivijäsenyys kussakin valinnassa voivat vaihdella. Dynaamiset osoittimet eivät tue ABSOLUUTTI-valintavaihtoehtoa.
FAST_FORWARD
Osoittaa FORWARD_ONLY, READ_ONLY kohdistinta, jonka suorituskyvyn optimointi on käytössä. FAST_FORWARD-vaihtoehtoa ei voi määrittää SCROLL- tai FOR_UPDATE-asetuksilla.
LUE AINOASTAAN
Estää tämän kohdistimen kautta tehdyt muutokset. WHERE CURRENT OF -lause ei voi viitata kursoriin UPDATE- tai DELETE-käskyssä. Tämä vaihtoehto on ensisijainen kohdistimen oletuspäivitysominaisuuteen nähden.
SCROLL_LOCKS
Osoittaa, että kohdistimella tehdyt päivitykset tai poistot onnistuvat taatusti. SQL Server lukitsee rivit, kun niitä luetaan kohdistimeen varmistaakseen, että kyseiset rivit ovat käytettävissä myöhempiä muutoksia varten. SCROLL_LOCKS-vaihtoehtoa ei voi määrittää FAST_FORWARD- tai STATIC-asetuksilla.
OPTIMISTINEN
Määrittää, että kohdistimella tehdyt päivitykset tai poistot epäonnistuvat, jos riviä on päivitetty sen jälkeen, kun se on luettu kohdistimeen. SQL Server ei lukitse rivejä, kun ne luetaan kohdistimeen. Sen sijaan käytetään vertailuja aikaleima sarakkeen arvot tai tarkistussummat, jos ei taulukossa aikaleima sarake määrittääksesi, onko rivi muuttunut sen jälkeen, kun se luettiin kohdistimeen. Jos riviä on muokattu, sijoitetut päivitys- tai poistoyritykset epäonnistuvat. OPTIMISTIC-vaihtoehtoa ei voi määrittää FAST_FORWARD-vaihtoehdon kanssa.
TYPE_WARNING
Määrittää, että asiakkaalle lähetetään varoitus, jos kohdistin muunnetaan implisiittisesti yhdestä pyydetystä tyypistä toiseen.
select_lauseke
Tavallinen SELECT-käsky, joka määrittää kohdistimen tulosjoukon. Avainsanat COMPUTE, COMPUTE BY, FOR BROWSE ja INTO eivät ole sallittuja select_lauseke kohdistimen ilmoitus.
SQL Server muuntaa implisiittisesti kohdistimen toiseen tyyppiin, jos lausekkeet ovat mukana select_lauseke ristiriidassa pyydetyn tyypin kohdistimen kanssa. Lisätietoja on kohdassa Implisiittiset kohdistimen muunnokset.
PÄIVITYSTÄ]
Määrittää päivitettävät kursorin sarakkeet. Jos OF sarakkeen_nimi [,
... n] on annettu, vain luetellut sarakkeet sallivat muutokset. Jos UPDATE-käskyä käytetään ilman sarakeluetteloa, päivitys on mahdollista kaikille sarakkeille, ellei READ_ONLY samanaikaisuusvaihtoehtoa ole määritetty.
DECLARE CURSOR -käsky määrittää Transact-SQL-palvelimen kohdistimen attribuutit, kuten näkymän ominaisuudet ja kyselyn, jota käytetään luomaan tulosjoukko, jolla kohdistin toimii. OPEN-käsky täyttää tulosjoukon ja FETCH-käsky palauttaa rivin siitä. CLOSE-käsky tyhjentää kursoriin liittyvän nykyisen tulosjoukon. DEALLOCATE-käsky vapauttaa kursorin käyttämät resurssit.
DECLARE CURSOR -käskyn ensimmäinen muoto käyttää ISO-syntaksia kohdistimen parametrien määrittämiseen. DECLARE CURSOR -käskyn toinen muoto käyttää Transact-SQL-kielen laajennuksia, joiden avulla voit määrittää osoittimia käyttämällä samoja tyyppejä kuin tietokannan sovellusliittymien, kuten ODBC ja ADO, kohdistintoiminnoissa.
Näitä kahta muotoa ei voi sekoittaa. Jos määrität SCROLL-toiminnon tai jätät avainsanat pois ennen avainsanaa CURSOR, et voi käyttää avainsanoja CURSOR-sanan välissä ja myös select_lauseke avainsanoja. Määritettäessä avainsanoja CURSORin välillä sekä varten select_lauseke avainsanat, et voi määrittää SCROLL tai INSENSITIVE ennen avainsanaa CURSOR.
Jos käytät Transact-SQL-syntaksia DECLARE CURSOR -käskyssä etkä määritä READ_ONLY-, OPTIMISTIC- tai SCROLL_LOCKS-asetuksia, oletusarvo on seuraava.
Jos SELECT-käsky ei tue päivityksiä (tai sillä on riittämättömät oikeudet tai se käyttää etätaulukoita, jotka eivät tue päivityksiä jne.), kohdistin asetetaan kohtaan READ_ONLY.
STATIC ja FAST_FORWARD kohdistimet oletuksena READ_ONLY.
DYNAMIC- ja KEYSET-kohdistimet oletuksena ovat OPTIMISTIC.
Kursoreihin voidaan viitata vain muilla Transact-SQL-käskyillä. Tietokannan API-funktiot eivät voi viitata kohdistimiin. Esimerkiksi kun kohdistin on ilmoitettu, OLE DB-, ODBC- tai ADO-funktiot ja -menetelmät eivät voi viitata sen nimeen. Kohdistinrivejä ei voi valita vastaavilla API-funktioilla ja menetelmillä; Tätä tarkoitusta varten sinun on käytettävä Transact-SQL FETCH -käskyjä.
Seuraavia tallennettuja toimenpiteitä voidaan käyttää kursorin ominaisuuksien määrittämiseen sen ilmoittamisen jälkeen.
Muuttujia voidaan käyttää osana select_lauseke, jossa kohdistin on ilmoitettu. Kohdistimen muuttujien arvot eivät muutu sen ilmoittamisen jälkeen.
Oletusarvoisesti DECLARE CURSOR -oikeudet myönnetään kaikille käyttäjille, joilla on SELECT-oikeudet kohdistimen käyttämiin näkymiin, taulukoihin ja sarakkeisiin.
Et voi käyttää kohdistimia tai laukaisimia taulukossa, jossa on klusteroitu sarakevarastoindeksi. Tämä rajoitus ei koske klusteroituja indeksejä; Voit käyttää kohdistimia ja laukaisimia taulukossa, jossa on klusteroitu sarakevarastoindeksi.
A. Yksinkertaisen kohdistimen ja syntaksin käyttäminen
Kun avaat tämän kohdistimen, luotu tulosjoukko sisältää kaikki taulukon rivit ja sarakkeet. Tämä kohdistin voidaan päivittää, kaikki päivitykset ja poistot näkyvät tämän kohdistimen valinnassa. FETCH``NEXT noudetaan vain, koska SCROLL-parametria ei määritetty.
ILMOITTAA vend_cursor KURSORI VALITSEMAAN * FROM Osto.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. Sisäkkäisten kohdistimien käyttäminen raportin näyttämiseen
Seuraava esimerkki käyttää sisäkkäisiä kohdistimia monimutkaisen raportin näyttämiseen. Jokaiselle palveluntarjoajalle ilmoitetaan sisäinen kohdistin.
SET NOCOUNT ON ;
ILMOITTAA @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 ); TULOSTA" -------- Toimittajan tuoteraportti --------";
ILMOITTAA vendor_cursor VALITSE TOIMIJAN TUNNUS KOHDISTIN, nimi FROM Osto.Toimittaja WHERE PreferredVendorStatus = 1 TILAUS toimittajatunnuksen mukaan; AVAA vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 ALOITA TULOSTUS " " VALITSE @viesti = "----- Tuotteet myyjältä: "+ @toimittajan_nimi TULOSTA @viesti -- Määrittele sisäisen kursorin perusteella
-- myyjän_tunnuksella ulkokohdistimesta. ILMOITTAA product_cursor SELECT FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Muuttuva arvo ulkokohdistimesta AVAA product_cursor FETCH NEXT FROM FROM product_cursor INTO @product JOS @@FETCH_STATUS<>0 PRINT "<
Kursori on linkki kontekstuaaliseen muistialueeseen. Joissakin SQL-ohjelmointikielen toteutuksissa (Oracle, Microsoft SQL Server) - kyselyä suoritettaessa saatu tulosjoukko ja siihen liittyvä nykyinen tietueosoitin. Sanoisin, että kohdistin on virtuaalinen taulukko, joka edustaa vaihtoehtoista tallennustilaa. Tässä tapauksessa kursori antaa sinun käyttää sen tietoja ikään kuin ne olisivat tavallisen taulukon tietoja.
Kursoreita käytetään tallennetuissa menettelyissä. Tarpeeksi teoriaa, katsotaanpa esimerkkiä:
Meillä on tietokanta (tietokanta on vähän huono, tämä on yksi minun laboratoriotyöt, mutta tietokantaopettajamme vaati tällaista rakennetta)
/*pankkitiedot*/
LUO TAULUKKO `pankki` (
`BankName` VARCHAR (50) COLLATE utf8_bin EI NULL OLETUS "" ,
PRIMARY KEY ("pankkitunnus"))ENGINE=InnoDB
MERKKIJÄRJESTÖ "utf8" LAITTEITA "utf8_bin" ;
/*tiedot talletuksista */
LUO TAULUKKO `pankkijakelu` (
`BankId` INTEGER (11) EI NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) EI NULL ,
"ClientId" INTEGER (11) EI NULL ,
PRIMARY KEY ("Pankkitunnus", "ClientId"),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
RAJOITUS `bankdistribution_fk` ULKOINEN AVAIN (`BankId`) VIITTEET `bank` (`BankId`),
RAJOITE `bankdistribution_fk1` ULKOINEN AVAIN (`ClientId`) VIITTEET `client' (`ClientId`)
)ENGINE=InnoDB
/*tietoa sijoittajista*/
LUO TAULUKON `asiakas` (
`ClientId` INTEGER (3) EI NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) EI NULL ,
`Sukunimi` VARCHAR (50) COLLATE utf8_bin EI NULL OLETUS "" ,
`Nimi` VARCHAR (50) COLATE utf8_bin EI NULL OLETUS "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin EI NULL OLETUS "" ,
`Puhelin` VARCHAR (50) COLATE utf8_bin EI NULL OLETUS "" ,
`Osoite` VARCHAR (50) COLATE utf8_bin EI NULL OLETUS "" ,
"SafeId" INTEGER (5) EI NULL ,
PRIMARY KEY(`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 MERKKIJÄRJESTÖ "utf8" LAITTEITA "utf8_bin"
Oletetaan, että meidän on vastaanotettava jokainen pankki vuorotellen ja suoritettava joitakin toimintoja sen kanssa, seuraava kysely voi auttaa meitä tässä
Valitse `pankki`.* `pankista` RAJA NUMERO TIETOSTA, ME TARVITAMME,1. Siten käyttämällä RAJAA WE NEED_RECORD NUMBER, 1, poimimme jokaisen tietueen silmukaksi pankkitaulukosta ja suoritamme sen kanssa tarvitsemamme toiminnot samalla kun nostamme WE NEED_RECORD NUMBER arvoa yhdellä. Nyt teemme samoin, mutta käytämme kohdistin
Alkaa
/* muuttujat, joista poimimme tiedot */
Ilmoita vBankId kokonaisluku ;
Ilmoita vPankin nimi VARCHAR(50);
Ilmoita vOsoite VARCHAR(50);
Ilmoita vPhone VARCHAR (50);
/* hadler-muuttuja - a*/
Ilmoita valmis kokonaisluku oletusarvo 0;
/*Kohdistimen ilmoitus*/
Declare BankCursor Cursor for Select `pankki`.`BankId`,`bank`.`BankName`,`bank`.`Osoite`,`pankki`.`Puhelin, FROM `pankki`, jossa 1;
/*HANDLERin tarkoitus, joka selitetään alla*/
ILMOITTAA JATKOJA KÄSITTELYÄ SQLSTATE:lle "02000" SET done=1;
/* avoin kohdistin */
Avaa BankCursor;
/*hae tiedot*/
WHILE done = 0 DO
ryhdymme tarvittaviin toimiin
END WHILE ;
/*osoittimen sulkeminen*/
Sulje BankCursor;
LOPPU ;* Tämä lähdekoodi korostettiin Source Code Highlighterilla.
Virhe: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Viesti: Ei tietoja – nolla riviä haettu, valittu tai käsitelty
SQLSTATE: 02000 käynnistyy, kun kohdistimen loppu saavutetaan tai kun valinta tai päivitys palauttaa tyhjän merkkijonon.
Seuraavalla rivillä julistimme kohdistimen DECLARE kursorin_nimi CURSOR FOR select_lauseke;
Avaa kohdistin Avaa kursorin_nimi;
Sitten, kunnes saavutamme kohdistimen loppuun (WHILE done = 0 DO), poimimme tiedot ja käsittelemme niitä.
Sinun on suljettava kohdistin ennen kuin poistut tallennetusta toimenpiteestä. Sulje kursorin_nimi;
Se ei vaikuta monimutkaiselta. Mutta SQLSTATE "02000" liittyy monia sudenkuoppia.
WHILE done = 0 DO
HAE pankkikursori vBankId,vPankinNimi,vOsoite,vPhone;
Valitse (ContributeAmount) INTO vContributeAmountSUM FROM pankkijakelusta, jossa BankId = vBankId-raja 1;
teemme joitain toimia
END WHILE ;* Tämä lähdekoodi korostettiin Source Code Highlighterilla.
Kaikki on hyvin ja oikein syntaksin näkökulmasta. Mutta loogisesta näkökulmasta katsottuna ei. Saattaa käydä niin, että tallettajat eivät ole avanneet tilejä jossain pankissa, jolloin Select (ContributeAmount) INTO vContributeAmountSUM FROM pankkijakelusta, jossa BankId = vBankId raja 1; SQLSTATE: 02000 käynnistyy, done-muuttujan arvoksi tulee 1 ja while-silmukka päättyy odotettua aikaisemmin. Tämä voidaan välttää toimimalla seuraavasti
WHILE done = 0 DO
HAE pankkikursori vBankId,vPankinNimi,vOsoite,vPhone;
/* ote pankille sen talletusten määrästä */
if (vContributeAmountSUM > 0) then
/* ote pankille sen talletusten määrästä */
loppu Jos ;
teemme joitain toimia
END WHILE ;* Tämä lähdekoodi korostettiin Source Code Highlighterilla.
Ensimmäisellä pyynnöllä tarkistimme, onko lahjoituksia (jos niitä ei ole, niin vContributeAmountSUM == 0) ja vain jos niitä on, haemme tiedot.
Oletetaan nyt, että meidän on poistettava kunkin asiakkaan eri pankkien tilien kokonaissumma
Ilmoita ClientSummCursor Kursori kohtaan Valitse summa
Ilmoita ClientSummCursor-kohdistin Valitse summa (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join -asiakkaasta kohdassa (client.ClientId = bankdistribution.`ClientId`), jossa 1 ryhmittelee `bankdistributionin mukaan. "ClientId";Avaa ClientSummCursor;
WHILE done = 0 DO
HAE pankkikursori vBankId,vPankinNimi,vOsoite,vPhone;
/* ote pankille sen talletusten määrästä */
Valitse Count(ContributeAmount) INTO vContributeAmountSUM FROM pankkijakelusta, jossa BankId = vBankId-raja 1;
/* tarkista, onko tässä pankissa todella talletuksia */
if (vContributeAmountSUM > 0) then
/* ote pankille sen talletusten määrästä */
Valitse ContributeAmount INTO vContributeAmountSUM pankkijakelusta, jossa BankId = vBankId-raja 1;
loppu Jos ;
teemme joitain toimia.
END WHILE ;* Tämä lähdekoodi korostettiin Source Code Highlighterilla.
Sama tilanne voi syntyä, kun ClientSummCursor-kohdistimen tiedot päättyvät aikaisemmin kuin BankCursorin tiedot, SQLSTATE: 02000 laukeaa, done-muuttujaksi asetetaan 1 ja while-silmukka päättyy aiemmin kuin odotimme. Tämä voidaan välttää toimimalla seuraavasti
Avaa ClientSummCursor;
WHILE done = 0 DO
HAE pankkikursori vBankId,vPankinNimi,vOsoite,vPhone;
/* ote pankille sen talletusten määrästä */
Valitse Count(ContributeAmount) INTO vContributeAmountSUM FROM pankkijakelusta, jossa BankId = vBankId-raja 1;
/* tarkista, onko tässä pankissa todella talletuksia */
if (vContributeAmountSUM > 0) then
/* ote pankille sen talletusten määrästä */
Valitse ContributeAmount INTO vContributeAmountSUM pankkijakelusta, jossa BankId = vBankId-raja 1;
loppu Jos ;
/* ennen tietojen purkamista toisesta osoittimesta, muista sqlstate-tila */
SET old_status = valmis;
/* poimi tarvitsemamme tiedot */
FETCH ClientSummCursor INTO vSum,vClientId;
/* tarkista, onko tiedot haettu ja epäonnistuiko sqlstate 0200 */
jos (tehty = 0) sitten
teemme joitain toimia.
loppu Jos ;
/* ennen hetken loppua, palauta done-muuttujan arvo */
set made = old_status;
END WHILE ;* Tämä lähdekoodi korostettiin Source Code Highlighterilla.
Kiitos kaikille tähän asti lukeneille, toivottavasti tästä on jollekin hyötyä.
Kohdistimen toteutus tietokannassa muistuttaa Java-luokkaa, jolla on joukko tietoja ja menetelmiä sen käsittelemiseksi. Jossa sql kohdistin käyttää tietoja tavallisena taulukkona. Kursoreita voidaan käyttää liipaisimissa, tallennetuissa proseduureissa ja funktioissa.
SQL-standardin mukaisesti kohdistimien kanssa työskenneltäessä suoritetaan seuraavat perustoiminnot:
- kohdistin ilmoitus;
- kursorin avaaminen lukemalla dataa;
- rivi riviltä näytteenotto datasta kursorista;
- rivitietojen muuttaminen kohdistimen avulla;
- kohdistimen sulkeminen, jonka jälkeen se ei ole käytettävissä;
- vapauttamalla kursori, ts. kursorin poistaminen muistista, koska sen sulkeminen ei välttämättä vapauta siihen liittyvää muistia.
Eri toteutuksissa määritelmä kohdistin voi olla joitain eroja. Joskus on esimerkiksi välttämätöntä vapauttaa nimenomaan kohdistimelle varattu muisti. Kun kohdistin on vapautettu, vapautetaan myös siihen liittyvä muisti. Tämä mahdollistaa kursorin nimen uudelleenkäytön. Muissa toteutuksissa, kun kohdistin suljetaan, muistia vapautuu implisiittisesti.
Joissakin tapauksissa et voi tehdä ilman kursorin käyttöä. Jos mahdollista, sinun tulee kuitenkin välttää kursorin käyttöä ja työskennellä tavallisten tietojenkäsittelykomentojen kanssa: SELECT, UPDATE, INSERT, DELETE. Tämä johtuu siitä, että kursorit eivät salli muokkaustoimintoja koko tietomäärälle ja tietojenkäsittelytoimintojen suorittamisen nopeus kohdistimella on huomattavasti pienempi kuin standardi tarkoittaa SQL.
Jos ohjelma voi muuttaa kursoriin ladattua dataa, sitä kutsutaan muokattavaksi. Kun puhumme kursoreista, meidän ei pidä unohtaa tapahtumien eristämistä. Yksi käyttäjä muokkaa tietuetta käyttämällä kohdistinta, kun taas toinen käyttäjä lukee tietuetta käyttämällä omaa kohdistinta. Lisäksi hän voi muuttaa samaa tietuetta, mikä tekee tarpeelliseksi säilyttää tietojen eheyden.
Kursorin ilmoittaminen
Kohdistimet on ilmoitettava ennen kuin niitä voidaan käyttää. SQL-standardi käyttää seuraavaa syntaksia kohdistimen luomiseen:
Ilmoita kursorin_nimi kohdistin select_lausekkeelle ])]
Tämä lauseke ilmoittaa kohdistimen ilmoittaa kohdistin nimellä "kursorin_nimi".
SENSITIIVINEN luodaan staattinen kohdistin, joka ei salli muutosten tekemistä. Lisäksi muiden käyttäjien tekemiä muutoksia ei näytetä. Jos avainsana INSENSITIVE puuttuu, luodaan dynaaminen kohdistin.
Kun käytät avainsanaa SCROLL luotua kohdistinta voidaan vierittää mihin tahansa suuntaan, jolloin voit käyttää mitä tahansa valintakomentoja. Jos tämä argumentti jätetään pois, kohdistin on peräkkäinen, ts. sen katselu on mahdollista vain yhteen suuntaan - alusta loppuun.
Ilmaisu select_lauseke ilmaisee rakenteen tietojen lukemista varten, kuten select ... from ... . Se ei saa sisältää operaattoria sisään, koska kursorilla on oma operaattorinsa hakea täyttääksesi muuttujat kohdistimen tiedoilla.
Kun määritetään argumentti VAIN LUETTAVASTI vain luku -kohdistin luodaan, eikä tietoja saa muuttaa. Dynaaminen kohdistin voidaan ilmoittaa vain luku -kohdistimeksi, jolloin toisen käyttäjän tekemät muutokset voidaan näyttää.
Kursorin luominen argumentilla PÄIVITYSTÄ voit tehdä muutoksia kohdistimen tietoihin joko tietyissä sarakkeissa tai argumentin puuttuessa OF sarakkeen_nimi, kaikissa sarakkeissa.
Voit ilmoittaa useita kohdistimia aliohjelmassa. Mutta jokaisella osoittimella on oltava yksilöllinen nimi. Avataksesi kursorin sinun on käytettävä operaattoria avata joka avaa aiemmin määritellyn kohdistimen:
Kursori auki
SQL määrittelee seuraavan syntaksin kohdistimen avaamiseksi "kursori auki":
Avaa kursorin_nimi;
Tietojen hakeminen kursorista, kohdistimen nouto
Syntaksi tietojen lukemiseksi kursorista joihinkin muuttujiin on seuraava:
Hae kohdistimen_nimi kohtaan var_name [, var_name] ...;
Operaattori hakea valitsee avoimet kohdistimen tiedot muuttujiin, jotka sijaitsevat jälkeen sisään ja siirtää kohdistimen seuraavaan kohtaan.
Kursori kiinni
Operaattori kiinni sulkee kohdistimen. Jos operaattoria ei ole erikseen määritetty, kohdistin sulkeutuu automaattisesti, kun vastaava ohjelmalohko suljetaan.
Sulje kursorin_nimi;
Sulkemisen jälkeen kohdistin ei ole käytettävissä. Sulkemisen yhteydessä kaikki kohdistimen ollessa käynnissä asennetut lukot vapautetaan. Vain avoimet kohdistimet voidaan sulkea. Suljettu, mutta ei vapautettu kohdistin voidaan avata uudelleen. Avaamatonta kohdistinta ei saa sulkea.
Jokaisella DBMS:llä on omat erityispiirteensä kohdistimen käytössä.
Kursorien käytön ominaisuudet Oraclessa
PL/SQL:ssä on neljä kohdistinattribuuttia % LÖYDYT, %EI LÖYDETTY, %ISOPEN Ja %ROWCOUNT. Kohdistimen attribuutit ilmoitetaan kuten %TYPE- ja %ROWTYPE-operaattorit kohdistimen nimen oikealla puolella.
%FOUND-määrite
%NOTFOUND -määrite
Attribuutti %NOTFOUND on täsmälleen %FOUND-määrite vastakohta.
%ISOPEN-attribuutti
%ISOPEN-attribuutti osoittaa vain, onko kohdistin auki vai ei.
%ROWCOUNT määrite
Attribuutti %ROWCOUNT on numeerinen attribuutti, joka palauttaa kohdistimen tietyllä hetkellä lukemien rivien määrän.
Esimerkki SQL-kursorista Oracle DBMS:ssä
Ilmoita v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs-kohdistin valitse id, nimi, summa(comm) ja komm esimiehiltä, joissa tiedot välillä "2014-11-01" ja "2014-11-30" ryhmitellään id:n, nimen mukaan; aloita avoin crs; loop EXIT WHEN crs%NOTFOUND; HAE crs osoitteeseen v_id, v_name, v_comm; lisää bonus(id, name, comm) arvoihin (crs.id, crs.name, crs.comm); lopussa silmukka; tehdä; sulje crs; loppu;
Kursorien käytön ominaisuudet SQL-palvelimessa
MSSQL:ssä käytettävät osoittimet voivat olla peräkkäisiä tai vieritettäviä. Sequential mahdollistaa tietojen valitsemisen vain yhteen suuntaan - alusta loppuun. Vieritettävät osoittimet mahdollistavat liikkeen molempiin suuntiin ja voit hypätä mielivaltaiselle riville kohdistimen tulosjoukossa.
SQL Server tukee staattisia, dynaamisia, peräkkäisiä ja avainsarjaohjattuja kohdistimia.
Staattisessa kohdistimen suunnittelussa tiedot tallennetaan tilannekuvana jossain vaiheessa. Siksi toisen käyttäjän tietokantaan tekemät muutokset eivät näy. Kun kohdistinta avataan, palvelin asettaa lukon kaikille sen täyden tulosjoukon riveille. Staattinen kohdistin ei muutu luomisen jälkeen ja näyttää aina tietojoukon, joka oli olemassa sen avaamishetkellä. Jos muut käyttäjät muuttavat kohdistimen sisältämiä tietoja lähdetaulukossa, tämä ei vaikuta staattiseen kohdistin. Staattiseen kursoriin ei voi tehdä muutoksia, joten se avautuu aina vain luku -tilassa.
Dynaaminen kohdistin vaatii ylimääräisiä verkko- ja ohjelmistoresursseja. Dynaamisia kohdistimia käytettäessä tiedoista ei luoda täydellistä kopiota, vaan valinnat lähdetaulukoista suoritetaan vain, kun käyttäjä käyttää tiettyjä tietoja. Noudon aikana palvelin lukitsee rivit, ja kaikki käyttäjän kursorin koko tulossarjaan tekemät muutokset näkyvät kursorissa. Kuitenkin, kun kohdistin on hakenut tiedot, toisen käyttäjän tekemät muutokset eivät enää näy kursorissa.
Näppäinjoukolla ohjatulla kursorilla on ominaisuuksia staattisen ja dynaamisen välillä. Tietueet tunnistetaan näytteenoton yhteydessä ja siten muutoksia seurataan. Tämäntyyppinen kohdistin on hyödyllinen, kun toteutetaan taaksepäin vieritystä. Tässä tapauksessa tietojen lisäykset ja poistot eivät ole näkyvissä ennen kuin tiedot on päivitetty ja kohdistin valitsee uusi versio tallentaa, jos niihin on tehty muutoksia.
Staattisia kursoreita käytetään parhaiten tietojenkäsittelyjärjestelmissä, ts. raportointijärjestelmiä tai tilastollisia ja analyyttisiä tarkoituksia varten. Staattinen kohdistin noutaa paremmin suuria tietomääriä. Kohteiden (istuimet, liput) sähköisten ostojen tai varausten järjestelmissä on välttämätöntä havaita dynaamisesti päivitetyt tiedot muutosten yhteydessä. Tällaisissa tapauksissa käytetään dynaamista kohdistinta. Näissä sovelluksissa siirrettävän tiedon määrä on tyypillisesti pieni ja sitä käytetään yksittäisen tietueen tasolla.
Peräkkäiset kohdistimet eivät salli tietojen hakemista päinvastaiseen suuntaan, vain kohdistimen alusta loppuun. Peräkkäinen kohdistin ei tallenna joukkoa kaikkia tietorivejä. Ne luetaan tietokannasta heti, kun kursorissa on tehty valinta, mikä mahdollistaa kaikkien käyttäjien tietokantaan INSERT-, UPDATE-, DELETE-komennoilla tekemien muutosten dynaamisen heijastuksen. Kursori lukee viimeisimmän datatilan.
Kohdistimen ilmoitus
Ilmoita kursorin_nimi kohdistin SELECT_lausekkeelle ]]
Kun käytät avainsanaa PAIKALLINEN Paikallinen kohdistin luodaan, joka näkyy vain lohkossa, liipaisussa, tallennetussa toimintosarjassa tai käyttäjän määrittämässä funktiossa. avainsana GLOBAALI, määrittää globaalin kohdistimen, joka on olemassa, kunnes nykyinen yhteys suljetaan.
Operaattori VAIN_ ETEENPÄIN määrittää peräkkäisen kohdistimen, joka sallii tietojen hakemisen vain ensimmäiseltä riviltä viimeiseen. Kun käytät operaattoria SCROLL luodaan vieritettävä kohdistin, jonka avulla tietoja voidaan käyttää missä tahansa järjestyksessä ja mihin tahansa suuntaan.
Kohdistimen tyypin määrittävät operaattorit:
- STATIC - staattisen kohdistimen luominen;
- DYNAAMINEN - dynaamisen kohdistimen luominen;
- KEYSET - näppäinkursorin luominen.
Jos kohdistin LUE AINOASTAAN määritä argumentti FAST_FORWARD, luotu kohdistin optimoidaan nopea pääsy dataan. Tätä argumenttia ei voi käyttää yhdessä argumenttien kanssa VAIN_ ETEENPÄIN Ja OPTIMISTINEN.
Jos kursori luodaan operaattorilla OPTIMISTINEN, silloin on kiellettyä muuttaa tai poistaa rivejä, joita on muutettu kohdistimen avaamisen jälkeen.
Kun määritetään argumentti TYPE_WARNING palvelin raportoi implisiittisen kohdistimen tyypin muutoksen, jos se ei ole yhteensopiva SELECT-kyselyn kanssa.
Haetaan tietoja kursorista, hae
Välittömästi kohdistimen avaamisen jälkeen saat sen sisällön seuraavalla komennolla:
Kun käytät operaattoria ENSIMMÄINEN kohdistimen tulosjoukon ensimmäinen rivi palautetaan, josta tulee nykyinen rivi. Kun on määritelty KESTÄÄ kohdistimen viimeinen rivi palautetaan. Siitä tulee myös nykyinen rivi.
Kun määritetään operaattoria SEURAAVA rivi välittömästi nykyisen tulosjoukon jälkeen palautetaan. Tästä rivistä tulee nykyinen rivi. Oletuskomento FETCH käyttää juuri tätä menetelmää rivien hakemiseen.
Kun määritetään operaattoria ENNEN nykyistä riviä edeltävä rivi palautetaan. Tästä rivistä tulee nykyinen rivi.
Operaattori ABSOLUUTTI (rivin_numero | @rivin_numeromuuttuja) palauttaa rivin absoluuttisella järjestysnumerollaan kohdistimen koko tulosjoukossa. Rivinumero voidaan määrittää vakiolla tai muuttujan nimenä, johon rivinumero on tallennettu. Muuttujan on oltava kokonaislukutietotyyppi. Sekä positiiviset että negatiiviset arvot näytetään. Kun määritetään positiivinen arvo, merkkijono lasketaan joukon alusta, kun taas negatiivinen arvo lasketaan lopusta. Valitusta rivistä tulee nykyinen rivi. Jos määritetään tyhjä arvo, riviä ei palauteta.
Perustelu SUHTEELLINEN (rivien määrä | @muuttuva rivien määrä) palauttaa rivisiirron määritetyn määrän rivejä nykyisen rivin jälkeen. Jos määrität negatiivisen määrän rivejä, palautetaan rivi, joka on määritetty määrä rivejä ennen nykyistä riviä. Nolla-arvon määrittäminen palauttaa nykyisen rivin. Palautetusta rivistä tulee nykyinen rivi.
Jos haluat avata yleisen kohdistimen, sinun on määritettävä avainsana ennen sen nimeä GLOBAALI. Kohdistimen nimi voidaan määrittää myös muuttujan avulla.
Ilmaisussa INTO @muuttujan_nimi [,...n] määritellään muuttujien luettelo, johon palautetun rivin vastaavat sarakearvot tallennetaan. Muuttujien järjestyksen on vastattava kohdistimen sarakkeiden järjestystä ja muuttujan tietotyypin on vastattava kohdistimen sarakkeen tietotyyppiä.
Tietojen muuttaminen ja poistaminen kohdistimen avulla
Jos haluat muuttaa tietoja kohdistimen avulla, sinun on annettava UPDATE-komento seuraavassa muodossa:
Yhdessä toimenpiteessä nykyisen kohdistinrivin useiden sarakkeiden arvoja voidaan muuttaa, mutta niiden kaikkien on kuuluttava samaan taulukkoon.
Jos haluat poistaa tietoja kursorilla, käytä DELETE-komentoa seuraavassa muodossa:
Tämän seurauksena kohdistimen nykyinen rivi poistetaan.
Vapauta muistia, jaa
Voit poistaa kohdistimen muistista komennolla
Pura kohdistimen_nimi;
@@FETCH_STATUS-attribuutti
Voit määrittää rivien läsnäolon kohdistimessa käyttämällä yleistä muuttujaa @@FETCH_STATUS, joka ottaa nollasta poikkeavan arvon, jos kohdistimessa ei ole enää rivejä. Jos riviä ei ole vielä käytetty loppuun, @@FETCH_STATUS on yhtä suuri kuin nolla.
Esimerkki kursorista SQL-palvelimessa
Ilmoita @yritys varchar(50), @manager varchar(50), @message varchar(256); julistaa crs_clients kohdistin paikalliseksi valitulle yritykselle, johtaja asiakkaille, joissa kaupunki = "Moskova" järjestys yrityksen, johtajan mukaan; tulosta "Asiakasluettelo"; avaa crs_clients; hae seuraavaksi crs_clientsistä osoitteeseen @company, @manager; while @@FETCH_STATUS = 0 begin select @message = "Yritys " + @yritys + " johtaja " + @päällikkö; tulosta @viesti; -- siirry seuraavaan tietueeseen hae seuraavaksi crs_clientsistä @yritys, @manager; loppu; sulje crs_clients; purkaa crs_clients;
Kursorin määritelmä annetaan. Kuvaus sen tyypeistä ja käyttäytymisestä tarjotaan: staattiset, dynaamiset, peräkkäiset ja avainkursorit. Kursorin ohjauksen periaatteet kuvataan: kursorin luominen ja avaaminen, datan lukeminen, kursorin sulkeminen. Esimerkkejä kohdistimen ohjelmoinnista on annettu.Kursori käsite
Relaatiotietokannan kysely palauttaa yleensä useita tietorivejä (tietueita), mutta sovellus käsittelee vain yhden tietueen kerrallaan. Vaikka se käsittelee useita rivejä samanaikaisesti (esimerkiksi tietojen näyttäminen laskentataulukoiden muodossa), niiden määrä on silti rajoitettu. Lisäksi, kun tietoja muokataan, poistetaan tai lisätään, työyksikkö on sarja. Tässä tilanteessa kohdistimen käsite tulee etualalle, ja tässä yhteydessä kursori on osoitin riville.
SQL:n kohdistin on tietokannan muistin alue, joka on suunniteltu säilyttämään viimeinen SQL-käsky. Jos nykyinen käsky on tietokantakysely, muistiin tallennetaan myös rivi kyselytietoja, joita kutsutaan nykyiseksi arvoksi tai nykyiseksi kohdistinriviksi. Määritetty muistin alue on nimetty ja sovellusohjelmien käytettävissä.
Tyypillisesti kohdistimia käytetään valitsemaan tietokannasta osa siihen tallennetuista tiedoista. Sovellusohjelma voi tarkistaa yhden kohdistinrivin milloin tahansa. Kursoreita käytetään usein SQL-lauseet, joka on sisäänrakennettu proseduurikielillä kirjoitettuihin sovellusohjelmiin. Jotkut niistä ovat implisiittisesti tietokantapalvelimen luomia, kun taas toiset ovat ohjelmoijien määrittämiä.
SQL-standardin mukaisesti työskennellessäsi kohdistimien kanssa voidaan erottaa seuraavat päätoiminnot:
- luominen tai kohdistimen ilmoitus;
- avausosoitin, eli sen täyttäminen monitasoiseen muistiin tallennetuilla tiedoilla;
- valinta kursorista ja datarivien muuttaminen sen kanssa;
- kursorin sulkeminen, jonka jälkeen se ei pääse käyttäjäohjelmille;
- kursorin vapauttaminen, eli kursorin poistaminen objektina, koska sen sulkeminen ei välttämättä vapauta siihen liittyvää muistia.
Kohdistimen määritelmä voi vaihdella hieman toteutuksissa. Esimerkiksi joskus kehittäjän on vapautettava kohdistimelle varattu muisti. Jälkeen vapauta kohdistin myös siihen liittyvä muisti vapautetaan. Tämä mahdollistaa hänen nimensä uudelleenkäytön. Muissa toteutuksissa milloin kursorin sulkeminen muistin vapauttaminen tapahtuu implisiittisesti. Välittömästi toipumisen jälkeen se on käytettävissä muihin toimenpiteisiin: toisen kohdistimen avaaminen jne.
Joissakin tapauksissa kursorin käyttäminen on väistämätöntä. Tätä tulisi kuitenkin välttää mahdollisuuksien mukaan ja käyttää tavallisia tietojenkäsittelykomentoja: SELECT, UPDATE, INSERT, DELETE. Sen lisäksi, että kursorit eivät salli muokkaustoimintoja koko tietomäärälle, tietojenkäsittelytoimintojen suorittamisnopeus kohdistimella on huomattavasti pienempi kuin tavallisilla SQL-työkaluilla.
Kursorien toteutus MS SQL Server -ympäristössä
SQL Server tukee kolmen tyyppisiä kohdistimia:
- SQL-kursoreita käytetään ensisijaisesti liipaisimissa, tallennetuissa proseduureissa ja skripteissä;
- palvelinkursorit toimivat palvelimella ja toteuttavat sovellusohjelmointirajapinnan ODBC:lle, OLE DB:lle, DB_Librarylle;
- Asiakaskohdistimet toteutetaan itse asiakkaalla. Ne hakevat koko rivien tulosjoukon palvelimelta ja tallentavat sen paikallisesti, mikä nopeuttaa tietojenkäsittelyä vähentämällä verkkotoimintoihin kuluvaa hukkaan aikaa.
Erityyppiset monen käyttäjän sovellukset vaativat erityyppistä rinnakkaista pääsyä tietoihin. Jotkut sovellukset vaativat välittömän pääsyn tietokantaan tehdyistä muutoksista tietoihin. Tämä on tyypillistä lippujen varausjärjestelmille. Muissa tapauksissa, kuten tilastoraportointijärjestelmissä, tietojen vakaus on tärkeää, koska jos niitä jatkuvasti muutetaan, ohjelmat eivät pysty näyttämään tietoja tehokkaasti. Eri sovellukset tarvitsevat erilaisia kohdistinten toteutuksia.
SQL Serverissä kohdistintyypit vaihtelevat niiden tarjoamien ominaisuuksien mukaan. Kohdistimen tyyppi määritetään sen luontivaiheessa, eikä sitä voi muuttaa. Jotkin kursorityypit voivat havaita muiden käyttäjien tekemät muutokset tulosjoukon riveille. SQL Server kuitenkin seuraa tällaisten rivien muutoksia vain rivin käytön aikana eikä salli muutosten muokkaamista, kun rivi on jo luettu.
Kursorit on jaettu kahteen luokkaan: peräkkäinen ja vieritettävä. Peräkkäinen voit valita tietoja vain yhteen suuntaan - alusta loppuun. Vieritettävät osoittimet antaa suuremman toimintavapauden - on mahdollista liikkua molempiin suuntiin ja hypätä kursorin tulosjoukon mielivaltaiselle riville Jos ohjelma pystyy muokkaamaan tietoja, joihin kohdistin osoittaa, sitä kutsutaan vieritettäväksi ja muokattavaksi. Kursoreista puhuttaessa meidän ei pidä unohtaa tapahtumien eristämistä. Kun yksi käyttäjä muokkaa tietuetta, toinen lukee sen käyttämällä omaa kohdistinta, ja lisäksi hän voi muokata samaa tietuetta, mikä tekee tarpeelliseksi säilyttää tietojen eheyden.
SQL Server tukee staattista, dynaamista, peräkkäinen ja sitä ohjataan näppäinsarjalla.
Ohjelmassa kanssa staattinen kursori tieto luetaan tietokannasta kerran ja tallennetaan tilannevedoksena (jonkin ajankohdan mukaan), joten toisen käyttäjän tietokantaan tekemät muutokset eivät näy. Hetkisen kursorin avaaminen palvelin asettaa lukon kaikille sen täyden tulosjoukon riveille. Staattinen kursori ei muutu luomisen jälkeen ja näyttää aina tietojoukon, joka oli olemassa sen avaushetkellä.
Jos muut käyttäjät muuttavat kohdistimen sisältämiä tietoja lähdetaulukossa, tämä ei vaikuta staattinen kursori.
SISÄÄN staattinen kursori Muutoksia ei voi tehdä, joten se avautuu aina vain luku -tilassa.
Dynaaminen kohdistin ylläpitää tietoja "live-tilassa", mutta tämä vaatii verkko- ja ohjelmistoresursseja. Käyttämällä dynaamiset kohdistimet Täydellistä kopiota lähdetiedoista ei luoda, vaan dynaaminen valinta suoritetaan lähdetaulukoista vain, kun käyttäjä käyttää tiettyjä tietoja. Noudon aikana palvelin lukitsee rivit ja kaikki käyttäjän tekemät muutokset kursorin koko tulossarjaan näkyvät kursorissa. Jos joku toinen käyttäjä on kuitenkin tehnyt muutoksia sen jälkeen, kun osoitin on noutanut tiedot, ne eivät näy kohdistimessa.
Kohdistinta ohjataan näppäinsarjalla, on näiden ääripäiden välissä. Tietueet tunnistetaan näytteenoton yhteydessä ja siten muutoksia seurataan. Tämän tyyppinen kursori on hyödyllinen toteutettaessa vierittämistä taaksepäin - silloin rivien lisäykset ja poistot eivät näy ennen kuin tiedot on päivitetty, ja ajuri valitsee tietueesta uuden version, jos siihen on tehty muutoksia.
Peräkkäiset osoittimet eivät saa hakea tietoja päinvastaiseen suuntaan. Käyttäjä voi valita vain rivit kohdistimen alusta loppuun. Sarjakohdistin ei tallenna joukkoa kaikkia rivejä. Ne luetaan tietokannasta heti, kun ne on valittu kursorissa, jolloin kaikki käyttäjien tietokantaan tekemät muutokset näkyvät dynaamisesti INSERT-, UPDATE-, DELETE-komennoilla. Kohdistin näyttää tietojen viimeisimmän tilan.
Staattiset kursorit tarjoavat vakaan kuvan tiedoista. Ne sopivat hyvin tietojen "varastointi"järjestelmiin: sovelluksiin raportointijärjestelmiin tai tilastollisiin ja analyyttisiin tarkoituksiin. Sitä paitsi, staattinen kursori selviytyy muita paremmin suurten tietomäärien näytteenotosta. Sitä vastoin sähköiset osto- tai lipunvarausjärjestelmät edellyttävät dynaamista näkemistä päivitetyistä tiedoista, kun muutoksia tehdään. Tällaisissa tapauksissa sitä käytetään dynaaminen kohdistin. Näissä sovelluksissa siirrettävän tiedon määrä on tyypillisesti pieni ja sitä käytetään rivitasolla (yksittäinen tietue). Ryhmään pääsy on erittäin harvinaista.
Kohdistimen hallinta MS SQL Server -ympäristössä
Kohdistimen ohjaus toteutetaan suorittamalla seuraavat komennot:
- DECLARE - luominen tai kohdistimen ilmoitus;
- AUKI - avausosoitin, eli sen täyttäminen tiedoilla;
- FETCH valinta kursorista ja datarivien muuttaminen kohdistimen avulla;
- KIINNI - kursorin sulkeminen;
- POISTAA – kursorin vapauttaminen, eli kursorin poistaminen objektina.
Kohdistimen ilmoitus
SQL-standardi tarjoaa seuraavan komennon kohdistimen luomiseksi:
Avainsanan INSENSITIVE käyttäminen luo staattinen kursori. Tietojen muutokset eivät ole sallittuja, lisäksi muiden käyttäjien tekemiä muutoksia ei näytetä. Jos avainsana INSENSITIVE puuttuu, a dynaaminen kohdistin.
Kun määrität SCROLL-avainsanan, luotua kohdistinta voidaan vierittää mihin tahansa suuntaan, jolloin voit käyttää mitä tahansa valintakomentoja. Jos tämä argumentti jätetään pois, kohdistin on johdonmukainen, eli sen katselu on mahdollista vain yhteen suuntaan - alusta loppuun.
SELECT-käsky määrittää SELECT-pyynnön rungon, joka määrittää tuloksena olevan rivijoukon kohdistimelle.
FOR READ_ONLY määrittäminen luo vain luku -kohdistimen eikä salli tietojen muokkaamista. Se eroaa staattisesta, vaikka jälkimmäinen ei myöskään salli tietojen muuttamista. Voidaan ilmoittaa vain luku -kohdistimena dynaaminen kohdistin, jonka avulla toisen käyttäjän tekemät muutokset voidaan näyttää.
Kun luot kohdistimen FOR UPDATE -argumentilla, voit suorittaa sen kohdistimessa tietojen muutos joko määritetyissä sarakkeissa tai, jos argumenttia OF sarakkeen_nimi ei ole, kaikissa sarakkeissa.
MS SQL Server -ympäristössä kursorin luontikomennon seuraava syntaksi hyväksytään:
<создание_курсора>::= ILMOITTAA kursorin_nimi KURSORI SELECT_lausekkeelle ]]
LOCAL-avainsanan käyttäminen luo paikallisen kohdistimen, joka näkyy vain sen luoneen paketin, liipaisimen, tallennetun toimintosarjan tai käyttäjän määrittämän funktion puitteissa. Kun paketti, liipaisin, toiminto tai toiminto päättyy, kohdistin tuhoutuu implisiittisesti. Jos haluat siirtää kursorin sisällön sen luoneen rakenteen ulkopuolelle, sinun on määritettävä sen parametrille OUTPUT-argumentti.
Jos GLOBAL-avainsana on määritetty, luodaan yleinen kohdistin. se on olemassa, kunnes nykyinen yhteys suljetaan.
Määrittämällä FORWARD_ONLY luo sarjakohdistin; Tiedoista voidaan ottaa näytteitä vain ensimmäisestä rivistä viimeiseen.
Määrittämällä SCROLL luodaan vieritettävä kohdistin; Tietoja voidaan käyttää missä tahansa järjestyksessä ja mihin tahansa suuntaan.
Määrittämällä STATIC luo staattinen kursori.
KEYSET-määrittäminen luo näppäinkohdistimen.
Määrittämällä DYNAMIC luo dynaaminen kohdistin.
Jos määrität FAST_FORWARD-argumentin READ_ONLY-kohdistimelle, luotu kohdistin optimoidaan nopeaa tietojen käyttöä varten. Tätä argumenttia ei voi käyttää yhdessä FORWARD_ONLY- tai OPTIMISTIC-argumenttien kanssa.
OPTIMISTIC-argumentilla luotu kursori estää niiden rivien muuttamisen tai poistamisen, joita on muokattu kursorin avaaminen.
Määrittämällä argumentin TYPE_WARNING palvelin ilmoittaa käyttäjälle kohdistimen tyypin implisiittisestä muutoksesta, jos se ei ole yhteensopiva SELECT-kyselyn kanssa.
Kursorin avaaminen
varten kursorin avaaminen ja täytä se kohdistinta luotaessa määritetyn SELECT-kyselyn tiedoilla, käytä seuraavaa komentoa:
Jälkeen kursorin avaaminen Siihen liittyvä SELECT-käsky suoritetaan, jonka tulos tallennetaan monitasomuistiin.
Haetaan tietoja kursorista
Heti jälkeen kursorin avaaminen voit valita sen sisällön (vastaavan kyselyn suorittamisen tulos) seuraavalla komennolla:
FIRST-arvon määrittäminen palauttaa kohdistimen täydellisen tulosjoukon ensimmäisen rivin, josta tulee nykyinen rivi.
LAST-arvon määrittäminen palauttaa kohdistimen viimeisimmän rivin. Siitä tulee myös nykyinen rivi.
SEURAAVA:n määrittäminen palauttaa rivin välittömästi nykyisen rivin jälkeen koko tulosjoukossa. Nyt siitä tulee ajankohtaista. Oletusarvoisesti FETCH-komento käyttää tätä menetelmää rivien hakemiseen.
Avainsana PRIOR palauttaa rivin ennen nykyistä. Siitä tulee ajankohtaista.
Perustelu ABSOLUUTTI (rivin_numero | @rivin_numeromuuttuja) palauttaa rivin absoluuttisella järjestysnumerollaan kohdistimen koko tulosjoukossa. Rivinumero voidaan määrittää vakiolla tai muuttujan nimenä, johon rivinumero on tallennettu. Muuttujan on oltava kokonaislukutietotyyppi. Sekä positiiviset että negatiiviset arvot näytetään. Kun määritetään positiivinen arvo, merkkijono lasketaan joukon alusta, kun taas negatiivinen arvo lasketaan lopusta. Valitusta rivistä tulee nykyinen rivi. Jos määritetään tyhjä arvo, riviä ei palauteta.
Perustelu SUHTEELLINEN (rivien määrä | @muuttuva rivien määrä) palauttaa rivin, joka on määritetty määrä rivejä nykyisen rivin jälkeen. Jos määrität negatiivisen määrän rivejä, palautetaan rivi, joka on määritetty määrä rivejä ennen nykyistä riviä. Nolla-arvon määrittäminen palauttaa nykyisen rivin. Palautetusta rivistä tulee nykyinen rivi.
Vastaanottaja avaa globaali kohdistin, sinun on määritettävä GLOBAL-avainsana ennen sen nimeä. Kohdistimen nimi voidaan määrittää myös muuttujan avulla.
Suunnittelussa INTO @muuttujan_nimi [,...n] määritetään muuttujien luettelo, johon palautetun rivin vastaavat sarakearvot tallennetaan. Määritettävien muuttujien järjestyksen on vastattava kohdistimen sarakkeiden järjestystä, ja muuttujan tietotyypin on vastattava kohdistimen sarakkeen tietotyyppiä. Jos INTO-rakennetta ei ole määritetty, FETCH-komennon käyttäytyminen muistuttaa SELECT-komennon käyttäytymistä - tiedot näytetään näytöllä.
Tietojen muuttaminen ja poistaminen
Jos haluat tehdä muutoksia kohdistimen avulla, sinun on annettava UPDATE-komento seuraavassa muodossa:
Nykyisen kohdistinrivin useita sarakkeita voidaan muuttaa yhdellä toiminnolla, mutta niiden kaikkien on kuuluttava samaan taulukkoon.
Jos haluat poistaa tietoja kursorilla, käytä DELETE-komentoa seuraavassa muodossa:
Tämän seurauksena kohdistimen rivivirta poistetaan.
Kohdistimen sulkeminen
Sulkemisen jälkeen ohjelman käyttäjät eivät pääse kursoriin. Suljettuna kaikki sen käytön aikana asennetut lukot poistetaan. Sulkemista voidaan soveltaa vain avoimiin kohdistimiin. Suljettu, mutta ei vapautettu kursori voidaan avata uudelleen. Avaamatonta kohdistinta ei saa sulkea.
Vapauta kursori
Kohdistimen sulkeminen ei välttämättä vapauta siihen liittyvää muistia. Joidenkin toteutusten on nimenomaisesti purettava se DEALLOCATE-käskyn avulla. Jälkeen vapauta kohdistin Myös muistia vapautetaan, mikä mahdollistaa kursorin nimen uudelleenkäytön.
Sen määrittämiseksi, onko kohdistimen loppu saavutettu, on suositeltavaa käyttää toimintoa: @@FETCH_STATUS
@@FETCH_STATUS-funktio palauttaa:
0, jos haku onnistui;
1, jos haku epäonnistui, koska yritettiin hakea rivi kohdistimen ulkopuolelta;
2, jos haku epäonnistui poistetun tai muokatun rivin käytön vuoksi.
ILMOITTAA @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 TULOSTA "Ostalista" ILMOITTAA klient_cursor KURSORI PAIKALLINEN VALITSEMAAN Asiakaskoodi, yritys, sukunimi ASIAKKAALTA WHERE Kaupunki="Moskova" TILAUS Yritys, Sukunimi OPEN klient_cursori HAE SEURAAVAKSI ASIAKKASTA ASIAKKAAN @id_kl, @firm, @fam CH_STATUS @@FET =0 BEGIN SELECT @message="Asiakas "+@fam+ "Yritys "+ @firm PRINT @message SELECT @message="Tuotteen nimi Ostopäivä Hinta" TULOSTA @viesti ILMOITTAA tovar_cursor VALITSEMAAN Tuotteen nimi, tapahtuma.päivämäärä, tuote .Hinta* Tapahtuma.Määrä Kustannuksina tuotteesta SISÄLLINEN LIITTYMINEN Tuotteen tapahtuma. Tuotekoodi=Transaction.Product Code WHERE Transaction.Customer Code=@id_kl AVAA tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p JOS @@FETCH_STATUS<>0 TULOSTA "Ei ostoksia" WHILE @@FETCH_STATUS=0 ALOITA VALITSE @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) TULOSTA @message SET @s=@s+@p HAE SEURAAVAKSI tovar_cursorista @nam, @d, @p LOPPU SULJE tovar_cursor POISTA tovar_cursor SELECT @message="Kokonaiskustannukset "+ CAST(@s AS CHAR(6)) TULOSTA @viesti -- siirry seuraavaan asiakkaaseen -- HAE SEURAAVA klient_cursorista @id_kl, @firm, @fam LOPPU SULJE klient_cursor POISTA asiakas_kursori Esimerkki 13.6. Kursori näyttää luettelon Moskovasta asiakkaiden ostamista tuotteista ja niiden kokonaiskustannuksista.
Esimerkki 13.7. Kehitä vieritettävä kohdistin Moskovan asiakkaille. Jos puhelinnumero alkaa 1:llä, poista asiakas tällä numerolla ja korvaa ensimmäisessä kohdistinmerkinnässä puhelinnumeron ensimmäinen numero numerolla 4.
ILMOITTAA @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @viesti VARCHAR(80) TULOSTA "Asiakasluettelo" ILMOITTAA klient_cursori KURSORI MAAILMANLAAJUINEN SCROLL KEYSET VALITSEMAAN Yritys, Sukunimi, Puhelin Asiakkaalta WHERE Kaupunki ="Moskova" TILAUS Yrityksen, Sukunimi PÄIVITYS MUKAAN AVAA klient_cursor HAE SEURAAVANA klient_cursorista @firm, @fam, @tel WHILE @@FETCH_STATUS=0 ALOITA VALITSE @message="Asiakas "+@fam+ " Yritys "+ @firm " Puhelin "+ @tel TULOSTA @viesti -- jos puhelinnumero alkaa 1:llä, -- poista asiakas tällä numerolla JOS @tel LIKE '1%' POISTA asiakas MISSÄ NYKYINEN klient_kursori MUU -- siirry seuraavaan asiakas HAE SEURAAVAKSI klient_kursorista @firm, @fam, @tel LOPPU HAE ABSOLUUTTI 1 KENTÄS klient_kursorista @firm, @fam, @tel -- korvaa ensimmäisessä merkinnässä puhelinnumeron ensimmäinen numero numerolla 4 PÄIVITYS Client SET Phone ='4' + OIKEA(@ tel,PITOA(@tel)-1)) MISSÄ klient_kursorin NYKYINEN VALITSE @message="Asiakas "+@fam+" Yritys "+ @firm "Puhelin"+ @tel TULOSTA @viesti SULJE klient_kursori POISTA asiakaskohdistin Esimerkki 13.7. Vieritettävä kursori asiakkaille Moskovasta.
Esimerkki 13.8. Käyttö kohdistin proseduurin lähtöparametriksi. Toimenpide palauttaa tietojoukon - tuoteluettelon.
Proseduurin kutsuminen ja tietojen tulostaminen lähtökohdistimesta suoritetaan seuraavasti:
ILMOITTAA @my_cur KURSORI ILMOITTAA @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) ALOITA HAE SEURAAVAKSI @my_cur @n @n LOPETA SULJETTU @my_cur POISTA @my_cur
