Model examen BDA


Multumim, Sterpu Mihai

1. Sa se creeze tabela de obiecte xml temp1. Pe baza informatiilor din tabelele case_filme, filme, distributie, actori, sa se insereze in aceasta tabela cate un document xml pentru fiecare casa de filme. De exemplu, pentru casa de filme cu id-ul “11” documentul va fi:

<CASE_FILME idcasa=”11″>
<numecasa>CASA1</numecasa>
<LISTFILME nr_filme=”2″>
<FILM codf=”1″>
<denfilm>film1</denfilm>
<LIST_ACTORI nr_actori=”3″>
<ACTOR coda=”1″ pret=”20″>
<nume>actor1</nume>
</ACTOR>
<ACTOR coda=”2″ pret=”200″>
<nume>actor2</nume>
</ACTOR>
<ACTOR coda=”3″ pret=”150″>
<nume>actor3</nume>
</ACTOR>
</LIST_ACTORI>
</FILM>
<FILM codf=”2″>
<denfilm>film2</denfilm>
<LIST_ACTORI nr_actori=”3″>
<ACTOR coda=”1″ pret=”20″>
<nume>actor1</nume>
</ACTOR>
<ACTOR coda=”4″ pret=”120″>
<nume>actor4</nume>
</ACTOR>
<ACTOR coda=”6″ pret=”240″>
<nume>actor6</nume>
</ACTOR>
</LIST_ACTORI>
</FILM>
</LISTFILME>
</CASE_FILME>

Atributele nr_filme si nr_actori reprezinta numarul de actori pe care il are casa respectiva/filmul respectiv.

2. Creati tabela normala temp2 ( idcasa, listfilme), unde idcasa reprezinta id-ul casei de filme iar listfilme obiect de tip xml. Pe baza informatiilor din tabelele case_filme si temp1, populati tabela temp2. O linie din aceasta tabela va contine id-ul unei case de filme si fragmentul xml LISTFILME asociat, din temp1.

3. Creati tabelele normale case_filme1 (idcasa, numec), filme1(codf,casa,den) si distrib1(codf, coda, nume, pret). Folosind doar tabela temp2, inserati datele corespunzatoare in aceste tabele.

4. Afisati datele din tabelele mai sus create.

5. Stergeti tabelele si tipurile de date create.

Rezolvare
———

set long 10000;
set serveroutput on;
clear screen;

drop table temp1 force;
drop table temp2 force;
drop table case_filme1;
drop table filme1;
drop table distrib1;

create table case_filme1(
idcasa number(3),
numec varchar2(10)
);

create table filme1(
codf number(3),
casa number(3),
den varchar2(20)
);

create table distrib1(
codf number(3),
coda number(3),
nume varchar2(10),
pret number(5)
);

create table temp1 of xmltype;

create table temp2(
idcasa number(3),
listfilme xmltype
);

— cerinta 1

declare
cursor c1 is
select xmlelement
(
“CASE_FILME”,
xmlattributes
(
cf.idcasa as “idcasa”
),
xmlconcat
(
xmlelement
(
“numecasa”,
trim(cf.numec)
),
xmlelement
(
“LISTFILME”,
xmlattributes
(
(
select count(*)
from filme f
where cf.idcasa = f.casa
)
as “nr_filme”
),
(
select xmlagg
(
xmlelement
(
“FILM”,
xmlattributes
(
f1.codf as “codf”
),
xmlconcat
(
xmlelement
(
“denfilm”,
trim(f1.den)
),
xmlelement
(
“LIST_ACTORI”,
xmlattributes
(
(
select count(*)
from distributie d
where f1.codf = d.codf
) as “nr_actori”
),
(
select xmlagg
(
xmlelement
(
“ACTOR”,
xmlattributes
(
dd.coda as “coda”,
dd.pret  as “pret”
),
xmlelement
(
“nume”,
trim(aa.nume)
)
)
)
from actori aa, distributie dd
where f1.codf = dd.codf and dd.coda = aa.coda
)
)
)
)
)
from filme f1
where f1.casa = cf.idcasa
)
)
)
)
from case_filme cf;
var xmltype;
begin
open c1;
loop
fetch c1 into var;
exit when c1%notfound;

insert into temp1 values(var);

end loop;
close c1;
end;
/

— cerinta 2

