Discussion :

[Almenor]

Bonjour,

Débutant en SQL (1ere année en informatique de gestion) j'ai beaucoup de mal avec le SQL (bon les requêtes simple maintenant ça va, et grâce à ce forum, je maitrise enfin les trigger).

Actuellement j'ai beaucoup de mal avec les procédures.

J'ai les tables Commune, Quartier, Logement et je doit écrire une procédure qui permet d'entré un nom de commune + un nom de quartier et d'afficher tout les logements inoccupé d'une superficie de + de 150m².

Voici ce que j'ai écrit pour le moment :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE OR REPLACE PROCEDURE log_inoc_150
( v_comm IN Commune.nomcommune%TYPE,
  v_quart IN Quartier.libellequart%TYPE,
  v_rue OUT Logement.rue%TYPE,
  v_num OUT Logement.numero%TYPE,
  v_bp OUT Logement.bp%TYPE)
IS
BEGIN
    SELECT rue,numero,bp INTO v_rue,v_num,v_bp
    FROM Logement,Commune,Quartier WHERE v_comm=nomcommune AND v_quart=libellequart AND superficie>150 
         AND idquartier IN (SELECT idquartier FROM Quartier,Logement WHERE idquartier=idquart
         AND idlogement NOT IN (SELECT idlogement FROM Logement,Contrat WHERE idlogement=idlog));
END log_inoc_150;
/

On m'a parlé d'une boucle mais je ne vois pas trop où et comment la mettre et pourquoi...

Merci d'avance a toute personne qui pourra m'éclairer sur les procédures.

[mnitu]

Ajoutez un paramètre en entrée de votre procédure pour passer la superficie et modifiez votre procédure pour prendre en compte ce paramètre. Testez votre procédure avec la valeur 0 (en supposant que vos tables contiennent des données). Que se passe-t-il ?

[Almenor]

Vous voulez dire quelque chose comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE PROCEDURE log_inoc_150
( v_comm IN Commune.nomcommune%TYPE,
  v_quart IN Quartier.libellequart%TYPE,
  v_sup IN Logement.superficie%TYPE,
  v_rue OUT Logement.rue%TYPE,
  v_num OUT Logement.numero%TYPE,
  v_bp OUT Logement.bp%TYPE)
IS
BEGIN
    SELECT rue,numero,bp INTO v_rue,v_num,v_bp
    FROM Logement,Commune,Quartier WHERE v_comm=nomcommune AND v_quart=libellequart 
         AND idquartier IN (SELECT idquartier FROM Quartier,Logement WHERE idquartier=idquart
         AND idlogement NOT IN (SELECT idlogement FROM Logement,Contrat WHERE idlogement=idlog));
END log_inoc_150;
/

La procédure est bien crée, par contre quand j'essaye de l'executer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
BEGIN
log_inoc_150('Charleroi','Sud',0);
END;

ORA-06550: Ligne 2, colonne 1 :
PLS-00306: numéro ou types d'arguments erronés dans appel à 'LOG_INOC_150'
ORA-06550: Ligne 2, colonne 1 :
PL/SQL: Statement ignored

[Scriuiw]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE OR REPLACE PROCEDURE log_inoc_150
( v_comm IN Commune.nomcommune%TYPE,
  v_quart IN Quartier.libellequart%TYPE,
  v_sup IN Logement.superficie%TYPE,
  v_rue OUT Logement.rue%TYPE,
  v_num OUT Logement.numero%TYPE,
  v_bp OUT Logement.bp%TYPE)

- 3 paramètres entrées
- 3 paramètres sorties

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
BEGIN
log_inoc_150('Charleroi','Sud',0);
END;

- 3 paramètres entrées
- 0 paramètres sorties

A votre avis que se passe t-il avec un code comme celui-ci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
BEGIN
    SELECT rue,numero,bp INTO v_rue,v_num,v_bp
    FROM Logement,Commune,Quartier WHERE v_comm=nomcommune AND v_quart=libellequart AND superficie>150 
         AND idquartier IN (SELECT idquartier FROM Quartier,Logement WHERE idquartier=idquart
         AND idlogement NOT IN (SELECT idlogement FROM Logement,Contrat WHERE idlogement=idlog));
