Whoever Resembles a People is One of Them. He is not one of us who resembles other than us,
nor who resembles the Jews nor the Christians.(jami-at-tirmidhi-vol-5-ahadith-2695)
O you who believe (who wish to reach ALLAH C.C. before death)! Do not take my and
your enemies for friends!
And although they have denied what has come to you
from the Truth, you still love them (treating them as your friends).
If you go forth to struggle hard in My Way, seeking My Pleasure, (then why) you manifest
love to them and share with them your secrets.
And I know what you conceal and what you manifest. And whoever of you does that, then he
indeed has gone astray from the right Way. 60/Al-Mumtahanah-1
They are driving out the Messenger and yourselves from your land because you
believe in ALLAH C.C., your Lord.
Recite (read and explain to others) that which has been revealed to you of the Book
and keep up the Prayer.
Surely the Prayer keeps away from Al-Fahsh (to do what ALLAH C.C. prohibits) and Al-Munkar
(to deny what ALLAH C.C. commands).
Whoever obeys me, obeys ALLAH C.C., and whoever disobeys me, disobeys ALLAH C.C.
Whoever obeys the ruler, obeys me, and whoever disobeys the ruler, disobeys me. " (Sahih)

PLSQL

{tab=PLSQL’e Giriş}

{code class=”brush: xml;”}
set serveroutput on
declare
  bolum_adi varchar2(30);
begin
  select department_name
  into bolum_adi
  from departments
  where department_id=120 ;

dbms_output.put_line(‘Bölüm Adı:’|| bolum_adi);

end ;
{/code}
çıktısı
{code class=”brush: xml;”}Bölüm Adı:Treasury
{/code}
declare : Begin ile arasında değişkenlerin tanımlandığı alan.
into : SQL’den çekilen değer değişkene atanıyor.
dbms_output.put_line : Değişkenin değerini ekrana yazar. Alt satıra geçer. Sadece put olsa idi yanına devam eder. (put ların sonunda put_line gelmesi
gerekiyor aksi halde yazdırmıyor ?)
Bu prosedur ya da fonksiyon değil . Anonim bir bloktur, saklanmaz (scripti saklanmazsa) çöpe gitti. Fonksiyon olsa VT’ de saklanırdı. dbms_output.put_line debug gibi düşünülebilir kod düzgün çalışıyor mu , doğru değeri aldı mı gibi sürekli değişkenlerin değerini ekrana yazdırarak yanlışlığın nerde olduğu kontrol edilebilir. 5 birim ürünün bilgilerini getirmek için java 5 kere VT’ ye gider gelir. PLSQL’ de bir kerede yapılabilir. Bazı projelerdeki yavaşlıklarda kod java’da SQL’ e geçirilmesi gerekebiliyor. Hiç bir şey vt deki store prosedurden daha hızlı çalışamaz.
SGA – library cache daha önce çalışan SQL’ ler ve PLSQL’ leri derlenmiş olarak tutar tekrar çalıştırılmak istenirse library cache den getirir. Yeniden parse etmiyor. Parse işlemi uzun süren bir işlemdir. (tabloya yetki var mı, çağrılan kolonlar tabloda var mı, gibi kontroller yapar ) prosedurler herhangi bir programlama dilinden ya da SQL’ den çağrılabilir . Sorgu yapılabilen VT’ den prosedur de çalıştırılabilir.

PL/SQL Program Block
declare

begin (zorunlu)
   program logic (zorunlu)
   exception
end (zorunlu)
{code class=”brush: xml;”}
set serveroutput on –toad da gerek yok (execute as script ile çalıştırılacaksa gerek var)
begin
   dbms_output.put_line(‘Merhaba Dünya’);
end ;
{/code}
{code class=”brush: xml;”} –tek satır comment
{/code}
{code class=”brush: xml;”}/*
paragraf comment buraya yazılır
*/
{/code}
Tüm komutlar: ” ; ” ile bitirilir.
Örnek : Ekrana Merhaba yazdırır.

{code class=”brush: xml;”}SQL> begin
2 dbms_output.put_line(‘Merhaba’);
3 end;
4 /
{/code}

{code class=”brush: xml;”}Merheba
PL/SQL procedure successfully completed.
{/code}
örnek : ekrana merhaba1 den 5 e kadar yazdırır.
{code class=”brush: xml;”}SQL> begin
2
3 for i in 1..5
4 loop
5 dbms_output.put_line(‘Merhaba’||i);
6 end loop;
7 end;
8 / –toad da buna iihtiyaç olmayabilir sqlplus da istiyor
{/code}

{code class=”brush: xml;”}Merhaba1
Merhaba2
Merhaba3
Merhaba4
Merhaba5

PL/SQL procedure successfully completed.
{/code}

{code class=”brush: xml;”}declare
begin

for aa in 1..1000 LOOP
insert into employees (employee_id, last_name, first_name, department_id, salary)
values (192100 + aa, ‘mehmet’, ‘Derviş’, 1, 112600 + aa);
commit;
end loop ;
exception
when others then
rollback;
end;{/code}
Burada declare ye gerek yokmuş. 1’den 1000 kadar döngü ile insert yapacak. loop la end loop alta da yazılabilir. Burada her insert den sonra commit etmiş.1000 taneyi insert ettikten sonra değil commit loop ın dışında değil burada rollback e çokta gerek yokmuş insert edilemezse o statement geri alınıyor
commit loop un dışında olsa idi rollbak işe yarardı.
Bu örnek exception için pek iyi bir örnek değil.
ppl/sql de atamalar := diye yapılır.

{code class=”brush: xml;”}declare
bolum_no number;
bolum_adi varchar2(50);

begin
bolum_no :=45;
select department_name
into bolum_adi
from departments
where department_id=bolum_no;
dbms_output.put_line(‘Bölüm Adı:’||bolum_adi);
end;{/code}

{code class=”brush: xml;”}45 nolu department_id olmadığı için aşağıdaki hatayı verir
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8 {/code}
Hata mesajını yakalayabilmek için örneği aşağıdaki gibi değiştirelim.
{code class=”brush: xml;”}declare
bolum_no number;
bolum_adi varchar2(50);
begin
bolum_no :=45;
select department_name
into bolum_adi
from departments
where department_id=bolum_no;
dbms_output.put_line(‘Bölüm Adı:’||bolum_adi);
exception
when no_data_found then
dbms_output.put_line(bolum_no||’ kodlu bölüm adi bulunamadi’);
end;
{/code}
{code class=”brush: xml;”}45 kodlu bölüm adi bulunamadi
PL/SQL procedure successfully completed.{/code}
PLSQL’ de DDL komutları direk yazılamaz, dml (insert update delete) ler yazılabilir. DDL (alter delete drop truncate ) komutları için dynamic sql e ihtiyaç vardır. “execute immediate ‘drop table members”; yazılabilir.
{code class=”brush: xml;”}begin
drop table members; bu yazılamaz.
end;{/code}
alter create drop gibi komutlar execute immediate yani dynamic sql ile çalıştırılabilir. Tablo adını dinamik almak için de dynamic sql kullanılır. Tablodan değer almak için dynamic SQL’ e gerek yoktur. Bütün tabloların kayıt sayılarını gösteren çıktı bu genel bir istek, bir milyondan fazla kaydı olan tablolar vb.
sqlplus da ” / ” en son çalıştırılan statement ı tekrar çalıştırır, ” run ” da aynı işi yapar ek olarak statement ı ekrana yazar.
. sytax ı buffer da tutup komut satırına düşürür / da çalıştırılır. (buffer da bir adet tutabilir.)
{code class=”brush: xml;”}employee.salary%TYPE —> employee tablosunun salary kolonu tipinde{/code}
Hata yakalanırken kullanılan exceptionlarda ” WHEN others THEN ” en son yazılır. Çünkü genel hata , tanımlanmayan tüm hatalar buraya düşecek.
{code class=”brush: xml;”}set serveroutput on
declare
sicilno employees.employee_id%type;
ad employees.first_name%type;
soyad employees.last_name%type;
begin
sicilno := 199;
select first_name, last_name
into ad, soyad
from employees
where employee_id = sicilno;
dbms_output.put_line(ad||’ ‘||soyad);
end; {/code}
{code class=”brush: xml;”}Douglas Grant
PL/SQL procedure successfully completed.{/code}
select te 3 deger varsa into da 3 deger olmalı, into atılacak deger tek satır olmalı. Örnekte iki kişi geliyorsa into ya atılamaz.

{code class=”brush: xml;”}Declare

EmpLname VARCHAR2(20)
EmpSalary NUMBER(6)
EmpBDate DATE
{/code}
PLSQL’ de bunlara ek olarak boolean da tanımlanabiliyor. sql de tanımlanamıyor. İsimlendirme kuralları değişkenler için de geçerli.
{code class=”brush: xml;”}*max 30 karakter
*harf ile başlamak zorunda
*içinde harf rakam ve işaret olabilir.
*izin verilen işaretler $, #, _ underscore{/code}
Bunlar tablo , kolon ve pl sql de de değişken isimlerinde geçerli.

Veri Tipleri
{code class=”brush: xml;”}binary_integer : aslında number hem numara/number hem ondalıklı sayılarda kullanılabilen veri tipi.
PLS_integer : yeni gelen bir veri şekli binary_integer dan daha hızlıdır. number dan da daha hızlı olabiliyor pl/sql lerde
binary_float ,binary_double : ondalıktan sonra daha kesin hesaplamalar yapabiliyor
natural : doğal sayılar 0-2G (+)
positive : 1-2G
data type synonyms: diğer vt lerden gelen kodları çalıştırabilmek için oluşturulan takma adlar.
number yerine geçen takma adlar ; dec,decimal, double, precision, float, integer, int, numeric, real, smalllint
char character, string
varchar2 varchar
örneğin mysqlde yazılan scriptlerdeki varchar ları varchar2 ye çeviriyor.
{/code}

{code class=”brush: xml;”}Declare
NewEmpSalary Number(6) :=25000;
NewEmpSal Number(6) DEFAULT 25000;

“default” = ” := ” in yerine geçer . genelde kullanılan ” := “

Declare
EndTheLoop := FALSE;
{/code}

BEGIN
blolean değişkenler true or false değer alır.
toadda view —>toad options —> soldaki menuden editor seçilir
languages dan pl/sql seçilip
code templates tıklanır. Açılan pencerede istenildiği gibi kısatmalar (declare begin end >dbe gibi) girilir. code yazarken ctrl+space tuşu ile çağrılabilir.

{code class=”brush: xml;”}declare
sayi number;

begin
sayi :=67;
if (sayi < 100) then
dbms_output.put_line(‘sayi kucuk’);
else
dbms_output.put_line(‘sayi buyuk’);
end if;
end;
{/code}

{code class=”brush: xml;”}declare
sayi number;

begin
sayi :=67;
if (sayi < 100) then
dbms_output.put_line(‘sayi kucuk’);
dbms_output.put_line(‘hala kucuk’);
else
dbms_output.put_line(‘sayi buyuk’);
end if;
end;
{/code}

{code class=”brush: xml;”}declare
erkekmi boolean;

begin
erkekmi :=false;

if (erkekmi) then
dbms_output.put_line(‘erkek’);
else
dbms_output.put_line(‘bayan’);
end if ;

end;
{/code}
— bunun çıktısı bayan oluyor,
— false i true yaparsak erkek olur.

boolean kolonda kullanılamaz

{code class=”brush: xml;”}select dbms_random.value(1,5) from dual;
{/code}
{code class=”brush: xml;”}select trunc(dbms_random.value(0,49))+1 from dual;
{/code}
0 ile 49 arasında tesadüfi sayılar çıkar
declare da constant tanımlanırsa verinin değeri değiştirilemez.
Begin Clause
{code class=”brush: xml;”} declare
tarih date ;
ad varchar2(20);

begin
tarih := sysdate;
ad := upper(‘mehmet’);

dbms_output.put_line(‘tarih:’||tarih);
dbms_output.put_line(‘ad:’|| ad);

end;
{/code}
{code class=”brush: xml;”}declare
tarih date ;
ad varchar2(20);

begin
tarih := sysdate;
ad := user;
dbms_output.put_line(‘tarih:’||tarih);
dbms_output.put_line(‘ad:’|| ad);

end;
{/code}
bu örnekte görüldüğü üzere fonksiyonları select user from dual; gibi sorgu çekmeden kullanabiliyoruz.

Karşılaştırma Operatörleri

{code class=”brush: xml;”}declare
sayi1 number;
sayi2 number;

begin
sayi1 := 14;
sayi2 := 30;
IF (sayi1>10 and sayi2>10) THEN
dbms_output.put_line(‘İkisi de 10”dan büyük’);

END IF;

end;
{/code}
–sayilardan birini 10 dan küçük değer verirsek ekrana birşey yazmaz.
{code class=”brush: xml;”}declare
sayi1 number;
sayi2 number;

begin
sayi1 := 4;
sayi2 := 30;
IF (sayi1>10 OR sayi2>10) THEN
dbms_output.put_line(‘sayilardan biri 10”dan büyük’);

END IF;

end;
{/code}
{code class=”brush: xml;”}declare
sayi number :=7;

begin
if(sayi=1) then
dbms_output.put_line(‘sayi 1’);
elsif (sayi=2) then
dbms_output.put_line(‘sayi 2’);
elsif (sayi=3) then
dbms_output.put_line(‘sayi 3’);
else
dbms_output.put_line(‘sayi 3 den buyuk’);
end if;

end;
{/code}

{code class=”brush: xml;”}declare
max_maas number;

begin
select max(salary)
into max_maas
from hr.employees;
dbms_output.put_line(‘en yuksek maas’||max_maas);

end;
{/code}
{code class=”brush: xml;”}declare
max_maas number;
min_maas number;
ort_maas number;

begin
select max(salary)
into max_maas
from employees;

select min(salary)
into min_maas
from employees;

select round(avg(salary),2)
into ort_maas
from employees;

dbms_output.put_line(‘en yuksek maas: ‘||max_maas);
dbms_output.put_line(‘en dusuk maas: ‘||min_maas);
dbms_output.put_line(‘en ortalama maas: ‘||ort_maas);

end;
{/code}
Bu tek sorgu ile de yapılabilir.
{code class=”brush: xml;”}create table copy_dept as
select * from departmenst
where 1=2 ;
{/code}
–bu sekilde satırlar alınmadan departments ın boş kopyası
–oluşturulur.
–where 1=1 olursa sağlanan bir şart olduğunda satırları ile birlikte oluşturulur.
{code class=”brush: xml;”}begin
insert into copy_dept (department_id, department_name)
values (10, ‘test’) ;
commit;
end;
{/code}
{code class=”brush: xml;”}begin
insert into copy_dept (department_id, department_name)
values (10, ‘test’) ;

commit;
end;
{/code}
–commit end den sonra da konulabilir. commit prosedürün içine mi konulacak dışına mı ? içine konulursa proseduru çalıştıran kişi aynı zamanda commit de etmiş olur.

Havale işleminde hesap no 24 ‘ten hesap no su 30 olan kişiye 50 TL gönderme işlemi

{code class=”brush: xml;”}begin
update hesaplar
set miktar = miktar-50
where hesapno=24;

update hesaplar
set miktar = miktar+50
where hesapno=30;

commit;
exception
when others then
rollback;
end;
{/code}
Hesaptan para çekildi, hesapnosu 30 olan kişiye aktarılırken bir hata oldu, diyelimki constraint var üst sınır var 100 tl. yatırılan para ile 110 olacak bu durumda para 30’un hesabına geçmez ve rollback olur transaction mantığı bu. çalşırsa ikisi birlikte çalışsın ,çalışmazsa ikisi de rollback olsun. Eğer birinci update ten sonra commit olsa idi para hesaptan düşer ama diğerine hesabına constraint ten dolayı yatırılamaz., bu durumda tutarsızlık olur 50 tl yok ortalıklarda.

