Oracle SQL’de Prosedür Oluşturma

Merhabalar, bir önceki yazımda prosedür yazmanın avantajlarından ve faydalarından bahsetmiştim. Bu yazıda ise prosedür nasıl oluşturulur, bu süreç nasıl ilerler ve sonucunda elde ettiğimiz faydadan bahsedeceğim.

Kurumsal ortamlarda çalışırken devasa kaynak tablolarla uğraşmak veri işleme ve raporlama süreçlerinde ciddi bir yavaşlığa neden olabiliyor. Bu tablolar yüzlerce kolondan oluşabiliyor, erişim yetkiniz sınırlı olabiliyor ya da doğrudan sorguyu hızlandırmak için belirli kolonlara index atama gibi işlemleri yapmamız mümkün olmayabiliyor. Bu gibi durumlarda, ihtiyaca özel sadeleştirilmiş ara tablolar oluşturarak hem performansı artırmak hem de süreci daha yönetilebilir hale getirmek mümkündür.

Bunun için ilk adım olan prosedürü oluşturmak için veri tabanına bağlanıp sonra prosedür sekmesine ulaşıp üzerine tıklanarak prosedür oluşturulur. İsim verme gibi adımlardan sonra prosedürümüz hazır olacaktır. Bu kısmın devamında önemli olan ise bu prosedürün çalışma şeklidir. DROP-CREATE şeklinde mi yoksa TRUNCATE-INSERT şeklinde mi belirlememiz gerekmektedir.

Eğer tablo, prosedür içerisinde TRUNCATE komutu ile temizlenip tekrar kullanılacak şekilde planlanmışsa, bu tablonun öncesinde veri tabanında bir defaya mahsus olarak manuel şekilde create edilmesi gerekir.

Veri tabanına ait bir sayfada veya veri tabanı üzerinden tablolar sekmesinden tabloyu oluşturabiliriz. Bizim örnek olarak oluşturacağımız veri tablosu, son üç ayın hepsinde de aşım yapan müşterileri bulunduran tablo olacak.

Önce tabloyu create ediyoruz. İstediğimiz kolonları ve veri tiplerini belirtiyoruz.

CREATE TABLE ETL.ASIM_LAST_3_MONTHS (

GSM_NO VARCHAR2(20),

TL_ASIM NUMBER

);

Performans ihtiyacına göre tablodaki kolona/kolonlara index create ediyoruz.

CREATE INDEX IDX_ASIM_GSM_NO ON ETL.ASIM_LAST_3_MONTHS (GSM_NO);

Eğer bizim veri tabanımızda oluşturacağımız tabloya erişim ihtiyacı olan kullanıcılar var ise gerekli kullanıcı yetkilerini tanımlamamız gerekir. Örnek SELECT yetkisi tanımlama aşağıdaki gibidir:

GRANT SELECT ON ETL.ASIM_LAST_3_MONTHS TO USER;

Tablo oluşturma, index oluşturma ve user yetki verme sonrası prosedürümüze geri dönebiliriz. Bu kısımda da ilgili kaynak tablodan hangi verileri çekeceğimizi, nasıl çekeceğimizi belirlememiz gerekmektedir.

Aşağıdaki prosedür, kaynak tablo CUSTOMER_PROFILE’dan yalnızca ihtiyacımız olan verileri süzüp özetleyerek ETL.ASIM_LAST_3_MONTHS tablosuna aktarıyor. Böylelikle kaynak tabloda yaşayabileceğimiz yanlış yapılabilecek işlemleri; delete, update vb. engelliyor, sorgu süreleri kısaltabiliyor, istediğimiz verinin miktarını belirleyebiliyor ve çektiğimiz veri üzerinde oynama imkânı tanıyor. Örnek prosedür aşağıdaki gibidir: Prosedür oluştuğunda yapacağımız işlemler begin ve end arasında olmalıdır. Begin üst kısmında değişken tanımlayabiliriz. Yaptığımız işlemlerde EXECUTE IMMEDIATE diyip doğrudan script çalıştırıp commit edebilir veya tanımladığımız değişken için EXECUTE IMMEDIATE d_task; şeklinde de çalıştırabiliriz.

Buna ek olarak eğer ki çalışma sürelerini takip etmek istersek yine ek tablo oluşturup tablo içerisinde start end gibi kolonlar ekleyerek prosedürün çalışma süresi takip edilebilir veya farklı uygulamalar üzerinden de prosedürlerin periyodik çalışması sağlanarak o uygulamalardan da takip edilebilir.

