Laborator 6 PLSQL

ex1.plsql

-- Exercitiul 1 (record)
DECLARE
    CURSOR c_angajat IS
    SELECT employee_id, first_name, last_name, job_id, salary, hire_date FROM angajati
    WHERE employee_id=236;
    v_angajat c_angajat%ROWTYPE;
BEGIN
    OPEN c_angajat;
    LOOP
    FETCH c_angajat INTO v_angajat;
    EXIT WHEN c_angajat%notfound;
    DBMS_OUTPUT.PUT_LINE(v_angajat.employee_id || ' ' || v_angajat.first_name || ' ' || v_angajat.last_name  || ' ' || v_angajat.job_id || ' ' || v_angajat.salary || ' ' || v_angajat.hire_date);
    END LOOP;
    CLOSE c_angajat;
END;

-- Exercitiul 2 (angajare membru familie)
DECLARE
    CURSOR c_angajat IS
    SELECT employee_id, first_name, email, last_name, job_id, salary, hire_date FROM angajati
    WHERE employee_id=236;
    v_angajat c_angajat%ROWTYPE;
BEGIN
    v_angajat.employee_id := 237;
    v_angajat.first_name := 'Tudor';
    v_angajat.last_name := 'Ciurca';
    v_angajat.email := '[email protected]';
    v_angajat.job_id := 'IT_PROG';
    v_angajat.salary := 100;
    v_angajat.hire_date := '16-Nov-2023';
    INSERT INTO ANGAJATI (employee_id, first_name, last_name, email, hire_date, job_id, salary) 
    VALUES (v_angajat.employee_id, v_angajat.first_name, v_angajat.last_name, v_angajat.email, v_angajat.hire_date, v_angajat.job_id, v_angajat.salary);
END;

-- Exercitiul 3 (tip propriu/user defined record)
DECLARE
   TYPE angajat_romana IS RECORD
   (
        angajat_id angajati.employee_id%TYPE,
        prenume angajati.first_name%TYPE,
        nume angajati.last_name%TYPE,
        posta_electronica angajati.email%TYPE,
        data_angajarii angajati.hire_date%TYPE,
        salariu angajati.salary%TYPE,
        post_id angajati.job_id%TYPE
       );
    v_angajat angajat_romana;
    CURSOR c_angajat IS
    SELECT employee_id, first_name, last_name, email, hire_date, salary, job_id  FROM angajati
    WHERE employee_id=236;
BEGIN
    OPEN c_angajat;
    LOOP
    FETCH c_angajat INTO v_angajat;
    EXIT WHEN c_angajat%notfound;
    DBMS_OUTPUT.PUT_LINE(v_angajat.angajat_id || ' ' || v_angajat.nume || ' ' || v_angajat.prenume  || ' ' || v_angajat.post_id || ' ' || v_angajat.salariu || ' ' || v_angajat.data_angajarii);
    END LOOP;
    CLOSE c_angajat;
END;

-- Exercitiul 4 (mai multe inregistrari)
DECLARE
   TYPE angajat_romana IS RECORD
   (
        angajat_id angajati.employee_id%TYPE,
        prenume angajati.first_name%TYPE,
        nume angajati.last_name%TYPE,
        posta_electronica angajati.email%TYPE,
        data_angajarii angajati.hire_date%TYPE,
        salariu angajati.salary%TYPE,
        post_id angajati.job_id%TYPE
       );
    v_angajat angajat_romana;
    CURSOR c_angajat IS
    SELECT employee_id, first_name, last_name, email, hire_date, salary, job_id  FROM angajati
    WHERE last_name='Ciurca';
BEGIN
    OPEN c_angajat;
    LOOP
    FETCH c_angajat INTO v_angajat;
    EXIT WHEN c_angajat%notfound;
    DBMS_OUTPUT.PUT_LINE(v_angajat.angajat_id || ' ' || v_angajat.nume || ' ' || v_angajat.prenume  || ' ' || v_angajat.post_id || ' ' || v_angajat.salariu || ' ' || v_angajat.data_angajarii);
    END LOOP;
    CLOSE c_angajat;
END;

-- Exercitiul 5 (mai multe tipuri de atribute)
DECLARE
    TYPE info_generale IS RECORD (
        angajat_id angajati.employee_id%TYPE,
        prenume angajati.first_name%TYPE,
        nume angajati.last_name%TYPE
    );

    TYPE specific_info IS RECORD (
        posta_electronica angajati.email%TYPE,
        data_angajarii angajati.hire_date%TYPE,
        salariu angajati.salary%TYPE,
        post_id angajati.job_id%TYPE
    );

    v_general info_generale;

    v_specific specific_info;

    CURSOR c_angajat IS
        SELECT employee_id, first_name, last_name, email, hire_date, salary, job_id FROM angajati
        WHERE employee_id=236;

BEGIN
    OPEN c_angajat;
    FETCH c_angajat INTO v_general.angajat_id, v_general.nume, v_general.prenume, v_specific.posta_electronica, v_specific.data_angajarii, v_specific.salariu, v_specific.post_id;
    CLOSE c_angajat;

    DBMS_OUTPUT.PUT_LINE(v_general.angajat_id || ' ' || v_general.nume || ' ' || v_general.prenume  || ' ' || v_specific.post_id || ' ' || v_specific.salariu);
END;

-- Exercitiul 6 (variabile calculate)
DECLARE
    TYPE info_angajat IS RECORD (
        angajat_id angajati.employee_id%TYPE,
        nume_complet VARCHAR2(100),
        post_id angajati.job_id%TYPE,
        salariu_lunar NUMBER
    );
    
    v_angajat info_angajat;
    CURSOR c_angajat IS
        SELECT employee_id, first_name || ' ' || last_name as full_name, job_id, salary / 12 as monthly_salary FROM angajati
        WHERE employee_id=236;
BEGIN
    OPEN c_angajat;
    FETCH c_angajat INTO v_angajat.angajat_id, v_angajat.nume_complet, v_angajat.post_id, v_angajat.salariu_lunar;
    CLOSE c_angajat;
    
    DBMS_OUTPUT.PUT_LINE(v_angajat.angajat_id || ' ' || v_angajat.nume_complet || ' ' || v_angajat.post_id || ' ' || v_angajat.salariu_lunar);
END;