Ex sgbd

-- Exercitiul 1
SET serveroutput ON;
CLEAR screen;
accept p_valn PROMPT 'n=';
<<mainblock>>
DECLARE
  v_valn NUMBER := &p_valn;

  CURSOR myc(pcn NUMBER) IS
    SELECT *
    FROM employees
    WHERE salary > v_valn;

BEGIN

  DBMS_OUTPUT.put_line('Ati introdus n=' || v_valn);

  FOR j IN myc(v_valn) LOOP
    IF (j.commission_pct IS NOT NULL OR j.commission_pct > 0) THEN
      DBMS_OUTPUT.put_line(j.last_name || '->' || j.salary);
    END IF;
  END LOOP;
END;

-- Exercitiul 2
-- crearea tip de date tablou imbricat
CREATE OR REPLACE TYPE mytabimbri AS TABLE OF NUMBER;

-- bloc pentru populare si afisare
SET serveroutput ON;
CLEAR screen;
<<mainblock>>
DECLARE

  TYPE mytabidx IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_tabidx mytabidx;                     -- nu are nevoie de ctor

  v_tabimbri mytabimbri := mytabimbri(); -- necesita un ctor
  v_idx NUMBER := 0;
  v_aux NUMBER := 0;
BEGIN

  -- umplere
  FOR j IN 1..100 LOOP
    v_tabimbri.EXTEND;
    v_tabimbri(v_tabimbri.LAST) := j;
  END LOOP;

  -- stergere impare
  v_idx :=  v_tabimbri.FIRST;
  WHILE (v_idx IS NOT NULL) LOOP
    IF v_idx MOD 2 <> 0 THEN
      v_aux := v_idx;
      v_tabimbri.DELETE(v_aux);
    END IF;
    v_idx := v_tabimbri.NEXT(v_idx);
  END LOOP;

  -- afisare tablou nested
  DBMS_OUTPUT.put_line('Afisare tablou imbricat');
  v_idx :=  v_tabimbri.FIRST;
  WHILE (v_idx IS NOT NULL) LOOP
      v_tabidx(v_idx) := v_idx;
      DBMS_OUTPUT.put_line(v_idx || ') ' || v_tabimbri.NEXT(v_idx));
      v_idx := v_tabimbri.NEXT(v_idx);
  END LOOP;

  -- afisare tablou indexat in ordine inversa
  DBMS_OUTPUT.put_line('Afisare inversa tablou indexat');
  v_idx :=  v_tabidx.LAST;
  WHILE (v_idx IS NOT NULL) LOOP
      DBMS_OUTPUT.put_line(v_idx || ') ' || v_tabidx.NEXT(v_idx));
      v_idx := v_tabidx.PRIOR(v_idx);
  END LOOP;
END;

-- Exercitiul 3
CREATE OR REPLACE PROCEDURE myprocrm(p_in_out IN OUT NUMBER) IS
  v_in_param NUMBER := 0;

  -- praguri salariale
  c_liminf CONSTANT NUMBER := 3000;
  c_limsup CONSTANT NUMBER := 7000;

  -- coeficienti de marire al salariului (20%,15%,10%)
  c_k1 CONSTANT NUMBER := 1.2;
  c_k2 CONSTANT NUMBER := 1.15;
  c_k3 CONSTANT NUMBER := 1.10;
  c_v4 CONSTANT NUMBER := 1000;   -- valoare fixa salariu

  v_valid  NUMBER := 0;
  v_sal    NUMBER := 0;
  v_newsal NUMBER := 0;
BEGIN

  v_in_param := p_in_out;       -- copie pt val parametrului la input just to be sure
  IF v_in_param IS NULL THEN
    DBMS_OUTPUT.put_line('Parametru de intrare invalid');
    RETURN;
  END IF;

  -- verific daca angajatul cu codul specificat exista
  SELECT COUNT(*) INTO v_valid
  FROM employees
  WHERE employee_id = v_in_param;
  IF v_valid = 0 THEN
    DBMS_OUTPUT.put_line('Codul angajatului nu exista');
    RETURN;
  END IF;

  SELECT salary INTO v_sal
  FROM employees
  WHERE employee_id = v_in_param;

  -- calculare salariu nou
  CASE
    WHEN v_sal < c_liminf THEN
      v_newsal := v_sal * c_k1;   -- 20%
    WHEN v_sal BETWEEN c_liminf AND c_limsup THEN
      v_newsal := v_sal * c_k2;  -- 15%
    WHEN v_sal > c_limsup THEN
      v_newsal := v_sal * c_k3;  -- 10%
    WHEN v_sal IS NULL THEN
      v_newsal := c_v4;          -- hardcoded
  END CASE;

  -- salvarea date in parametrul de intrare iesire
  p_in_out := v_newsal;

  --dbms_output.put_line('Valoarea veche a salariului este ' || v_sal);
  --dbms_output.put_line('Valoarea noua  a salariului este ' || v_newsal);