END log_inoc_150;

si la requête ramène :
- Aucune ligne
- Une ligne
- Plusieurs lignes

je doit écrire une procédure qui permet d'entré un nom de commune + un nom de quartier et d'afficher tout les logements inoccupé d'une superficie de + de 150m².

De plus, une telle procédure ne ramènera (au mieux) qu'un seul enregistrement (ou logement) or il vous faut afficher tous les logements répondants au critères de sélection

Donc la demande est-elle d'avoir une procédure qui renvoi le ou les logements disponibles OU d'afficher les logements disponibles ?

[Almenor]

Bon, je replonge dans mes cours car là j'ai comme l'impression d'être bien largué... ensuite je repasserai par ici pour un complément d'information ou pour clôturer.

[Almenor]

J'ai relu un peu mes cours, effectivement il manque quelques petites choses.

Voilà ce que j'ai écrit pour le moment :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE OR REPLACE PROCEDURE log_inoc_150
( v_comm IN Commune.nomcommune%TYPE,
  v_quart IN Quartier.libellequart%TYPE,
  v_rue OUT Logement.rue%TYPE,
  v_num OUT Logement.numero%TYPE,
  v_bp OUT Logement.bp%TYPE)
IS
BEGIN
    SELECT rue,numero,bp INTO v_rue,v_num,v_bp
    FROM Logement,Commune,Quartier WHERE v_comm=nomcommune AND v_quart=libellequart AND superficie>150 
         AND idquartier IN (SELECT idquartier FROM Quartier,Logement WHERE idquartier=idquart
         AND idlogement NOT IN (SELECT idlogement FROM Logement,Contrat WHERE idlogement=idlog));
END log_inoc_150;
/
 
VARIABLE g_rue VARCHAR2(30);
VARIABLE g_num INTEGER;
VARIABLE g_bp VARCHAR2(4);
 
EXECUTE log_inoc_150('Aiseau','Sud',:g_rue,:g_num,:g_bp);
PRINT g_rue g_num g_bp;

Mais ! Problème ! Oracle Apex ne reconnais pas les mot clé VARIABLE, EXECUTE et PRINT... Je ne sais donc pas tester.

[Scriuiw]

Essaie avec SQL+ dans ce cas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SQL> var num number;
SQL> exec :num:=6;
 
Procédure PL/SQL terminée avec succès.
 
SQL> print :num;
 
       NUM
----------
         6

Sinon pour t'avancer un peu, je pense qu'il te faudrait revoir les notions de curseur, boucle et dbms_output

[sweetasnz]

Bonjour,
je te copie une de mes procédures (simple) avec l'appel et l'affichage(à lancer en sqlplus):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE OR REPLACE PROCEDURE getnextval(idval OUT number, nomseq varchar2) IS
BEGIN
execute immediate 'SELECT '||nomseq||'.NEXTVAL FROM DUAL' INTO idval;
END;
/
-- appel :
SET serveroutput ON size 100000
 
declare
idval number;
begin
getnextval(idval,'ma_seq');
dbms_output.put_line(idval);
end;
/

ça pourra peut être t'aider.

[Scriuiw]

Et pour t'aider dans l'exercice du week end, la mise en oeuvre d'une boucle simple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
set serveroutput on
DECLARE
 
CURSOR C_Rows IS
SELECT	1 AS Num FROM DUAL
UNION ALL
SELECT	2 AS Num FROM DUAL
UNION ALL
SELECT	3 AS Num FROM DUAL;
 
BEGIN
  dbms_output.put_line('Debut');
  FOR i IN C_Rows LOOP
    dbms_output.put_line('Ligne '||i.Num);
  END LOOP;
  dbms_output.put_line('Fin');
END;
/

En alliant les différentes méthodes tu devrais pouvoir arriver à faire ta procédure

[Almenor]

Merci à tous pour votre aide

Il y a juste une chose que je n'arrive pas à comprendre : pourquoi une boucle ?

