Örnek: Veritabanında “procedure” ve “PlSql” kullanımları örneklerle anlatıyor.

Veritabanında birden fazla işi tek bir yapıda gerçekleştirebilmek için Procedure (yordam) yapılarını kullanırız. Procedure’ler birçok değer döndürebilirler, birçok çıktı üretebilirler ancak function’lar sadece tek değer döndürürler ve daha çok matematiksel işlemler için function yapılarını kullanırız. Kullanım amacına göre aralarında en büyük farkın bu olduğunu söyleyebiliriz. Bu blog yazısında Plsql ve Procedure lerden örnekler vereceğiz. Developer’lar ‘Plsql bloğu’, ‘plsql batch’i’, ‘batch’, ‘plsql iş parçası’ vs. ifadeler kullanırlar.

Hemen her programlama dilinde ilk örnek “merhaba” vb. yazısını ekranda göstermektir, biz de bununla başlayalım 🙂

declare 
begin
dbms_output.put_line('Merhaba');
end;

Değişkenin değerini output a yazar;

declare 
adi varchar2(10):='ORHAN';
begin
dbms_output.put_line(adi);
end;

Aşağıdaki plsql bloğu hasta adını output a yazar;

DECLARE
 text VARCHAR2 (200);
 v_text LONG;
BEGIN
 v_text := 'select hasta_adi as text from hastalar where hasta_no=10';
EXECUTE IMMEDIATE v_text INTO text;
DBMS_OUTPUT.put_line (text);
END;

Aşağıdaki plsql bloğu hastanın hasta nosunu output a yazar;

DECLARE
 text VARCHAR2 (200);
 hno number;
 v_text LONG;
BEGIN
 v_text := 'select hasta_adi as text, hasta_no as hno from hastalar where hasta_no=10';
EXECUTE IMMEDIATE v_text into text, hno;
DBMS_OUTPUT.put_line (text||' ''in hasta no''su '||hno);
END;

Aşağıdaki plsql bloğu kullanıcının gireceği hasta no’lu kaydı siler ve output a yazar, kayıt bulunamazsa bulunamadı mesajını yazar;

DECLARE
 soru NUMBER;
BEGIN
 DELETE FROM hastalar WHERE hasta_no = &soru;
 DBMS_OUTPUT.put_line ('hasta kaydı silindi');
IF SQL%NOTFOUND
 THEN
 DBMS_OUTPUT.put_line ('hastalar tablosunda bu id bulunamadı!.');
 END IF;
END;

Aşağıdaki plsql bloğunda bind variable ile girilen tarih aralığına ait kayıtları siler.
Tarih kolonları için & kullanacaksak to_date(‘30.03.2017′,’dd.mm.yyyy’) gibi tarih formatını kullanmalıyız. Tarih kolonları için :t1 şeklinde bind variable’lı kullanacaksak çıkacak ekranda direk tarih bilgisi girebiliriz

BEGIN
 DELETE FROM hastalar WHERE tarih between :t1 and :t2;
 DBMS_OUTPUT.put_line ('hasta kaydi silindi');
IF SQL%NOTFOUND
 THEN
 DBMS_OUTPUT.put_line ('hastalar tablosunda bu id bulunamadi!.');
 END IF;
END;

Tek satır kayıt döndüreceksek aşağıdaki örnekteki gibi kullanabiliriz;

DECLARE
 text VARCHAR2 (200);
 v_text LONG;
BEGIN
 v_text := 'select hasta_adi as text from hastalar where hasta_adi=''ORHAN''';
EXECUTE IMMEDIATE v_text INTO text;
DBMS_OUTPUT.put_line (text);
END;

Birden fazla kayıt döndüreceksek aşağıdaki örnekteki gibi kullanabiliriz;

DECLARE
 text VARCHAR2 (200);
 v_text LONG;
