Kod: Tümünü seç
SET TERM ^ ;
CREATE PROCEDURE SP_BASIT (AD VARCHAR(15) CHARACTER SET WIN1254)
RETURNS (EN_BUYUK_SIRA_NO INTEGER)
AS
begin
select max(SIRA_NO) FROM musteri
WHERE ADI=:AD
into :EN_BUYUK_SIRA_NO;
suspend;
end ^
SET TERM ; ^
GRANT SELECT ON MUSTERI TO PROCEDURE SP_BASIT;
şimdi Stored Procedureyi tanıyalım
CREATE PROCEDURE > bu kodla prosedürü oluştur komutu veriyoruz. hemen sonrasında prosedüe bir isim vermemiz gerekiyor. ben SP_BASIT Diye bir isim seçtim. siz istediğiniz bir ismi seçebilirsiniz.ö isim tanımlamadan sonra input ( Gitriş ) parametresi olarak adlandırdığımız Alan isimlerini tanımlıyoruz.
(AD VARCHAR(15) CHARACTER SET WIN1254) Gördüğünüz gibi önce alan ismi Sonra alan tipi ve uzunluğunu tanımladıktan sonra Char ve Varchar tipleri için Türkçe karakter desteğini gösteriyoruz. Tıpkı tablolardaki alan tanımlaması gibi,
peki input (giriş) parametresi nedir. input parametrelerini bir değişken yada alan gibi düşünebilirsiniz. input parametreleri dışardan bilgi alıp Stored Procedurede kullanmanızı sağlar. örneğin yukarıda Stored Procedure içinde yazdığımız sorgudaki şart İnput parametreinde tanımladığımız Ad parametresine göre sorguyu gerçekleştiriyor.
Kod: Tümünü seç
select max(SIRA_NO) FROM musteri
WHERE ADI=:AD
Etkisini görmek için Tools menüsünden SQL Editor açıp şu kodu Select * from SP_BASIT ('FAOsoft') yazın Run buttonuna basın. Gördüğünüz gib tıpkı bir tablo gibi sorguluyoruz. burada gönderdiğimiz parametreyi parantez içine alıyoruz. input parametresinden sonra tanımladığımız RETURNS (EN_BUYUK_SIRA_NO INTEGER)
Bu bölümede Output ( çıkış ) parametresi diyoruz çıkış parametreleri Stored Procedurede sorgudan çıkan sonucu Gösterir. OUTPUT parametresi tanımlamazsanız Stored Procedure çalışır ama herhangi bir sonuç döndürmez. Sonuç Döndürmesi için mutlaka Output parametresi tanımlayın . Output parametresi tanımlamadan önce mutlaka RETURNS Komutunu kullanmalısınız. Output parametrelerinide tıpkı input parametreleri gibi tanımlayın. input parametrelwerini Where bölümünde kullandığım alanlarla beraber kullanıyorduk. Output parametreleri ise SP ( stored procedurelere kısaca SP dicem) lerde kullandığımız Sorgu cümlesinde Select ve from arassında seçtiğimiz alanlarla koordineli bir şekilde çalışır.
select max(SIRA_NO) FROM buradan çıkan sonuç OUTPUT parametresi tanımladığımız EN_BUYUK_SIRA_NO parametresine atanır. atama işlemi where bölümündeki şarttan sonra yazdığımız into komutu ile gerçekleşir into komutundan sonra Output parametreinnde tanımladığımız parametreleri önüne ( : ) ekleyerek tanımlayın.
örnek
Kod: Tümünü seç
into :EN_BUYUK_SIRA_NO;
AS > INPUT ve OUTPUT parametreleri ile SP'yi birbirinden ayırt edebillmeyi kolaylaştırır.
suspend; > bu komutu Output parametresi tanımladığınızda End'den önce kullanmalısınız. Output parametresi tanımlayıp Suspend'i kullanmazsanız SP çalışacak ancak herhangi bir değer geriye döndürmeyecektir.
SP'nin başındaki SET TERM ^ ; ve Stored Procedurenin sonundaki SET TERM ; ^ tanmlamaları SP'nin aktif databaseye ait olduğunu gösterir. End'in sonundaki ^ işareti ise sp nin bittiğini göterir. Buradaki ( ^ ) işareti yerine istediğiniz karakteri kullanabilirsiniz. Varsayyılanı değiştiremenizi tavsiye ederim. Sayfadayer işgal etmemesi için aşağıdaki örneklerde kullanılmamıştır.
Sorgu Begin ve End Bloğu Arasında olmalıdır. sorgu sonunda ( ; ) kullanılmalıdır.
son olarak GRANT fonksiyonuyla musteri tablosundan seçme hakkını alıyoruz.
seçme hakkını kaldırmak için GRANT yerine REVOKE fonksiyonunu kullanınız. bu komutun kullanımı ilerki konularda anlatılmıştır.
Kod: Tümünü seç
GRANT SELECT ON MUSTERI TO PROCEDURE SP_BASIT;
REVOKE SELECT ON MUSTERI TO PROCEDURE SP_BASIT;
1. all : Select, delete, insert, update, execute haklarını içerir.
2. select : Bir tablonun tümünü veya bir kısmını görüntüleyebilme.
3. delete : Bir tablodan veya görüntüsünden (view) kayıt silebilme.
4. insert : Bir tabloya veya görüntüsüne kayıt ekleyebilme.
5. update : Bir tablodaki veya gürüntüsündeki kayıtları güncelleyebilme.
6. execute : Bir Depolanmış yordamı (Stored Procedure) çalıştırabilme.
Örnek 2
Kod: Tümünü seç
CREATE PROCEDURE SP_BASIT2 (SIRA INTEGER)
RETURNS (AD VARCHAR(15) CHARACTER SET WIN1254, SOYAD VARCHAR(15) CHARACTER SET WIN1254)
AS
begin
select SIRA_NO, ADI, SOYADI FROM MUSTERI
where musteri.sira_no=:SIRA
into :SIRA, :AD, :SOYAD;
suspend;
end
yukarıdaki örnekte 3 Output parametresi tanımladık 1'den fazla parametrede aralarına ( , ) konur. tanımlanan input ve Output parametreleri ve sorguda kullanılan alan adları aynı özelliklere (alan tipi,alan uzunluğu vb.) sahip olmalıdır.
Örnek 3
Şimdiye kadar yaptığımız SPler Tek kayıt yada tek değer dönderiyordu. SP'den birden çok kayıt alabilmek için sorgumuzu FOR..DO Bloğu içine yazıyoruz.
Birden çok kayıt döndüren SPlerde Delphi içinden IBQUERY veya IBDATASET gbi bileşenlerinden birini kullanın . IBSTOREDPROC bileşenini kullanırsanız hata alırsınız.
Kod: Tümünü seç
CREATE PROCEDURE SP_tumunu_Listele
RETURNS (AD VARCHAR(15) CHARACTER SET WIN1254, SOYAD VARCHAR(15) CHARACTER SET WIN1254)
AS
begin
FOR
select SIRA_NO, ADI, SOYADI FROM MUSTERI
into :SIRA, :AD, :SOYAD;
DO
begin
suspend;
end
end
For...Do kullanıldığında SP'de başka komut varsa başka bir Begin End bloğu arasına alınır.
Örnek 4
Aşağıdaki SP adı FAOsoft Olan tüm kayıtları listeler.
Kod: Tümünü seç
CREATE PROCEDURE SP_tumunu_Listele2 (ADIM VARCHAR(15) CHARACTER SET WIN1254)
RETURNS (AD VARCHAR(15) CHARACTER SET WIN1254, SOYAD VARCHAR(15) CHARACTER SET WIN1254)
AS
begin
FOR
select SIRA_NO, ADI, SOYADI FROM MUSTERI
where musteri.ADI,=:ADIM
into :SIRA, :AD, :SOYAD;
DO
suspend;
end
SP İle Kayıt Silme
Aşağıdaki SP de input parametresine verilen numaraya eşit olan kayıtlar silinir.
Kod: Tümünü seç
CREATE PROCEDURE SP_SIL (SIRA INTEGER)
AS
begin
DELETE FROM musteri WHERE musteri.sira_no=:SIRA;
end
SPde silme işlemi ve dolaylı olarak seçme işlemi gerçekleştiği için Grant bölümü aşağıdaki şekilde olmalıdır.
Kod: Tümünü seç
GRANT SELECT,DELETE ON MUSTERI TO PROCEDURE SP_SIL;
SP İle Kayıt Ekleme
Kod: Tümünü seç
CREATE PROCEDURE SP_EKLE (SIRA INTEGER, AD VARCHAR(15) CHARACTER SET WIN1254,
SOYAD VARCHAR(15) CHARACTER SET WIN1254)
AS
begin
INSERT INTO musteri (SIRA_NO,ADI,SOYADI)
VALUES (:SIRA, :AD, :SOYAD);
end
Yukarıda kullandığımız gibi bildiğimiz SQL koduyla parametrelerin başına ( : ) işaretini ekleyerek parametrelere gönderilen bilgileri tabloya ekliuyoruz.
Grant bölümü aşağıdaki şekilde olmalıdır.
Kod: Tümünü seç
GRANT INSERT ON MUSTERI TO PROCEDURE SP_EKLE;
SP İle Kayıt Güncelleme
Kod: Tümünü seç
CREATE PROCEDURE SP_GUNCELLE (
SIRA INTEGER,
AD VARCHAR(15) CHARACTER SET WIN1254,
SOYAD VARCHAR(15) CHARACTER SET WIN1254,
YENI_SIRA INTEGER)
AS
begin
update musteri
set SIRA_NO=:YENI_SIRA, ADI=:AD, SOYADI=:SOYAD
where SIRA_NO=:SIRA;
end
Yukarıdaki kodda değiştirilmek istenen kaydın primary key değerini Sıra parametresine verip diğer parametrelerede yeni değerleri aynı mantıkla SQL Kodlarıyla atıyoruz.
Grant bölümü aşağıdaki şekilde olmalıdır.
Kod: Tümünü seç
GRANT SELECT,UPDATE ON MUSTERI TO PROCEDURE SP_GUNCELLE;
SP'de Değişken Kullanma
SP'de değişken tanımlama AS Begin sözcükleri arasında yapılır. Değişkenler Declare Variable ile belirtilir. Her değişken tanımından önce Declare Variable tanımı kullanılmalıdır. her değişkenm tanımında nokktalı virgül bulunmalıdır.
Değişken, input,Output parametrelerinin tipleri ve özellikleri tablolarda kullandığımız alan tiplerini ve özelliklerini kullanırlar. IB / FB'de End sözcüğünden sonra noktalı virgül kullanılmaz.
/*bu iki işaret arasında yazılanlar açıklamadır. IB / FB bu yazıları dikkate almaz*/
Kod: Tümünü seç
CREATE PROCEDURE SP_VAR_ORNEK (SIRA INTEGER)
AS
DECLARE VARIABLE NUMARACONTROL INTEGER;
DECLARE VARIABLE ADCONTROL VARCHAR(15) CHARACTER SET WIN1254;
begin
select SIRA_NO, ADI FROM MUSTERI
where musteri.sira_no=:SIRA
into :NUMARACONTROL, :ADCONTROL; /* sorgu soonucu input ,output gibi değişkenlere atanıyor*/
if ((NUMARACONTROL=1) and (ADCONTROL='FAOsoft')) then /* seçilen kaydın nosu 1 adı FAOsoftsa*/
Begin
exception silmemezsin; /* daha önce tanımladığımız mesajı göster*/
Exit ; /* SP DEN ÇIKIŞ. kullandığımız exit komutu burada gereksizdir amaç Öğrenmek*/
end
else /* seçilen kaydın nosu 1 adı FAOsoft değşilse sil*/
DELETE FROM musteri WHERE musteri.sira_no=:SIRA;
suspend;
end
Grant bölümü aşağıdaki şekilde olmalıdır.
Kod: Tümünü seç
GRANT SELECT,DELETE ON MUSTERI TO PROCEDURE SP_VAR_ORNEK;
if then else Kullanımı
Delphide kullandığımızın benzeridir.
Elseden önceki komutada Noktalı virgül konur. then veya elsedeki komut birden fazlaysa delpbideki gibi Begin ve End bloğu arasına yazılır. if...then arasındaki şart parantez içine alınır. if..then arasında birden fazla şart varsa şartların hepsi bir parantez içine eklenir(kendi parantezleri ile birlikte). yukarıda örneklendirilmiştir.
Tablo Kullanmayan SP
SPler her zaman tablolardaki işlemleri yapmak için kuıllanmayabiliriz.
Aşağıdaki örnek SP verdiğiniz tarihin haftanın hangi güne denk geldiğini bulacaktır.
Kod: Tümünü seç
CREATE PROCEDURE SP_GUN_BUL (
TARIH DATE)
RETURNS (
GUN_ADI VARCHAR(10))
AS
DECLARE VARIABLE BENIM_TARIH DATE;
DECLARE VARIABLE TARIH_KOPYASI DATE;
DECLARE VARIABLE FARK1 INTEGER;
DECLARE VARIABLE FARK2 INTEGER;
DECLARE VARIABLE GUN_FARKI INTEGER;
DECLARE VARIABLE SONUC INTEGER;
begin
/*DEGİŞKENLERE ATAMA YAP*/
BENIM_TARIH= '15.12.96'; /*HERHANGİ BİR PAZAR GUNU*/
TARIH_KOPYASI= :TARIH;
FARK1 = 1;
FARK2 = 2;
/*HESAPLA*/
WHILE (:FARK1 <> :FARK2) DO
BEGIN
FARK1 = TARIH - BENIM_TARIH;
GUN_FARKI = :FARK1 / 7;
FARK2 = :GUN_FARKI * 7;
IF (:FARK1 <> :FARK2) THEN
TARIH = :TARIH + 1;
END
SONUC = 7-(:TARIH-:TARIH_KOPYASI);
if (SONUC=1) then
GUN_ADI='Pazartesi';
else
if (SONUC=2) then
GUN_ADI='Salı';
else
if (SONUC=3) then
GUN_ADI='Çarşamba';
else
if (SONUC=4) then
GUN_ADI='Perşembe';
else
if (SONUC=5) then
GUN_ADI='Cuma';
else
if (SONUC=6) then
GUN_ADI='Cumartesi';
else
if (SONUC=7) then
GUN_ADI='Pazar';
suspend;
end
Tools> SQL Editörü açıp oraya şu kodu yazarak etkisini görebilirsiniz.
Kod: Tümünü seç
select * from sp_gun_bul ('26.02.2005')
Yukarıda kullandığımız WHILE şart DO Delphide kullandığımızın benzeridir ve verilen şart gerçekleşinceye kadar işler. WHILE şart DO dan sonra yazılan komut sayısı birden fazlaysa Begin ve End bloğu arasında yazılır.
Değişkenlere = ( eşittir) simgesi ile değer atıyoruz. = ( eşittir) simgesi ile ayrıca karşılaştırma içinde kullanılır.
SP veya Trigger İçinden Giriş Parametreli Sp Çağırma
EXECUTE PROCEDURE spadı (giriş Patametre1,giriş Patametre2 ) RETURNING_VALUES çıkış Patametre1, çıkış Patametre2
giriş Patametreli bir sp aşağıdaki gibi çağrılır.
Kod: Tümünü seç
EXECUTE PROCEDURE SP_Listele (10,'FAOsoft')
çağıracağımız sp gösterdiimiz kaydı değiştirip yeni halini listelesin
Kod: Tümünü seç
CREATE PROCEDURE SP_GUNCELLE (
SIRA INTEGER,
AD VARCHAR(15),
SOYAD VARCHAR(15))
RETURNS (
SIRAM INTEGER,
ADIM VARCHAR(15),
SOYADIM VARCHAR(15))
AS
begin
update musteri
set SIRA_NO=:SIRA, ADI=:AD, SOYADI=:SOYAD
where SIRA_NO=:SIRA;
select SIRA_NO, ADI, SOYADI FROM MUSTERI
where musteri.sira_no=:SIRA
into :SIRAM, :ADIM, :SOYADIM;
suspend;
end
yukarıdaki sp yi çağıracak sp mizde aşağıdaki gibi olsun
Kod: Tümünü seç
CREATE PROCEDURE SP_CAGIR (
SIRA INTEGER,
AD VARCHAR(15) CHARACTER SET WIN1254,
SOYAD VARCHAR(15) CHARACTER SET WIN1254)
RETURNS (
SIRAM INTEGER,
ADIM VARCHAR(15) CHARACTER SET WIN1254,
SOYADIM VARCHAR(15) CHARACTER SET WIN1254)
AS
begin
execute procedure sp_guncelle :SIRA, :AD, :SOYAD returning_values :SIRAM, :ADIM, :SOYADIM;
suspend;
end
Tools> SQL Editörü açıp oraya şu kodu yazarak etkisini görebilirsiniz.
Kod: Tümünü seç
select * from SP_CAGIR (2,'fao','soft');
Grant bölümü aşağıdaki şekilde olmalıdır.
Kod: Tümünü seç
GRANT EXECUTE ON PROCEDURE SP_GUNCELLE TO PROCEDURE SP_CAGIR;
CASE...WHEN...THEN...ELSE
Bir değere göre daha fazla alternatifi yerine getirmeyi sağlar.
Kullanım biçimi:
CASE değer
WHEN değer THEN işlem
WHEN değer THEN işlem
ELSE işlem
END
AŞAĞIDAKİ ÖRNEK SELECT İÇİNDE ŞARTLI SORGULAMA YAPIYOR.
select ADI, SOYADI, case when YASI > 50 then 'Yaşlı' else 'Genç' end from DEFTER
haftanın gununu yazan bir ornek :
Kod: Tümünü seç
select case extract(weekday from :TARIH)
when 0 then 'PZR'
when 1 then 'PTS'
when 2 then 'SAL'
when 3 then 'ÇRŞ'
when 4 then 'PRŞ'
when 5 then 'CUM'
when 6 then 'CTS'
end from rdb$database
Örnek
WHEN...DO
When .. Do arasına yazdığınız hata kodu gerçekleşirse Do dan sonra istedğiniz Kodllar yazılarak hata verilmesi engellenir.
WHEN hata1, hata2,hata3 ANY
DO komutlar
ANY birden fazla hata kodundan herhangi birinin oluşması durumunda do'dan sonraki komutların çalışmassını sağlar. tek hata belirtilmişse kullanılmaz. herhangi bir hata belirtilmeyip sadece any i kullanırsanız herhangi bir hata oluşursa yazdığınız kod işler.
do'dan sonra komut sayısı birden fazlaysa Begin ve End bloğu arasında yazılır. .
When .. Do arasına kendi hata şartınızı, exceptionsu, interbasenin ürettiği hataları ve sql kodlarının ürettiği hataları yakalayabilirsiniz.
When .. Do arasına aşağıdaki gbib yazın
sizin şaartınız
SQLCODE SQLkodnumarası ............ sql kodlarının ürettiği hataları
GDSCODE hatakodu ............... interbasenin ürettiği hatalar
Önek
Kod: Tümünü seç
CREATE PROCEDURE NUMBERPROC (A INTEGER) RETURNS (B INTEGER) AS
BEGIN
B = 0;
BEGIN
UPDATE R SET F1 = F1 + :A;
UPDATE R SET F2 = F2 * F2;
UPDATE R SET F1 = F1 + :A;
WHEN SQLCODE -803 DO
B = 1;
END
EXIT;
END!!
Stored Procedure Düzeltme
Tek yapmanız Gereken CREATE PROCEDURE Yerine ALTER PROCEDURE Yazdıktan sonra SP ismini yazıp istediğiniz yerleri değiştirmek.
örneğin SP_GUN_BUL SP sinin sonuc değişkeninin tipini smallint yapalım
Kod: Tümünü seç
ALTER PROCEDURE SP_GUN_BUL (
TARIH DATE)
RETURNS (
GUN_ADI VARCHAR(10))
AS
DECLARE VARIABLE BENIM_TARIH DATE;
DECLARE VARIABLE TARIH_KOPYASI DATE;
DECLARE VARIABLE FARK1 INTEGER;
DECLARE VARIABLE FARK2 INTEGER;
DECLARE VARIABLE GUN_FARKI INTEGER;
DECLARE VARIABLE SONUC SMALLINT;
begin
/*DEGİŞKENLERE ATAMA YAP*/
BENIM_TARIH= '15.12.96'; /*HERHANGİ BİR PAZAR GUNU*/
TARIH_KOPYASI= :TARIH;
FARK1 = 1;
FARK2 = 2;
/*HESAPLA*/
WHILE (:FARK1 <> :FARK2) DO
BEGIN
FARK1 = TARIH - BENIM_TARIH;
GUN_FARKI = :FARK1 / 7;
FARK2 = :GUN_FARKI * 7;
IF (:FARK1 <> :FARK2) THEN
TARIH = :TARIH + 1;
END
SONUC = 7-(:TARIH-:TARIH_KOPYASI);
if (SONUC=1) then
GUN_ADI='Pazartesi';
else
if (SONUC=2) then
GUN_ADI='Salı';
else
if (SONUC=3) then
GUN_ADI='Çarşamba';
else
if (SONUC=4) then
GUN_ADI='Perşembe';
else
if (SONUC=5) then
GUN_ADI='Cuma';
else
if (SONUC=6) then
GUN_ADI='Cumartesi';
else
if (SONUC=7) then
GUN_ADI='Pazar';
suspend;
end
bu şekilde içindeki kodlarıda değiiştirebilirsiniz.
Stored Procedure Silme
DROP PROCEDURE ADI;
Örnek
Kod: Tümünü seç
DROP PROCEDURE SP_GUN_BUL;
SP kullanımdaysa veya başka bir SP yada Trigger tarafından kullanılıyorsa silinmeyeceektir
Not : Grant vb. ni IB Expertle kolayca halledebilirsiniz. amaç mantığını bilmeniz. SP ve, Viewler tablolar gibi seçilebildiğinden aynı isme sahip olamaazlar. Bir sp ile tablo silme vb.. yapamazsınız. sp de kullandığımız kodları triggerdada kullanabiliriz.