Skip to content

Instantly share code, notes, and snippets.

@ciurca

ciurca/lab7.plsql

Last active January 7, 2024 14:14
Show Gist options
  • Save ciurca/1fdf63946348d67d1aa5450eb3cb925a to your computer and use it in GitHub Desktop.
Save ciurca/1fdf63946348d67d1aa5450eb3cb925a to your computer and use it in GitHub Desktop.
Download ZIP
Laborator 7 plsql
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment