Curseurs

[30 min]

Afin d'affiner l'analyse du profil de chaque client, le service marketing souhaite avoir un état pour chaque client avec le nombre de ses factures et son chiffre d'affaire total.

Question

Écrivez deux fonctions stockées qui prendront en argument un numéro de client, et retourneront sous forme de number le nombre de factures et le chiffre d'affaire, respectivement.

Indice

1
CREATE OR REPLACE FUNCTION nbfact(num_client NUMBER) RETURN NUMBER
2
...
3
END nbfact;
4
/
5
SHOW ERRORS
6
7
CREATE OR REPLACE FUNCTION ca (num_client NUMBER) RETURN NUMBER
8
...
9
END ca;
10
/
11
SHOW ERRORS
Solution
1
CREATE OR REPLACE FUNCTION nbfact(num_client NUMBER) RETURN NUMBER
2
IS
3
  nb NUMBER;
4
BEGIN
5
  SELECT COUNT(f.num) INTO nb
6
  FROM facture f
7
  WHERE f.client=num_client;
8
9
  RETURN nb;
10
END nbfact;
11
/
12
SHOW ERRORS
13
14
CREATE OR REPLACE FUNCTION ca (num_client NUMBER) RETURN NUMBER
15
IS
16
  ca NUMBER;
17
BEGIN
18
  SELECT sum(p.prix*lf.qte) INTO ca
19
  FROM produit p, facture f, ligne_fact lf
20
  WHERE p.num=lf.produit
21
  AND lf.facture=f.num
22
  AND f.client=num_client;
23
24
  RETURN ca;
25
END ca;
26
/
27
SHOW ERRORS
Question
Écrivez un programme PL/SQL anonyme qui affiche pour le client numéro 1 le nombre de factures et le chiffre d'affaire.
Indice
Pensez à exécuter le "SET SERVEROUTPUT ON" avant l'exécution de "DBMS_OUTPUT.PUT_LINE".
Indice
Utlisez la syntaxe DECLARE ... BEGIN ... END pour déclarer un bloc PL/SQL anonyme.
1
DECLARE 
2
  client NUMBER; 
3
BEGIN
4
   client:=1;
5
  ...
6
END;
7
/
Solution
1
SET SERVEROUTPUT ON
2
DECLARE
3
  client NUMBER;
4
BEGIN
5
  client:=1;
6
  DBMS_OUTPUT.PUT_LINE (nbfact(1));
7
  DBMS_OUTPUT.PUT_LINE (ca(1));
8
END;
9
/
Question
Écrivez un second programme PL/SQL qui affiche pour chaque client son nombre de factures et son chiffre d'affaire sous la forme suivante :
1
Client 1
2
10 / 152.23
3
------------------
4
Client 2
5
5 / 123.34
6
------------------
7
...
Indice
Utiliser un curseur sur la table client :
1
 CURSOR c_client IS SELECT num FROM client;
Indice
Utiliser le caractère de concaténation || pour afficher plusieurs informations sur une même ligne :
1
 DBMS_OUTPUT.PUT_LINE('Client' || client.num);
Solution
1
SET SERVEROUTPUT ON
2
DECLARE
3
  CURSOR c_client IS SELECT num FROM client;
4
  num client.num%TYPE;
5
BEGIN
6
  OPEN c_client;
7
  LOOP
8
    FETCH c_client INTO num;
9
    EXIT WHEN c_client%NOTFOUND;
10
    DBMS_OUTPUT.PUT_LINE('Client' || num);
11
    DBMS_OUTPUT.PUT_LINE (nbfact(num) || '/' || ca(num));
12
    DBMS_OUTPUT.PUT_LINE('-----------------');
13
  END LOOP;
14
END;
15
/
Question
Transformez le bloc anonyme en procédure. Testez cette procédure.
Solution
1
CREATE OR REPLACE PROCEDURE pClient
2
IS
3
  CURSOR c_client IS SELECT num FROM client;
4
  num client.num%TYPE;
5
BEGIN
6
  OPEN c_client;
7
  LOOP
8
    FETCH c_client INTO num;
9
    EXIT WHEN c_client%NOTFOUND;
10
    DBMS_OUTPUT.PUT_LINE('Client' || num);
11
    DBMS_OUTPUT.PUT_LINE (nbfact(num) || '/' || ca(num));
12
    DBMS_OUTPUT.PUT_LINE('-----------------');
13
  END LOOP;
14
END;
15
/
16
17
SET SERVEROUTPUT ON
18
BEGIN
19
pClient;
20
END;
21
/