Diferència entre revisions de la pàgina «MySQL: read-only cursors»
Salta a la navegació
Salta a la cerca
m |
m |
||
| Línia 1: | Línia 1: | ||
| + | =Introducció= | ||
| + | Si volem incrementar el salari de tots els empleats un 10% és tan senzill com fer: | ||
| + | <pre> | ||
| + | mysql> update employees set salary=salary*1.1; | ||
| + | </pre> | ||
| + | En el cas de què vulguis fer aquest ''update'' i tornar a deixar la taula com estava, ho pots fer de la següent manera: | ||
<pre> | <pre> | ||
| − | + | start transaction; #comencem una transacció | |
| − | update | + | update employees set salary=salary*1.1; #fem el update |
| − | + | select * from employees; #veiem els canvis que s'han realitzat | |
| + | rollback; #tirem endarrere els canvis | ||
| + | </pre> | ||
Ara bé, si volem incrementar el salari un 10% els empleats dels empleats que tenen un fill no és tan senzill. Podem intentar: | Ara bé, si volem incrementar el salari un 10% els empleats dels empleats que tenen un fill no és tan senzill. Podem intentar: | ||
| − | update employees set salary=salary*1.1 where employee_id IN (select e.employee_id from employees e INNER JOIN dependents d USING(employee_id)); | + | <pre> |
| + | $ update employees set salary=salary*1.1 where employee_id IN (select e.employee_id from employees e INNER JOIN dependents d USING(employee_id)); | ||
ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause | ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause | ||
| − | + | </pre> | |
| − | Això no ho podem fer pel mateix motiu que no podem fer cursors ''actualitzables''. A MySQL els cursors són ''read-only''. | + | És a dir, no podem fer el ''update'' en el mateix moment que estem fent una select amb la mateixa taula. Això és així per seguretat, i no ho podem fer pel mateix motiu que no podem fer cursors ''actualitzables''. A MySQL els cursors són ''read-only''. |
El que sí que funciona és fer una vista, i actualitzar sobre la vista: | El que sí que funciona és fer una vista, i actualitzar sobre la vista: | ||
| + | <pre> | ||
| + | create view empleats_amb_fills as select e.employee_id,e.first_name,e.last_name,salary from employees e INNER JOIN dependents d USING(employee_id); | ||
| − | |||
update empleats_amb_fills set salary=salary*1.1; | update empleats_amb_fills set salary=salary*1.1; | ||
| + | select * from empleats_amb_fills | ||
| + | </pre> | ||
| + | *https://dev.mysql.com/doc/refman/8.0/en/cursors.html | ||
| − | + | A Oracle existeixen els ''CURSOR FOR UPDATE'', però a MySQL els cursors són ''read only''. A Oracle podem estudiar el següent exemple: | |
| − | A Oracle existeixen els CURSOR FOR UPDATE, però a MySQL els cursors són ''read only''. | ||
*https://www.oracletutorial.com/plsql-tutorial/oracle-cursor-for-update/ | *https://www.oracletutorial.com/plsql-tutorial/oracle-cursor-for-update/ | ||
En aquest exemple (llenguatge PL/SQL d'Oracle) llegim en un cursor la taula ''customers'', i a mida que recorrem (''LOOP'') els resultats actualitzem el camp ''credit_limit''. Això no ho podem fer a MySQL. Anem a veure una alternativa per poder fer-ho. | En aquest exemple (llenguatge PL/SQL d'Oracle) llegim en un cursor la taula ''customers'', i a mida que recorrem (''LOOP'') els resultats actualitzem el camp ''credit_limit''. Això no ho podem fer a MySQL. Anem a veure una alternativa per poder fer-ho. | ||
| − | + | =Solució alternativa per fer cursors actualitzables= | |
| − | Per solucionar aquest tema, el que farem és generar les sentències ''udpate'' que ens permetran actualitzar els valors, a posteriori. Generarem una llista de comandes ''update'', que les | + | Per solucionar aquest tema, el que farem és generar les sentències ''udpate'' que ens permetran actualitzar els valors, a posteriori. Generarem una llista de comandes ''update'', que les volcarem per la pantalla o a un fitxer. |
Treballem amb la base de dades HR. Comencem per un exemple senzill i després farem un exemple més complicat. | Treballem amb la base de dades HR. Comencem per un exemple senzill i després farem un exemple més complicat. | ||
| − | + | '''NOTA''': per tal de visualitzar de forma clara el resultat, convé utilitzar l'opció ''silent'': -ss | |
| − | + | </pre> | |
| + | <pre> | ||
| + | $ mysql -u alumne -pkeiL2lai -ss HR | ||
| + | </pre> | ||
| + | ==Primer exemple== | ||
| + | procediment ''modificarSalari'': | ||
| + | <pre> | ||
DROP PROCEDURE IF EXISTS modificarSalari; | DROP PROCEDURE IF EXISTS modificarSalari; | ||
| Línia 54: | Línia 72: | ||
END$$ | END$$ | ||
DELIMITER ; | DELIMITER ; | ||
| − | |||
| − | |||
| − | |||
| − | |||
</pre> | </pre> | ||
<pre> | <pre> | ||
| − | + | CALL modificarSalari(); | |
</pre> | </pre> | ||
| − | |||
I amb això el que obtenim és una llista de sentències SQL que ens serveixen per actualitzar el salari dels empleats que tenen fills: | I amb això el que obtenim és una llista de sentències SQL que ens serveixen per actualitzar el salari dels empleats que tenen fills: | ||
<pre> | <pre> | ||
| Línia 74: | Línia 87: | ||
... | ... | ||
</pre> | </pre> | ||
| + | ==Segon exemple== | ||
<pre> | <pre> | ||
Ara el que farem és implementar una regla més complicada: incrementarem el sou d'un empleat seguint aquestes dues regles: | Ara el que farem és implementar una regla més complicada: incrementarem el sou d'un empleat seguint aquestes dues regles: | ||
| + | |||
1) s'incrementa el sou amb el % relatiu de la diferència entre el max_salary i el min_salary del job al qual pertany l'empleat. Per exemple, per al cas del job ''Stock Manager'', la diferència entre 8550-5500 és 3000, i s'incrementa el sou en un (3000/8500)*100 = 35,29%. | 1) s'incrementa el sou amb el % relatiu de la diferència entre el max_salary i el min_salary del job al qual pertany l'empleat. Per exemple, per al cas del job ''Stock Manager'', la diferència entre 8550-5500 és 3000, i s'incrementa el sou en un (3000/8500)*100 = 35,29%. | ||
| + | |||
2) sobre aquest valor, s'incrementa el sou un 10% si l'empleat té un fill. | 2) sobre aquest valor, s'incrementa el sou un 10% si l'empleat té un fill. | ||
Primer de tot treballem la consulta que ens dona informació sobre el salari min i màx del job de l'empleat, i de si aquest té o no té fills: | Primer de tot treballem la consulta que ens dona informació sobre el salari min i màx del job de l'empleat, i de si aquest té o no té fills: | ||
| − | + | <pre> | |
select e.employee_id, e.job_id, e.salary, min_salary, max_salary,d.employee_id from employees e INNER JOIN jobs j USING(job_id) | select e.employee_id, e.job_id, e.salary, min_salary, max_salary,d.employee_id from employees e INNER JOIN jobs j USING(job_id) | ||
LEFT OUTER JOIN dependents d ON e.employee_id=d.employee_id; | LEFT OUTER JOIN dependents d ON e.employee_id=d.employee_id; | ||
| − | + | </pre> | |
| − | + | proceciment ''modificarSalari_v2'': | |
| + | <pre> | ||
DROP PROCEDURE IF EXISTS modificarSalari_v2; | DROP PROCEDURE IF EXISTS modificarSalari_v2; | ||
| Línia 125: | Línia 142: | ||
END$$ | END$$ | ||
DELIMITER ; | DELIMITER ; | ||
| − | + | </pre> | |
| + | <pre> | ||
CALL modificarSalari_v2(); | CALL modificarSalari_v2(); | ||
| Línia 170: | Línia 188: | ||
update employees set salary=8775.00 where employee_id=123; # 6500.00 | update employees set salary=8775.00 where employee_id=123; # 6500.00 | ||
</pre> | </pre> | ||
| + | =Tasques a realitzar= | ||
| + | (TBD) | ||
| + | =Entrega= | ||
| + | (TBD) | ||
{{Autor}}, abril 2022 | {{Autor}}, abril 2022 | ||
Revisió del 18:26, 13 abr 2022
Contingut
Introducció
Si volem incrementar el salari de tots els empleats un 10% és tan senzill com fer:
mysql> update employees set salary=salary*1.1;
En el cas de què vulguis fer aquest update i tornar a deixar la taula com estava, ho pots fer de la següent manera:
start transaction; #comencem una transacció update employees set salary=salary*1.1; #fem el update select * from employees; #veiem els canvis que s'han realitzat rollback; #tirem endarrere els canvis
Ara bé, si volem incrementar el salari un 10% els empleats dels empleats que tenen un fill no és tan senzill. Podem intentar:
$ update employees set salary=salary*1.1 where employee_id IN (select e.employee_id from employees e INNER JOIN dependents d USING(employee_id)); ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause
És a dir, no podem fer el update en el mateix moment que estem fent una select amb la mateixa taula. Això és així per seguretat, i no ho podem fer pel mateix motiu que no podem fer cursors actualitzables. A MySQL els cursors són read-only.
El que sí que funciona és fer una vista, i actualitzar sobre la vista:
create view empleats_amb_fills as select e.employee_id,e.first_name,e.last_name,salary from employees e INNER JOIN dependents d USING(employee_id); update empleats_amb_fills set salary=salary*1.1; select * from empleats_amb_fills
A Oracle existeixen els CURSOR FOR UPDATE, però a MySQL els cursors són read only. A Oracle podem estudiar el següent exemple:
En aquest exemple (llenguatge PL/SQL d'Oracle) llegim en un cursor la taula customers, i a mida que recorrem (LOOP) els resultats actualitzem el camp credit_limit. Això no ho podem fer a MySQL. Anem a veure una alternativa per poder fer-ho.
Solució alternativa per fer cursors actualitzables
Per solucionar aquest tema, el que farem és generar les sentències udpate que ens permetran actualitzar els valors, a posteriori. Generarem una llista de comandes update, que les volcarem per la pantalla o a un fitxer.
Treballem amb la base de dades HR. Comencem per un exemple senzill i després farem un exemple més complicat.
NOTA: per tal de visualitzar de forma clara el resultat, convé utilitzar l'opció silent: -ss
$ mysql -u alumne -pkeiL2lai -ss HR
Primer exemple
procediment modificarSalari:
DROP PROCEDURE IF EXISTS modificarSalari;
DELIMITER $$
CREATE PROCEDURE modificarSalari (
)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE vemployee_id INT;
DECLARE vsalary DECIMAL(8,2);
DEClARE curEmpleats CURSOR FOR select e.employee_id,salary from employees e INNER JOIN dependents d USING(employee_id);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN curEmpleats;
getEmpleats: LOOP
FETCH curEmpleats INTO vemployee_id, vsalary;
IF finished = 1 THEN
LEAVE getEmpleats;
END IF;
SELECT CONCAT('update employees set salary=',vsalary*1.1,' where employee_id=',vemployee_id,';');
END LOOP getEmpleats;
CLOSE curEmpleats;
END$$
DELIMITER ;
CALL modificarSalari();
I amb això el que obtenim és una llista de sentències SQL que ens serveixen per actualitzar el salari dels empleats que tenen fills:
update employees set salary=26400.000 where employee_id=100; update employees set salary=18700.000 where employee_id=101; update employees set salary=18700.000 where employee_id=102; update employees set salary=9900.000 where employee_id=103; update employees set salary=6600.000 where employee_id=104; update employees set salary=5280.000 where employee_id=105; update employees set salary=5280.000 where employee_id=106; ...
Segon exemple
Ara el que farem és implementar una regla més complicada: incrementarem el sou d'un empleat seguint aquestes dues regles: 1) s'incrementa el sou amb el % relatiu de la diferència entre el max_salary i el min_salary del job al qual pertany l'empleat. Per exemple, per al cas del job ''Stock Manager'', la diferència entre 8550-5500 és 3000, i s'incrementa el sou en un (3000/8500)*100 = 35,29%. 2) sobre aquest valor, s'incrementa el sou un 10% si l'empleat té un fill. Primer de tot treballem la consulta que ens dona informació sobre el salari min i màx del job de l'empleat, i de si aquest té o no té fills: <pre> select e.employee_id, e.job_id, e.salary, min_salary, max_salary,d.employee_id from employees e INNER JOIN jobs j USING(job_id) LEFT OUTER JOIN dependents d ON e.employee_id=d.employee_id;
proceciment modificarSalari_v2:
DROP PROCEDURE IF EXISTS modificarSalari_v2;
DELIMITER $$
CREATE PROCEDURE modificarSalari_v2 (
)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE vemployee_id INT;
DECLARE vsalary DECIMAL(8,2);
DECLARE vsalary_new DECIMAL(8,2);
DECLARE vjob_salary_min DECIMAL(8,2);
DECLARE vjob_salary_max DECIMAL(8,2);
DECLARE vfill DECIMAL(8,2);
DECLARE inc DECIMAL (4,2);
DEClARE curEmpleats CURSOR FOR select e.employee_id, e.salary, min_salary, max_salary,d.employee_id from employees e INNER JOIN jobs j USING(job_id) LEFT OUTER JOIN dependents d ON e.employee_id=d.employee_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN curEmpleats;
getEmpleats: LOOP
FETCH curEmpleats INTO vemployee_id, vsalary, vjob_salary_min, vjob_salary_max, vfill;
IF finished = 1 THEN
LEAVE getEmpleats;
END IF;
SET inc = (vjob_salary_max-vjob_salary_min)/vjob_salary_max;
SET vsalary_new = vsalary*(1+inc);
IF vfill IS NOT NULL THEN
SET vsalary_new = vsalary_new*1.1;
END IF;
#SELECT vsalary;
SELECT CONCAT('update employees set salary=',vsalary_new,' where employee_id=',vemployee_id,'; # ', vsalary);
END LOOP getEmpleats;
CLOSE curEmpleats;
END$$
DELIMITER ;
CALL modificarSalari_v2(); update employees set salary=13968.90 where employee_id=206; # 8300.00 update employees set salary=19668.00 where employee_id=205; # 12000.00 update employees set salary=7260.00 where employee_id=200; # 4400.00 update employees set salary=39600.00 where employee_id=100; # 24000.00 update employees set salary=28050.00 where employee_id=101; # 17000.00 update employees set salary=28050.00 where employee_id=102; # 17000.00 update employees set salary=15147.00 where employee_id=109; # 9000.00 update employees set salary=13800.60 where employee_id=110; # 8200.00 update employees set salary=12959.10 where employee_id=111; # 7700.00 update employees set salary=13127.40 where employee_id=112; # 7800.00 update employees set salary=11612.70 where employee_id=113; # 6900.00 update employees set salary=19668.00 where employee_id=108; # 12000.00 update employees set salary=11154.00 where employee_id=203; # 6500.00 update employees set salary=15840.00 where employee_id=103; # 9000.00 update employees set salary=10560.00 where employee_id=104; # 6000.00 update employees set salary=8448.00 where employee_id=105; # 4800.00 update employees set salary=8448.00 where employee_id=106; # 4800.00 update employees set salary=7392.00 where employee_id=107; # 4200.00 update employees set salary=20020.00 where employee_id=201; # 13000.00 update employees set salary=10296.00 where employee_id=202; # 6000.00 update employees set salary=17270.00 where employee_id=204; # 10000.00 update employees set salary=5285.50 where employee_id=115; # 3100.00 update employees set salary=4944.50 where employee_id=116; # 2900.00 update employees set salary=4774.00 where employee_id=117; # 2800.00 update employees set salary=4433.00 where employee_id=118; # 2600.00 update employees set salary=4262.50 where employee_id=119; # 2500.00 update employees set salary=17787.00 where employee_id=114; # 11000.00 update employees set salary=23100.00 where employee_id=145; # 14000.00 update employees set salary=22275.00 where employee_id=146; # 13500.00 update employees set salary=14190.00 where employee_id=176; # 8600.00 update employees set salary=12600.00 where employee_id=177; # 8400.00 update employees set salary=10500.00 where employee_id=178; # 7000.00 update employees set salary=9300.00 where employee_id=179; # 6200.00 update employees set salary=6200.00 where employee_id=192; # 4000.00 update employees set salary=6045.00 where employee_id=193; # 3900.00 update employees set salary=4320.00 where employee_id=126; # 2700.00 update employees set salary=10800.00 where employee_id=120; # 8000.00 update employees set salary=11070.00 where employee_id=121; # 8200.00 update employees set salary=10665.00 where employee_id=122; # 7900.00 update employees set salary=8775.00 where employee_id=123; # 6500.00
Tasques a realitzar
(TBD)
Entrega
(TBD)
creat per Joan Quintana Compte, abril 2022
