Skip to content

Instantly share code, notes, and snippets.

@ciurca

ciurca/ex1.plsql

Last active January 7, 2024 14:14
Show Gist options
  • Save ciurca/0c4871281763967e616f551e824aec6d to your computer and use it in GitHub Desktop.
Save ciurca/0c4871281763967e616f551e824aec6d to your computer and use it in GitHub Desktop.
Download ZIP
Laborator 6 PLSQL
Raw
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 := 'ciurcat@gmail.com';
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment