Oracle Ders 39 – Views – 04 Complex View – 03

Merhaba Arkadaslar,
Bu yazimizda Complex View konusuna devam edecegiz ve bitirecegiz.Onceki yazilarda Matematiksel ifadeler/expression ve join islemi ile Complex View olusturduk. Daha once belirtigim gibi group by sonucu olusturulan view’ler de Complex View’ dir.

Group By ile Complex View
Yeni bir view olusturalim ;

create or replace view balancedue
as  select customer#, order#, sum(quantity*retail) total
from customers join orders using (customer#)
join orderitems using(order#)
join books using(isbn)
group by customer#, order#;

select * from balancedue;

View uzerinden delete islemini deneyelim ;

delete from balancedue
where customer# = 1010;

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 – “data manipulation operation not legal on this view”

Complex View , Group By iceriyorsa DML islemlerine izin verilmez !!

Distinct  ile Complex View
Distinct anahtar kelimesini daha once islemistik.Yeni bir tablo olusturalim ve kayitlar ekleyelim ;

create table table_complex(
col1 number,
col2 varchar2(5)
);

insert into table_complex values (1,'A');
insert into table_complex values (1,'A');
insert into table_complex values (1,'B');
insert into table_complex values (1,'C');
insert into table_complex values (2,'D');
insert into table_complex values (3,'D');

Yeni bir view olusturalim ;

create or replace view view_distinct
as select distinct * from table_complex;

distinct anahtar kelimesini kullandigimiza dikkat edelim;

Yeni bir kayit ekleyelim /insert ;

insert into view_distinct values (4,'E');

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 – “data manipulation operation not legal on this view”

Guncelleme islemi deneyelim /update ;

update view_distinct 
set col1=10 where col2= 'A';

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 – “data manipulation operation not legal on this view”

Silme islemi deneyelim / delete;

delete from view_distinct
where col1=1;

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 – “data manipulation operation not legal on this view”

distinct anahtar kelimesini kaldirip tekrar denersek ;

create or replace view view_distinct
as select * from table_complex;

insert into view_distinct values (4,'E');

update view_distinct 
set col1=10 where col2= 'A';

delete from view_distinct
where col1=1;

Islemler basariyla gerceklesir.

Distinct anahtar kelimesi ile olusturulan view’lerde DML islemlerine izin verilmez !

Yazimi burada sonlandiriyorum.
Herkese Bol Javali Gunler dilerim.
Be an oracle man , import java.*;
Levent Erguder
injavawetrust