Requetes de TP INFORMIX 3eme anneé

par **s.souhail** Mer 25 Oct 2006 - 18:45

Merci Farah

DATABASE Societe
main

# Requete b)
# ---------
{
DEFINE P_Employe RECORD LIKE Employe.*
DECLARE C_Employe CURSOR FOR SELECT * FROM Employe ORDER BY nume
OPEN C_Employe
WHILE (true and status=0)
FETCH C_Employe INTO P_Employe.*
IF STATUS=100 THEN EXIT WHILE
ELSE DISPLAY P_Employe.*
SLEEP 0
END IF
END WHILE
Sleep 2
CLOSE C_Employe
}

# Requete c)
# ---------
{DEFINE P_chef LIKE Employe.nome
SELECT nome INTO P_chef FROM Employe WHERE nume=
(SELECT chef FROM Employe WHERE nome='Boutayeb')
DISPLAY P_chef AT 12,12
SLEEP 2
}

# Requete d)
# ---------
{ DEFINE V_nome LIKE Employe.nome
DEFINE V_fonct LIKE Employe.fonct
DEFINE V_sal LIKE Employe.sal
declare C_Employe cursor for select nome,fonct,sal into V_nome,V_fonct,V_sal
FROM Employe
WHERE fonct=(select fonct from Employe where nome='Nasri')
OR sal>( SELECT sal FROM employe where nome='Boutayeb')
#ORDER BY sal
open C_Employe
while true
FETCH C_Employe into V_nome, V_fonct, V_sal
IF status=100 THEN EXIT WHILE
ELSE display V_nome, V_fonct, V_sal
display " "
SLEEP 0
End if
end while
sleep 5
CLOSE C_Employe
}

# Requete e)
# ---------
{define employe Record like employe.*
declare C_Employe cursor for Select * from employe A, employe B
where A.chef=B.nume and A.nums<>B.nums

open C_Employe

while true
FETCH C_Employe into employe.*
IF status=100 THEN EXIT WHILE
ELSE display employe.*
SLEEP 2
End if
end while

CLOSE C_Employe
}

# Requete f)
# ---------
{WHENEVER ERROR CONTINUE
INSERT INTO Employe (nume) VALUES (7782)
IF STATUS=-239 THEN
DISPLAY "tuple existant" At 12, 30
END IF
sleep 2
}

# Requete g)
# ---------
{define sal_moy decimal(7,2)
define com_moy decimal(7,2)
select avg(sal) x from employe
where nums=(select nums from service where loc='Meknes')
into temp tabsal
select avg(comm) y from employe
where nums=(select nums from service where loc='Meknes')
into temp tabcom
select x into sal_moy from tabsal
select y into com_moy from tabcom
update employe
set sal= 1.1 * sal_moy,
comm = 1.2 * com_moy,
nums=(select nums from service where loc='Casa')
where nums=(select nums from service where loc ='Meknes')

if status =0 then display 'succes'
else display 'echec'
end if
sleep 2
}
# Requete h)
# ---------
{
define V1 like employe.fonct
define V2 smallint
define V3 like employe.sal
declare C_Employe cursor for select count(*),fonct, AVG(sal) from employe
group by fonct
having count(*)>=3
open C_Employe

while true
FETCH C_Employe into V2, V1, V3
IF status=100 THEN EXIT WHILE
ELSE display V1, V2, V3
SLEEP 0
End if
end while

CLOSE C_Employe
}

# Requete e')
# ---------
{define V1 record like employe.*
define V2 like enfant.nomf

declare C cursor for select A.*, B.nomf
from employe A,outer enfant B
where A.nume=B.nume
open C
while true
FETCH C into V1.*, V2
IF status=100 THEN EXIT WHILE
ELSE display V1.*
display " "
display V2
display " "
sleep 0
End if
end while
sleep 2
close C

}

# Requete e'')
# ---------
{define V2 like employe.fonct
define V1 like employe.sal

declare C cursor for select SUM(sal), fonct from employe
where nums=10
and fonct in (select fonct from Employe A , service B
where A.nums=B.nums
and B.noms='vente')
group by fonct
open C
while true
FETCH C into V1, V2
IF status=100 THEN EXIT WHILE
ELSE display V2, V1
End if
end while
sleep 2
CLOSE C
}

# Requete g')
# ---------
{define V char(18 )
declare C cursor for select tabname
from systables T, syscolumns C
where T.tabid=C.tabid
and colname='nums'
open C
while true
FETCH C into V
IF status=100 THEN EXIT WHILE
ELSE display V
End if
end while
sleep 2
CLOSE C
}
# Requete g'')
# ---------
{define V smallint
select coltype into V from Syscolumns
where tabid=(select tabid from systables
where tabname='Employe')
and colname='fonct'
display "Le type de donn‚es de 'fonct' est: ",V at 12, 12
sleep 2
}
END MAIN

Vive INSEA