Sa se specifice daca o galerie este mare, medie sau mica dupa cum numarul operelor de arta expuse īn galeria respectiva este mai mare decāt 200, cuprins īntre 100 si 200 sau mai mic decāt 100.

SET SERVEROUTPUT ON

DEFINE p_cod_gal = 753

DECLARE

v_cod_galerie opera.cod_galerie%TYPE := &p_cod_gal;

v_numar NUMBER(3) := 0;

v_comentariu VARCHAR2(10);

BEGIN

SELECT COUNT(*)

INTO v_numar

FROM opera

WHERE cod_galerie = v_cod_galerie;

IF v_numar < 100 THEN

v_comentariu := 'mica';

ELSIF v_numar BETWEEN 100 AND 200 THEN

v_comentariu := 'medie';

ELSE

v_comentariu := 'mare';

END IF;

DBMS_OUTPUT.PUT_LINE('Galeria avand codul '||

v_cod_galerie ||' este de tip '|| v_comentariu);

END;

/

ALTER TABLE opera

Īn exemplul care urmeaza, comanda FORALL insereaza un numar arbitrar de linii la fiecare iteratie, iar dupa fiecare iteratie atributul %BULK_ROWCOUNT returneaza numarul acestor linii inserate.

SET SERVEROUTPUT ON

DECLARE

TYPE alfa IS TABLE OF NUMBER;

beta alfa;

BEGIN

SELECT cod_artist BULK COLLECT INTO beta FROM artist;

FORALL j IN 1..beta.COUNT

INSERT INTO tab_art

SELECT cod_artist,cod_opera

FROM opera

WHERE cod_artist = beta(j);

FOR j IN 1..beta.COUNT LOOP

