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)

set serveroutput on
declare
dept_name varchar2(30);
begin
select department_name
into dept_name
from departments
where department_id=120 ;
dbms_output.put_line('Department Name:'|| department_name);
end ;

çıktısı

Bölüm Adı:Treasury

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}
set serveroutput on -- no need to use toad (however toad is necessary if wanting to: execute as script)
begin
dbms_output.put_line('Merhaba dünya');
end ;
-- a single line of comment
/*
paragraf comment buraya yazılır
*/

Tüm komutlar: " ; " ile bitirilir.
Örnek : Ekrana Merhaba yazdırır.

SQL> begin
2 dbms_output.put_line('Merhaba');
3 end;
4 /
Merheba
PL/SQL procedure successfully completed.

örnek : ekrana merhaba1 den 5 e kadar yazdırır.

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
Merhaba1
Merhaba2
Merhaba3
Merhaba4
Merhaba5
PL/SQL procedure successfully completed.

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.

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;
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

Hata mesajını yakalayabilmek için örneği aşağıdaki gibi değiştirelim.

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;
45 kodlu bölüm adi bulunamadi
PL/SQL procedure successfully completed.

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.

begin
drop table members; bu yazılamaz.
end;

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.)

employee.salary%TYPE ---> employee tablosunun salary kolonu tipinde

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.

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;
Douglas Grant
PL/SQL procedure successfully completed.

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.

Declare
EmpLname VARCHAR2(20)
EmpSalary NUMBER(6)
EmpBDate DATE

PLSQL' de bunlara ek olarak boolean da tanımlanabiliyor. sql de tanımlanamıyor. İsimlendirme kuralları değişkenler için de geçerli.

*max 30 karakter
*harf ile başlamak zorunda
*içinde harf rakam ve işaret olabilir.
*izin verilen işaretler $, #, _ underscore

Bunlar tablo , kolon ve pl sql de de değişken isimlerinde geçerli.

Veri Tipleri

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.
Declare
NewEmpSalary Number(6) :=25000;
NewEmpSal Number(6) DEFAULT 25000;
"default" = " := " in yerine geçer . genelde kullanılan " := "
Declare
EndTheLoop := FALSE;

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.

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;
declare
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;
declare
erkekmi boolean;
begin
erkekmi :=false;
if (erkekmi) then
dbms_output.put_line('erkek');
else
dbms_output.put_line('bayan');
end if ;
end;

-- bunun çıktısı bayan oluyor,
-- false i true yaparsak erkek olur.

boolean kolonda kullanılamaz

select dbms_random.value(1,5) from dual;

0 ile 49 arasında tesadüfi sayılar çıkar
declare da constant tanımlanırsa verinin değeri değiştirilemez.
Begin Clause

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;
declare
tarih date ;
ad varchar2(20);
begin
tarih := sysdate;
ad := user;
dbms_output.put_line('tarih:'||tarih);
dbms_output.put_line('ad:'|| ad);
end;

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

Karşılaştırma Operatörleri

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;

--sayilardan birini 10 dan küçük değer verirsek ekrana birşey yazmaz.

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;
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;
declare
max_maas number;
begin
select max(salary)
into max_maas
from hr.employees;
dbms_output.put_line('en yuksek maas'||max_maas);
end;
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;

Bu tek sorgu ile de yapılabilir.

create table copy_dept as
select * from departmenst
where 1=2 ;

--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.

begin
insert into copy_dept (department_id, department_name)
values (10, 'test') ;
commit;
end;
begin
insert into copy_dept (department_id, department_name)
values (10, 'test') ;
commit;
end;

--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

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;

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

alter table employee
add EmpID NUMBER(4) CONTSTRAINT employeePK PRIMARY KEY NOVALIDATE ;

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.

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 ;
/
declare
count number;
begin
count := 1;
loop
dbms_output.put_line(count);end loop;
end ;

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.

declare
sayi number;
begin
sayi := 1;
loop
dbms_output.put_line(sayi);end loop;
end ;

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.