CREATE OR REPLACE PROCEDURE P_ASIM_LAST_3_MONTHS AS

d_task VARCHAR2 (32000);

v_table_name VARCHAR2(50);

BEGIN

— Veri tabanımızdaki tabloyu güncel tutmak için kaynak tabloya gitmeden önce temizleme:

EXECUTE IMMEDIATE ‘TRUNCATE TABLE ABC_ETL.ASIM_LAST_3_MONTHS’;

v_table_name := ‘ASIM_LAST_3_MONTHS’;

— İstenen veriyi kaynak tablodan çekme:

d_task := ‘INSERT INTO ABC_ETL.’ || v_table_name ||

‘(GSM_NO, TL_ASIM)

Get Ercan Ergen’s stories in your inbox

Join Medium for free to get updates from this writer.Subscribe

SELECT GSM_NO, FLOOR(MAX(TOTAL_NET_AMOUNT)) AS TL_ASIM

FROM CUSTOMER_PROFILE

WHERE TO_DATE(MONTH_ID,’’YYYYMM’’) >= (

SELECT ADD_MONTHS(TO_DATE(MAX(MONTH_ID),’’YYYYMM’’), -2)

FROM CUSTOMER_PROFILE)

AND FLOOR(TOTAL_NET_AMOUNT) > 0

AND GSM_NO IN (

SELECT GSM_NO FROM (

SELECT GSM_NO

FROM CUSTOMER_PROFILE

WHERE TO_DATE(MONTH_ID,’’YYYYMM’’) >= (

SELECT ADD_MONTHS(TO_DATE(MAX(MONTH_ID),’’YYYYMM’’), -2)

FROM CUSTOMER_PROFILE)

AND FLOOR(TOTAL_NET_AMOUNT) > 0)

GROUP BY GSM_NO

HAVING COUNT(*) = 3)

GROUP BY GSM_NO ‘;

EXECUTE IMMEDIATE d_task;

commit;

END;

Prosedür Ne Yapıyor?

Her çalışma öncesi güncel veriyi tabloya insert etmek için CEP_ETL.ASIM_LAST_3_MONTHS tablosu prosedür başında truncate edilerek eski veriler siliniyor ve tablonun içi boşaltılıyor.

CUSTOMER_PROFILE tablosundan sadece son 3 aya ait veriler çekiliyor. Burada yalnızca her ay için verisi olan (yani 3 kayıt bulunan) GSM numaraları dahil ediliyor.

d_task, v_table_name gibi değişkenler prosedür içerisinde kullanılıyor. Bu sayede kısa ve kolay kullanım elde edilip, defalarca kullanılma imkânı sunuyor.

Prosedürü Neden Kullanıyoruz?

CUSTOMER_PROFILE tablosu büyük ve karmaşık: Çok fazla kolon içerdiği için sadece ihtiyacımız olan birkaç kolonla çalışmak daha mantıklı.

Kaynak tabloda yetkimiz sınırlı: Index oluşturmak, tabloyu yeniden düzenlemek gibi işlemler yapamıyoruz. Ancak kendi oluşturduğumuz hedef tabloda bu imkanlara sahibiz.

Performans artışı: Küçük, optimize edilmiş tablolar sorgu süresini ciddi oranda kısaltır. Örneğin bu prosedürle oluşturulan tabloya ileride index eklenebilir ve raporlama işlemleri çok daha hızlı hale gelebilir.

Bakım kolaylığı: Prosedürle çalışan yapılar merkezi olarak kontrol edilebilir. Eğer kaynak tabloda bir değişiklik olursa sadece prosedürü güncelleyerek tüm süreci tekrar düzene sokmak mümkündür.

Sonuç olarak bu örnek, Oracle PL/SQL’de prosedür yazmanın yalnızca otomasyon sağlamakla kalmayıp, erişim ve performans gibi kısıtlamalara da çözüm sunabileceğini gösteriyor. Veriyi sadeleştirerek iş zekâsı ve raporlama süreçlerini hızlandırmak isteyen herkesin bu tür yaklaşımları düşünmesi doğrudan istenen veriye hızlı ulaşmasını sağlarken, kaynak sistemlerde sorun yaşamanın da önüne geçmiş olacak.