La question parait un peu conne, je comprends très bien que une boucle vas (très certainement) servir à aller voir tout les enregistrements afin de retourner plusieurs résultats. Là ou je m'y perds, c'est que j'ai écrit, pas plus tard qu'aujourd'hui, cette procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE OR REPLACE PROCEDURE augm_stud_comm
( v_pourcent IN Logement.loyer%TYPE,
  v_comm IN Commune.nomcommune%TYPE)
IS
BEGIN
    UPDATE Logement
    SET loyer=loyer*(1+v_pourcent) 
    WHERE typelog LIKE 'studio' 
          AND idlogement IN (SELECT Logement.idlogement 
                             FROM Logement,Quartier,Commune
                             WHERE Logement.idquart=Quartier.idquartier 
							       AND Quartier.idcom=Commune.idcommune 
							       AND Commune.nomcommune=v_comm);
END augm_stud_comm;
/
 
 
EXECUTE augm_stud_comm(0.02,'Courcelles');

Qui permet d'augmenter d'un pourcentage choisis les loyers des studio d'une commune choisie.
J'ai dans la commune "Courcelles" plusieurs studio qui ont tous été mis à jour et ce sans boucle.

Donc si quelqu'uns pouvait m'expliquer pourquoi dans le premier cas il en faut une et pourquoi dans ce dernier cas ça fonctionne sans, ça me rendrait un immense service

[Scriuiw]

Ton UPDATE est une commande ensembliste qui va donc mettre les lignes à jour correspondant à ton sélect. L'instruction met à jour la BDD mais ne renvoi aucune information.

Pour une requête SELECT nécessaire à de l'affichage il existe une contrainte en PL/SQL que tu n'as pas en SQL.

Par exemple la requête suivante en SQL passe sans encombre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  1  SELECT     num
  2  FROM
  3    (SELECT 1 AS Num FROM DUAL
  4    UNION ALL
  5    SELECT 2 AS Num FROM DUAL
  6    UNION ALL
  7*   SELECT 3 AS Num FROM DUAL)
SQL> /
 
       NUM
----------
         1
         2
         3

Si je la retranscris en PL/SQL j'obtiens une erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  1  declare
  2  resultat number(6);
  3  begin
  4  SELECT     num
  5  INTO resultat
  6  FROM
  7    (SELECT 1 AS Num FROM DUAL
  8    UNION ALL
  9    SELECT 2 AS Num FROM DUAL
 10    UNION ALL
 11    SELECT 3 AS Num FROM DUAL);
 12* end;
SQL> /
declare
*
ERREUR à la ligne 1 :
ORA-01422: l'extraction exacte ramène plus que le nombre de lignes demandé
ORA-06512: à ligne 4

Le moteur PL/SQL ne peut pas renvoyer les n résultats dans une seule variable et provoque l'erreur TOO_MANY_ROWS.
Basiquement en PL/SQL une requête de SELECT se doit de renvoyer 1 ligne, ni plus, ni moins. Dans le cas contraire tu provoques une exception (NO_DATA_FOUND ou TOO_MANY_ROWS).

C'est pourquoi si tu veux utiliser un SELECT qui renvoi plusieurs lignes il te faut passer par un curseur.

[McM]

Un update ne renvoie rien comme donnée, comme un Insert ou un Delete.

Un SELECT par contre renvoie une ou plusieurs lignes.
Dans du plsql, un SELECT est obligatoirement renvoyé vers une "variable" par la clause INTO
Il faut comprendre que le plsql n'a pas vocation d'interagir avec l'utilisateur.
Donc pour traiter plusieurs lignes ramenées par un select en plsql, il faut boucler sur ces lignes et les traiter une par une.

[Almenor]

Donc, pour parcourir les tuples pas besoin de boucle par contre pour renvoyer un résultat oui.
Je comprends, merci

sur ce, je retourne bosser sur la première procédure

[Almenor]

Voici ce que j'ai écrit entre deux coup de téléphone (le chef en réunion ça me permet d'avancer un peu :p ) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
SET SERVEROUTPUT ON --permet de rendre actives les fonctions du paquetage DBMS_OUTPUT
 