END myprocrm;

-- apelarea procedurii create dintr-un bloc
SET serveroutput ON;
CLEAR screen;
<<mainblock>>
DECLARE
  v_param NUMBER := 100;
BEGIN
  DBMS_OUTPUT.put_line('Angajatul cu codul ' || v_param || ' are ');
  myprocrm(v_param);
  DBMS_OUTPUT.put_line('valoarea noua a salariului : ' || v_param);
END;

-- Exercitiul 4
/* Abordarea ar consta in crearea unui trigger la nivel de linie pe tabelul
employees pe insertie sau update de salariu de tip BEFORE care sa modifice dinamic valorile
in functie de ce regaseste in tabelul jobs pe maxim si minim */
CREATE OR REPLACE
TRIGGER mytrg
BEFORE INSERT OR UPDATE OF salary, job_id
ON employees
FOR EACH ROW
DECLARE
  v_job_id jobs.job_id%TYPE;

  v_sal_inserat NUMBER := 0;
  v_minsal NUMBER := 0;
  v_maxsal NUMBER := 0;
BEGIN

  -- pp: avand un FK pe job_id -> job_id-ul va fi mereu valid

  IF inserting THEN
    SELECT min_salary, max_salary INTO v_minsal, v_maxsal
    FROM jobs
    WHERE job_id = :NEW.job_id;

    DBMS_OUTPUT.put_line('Slariul min:'|| v_minsal);
    DBMS_OUTPUT.put_line('Slariul max:'|| v_maxsal);

    -- asiguram limitele corespunzatoare
    v_sal_inserat := GREATEST(v_minsal, :NEW.salary);
    v_sal_inserat := LEAST(v_maxsal, v_sal_inserat);

    DBMS_OUTPUT.put_line('Salariul nou este ' ||  v_sal_inserat);

    -- teoretic in acet moment salariul pentru noua linie inserata se afla
    -- intre limitele intervalului.
    :NEW.salary := v_sal_inserat;
  ELSIF updating('salary') THEN
    SELECT NVL(min_salary, 0), NVL(max_salary, 0) INTO v_minsal, v_maxsal
    FROM jobs
    WHERE job_id = :OLD.job_id;   -- singura diferenta practica fata de mai sus
                                     -- din moment ce in acest job_id-ul este cel vechi

    -- asiguram limitele corespunzatoare
    v_sal_inserat := GREATEST(v_minsal, :NEW.salary);
    v_sal_inserat := LEAST(v_maxsal, v_sal_inserat);

    DBMS_OUTPUT.put_line('Salariul nou este ' ||  v_sal_inserat);

    -- teoretic in acet moment salariul pentru noua linie inserata se afla
    -- intre limitele intervalului.
    :NEW.salary := v_sal_inserat;
  ELSIF updating('job_id') THEN
    -- extrage limite pentru noul job_id
    SELECT NVL(min_salary, 0), NVL(max_salary, 0) INTO v_minsal, v_maxsal
    FROM jobs
    WHERE job_id = :NEW.job_id;

    -- asiguram limitele corespunzatoare
    v_sal_inserat := GREATEST(v_minsal, :NEW.salary);
    v_sal_inserat := LEAST(v_maxsal, v_sal_inserat);

    DBMS_OUTPUT.put_line('Salariul nou este ' ||  v_sal_inserat);

    -- teoretic in acet moment salariul pentru noua linie inserata se afla
    -- intre limitele intervalului.
    :NEW.salary := v_sal_inserat;
  END IF;
END;

-- testare pe insertie (teoretic ar trebui sa functioneze si pe update...)
INSERT INTO
employees
VALUES(500, 'RR', 'MM', 'rr0@mm', '123456789', SYSDATE, 'SH_CLERK', 50000, 0.1, 100, 50);
INSERT INTO
employees
VALUES(501, 'RR', 'MM', 'rr1@mm', '123456789', SYSDATE, 'SH_CLERK', 2000, 0.1, 100, 50);
INSERT INTO
employees
VALUES(502, 'RR', 'MM', 'rr2@mm', '123456789', SYSDATE, 'SH_CLERK', 3000, 0.1, 100, 50);

ROLLBACK;

ALTER TRIGGER DISABLE mytrg;