TD4

-- EXO 1
-- 1.
SET SERVEROUTPUT ON;

DECLARE
n NUMBER;

BEGIN
  n := &n;

  FOR i IN 0..10
  LOOP

    DBMS_OUTPUT.PUT_LINE(n || ' x ' || i || ' = ' || n*i);
  END LOOP;

END;

-- 2.
DECLARE
n NUMBER;
n_facto NUMBER := 1;

BEGIN
  LOOP
    n := &n;
    EXIT WHEN n > 0;

  END LOOP

  FOR i IN 1..n
  LOOP
    n_facto := n_facto * i;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(n || '! = ' || n_facto);

END;

-- 3.
DECLARE

  v_tabExist NUMBER;
  v_sommeDue NUMBER;
  v_duree NUMBER;
  v_remboursementAnnuel NUMBER;
  v_taux NUMBER;
  v_interet NUMBER;
  v_annuite NUMBER;

BEGIN


  SELECT COUNT(*) INTO v_tabExist FROM  user_tables WHERE table_name = 'PRET';
  IF v_tabExist > 0 THEN
    EXECUTE IMMEDIATE 'drop table PRET';
  END IF;
  EXECUTE IMMEDIATE 'create table PRET (duree number, sommeDue number, rembAnnuel number, interet number, annuite number)';

  v_sommeDue := &Somme_Emprunte;
  v_duree := &Duree_Emprunt;
  v_taux := &Taux_annuel;

  v_remboursementAnnuel := v_sommeDue/v_duree;
  v_interet := v_sommeDue * v_taux;
  v_annuite := v_remboursementAnnuel + v_interet;

  FOR i IN 1..(v_sommeDue/v_remboursementAnnuel)
  LOOP
    INSERT INTO PRET VALUES(i, v_sommeDue, v_remboursementAnnuel, v_interet, v_annuite);
    v_sommeDue := v_sommeDue - v_remboursementAnnuel;
    v_interet := v_sommeDue * v_taux;
    v_annuite := v_remboursementAnnuel + v_interet;
  END LOOP;

END;




-- EXO 2
-- 1.
-- a-c)


DECLARE
  v_sal emp2.SAL%TYPE;
  v_moySal emp2.SAL%TYPE := 0;
  v_id EMP2.EMPNO%TYPE;
  v_job emp2.JOB%TYPE;
  v_temp NUMBER;
CURSOR c_emp IS SELECT job, empno, sal FROM emp;

BEGIN
  v_id := &EMPNO;
  SELECT job INTO v_job FROM emp2 WHERE EMPNO = v_id;
  FOR v_emp IN c_emp
  LOOP
    IF v_emp.job = v_job THEN
      v_moySal := v_moySal + v_emp.sal;
    END IF ;
  END LOOP;
  SELECT COUNT(*)INTO v_temp FROM emp2 WHERE job = v_job;
  v_moySal := v_moySal / v_temp;

  IF v_emp.sal < v_moySal THEN
    UPDATE EMP2
    SET SAL = v_moySal
    WHERE EMPNO = v_emp.empno AND EMPNO = v_id;
  ELSE
    UPDATE EMP2
    SET SAL = SAL * 1.1
    WHERE EMPNO = v_emp.empno AND EMPNO = v_id;
  END IF;

END;

-- 2.
-- Commande SQL
ALTER TABLE dept2 add (BUDGET NUMBER(10,2));
-- Bloc PL SLQ a
DECLARE
  CURSOR c_dept IS SELECT deptno FROM dept2;
  v_temp emp2.sal%TYPE;

BEGIN
  FOR v_dept IN c_dept
  LOOP
    SELECT SUM(sal) INTO v_temp FROM emp2 WHERE emp2.deptno = v_dept.deptno;
    UPDATE DEPT2
    SET BUDGET = v_temp WHERE v_dept.deptno = dept2.deptno;
    DBMS_OUTPUT.PUT_LINE(v_temp);
  END LOOP;
END;


-- Bloc PL SQL b
DECLARE