declare
cursor c2 is
select idcasa from case_filme;
cursor c3 (p_id number) is
select extract
(
t1.OBJECT_VALUE,
‘/CASE_FILME[@idcasa=”‘ || to_char(p_id) ||'”]/LISTFILME’
)
from temp1 t1
where existsNode
(
t1.OBJECT_VALUE,
‘/CASE_FILME[@idcasa=”‘ || to_char(p_id) ||'”]/LISTFILME’
) = 1;
v_idcasa number;
var xmltype;
begin
open c2;
loop
fetch c2 into v_idcasa;
exit when c2%notfound;

–dbms_output.put_line(‘idcasa curenta este ‘ || v_idcasa);

open c3(v_idcasa);

fetch c3 into var;
exit when c3%notfound;
/*
if var is null
then
dbms_output.put_line(‘e null’);
end if;
*/
–dbms_output.put_line(var.getClobVal());

insert into temp2 values(v_idcasa, var);
–dbms_output.put_line(‘Am inserat pentru ‘ || v_idcasa);
close c3;

end loop;
close c2;
end;
/

— cerinta 3

declare
cursor c4 is
select idcasa
from temp2 t2;
/*
cursor c5 is
select t2.idcasa,
extract
(
t2.listfilme,
‘/LISTFILME/FILM/@codf’
).getNumberVal(),
extract
(
t2.listfilme,
‘/LISTFILME/FILM/denfilm/text()’
).getStringVal()
from temp2 t2;*/

cursor c6 is
select t2.idcasa,
extract
(
t2.listfilme,
‘/LISTFILME’
) from temp2 t2;

–cursor c7 is

v_idcasa number;
v_numecasa string(10);
v_codf number;
v_denfilm string(20);
v_coda number;
v_denactor string(10);
v_pret number;
var xmltype;
v_nrfilme number;
v_nractori number;
i number;
j number;
var2 xmltype;
begin
open c4;
loop
fetch c4 into v_idcasa;
exit when c4%notfound;

— nu exista informatia numec in temp2, completam doar idcasa
insert into case_filme1(idcasa) values(v_idcasa);

end loop;
close c4;

/*
open c5;
loop
fetch c5 into v_idcasa, v_codf, v_denfilm;
exit when c5%notfound;

dbms_output.put_line(v_idcasa || ‘ ‘ || v_codf || ‘ ‘ || v_denfilm);

end loop;
close c5;*/

open c6;
loop
fetch c6 into v_idcasa, var;
exit when c6%notfound;

/*
dbms_output.put_line(v_idcasa);*/
/*
dbms_output.put_line(var.getClobVal());
dbms_output.put_line(‘Am citit’);
*/

select extract(var, ‘/LISTFILME/@nr_filme’).getNumberVal() into v_nrfilme from dual;

–dbms_output.put_line(‘Sunt ‘ || v_nrfilme);

/*
v_codf number;
v_denfilm string(20);
*/

for i in 1..v_nrfilme
loop
select extract(var, ‘/LISTFILME/FILM[‘|| i ||’]/@codf’).getNumberVal() into v_codf from dual;
select extract(var, ‘/LISTFILME/FILM[‘|| i ||’]/denfilm/text()’).getStringVal() into v_denfilm from dual;
–dbms_output.put_line(‘codul e ‘ || v_codf || ‘ ‘ || v_denfilm);

insert into filme1 values(v_codf, v_idcasa, v_denfilm);

select extract
(
t2.listfilme,
‘/LISTFILME/FILM[@codf=”‘ || v_codf || ‘”]/LIST_ACTORI’
)into var2
from temp2 t2
where existsNode(
t2.listfilme,
‘/LISTFILME/FILM[@codf=”‘|| v_codf ||'”]/LIST_ACTORI’
) = 1;

select extract(var2, ‘/LIST_ACTORI/@nr_actori’).getNumberVal() into v_nractori from dual;

–dbms_output.put_line(‘Sunt ‘ || v_nractori);

/*
v_coda number;
v_denactor string(10);
v_pret number;
*/

for j in 1..v_nractori
loop
select extract(var2, ‘/LIST_ACTORI/ACTOR[‘|| j || ‘]/@coda’).getNumberVal() into v_coda from dual;
select extract(var2, ‘/LIST_ACTORI/ACTOR[‘|| j || ‘]/@pret’).getNumberVal() into v_pret from dual;
select extract(var2, ‘/LIST_ACTORI/ACTOR[‘|| j ||’]/nume/text()’).getStringVal() into v_denactor from dual;

insert into distrib1 values(v_codf, v_coda, v_denactor, v_pret);

end loop;

end loop;

end loop;
close c6;

end;
/

— cerinta 4

declare
cursor c10 is
select * from case_filme1;
cursor c11 is
select * from filme1;
cursor c12 is
select * from distrib1;

v_idcasa number(3);
v_numec string(10);
–fetch c12 into v_codf, v_coda, v_nume, v_pret;

v_codf number(3);
v_coda number(3);
v_nume varchar2(20);
v_pret number(3);
v_casa number(3);
v_den varchar2(20);
begin
open c10;
loop
fetch c10 into v_idcasa, v_numec;
exit when c10%notfound;

dbms_output.put_line(v_idcasa);

end loop;
close c10;

open c11;
loop
fetch c11 into v_codf, v_casa, v_den;
exit when c11%notfound;

dbms_output.put_line(v_codf || ‘ ‘ || v_casa || ‘ ‘ || v_den);

end loop;
close c11;

open c12;
loop
fetch c12 into v_codf, v_coda, v_nume, v_pret;
exit when c12%notfound;

dbms_output.put_line(v_codf || ‘ ‘ || v_coda || ‘ ‘ || v_nume || ‘ ‘ || v_pret);

end loop;
close c12;
end;
/

drop table temp1 force;
drop table temp2 force;
drop table case_filme1;
drop table filme1;
drop table distrib1;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: