Scribd Logo
Upload

Welcome to Scribd!

  • 418 views

    Subiecte Examen Baze de Date Csie

    Uploaded by

    Christiana Tomescu
    The document contains PL/SQL code blocks that: 1. Updates client credit limits based on orders from a given year. It handles exceptions if no records are found. 2. Defines a function that counts employees in a department whose salary is above the average, handling exceptions if the department doesn't exist or has no employees. 3. Creates a procedure that displays the top 3 departments ordered by employee count.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Subiecte Examen Baze de Date Csie

    Uploaded by

    Christiana Tomescu
    418 views10 pages
    The document contains PL/SQL code blocks that: 1. Updates client credit limits based on orders from a given year. It handles exceptions if no records are found. 2. Defines a function that counts employees in a department whose salary is above the average, handling exceptions if the department doesn't exist or has no employees. 3. Creates a procedure that displays the top 3 departments ordered by employee count.

    Original Title

    Subiecte examen baze de date csie

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains PL/SQL code blocks that: 1. Updates client credit limits based on orders from a given year. It handles exceptions if no records are found. 2. Defines a function that counts employees in a department whose salary is above the average, handling exceptions if the department doesn't exist or has no employees. 3. Creates a procedure that displays the top 3 departments ordered by employee count.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    418 views10 pages

    Subiecte Examen Baze de Date Csie

    Uploaded by

    Christiana Tomescu
    The document contains PL/SQL code blocks that: 1. Updates client credit limits based on orders from a given year. It handles exceptions if no records are found. 2. Defines a function that counts employees in a department whose salary is above the average, handling exceptions if the department doesn't exist or has no employees. 3. Creates a procedure that displays the top 3 departments ordered by employee count.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 10

    --ex1

    set serveroutput on;


    --10.50
    --11.54
    declare
    ex1 exception;
    an number;
    begin
    an :='&a';
    Update clienti set limita_credit=limita_credit*0.95 where id_client in (select id_client from comenzi where an =
    extract (year from data) ) ;
    if sql%found then dbms_output.put_line(sql%rowcount) ;
    else raise ex1;
    end if;
    exception
    when ex1 then dbms_output.put_line ( 'Nu s-a efectuat nicio modificare') ;
    end;
    /

    --ex2
    --nr de ang dintr-un anumit departament cu salariul mai mare decat media
    create or replace function fct_numara_ang (id_dep in angajati.id_departament%type)
    return number
    is
    media number;
    nr number;
    cursor a is select id_departament from departamente ;
    cursor b is select id_departament from angajati;
    da boolean:=false;
    ok boolean:=false;
    ex1 exception;
    ex2 exception;
    begin

    for i in a loop exit when a%notfound;


    if i.id_departament=id_dep then da:=true;
    end if; end loop;

    for i in b loop exit when b%notfound;


    if id_dep=i.id_departament then ok:=true;
    end if; end loop;

    if da=false then raise ex1;


    elsif da=true and ok=false
    then raise ex2;
    end if;

    select avg(salariul) into media from angajati ;


    select count(id_angajat) into nr from angajati where salariul> media and id_departament=id_dep ;

    return nr;
    exception
    when ex1 then dbms_output.put_line ('Nu exista departamentul');
    when ex2 then dbms_output.put_line ('Departamentul nu are angajati');

    end;
    /

    --3
    create or replace procedure proc_afiseaza_dep
    is
    cursor a is select id_departament, denumire_departament, fct_numara_ang(id_departament) nrA from
    departamente order by 3 desc;
    b a%rowtype;
    begin
    open a;
    loop
    fetch a into b ;
    exit when a%notfound or a%rowcount > 3 ;
    dbmbs_output.put_line ( b.id_departament||' ' || b.denumire_departament||' ' || b.nrA) ;
    end loop;
    close a;
    end; /

    --4
    begin
    execute immediate ' drop function fct_numara_ang' ;
    execute immediate 'drop procedure proc_afiseaza_ang';
    end;
    /
    --ALT NR.
    --ex 1. Sa se creeze un bloc prin care sa se adauge o coloana noua - stoc - in tabela produse, cu restrictia ca
    stocul sa fie>0.
    begin
    execute immediate
    'alter table produse
    add stoc number
    constraint verifica_stoc
    check (stoc>0)' ;
    end;
    /

    --2. Creeati un bloc prin care sa se adauge valori in coloana adaugata anterior astfel: daca produsul a mai fost
    comandat, stoc=20, altfel stoc=30;
    BEGIN
    UPDATE produse
    SET stoc=(CASE
    WHEN id_produs IN (SELECT id_produs
    FROM rand_comenzi) THEN 20
    ELSE 30
    END);
    IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    END IF;
    END;
    /
    --3. Sa se creeze un trigger care sa nu permita comanda unui produs in cantitate mai mare decat stocul aferent.
    create or replace trigger check_stock
    before insert on rand_comenzi
    for each row
    declare
    v_stoc NUMBER;
    BEGIN
    SELECT stoc INTO v_stoc FROM produse
    WHERE id_produs=:new.id_produs;
    IF :new.cantitate>v_stoc THEN RAISE_APPLICATION_ERROR(-20000,'eroare');
    ELSE UPDATE produse SET stoc=stoc-:new.cantitate
    WHERE id_produs=:new.id_produs;
    END IF;
    END;
    /
    --4. SA SE CREEZE O PROCEDURA CARE SA RETURNEZE NR DE ANGAJATI CU SALARIU>MEDIA CARE FAC PARTE
    DINTR-UN DEPARTAMENT AL CARUI ID ESTE DAT CA PARAM.
    --TRATATI PRIN EXCEPTII CAZUL IN CARE NU EXISTA DEPARTAMENTE, PRECUM SI CAZUL IN CARE NU EXISTA
    ANGAJATI
    create or replace procedure nr_ang (nr out number, id_dep in departamente.id_departament%type)
    is
    media number;
    ex1 exception;
    ex2 exception;
    da boolean:= false;
    ok boolean:= false;
    cursor a is select id_departament from departamente;
    cursor b is select id_departament from angajati;
    begin

    for i in a loop
    exit when a%notfound;
    if i.id_departament=id_dep then da:=true;
    end if;
    end loop;

    for i in b loop exit when b%notfound;


    if id_dep=i.id_departament then ok:=true;
    end if;
    end loop;

    if da=false then raise ex1;


    elsif da=true and ok=false
    then raise ex2;
    end if;

    select avg(salariul) into media from angajati where id_departament= id_dep ;


    select count(id_angajat) into nr from angajati where id_departament = id_dep and salariul>media;
    dbms_output.put_line(nr) ;

    exception
    when ex1 then dbms_output.put_line ('Nu exista departamentul');
    when ex2 then dbms_output.put_line ('Departamentul nu are angajati');

    end;
    /

    COMANDA VALOROASA

    set serveroutput on

    --1

    begin

    execute immediate 'create table info_ang (id_angajat number(6),

    nume varchar(20),functie_curenta varchar(20),salariul number(8,2),

    numar_functii_anterioare number(6))';

    end;

    --1**
    create or replace procedure proc_comanda_valoroasa
    (nr out comenzi.nr_comanda%type,v_data out comenzi.data%type,
    v_id in angajati.id_angajat%type) is
    cursor a is select id_angajat from angajati;
    cursor b is select id_angajat from comenzi;
    maxim number;
    ex1 exception;
    ex2 exception;
    da boolean:=false;
    ok boolean:=false;
    begin
    --aici verif daca exista angajatul
    for i in a loop exit when a%notfound;
    if v_id=i.id_angajat then da:=true;
    end if; end loop;
    --aici verific daca angajatul a dat comenzi
    for i in b loop exit when b%notfound;
    if v_id=i.id_angajat then ok:=true;
    end if; end loop;
    if da=false then raise ex1;
    elsif da=true and ok=false
    then raise ex2;
    else
    --fac maximul
    select max(sum(pret*cantitate)) into maxim
    from rand_comenzi rc, comenzi c where c.nr_comanda=rc.nr_comanda
    and c.id_angajat=v_id group by c.nr_comanda;
    --verific care e comanda cu val maxima
    select c.nr_comanda,c.data into nr,v_data
    from comenzi c,rand_comenzi rc where
    c.nr_comanda=rc.nr_comanda and c.id_angajat=v_id
    group by c.nr_comanda,c.data
    having sum(pret*cantitate)=maxim;
    --insert into info_ang values(nr,v_data,maxim,v_id);
    end if;
    exception when ex1 then nr:=-1;--nu exista angajatul
    when ex2 then nr:=-2;--angajatu exista da n a dat comennzi
    end proc_comanda_valoroasa;

    --2
    --numara angajatii care sunt in departamentul ang cu id primit ca parametru

    create or replace procedure proc_numara_ang

    (v_funct out angajati.id_functie%type,nr out number,v_id in angajati.id_angajat%type)

    is

    cursor a is select id_angajat from angajati;

    cursor b is select id_angajat from istoric_functii;

    da boolean:=false;

    ok boolean:=false;

    ex1 exception;

    ex2 exception;
    begin

    for i in a loop exit when a%notfound;

    if i.id_angajat=v_id then da:=true;

    end if; end loop;

    for i in b loop exit when b%notfound;

    if i.id_angajat=v_id then ok:=true;

    end if; end loop;

    if da=false

    --daca nu exista id raise ex1

    then raise ex1;

    elsif da=true and ok=false

    --daca exista id dar nu e in istoric funct raise ex2

    then raise ex2;

    else

    select id_functie into v_funct from angajati where id_angajat=v_id;

    select count(id_angajat) into nr from istoric_functii where id_angajat=v_id;

    end if;

    exception when ex1 then

    --dbms_output.put_line('Nu exista acest angajat.');

    nr:=-1;

    when ex2 then

    --dbms_output.put_line('Angajatul nu si-a schimbat functia.');

    nr:=-2;

    when others then

    dbms_output.put_line(sqlerrm);

    end proc_numara_ang;
    declare

    v_funct angajati.id_functie%type;nr number;

    begin

    proc_numara_ang(v_funct,nr,108);

    dbms_output.put_line(v_funct);

    dbms_output.put_line(nr);

    end;

    --3
    --afisezi departamentele care au mai mult de 2 angajati
    create or replace procedure proc_afiseaza_ang

    is

    v_funct angajati.id_functie%type;
    nr number;

    cursor c is select id_angajat,nume,salariul from angajati;

    begin

    for i in c loop exit when c%notfound;

    proc_numara_ang(v_funct,nr,i.id_angajat);

    if nr>=2

    then

    dbms_output.put_line('Angajatul: '||i.id_angajat||' '||i.nume||' '||i.salariul);

    insert into info_ang values(i.id_angajat,i.nume,v_funct,i.salariul,nr);

    end if;

    end loop;

    end proc_afiseaza_ang;

    execute proc_afiseaza_ang;

    select * from info_ang;


    --4

    create or replace trigger trg_gestiune_istoric after

    update of id_functie on angajati

    for each row

    begin

    update info_ang set

    numar_functii_anterioare=numar_functii_anterioare+1

    where id_angajat=:old.id_angajat;

    end;

    --alter trigger trg_gestiune_istoric enable;

    --AD_VP 101

    --daca rulez ce este aici nr_functii_ant devine 3 din 2 pt id 101

    update angajati set id_functie='AD_PRES' where id_angajat=101;

    select * from info_ang;

    rollback;

    --alter trigger trg_gestiune_istoric disable;


    set serveroutput on
    --Sa se scrie o functie care sa returneze pretul de lista pentru produsul comandat
    create or replace function functie
    (v_id in produse.id_produs%type, cat in produse.categorie%type, discount in number)
    return number
    as
    ok boolean:= false;
    cursor c is select p.pret_lista,p.id_produs, p.categorie from produse p, rand_comenzi rc
    where p.id_produs=rc.id_produs and rc.id_produs=v_id;
    ex exception;
    begin
    for i in c loop exit when c%notfound;
    if i.id_produs=v_id
    then ok:=true;
    end if;
    end loop;
    if ok=true then
    begin
    for i in c loop exit when c%notfound ;
    if i.categorie=cat
    then return i.pret_lista*(1-discount);
    else return i.pret_lista;
    end if;
    end loop ;
    end;
    else
    raise ex;
    end if;
    exception
    when ex then return 0;
    when no_data_found then return -1;
    when other then return -2;
    end;
    /

    You might also like