Sürekli update cümlesi yazmak zor iş. Prosedur ile hesap no girelim artış miktarını girelim -50 dersek , 50 düşsün ,+50 dersek 50 eklesin. Burada yani ilk cümlede commit girilirse tutarsızlık olabilir. prosedurlerin içine commit in konulacağı yer duruma göre değişir.

commit konulmazsa değişiklik yapılan sessionda değişiklik gözükür. Diğer sessionlarda görünmez.

constraint eklemelerinde ” novalidate ” eski verilere bakma yenilere bak anlamındadır. constrainte uymayan eski verilere karışmaz.
default ” enablevalidate ” dir

{code class=”brush: xml;”}alter table employee
add EmpID NUMBER(4) CONTSTRAINT employeePK PRIMARY KEY NOVALIDATE ;
{/code}

create squence EmpIDsequence ;

tabloada primary olduğu için sürekli aynı sayıları insert edemez bu yüzden değeri sequence den alır.
{code class=”brush: xml;”}DECLARE

BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO employee (EmpID, lname, fname, ano, salary)
VALUES (EmpIDsequence.NEXTVAL, ‘Doe’, ‘John’, 1, 30000 + i) ;
END LOOP ;
END ;
/
{/code}
GO TO
Kurumsal projelerde pek kullanılan bir yapı değil . Çoğu programlama dilinden kaldırıldı.
Label
Yer, gidilebilecek bir yer
LOOP (önemli)
İlla for ile kullanılacak bir diye bir kural yok, loop for dan bağımsız bir şeydir.
{code class=”brush: xml;”}DECLARE
BEGIN

LOOP

……..
……..

EXIT WHEN …..
……
……
END LOOP;
END;
{/code}
Exit şartı konulması gereklidir yoksa sonsuza kadar devam eder. Exit koşulu ile çıkılabilir.
loop tehlikeli bir özelliktir . sonsuz döngüye girerse toad ı kapatmak gerekir.
Loop la 1 den 10 a kadar yazdıralım.
{code class=”brush: xml;”}declare
sayi number;

begin
sayi := 1;

loop
dbms_output.put_line(sayi);

end loop;

end ;
{/code}
Bu halde çalıştırılırsa toad ı kapamak zorunda kalırız.
{code class=”brush: xml;”}declare
sayi number;

begin
sayi := 1;

loop
dbms_output.put_line(sayi);
sayi := sayi+1;
exit when sayi > 100;

end loop;

end ;
{/code}
Sayı artırılması unutulursa da sonsuz döngüye girer. Unutmamak için “exit when sayi > 100; ” başa koymakta fayda var. Bu örneği for ile yapmak çok daha kolay.
{code class=”brush: xml;”} declare
sayi number;

begin
for i in 1..10
loop
dbmps_output.put_line(sayi);
end loop
end ;
{/code}
{code class=”brush: xml;”}begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop ;
end ;
{/code}
Bitirilecek sayı bilindiğinde for kullanılıyor.
{code class=”brush: xml;”}BEGIN
FOR i in reverse 1..10 LOOP –10 dan 1 e kadar
{/code}

WHILE
Bitirilecek sayı bilinmediğinde kullanılır.
{code class=”brush: xml;”}declare
sayi number;

begin
sayi := 1;

while (sayi<=10)
loop
dbms_output.put_line(sayi);
sayi := sayi+1;

end loop ;
end ;
{/code}
Not: Bu örnek çalışmadı.
{code class=”brush: xml;”}declare
ogrenci_sayi number ;
ogrenci_not number;
not_toplam number :=0 ;
ort_toplam number :=0 ;
begin
ogrenci_sayi :=0;
ogrenci_not := &ogr_not ;
while (ogrenci_not>0)
loop
not_toplam := not_toplam + ogrenci_not ;
dbms_output.put_line(‘NOT_TOPLAM: ‘ || not_toplam) ;
ogrenci_sayi := ogrenci_sayi + 1 ;
dbms_output.put_line(‘Ogrenci Sayi: ‘ || ogrenci_sayi) ;
ogrenci_not := &ogr_ not
dbms_output.put_line(‘Not: ‘ || ogrenci_not) ;
end loop;
ort_not := not_toplam / ogrenci_sayi ;
dbms_output.put_line(‘Ortalama: ‘ || ort_toplam) ;
end;
{/code}

CASE
Decode ile case arasındaki fark ; case tek başına kullanılabilir. Decode kullanılamaz.
{code class=”brush: xml;”}select decode (……..
into
select decode diyip into ile cinsiyete atabilirsiniz
case de ise
:= decode (cinsiyet. ‘E’, ‘Erkek’, ‘K’, ‘Kız’, ‘Girilmedi’);
{/code}
{code class=”brush: xml;”}declare
birth_month CHAR(3)
message VARCHAR2(40);
BEGIN
x_ssn := &prompt_for_ssn;
select bdate
into x bdate
from employee
where ssn = x_ssn;

birth_month := to_char (x_bdate, ‘mon’);

case birth_month
when ‘JAN’ THEN message := ‘start of the year’
when ‘FEB’ THEN message := ‘short month’
ELSE message := ‘No comment’
end case;

DBMS_OUTPUT.PUT_LINE (message);
END;
/
{/code}

Exception
Kullanıcılara ora- ile başlayan hatalar gösterilmez. Exception ile yakalıyıp kullanıcının anladığı bir hata mesajına döndürmek lazım. “Kişi bulunamadı vb”
{code class=”brush: xml;”}declare
dept_name varchar2 (50);

begin

select department_name
into dept_name
from departments
where department_id=&takim;

dbms_output.put_line(‘Departman: ‘|| dept_name );

end;
{/code}
Bu örnekte takımın değeri (value) tablo da yok ise “ORA-0143 no data found ” hatası alır. Kullanıcının hatayı bu şekilde almaması lazım. Her prosedur vb yapıda exception koymak gereklidir. Kullanıcı “ora-” lı hatalar görmemeli.

” others ” Tüm hata mesajlarını yakalayan operatordur. Genel birşeydir. Sadece hata oluştu dedirtebiliriz. Ne oldugu bilinemeyebilir.

{code class=”brush: xml;”}declare
dept_name varchar2 (50);

begin

select department_name
into dept_name
from departments
where department_id=&takim;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception
when others then
dbms_output.put_line(‘Hata oluştu.’);

end;
{/code}
{code class=”brush: xml;”}declare
dept_name varchar2 (50);

begin

select department_name
into dept_name
from departments
where department_id=&takim;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception
when no_data_found then
dbms_output.put_line(‘girilen No lu kayıt bulunamadı.’);

end;
{/code}
{code class=”brush: xml;”}declare
dept_name varchar2 (50);

begin

select department_name
into dept_name
from departments
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception
when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);

end;
{/code}
{code class=”brush: xml;”}”400 Nolu kayıt bulunamadı.” Hatası alınır. {/code}
“0” a bölünme oracle da bir hatadır divide by zero diye bir hata verir.
{code class=”brush: xml;”}declare
dept_name varchar2 (50);
sayi number;

begin

sayi :=7/0;

select department_name
into dept_name
from departments
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception
when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);
when others then
dbms_output.put_line(‘Hata oluştu’);

end;
{/code}
“hata oluştu” mesajı alınır.
{code class=”brush: xml;”}declare
dept_name varchar2 (50);
sayi number;

begin

sayi :=7/0;

select department_name
into dept_name
from departments
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception
when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);
when zero_divide then
dbms_output.put_line(‘sıfıra bölünme’);

–when others then
–dbms_output.put_line(‘Hata oluştu’);

end;
{/code}
“sıfıra bölünme ” hatası alınır.

{code class=”brush: xml;”}alter table emp drop primary key cascade; adını bilmeden yapılabiliyor
alter table emp drop constraint pk_teams_tid cascade; baska tablonun FK sı ise direk siler hata vermez. {/code}

