8768 sujets

Développement web côté serveur, CMS

Comment définir une procédure en SQL ?

Bonjour à tous

Comme je l'ai déjà dit, malgré mon âge avancé je suis un total débutant en SQL, langage dont j'étais parvenu à me passer jusque récemment.
Je n'ai encore jamais écrit de procédure stockée et j'ai besoin de votre aide pour ma première procédure.

Mon problème est de trier des adresses postales en "Zones", à savoir
01-paris
02-idf
03-other
99-abroad

J'ai actuellement un code PHP que j'exécute sur les résultats d'une requête, qui contient la fonction suivante

function getArea($zip, $country) {
  if(is_null($country)) $country = 'France';
  if($country != 'France') return '99-abroad';
  if(strlen($zip) != 5) return '03-other';
  switch(substr($zip, 0, 2)) {
    case '75':
      return '01-paris';
    case '77': case '78': case '91': case '92': case '93': case '94': case '95':
      return '02-idf';
    default: 
      return '03-other';
  }
}


Je préfèrerais écrire une fonction similaire en SQL de façon à pouvoir écrire une requête

SELECT addr.street, addr.town, addr.zip, addr.country, 
	     GetArea(addr.zip, addr.country) as area
	FROM Addresses
	ORDER BY area, zip
;

ce qui me permettrait de récupérer les données dans l'ordre qui m'intéresse, plutôt que de devoir faire ce tri en PHP.

Je sais que ce doit être possible, j'ai essayé de le faire mais tous mes essais se traduisent par des messages "Erreur de syntaxe" sans explication sur ces erreurs.

Merci de votre aide.
Modifié par PapyJP (15 Feb 2022 - 15:02)
Salut,

en solution qui répond au problème (mais pas à la demande de procédure Smiley sweatdrop ), tu dois pouvoir juste crée une vue qui va contenir ce qu'il faut : 


create view Addresses_with_area as 
select street, town ,zip , country , "99-abroad" area from Addresses where country !="France" 
union 
select street, town ,zip , country , "01-paris" from Addresses where country="France" and length(zip)=5 and zip like "75%"  
union 
select street, town ,zip , country , "02-idf" from Addresses where country="France" and length(zip)=5 and (zip like "77%" or zip like "78%" or zip like "91%" or  zip like "92%" or zip like "93%" or zip like "94%" or zip like "95%" ) 
union 
select street, town ,zip , country , "03-other" from Addresses where country="France" and (length(zip)!=5 or (length(zip)=5 and not (zip like "75%" or zip like "77%" or zip like "78%" or zip like "91%" or  zip like "92%" or zip like "93%" or zip like "94%" or zip like "95%")));


Après je dois admettre que j'ai un peu des doutes niveaux performances, c'est probablement plus efficace de le calculer une seule fois et de le stocker pour de bon (du coup il doit falloir une colonne de plus et des triggers sur insert et update pour que la valeur reste tout le temps bonne Smiley bawling )
Merci de ta réponse
Entre temps j'ai trouvé une expression du même genre, que je vais utiliser faute de mieux, mais j'aurais bien aimé avoir l'occasion d'aborder par ce biais les procédures stockées.
Une colonne de plus n'est pas un problème, mais les triggers sont un autre domaine où je n'ai aucune compétence Smiley rolleyes

Comme pour le moment je vais garder mon expression complexe, car je n'ai pas (encore) eu besoin de calculer les zones, avoir cette expression dans la seule requête qui en n'a besoin n'es pas un problème.

Voici ce que j'ai écrit

SELECT street, zip, town, country,
		CASE
			WHEN country IS NOT NULL AND country != 'France' THEN '99-abroad'
			WHEN zip LIKE('75%') THEN '01-paris'
			WHEN zip RLIKE('^(77|78|91|92|93|94|95)\d\d\d$') THEN '02-idf'
			ELSE '03-other'
		END
		as region
	FROM Addresses
	ORDER BY region, zip


EDIT : sauf que mon RLIKE ne marche pas, je vais devoir écrire autant de lignes que de départements en Île de France Smiley fache
Modifié par PapyJP (15 Feb 2022 - 16:05)
Pour ton Rlike mysql ne supporte pas le \d j'ai l'impression.

Cela fonctionne en remplaçant par [0-9] (et j'ai mis un {3} plutôt que d'écrire 3 fois [0-9] ) : 

