Fonctions stockées

[30 min]

À la fin de chaque année, le fournisseur des produits du projet MediaTek demande à l'association un inventaire sur les produits vendus. Cet inventaire lui est envoyé par e-mail pour analyse et traitement. L'information demandée est la suivante : donner pour chaque produit, sa désignation, son prix actuel, et une mention sur la demande :

  • demande "forte" si le nombre d'articles vendus dépasse 15,

  • demande "moyenne" si le nombre d'articles vendus est compris entre 11 et 15,

  • et demande "faible" sinon.

Question

Écrire une fonction stockée qui retourne la valeur de demande (forte, moyenne, faible) en fonction d'un numéro de produit.

Indice

Pensez à utiliser l'instruction show errors en fin de block PL/SQL pour avoir en retour les éventuelles erreurs de complication.

Indice

Utilisez la trame suivante pour vous aider.

1
CREATE OR REPLACE FUNCTION fDemande (num_produit number) RETURN varchar
2
IS
3
  qte_vendue NUMBER;
4
BEGIN
5
  SELECT ... INTO qte_vendue
6
  FROM ...
7
  WHERE ...
8
  IF ... THEN
9
    RETURN ('forte');
10
    ...
11
  ELSE
12
    ...
13
  END IF;
14
END fDemande;
15
/
16
SHOW ERRORS

Solution

Solution avec un IF ... ELSIF ...
1
CREATE OR REPLACE FUNCTION fDemande (num_produit in number) RETURN varchar
2
IS
3
  qte_vendue NUMBER;
4
BEGIN
5
  SELECT sum(lf.qte) INTO qte_vendue
6
  FROM ligne_fact lf
7
  WHERE lf.produit=num_produit;
8
  IF (qte_vendue > 15) THEN
9
    RETURN ('forte');
10
  ELSIF (qte_vendue BETWEEN 11 AND 15) THEN
11
    RETURN ('moyenne');
12
  ELSE
13
    RETURN('faible');
14
  END IF;
15
END fDemande;
16
/
17
SHOW ERRORS
Solution alternative avec un CASE
1
CREATE OR REPLACE FUNCTION fDemande (num_produit in number) RETURN varchar
2
IS
3
  qte_vendue NUMBER;
4
BEGIN
5
  SELECT sum(lf.qte) INTO qte_vendue
6
  FROM ligne_fact lf
7
  WHERE lf.produit=num_produit;
8
  CASE
9
    WHEN qte_vendue > 15 THEN
10
      RETURN ('forte');
11
    WHEN
12
      qte_vendue BETWEEN 11 AND 15 THEN RETURN ('moyenne');
13
    ELSE
14
      RETURN('faible');
15
  END CASE;
16
END fDemande;
17
/
18
SHOW ERRORS

Question

Écrire une requête SELECT qui utilise cette fonction pour présenter la liste des produits avec la demande.

Indice

1
SELECT ..., f(...), ... FROM ... WHERE ...

Solution

1
SELECT num, designation, fDemande(num) FROM produit;

Question

Écrire un bloc PL/SQL permettant d'obtenir l'état suivant sous la forme d'un fichier texte inventaire.txt :

1
1(Matrix) : moyenne
2
2(Seigneur des anneaux) : forte
3
3(Mistral Gagnant) : faible

Indice

1
On utilisera la fonction de concaténation ||

Solution

1
SET heading off
2
SPOOL /tmp/inventaire.txt
3
SELECT num || '(' || designation || ') : ' || fDemande(num) FROM produit;
4
SPOOL OFF