Laborator 7 plsql

lab7.plsql

-- Exercitiul 1 (exceptie)
DECLARE
    v_angajat angajati%ROWTYPE;
BEGIN
    SELECT * INTO v_angajat FROM angajati WHERE employee_id = 259;
    dbms_output.put_line(v_angajat.first_name || ' ' || v_angajat.last_name || ' ' || v_angajat.salary);
    EXCEPTION 
    WHEN no_data_found THEN
        dbms_output.put_line('Nu exista angajati in categoria ceruta');
END;

-- Exercitiul 2 (variabila incompatibila)
DECLARE
    v_angajat angajati%ROWTYPE;
    v_jobid angajati.employee_id%TYPE;
BEGIN
    v_jobid := 'fsaf';
    SELECT * INTO v_angajat FROM angajati WHERE employee_id = v_jobid;
    dbms_output.put_line(v_angajat.first_name || ' ' || v_angajat.last_name || ' ' || v_angajat.salary);
    EXCEPTION 
    WHEN value_error THEN
        dbms_output.put_line('Valoarea variabilei este incorecta.');
END;

-- Ex 4 (salariu > 30.000)
DECLARE
    eroare_salariu EXCEPTION;
    v_salariu angajati.salary%TYPE;
    v_emp_id angajati.employee_id%TYPE;
BEGIN
    v_emp_id := 237;
    v_salariu:= 30001;
    IF v_salariu > 30000 THEN
        raise eroare_salariu;
    ELSE 
    UPDATE angajati
    SET salary = v_salariu
    WHERE employee_id = v_emp_id;
    dbms_output.put_line('Salariu actualizat');
    END IF;
    EXCEPTION
        WHEN eroare_salariu THEN
            dbms_output.put_line('Salariu prea mare! Introduceti o valoare mai mica de $30.000');
END;

--- PROCEDURI


-- Ex 1: Procedura ANGAJEZ
CREATE OR REPLACE PROCEDURE angajez 
IS
BEGIN
    INSERT INTO angajati (employee_id, first_name, last_name, email, hire_date, job_id)
    VALUES (777, 'Ion', 'POPESCU', 'POPESCU', SYSDATE, 'SA_REP');
    dbms_output.put_line('SUCCES: S-a inserat noul angajat');
END angajez;


BEGIN
    angajez;
END;

-- Ex 2: Procedura ANGAJEAZA_MA 
CREATE OR REPLACE PROCEDURE angajeaza_ma
(
    v_job_id IN angajati.job_id%TYPE,
    v_nume IN angajati.first_name%TYPE,
    v_prenume IN angajati.last_name%TYPE)
IS
v_salariu angajati.salary%TYPE;
v_email angajati.email%TYPE;
BEGIN
    SELECT MIN(salary) INTO v_salariu FROM angajati WHERE job_id=v_job_id;
    v_email := v_nume || v_prenume || '@gmail.com';
    INSERT INTO angajati (employee_id, first_name, last_name, email, hire_date, job_id, salary)
    VALUES (555, v_nume, v_prenume, v_email, SYSDATE, v_job_id, v_salariu);
    dbms_output.put_line('SUCCES: S-a inserat noul angajat');
END angajeaza_ma;

BEGIN
    angajeaza_ma('SA_REP', 'Ciurca', 'Radu');
END;

-- Ex 3: Procedura ANGAJEAZA_CA 
CREATE OR REPLACE PROCEDURE angajeaza_ca
(
    v_job_id IN angajati.job_id%TYPE,
    v_nume IN angajati.first_name%TYPE,
    v_prenume IN angajati.last_name%TYPE)
