MySQL: read-only cursors - Historial de revisió

Joan: /* Entrega */

2022-05-03T14:42:29Z

Entrega

Joan: /* Tasques a realitzar */

2022-05-03T14:41:27Z

Tasques a realitzar

Joan: /* Tasques a realitzar */

2022-05-03T14:40:56Z

Tasques a realitzar

Joan: /* Tasques a realitzar */

2022-05-02T19:10:51Z

Tasques a realitzar

Joan: /* Tasques a realitzar */

2022-05-02T19:10:42Z

Tasques a realitzar

Joan: /* Introducció */

2022-05-02T19:10:27Z

Introducció

Joan: /* Segon exemple */

2022-04-13T18:27:07Z

Segon exemple

Joan a 18:26, 13 abr 2022

2022-04-13T18:26:48Z

Joan a 14:42, 13 abr 2022

2022-04-13T14:42:56Z

Joan: Es crea la pàgina amb «

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

2022-04-13T14:42:28Z

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

Pàgina nova

<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 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
</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>
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
</pre>
{{Autor}}, abril 2022

@@ Línia 269: / Línia 269: @@
 =Entrega=
-(TBD)
+Entregaràs dins d'un pdf les captures de pantalla i comentaris suficients per demostrar que has fet l'exercici de forma autònoma en el docker de la teva màquina. Dins del temps previst.
 {{Autor}}, abril 2022

@@ Línia 192: / Línia 192: @@
 =Tasques a realitzar=
-Treballaràs dins del docker (recorda d'utilitzar l'opció -ss).
+Són 4 exercicis. Treballaràs dins del docker (recorda d'utilitzar l'opció -ss).
 <pre>
 $ docker start mysql_m02bd
 $ docker exec -it mysql_m02bd mysql -u alumne -pkeiL2lai -ss HR
+</pre>
 '''1.''' En el teu docker (base de dades HR), crearàs i executaràs el segon exemple (procediment ''modificarSalari_v2''). Aquest procediment genera una llista per pantalla de les comandes (''updates'') que hauràs d'executar.

@@ Línia 192: / Línia 192: @@
 =Tasques a realitzar=
-(TBD)
+Treballaràs dins del docker (recorda d'utilitzar l'opció -ss).
 <pre>
 notee     (\t) Don't write into outfile.
 system    (\!) Execute a system shell command.
 tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
-mysql> system ls fitxer.txt
+mysql> CALL modificarSalari_v2();
-mysql> system cat fitxer.txt
+mysql> select employee_id, salary from employees where employee_id=206;
-  docker start mysql_m02bd
+Comprovem que s'ha fet el canvi:
-  history | grep docker
+<pre>
-  docker exec -it mysql_m02bd mysql -ualumne -pkeiL2lai
+mysql> select employee_id, salary from employees where employee_id=206;
-docker exec -it mysql_m02bd /bin/bash
+mysql> select employee_id, salary from employees where employee_id=206;
-https://wiki.joanillo.org/index.php/Docker_al_m%C3%B2dul_DAM-M02
+	8300.00
-dins de /etc/
+</pre>
 $ docker exec -it mysql_m02bd /bin/bash
-bash-4.4#
+bash-4.4# ls /etc/HR.sql
+</pre>
-el script SQL està a la carpeta ''src/'':
+Per importar el script farem:
-/etc/HR.sql
+<pre>
+bash-4.4# mysql -h localhost -u alumne -pkeiL2lai -D HR < /etc/HR.sql
+</pre>
-# mysql -u alumne -pkeiL2lai HR < /etc/HR.sql
+Per comprovar quin és el salari del employee_id=206:
+<pre>
-comprovem que l'emleat 206 torna a tenir el sou original (8300e)
+bash-4.4# mysql -h localhost -u alumne -pkeiL2lai -D HR -ss -e "select employee_id, salary from employees where employee_id=206"
 </pre>

@@ Línia 194: / Línia 194: @@
 (TBD)
 <pre>
 notee     (\t) Don't write into outfile.
 system    (\!) Execute a system shell command.

@@ Línia 193: / Línia 193: @@
 =Tasques a realitzar=
 (TBD)
 =Entrega=
 (TBD)
 {{Autor}}, abril 2022

@@ Línia 1: / Línia 1: @@
 =Introducció=
 Si volem incrementar el salari de tots els empleats un 10% és tan senzill com fer:
 <pre>
@@ Línia 30: / Línia 32: @@
 *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.

@@ Línia 88: / Línia 88: @@
 </pre>
 ==Segon exemple==
 Ara el que farem és implementar una regla més complicada: incrementarem el sou d'un empleat seguint aquestes dues regles:
@@ Línia 188: / Línia 187: @@
 update employees set salary=8775.00 where employee_id=123; # 6500.00
 </pre>
 =Tasques a realitzar=
 (TBD)

@@ Línia 1: / Línia 1: @@
 <pre>
-Si volem incrementar el salari de tots els empleats un 10% és tan senzill com fer:
+start transaction;                      #comencem una transacció
-update view set salary=salary*1.1;
+update employees set salary=salary*1.1; #fem el update
+select * from employees;                #veiem els canvis que s'han realitzat
 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>
 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:
 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''. 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 volcaré per la pantalla o a un fitxer.
+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>
 DROP PROCEDURE IF EXISTS modificarSalari;
@@ Línia 54: / Línia 72: @@
 END$$
 DELIMITER ;
 </pre>
 <pre>
-$ mysql -u alumne -pkeiL2lai -ss HR
+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>
@@ Línia 74: / Línia 87: @@
 ...
 </pre>
 <pre>
 Ara el que farem és implementar una regla més complicada: incrementarem el sou d'un empleat seguint aquestes dues regles:
 ) 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%.
 ) 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'':
 DROP PROCEDURE IF EXISTS modificarSalari_v2;
@@ Línia 125: / Línia 142: @@
 END$$
 DELIMITER ;
+</pre>
 CALL modificarSalari_v2();
@@ Línia 170: / Línia 188: @@
 update employees set salary=8775.00 where employee_id=123; # 6500.00
 </pre>
 {{Autor}}, abril 2022