Un cursor a SQL Server és un d objecte atabase que ens permet recuperar cada fila alhora i manipular les seves dades . Un cursor no és més que un punter a una fila. Sempre s'utilitza juntament amb una instrucció SELECT. Normalment és una col·lecció de SQL lògica que recorre un nombre predeterminat de files una per una. Una il·lustració senzilla del cursor és quan tenim una àmplia base de dades de registres dels treballadors i volem calcular el sou de cada treballador després de deduir els impostos i les baixes.
El servidor SQL El propòsit del cursor és actualitzar les dades fila per fila, canviar-les o realitzar càlculs que no són possibles quan recuperem tots els registres alhora. . També és útil per realitzar tasques administratives com les còpies de seguretat de bases de dades SQL Server en ordre seqüencial. Els cursors s'utilitzen principalment en els processos de desenvolupament, DBA i ETL.
En aquest article s'explica tot sobre el cursor d'SQL Server, com ara el cicle de vida del cursor, per què i quan s'utilitza el cursor, com implementar els cursors, les seves limitacions i com podem substituir un cursor.
Cicle de vida del cursor
Podem descriure el cicle de vida d'un cursor a la cinc seccions diferents com segueix:
1: Declarar el cursor
El primer pas és declarar el cursor mitjançant la instrucció SQL següent:
Linux executeu cmd
DECLARE cursor_name CURSOR FOR select_statement;
Podem declarar un cursor especificant el seu nom amb el tipus de dades CURSOR després de la paraula clau DECLARE. Aleshores, escriurem la instrucció SELECT que defineix la sortida del cursor.
2: Obriu el cursor
És un segon pas en què obrim el cursor per emmagatzemar les dades recuperades del conjunt de resultats. Ho podem fer utilitzant la següent instrucció SQL:
OPEN cursor_name;
3: Obtenir el cursor
És un tercer pas en què les files es poden obtenir una per una o en un bloc per fer manipulacions de dades com ara operacions d'inserció, actualització i supressió a la fila activa actualment del cursor. Ho podem fer utilitzant la següent instrucció SQL:
FETCH NEXT FROM cursor INTO variable_list;
També podem utilitzar el Funció @@FETCHSTATUS a SQL Server per obtenir l'estat del cursor de la instrucció FETCH més recent que s'ha executat contra el cursor. El FETCH La declaració va tenir èxit quan @@FETCHSTATUS dóna una sortida zero. El MENTRE La instrucció es pot utilitzar per recuperar tots els registres del cursor. El codi següent ho explica més clarament:
WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM cursor_name; END;
4: Tanca el cursor
És un quart pas en el qual s'ha de tancar el cursor un cop acabem de treballar amb un cursor. Ho podem fer utilitzant la següent instrucció SQL:
CLOSE cursor_name;
5: Desassignar el cursor
És el cinquè i últim pas en què esborrarem la definició del cursor i alliberarem tots els recursos del sistema associats al cursor. Ho podem fer utilitzant la següent instrucció SQL:
DEALLOCATE cursor_name;
Usos del cursor SQL Server
Sabem que els sistemes de gestió de bases de dades relacionals, inclòs SQL Server, són excel·lents per gestionar dades en un conjunt de files anomenat conjunts de resultats. Per exemple , tenim taula taula_productes que conté les descripcions del producte. Si volem actualitzar el preu del producte, després el següent ' ACTUALITZA' La consulta actualitzarà tots els registres que coincideixin amb la condició del ' ON' clàusula:
UPDATE product_table SET unit_price = 100 WHERE product_id = 105;
De vegades, l'aplicació ha de processar les files d'una manera única, és a dir, fila per fila en lloc de tot el conjunt de resultats alhora. Podem fer aquest procés utilitzant cursors a SQL Server. Abans d'utilitzar el cursor, hem de saber que els cursors tenen molt mal rendiment, per la qual cosa s'ha d'utilitzar sempre només quan no hi ha cap opció excepte el cursor.
El cursor utilitza la mateixa tècnica que fem servir bucles com FOREACH, FOR, WHILE, DO WHILE per iterar un objecte alhora en tots els llenguatges de programació. Per tant, es podria triar perquè aplica la mateixa lògica que el procés de bucle del llenguatge de programació.
Tipus de cursors a SQL Server
A continuació es mostren els diferents tipus de cursors a SQL Server:
- Cursors estàtics
- Cursors dinàmics
- Cursors només cap endavant
- Cursors de tecles
Cursors estàtics
El conjunt de resultats que mostra el cursor estàtic és sempre el mateix que quan es va obrir el cursor per primera vegada. Com que el cursor estàtic emmagatzemarà el resultat a tempdb , sempre ho són Llegeix només . Podem utilitzar el cursor estàtic per avançar i enrere. A diferència d'altres cursors, és més lent i consumeix més memòria. Com a resultat, només el podem utilitzar quan el desplaçament és necessari i altres cursors no són adequats.
Aquest cursor mostra les files que s'han eliminat de la base de dades després d'obrir-la. Un cursor estàtic no representa cap operació d'INSERT, UPDATE o DELETE (tret que el cursor es tanqui i torni a obrir).
entitat relacional
Cursors dinàmics
Els cursors dinàmics són oposats als cursors estàtics que ens permeten realitzar les operacions d'actualització, supressió i inserció de dades mentre el cursor està obert. És desplaçable per defecte . Pot detectar tots els canvis fets a les files, l'ordre i els valors del conjunt de resultats, tant si els canvis es produeixen dins del cursor com fora del cursor. Fora del cursor, no podem veure les actualitzacions fins que no es confirmin.
Cursors només cap endavant
És el tipus de cursor predeterminat i més ràpid entre tots els cursors. S'anomena cursor només cap endavant perquè només es mou cap endavant a través del conjunt de resultats . Aquest cursor no admet el desplaçament. Només pot recuperar files des del principi fins al final del conjunt de resultats. Ens permet realitzar operacions d'inserció, actualització i supressió. Aquí, l'efecte de les operacions d'inserció, actualització i supressió realitzades per l'usuari que afecten les files del conjunt de resultats són visibles a mesura que s'obtenen les files del cursor. Quan es va obtenir la fila, no podem veure els canvis fets a les files a través del cursor.
Els cursors de només cap endavant són tres que es classifiquen en tres tipus:
- Forward_Only Keyset
- Forward_Only estàtic
- Avanç ràpid
Cursors controlats per joc de tecles
Aquesta funcionalitat del cursor es troba entre un cursor estàtic i un dinàmic pel que fa a la seva capacitat per detectar canvis. No sempre pot detectar canvis en la pertinença i l'ordre del conjunt de resultats com un cursor estàtic. Pot detectar canvis en els valors de les files del conjunt de resultats com si fos un cursor dinàmic. Només es pot passar de la primera a l'última i l'última a la primera fila . L'ordre i la pertinença es fixen sempre que s'obre aquest cursor.
S'utilitza per un conjunt d'identificadors únics iguals que les claus del conjunt de claus. El conjunt de claus està determinat per totes les files que qualificaven la instrucció SELECT quan es va obrir el cursor per primera vegada. També pot detectar qualsevol canvi a la font de dades, que admet operacions d'actualització i supressió. Es pot desplaçar per defecte.
Implementació de l'exemple
Implementem l'exemple del cursor al servidor SQL. Podem fer-ho creant primer una taula anomenada ' client ' utilitzant la declaració següent:
CREATE TABLE customer ( id int PRIMARY KEY, c_name nvarchar(45) NOT NULL, email nvarchar(45) NOT NULL, city nvarchar(25) NOT NULL );
A continuació, inserirem valors a la taula. Podem executar la instrucció següent per afegir dades a una taula:
INSERT INTO customer (id, c_name, email, city) VALUES (1,'Steffen', '[email protected]', 'Texas'), (2, 'Joseph', '[email protected]', 'Alaska'), (3, 'Peter', '[email protected]', 'California'), (4,'Donald', '[email protected]', 'New York'), (5, 'Kevin', '[email protected]', 'Florida'), (6, 'Marielia', '[email protected]', 'Arizona'), (7,'Antonio', '[email protected]', 'New York'), (8, 'Diego', '[email protected]', 'California');
Podem verificar les dades executant el SELECCIONA declaració:
SELECT * FROM customer;
Després d'executar la consulta, podem veure la sortida següent on tenim vuit files a la taula:
Ara, crearem un cursor per mostrar els registres dels clients. Els fragments de codi següents expliquen tots els passos de la declaració o creació del cursor reunint-ho tot:
1 milió en dígits
--Declare the variables for holding data. DECLARE @id INT, @c_name NVARCHAR(50), @city NVARCHAR(50) --Declare and set counter. DECLARE @Counter INT SET @Counter = 1 --Declare a cursor DECLARE PrintCustomers CURSOR FOR SELECT id, c_name, city FROM customer --Open cursor OPEN PrintCustomers --Fetch the record into the variables. FETCH NEXT FROM PrintCustomers INTO @id, @c_name, @city --LOOP UNTIL RECORDS ARE AVAILABLE. WHILE @@FETCH_STATUS = 0 BEGIN IF @Counter = 1 BEGIN PRINT 'id' + CHAR(9) + 'c_name' + CHAR(9) + CHAR(9) + 'city' PRINT '--------------------------' END --Print the current record PRINT CAST(@id AS NVARCHAR(10)) + CHAR(9) + @c_name + CHAR(9) + CHAR(9) + @city --Increment the counter variable SET @Counter = @Counter + 1 --Fetch the next record into the variables. FETCH NEXT FROM PrintCustomers INTO @id, @c_name, @city END --Close the cursor CLOSE PrintCustomers --Deallocate the cursor DEALLOCATE PrintCustomers
Després d'executar un cursor, obtindrem la següent sortida:
Limitacions del cursor SQL Server
Un cursor té algunes limitacions de manera que s'hauria d'utilitzar sempre només quan no hi hagi cap opció excepte el cursor. Aquestes limitacions són:
- El cursor consumeix recursos de xarxa perquè requereix un viatge d'anada i tornada cada vegada que obté un registre.
- Un cursor és un conjunt de punters residents a la memòria, el que significa que necessita una mica de memòria que altres processos podrien utilitzar a la nostra màquina.
- Impposa bloquejos a una part de la taula o a tota la taula quan es processen les dades.
- El rendiment i la velocitat del cursor són més lents perquè actualitzen els registres de la taula una fila a la vegada.
- Els cursors són més ràpids que els bucles while, però tenen més sobrecàrrega.
- El nombre de files i columnes introduïdes al cursor és un altre aspecte que afecta la velocitat del cursor. Es refereix al temps que triga a obrir el cursor i executar una instrucció fetch.
Com podem evitar els cursors?
El treball principal dels cursors és recórrer la taula fila per fila. La manera més senzilla d'evitar els cursors es mostra a continuació:
Utilitzant el bucle while SQL
La manera més senzilla d'evitar l'ús d'un cursor és utilitzar un bucle while que permet inserir un conjunt de resultats a la taula temporal.
Funcions definides per l'usuari
De vegades s'utilitzen cursors per calcular el conjunt de files resultant. Podem aconseguir-ho utilitzant una funció definida per l'usuari que compleixi els requisits.
Ús de les unions
Join processa només aquelles columnes que compleixen la condició especificada i, per tant, redueix les línies de codi que donen un rendiment més ràpid que els cursors en cas que s'hagin de processar registres enormes.