IS
v_salariu angajati.salary%TYPE;
v_email angajati.email%TYPE;
v_salariu_min angajati.salary%TYPE;
v_salariu_max angajati.salary%TYPE;
BEGIN
    SELECT MIN(salary) INTO v_salariu_min FROM angajati WHERE job_id=v_job_id;
    SELECT MAX(salary) INTO v_salariu_max FROM angajati WHERE job_id=v_job_id;
    SELECT dbms_random.value (v_salariu_min, v_salariu_max) INTO v_salariu FROM DUAL;
    v_email := v_nume || v_prenume || '@gmail.com';
    INSERT INTO angajati (employee_id, first_name, last_name, email, hire_date, job_id, salary)
    VALUES (698, v_nume, v_prenume, v_email, SYSDATE, v_job_id, v_salariu);
    dbms_output.put_line('SUCCES: S-a inserat noul angajat');
END angajeaza_ca;

BEGIN
    angajeaza_ca('SA_REP', 'Ion', 'Bal');
END;

-- Ex 4: Procedura Angajez_Masiv_CA
CREATE OR REPLACE TYPE t_angajat AS OBJECT (
  emp_id NUMBER,
  nume      VARCHAR2(100),
  prenume   VARCHAR2(100),
  email     VARCHAR2(100),
  data_ang  DATE,
  job_id VARCHAR(25),
  salariu   NUMBER
);
-- Ex4 Angajez_Masiv_CA
CREATE OR REPLACE TYPE t_lista_angajati AS TABLE OF t_angajat;
DROP TYPE t_lista_angajati;

CREATE OR REPLACE PROCEDURE angajeaza_masiv_ca
(
    v_job_id IN angajati.job_id%TYPE,
    v_numar_persoane IN NUMBER)
IS
v_angj_id NUMBER;
v_email angajati.email%TYPE;
v_salariu_min angajati.salary%TYPE;
v_salariu_max angajati.salary%TYPE;
v_lista_angajati t_lista_angajati := t_lista_angajati();
BEGIN
    SELECT MIN(salary) INTO v_salariu_min FROM angajati WHERE job_id=v_job_id;
    SELECT MAX(salary) INTO v_salariu_max FROM angajati WHERE job_id=v_job_id;
    FOR i IN 1..v_numar_persoane LOOP
    SELECT MAX(employee_id) + 1 INTO v_angj_id FROM angajati;
    v_lista_angajati.EXTEND;
    v_lista_angajati(i) := t_angajat(
      emp_id       => 1000 + i,
      nume     => 'Nume' || TO_CHAR(i),
      prenume  => 'Prenume' || TO_CHAR(i),
      email    => 'email' || TO_CHAR(i) || '@gmail.com',
      data_ang => SYSDATE,
      job_id => v_job_id,
      salariu  => DBMS_RANDOM.VALUE(v_salariu_min, v_salariu_max)
    );
    END LOOP;
    FOR i IN 1..v_numar_persoane LOOP
    INSERT INTO angajati (employee_id, first_name, last_name, email, hire_date, job_id, salary)
    VALUES (v_lista_angajati(i).emp_id, v_lista_angajati(i).nume, v_lista_angajati(i).prenume, v_lista_angajati(i).email, v_lista_angajati(i).data_ang, v_lista_angajati(i).job_id, v_lista_angajati(i).salariu);
    END LOOP;
END angajeaza_masiv_ca;

BEGIN
    angajeaza_masiv_ca('SA_REP', 3);
END;

-- Ex 4: concediez_ultim
CREATE OR REPLACE PROCEDURE concediez_ultim 
IS
v_angj_id angajati.employee_id%TYPE;
    PROCEDURE afisare_confirmare
    (
        ang_id IN angajati.employee_id%TYPE
    )
    IS
    BEGIN
        dbms_output.put_line('S-a sters angajatul cu ID ' || ang_id );
        dbms_output.put_line('Ii uram succes in gasirea unui nou JOB. :(');
    END afisare_confirmare;
BEGIN
    SELECT MAX(employee_id) INTO v_angj_id FROM angajati;
    DELETE FROM angajati WHERE employee_id=v_angj_id;
    afisare_confirmare(v_angj_id);
END concediez_ultim;

BEGIN
    concediez_ultim;
END;