Dept (department_id) mükerrer kayıt girip (department_id si 10 olan iki adet kayıt bulunmaktadır.

{code class=”brush: xml;”}declare
dept_name varchar2 (50);
sayi number;

begin

–sayi :=7/0;

select department_name
into dept_name
from dept
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception

when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);

when zero_divide then
dbms_output.put_line(‘sıfıra bölünme’);

when others then
dbms_output.put_line(‘Hata oluştu’);

end;
{/code}
{code class=”brush: xml;”} “Hata oluştu”{/code}

{code class=”brush: xml;”} exception when others then null; {/code}
Bunun anlamı kimseye mesaj gösterme, exception koyma daha iyi Gerçekte hatanın olduğunu görmek için others iptal edilir.
{code class=”brush: xml;”}declare
dept_name varchar2 (50);
sayi number;

begin

–sayi :=7/0;

select department_name
into dept_name
from dept
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception

when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);

when zero_divide then
dbms_output.put_line(‘sıfıra bölünme’);

–when others then
— dbms_output.put_line(‘Hata oluştu’);

end;
{/code}

{code class=”brush: xml;”}ora-01422 more than requested number of rows şeklinde hata verir. department_id si 10 olan iki adet kayıt var çünkü into ya tek değer atılabiliyor.{/code}

{code class=”brush: xml;”}declare
dept_name varchar2 (50);
sayi number;

begin

–sayi :=7/0;

select department_name
into dept_name
from dept
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception

when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);

when zero_divide then
dbms_output.put_line(‘sıfıra bölünme’);

–when too_many_rows then
— dbms_output.put_line(‘Beklenenden çok sonuç dönüyor’);

when others then
dbms_output.put_line(‘Hata oluştu’);

end;
{/code}
{code class=”brush: xml;”}”Beklenenden çok sonuç dönüyor” hatası alınır.{/code}

{code class=”brush: xml;”}declare
dept_name varchar2 (50);
sayi number;

begin

–sayi :=7/0;

select department_name
into dept_name
from dept
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception

when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);

when zero_divide then
dbms_output.put_line(‘sıfıra bölünme’);

–when too_many_rows then
–dbms_output.put_line(‘Beklenenden çok sonuç dönüyor’);

when others then
dbms_output.put_line(‘Hata oluştu ‘||SQLERRM);

end;
{/code}
{code class=”brush: xml;”}Hata oluştuORA-01422: exact fetch returns more than requested number of rows{/code}
“SQLERRM” önemli bir özelliktir. En azından hatanın ne olduğunu yazar.
{code class=”brush: xml;”}declare
dept_name varchar2 (50);
sayi number;

begin

–sayi :=7/0;

select department_name
into dept_name
from dept
where department_id=&departman;

dbms_output.put_line(‘Departman: ‘|| dept_name );

exception

when no_data_found then
dbms_output.put_line(&departman||’ No lu kayıt bulunamadı.’);

when zero_divide then
dbms_output.put_line(‘sıfıra bölünme’);

–when too_many_rows then
–dbms_output.put_line(‘Beklenenden çok sonuç dönüyor’);

when others then
dbms_output.put_line(‘Hata oluştu ‘ ||SQLCODE|| ‘ ‘ ||SQLERRM);

end;
{/code}
{code class=”brush: xml;”}Hata oluştu -1422 ORA-01422: exact fetch returns more than requested number of rows{/code}
SQLERRM daha iyi Bir prosedur ile ORA-01422 hatası gelince türkçe bir mesaj getirtilebilir.
{code class=”brush: xml;”}exception
when others then
dbms_output.put_line(SQLERRM);
{/code}
En azından böyle birşey yapılabilir.

Pragma Exception_INIT

{code class=”brush: xml;”}alter table hr.dept modify DEPARTMENT_name not null novalidate;{/code}
{code class=”brush: xml;”}insert into dept (department_id)
values (700);{/code}
{code class=”brush: xml;”}” ORA-01400 ” : cannot insert null into…. şeklinde hata alınır.{/code}
Ön tanımlı hata olmadığı için pragma kullanılarak kendimiz tanımlarız. Pragma ile ön tanımlı olmayan hataları yakalayabiliriz.
{code class=”brush: xml;”} declare
not_null_eklenemez exception ;
pragma exception_init(not_null_eklenemez, -1400);

begin

insert into dept (department_id)
values (700);

exception
when not_null_eklenemez then
dbms_output.put_line(‘Hata : Not null alanı gecemezsiniz!’);

end;
{/code}
Hata : Not null alanı geçemezsiniz!

Implicit Cursors

{code class=”brush: xml;”}begin
delete from copy_dept
where department_id < 30 ;

dbms_output.put_line(SQL%ROWCOUNT||’kayit silindi’);
end;
{/code}
{code class=”brush: xml;”}”3 kayıt silindi ” şeklinde kaç kayıdın etkilendiğini bildirir.{/code}
{code class=”brush: xml;”}begin

delete from copy_dept
where department_id > 1000 ;

if (SQL%FOUND) then
dbms_output.put_line(‘Silme işlemi yapıldı’);
else
dbms_output.put_line(‘Silme işlemi gerçekleşstirilemedi.’);
end if ;
end;
{/code}
{code class=”brush: xml;”}”Silme işlemi gerçekleşstirilemedi” mesajını döner.{/code}
SQL%FOUND : Çalıştırılan SQL herhangi bir satırı değiştirdi ise örnekte bütün değerler 1000 den küçük olduğundan . SQL%FOUND FALSE oluyor. SQL%NOTFOUND ise TRUE oluyor. SQL%FOUND ve SQL%NOTFOUND birbirlerinin zıttı oluyorlar.
30′ dan büyükleri sil dediğimizde SQL%FOUND TRUE oluyor . SQL yapıldı gerçekleştirildi gibi bir değer.

User-Defind Events
Örneğin çalışanların ortalama maaşından düşük maaş olamaz diye bir tanımlama yaptık. Yeni giren kişiye maaş verilirken ortalama maaşa bakıp yüksek bir maaş verilecek. Düşük maaş verildiğinde oluşacak hatayı oracle yakalayamaz. iş mantığı ile oluşan hataları
geliştirici tanımlar. Örneğin bir günde 10’dan fazla kişi izinli omayacak. IK 11. kişiye izin vermek istediğinde program bunu
algılayacak ve hata verecek. Bu oracle ın yakalayabileceği bir hata değil.
{code class=”brush: xml;”}declare
maas number;
ort_maas number;
ort_maastan_buyuk_olmali exception;

begin

maas :=3000;
select round(avg(salary))
into ort_maas
from employees;

if (maas < ort_maas) then
raise ort_maastan_buyuk_olmali;
end if;

dbms_output.put_line(‘Ort_Maaş: ‘ ||ort_maas);

exception
when ort_maastan_buyuk_olmali then
dbms_output.put_line(‘ Maaş (‘ ||maas||’) ort maaştan(‘||ort_maas||’) küçük olamaz.’);

end;
{/code}
{code class=”brush: xml;”} “Maaş (3000) ort maaştan(6462) küçük olamaz.” çıktısını verir.{/code}
Not: 20000 – 20999 arasındaki sayılar kullanıcılara ayrılmıştır.
{code class=”brush: xml;”}begin

maas :=3000;
select round(avg(salary))
into ort_maas
from employees;

if (maas < ort_maas) then
raise_application_error(-20100,’Maaş ortalama maaştan küçük olamaz.’);
end if;

dbms_output.put_line(‘Ort_Maaş: ‘ ||ort_maas);

end;
{/code}
{code class=”brush: xml;”}”ORA-20100: Maaş ortalama maaştan küçük olamaz.” şeklinde hata verir.{/code}
Prosedurun ya da fonksiyonun içine bunun konulması istenmez. Çünkü hata kullanıcıya değil application server a gelir.

{tab=Cursor}

