MySQL: read-only cursors

De wikijoan
La revisió el 14:42, 13 abr 2022 per Joan (discussió | contribucions) (Es crea la pàgina amb «<pre> Si volem incrementar el salari de tots els empleats un 10% és tan senzill com fer: update view set salary=salary*1.1; Ara bé, si volem incrementar el salari...».)
(dif) ← Versió més antiga | Versió actual (dif) | Versió més nova → (dif)
Salta a la navegació Salta a la cerca
Si volem incrementar el salari de tots els empleats un 10% és tan senzill com fer:
update view set salary=salary*1.1;

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

Això 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;


A Oracle existeixen els CURSOR FOR UPDATE, però a MySQL els cursors són ''read only''. Per exemple, a Oracle tenim el següent exemple:
*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.

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 volcaré 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.



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();

'''NOTA''': per tal de visualitzar de forma clara el resultat, convé utilitzar l'opció ''silent'': -ss
<pre>
$ mysql -u alumne -pkeiL2lai -ss HR

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;
...

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:

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;


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


creat per Joan Quintana Compte, abril 2022