CREATE OR REPLACE PROCEDURE log_inoc_150
( v_comm IN Commune.nomcommune%TYPE,
  v_quart IN Quartier.libellequart%TYPE,
  v_rue OUT Logement.rue%TYPE,
  v_num OUT Logement.numero%TYPE,
  v_bp OUT Logement.bp%TYPE)
IS
 
DECLARE
CURSOR Cursor_logem_quart_plus150 IS
 
SELECT rue,numero,bp INTO v_rue,v_num,v_bp
FROM Logement,Commune,Quartier WHERE Commune.idcommune=Quartier.idcom AND Quartier.idquartier=Logement.idquart 
	 AND v_comm=nomcommune AND v_quart=libellequart AND superficie>150 
     AND idquartier IN (SELECT idquartier FROM Quartier,Logement WHERE idquartier=idquart
     AND idlogement NOT IN (SELECT idlogement FROM Logement,Contrat WHERE idlogement=idlog));
 
BEGIN
 
    DBMS_OUTPUT.PUT_LINE('Debut');
	FOR i IN Cursor_logem_quart_plus150 LOOP	
	    DBMS_OUTPUT.PUT_LINE('      '||i.v_rue 
		                          '      '||i.v_num 
			             '      '||i.v_bp);
    END LOOP;
	DBMS_OUTPUT.PUT_LINE('Fin');
END log_inoc_150;
/
 
VARIABLE g_rue VARCHAR2(50);
VARIABLE g_num INTEGER;
VARIABLE g_bp VARCHAR2(4);
 
EXECUTE log_inoc_150('Chatelineau','Sud',:g_rue,:g_num,:g_bp);

Ce soir j'ai cours de Langage Procédurale, si le serveur Oracle est ouvert j'essayerai de lancer la procédure pour voir, sinon demain matin j'ai SGBD donc là je pourrai tester sans problème, mais je pense (j'espère ?) ne plus être très loin de réussir cette procédure.

J'ai juste un gros doute sur l'execute... étant donné que l'on écrit les résutats dans la boucle, plus besoins de créer des variables avant l'execute ? Dans ce cas, plus besoins de spécifier les :g_rue,:g_num,:g_bp dans l'execute, mais du coup les OUT en début de code n'ont plus lieu d'être... là je coince un peu.

[Scriuiw]

Les paramètres de sorties dans ce cas sont effectivement inutiles

Après, tout dépend de l'énoncé. S'agit-il :
- D'afficher les logements
- Retourner les logements en sortie de procédure

[Almenor]

Il faut les afficher.

Mais c'est ce que fait DBMS_OUTPUT.PUT_LINE non ? Donc, à chaque tour de boucle la rue/numero/bp vas s'afficher.

[Almenor]

Enfin Réussi !

Merci à Tous

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
SET SERVEROUTPUT ON 
 
CREATE OR REPLACE PROCEDURE log_inoc_150
( v_comm IN Commune.nomcommune%TYPE,
  v_quart IN Quartier.libellequart%TYPE)
 
IS
 
CURSOR Cursor_logem_quart_plus150 IS
 
SELECT rue,numero,bp
FROM Logement,Commune,Quartier WHERE Commune.idcommune=Quartier.idcom AND Quartier.idquartier=Logement.idquart 
	 AND v_comm=nomcommune AND v_quart=libellequart AND superficie>150 
     AND idquartier IN (SELECT idquartier FROM Quartier,Logement WHERE idquartier=idquart
     AND idlogement NOT IN (SELECT idlogement FROM Logement,Contrat WHERE idlogement=idlog));
 
BEGIN
 
 
	FOR i IN Cursor_logem_quart_plus150 
    LOOP	
	    DBMS_OUTPUT.PUT_LINE (i.rue
                                                              ||' '||i.numero
                                                              ||' '||i.bp);
    END LOOP;
 
END log_inoc_150;
/
 
EXECUTE log_inoc_150 ('Courcelles','Centre');