1)
CREATE OR REPLACE FUNCTION plus2 (
x IN INT,
y IN INT
) RETURN INT
IS
BEGIN
RETURN x + y;
END;
DECLAREccccccc
a INT := 5;
b INT := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE(plus2(a, b));
END;
2)
CREATE OR REPLACE FUNCTION FN_factorielle (
x IN INT
) RETURN INT
IS
a INT := 1;
r INT := 1;
BEGIN
IF x = 0 THEN
RETURN 1;
END IF;
a := x;
LOOP
EXIT WHEN a = 1;
r := r * a;
a := a - 1;
END LOOP;
RETURN r;
END;
DECLARE
a INT := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE(FN_Factorielle(5));
END;
3)
a)
DECLARE
a INT := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE(FN_Factorielle(5));
END;
b)
DECLARE
a INT := 1;
BEGIN
LOOP
EXIT WHEN a = 11;
DBMS_OUTPUT.PUT_LINE(FN_Factorielle(a));
a:=a+1;
END LOOP;
END;
4)
CREATE OR REPLACE PROCEDURE PROC_Dept (
x IN INT,
cnt out INT
) IS
BEGIN
SELECT COUNT(EMPLOYEE_ID) INTO cnt FROM employees WHERE DEPARTMENT_ID = x;
DBMS_OUTPUT.PUT_LINE(cnt);
END;
DECLARE
d_id NUMBER := :id;
ou NUMBER ;
BEGIN
PROC_Dept(d_id,ou);
END;
5)
CREATE OR REPLACE PROCEDURE PROC_Maximum (
x IN INT,
y IN INT,
cnt OUT INT
) IS
BEGIN
IF x > y THEN
cnt := x;
ELSIF y > x THEN
cnt := y;
ELSE
cnt := 0;
END IF;
END;
DECLARE
x NUMBER := :x;
y NUMBER := :y;
ou NUMBER ;
BEGIN
PROC_Maximum (x,y,ou);
DBMS_OUTPUT.PUT_LINE(ou);
END;
6)
CREATE OR REPLACE PROCEDURE PROC_Max_Dept (
x IN INT,
y IN INT,
cnt OUT INT
) IS
w INT;
z INT;
BEGIN
PROC_Dept(x,w);
PROC_Dept(y,z);
IF w > z THEN
cnt := x;
ELSIF z > w THEN
cnt := y;
ELSE
cnt := 0;
END IF;
END;
DECLARE
x NUMBER := :x;
y NUMBER := :y;
ou NUMBER ;
BEGIN
PROC_Max_Dept (x,y,ou);
DBMS_OUTPUT.PUT_LINE(ou);
END;
X=90 res=3 employees
Y=80 res=34 employees
7)
CREATE OR REPLACE PROCEDURE PROC_Permute (
x IN OUT INT,
y IN OUT INT
) IS
temp INT;
BEGIN
temp := x;
x := y;
y := temp;
END;
8)
CREATE OR REPLACE FUNCTION FN_Annual_Income (
x IN INT
) RETURN INT
IS
w INT;
z INT;
BEGIN
select salary into w from employees where employee_id=x;
z:=w*12;
return z;
END;
DECLARE
x NUMBER := :x;
BEGIN
DBMS_OUTPUT.PUT_LINE(FN_Annual_Income(x));
END;
9)
a)
DECLARE
CURSOR c_emp IS
SELECT employee_id, first_name FROM employees;
v_first_name employees.first_name%TYPE;
v_id employees.employee_id%TYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id, v_first_name;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ' | Annual Salary: ' ||
FN_Annual_Income(v_id));
END LOOP;
CLOSE c_emp;
END;
b)
DECLARE
x NUMBER := 120;
BEGIN
DBMS_OUTPUT.PUT_LINE(FN_Annual_Income(x));
END;
c)
DECLARE
CURSOR c_emp IS
SELECT employee_id FROM employees;
v_id employees.employee_id%TYPE;
res int:=0;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id;
EXIT WHEN c_emp%NOTFOUND;
res:=res+FN_Annual_Income(v_id);
END LOOP;
DBMS_OUTPUT.PUT_LINE(res);
CLOSE c_emp;
END;
10)
CREATE OR REPLACE PROCEDURE PROC_Augment_Salaire (
x IN INT,
y IN INT
) IS
w INT;
z INT;
BEGIN
SELECT salary INTO w FROM employees WHERE employee_id = x;
z := w + (w * y / 100);
UPDATE employees SET salary = z WHERE employee_id = x;
DBMS_OUTPUT.PUT_LINE('Old salary: ' || w || ' | New salary: ' || z);
END;
DECLARE
x NUMBER := 120;
BEGIN
PROC_Augment_Salaire(x,15);
END;
11)
CREATE OR REPLACE PROCEDURE PROC_Affichage (
x IN INT
) IS
w INT;
BEGIN
SELECT salary INTO w FROM employees WHERE employee_id = x;
DBMS_OUTPUT.PUT_LINE('salary: ' || w);
END;
DECLARE
x NUMBER := 120;
BEGIN
PROC_Affichage (x);
END;
12)
DECLARE
x NUMBER := 120;
BEGIN
PROC_Affichage (x);
PROC_Augment_Salaire(x,50);
PROC_Affichage (x);
END;
13)
CREATE OR REPLACE PROCEDURE PROC_Ajout (
x IN INT,
name IN VARCHAR2
) IS
v_id INT;
v_name VARCHAR2(30);
CURSOR c_emp IS
SELECT department_id, department_name FROM DEPARTMENTS;
BEGIN
INSERT INTO DEPARTMENTS(department_id, department_name)
VALUES (x, name);
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id, v_name;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('dep id : ' || v_id || ' | name: ' || v_name);
END LOOP;
CLOSE c_emp;
END;
BEGIN
PROC_Ajout(1150, 'yass_dep');
END;
14) select * from User_objects;
15)select * from User_source;
16) describe FN_ANNUAL_INCOME;
17)describe PROC_Augment_Salaire;