declare
sayi number;
begin
for i in 1..10
loop
dbmps_output.put_line(sayi);
end loop
end ;
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop ;
end ;

Bitirilecek sayı bilindiğinde for kullanılıyor.

BEGIN
FOR i in reverse 1..10 LOOP --10 dan 1 e kadar

WHILE
Bitirilecek sayı bilinmediğinde kullanılır.

declare
sayi number;
begin
sayi := 1;
while (sayi<=10)
loop
dbms_output.put_line(sayi);
sayi := sayi+1;
end loop ;
end ;

Not: Bu örnek çalışmadı.

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;

CASE
Decode ile case arasındaki fark ; case tek başına kullanılabilir. Decode kullanılamaz.

select decode (........
into
select decode diyip into ile cinsiyete atabilirsiniz
case de ise
:= decode (cinsiyet. 'E', 'Erkek', 'K', 'Kız', 'Girilmedi');
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;
/

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"

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;

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.

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;
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;
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;
"400 Nolu kayıt bulunamadı." Hatası alınır.

"0" a bölünme oracle da bir hatadır divide by zero diye bir hata verir.

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;

"hata oluştu" mesajı alınır.

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;

"sıfıra bölünme " hatası alınır.

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.

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

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;
"Hata oluştu"
exception when others then null;

Bunun anlamı kimseye mesaj gösterme, exception koyma daha iyi Gerçekte hatanın olduğunu görmek için others iptal edilir.

"There has been an error."
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;
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;
"Beklenenden çok sonuç dönüyor" hatası alınır.
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;
Hata oluştuORA-01422: exact fetch returns more than requested number of rows

"SQLERRM" önemli bir özelliktir. En azından hatanın ne olduğunu yazar.

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;
Hata oluştu -1422 ORA-01422: exact fetch returns more than requested number of rows

SQLERRM daha iyi Bir prosedur ile ORA-01422 hatası gelince türkçe bir mesaj getirtilebilir.

declare
dept_name varchar2 (50);
count number;
begin
--count :=7/0;select department_name
into dept_name
from dept
where department_id=&department;
dbms_output.put_line('department: '|| dept_name );
exception
when no_data_found then
dbms_output.put_line('Unable to find record no. '||&department);
when zero_divide then
dbms_output.put_line('Divide by zero error.');
--when too_many_rows then
--dbms_output.put_line('There have been more results than expected');when others then
dbms_output.put_line('There has been an error. ' ||SQLCODE|| ' ' ||SQLERRM);
end;
The resulting error message: "ORA-01422: exact fetch returns more than requested number of rows"

SQLERRM is a better procedure as it can output the ORA-01422 error dialog in a local language.

exception
when others then
dbms_output.put_line(SQLERRM);

At least it can do something like:

Pragma Exception_INIT

alter table hr.dept modify DEPARTMENT_name not null novalidate;
insert into dept (department_id)
values (700);
Outputs an error such as: " ORA-01400 " : cannot insert null into....

As this is an undefined error, it can be declared using 'pragma', which is a useful way of catching undeclared errors.

declare
not_null_not_allowed exception ;
pragma exception_init(not_null_not_allowed , -1400);begin
insert into dept (department_id)
values (700);
exception
when not_null_not_allowed then
dbms_output.put_line('Error: Can't leave a not null field !');
end;

Hata : Not null alanı geçemezsiniz!
Implicit Cursors

begin
delete from copy_dept
where department_id < 30 ;
dbms_output.put_line(SQL%ROWCOUNT||'records have been deleted.');
end;
"3 kayıt silindi " şeklinde kaç kayıdın etkilendiğini bildirir.
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;
"Silme işlemi gerçekleşstirilemedi" mesajını döner.

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.

declare
salary number;
avg_salary number;
higher_than_avg_salary exception;
begin
salary :=3000;
select round(avg(salary))
into avg_salary
from employees;
if (salary < avg_salary) then
raise higher_than_avg_salary;
end if;dbms_output.put_line('Average Salary: ' ||avg_salary);
exception
when higher_than_avg_salary then
dbms_output.put_line(' The entered amount of (' ||salary||') should not be less that ('||avg_salary||'));
end;
The output "The entered amount of 3000 should not be less than 6462"

Not: 20000 - 20999 arasındaki sayılar kullanıcılara ayrılmıştır.

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;
"ORA-20100: Maaş ortalama maaştan küçük olamaz." şeklinde hata verir.

Prosedurun ya da fonksiyonun içine bunun konulması istenmez. Çünkü hata kullanıcıya değil application server a gelir.


Explicit Cursors
5 Önemli konudan biri.

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.

declare
cursor c_employee is
select employee_id, first_name, last_name
from employees
order by employee_id;
counter number;
begin
counter :=1;
for r_employee in c_employee
loop
dbms_output.put_line(counter||' '||r_employee.employee_id||' '||r_employee.first_name||' '||r_employee.last_name);
counter :=counter+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.

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.

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 name = r_emp.first_name ||' '|| r_emp.last_name
where employee_id = r_emp.employee_id;
commit;
end loop;
end;
-- can be achieved in the following way:
update emp
set name = first_name||' ' || last_name
select e.first_name, e.last_name, d.department_name department_name
from emp e, departments d
where e.department_id = d.department_id ;
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.

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 ;

begin
truncate table emp ;
end;

bu direk çalışamıyor (DDL,create drop,alter truncate ) dynamic sql istiyor.

begin
execute immediate 'truncate table emp' ;
end;
declare
table_name varchar2(30);
sayi number;begin
table_name := 'emp';
select count (*)
into sayi
from table_name;
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.

declare
table_name varchar2(30);
sayi number;begin
table_name := 'emp';execute immediate ' select count(*) from '|| table_name into sayi;
dbms_output.put_line(sayi);
end;
declare
table_name varchar2(30);
sayi number;
kelime varchar2(10) := 'from';begin
table_name := 'emp';execute immediate ' select count(*) '||kelime||' '|| table_name 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

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;

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

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

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)); -- wont exit the loop until sayi2 is unequal to sayi1
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;

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.

REGIONS -> 4
LOCATIONS -> 23
JOBS -> 19
MLOG$_EMPLOYEES -> 0
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.

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.

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.

select *
from employees
for update of salary;
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.

update table_name
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 ;

update table_name
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.

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;
Ellen Abel
Sundar Ande
Mozhe Atkinson

Bu şekilde declare bölümünde cursor tanımlamaya gerek kalmıyor.
Nested blocks

declare
salary number ;
begin
select salary -- Can the "dbms_output.put_line('complete');" line be run even if this select statement results in an error ?
into salary -- This isnt possible in this structure because as soon as there is an error, it will trigger the exception and prevent anything else from running.
from employees -- A second level block can be constructed internally, hence the term nested.
where employee_id>100;
dbms_output.put_line('complete');
delete from employees
where employee_id = -198;exception
when others then
dbms_output.put_line(SQLERRM);end;
declare
salary number ;
begin
declare
begin
select salary
into maas
from employees
where employee_id>100;
exception
when others then
dbms_output.put_line('an error has been encountered');
end;
dbms_output.put_line('complete');
delete from employees
where employee_id = -198;exception
when others then
dbms_output.put_line(SQLERRM);end;
begin
func1()
proc1()
func2()

func3()

Örneğin proc da hata verdi proc un içinde exception ile hata yakalanırsa diğerleri çalışmaya devam eder.

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)