DBMS_OUTPUT.PUT_LINE ('Pentru artistul avand codul ' ||

beta(j) || ' au fost inserate ' || SQL%BULK_ROWCOUNT(j) || inregistrari (opere de arta)');

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Numarul total de inregistrari inserate este '||SQL%ROWCOUNT);

END;

Pentru toti artistii care au opere de arta expuse īn muzeu sa se insereze īn tabelul temp informatii referitoare la numele acestora si anul nasterii.

DECLARE

v_nume artist.nume%TYPE;

v_an_nas artist.an_nastere%TYPE;

CURSOR info IS

SELECT DISTINCT nume, an_nastere

FROM artist;

BEGIN

OPEN info;

LOOP

FETCH info INTO v_nume, v_an_nas;

EXIT WHEN info%NOTFOUND;

INSERT INTO temp

VALUES (v_nume || TO_CHAR(v_an_nas));

END LOOP;

CLOSE info;

COMMIT;

Sa se calculeze, utilizānd un ciclu cursor cu subcereri, valoarea operelor de arta expuse īntr-o galerie al carei cod este introdus de la tastatura. De asemenea, sa se obtina media valorilor operelor de arta expuse īn galeria respectiva.

SET SERVEROUTPUT ON

ACCEPT p_galerie PROMPT 'Dati codul galeriei:'

DECLARE

v_cod_galerie galerie.cod_galerie%TYPE:=&p_galerie;

  val    NUMBER;

  media  NUMBER;

  i      INTEGER;

BEGIN

val:=0;

i:=0;

FOR numar_opera IN

(SELECT cod_opera, valoare

FROM opera

WHERE cod_galerie = v_cod_galerie) LOOP

val := val + numar_opera.valoare;

i := i+1;

END LOOP;--īnchidere implicita

DBMS_OUTPUT.PUT_LINE('Valoarea operelor de arta din

galeria cu numarul ' || TO_CHAR(v_cod_galerie) || '

este ' || TO_CHAR(val));

IF i=0 THEN

DBMS_OUTPUT.PUT_LINE('Galeria nu are opere de arta');

ELSE

media := val/i;

DBMS_OUTPUT.PUT_LINE('Media valorilor operelor de arta

din galeria cu numarul ' || TO_CHAR(v_cod_galerie)

|| ' este ' || TO_CHAR(media));

END IF;

END;

/

Utilizānd un cursor parametrizat sa se obtina codurile operelor de arta din fiecare sala, identificatorul salii si al galeriei. Rezultatele sa fie inserate īn tabelul mesaje.

DECLARE

v_cod_sala sala.cod_sala%TYPE;

v_cod_galerie galerie.cod_galerie%TYPE;

v_car VARCHAR2(75);

CURSOR sala_cursor IS

SELECT cod_sala,cod_galerie

FROM sala;

CURSOR ope_cursor (v_id_sala NUMBER,v_id_galerie NUMBER) IS

SELECT cod_opera || cod_sala || cod_galerie

FROM opera

WHERE cod_sala = v_id_sala

AND cod_galerie = v_id_galerie;

BEGIN

OPEN sala_cursor;

LOOP

FETCH sala_cursor INTO v_cod_sala,v_cod_galerie;

EXIT WHEN sala_cursor%NOTFOUND;

IF ope_cursor%ISOPEN THEN

CLOSE ope_cursor;

END IF;

OPEN ope_cursor (v_cod_sala, v_cod_galerie);

LOOP

FETCH ope_cursor INTO v_car;

EXIT WHEN ope_cursor%NOTFOUND;

INSERT INTO mesaje (rezultat)

VALUES (v_car);

END LOOP;

CLOSE ope_cursor;

END LOOP;

CLOSE sala_cursor;

COMMIT;

Sa se dubleze valoarea operelor de arta pictate pe pānza care au fost achizitionate īnainte de 1 ianuarie 1956.

DECLARE

CURSOR calc IS

SELECT *

FROM opera

WHERE material = 'panza'

AND data_achizitie <= TO_DATE('01-JAN-56','DD-MON-YY')

FOR UPDATE OF valoare NOWAIT;

BEGIN

FOR x IN calc LOOP

UPDATE opera

SET valoare = valoare*2

WHERE CURRENT OF calc;

END LOOP;

-- se permanentizeaza actiunea si se elibereaza blocarea

COMMIT;

Sa se defineasca un cursor care furnizeaza codurile operelor expuse īn cadrul unei expozitii avānd un cod specificat (val_cod) si care se desfasoara īntr-o localitate precizata (val_oras). Sa se afiseze data cānd a avut loc vernisajul acestei expozitii.

Īn acest caz cursorul returneaza doua coloane, cea de-a doua coloana fiind un cursor imbricat.

CURSOR alfa (val_cod NUMBER, val_oras VARCHAR2(20)) IS

SELECT l.datai,

CURSOR (SELECT d.cod_expo,

CURSOR (SELECT f.cod_opera

FROM figureaza_in f

WHERE f.cod_expo=d.cod_expo) AS xx

FROM expozitie d

WHERE l.cod_expo = d.cod_expo) AS yy

FROM locped l

Sa se creeze o procedura stocata care micsoreaza cu o cantitate data (cant) valoarea politelor de asigurare emise de firma ASIROM.

CREATE OR REPLACE PROCEDURE mic (cant IN NUMBER) AS

BEGIN

UPDATE politaasig

SET valoare = valoare - cant

WHERE firma = 'ASIROM

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR (-20010,'nu exista ASIROM');

Sa se creeze o procedura locala prin care se insereaza informatii īn tabelul editata_de.

DECLARE

PROCEDURE editare

(v_cod_sursa editata_de.cod_sursa%TYPE,

v_cod_autor editata_de.cod_autor%TYPE) IS

BEGIN

INSERT INTO editata_de VALUES (v_cod_sursa,v_cod_autor);

END;

BEGIN

.

editare(75643, 13579); .

Sa se creeze o functie stocata care determina numarul operelor de arta realizate pe pānza, ce au fost achizitionate la o anumita data.

CREATE OR REPLACE FUNCTION numar_opere (v_a IN opera.data_achizitie%TYPE)

RETURN NUMBER AS alfa NUMBER;

BEGIN

SELECT COUNT(ROWID) INTO alfa

FROM opera

WHERE material='panza' AND data_achizitie = v_a;

RETURN alfa;

Sa se afiseze operele de arta (titlu, valoare, stare) a caror valoare este mai mare decāt valoarea medie a tuturor operelor de arta din muzeu.

CREATE OR REPLACE FUNCTION valoare_medie

RETURN NUMBER IS

  v_val_mediu opera.valoare%TYPE;

BEGIN

SELECT AVG(valoare)

INTO v_val_mediu

FROM opera;

RETURN v_val_mediu;

END;

Referirea acestei functii īntr-o comanda SQL se poate face prin:

SELECT titlu, valoare, stare

FROM opera

Programul de lucru la administratia muzeului este de luni pāna vineri, īn intervalul (8:00 a.m. - 10:00 p.m.). Sa se construiasca un declansator la nivel de instructiune care īmpiedica orice activitate asupra unui tabel al bazei de date, īn afara acestui program.

CREATE OR REPLACE PROCEDURE verifica IS

BEGIN

IF ((TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6)

AND

TO_DATE(TO_CHAR(SYSDATE,'hh24:mi'), 'hh24:mi')

NOT BETWEEN TO_DATE('08:00','hh24:mi')

AND TO_DATE('22:00','hh24:mi'))

THEN

RAISE_APPLICATION_ERROR (-27733, 'nu puteti reactualiza acest tabel deoarece sunteti in afara programului');

END verifica;

/

CREATE OR REPLACE TRIGGER BIUD_tabel1

BEFORE INSERT OR UPDATE OR DELETE ON tabel1

BEGIN

verifica;

Sa se implementeze cu ajutorul unui declansator constrāngerea ca valorile operelor de arta nu pot fi reduse (trei variante).

Varianta 1

CREATE OR REPLACE TRIGGER verifica_valoare

  BEFORE UPDATE OF valoare ON opera

  FOR EACH ROW

  WHEN (NEW.valoare < OLD.valoare)

BEGIN

  RAISE_APPLICATION_ERROR (-20222, 'valoarea unei opere de           arta nu poate fi micsorata');

Se presupune ca pentru fiecare galerie exista doua cāmpuri (min_valoare si max_valoare) īn care se retin limitele minime si maxime ale valorile operelor din galeria respectiva. Sa se implementeze cu ajutorul unui declansator constrāngerea ca, daca aceste limite s-ar modifica, orice opera de arta trebuie sa aiba valoarea cuprinsa īntre noile limite.

CREATE OR REPLACE TRIGGER verifica_limite

BEFORE UPDATE OF min_valoare, max_valoare ON galerie

FOR EACH ROW

DECLARE

v_min_val opera.valoare%TYPE;

v_max_val opera.valoare%TYPE;

e_invalid EXCEPTION;

BEGIN

SELECT MIN(valoare), MAX(valoare)

INTO v_min_val, v_max_val

FROM opera

WHERE cod_galerie = :NEW.cod_galerie;

IF (v_min_val < :NEW.min_valoare) OR

(v_max_val > :NEW.max_valoare) THEN

RAISE e_invalid;

END IF;

EXCEPTION

WHEN e_invalid THEN

RAISE_APPLICATION_ERROR (-20567, 'Exista opere de arta

ale caror valori sunt in afara domeniului permis');

Sa se implementeze cu ajutorul unui declansator restrictia ca īntr-o sala pot sa fie expuse maximum 10 opere de arta.

CREATE OR REPLACE TRIGGER TrLimitaopere

BEFORE INSERT ON opera

FOR EACH ROW

DECLARE

v_Max_opere CONSTANT NUMBER := 10;

v_opere_curente NUMBER;

BEGIN

SELECT COUNT(*) INTO v_opere_curente

FROM opera

WHERE cod_sala = :NEW.cod_sala;

IF v_opere_curente + 1 > v_Max_opere THEN

RAISE_APPLICATION_ERROR(-20000,'Prea multe opere de

arta in sala avand codul ' || :NEW.cod_sala);

END IF;

END TrLimitaopere;

Cu toate ca declansatorul pare sa produca lucrul dorit, totusi dupa o reactualizare a tabelului opera īn urmatoarea maniera:

INSERT INTO opera (cod_opera, cod_sala)

VALUES (756893, 10);

se obtine urmatorul mesaj de eroare:

ORA-04091: tabel opera is mutating, trigger/function may not see it

ORA-04088: error during execution of trigger TrLimitaopere

Sa se creeze un declansator care:

a)       daca este eliminata o sala, va sterge toate operele expuse īn sala respectiva;

b)       daca se schimba codul unei sali, va modifica aceasta valoare pentru fiecare opera de arta expusa īn sala respectiva.

CREATE OR REPLACE TRIGGER sala_cascada

BEFORE DELETE OR UPDATE OF cod_sala ON sala

FOR EACH ROW

BEGIN

IF DELETING THEN

DELETE FROM opera

WHERE cod_sala = :OLD.cod_sala;

END IF;

IF UPDATING AND :OLD.cod_sala != :NEW.cod_sala THEN

UPDATE opera

SET cod_sala = :NEW.cod_sala

WHERE cod_sala = :OLD.cod_sala;

END IF;

Sa se implementeze un declansator care nu permite acceptarea īn muzeu a operelor de arta avānd valoarea mai mica de 100000$.

CREATE OR REPLACE TRIGGER minim_valoare

BEFORE INSERT ON opera

FOR EACH ROW

BEGIN

IF :NEW.valoare < 100000 THEN

RAISE_APPLICATION_ERROR

(-20005,'operele de arta trebuie sa aiba valoare

mai mare de 100000$');

END IF;

END;

Pe statia client poate fi scris un program care detecteaza si trateaza eroarea.

DECLARE

/* declarare exceptie */

nu_accepta EXCEPTION;

/* asociaza nume,codului eroare folosit in trigger */

PRAGMA EXCEPTION_INIT(nu_accepta,-20005);

BEGIN

/* incearca sa inserezi */

INSERT INTO opera .;

EXCEPTION

/* tratare exceptie */

WHEN nu_accepta THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

/* SQLERRM va returna mesaj din RAISE_APPLICATION_ERROR */

. Sa se creeze tabelul test_zod avānd aceeasi structura si date ca si tabelul salariat_zod. Sa se adauge īn tabelul test_zod un nou cāmp numit adresa de tip tablou imbricat. Acest tablou are trei componente īn care pentru fiecare salariat sunt continute strada, blocul si sectorul de resedinta. Sa se insereze o linie noua īn tabel. Sa se insereze o valoare pentru adresa salariatului avānd codul 1. Apoi pentru fiecare salariat sa se listeze codul si adresa. Sa se stearga tabelul test_zod.

CREATE TABLE test_zod AS SELECT cod_ang, nume FROM salariat_zod;

CREATE OR REPLACE TYPE tip_adresa_zod IS TABLE OF VARCHAR(50);

/

ALTER TABLE test_zod

ADD (adresa tip_adresa_zod) NESTED TABLE adresa STORE AS tabel_adresa_zod;

INSERT INTO test_zod (cod_ang,nume,adresa)

VALUES (90, 'Florin',tip_adresa_zod('Str1', 'V7', '5'));

update test_zod

SET adresa = tip_adresa_zod('Str2', 'M2', '6')

WHERE cod_ang=90;

SELECT a.cod_ang, b.*

FROM test_zod a, TABLE (a.adresa) b;

Sa se afiseze numele si salariul salariatilor care au salariul mai mare decāt o valoare introdusa de la tastatura. Se va folosi un ciclu cursor cu subcereri

Obs: Īn plus, fata de un ciclu cursor simplu, acest tip de cursor nici nu trebuie declarat.

SET SERVEROUTPUT ON

ACCEPT p_sal PROMPT 'Dati o valoare: '

DECLARE

v_sal number:=&p_sal;

BEGIN

FOR v_c7 IN (SELECT last_name, salary FROM employees WHERE salary>v_sal) LOOP

DBMS_OUTPUT.PUT_LINE('Salariatul '|| v_c7.last_name||' are salariul ' ||v_c7.salary);

END LOOP;

Sa se declare un cursor parametrizat (parametrii fiind var_salary si var_dept) prin care sa se afiseze numele si salariul salariatilor pentru care salary<var_salary si department_id<>var_dept. Rezolvarea se va face īn doua moduri (cursor explicit si ciclu cursor)

SET SERVEROUTPUT ON

DECLARE

v_nume employees.last_name%TYPE;

v_sal employees.salary%TYPE;

CURSOR c8_zod (var_salary NUMBER, var_dept NUMBER) IS

SELECT last_name, salary

FROM employees

WHERE salary<var_salary AND department_id<>var_dept;

BEGIN

OPEN c8_zod (2500,80);

LOOP

FETCH c8_zod INTO v_nume,v_sal;

EXIT WHEN c8_zod %NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Salariatul '|| v_nume||' are salariul ' ||v_sal);

END LOOP;

CLOSE c8_zod;

DBMS_OUTPUT.PUT_LINE(' ** ** ******');

FOR v_c8 IN c8_zod(2500,80) LOOP

DBMS_OUTPUT.PUT_LINE('Salariatul '|| v_c8.last_name||' are salariul ' ||v_c8.salary);

END LOOP;

END;

Sa se mareasca cu 1000 salariile angajatilor care au fost angajati īn 2000 (din tabelul emp_zod). Se va folosi un cursor SELECT FOR UPDATE.

Observatie Uneori este necesara blocarea liniilor īnainte ca acestea sa fie sterse sau reactualizate. Blocarea se poate realiza (atunci cānd cursorul este deschis) cu ajutorul comenzii SELECT care contine clauza FOR UPDATE.

SELECT last_name, hire_date, salary

FROM emp_zod

WHERE TO_CHAR(hire_date, 'yyyy') = 2000;

DECLARE

CURSOR c9_zod IS

SELECT *

FROM emp_zod

WHERE TO_CHAR(hire_date, 'YYYY') = 2000

FOR UPDATE OF salary NOWAIT;

BEGIN

FOR v_c9 IN c9_zod LOOP

UPDATE emp_zod

SET salary= salary+1000

WHERE CURRENT OF c9_zod;

END LOOP;

END;

/

SELECT last_name, hire_date, salary

FROM emp_zod

WHERE TO_CHAR(hire_date, 'yyyy') = 2000;

Sa se declare o procedura īntr-un bloc PL/SQL anonim prin care sa se introduca īn tabelul emp_zod o noua īnregistrare de forma: employee_id, last_name, email, hire_date, job_id (procedura locala).

DECLARE

PROCEDURE p1_zod

(v_cod emp_zod.employee_id %TYPE,

v_nume emp_zod.last_name %TYPE,

v_email emp_zod.email %TYPE,

v_data emp_zod.hire_date%TYPE,

v_job emp_zod.job_id %TYPE)

IS

BEGIN

INSERT INTO emp_zod( employee_id, last_name, email, hire_date, job_id

VALUES (v_cod, v_nume, v_email, v_data, v_job);

END;

BEGIN

p1_zod(700, 'Aly' , '[email protected]', SYSDATE, 'economist');

END;

SELECT employee_id, last_name, email, hire_date, job_id

FROM emp_zod;

Sa se declare o procedura locala care are parametrii:

- rezultat (parametru de tip OUT) de tip last_name din employees;

- comision (parametru de tip IN) de tip commission_pct din employees, initializat cu NULL;

- cod (parametru de tip IN) de tip employee_id din employees, initializat cu NULL.

Daca comisionul nu este NULL atunci īn rezultat se va memora numele salariatului care are comisionul respectiv. Īn caz contrar, īn rezultat se va memora numele salariatului al carui cod are valoarea data īn apelarea procedurii.

SET SERVEROUTPUT ON

DECLARE

nume employees.last_name%TYPE;

PROCEDURE p6_zod (rezultat OUT employees.last_name% TYPE,

comision IN employees.commission_pct %TYPE:=NULL,

cod IN employees.employee_id %TYPE:=NULL)

IS

BEGIN

IF (comision IS NOT NULL) THEN

SELECT last_name

INTO rezultat

FROM employees

WHERE commission_pct = comision;

DBMS_OUTPUT.PUT_LINE('numele salariatului care are comisionul '||comision||' este

'||rezultat);

ELSE

SELECT last_name

INTO rezultat

FROM employees

WHERE employee_id =cod;

DBMS_OUTPUT.PUT_LINE('numele salariatului avand codul '||cod||' este '||rezultat);

END IF;

END;

BEGIN

p6_zod (nume,0.4);

p6_zod (nume,cod=>200);

END;

/

SET SERVEROUTPUT OFF

-- Afiseaza:

numele salariatului care are comisionul .4 este Russell

. Sa se creeze un declansator la nivel de instructiune care sa permita lucrul asupra tabelului emp_zod (INSERT, UPDATE, DELETE) decāt īn intervalul de ore 8:00 - 20:00, de luni pāna sāmbata.

CREATE OR REPLACE TRIGGER trig1_zod

BEFORE INSERT OR UPDATE OR DELETE ON emp_zod

BEGIN

IF (TO_CHAR(SYSDATE,'D') = 1) OR (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN 8 AND 20)

THEN

RAISE_APPLICATION_ERROR (-20001, 'tabelul emp_zod nu poate fi reactualizat');

-- numarul erorii trebuie cuprins intre -20999si -20000

END IF;

END;

/

Sa se creeze un declansator la nivel de linie (FOR EACH ROW) prin care sa nu se permita micsorarea salariilor angajatilor din tabelul emp_zod.

Varianta 1

CREATE OR REPLACE TRIGGER trig2_zod

BEFORE UPDATE OF salary ON emp_zod

FOR EACH ROW

BEGIN

IF (:NEW.salary < :OLD.salary) THEN

    RAISE_APPLICATION_ERROR (-20002, 'salariul nu poate fi micsorat');

END IF;

END;

/

UPDATE emp_zod

SET salary=salary-100;

Sa se creeze o vizualizare care sa contina date despre fiecare salariat (employee_id, last_name, email, hire_date, job_id, salary, department_id) si departamentul īn care lucreaza acesta (department_id, department_name, plati).

Sa se defineasca un declansator (declansator INSTEAD OF) prin care reactualizarile ce au loc asupra vizualizarii se propaga automat īn tabelele de baza.

CREATE OR REPLACE VIEW v_join_zod AS

SELECT employee_id, last_name, email, hire_date, job_id, salary, e.department_id,

department_name, plati

FROM emp_zod e, dept_zod d

WHERE e.department_id = d.department_id;

SELECT column_name, updatable

FROM user_updatable_columns

WHERE table_name = UPPER('v_join_zod');

CREATE OR REPLACE TRIGGER trig5_zod

INSTEAD OF INSERT OR DELETE OR UPDATE ON v_join_zod

FOR EACH ROW

BEGIN

IF INSERTING THEN

-- inserarea in vizualizare determina inserarea in emp_zod si reactualizarea in dept_zod

INSERT INTO dept_zod(department_id, department_name, plati)

VALUES (:NEW.department_id, :NEW.department_name, :NEW.salary);

INSERT INTO emp_zod (employee_id, last_name, email, hire_date, job_id, salary,

department_id)

VALUES (:NEW.employee_id, :NEW.last_name, :NEW.email, :NEW.hire_date,

:NEW.job_id, :NEW.salary, :NEW.department_id);

ELSIF DELETING THEN

-- stergerea unui salariat din vizualizare determina stergerea din emp_zod si reactualizarea in dept_zod (am considerat ca nu sterg departamentul)

DELETE FROM emp_zod

WHERE department_id = :OLD.department_id;

UPDATE dept_zod

SET plati = plati - :OLD.salary

WHERE department_id = :OLD.department_id;

ELSIF UPDATING ('salary') THEN

/* modificarea unui salariu din vizualizare determina modificarea salariului in emp_zod si reactualizarea in dept_zod */

UPDATE emp_zod

SET salary = :NEW.salary

WHERE employee_id = :OLD.employee_id;

UPDATE dept_zod

SET plati = plati - :OLD.salary + :NEW.salary

WHERE department_id = :OLD.department_id;

ELSIF UPDATING ('department_id') THEN

/* modificarea unui cod de departament din vizualizare determina modificarea codului in salariat si reactualizarea in departament */

UPDATE emp_zod

SET department_id = :NEW.department_id

WHERE employee_id = :OLD.employee_id;

UPDATE dept_zod

SET plati = plati - :OLD.salary

WHERE department_id = :OLD.department_id;

UPDATE dept_zod

SET plati = plati + :NEW.salary

WHERE department_id = :NEW.department_id;

END IF;

Sa creeze un declansator prin care sa nu se permita stergerea informatiilor din tabelul emp_zod de catre utilizatorul curent (declansator la nivel de baza de date).

CREATE OR REPLACE TRIGGER trig6_zod

BEFORE DELETE ON emp_zod

BEGIN

IF USER= UPPER('g233') THEN

RAISE_APPLICATION_ERROR(-20900,'Nu ai voie sa stergi!');

END IF;

END;

Sa se creeze un tabel ce contine urmatoarele cāmpuri: user_id, nume_bd, eveniment_sis, nume_obj, data. Apoi, sa se creeze un declansator sistem (la nivel de schema) care sa introduca date īn acest tabel dupa ce utilizatorul a folosit o comanda LDD.

CREATE TABLE info_zod

(user_id VARCHAR2(30),

nume_bd VARCHAR2(50),

eveniment_sis VARCHAR2(20),

nume_obj VARCHAR2(30),

data DATE);

CREATE OR REPLACE TRIGGER trig7_zod

AFTER CREATE OR DROP OR ALTER ON SCHEMA

BEGIN

INSERT INTO info_zod

VALUES (SYS.LOGIN_USER, SYS.DATABASE_NAME, SYS.SYSEVENT,

SYS.DICTIONARY_OBJ_NAME, SYSDATE);

END;

/

CREATE INDEX ind_zod ON salariat_zod(nume);

DROP INDEX ind_zod;

SELECT * FROM info_zod;

Sa se creeze urmatoarele tabele:

CREATE TABLE cititor_zod (

codec CHAR (5),

nume VARCHAR2 (20),

dep NUMBER (3),

PRIMARY KEY (codec));

CREATE TABLE domeniu_zod (

coded CHAR (5),

dom VARCHAR2 (30),

PRIMARY KEY (coded));

CREATE TABLE carte_zod (

codel CHAR (5),

titlu VARCHAR2 (30),

autor VARCHAR2 (20),

pret NUMBER (8,2),

nr_ex NUMBER (3),

coded CHAR (5),

PRIMARY KEY (codel),

FOREIGN KEY (coded) REFERENCES domeniu_zod (coded) ON DELETE CASCADE);

CREATE TABLE imprumuta_zod (

codec CHAR (5),

codel CHAR (5),

data_im DATE DEFAULT SYSDATE,

data_res DATE,

data_ef DATE DEFAULT SYSDATE+10,

PRIMARY KEY (codec, codel, data_im),

FOREIGN KEY (codec) REFERENCES cititor_zod (codec) ON DELETE CASCADE,

FOREIGN KEY (codel) REFERENCES carte_zod (codel) ON DELETE CASCADE);

2. Sa se insereze date īn aceste tabele.

INSERT INTO cititor_zod VALUES (100, 'VANEC LILIANA', 10);

Sa se genereze un raport care sa contina date despre angajati. Setarile de pagina vor fi urmatoarele:

-marimea paginii 20 de linii

-marimea liniei 50 de caractere :

-antetul paginii format din 'Angajati', aliniat īn centru  si 'Luna Aprilie', aliniat īn partea dreapta, bold

-subsolulpaginii format din textul 'Raport lunar', aliniat īn centru, bold.

set pagesize 20

set linesize 50

TTITLE CENTER 'Angajati' RIGHT 'Luna Martie' BOLD

BTITLE CENTER 'Raport lunar' BOLD

SELECT last_name, job_id, salary

FROM employees

Pentru fiecare angajat sa se selecteze codul departamentului, numele si job-ul. Sa se grupeze liniile rezultatului dupa cod departament, iar īn interiorul departamentului dupa job; grupurile pe departamente sa fie despartite īntre ele prin doua rānduri, iar gruparile dupa job printr-un rānd. Sa se anuleze efectele comenzii BREAK. (eu)

Break on department_id skip 2 on job_id skip 1

SELECT department_id, last_name, job_id

FROM employees

Order by department_id, job_id;

Sa se creeze un raport prin care sa se afiseze codul departamentului, numele si salariul pentru fiecare angajat, media salariilor pe departament si media salariilor pe īntreprindere.

BREAK ON department_id SKIP 2 ON REPORT

COMPUTE AVG OF salary ON department_id

COMPUTE AVG OF salary ON REPORT

SELECT department_id, last_name, salary

FROM employees

ORDER BY department_id;

CLEAR BREAK

Sa se creeze un raport prin care sa se afiseze codul departamentului, job-ul, numele si salariul pentru fiecare angajat, suma salariilor pe fiecare job, suma salariilor pe departament si suma totala a salariilor (la nivel de raport). Se vor seta coloanele īn mod corespunzator informatiilor afisate. (eu)

BREAK ON job_id SKIP 1 on department_id skip 2 ON REPORT skip 3

COMPUTE sum OF salary ON job_id

COMPUTE sum OF salary ON department_id

COMPUTE sum OF salary ON REPORT

COLUMN department_id HEADING "Departament"

COLUMN job_id HEADING "Job"

COLUMN last_name HEADING "Nume"

COLUMN salary HEADING "Salariu"

SELECT department_id, job_id, last_name, salary

FROM employees

ORDER BY department_id, job_id;

CLEAR BREAK

Sa se stearga din tabelul emp_zod toti angajatii care nu lucreaza īn departamentul 20. Sa se introduca sau sa actualizeze datele din tabelul emp_zod folosind tabelul employees.

DELETE FROM emp_zod

WHERE department_id!=20;

MERGE INTO emp_zod a

USING employees b

ON (a.employee_id = b.employee_id)

WHEN MATCHED THEN

UPDATE SET

a.first_name=b. first_name,

a.last_name=b.last_name,

a.email=b.email,

a.phone_number=b.phone_number,

a.hire_date= b.hire_date,

a.job_id= b.job_id,

a.salary = b.salary,

a.commission_pct= b.commission_pct,

a.manager_id= b.manager_id,

a.department_id= b.department_id

WHEN NOT MATCHED THEN

INSERT VALUES(b.employee_id, b.first_name, b.last_name, b.email, b.phone_number,

b.hire_date, b.job_id, b.salary, b.commission_pct, b.manager_id,

Creati tabelul salariat_zod avānd urmatoarea structura:

CREATE TABLE salariat_zod (

cod_angajat NUMBER(4) NOT NULL,

nume VARCHAR2(25),

prenume VARCHAR2(25),

functia VARCHAR2(20),

sef NUMBER(4),

data_angajarii DATE DEFAULT SYSDATE,

varsta NUMBER,

email CHAR(10),

Se dau urmatoarele valori:

Inserati īn tabelul salariat_zod prima īnregistrare din tabelul de mai sus fara sa precizati lista de coloane īn comanda

INSERT INTO salariat_zod

VALUES (1, 'Balint', 'Alexandru', 'director', NULL, '10-MAR-2005', 30, '[email protected]', 5500);

Inserati a doua īnregistrare folosind o lista de coloane din care excludeti data_angajarii si salariul care au valori implicite. Observati apoi rezultatul. (eu)

INSERT INTO salariat_zod (cod_angajat, nume, prenume, functia, sef, varsta, email)

VALUES (2, 'Iucinu', 'Andrei', 'functionar', 1, 25, '[email protected]');

Creati un script care sa permita inserarea de valori de la tastatura cu ajutorul caruia inserati urmatoarele doua īnregistrari. Construiti email-ul astfel: prima litera este din prenume concatenata cu numele (cu litere mici). (eu)

INSERT INTO salariat_zod

VALUES (&cod, '&num', '&pre', '&func', &sef, '&data', &vars, '&email', &sal);

Creati tabelul functionar_zod care sa contina functionarii din tabelul salariat_zod, avānd urmatoarele coloane: codul, numele, salariul anual si data angajarii. Verificati cum a fost creat tabelul si ce date contine. (eu)

CREATE TABLE functionar_zod AS

SELECT cod_angajat, nume, salariu*12 salariu_anual, data_angajarii

FROM salariat_zod

Adaugati o noua coloana tabelului salariat_zod care sa contina data nasterii.

ALTER TABLE salariat_zod

ADD (datan DATE);

Modificati dimensiunea coloanei nume la 30 si pe cea a salariului la 12 cu 3 zecimale.

ALTER TABLE salariat_zod

MODIFY (nume VARCHAR2(30), salariu NUMBER(12,3));

ALTER TABLE salariat_zod

stergeti si apoi creati din nou tabelul salariat_zod cu urmatoarea structura.

Observatie:

Constrāngerile de tip CHECK se pot implementa la nivel de coloana doar daca nu refera o alta coloana a tabelului.

DROP TABLE salariat_zod;

CREATE TABLE salariat_zod (

cod_ang NUMBER(4) PRIMARY KEY,

nume VARCHAR2(25) NOT NULL,

prenume VARCHAR2(25),

data_nasterii DATE,

functia VARCHAR2(9) NOT NULL,

sef NUMBER(4) REFERENCES salariat_zod (cod_ang)

data_angajarii DATE DEFAULT SYSDATE,

email VARCHAR2(20) UNIQUE,

salariu NUMBER(9,2) CHECK (salariu >

cod_dep NUMBER(4) NOT NULL,

CONSTRAINT const_c_zod CHECK (data_angajarii > data_nasterii),

CONSTRAINT const_u_zod UNIQUE (nume,prenume,data_nasterii));

stergeti tabelul salariat_zod, iar apoi recreati-l implementānd toate constrāngerile la nivel de tabel.

Observatie: Constrāngerea de tip NOT NULL se poate declara doar la nivel de coloana.

DROP TABLE salariat_zod;

CREATE TABLE salariat_zod (

cod_ang NUMBER(4),

nume VARCHAR2(25) NOT NULL,

prenume VARCHAR2(25),

data_nasterii DATE,

functia VARCHAR2(9) NOT NULL,

sef NUMBER(4),

data_angajarii DATE DEFAULT SYSDATE,

email VARCHAR2(20),

salariu NUMBER(9,2),

cod_dep NUMBER(4) NOT NULL,

CONSTRAINT ccp_zod PRIMARY KEY (cod_ang

CONSTRAINT cce_zod FOREIGN KEY (sef) REFERENCES salariat_zod (cod_ang),

CONSTRAINT cu1_zod UNIQUE (email),

CONSTRAINT cc1_zod CHECK (data_angajarii > data_nasterii),

CONSTRAINT cc2_zod CHECK (salariu > 0),

CONSTRAINT cu2_zod UNIQUE (nume,prenume,data_nasterii));

18. Creati tabelul departament_zod care sa aiba urmatoarea structura. (eu)

DROP TABLE departament_zod;

CREATE TABLE departament_zod (

cod_dep NUMBER(4) PRIMARY KEY,

nume VARCHAR2(20) NOT NULL,

oras VARCHAR2(25));

20. Īncercati sa adaugati o constrāngere de cheie externa pe cod_dep din salariat_zod. Ce observati?

ALTER TABLE salariat_zod

ADD CONSTRAINT cce2_zod FOREIGN KEY (cod_dep) REFERENCES departament_zod (cod_dep)

Imi da eroarea:

ORA-02298: cannot validate (G233.CCE2_ZOD) - parent keys not found

. stergeti constrāngerea cce2_zod. Recreati aceasta constrāngere adaugānd optiunea ON DELETE CASCADE.

ALTER TABLE salariat_zod

DROP CONSTRAINT cce2_zod

ALTER TABLE salariat_zod

ADD CONSTRAINT cce2_zod FOREIGN KEY (cod_dep) REFERENCES departament_zod (cod_dep) ON DELETE CASCADE;

stergeti departamentul 20 din tabelul departament_zod. Ce observati īn tabelul salariat_zod? Anulati modificarile.

Se rezolva ca mai sus. Se observa ca se sterge si salariatul din departamentul 20.

stergeti constrāngerea cce2_zod. Recreati aceasta constrāngere adaugānd optiunea ON DELETE SET NULL.

ALTER TABLE salariat_zod

DROP CONSTRAINT cce2_zod;

ALTER TABLE salariat_zod

Sa se creeze vizualizarea v_emp_zod care sa contina codul si numele salariatilor din tabelul emp_zod. Sa se afiseze continutul acesteia. Sa se insereze o noua īnregistrare īn aceasta vizualizare. Ce observati? Sa se stearga vizualizarea v_emp_zod.

CREATE VIEW v_emp_zod (cod, nume)

AS SELECT employee_id, last_name

FROM emp_zod;

INSERT INTO v_emp_zod

VALUES (400,'N1');

Inserarea imi va da eroarea:

ORA-01400: cannot insert NULL into ("G233"."EMP_ZOD"."EMAIL")

DROP VIEW v_emp_zod;

Sa se creeze vizualizarea v_emp_zod care sa contina codul, numele, emailul, data angajarii, salariul si codul jobului salariatilor din tabelul emp_zod. Sa se insereze o noua īnregistrare īn aceasta vizualizare. Sa se verifice ca noua īnregistrare a fost inserata si īn tabelul de baza.

CREATE VIEW v_emp_zod

AS SELECT employee_id, last_name, email, hire_date, salary,job_id FROM emp_zod;

INSERT INTO v_emp_zod VALUES (400,'N1','E1',SYSDATE,5000,'SA_REP');

SELECT employee_id, last_name, email, hire_date, salary, job_id FROM emp_zod;

9. Sa se creeze vizualizarea v_manager_zod care sa contina numele, emailul, data angajarii, salariul si codul jobului managerilor din tabelul emp_zod. Īn aceasta vizualizare nu se va permite modificarea joburilor.

CREATE VIEW v_manager_zod

AS SELECT employee_id, last_name, email, hire_date, salary,job_id

FROM emp_zod

WHERE job_id LIKE '%MAN'

WITH CHECK OPTION; // verifica ce pun in clauza where si nu ma mai lasa sa actualizez

Sa se creeze vizualizarea v_emp_info_zod asupra tabelului emp_zod care contine codul, numele, prenumele, emailul si numarul de telefon ale angajatilor companiei. Se va impune unicitatea valorilor coloanei email si constrāngerea de cheie primara pentru coloana corespunzatoare codului galeriei.

Observatie: Constrāngerile asupra vizualizarilor pot fi definite numai īn modul DISABLE NOVALIDATE. Aceste cuvinte cheie trebuie specificate la declararea constrāngerii, nefiind permisa precizarea altor stari.

CREATE VIEW v_emp_info_zod (

employee_id, first_name, last_name, email UNIQUE DISABLE NOVALIDATE, phone_number,

CONSTRAINT ccp2_zod PRIMARY KEY (employee_id) DISABLE NOVALIDATE)

AS SELECT employee_id, first_name, last_name, email phone_number

FROM emp_zod

13. Sa se adauge o constrāngere de cheie primara asupra vizualizarii v_manager_zod.

ALTER VIEW v_manager_zod

ADD CONSTRAINT ccp3_v_manager PRIMARY KEY (employee_id) DISABLE NOVALIDATE;