Explicit Cursors
5 Önemli konudan biri.
{code class=”brush: xml;”}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;
{/code}

{code class=”brush: xml;”}100 Steven King
101 EMRAH Kochhar
102 Lex De Haan
103 Alexander Hunold
…..{/code}
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.

{code class=”brush: xml;”}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;
{/code}

{code class=”brush: xml;”}1 100 Steven King
2 101 EMRAH Kochhar
3 102 Lex De Haan
…….{/code}
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.

{code class=”brush: xml;”}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;
{/code}

–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.
{code class=”brush: xml;”}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;{/code}

{code class=”brush: xml;”}update emp
set isim = first_name||’ ‘ || last_name
–şeklinde de yapılabiliyor .{/code}
{code class=”brush: xml;”}select e.first_name, e.last_name, d.department_name department_name
from emp e, departments d
where e.department_id = d.department_id ;
{/code}

{code class=”brush: xml;”}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; {/code}
2. cursor parametre alan cursordır. 1. nin department_id sini alıyor o department_id sine sahip üyeleri getiriyor.
{code class=”brush: xml;”}Administration
Jennifer Whalen
Marketing
Michael Hartstein
Pat Fay
Purchasing
Den Raphaely
Alexander Khoo
Shelli Baida
…. {/code}
truncate : rollback yapılamayanı, redo kullanmaz.
select count(*) from emp ;
{code class=”brush: xml;”}begin
truncate table emp ;
end;{/code}
bu direk çalışamıyor (DDL,create drop,alter truncate ) dynamic sql istiyor.
{code class=”brush: xml;”}begin
execute immediate ‘truncate table emp’ ;
end;{/code}
{code class=”brush: xml;”}declare

tablo_adi varchar2(30);
sayi number;

begin
tablo_adi := ’emp’;

select count (*)
into sayi
from tablo_adi;
dbms_output.put_line(sayi);
end; {/code}
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.
{code class=”brush: xml;”}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;
{/code}
{code class=”brush: xml;”}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; {/code}
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

{code class=”brush: xml;”}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;
{/code}

{code class=”brush: xml;”}select table_name , num_rows from user_tables;{/code}
Bu şekilde de bulunabilir.
{code class=”brush: xml;”}analyze table emp compute statistics;{/code}
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
{code class=”brush: xml;”}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.{/code}
{code class=”brush: xml;”}select ‘analyze table ‘ || table_name|| ‘ compute statistics;’ from user_tables;{/code}
İ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.
{code class=”brush: xml;”}select table_name , num_rows from user_tables;{/code}
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
{code class=”brush: xml;”}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;{/code}
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
{code class=”brush: xml;”}select trunc(dbms_random.value(1,50)) from dual;{/code}
{code class=”brush: xml;”}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; {/code}
{code class=”brush: xml;”}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; {/code}
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.
{code class=”brush: xml;”}REGIONS -> 4
LOCATIONS -> 23
JOBS -> 19
MLOG$_EMPLOYEES -> 0 {/code}
Şeklinde çıkış alınır (toad da bozuk çıkabiliyor .)
{code class=”brush: xml;”}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; {/code}
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.
{code class=”brush: xml;”}select *
from employees
for update ;{/code}
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.
{code class=”brush: xml;”}select *
from employees
for update of salary; {/code}
Bu şekilde sadece salary kolonu kilitlenir. Çalıştıracağınız cursor sırasında güncelleme yapılmasını istenmiyorsa kullanılır.
{code class=”brush: xml;”}declare
cursor employees is
select *
from employee
for update of salary, last_name ;{/code}
salary ve last_name kolonları bu cursor kapanana kadar kilitli kalır.
{code class=”brush: xml;”}update tablo_ismi
set salary = 5000
where current of c_member{/code}
Üzerinde bulunduğum kaydın maaşını 5000 yap. Bu aşağıdaki gibi de yapılabilir.
Tercih edilen ;
{code class=”brush: xml;”}update tablo_ismi
set salary = 5000
where employee_id = r_member.employee_id {/code}
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.
{code class=”brush: xml;”}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; {/code}
{code class=”brush: xml;”}Ellen Abel
Sundar Ande
Mozhe Atkinson
…….{/code}
Bu şekilde declare bölümünde cursor tanımlamaya gerek kalmıyor.
Nested blocks

{code class=”brush: xml;”}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;{/code}

{code class=”brush: xml;”}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; {/code}
{code class=”brush: xml;”}begin
fonk1()
proc1()
fonk2()
fonk3(){/code}
Örneğin proc da hata verdi proc un içinde exception ile hata yakalanırsa diğerleri çalışmaya devam eder.

 

 {tab=Fonksiyon}

Fonksiyon
sysdate sistem tarihini getiren bir fonksiyondur .
dbms_output.put_line———-< prosedurdur.
Fonksiyon bir değer döndürür. (SELECT) Fonksiyonun içinde genelde select li işler yapılır. Takım id sini ister , girilen takım id sinin ismini döndürür.
Örnek türkçe tarih dönder ;
create or replace function ; (view da da c. or r. ) düşürülürse haklar gidiyor . bu yüzden create or replace tercih ediliyor haklar gitmiyor, kot değişmiş oluyor)
{code class=”brush: xml;”}create or replace function turkce_tarih_getir return varchar2 is
tarih varchar2 (15);{/code}
{code class=”brush: xml;”}begin
select to_char(sysdate, ‘DD/MM/YYYY’)
into tarih
from dual;

return tarih;
end; {/code}
Toad’da editorden “run as script” ile de çalıştırılabilir.
{code class=”brush: xml;”}select turkce_tarih_getir from dual; ——–> ile sonuç görülebilir.{/code}
Bir fonksiyon her tablo üzerinde çalışır, ama o tablodaki satır sayısı kadar sonuç dönderir.
{code class=”brush: xml;”}select turkce_tarih_getir from emp;{/code}
Dual den tek sonuç döner. Alternatif olarak tek_satir diye bir tablo oluşturulup bu tabloya tek satırlık giriş yapılır bu şekilde de tek dönüş yaptırılabilir. Sysdate in yerine alternatif bir fonksiyon oluşturmuş olduk.
Örnek büyük harfe çevir;
{code class=”brush: xml;”}create or replace function buyuk_harfe_cevir (p_kelime varchar2) return varchar2 is
buyuk_kelime varchar2(100);

begin
buyuk_kelime := upper(p_kelime);
return upper(p_kelime);
end;{/code}
{code class=”brush: xml;”}select buyuk_harfe_cevir(‘mehmet’) from dual;
select first_name, buyuk_harfe_cevir(first_name) from emp; –fn lerin yanına bir kolon ekleyip büyük hallerini getirir.
select buyuk_harfe_cevir(first_name) from emp ; –fn leri büyük harfe çevirir.
first_name üzerine bir fonksiyon uygulanıyor.
{/code}
{code class=”brush: xml;”}create public synonym bhc for buyuk_harfe_cevir; hr kulanıcısının hakkı olmayabilir. grant create public synonym to hr;
create synonym bhc for buyuk_harfe_cevir; bu yüzden sadece kendisi için oluşturur. ya da hak verilir.{/code}
Fonksiyonun kullanıldığı yerler : takım adı getir, ben id yi yolliyim o takım adı göndersin gibi
{code class=”brush: xml;”}create or replace function dept_adi_getir(p_deptno number) return varchar2 is
dept_name varchar2(30);

begin
select department_name
into dept_name
from departments
where department_id = p_deptno;

return dept_name;

exception
when others then
return SQLERRM;

end; {/code}
{code class=”brush: xml;”}select dept_adi_getir (100) from dual;
select dept_adi_getir (108) from dual; –exception koyduğumuz için veri olmadıgında hata donecektir.{/code}
{code class=”brush: xml;”}create or replace function dept_adi_getir(p_deptno number) return varchar2 is
dept_name varchar2(30);

begin
select department_name
into dept_name
from departments
where department_id = p_deptno;

return dept_name;

