Mostra la font per a MySQL: read-only cursors

=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>
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
</pre>
Ara bé, si  volem incrementar el salari un 10% els empleats dels empleats que tenen un fill no és tan senzill. Podem intentar:
<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
</pre>
É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:
<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;
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:
*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.
=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
</pre>
<pre>
$ mysql -u alumne -pkeiL2lai -ss HR
</pre>
==Primer exemple==
procediment ''modificarSalari'':
<pre>
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 ;
</pre>
<pre>
CALL modificarSalari();
</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:
<pre>
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;
...
</pre>
==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;
</pre>
proceciment ''modificarSalari_v2'':
<pre>
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 ;
</pre>
<pre>
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
</pre>

=Tasques a realitzar=
(TBD)
=Entrega=
(TBD)
{{Autor}}, abril 2022