Cursor
Explicit Cursors
5 Önemli konudan biri.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare cursor c_employee is select employee_id, first_name, last_name from employees order by employee_id; begin for r_employee in c_employee loop dbms_output.put_line(r_employee.employee_id||' '||r_employee.first_name||''||r_employee.last_name); end loop; end; 100 Steven King 101 EMRAH Kochhar 102 Lex De Haan 103 Alexander Hunold ..... |
cursor ı birden fazla satırın tutulduğu değişken olarak tanımlayabiliriz. cursor dan alınan satırı bir değişkene atılması lazım “for r_employee in c_employee” cursor daki “employee_id, first_name, last_name” değerleri r_employee atanıyor.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
declare cursor c_employee is select employee_id, first_name, last_name from employees order by employee_id; sayac number; begin sayac :=1; for r_employee in c_employee loop dbms_output.put_line(sayac||' '||r_employee.employee_id||' '||r_employee.first_name||' '||r_employee.last_name); sayac :=sayac+1; end loop; end; 1 100 Steven King 2 101 EMRAH Kochhar 3 102 Lex De Haan ....... |
3. kayıt alınmak istendiğinde if sayac =3 şeklinde şart konulur. hr şemasındaki tabloların kayıt sayılarını bulan bir cursor nasıl yazılır ? yapılabiliyorsa ciddi bir adım mış .
veri tabanında kaç tablo var ?
drop table emp purge
create table emp as select * from employees;
pk fk yi almıyor nn alıyor
alter table emp modify (last_name null) ; –not null constraintini null girilebilir yapıyor.
update emp set last_name=null; last_name lerin hepsini null (boş gibi bişiy) yapıyor.
Örnek: bu genel istek
emp nin last_name leri boş employees de last_nameler var. employees de ki last_name leri emp ye kopyalayabilir misin ?
employee_id unique olduğu için bu id ile eşleştiriyoruz.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare cursor c_emp is select employee_id, last_name from employees; begin for r_emp in c_emp loop update emp set last_name = r_emp.last_name where employee_id = r_emp.employee_id; commit; end loop; end; |
–10 milyon kayıt da değişiklik yapılıyorsa commiti loop un içine kopyalamak lazım. Aksi takdir tüm değişikliği undo da yapar undo şişer 4GB den 20-30 GB ye çıkar. VT durur o kadar yer yoksa. Herşeyden sonra commit koymak da biraz yavaş olur. Tablodaki kayıt satılarına göre hareket etmek lazım. Employees tablosu cursor a alınır. Çünkü birinin verisi lazım, emp ye isim tablosu ekleniyor .
alter table emp drop column isim ;
alter table emp add (isim varchar2 (100) );
isim bölümüne ad soyad şeklinde yazdırılacak.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare cursor c_emp is select employee_id, first_name, last_name from emp; begin for r_emp in c_emp loop update emp set isim = r_emp.first_name ||' '|| r_emp.last_name where employee_id = r_emp.employee_id; commit; end loop; end; |
|
1 2 3 |
update emp set isim = first_name||' ' || last_name --şeklinde de yapılabiliyor . |
|
1 2 3 |
select e.first_name, e.last_name, d.department_name department_name from emp e, departments d where e.department_id = d.department_id ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
declare cursor c_DEPARTMENT is select department_id, DEPARTMENT_NAME from departments; cursor c_member (p_depno number) is select first_name, last_name from emp where department_id = p_depno; begin for r_department in c_department loop dbms_output.put_line(r_department.DEPARTMENT_NAME); for r_member in c_member(r_department.department_id) loop dbms_output.put_line(' '||r_member.first_name||' '|| r_member.last_name); end loop; end loop; end; |
2. cursor parametre alan cursordır. 1. nin department_id sini alıyor o department_id sine sahip üyeleri getiriyor.
|
1 2 3 4 5 6 7 8 9 10 |
Administration Jennifer Whalen Marketing Michael Hartstein Pat Fay Purchasing Den Raphaely Alexander Khoo Shelli Baida .... |
truncate : rollback yapılamayanı, redo kullanmaz.
select count(*) from emp ;
|
1 2 3 |
begin truncate table emp ; end; |
bu direk çalışamıyor (DDL,create drop,alter truncate ) dynamic sql istiyor.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
begin execute immediate 'truncate table emp' ; end; declare tablo_adi varchar2(30); sayi number; begin tablo_adi := 'emp'; select count (*) into sayi from tablo_adi; dbms_output.put_line(sayi); end; |
Bu çalışmaz ; tablo ve kolon adı parametrik yollanamaz, değer parametrik yollanabilir.
select first_name, last_name from emp where employee_id = 45 ; burada sadece 45 i parametrik yollayabilirsiniz.
execute immeidate ile heryeri parametrik yollayabiliriz.
|
1 2 3 4 5 6 7 8 9 |
declare tablo_adi varchar2(30); sayi number; begin tablo_adi := 'emp'; execute immediate ' select count(*) from '|| tablo_adi into sayi; dbms_output.put_line(sayi); end; |
|
1 2 3 4 5 6 7 8 9 10 |
declare tablo_adi varchar2(30); sayi number; kelime varchar2(10) := 'from'; begin tablo_adi := 'emp'; execute immediate ' select count(*) '||kelime||' '|| tablo_adi into sayi; dbms_output.put_line(sayi); end; |
bu şekilde de çalıştırılabilir aynı sonucu verir.
Veri tabanındaki tabloların kayıt sayılarını bulma
Nelere ihtiyaç var : tablo isimleri, ve kayıt sayılarına
|
1 2 3 4 5 6 7 8 9 10 11 12 |
declare cursor c_tablo is select table_name from user_tables; kayit_sayisi number; begin for r_tablo in c_tablo loop execute immediate 'select count(*) from ' ||r_tablo.table_name into kayit_sayisi; dbms_output.put_line(r_tablo.table_name|| '-' || kayit_sayisi); end loop; end; |
|
1 |
select table_name , num_rows from user_tables; |
Bu şekilde de bulunabilir.
analyze table emp compute statistics;
Tablonun istatistiklerini tutar. Dynamic viewler de tutulur normalde bu komut ile güncellenir. Bu bilgi ne kadar güncel olursa VT o kadar doğru karar verir.
Oracle tablonun kayıt sayısını güncel olarak görebilmesi için istatistik toplaması gerekir. Bunu ya dbs yapar ya da user. 10 g de her saat kendisi yapar. 9i hiç yapmıyordu . Kayıt bilgisini güncel olarak bilemediği için örneğin 100 kayıt var sanıyor bu yüzden full scan yapıyor halbuki güncelleme yapılmıştı ve 1 milyon kayıt girilmişti . Kayıt sayısını bilse index kullanacak 10 dakikada getireceğine 30 sn de getirecek.
Bütün tablolara iki şekilde analyze işlemi yaptırabiliriz
1-
analyze table emp compute statistics;
analyze table employees compute statistics;
analyze table dept compute statistics;
…
bu şekilde tüm tabloları bir script dosyasına koyup çalıştırabiliriz.
select ‘analyze table ‘ || table_name|| ‘ compute statistics;’ from user_tables;
İle bu test elde edilip delimited txt olarak kayıt edilip istenildiğinde çalıştırıldı.
İstatistiklerin güncel olduğunu anlamak için aşağıdaki sorgu kullanılabilir.
select table_name , num_rows from user_tables;
Test için de select count(*) kullanılır.
2-execute immediate ile (dynamic sql ile)
Otomatik olarak bağlı olunan şemanın istatistiklerini toplar
|
1 2 3 4 5 6 7 8 9 10 |
declare cursor c_tablo is select table_name from tabs; begin for r_tablo in c_tablo loop execute immediate ' analyze table ' || r_tablo.table_name || ' compute statistics ' ; end loop; end; |
Bir cursor ile num_rows u başka bir cursor ile count(*) ile çekip karşılaştırma plsql i yan yana yazdırıp denenebilir.
WHILE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
select trunc(dbms_random.value(1,50)) from dual; declare sayi1 number; sayi2 number; sayi3 number; sayi4 number; sayi5 number; sayi6 number; begin sayi1 := trunc(dbms_random.value(1,50)); sayi2 := trunc(dbms_random.value(1,50)); while (sayi2 = sayi1) loop sayi2 := trunc(dbms_random.value(1,50)); --sayi2 sayi1' eşit olmayana kadar bu döngünden çıkamaz. end loop ; sayi3 := trunc(dbms_random.value(1,50)); while (sayi3 = sayi1 OR sayi3 = sayi1) loop sayi3 := trunc(dbms_random.value(1,50)); end loop; dbms_output.put_line('Sayı1: ' || sayi1) ; dbms_output.put_line('Sayı2: ' || sayi2) ; dbms_output.put_line('Sayı3: ' || sayi3) ; end loop; end; |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
declare cursor c_tablo is select table_name from tabs; kayit_sayisi number ; begin for r_tablo in c_tablo loop execute immediate ' select count(*) from ' || r_tablo.table_name into kayit_sayisi; dbms_output.put_line(rpad(r_tablo.table_name,25, ' ' ) || ' -> '||kayit_sayisi); end loop; end; |
rpad ile düzgün gözükmesi sağlanır. 25 karakter sayısı sonrasında ‘ ‘ arasına boşluk * vb işaretler konulabilir.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
REGIONS -> 4 LOCATIONS -> 23 JOBS -> 19 MLOG$_EMPLOYEES -> 0 Şeklinde çıkış alınır (toad da bozuk çıkabiliyor .) declare cursor c_member is select e.first_name, e.last_name, d.department_name dept from employees e, departments d where e.department_id = d.department_id; r_member c_member%rowtype ; begin open c_member; loop fetch c_member into r_member; exit when c_member%notfound; dbms_output.put_line(r_member.first_name || ' ' || r_member.last_name || ' ' || r_member.dept ) ; end loop; close c_member; end; |
for ile bu işi çok daha kolay yapılabilir. cursor lara c_ ile başlamak karışmaması için faydalıdır.
insert into ile , kaç kayıt girildiğini
dbms_output.put_line(SQL%COUNT) ile olmazsa
dbms_output.put_line(SQL%ROWCOUNT) ile yazdırabiliriz
UPDATEABLE CURSORS
insert upda sırasında readconsistency için kilitlenen satır (row) ilk update yapandadır kilit comitlemediği (yada rollbak) için kilit kalkmamıştır sonradan update yapmak isteyen kilitlenir.
1. commit ettiğinde 2. nin de update i gerçekleşir.
Deadlock : kilitlenmenin iki katıdır . kilitlenme ile aynı şey deildir.
a kullanıcısı, b kullanıcısının kilitli kaydını güncellemeye çalışıyor
b kullanıcısı da a kullanıcısının kilitli kaydını güncellemeye çalışırsa
ikisi de kilitlenir.Bu durumda oracle ilkini deadlock hatası vererek atar.
2. , 1. nin commit/rollback etmesini bekler.
|
1 2 3 |
select * from employees for update ; |
Bu sql cümlesi ile bütün kayıtların kilidi bu komutu çalıştıran kullanıcıya geçer. commit leyerek kilit çözülür.
|
1 2 3 |
select * from employees for update of salary; |
Bu şekilde sadece salary kolonu kilitlenir. Çalıştıracağınız cursor sırasında güncelleme yapılmasını istenmiyorsa kullanılır.
|
1 2 3 4 5 |
declare cursor employees is select * from employee for update of salary, last_name ; |
salary ve last_name kolonları bu cursor kapanana kadar kilitli kalır.
|
1 2 3 |
update tablo_ismi set salary = 5000 where current of c_member |
Üzerinde bulunduğum kaydın maaşını 5000 yap. Bu aşağıdaki gibi de yapılabilir.
Tercih edilen ;
|
1 2 3 |
update tablo_ismi set salary = 5000 where employee_id = r_member.employee_id |
parametre alan Cursorlar da (Including Cursor Parameters ) for kullanmak daha avantajlısıdır. Ne sonsuz döngüye girer, ne de açık cursor unutulur. Açık cursor performans açısından tehlikeli olabilir. Cursor ı açık bırakmak sistemden kullanmaya devam etmektir. 50 kişi çağırsa 50 tane cursor açık kalacak. bir süre ram yetmicektir.
|
1 2 3 4 5 6 |
begin for r_member in (select first_name fn, last_name ln from employees) loop dbms_output.put_line(r_member.fn ||' ' || r_member.ln); end loop; end; |
|
1 2 3 4 |
Ellen Abel Sundar Ande Mozhe Atkinson ....... |
Bu şekilde declare bölümünde cursor tanımlamaya gerek kalmıyor.
Nested blocks
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
declare maas number ; begin select salary --bu select cümleciği hataya düşse bile alındaki " dbms_output.put_line('bitti');" çalışması sağlanabilir mi? into maas --bu yapıda olmaz cümkü hatay düştüğü anda o bloğun içindeki exceptiona düşer. gerisini çalıştırmayı keser. from employees --ikinci bir iç içe blok ile yapılabilir "nested" buradan geliyor. where employee_id>100; dbms_output.put_line('bitti'); delete from employees where employee_id = -198; exception when others then dbms_output.put_line(SQLERRM); end; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
declare maas number ; begin declare begin select salary into maas from employees where employee_id>100; exception when others then dbms_output.put_line('hataya içerde düştü'); end; dbms_output.put_line('bitti'); delete from employees where employee_id = -198; exception when others then dbms_output.put_line(SQLERRM); end; |
|
1 2 3 4 5 |
begin fonk1() proc1() fonk2() fonk3() |
Örneğin proc da hata verdi proc un içinde exception ile hata yakalanırsa diğerleri çalışmaya devam eder.
RMAN ile farklı...
12 Mart 2019