BEGIN
 for i in (select hasta_adi from hastalar)
 loop
 v_text:='select * from hastalar where hasta_adi='''||i.hasta_adi||'''';
 EXECUTE IMMEDIATE v_text;
DBMS_OUTPUT.put_line (i.hasta_adi);
 end loop;
END;

veya da;

DECLARE
 text VARCHAR2 (200);
 v_text LONG;
BEGIN
 for i in (select hasta_adi from hastalar)
 loop
 v_text:='update hastalar set hasta_adi=''ORHAN'' where hasta_adi='''||i.hasta_adi||'''';
 EXECUTE IMMEDIATE v_text;
 DBMS_OUTPUT.put_line (i.hasta_adi);
 end loop;
END;

yada bir diğer örnek;

DECLARE
 v_grant VARCHAR2 (500);
BEGIN
 FOR i IN (SELECT DISTINCT table_name FROM user_tables where table_name like 'M%')
 LOOP
 v_grant := ' grant select on ' || i.table_name || ' to ORHAN';
 execute immediate v_grant;
 DBMS_OUTPUT.put_line (v_grant);
 END LOOP;
END;

grant select on MODELSET$ to ORHAN;
grant select on METAPATHMAP$ to ORHAN;

Yazdığımız Plsql bloğunun başındaki declare kısmını kaldırıp “create or replace procedure proc_name is …” ifedesiyle procedure yapısına çevirebilriz kolayca.
Aşağıdaki procedure girilen parametrelere göre ilgili kaydı update eder;

-- declare
CREATE OR REPLACE PROCEDURE pro_update (phasta_no IN NUMBER,
 phasta_adi IN VARCHAR2)
AS
BEGIN
 UPDATE hastalar
 SET hasta_adi = phasta_adi
 WHERE hasta_no = phasta_no;
COMMIT;
END;

execute pro_update (20, 'ORHAN');

Aşağıdaki procedure örneğinde developer tarafından tanımlı hata durumu var (RAISE_APPLICATION_ERROR);

CREATE OR REPLACE PROCEDURE pro_insert (phasta_no IN NUMBER,
 phasta_adi IN VARCHAR2,
 ptarih IN date)
AS
BEGIN
 if phasta_no=50 then
 RAISE_APPLICATION_ERROR (-20000, 'Bu hasta kaydini ekleyemezsiniz!.');
 else 
 insert into hastalar values (phasta_no,phasta_adi,ptarih);
COMMIT; 
 end if;
end; 
 
execute pro_insert (50,'ORHAN', sysdate);

Yukarıdaki örnekten yola çıkarak RAISE_APPLICATION_ERROR dan kısaca söz edelim;
Kullanıcı tanımlı hatalar’da ise ‘raise _applicaion_error(errorID, errorDesc)‘ kullanılır. ’errorID‘ ve ‘errorDesc‘ alanlarını kullanıcı belirler.’errorID‘ [-20000,-20999] arasında olmalıdır. ’errorDesc‘ alanı ise en fazla 2000 karakterlik ‘varchar2′ veri tipinde açıklama yazılabilir.

declare
v_value number :=110;
begin
if(v_value > 100) then
raise_application_error(-20010,'Değer büyük!');
end if;
end;

Bir diğer yol, hatayı tanımlayıp raise etmek;

declare
v_value number :=110;
deger exception;
begin
if(v_value > 100) then
raise deger;
end if;
exception
when deger then
raise_application_error(-20010,'Değer büyük!');
end;

Aşağıdaki procedure cursor de tanımlı sorgudan gelen ‘drop table …’ ddl işlemlerini çalıştırarak bu tabloları drop eder, hata alınırsa ORA- hatasını verir;

--declare
create or replace procedure proc1 is
 CURSOR c1 IS
 SELECT 'drop table ' || table_name || ' cascade constraints' text
 FROM dba_tables
 WHERE owner = 'SYS' AND table_name LIKE 'A%'; 
BEGIN
 FOR i IN c1
 LOOP
 BEGIN
 DBMS_OUTPUT.put_line (i.text);

--EXECUTE IMMEDIATE (i.text);
 EXCEPTION
 WHEN OTHERS
 THEN
 DBMS_OUTPUT.PUT_LINE ('ERROR=' || i.text);
 DBMS_OUTPUT.PUT_LINE (SQLERRM); 
 END;
 END LOOP;
END;

Aşağıdaki 3 şekilde procedure ü çalıştırabiliriz;

1)

GRANT EXECUTE ON proc1 TO SYSTEM;

2)

BEGIN 
 SYS.PROC1; 
END;

3)

execute proc1;

Yukarıdaki procedure den yola çıkarak CURSOR mantığından söz edelim;
cursor, memory’i kullanan bir mekanizma olduğundan en hızlı data çekme işlemi yapılır.

open -> ile cursor açılır,
fetch -> ile tek tek kayıtlar okunur,
close -> ile de kapanır.

cursor cursor_adi is  select cümlemiz …  şeklinde tanımlanır

open cursor_adi;
fetch cursor_adi into degisken_adi;
close cursor_adi;

şeklinde kullanılır.

* cursor ile veri bir defa çekilir ve istenildiği kadar kullanılır.

* fetch loop içinde kullanıldığında birden fazla kayıdı getirmiş oluruz.

exit when cursor_adi%NOTFOUND;  -> kayıt yoksa döngüden çık demektir.