exception
when no_data_found then
return p_deptno || ‘ numaralı takım yok ‘ ;

end;{/code}
{code class=”brush: xml;”}”108 numaralı takım yok ” şeklinde dönüş yapar.{/code}

 

{tab=Prosedur}

Prosedur

Prosedur ile  insert,update delete  gibi işlemler yaptırılır. Prosedurlerle select işlemleri pek yapılmaz, yapısı pek uygun değildir.

Tablo silerken kimi drop, kimi purge ı kullanılıyor standart kullanım için prosedur yazıp herkesi bunu kullanmaya zorlayabiliriz. Buna dba karar verir
{code class=”brush: xml;”}create or replace procedure tablo_dusur (p_tabloadi varchar2) is
begin

execute immediate ‘drop table ‘ || p_tabloadi || ‘ purge’ ; — ‘ purge’ şeklinde olacak. ‘ ile purge arasında boşluk olacak.
dbms_output.put_line(p_tabloadi || ‘ tablosu düşürüldü. ‘);

end;{/code}
{code class=”brush: xml;”}begin
tablo_dusur (’employees_2′) ;
end;{/code}
iki şekilde de çalıştırılabilir.
{code class=”brush: xml;”}exec tablo_dusur(’employees_3′);{/code}
{code class=”brush: xml;”}create or replace procedure dept_ekle (p_deptid number, p_deptadi varchar2) is
begin

insert into dept(department_id, department_name) –DDL olduğu için exec e gerek kalmadan direk yazılabilir.
values (p_deptid, p_deptadi);

commit;

end; {/code}
{code class=”brush: xml;”}exec dept_ekle(800, ‘sekizyuz’){/code}
{code class=”brush: xml;”}select upper(‘ibrahimİBRAHİM’) from dual; —–>IBRAHIMİBRAHİM
select lower(‘IĞDIRığdırİBRAHİM’) from dual; —>iğdirığdırıbrahım{/code}
Bu oracle ın bug ıdır.
{code class=”brush: xml;”}select ‘ismail’ from dual;
select replace(‘ismail’,’i’,’İ’) from dual;{/code}
küçük “i” leri replace yapılarak çözebiliriz.
{code class=”brush: xml;”}select upper(‘ibrahimİBRAHİM’) from dual;
select lower(‘IĞDIRığdırİBRAHİM’) from dual;
select ‘ismail’ from dual;
select replace(‘ismail’,’i’,’İ’) from dual;

from personel
where upper(lower(ad))=upper(lower(‘omer’))
where buyult(ad)=buyult(‘omer’){/code}
Çalıştıran Kullanın tablolarını ve indexlerinin istatistiklerini toplar.
{code class=”brush: xml;”}create or replace procedure tablo_istatistikleri_topla is
cursor c_tablo is
select table_name
from user_tables;
begin
for r_tablo in c_tablo
loop
execute immediate ‘analyze table ‘ || r_tablo.table_name || ‘ compute statistics’;
dbms_output.put_line(r_tablo.table_name || ‘ analize edildi.’);
end loop;
end;{/code}
exec tablo_istatistikleri_tablo –şeklinde de çalıştırılabilir. İstenirse toad yazılımının jobs sekmesinden zamanlanabilir.
Komut ortamındada bu şekilde zamanlanabilir.
{code class=”brush: xml;”}DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.submit
(job => jobno,
what => ‘HR.TABLO_ISTATISTIKLERI_TOPLA;’,
next_date => trunc(sysdate)+23/24,
interval => ‘SYSDATE + 1’,
no_parse => TRUE );

DBMS_OUTPUT.put_line (‘Created Job – the job number is:’ || TO_CHAR (jobno));
COMMIT;
END;
/{/code}

Not :”create table xxxxx as select * from yyy ” bu şekilde oluşturulan tablolarda constraintler taşınmıyor.
indexler tablolardaki kayıt sayısına user_tables dan bakar

insert Stress

{code class=”brush: xml;”}begin
for i in 1..5
loop
insert into emp2
select * from emp2 ;
commit;
end loop ;
end;{/code}

Bir şemadaki tabloların satır Sayısı Karşılaştıran prosedür

{code class=”brush: xml;”}Create or replace procedure satir_sayisi_karsilastir is
cursor c_tablo is
select table_name, num_rows
from user_tables;

oracle_sayi number;
gercek_sayi number;

begin
dbms_output.put_line(‘GERCEK ORACLE’);
dbms_output.put_line(‘—— ——‘);

for r_tablo in c_tablo
loop
oracle_sayi := r_tablo.num_rows;

execute immediate ‘select count(*) from ‘ ||r_tablo.table_name into gercek_sayi;

if (oracle_sayi = gercek_sayi) then
dbms_output.put_line(r_tablo.table_name||’-‘||gercek_sayi||
‘*****’||r_tablo.table_name||’-‘||oracle_sayi);
else

dbms_output.put_line(‘Dikkat : ‘|| r_tablo.table_name||’-‘||gercek_sayi||
‘********’||r_tablo.table_name||’-‘||oracle_sayi);

end if ;

end loop;

end;{/code}

{code class=”brush: xml;”}Create or replace procedure satir_sayisi_karsilastir is
cursor c_tablo is
select table_name, num_rows
from user_tables;

oracle_sayi number;
gercek_sayi number;

begin
dbms_output.put_line(‘GERCEK ORACLE’);
dbms_output.put_line(‘—— ——‘);

for r_tablo in c_tablo
loop
oracle_sayi := r_tablo.num_rows;

execute immediate ‘select count(*) from ‘ ||r_tablo.table_name into gercek_sayi;

if (oracle_sayi = gercek_sayi) then
null;
— dbms_output.put_line(r_tablo.table_name||’-‘||gercek_sayi||
— ‘*****’||r_tablo.table_name||’-‘||oracle_sayi);
else

dbms_output.put_line(‘Dikkat : ‘|| r_tablo.table_name||’-‘||gercek_sayi||
‘********’||r_tablo.table_name||’-‘||oracle_sayi);

end if ;
end loop;
end;{/code}

not : is de as destekleniyor. end procedure name yazılabilir de yazılmayabilir de

in & out
{code class=”brush: xml;”}create or replace procedure emp_ad_soyad_getir
(p_employee_id number, ad out varchar2, soyad out varchar2, maas out number) is — (p_employee_id in number, ) burada hiçbirşey söylenmediginde aslında in denilmiş olur
begin
select first_name, last_name, salary
into ad, soyad , maas
from employees
where employee_id = p_employee_id ;
end ;{/code}

aşağıdakiler sqlplus ta çalıştırırlır
{code class=”brush: xml;”}variable ad varchar2(20)
variable soyad varchar2(20) — bu ikisi sqlplus da var muhtemelen toad da çalışmaz.
exec easg(206, :ad, :soyad); –easg emp ad soyad getir. şeklinde çalıştırılacak.
print ad
print soyad{/code}
{code class=”brush: xml;”}create synonym easg for emp_ad_soyad_getir{/code}

{code class=”brush: xml;”}declare
ad varchar2(20);
soyad varchar2(20);
maas number;
begin
easg(206, ad, soyad, maas); –iki nokta sqlplus ortamında tanımlanan değişkenlere ulaşmak için kullanılır. bu yüzden burada kullanılmıyor.
dbms_output.put_line(ad||’ ‘||soyad||’ ‘||maas);
end;{/code}

prosedürün Fonksiyona göre avantajı ; fonk tek değer döner ya ad ya soyad yada ad soyad birleşik döner. ama ayrı ayrı iki değer dönemez. adı alıp bir değişene atamaz . prosedür bunu yapabiliyor. Fonksiyon bunları yapamaz. fonk ya number ya varchar2 dir. procedure ise out ile aynı anda birçok değişkeni ve tipini alabiliyor.

