Les procédures

Version imprimable

Les procédures

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:

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.

14/03/2012, 14h52
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 ?

Vous voulez dire quelque chose comme ça :

Code:

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:

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

Citation:

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

Code:

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:

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:

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

Citation:

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 ?

15/03/2012, 16h40
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.

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

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

Code:

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.

16/03/2012, 11h30
Scriuiw
Essaie avec SQL+ dans ce cas
Code:

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

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

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.

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

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 :ccool:

Merci à tous pour votre aide :ccool:

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:

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 ;)

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:

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:

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.

16/03/2012, 15h16
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.
16/03/2012, 15h22
Almenor

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

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

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:

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.

16/03/2012, 16h46
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
16/03/2012, 17h47
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.

Enfin Réussi !

Merci à Tous :ccool:

Code:

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');