Poster un commentaire ou une réponse
TP Procédures et Fonctions
Zaineb :
nb posts:320
nb discussions:33
inscrit le :23-01-2015
Zaineb :
nb posts:320
nb discussions:33
inscrit le :23-01-2015
Zaineb :
nb posts:320
nb discussions:33
inscrit le :23-01-2015
Correction:
ex1
a)create or replace procedure greet is
today date :=sysdate;
tomorrow today%type;
begin
tomorrow :=sysdate+1;
dbms_output.put_line ('hellow world today is :'||today|| ' and tomorrow is :'||tomorrow);
end;
//appel
begin
greet;
end;
Ex2
create or replace procedure greet2(nom varchar2) is
today date :=sysdate;
tomorrow today%type;
begin
tomorrow :=sysdate+1;
dbms_output.put_line ('hellow '|| nom ||' today is :'||today|| ' and tomorrow is :'||tomorrow);
end;
begin
greet2('jihene');
end;
Ex3
a)create or replace function nom_dept(numero departments.department_id%type)
return VARCHAR2 is
nom departments.department_name%type ;
begin
select department_name into nom from departments
where department_id = numero ;
return nom ;
end ;
select nom_dept(20) from dual;
ou
declare
nom varchar2(30);
begin
nom:= nom_dept (20);
dbms_output.put_line(nom);
end;
b) création de la procedure:
create or replace procedure nom_dept2(numero IN departments.department_id%type,
nom OUT departments.department_name%type) is
begin
select department_name into nom from departments
where department_id = numero ;
end ;
declare
nom varchar2(30);
begin
nom_dept2 (20,nom);
dbms_output.put_line(nom);
end;
Ex4
Create or replace function fn_revenu_ann (v_empno in number)
Return number is
v_sal_annuel number;
begin
select ((salary * 12) + decode(nvl(commission_pct,0),0,salary,commission_pct*12)) into v_sal_annuel from employees where (employee_id = v_empno);
return v_sal_annuel;
end;
Create or replace function fn_revenu_annuel (v_empno in number)
Return number is
v_sal number;
v_comm number;
v_sal_ann number;
begin
select salary ,nvl(commission_pct,0) into v_sal,v_comm from employees where (employee_id = v_empno);
if v_comm>0 then
v_sal_ann := (v_sal+v_comm)*12;
else v_sal_ann:= v_sal*13;
end if;
return v_sal_ann;
end;
appel
begin
dbms_output.put_line(fn_revenu_annuel (101));
end;
ex5
CREATE OR REPLACE FUNCTION factorielle (N IN NUMBER)
RETURN NUMBER IS
BEGIN
IF (N<=1) THEN
RETURN 1;
ELSE
RETURN N *factorielle(N-1);
END IF;
END;
declare
n number;
begin
n:= factorielle(10);
dbms_output.put_line(n);
end;
sol 2
create or replace function fact(n number) return number
is
c NUMBER;
begin
c := 1;
if n<=1
then return 1;
else
for i in 1..n loop
c:= c*i;
end loop;
end if;
return c;
END;
/
declare
n number ;
begin
n:= fact(10);
dbms_output.put_line(n);
end;
Ex6
CREATE OR REPLACE PROCEDURE F_Test_Augmentation
IS
aug NUMBER(2,2):=0.2;
cursor c is select employee_id,salary from employees;
BEGIN
for cc in c
loop
update employees
set salary = salary + (salary *aug)
where employee_id = cc.employee_id;
commit;
end loop;
END;
begin
F_Test_Augmentation;
end;
ex 7
CREATE OR REPLACE PROCEDURE emp_salary_increase
(emp_id IN employees.employee_id%type, salary_inc IN OUT employees.salary%type)
IS
tmp_sal number;
BEGIN
SELECT salary
INTO tmp_sal
FROM employees
WHERE employee_ID = emp_id;
IF tmp_sal between 1000 and 2000 THEN
salary_inc := tmp_sal * 1.3;
ELSIF tmp_sal >2000 THEN
salary_inc := tmp_sal * 1.5;
END IF;
END;
/
programme appelant:
DECLARE
CURSOR updated_sal is
SELECT employee_id,salary
FROM employees;
pre_sal number;
BEGIN
FOR emp_rec IN updated_sal LOOP
pre_sal := emp_rec.salary;
emp_salary_increase(emp_rec.employee_id, emp_rec.salary);
dbms_output.put_line('The salary of ' || emp_rec.employee_id ||
' increased from '|| pre_sal || ' to '||emp_rec.salary);
END LOOP;
END;