prosedure ve fonksiyona program unit de denmektedir. Bunlar üzerinde sadece çalıştırma (execute) hakkı verilebilir.
{code class=”brush: xml;”}gran execute on raise_salary to student01;{/code}
fonsiyon procedure oluştururken çıkan hataları
{code class=”brush: xml;”}show errors — ile görülebilir. veya
select * from user_errors –den de görülebilir.
show errors procedure raise_salary –fazla sayıda hata varsa {/code}
Compile/Recompile
{code class=”brush: xml;”}alter procedure raise_salary compile ;
alter function raise_salary compile ;{/code}
Not: 10G de invalid duruma düşen prog parçaları , çağrıldığında tekrar compile etmeye çalışır eğer düzeltildi ise sorun kalmaz
{code class=”brush: xml;”}select object_name, status
from user_objects{/code}
{code class=”brush: xml;”}drop procedure raise_salary ;
drop function salary_valid ;
drop type name ;{/code}
{code class=”brush: xml;”}select * from
from user_objects
where object_type=’PROCEDURE’ {/code}
toad da kodu görebiliyoruz. toad yoksa user_source view/table dan da görebiliyoruz.

 {tab=Packages}

Packages

projelerde çok sayıda fonksiyon ve procedure olabilir. Üretimin fonksiyonları nelerdir toparlayalım !
fonkskiyon ve procedurelerin ne için oluşturulduğunun bilinmesi gerekir.

paket (package) prosedur ve fonksiyonları bir arada toplar. 2 kısımdan oluşur spec ve body,
spec : tanımların tutulduğu yer.
body : kodların tutuldugu yer.

{code class=”brush: xml;”}CREATE OR REPLACE PACKAGE tarihsaat is –as de kullanılabilir.

function tarih_dondur return varchar2;
function saat_dondur return varchar2;
procedure tarih_saat_yazdir ; –procedurun dönüş tipi yokmuş
end ;

CREATE OR REPLACE PACKAGE BODY tarihsaat is

function tarih_dondur return varchar2 is
tarih varchar2(20);

begin
select to_char(sysdate, ‘DD/MM/YYYY’)
into tarih
from dual;

return tarih ;
end;

function saat_dondur return varchar2 is
saat varchar2(20);
begin
select to_char(sysdate, ‘HH24:MI:SS’)
into saat
from dual;

return saat;
end;

procedure tarih_saat_yazdir is
begin
dbms_output.put_line(sysdate);
end;
end;{/code}

tanımda olan herşey body de olmak zorunda dışarı açılması istenmeyen program parçaları spec kısmına konulabilir.
Fonk ve prosedürü dışarı kapama şansı yoktur ama paket dışarı kapanabilir.

paket
{code class=”brush: xml;”}select TARIHSAAT.SAAT_DONDUR from dual ; — şeklinde çalıştırılabilir.{/code}

procedure bu şekilde çalıştıramayız ya begin end arasında ya da exec ile çalıştırabiliriz.
paketten herhangi bir sey cagrıldıgında paketteki hersey memory ye yüklenir.
projelerde kendi başına fonk/proce olmamalı herşey paketlerde olmalı. Profesyonel projeler için herşeyi paket içinde oluşturmak en iyisi

Örnek :

{code class=”brush: xml;”}CREATE OR REPLACE PACKAGE dept_ISLERI is
procedure dept_ekle(p_deptno number, p_deptadi varchar2);
procedure dept_cikar(p_deptno number);
–procedure dept_yazdir;
–function dept_adi_getir (p_deptno number) return varchar2;
–function dept_sayisi_getir return number;
end;

CREATE OR REPLACE PACKAGE BODY dept_ISLERI is

procedure dept_ekle(p_deptno number, p_deptadi varchar2) is
begin
insert into dept(department_id, department_name)
values (p_deptno, p_deptadi);
end;

procedure dept_cikar(p_deptno number) is
begin
delete from dept
where department_id = p_deptno;

if (SQL%NOTFOUND) then
raise_application_error(-20100, ‘Böyle bir department yok’);
end if;
exception
when others then
raise_application_error(-20100, SQLERRM);

 end;

end;{/code}

UTL
oracle sunucuda fiziksel dosya açma okuma yazma işlemleri için kullanılır (CSV vb.) (sunucu üzerinde , client üzerinde değil)
mail göndermek için utl_mail
dosya okuma yazma işlemleri için utl_file

employees kayıtlarını okuyup bir text file yazdırmak için.
utl_file “c:\” nin root una yazdıramazsınız ama bir direktory ye yazdırabilirsiniz.

create directory Raporlar as ‘/u02/UTL_Klasor’ ;
-kullanıcıya directory oluşturma izni verilebilir.
grant create any directory to kullanıcı_ismi;

ya da
system kullanıcısı ile directory oluşturup kullanıcıya okuma yazma hakkı verilir.
grant read,write on directory Raporlar to kullanıcı_adı ;

Kullanıcının(user) klasörü (directory) yoktur kim oluşturursa oluştursun sys ait görünüyor “dba_directories” den sorgulanabilir.

UTL_FILE, parametreleri

{code class=”brush: xml;”}fopen ; Dosyayı açar
is_open : dosya açıkmı kapalı mı
put : birşey yazmak için
new_line : alt satıra geçmek için
put_line : yazıp alt satıra geçmek için
get_line : okumak için
put lar yazmak için get ler okumak için
fseek : dosyayı bulmak için
fclose : dosyayı kapatmak için. {/code}

directory ismi mutlaka büyük harflerle yazılmalı

{code class=”brush: xml;”}declare
textfile utl_file.file_type;
cursor c_employees is
select employee_id, first_name, last_name
from employees ;

satir varchar2(500);

begin
textfile := utl_file.fopen(‘RAPORLAR’,’employees_kayit.txt’,’w’,32767);

for r_employees in c_employees
loop

satir := r_employees.employee_id||’,’||r_employees.first_name||’,’||r_employees.last_name;
utl_file.PUT_LINE(textfile,satir);

end loop;

utl_file.fclose(textfile);

end;{/code}

{code class=”brush: xml;”}declare
satir varchar2(500); –4000 e kadar çıkabilir.
textfile utl_file.file_type;

begin

textfile := utl_file.FOPEN(‘RAPORLAR’,’employees_kayit.txt’,’r’,32767);

if not utl_file.IS_OPEN(textfile) then
dbms_output.put_line(‘Dosya Acilamadi’);
else
loop
utl_file.GET_LINE(textfile,satir);
dbms_output.put_line(satir);
end loop;
end if ;

utl_file.fclose(textfile);

exception
when no_data_found then
utl_file.fclose(textfile);
when others then
dbms_output.put_line(SQLERRM);
utl_file.FCLOSE_ALL;

end ;{/code}

{tab=Trigger}
Trigger
tablo ve veri tabanı triggerları vardır
tablo triggerları insert update delete öncesinde veya sonrasında devreye giren mekanizma örneğin aralarında link bulunan iki VT’den birindeki x tablasuna insert edildiğinde aynı verinin y tablosuna da geçmesi isteniyorsa burada trigger kullanılabilir. (insert trigger -before insert after insert )
Bir tabloya mesai saatleri dışında değişiklik yapılması istenmiyorsa o tabloya mesai saatlerini kontrol eden bir trigger konulabilir.

statement level trigger ; insert statementı yapılıyorsa o statement için çalışan trigger. (bu saatte insert edemesin vb.)

row level trigger ; Tablodaki her satır için çalışır. Değiştirilen herseyin başka bir yerde de değiştirilmesi gerekiyorsa kullanılır. Etkilenen her satır için.

instead of trigger ; view üzerinden update etmek istenirse , iot ile bu view a update yapılmak istenirse git şurları güncelle
gibi istekler olursa kullanılır çok fazla bir kullanım alanı yoktur

örnek : mesai saatleri dışında employees tablosunda delete/update/insert yapılması istenmiyorsa
{code class=”brush: xml;”}create or replace trigger security_time_check
before insert or update or delete on emp –before update of salary on emp şeklinde de tanımlama yapılabilir.

declare
gun varchar2(3);
saat number(2);

begin