WHEN zip RLIKE('^(77|78|91|92|93|94|95)[0-9]{3})

Modifié par Mathieuu (15 Feb 2022 - 16:23)
Merci
C’est vrai que les expressions régulières en SQL sont limitées, j’avais oublié
Pour les triggers c'est un peu plus pénible à faire, il faut préciser si on agit avant ou après l'insertion / la mise à jour et cela rajoute des "variables" (NEW / OLD) qui correspondent à la nouvelle valeur que l'on va mettre ou l'ancienne que l'on va écraser.
De plus il faut bien tester pour être sur que l'on obtient bien ce que l'on veut parce que cela peut vite remplir n'importe comment si on a oublié des cas Smiley ohwell

Du coup un alter table pour rajouter la région : 
alter table Addresses add column region varchar(10);


Puis un trigger pour mettre à jour la région automatiquement lorsque l'on ajoute une ligne.
On commence par changer le symbole qui indique la ligne est fini vu qu'il ne faut plus qu'il s'arrete à chaque point virgule pour que l'on puisse écrire le trigger
delimiter $$

Puis on crée notre trigger :
CREATE TRIGGER BeforeInsertAddressesSetNEWRegion BEFORE INSERT ON Addresses FOR EACH ROW  BEGIN IF NEW.country IS NOT NULL AND NEW.country != 'France' THEN SET NEW.region = '99-abroad'; ELSE IF NEW.zip LIKE('75%') THEN SET NEW.region = '01-paris'; ELSE IF NEW.zip
RLIKE('^(77|78|91|92|93|94|95)[0-9]{3}$') THEN SET NEW.region =  '02-idf'; ELSE SET NEW.region = '03-other'; END IF; END IF;  END IF; END$$


Mini Edit : le même code indenté pour la lisibilité : 
CREATE TRIGGER BeforeInsertAddressesSetNEWRegion BEFORE INSERT ON Addresses 
FOR EACH ROW  
BEGIN 
    IF NEW.country IS NOT NULL AND NEW.country != 'France' 
    THEN SET NEW.region = '99-abroad'; 
    ELSE 
        IF NEW.zip LIKE('75%') 
        THEN SET NEW.region = '01-paris'; 
        ELSE 
            IF NEW.zip RLIKE('^(77|78|91|92|93|94|95)[0-9]{3}$') 
            THEN SET NEW.region =  '02-idf'; 
            ELSE SET NEW.region = '03-other'; 
            END IF; 
        END IF;  
    END IF; 
END$$


Ensuite on fait la même chose pour le update (je pensais qu'on pouvait faire un "BEFORE INSERT OR UPDATE ON Addresses" mais ça m'envoie boulet Smiley bawling )

CREATE TRIGGER BeforeUpdateAddressesSetNEWRegion BEFORE UPDATE ON Addresses FOR EACH ROW  BEGIN IF NEW.country IS NOT NULL AND NEW.country != 'France' THEN SET NEW.region = '99-abroad'; ELSE IF NEW.zip LIKE('75%') THEN SET NEW.region = '01-paris'; ELSE IF NEW.zip
RLIKE('^(77|78|91|92|93|94|95)[0-9]{3}$') THEN SET NEW.region =  '02-idf'; ELSE SET NEW.region = '03-other'; END IF; END IF;  END IF; END$$


On re bascule sur le delimiter habituel (sinon il faut que tu termines tes commandes par $$)
delimiter ;


A priori cela met bien la bonne valeur lorsque l'on fait des inserts, et pour remplir la colonne qui est vide pour les lignes deja existantes avant le atler table, on peut faire un update qui ne fait rien sur toutes les lignes pour déclencher le trigger partout : 
update Addresses SET zip=zip

Modifié par Mathieuu (16 Feb 2022 - 14:44)
Merci de cet éclairage sur un domaine qui était complètement obscur à mes yeux.

Remarque: compte tenu que zip contient un code postal vérifié auparavant comme "légal", et que l'on a bien vérifié auparavant que c'était bien en France, RLIKE('^(77|78|91|92|93|94|95)) est suffisant.
A priori on est pas sur d’être en France, potentiellement on peut aussi être dans une country null
Mathieuu a écrit :
A priori on est pas sur d’être en France, potentiellement on peut aussi être dans une country null

Sauf bug ailleurs dans la gestion des tables de la base, ce ne devrait pas être le cas.
Je vais ouvrir un autre fil de discussion sur la gestion de ces tables.
Sujet clos
Aller à