drop table t1;
/
commit;
/
create table t1 of Student
(
constraint pk_t1 primary key(Identif)
)object identifier is primary key;
/
declare
n number;
dat date;
addr address;
un Universitet;
begin
  n:=Student.getid;
  dat:=current_date;
  insert into t1(identif,Lastname,firstname,middlename,datebirth,adr,unv) values(n,to_char(n),'222','333',dat,addr,un);
  n:=Student.getid;
  insert into t1(identif,Lastname,firstname,middlename,datebirth,adr,unv) values(n,to_char(n),'222','333',dat,addr,un);
  n:=Student.getid;
  insert into t1(identif,Lastname,firstname,middlename,datebirth,adr,unv) values(n,to_char(n),'222','333',dat,addr,un);
  n:=Student.getid;
  un:=Universitet('БГТУ',address);
  insert into t1(identif,Lastname,firstname,middlename,datebirth,adr,unv) values(n,to_char(n),'222','333',dat,addr,un);
  n:=Student.getid;
  insert into t1(identif,Lastname,firstname,middlename,datebirth,adr,unv) values(n,to_char(n),'222','333',dat,addr,un);
end;
/
select * from t1;
/
delete from t1;
/
declare
un Universitet;
begin
  un:=Universitet('БГТУ',address);
  update t1 set unv=un where identif = 148;
end;
/
declare
iden number;
begin
  iden:=Student.getid;
  update t1 set identif=iden where identif = 116;
end;
/
delete from t1 where identif = 113;
/
create or replace  type fv1 as object
(
id number(10),
sLastname varchar2(20),
sfirstname varchar2(1),
smiddlename varchar2(1),
addr address,
unname varchar2(20)
)
/
create view v1 of fv1 with object identifier(id)
as 
select item.identif id, item.Lastname sLastname, substr(item.firstname,1,1) sfirstname, substr(item.middlename,1,1) smiddlename, Treat(item.adr as address) addr, Treat(item.unv as UNIVERSITET).Name_Universitet unname from t1 item;
/
select * from v1;
/
drop view v1;
/
create or replace type progress as object
(
id number(10),
dt date,
subject varchar(20),
typectr varchar(20),
mark number(2)
)
/
drop type progress;
/
create table t2 of progress
(
constraint pr_pk primary key(id,dt,subject),
constraint fk foreign key(id) references t1(identif)
)
/
drop table t2;
/
declare
type tc is ref cursor;
cur tc;
str1 varchar2(20):='subject1';
str2 varchar2(20):='subject2';
d1 date:=to_date('01.01.2011');
d2 date:=to_date('21.04.2011');
n number(10);
begin 
open cur for select identif from t1;
for i in 1..5
loop
  fetch cur into n;
  insert into t2(id,dt,subject,typectr,mark) values(n,d1,str1,'Экзамен',i*2);
  insert into t2(id,dt,subject,typectr,mark) values(n,d2,str2,'Зачет',i+4);
end loop;
end;
/
select * from t2;
/
delete from t2;
/
create or replace type fv2 as object
(
id number(10),
rt number(10)
)
/

create view v2 of fv2 with object identifier(id)
as 
select o.id id,sum(o.mark) from t2 o group by o.id order by o.id;
/
drop view v2;
/
select * from v2;
/
create or replace type fv3 as object
(
id number(10),
sLastname varchar2(20),
sfirstname varchar2(1),
smiddlename varchar2(1),
unname varchar2(20),
rt number(10)
)
/
create view v3 of fv3 with object identifier(id)
as 
select o1.id id,o1.sLastname,o1.sfirstname,o1.smiddlename,o1.unname,o2.rt from v1 o1 join v2 o2 on o1.id = o2.id;
/
select * from v3 order by rt desc;
/
update t2 set mark = 8 where id = 151 and subject='subject1';