gun := to_char(sysdate, ‘DY’, ‘NLS_DATE_LANGUAGE=TURKISH’);
saat := to_number(to_char(sysdate, ‘fmHH24’));

if (gun = ‘CMT’ OR gun = ‘PAZ’) OR (saat not between 8 and 17) then

raise_application_error(-20100, ‘Mesai saatleri dışında değişiklik yapamazsınız’);
end if ;

end;{/code}
Disable etmek için
{code class=”brush: xml;”}alter trigger security_time_check disable ; {/code}

{code class=”brush: xml;”}create or replace trigger security_time_check
before insert or update or delete on emp

declare
gun varchar2(3);
saat number(2);

begin

gun := to_char(sysdate, ‘DY’, ‘NLS_DATE_LANGUAGE=TURKISH’);
saat := to_number(to_char(sysdate, ‘fmHH24’));

if (gun = ‘CMT’ OR gun = ‘PAZ’) OR (saat not between 8 and 17) then

if inserting then

raise_application_error(-20100, ‘Mesai saatleri dışında emp de insert yasak’);
elsif updating then
raise_application_error(-20100, ‘Mesai saatleri dışında emp de update yasak’);
else
raise_application_error(-20100, ‘Mesai saatleri dışında emp de delete yasak’);
end if;

end if ;

end;{/code}

Row LEvel Triggers
for each row : tüm tablo için değil her satır için çalışıyor.
Örnekmaaş update edilirken ortalama maaşın altında olmasın.

{code class=”brush: xml;”}create or replace trigger ort_maasdan_buyuk_olmali
before insert or update or delete of salary on emp
for each row

declare
ort_maas number ;
begin
select round(avg(salary))
into ort_maas
from emp ;

if :new.salary < ort_maas then — yeni maas
raise_application_error(-20100, ‘Maasin yeni degeri Ortalama maasdan(‘||ort_maas||’) kucuk olamaz’);
end if ;

end;{/code}

satir bazin calistigi icin ortalama maaşı bulamıyor.

log tablosu
{code class=”brush: xml;”}create table emp_log_tablo
(
kullanici varchar2(20),
tarihsaat date,
tablo_adi varchar2(30),
islem varchar2(10),
employees_id number,
eski_maas number,
yeni_maas number
){/code}

{code class=”brush: xml;”}create or replace trigger trg_emp_maas_log
after update of salary on emp
for each row

declare

begin
INSERT INTO HR.EMP_LOG_TABLO (
KULLANICI, TARIHSAAT, TABLO_ADI,
ISLEM, EMPLOYEES_ID, ESKI_MAAS,
YENI_MAAS)
VALUES (user ,sysdate ,’employees’ ,’update’,:new.employee_id , :old.salary , :new.salary );

end;{/code}

tablo olustur
{code class=”brush: xml;”}create table musteri
(
no number primary key,
ad varchar2(20),
soyad varchar2(20)
){/code}

squence olustur
{code class=”brush: xml;”}create sequence seq_musteri ;
insert into musteri
values (seq_musteri.nextval,’Musteri1′, ‘Soyad1’){/code}

trigger olustur
{code class=”brush: xml;”}create or replace trigger trg_bef_ins_musteri
before insert on musteri
for each row
declare
begin
select seq_musteri.nextval
into :new.no
from dual;
end;{/code}

test edilir
{code class=”brush: xml;”}insert into musteri(ad, soyad)
values (‘Deneme’, ‘Yanilma’){/code}

new ve old kelimelerini yalnızca for each row için kullanabiliriz yani row level de kullanabiliriz. statement level de kullanamayız.

Auto increment bir alan isteniyorsa en iyi çözüm sequence + trigger dır. Kimin hangi tarihte insert ettiğini loglamak için.

tablo olustur
{code class=”brush: xml;”}create table musteri_log
(
kullanici varchar2(20),
islem varchar2(10),
tarihsaat date
){/code}

procedure olustur
{code class=”brush: xml;”}create or replace procedure proc_musteri_logla
(kullanici varchar2, islem varchar2) as

begin
insert into musteri_log
values(kullanici, islem, sysdate);

end;{/code}

trigger olustur
{code class=”brush: xml;”}create or replace trigger trg_after_ins_musteri
after insert on musteri
for each row
declare
begin
proc_musteri_logla(user, ‘insert’);
end;{/code}

2. alternatif
{code class=”brush: xml;”}create or replace trigger trg_after_ins_musteri_1
after insert on musteri

call proc_musteri_logla(user,’insert’){/code}

not : begin end olursa call olmayacak

test edilir
{code class=”brush: xml;”}insert into musteri (ad, soyad)
values (‘test’, ‘deneme’){/code}

{code class=”brush: xml;”}alter trigger trg_after_ins_musteri compile
alter trigger trg_after_ins_musteri disable/enable{/code}

yeni VT’ lerde long alanlar clob olarak tutuluyor. long (2GB tutar) karakter tutar en önemli fark clobla (4GB tutar)
long alan içinde text araması yapılamaz

örnek
{code class=”brush: xml;”}create table yazi1
(
no number,
yazi long
);{/code}
{code class=”brush: xml;”}create table yazi2
(
no number,
yazi clob
);{/code}

{code class=”brush: xml;”}insert into yazi1 values(1,’test test’)
insert into yazi2 values(1,’test test’){/code}
{code class=”brush: xml;”}select * from yazi1 where yazi like ‘%test%’ {/code}
Bu sorgu yazi2 için sonuç dönderir ama yazi1 de hata verir.

type… table
index by table diye tanımlanıyor. Binary integer veya PLS_integer , PLS daha hızlı oldugundan tercih ediliyor.

{code class=”brush: xml;”}declare
type sayi_listesi is table of number
index by pls_integer;

sayilar sayi_listesi;

begin

FOR i IN 1 .. 6 LOOP

sayilar(i) := i + 5;

END LOOP;

FOR i IN sayilar.first..sayilar.last LOOP

dbms_output.put_line(sayilar(i));

END LOOP;

end;{/code}

{code class=”brush: xml;”}declare
type sayi_listesi is table of number
index by pls_integer;

sayilar sayi_listesi;

begin

FOR i IN 1 .. 6 LOOP

sayilar(i) := i + 5;

END LOOP;

FOR i IN sayilar.first..sayilar.last LOOP

dbms_output.put_line(sayilar(i));

END LOOP;

dbms_output.put_line(‘sayi:’||sayilar.count);

end;{/code}

{code class=”brush: xml;”}declare
type sayi_listesi is table of number
index by pls_integer;

sayilar sayi_listesi;

begin

FOR i IN 1 .. 6 LOOP

sayilar(i) := i + 5;

END LOOP;

FOR i IN sayilar.first..sayilar.last LOOP

dbms_output.put_line(sayilar(i));

END LOOP;

dbms_output.put_line(‘sayi:’||sayilar.count);

if (sayilar.exists(6)) then
dbms_output.put_line(‘6. eleman var.’);
end if;

end;{/code}

sayılarda 6. eleman var mı ?

{code class=”brush: xml;”}çktısı
6
7
8
9
10
11
sayi:6
6. eleman var.{/code}

sayilar.first sayilar 1 e götürüyor
sayilar.last sayilar 6 ya götürüyor
sayilar.count içinde kaç eleman olduğunu gösteriyor.
sayilar.exists 0 var mı 6 var mı 7 var mı vb.

{code class=”brush: xml;”}declare
type sayi_listesi is table of number
index by pls_integer;

sayilar sayi_listesi;

begin

FOR i IN 1 .. 6 LOOP

sayilar(i) := i + 5;

END LOOP;

FOR i IN sayilar.first..sayilar.last LOOP

dbms_output.put_line(sayilar(i));

END LOOP;

dbms_output.put_line(‘sayi:’||sayilar.count);

if (sayilar.exists(6)) then
dbms_output.put_line(‘6. eleman var.’);
end if;

end;{/code}

 {/tabs}

{jcomments on}

Copyright © 2015 Sysdba All Rights Reserved Web Designer