Table emp
Table dept
\\\\ Effacer un département donné
set serveroutput on
VARIABLE g\_result VARCHAR2(50);
ACCEPT p\_deptno PROMPT ‘Donner le numéro du département :';
DECLARE
v\_result NUMBER(2) ;
BEGIN
DELETE
FROM dept
WHERE deptno = &p\_deptno;
dbms\_output.put\_line(' la suppression de '||Sql%rowcount||' enregsitrement(s)');
v\_result := SQL%rowcount;
:g\_result := (TO\_CHAR (v\_result) || ' enregistrement(s) supprimé(s)') ;
IF SQL%NOTFOUND Then
dbms\_output.put\_line('aucun enregistrement n’’a été trouvé ');
END IF;
END ;
/
PRAGMA EXCEPTION\_INIT
set serveroutput on
DECLARE
emp\_exist EXCEPTION;
PRAGMA EXCEPTION\_INIT (emp\_exist,-2292);
v\_deptno dept.deptno%TYPE := 20;
BEGIN
DELETE
FROM dept
WHERE deptno = v\_deptno;
COMMIT;
EXCEPTION
WHEN emp\_exist THEN
DBMS\_OUTPUT.PUT\_LINE('Suppression Impossible du dept: '||TO\_CHAR(v\_deptno)|| 'Employés existants ');
END;
/
--- MODIFIER LE SALAIRE D’UN EMPLOYE DE NOM DONNE
SET SERVEROUTPUT ON;
ACCEPT nom PROMPT 'donner le nom de l''employè : ';
DECLARE
nomemp emp.ename%type:='&nom';
OK exception;
Non\_trouve exception;
BEGIN
Update emp
SET sal = 200
Where ename = nomemp; ----- where ename='&nom';
IF SQL%NOTFOUND then
Raise non\_trouve;
else Raise ok;
END IF;
Exception
When non\_trouve then
DBMS\_OUTPUT.PUT\_LINE ('employé non trouvé ! ! ! ');
When OK then
DBMS\_OUTPUT.PUT\_LINE('MODIFICATION FAITE’);
END;
/
Afficher les employés CLERK du département 20, suivis de leurs nombres, et ensuite faire la même chose pour les SALESMAN du département 30.
DECLARE
CURSOR emp\_cursor (p\_job VARCHAR2(30),
p\_deptno NUMBER)
IS
SELECT empno, ename
FROM emp
Publicité
WHERE job = p\_job AND deptno = p\_deptno;
emp\_record emp\_cursor%ROWTYPE;
BEGIN
OPEN emp\_cursor (‘CLERK’,20);
LOOP
FETCH emp\_cursor INTO emp\_record;
EXIT WHEN emp\_cursor%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE(emp\_record.empno||’ ‘||emp\_record.ename);
END LOOP;
DBMS\_OUTPUT.PUT\_LINE(‘Nombred’’ employés: ’||emp\_cursor%ROWCOUNT);
CLOSE emp\_cursor;
OPEN emp\_cursor(‘SALESMAN’,30);
…………………………………………………………………...
END;
/
DECLARE
CURSOR emp\_cursor IS
SELECT ename, sal, deptno
FROM emp
WHERE deptno=20
FOR UPDATE OF sal NOWAIT;
BEGIN
FOR emp\_rec IN emp\_cursor
LOOP
IF emp\_rec.sal<2000 THEN
DBMS\_OUTPUT.PUT\_LINE(emp\_rec.ename||’
‘||emp\_rec.sal);
UPDATE emp
SET sal=sal\*1.1
WHERE CURRENT OF emp\_cursor;
END IF;
END LOOP;
COMMIT;
END;
/
-- Insérer dans une table pour chaque responsable la liste de ses employés
Drop table messages;
create table messages (msg varchar2(100));
set serveroutput on
DECLARE
CURSOR C1 IS select DISTINCT (mgr) from emp order by mgr ;
CURSOR C2 (v\_mgr number) IS
Select empno from emp
where mgr = v\_mgr
order by mgr;
v\_resp c1%rowtype ;
v\_emp c2%rowtype ;
BEGIN
OPEN C1 ;
LOOP
FETCH C1 INTO v\_resp;
EXIT when C1%NOTFOUND;
IF v\_resp.mgr IS not null THEN
INSERT Into messages Values ('responsable: '|| v\_resp.mgr);
End if; ???????????????????????
IF C2%ISOPEN THEN
close C2;
END IF;
OPEN C2 (v\_resp.mgr);
Loop
FETCH C2 into v\_emp;
exit when C2%NOTFOUND;
INSERT into messages values (' employé : '||v\_emp.empno);
EndLoop;
Close C2;
END LOOP;
Close C1;
Publicité
END;
/
Question : Insérer dans une table Resultat pour chaque département les noms de ses employés.
DECLARE
Cursor v\_cursor1 IS
SELECT deptno, dname
FROM dept;
v\_record1 v\_cursor1%rowtype;
CURSOR v\_cursor2 (v\_deptno dept.deptno%type) IS
SELECT ename
FROM emp
WHERE deptno = v\_deptno;
v\_record2 v\_cursor2%rowtype;
v\_msg varchar2(100):= ' ';
BEGIN
OPEN v\_cursor1;
loop
FETCH v\_cursor1 INTO v\_record1;
open v\_cursor2(v\_record1.deptno);
loop
FETCH v\_cursor2 INTO v\_record2;
EXIT when v\_cursor2%NOTFOUND ;
v\_msg := v\_msg || v\_record2.ename || ', ';
end loop; ----- boucle interne -----
EXIT when v\_cursor1%NOTFOUND ;
INSERT into resultat
Values (v\_record1.deptno, v\_record1.dname,v\_msg);
close v\_cursor2; ---- curseur des employés ---
v\_msg := '';
end loop; \\\\ boucle externe\\\\*\
close v\_cursor1; --- curseur des départments ----
COMMIT;
END;
/
-- Insérer dans une table pour chaque département la liste de ses employés
DROP table messages;
create table messages (msg varchar2(100));
set serveroutput on
DECLARE
CURSOR C1 IS SELECT DISTINCT (deptno) from emp
order by deptno;
CURSOR C2 (v\_deptno number) IS
SELECT empno, ename
FROM emp
WHERE deptno= v\_deptno;
v\_dept C1%rowtype ;
v\_emp C2%rowtype ;
BEGIN
OPEN C1 ;
LOOP
FETCH C1 INTO v\_dept;
EXIT When C1%NOTFOUND;
INSERT INTO messages Values ('département numéro: '|| v\_dept.deptno);
IF C2%ISOPEN THEN
Close C2;
END IF;
OPEN C2 (v\_dept.deptno);
Loop
FETCH C2 INTO v\_emp;
EXIT When C2%NOTFOUND;
INSERT into messages
values (' l’’employé '||v\_emp.empno||' '||v\_emp.ename);
End Loop;
Close C2;
END LOOP;
Close C1;
END;
Publicité
/
Revenu des employés ayant une commission et appartenant au même département d’un employé donné
create or replace function total\_revenu
(numemp in emp.empno%type) return NUMBER
IS
total number:=0;
CURSOR emp\_cursor IS
SELECT sal, comm
FROM emp
WHERE deptno = (SELECT deptno
FROM emp
WHERE empno=numemp)
and comm is not null;
TYPE emp\_record\_type IS RECORD
(com emp.comm%type,
Salaire emp.sal%type
);
emp\_record emp\_record\_type;
BEGIN
OPEN emp\_cursor;
loop
FETCH emp\_cursor INTO emp\_record;
EXIT when emp\_cursor%notfound;
total:=total+(emp\_record.salaire+emp\_record.com);
end loop;
CLOSE emp\_cursor;
return(total);
END;
/
EXECUTION
SQL> VARIABLE x number
SQL> execute :X:=total\_revenu(7369);
Procédure PL/SQL terminée avec succès.
SQL> PRINT X
X
----------
5950
Ecrire une procédure Afficher\_tables qui donne le nom de toutes vos tables
set serveroutput on
create or replace procedure AFFICHER\_tables
IS
CURSOR afficher\_nom IS
Select table\_name
From user\_tables;
BEGIN
dbms\_output.put\_line(' Nom des tables : ');
FOR nom IN afficher\_nom
LOOP
dbms\_output.put\_line( nom.table\_name);
END LOOP;
END;
/
EXECUTE afficher\_tables;
Ex3 : Ecrire une procédure pour afficher le nom, le salaire et la commission d’un employé donné.
CREATE OR REPLACEPROCEDURE Interr\_emp(
v\_id IN emp.empno%TYPE,
v\_name OUT emp.ename%TYPE,
v\_salary OUT emp.sal%TYPE,
v\_comm OUT emp.comm%TYPE)
IS
BEGIN
SELECT ename, sal, comm
INTO v\_name, v\_salary, v\_comm
FROM emp
WHERE empno = v\_id;
END Interr\_emp;
/
Publicité
EX4 : Ecrire une procédure pour afficher le nom et la ville d’un département donné.
set serveroutput on
set verify off
CREATE or REPLACE procedure TEST (n IN dept.deptno%type ,
nom OUT dept.dname%type,
ville OUT dept.loc%type)
IS
BEGIN
SELECT dname, loc INTO nom, ville
FROM dept
WHERE deptno = n;
END;
/
\\\ Programme principal \\\**
ACCEPT numero prompt 'donner le numéro du dept ';
DECLARE
dnom dept.dname%type;
dville dept.loc%type;
BEGIN
TEST (&numero, dnom,dville);
dbms\_output.put\_line('le nom du département est: ' ||dnom||’situé à ;’||dville);
END;
/
Ex5 : Ecrire une procédure pour afficher le lieu de travail d’un employé de nom donné (supposé unique).
set serveroutput on;
create or replace procedure emp\_lieu(
empname IN emp.ename%type)
IS
lieu\_travail dept.dname%type;
BEGIN
SELECT dname into lieu\_travail
FROM dept
WHERE deptno IN (select deptno
from emp
where emp.ename = empname);
dbms\_output.put\_line(empname||' travaille à ‘|| lieu\_travail);
END;
/
Ex6 : les employés qui travaillent avec un employé donné
set serveroutput on
create or replace PROCEDURE ENSEMBLES (
empnum IN emp.empno%type )
IS
CURSOR C\_ENS IS
SELECT ename
FROM emp
WHERE deptno = ( SELECT deptno
FROM emp
WHERE empno=empnum );
rec C\_ENS%rowtype ;
nom\_emp emp.ename%type;
BEGIN
SELECT ename INTO nom\_emp
FROM emp
WHERE empno = empnum;
dbms\_output.put\_line('les employés qui travaillent avec l’’employé ‘||nom\_emp || ' sont : ');
FOR rec IN C\_ENS
Loop
IF rec.ename != nom\_emp THEN
DBMS\_OUTPUT.PUT\_LINE(rec.ename);
END IF;
endloop;
END ;
/