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
38
39
40
41
42
43
|
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE proc_logem_quart_plus150
(p_nomquart IN Quartier.nomquart%TYPE,
p_nomcommune IN Commune.nomcom%TYPE)
IS
v_numero Logement.numero%TYPE;
v_nomquart Quartier.nomquart%TYPE;
v_cp Commune.cp%TYPE;
v_nomcom Commune.nomcom%TYPE;
CURSOR Cur_Logement IS
SELECT
L.numero, Q.nomquart, C.cp, C.nomcom
FROM
Commune C, Quartier Q, Logement L
WHERE
NOT EXISTS (SELECT * FROM Contrat WHERE idlog = L.idlog)
AND L.idquart = Q.idquart
AND Q.idcom = C.idcom
AND p_nomquart = Q.nomquart
AND p_nomcom = C.nomcom
AND L.superficie > 150;
BEGIN
OPEN Cur_Logement;
FETCH Cur_Logement INTO v_numero, v_nomquart, v_cp, v_nomcom ;
WHILE cur_Logement%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_numero||' '||
v_nomquart||' '||
v_cp||' '||
v_nomcom);
FETCH Cur_Logement INTO v_numero, v_nomquart, v_cp, v_nomcom; |