create or replace function get_turkish_date return varchar2 is
date varchar2 (15);
begin
select to_char(sysdate, 'DD/MM/YYYY')
into date
from dual;
return date;
end;

Toad'da editorden "run as script" ile de çalıştırılabilir.

select get_turkish_date from dual;

Bir fonksiyon her tablo üzerinde çalışır, ama o tablodaki satır sayısı kadar sonuç dönderir.

select get_turkish_date from emp;

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;

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;
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.
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.

Fonksiyonun kullanıldığı yerler : takım adı getir, ben id yi yolliyim o takım adı göndersin gibi

create or replace function get_dept_name(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;
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.
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;
"108 numaralı takım yok " şeklinde dönüş yapar.


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

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;
begin
tablo_dusur ('employees_2') ;
end;

iki şekilde de çalıştırılabilir.

exec drop_table('employees_3');
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;
exec add_dept(800, 'eighthundred')
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

Bu oracle ın bug ıdır.

select 'ismail' from dual;
select replace('ismail','i','İ') from dual;

küçük "i" leri replace yapılarak çözebiliriz.

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')

Çalıştıran Kullanın tablolarını ve indexlerinin istatistiklerini toplar.

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;

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.

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;
/

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

begin
for i in 1..5
loop
insert into emp2
select * from emp2 ;
commit;
end loop ;
end;

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

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;

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

in & out

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 ;

aşağıdakiler sqlplus ta çalıştırırlır

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
create synonym easg for emp_ad_soyad_getir
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;

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.

gran execute on raise_salary to student01;

fonsiyon procedure oluştururken çıkan hataları

show errors -- to view errors
select * from user_errors -- an alternative way of viewing errors
show errors procedure raise_salary -- if there are many errors

Compile/Recompile

alter procedure raise_salary compile ;
alter function raise_salary compile ;

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

select object_name, status
from user_objects
drop procedure raise_salary ;
drop function salary_valid ;
drop type name ;
select * from
from user_objects
where object_type='PROCEDURE'

toad da kodu görebiliyoruz. toad yoksa user_source view/table dan da görebiliyoruz.


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.

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;

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

select TARIHSAAT.SAAT_DONDUR from dual ; -- şeklinde çalıştırılabilir.

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 :

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;

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

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.

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

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;

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 ;


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

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;

Disable etmek için

alter trigger security_time_check disable ;
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;

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.

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;

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

log tablosu

create table emp_log_table
(
user_name varchar2(20),
date_time date,
table_name varchar2(30),
transaction varchar2(10),
employees_id number,
old_salary number,
new_salary number
)
create or replace trigger trg_emp_salary_log
after update of salary on emp
for each row
declare
begin
INSERT INTO HR.EMP_LOG_TABLE (
USER_NAME, DATE_TIME, TABLE_NAME,
TRANSACTION, EMPLOYEES_ID, OLD_SALARY,
NEW_SALARY)
VALUES (user ,sysdate ,'employees' ,'update',:new.employee_id , :old.salary , :new.salary );
end;

Create a table

create table musteri
(
no number primary key,
ad varchar2(20),
soyad varchar2(20)
)

tablo olustur

create sequence seq_musteri ;
insert into musteri
values (seq_musteri.nextval,'Musteri1', 'Soyad1')

squence olustur

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;

test edilir

insert into musteri(ad, soyad)
values ('Deneme', 'Yanilma')

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

create table musteri_log
(
kullanici varchar2(20),
islem varchar2(10),
tarihsaat date
)

procedure olustur

create or replace procedure proc_musteri_logla
(kullanici varchar2, islem varchar2) as
begin
insert into musteri_log
values(kullanici, islem, sysdate);

end;

trigger olustur

create or replace trigger trg_after_ins_musteri
after insert on musteri
for each row
declare
begin
proc_musteri_logla(user, 'insert');
end;

2. alternatif

create or replace trigger trg_after_ins_musteri_1
after insert on musteri
call proc_musteri_logla(user,'insert')

not : begin end olursa call olmayacak

test edilir

insert into musteri (ad, soyad)
values ('test', 'deneme')

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

create table yazi1
(
no number,
yazi long
);
create table yazi2
(
no number,
yazi clob
);
insert into yazi1 values(1,'test test')
insert into yazi2 values(1,'test test')
select * from yazi1 where yazi like '%test%'

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.

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;

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;

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;

sayılarda 6. eleman var mı ?

çktısı
6
7
8
9
10
11
sayi:6
6. eleman var.

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.

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;

Copyright © 2015 Sysdba All Rights Reserved Web Designer