seminar5

SET serveroutput ON;

DECLARE
  CURSOR c IS SELECT nume, salariul FROM angajati
  ORDER BY salariul DESC;

  r c%ROWTYPE;
BEGIN
  OPEN c;
    LOOP
      FETCH c INTO r;
      EXIT WHEN c%notfound OR c%rowcount>10;
      DBMS_OUTPUT.PUT_LINE('Angajatul '||r.nume||' are salariul ' || r.salariul);
    END LOOP;
  CLOSE c;
END;
/

DECLARE
  CURSOR c IS SELECT nume, salariul FROM angajati
  ORDER BY salariul DESC;
BEGIN
    FOR r IN c LOOP
      EXIT WHEN c%rowcount>10;
      DBMS_OUTPUT.PUT_LINE('Angajatul '||r.nume||' are salariul ' || r.salariul);
    END LOOP;
END;
/

/*cursor anonim*/
BEGIN
    FOR r IN (SELECT denumire_departament, SUM(salariul) suma FROM angajati a, departamente d
    WHERE a.id_departament = d.id_departament GROUP BY denumire_departament) LOOP
      /*EXIT WHEN c%rowcount>10;*/
      DBMS_OUTPUT.PUT_LINE('Departamentul '||r.denumire_departament||' plateste salarii in valoare de ' || r.suma);
    END LOOP;
END;
/


/*cursor cu parametru*/
DECLARE
  CURSOR c(p_id angajati.id_departament%TYPE) IS SELECT nume FROM angajati WHERE id_departament = p_id;
  r c%ROWTYPE;
BEGIN
  OPEN c(&id);
    LOOP
      FETCH c INTO r;
       DBMS_OUTPUT.PUT_LINE('Angajatul ' || r.nume);
      EXIT WHEN c%notfounD;
    END LOOP;
  CLOSE c;
END;
/


/*cursori imbricati*/
DECLARE
  CURSOR c IS SELECT nr_comanda, data FROM comenzi;
  CURSOR c1(p_nr NUMBER) IS SELECT denumire_produs, cantitate FROM produse p, rand_comenzi rc
  WHERE p.id_produs = rc.id_produs AND rc.nr_comanda = p_nr;

  r c%ROWTYPE;
  r1 c1%ROWTYPE;
BEGIN
  OPEN c;
  LOOP
  FETCH c INTO r;
  EXIT WHEN c%notfound;
  DBMS_OUTPUT.PUT_LINE('Comanda' || r.nr_comanda || ' incheiata la data '|| r.data || ' are urmatoarele produse ');
    OPEN c1(r.nr_comanda);
      LOOP
        FETCH c1 INTO r1;
      EXIT WHEN c1%notfound;
        DBMS_OUTPUT.put_line('Produsul ' || r1.denumire_produs);
      END LOOP;
    CLOSE c1;
      DBMS_OUTPUT.put_line('==============');
  END LOOP;
  CLOSE c;
END;
/

/*FOR UPDATE*/
SELECT * FROM angajati;

DECLARE
  CURSOR c IS SELECT * FROM angajati FOR UPDATE WAIT 20;
BEGIN
  FOR r IN c
    LOOP
      IF r.salariul < 5000 THEN
        /*UPDATE angajati SET salariul = salariul+1 WHERE id_angajat = r.id_angajat;*/
        UPDATE angajati SET salariul = salariul+1 WHERE CURRENT OF c;

      END IF;
    END LOOP;
END;
/
ROLLBACK;


CREATE TABLE angajati_noi(id NUMBER(2), nume VARCHAR2(50));
INSERT INTO angajati_noi VALUES(1, 'Popescu');


SELECT * FROM angajati_noi;

DECLARE
  CURSOR c IS SELECT * FROM angajati_noi ORDER BY id FOR UPDATE NOWAIT;
  r c%ROWTYPE;
  r_ant c%ROWTYPE;
BEGIN
  OPEN c;
    FETCH c INTO r_ant;
    LOOP
      FETCH c INTO r;
      EXIT WHEN c%NOTFOUND;
      IF r_ant.id = r.id THEN DELETE FROM angajati_noi WHERE CURRENT OF c;
      END IF;
      r_ant:=r;
    END LOOP;
  CLOSE c;
END;
/