754 |
raphael |
1 |
/*
|
755 |
raphael |
2 |
Mise à jour de réferentiels NULL ou vides pour les observations orphelines (sans nom_ret_nn)
|
754 |
raphael |
3 |
2722 observations trouvées au 2013/07/19
|
|
|
4 |
*/
|
|
|
5 |
|
|
|
6 |
DROP PROCEDURE IF EXISTS getNomSci;
|
|
|
7 |
DROP PROCEDURE IF EXISTS getNomSciCount;
|
|
|
8 |
DROP PROCEDURE IF EXISTS getNomSciAuteur;
|
|
|
9 |
DROP PROCEDURE IF EXISTS getNomSciAuteurCount;
|
|
|
10 |
DROP PROCEDURE IF EXISTS cur;
|
|
|
11 |
|
|
|
12 |
delimiter |
|
|
|
13 |
|
|
|
14 |
-- obtient le nombre de matches sur nom_sel = nom_sci
|
|
|
15 |
CREATE PROCEDURE getNomSciCount(IN _nom varchar(500), OUT param1 INT)
|
|
|
16 |
BEGIN
|
816 |
raphael |
17 |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE nom_sci = _nom) AS req;
|
754 |
raphael |
18 |
END
|
|
|
19 |
|
|
|
|
20 |
-- retourne les paramètres d'une match
|
|
|
21 |
CREATE PROCEDURE getNomSci(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255))
|
|
|
22 |
BEGIN
|
|
|
23 |
SELECT * INTO param1, param2, param3, param4, param5 FROM
|
816 |
raphael |
24 |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE nom_sci = _nom
|
754 |
raphael |
25 |
UNION ALL
|
816 |
raphael |
26 |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE nom_sci = _nom) AS req;
|
754 |
raphael |
27 |
END
|
|
|
28 |
|
|
|
|
29 |
|
|
|
30 |
-- obtient le nombre de matches sur nom_sel = CONCAT(nom_sci, " ", auteur)
|
|
|
31 |
-- quasiment identique à ci-dessus, sauf que nous excluons de la recherche de bdtfx et bdtxa les nom dont le nom d'auteur est ''
|
|
|
32 |
CREATE PROCEDURE getNomSciAuteurCount(IN _nom varchar(500), OUT param1 INT)
|
|
|
33 |
BEGIN
|
816 |
raphael |
34 |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req;
|
754 |
raphael |
35 |
END
|
|
|
36 |
|
|
|
|
37 |
-- retourne les paramètres d'une match
|
|
|
38 |
CREATE PROCEDURE getNomSciAuteur(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255))
|
|
|
39 |
BEGIN
|
|
|
40 |
SELECT * INTO param1, param2, param3, param4, param5 FROM
|
816 |
raphael |
41 |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != ''
|
754 |
raphael |
42 |
UNION ALL
|
816 |
raphael |
43 |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req;
|
754 |
raphael |
44 |
END
|
|
|
45 |
|
|
|
|
46 |
|
|
|
47 |
CREATE PROCEDURE cur()
|
|
|
48 |
BEGIN
|
|
|
49 |
DECLARE done INT DEFAULT 0;
|
|
|
50 |
DECLARE subst INT DEFAULT 0;
|
|
|
51 |
DECLARE _id_observation bigint(20) DEFAULT 0;
|
|
|
52 |
DECLARE _nom varchar(255) DEFAULT NULL;
|
|
|
53 |
|
|
|
54 |
-- la requête principale de sélection des observations à mettre à jour
|
816 |
raphael |
55 |
DECLARE cur1 CURSOR FOR SELECT id_observation, nom_sel FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NULL; -- 78149
|
754 |
raphael |
56 |
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
|
|
57 |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
|
|
|
58 |
|
|
|
59 |
OPEN cur1;
|
|
|
60 |
|
|
|
61 |
REPEAT
|
|
|
62 |
FETCH cur1 INTO _id_observation, _nom;
|
|
|
63 |
CALL getNomSciCount(_nom, @a);
|
|
|
64 |
-- SELECT _id_observation, _nom, @a;
|
|
|
65 |
IF @a = 1 THEN
|
|
|
66 |
CALL getNomSci(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille);
|
|
|
67 |
SELECT "updb: getNomSci", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille;
|
816 |
raphael |
68 |
UPDATE `BASEEDIT`.`cel_obs` c SET
|
754 |
raphael |
69 |
c.nom_referentiel = @_ref,
|
|
|
70 |
c.nom_ret = @_nom,
|
|
|
71 |
c.nom_ret_nn = @_num_nom,
|
|
|
72 |
c.nt = @_num_tax,
|
|
|
73 |
c.famille = @_famille
|
|
|
74 |
WHERE id_observation = _id_observation;
|
|
|
75 |
SET subst = subst + 1;
|
|
|
76 |
/* ELSE
|
|
|
77 |
CALL getNomSciAuteurCount(_nom, @a);
|
|
|
78 |
IF @a = 1 THEN
|
|
|
79 |
CALL getNomSciAuteur(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille);
|
|
|
80 |
SELECT "updb: getNomSciAuteur", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille;
|
816 |
raphael |
81 |
UPDATE `BASEEDIT`.`cel_obs` c SET
|
754 |
raphael |
82 |
c.nom_referentiel = @_ref,
|
|
|
83 |
c.nom_ret = @_nom,
|
|
|
84 |
c.nom_ret_nn = @_num_nom,
|
|
|
85 |
c.nt = @_num_tax,
|
|
|
86 |
c.famille = @_famille
|
|
|
87 |
WHERE id_observation = _id_observation;
|
|
|
88 |
SET subst = subst + 1;
|
|
|
89 |
END IF;*/
|
|
|
90 |
END IF;
|
|
|
91 |
UNTIL done END REPEAT;
|
|
|
92 |
select subst AS 'nombre de mises à jour effectuées';
|
|
|
93 |
CLOSE cur1;
|
|
|
94 |
END
|
|
|
95 |
|
|
|
96 |
|
|
|
97 |
|
|
|
98 |
|
|
|
|
99 |
|
|
|
100 |
delimiter ;
|