Exercice 1

1.

CREATE OR REPLACE FUNCTION getdeptnom(dept_number IN dept.deptno%TYPE) RETURN varchar2 IS
deptnom varchar2(30);
BEGIN
SELECT dname INTO deptnom FROM dept WHERE deptno=dept_number;
RETURN deptnom;
END getdeptnom;
/

2.

CREATE OR REPLACE PROCEDURE getnom(dept_number IN dept.deptno%TYPE,deptnom OUT dept.dname%TYPE)
IS
deptname varchar2(30);
BEGIN
SELECT dname INTO deptname FROM dept WHERE deptno=dept_number;
deptnom:=deptname;
END getnom;
/

Exercice 2:

1.

CREATE OR REPLACE PROCEDURE printer IS

Cursor cursorprinter IS (SELECT ename,job,getdeptid(deptno) deptname FROM emp) ;
ligne cursorprinter % Rowtype;
BEGIN
OPEN cursorprinter;
loop
Fetch cursorprinter INTO ligne;
exit WHEN cursorprinter%NotFound;
DBMS_OUTPUT.put_line('l employe <<' || ligne.ename ||'>> a la profession <<' || ligne.job || '>> dans le departement <<' || ligne.deptname || '>>');
END loop;
close cursorprinter;
END printer;
/

2.

CREATE OR REPLACE FUNCTION getemployename(numero IN emp.empno%TYPE) RETURN varchar2
IS
nom varchar2(30);
BEGIN
SELECT ename INTO nom FROM emp WHERE empno=numero;
DBMS_OUTPUT.put_line('l employe <<' || nom || '>> a le code <<' || numero || '>>');
RETURN (nom);
exception
WHEN no_data_found
THEN DBMS_OUTPUT.PUT_LINE('code not found');
RETURN('AUCUN');
END getemployename;
/