Oracle, Dynamic SQL ve Dynamic PL/SQL Kullanımı

Herkese Selam,

Bu yazida sizlerle Oracle’da dinamik SQL’lerin nasil olusturuldugu ve hangi yöntemler kullanilarak çalistirildigindan bahsedicem.

Öncelikle Dinamik SQL kavramindan birazcik bahsedelim.

Dinamik SQL: Dinamik SQL’ler, tasarim asamasinda degil, run time(çalisma zamaninda) ‘da olusturulan SQL cümleleridir. Terminal üzerinden yazilan SQL cümleciklerinden farksizdirlar. Sorgu islenir ve dönen bir sonuc varsa tekrar programa geri döndürülür.

Suan hali hazirda 2 farkli yöntem ile dinamik SQL implementasyonunu gerçeklestirebilmekteyiz. Bunlardan birincisi DBMS_SQL paketini kullanmak (DBMS_SQL Paketi Oracle 8.1 Versyonundan beri varligini sürdürmekte), diger yöntem ise EXECUTE_IMMEDIATE söz deyimini kullanmaktir (EXECUTE_IMMEDIATE Oracle 8i versyonundan beri varligini sürdürmektedir.).

EXECUTE_IMMEDIATE kullanmak programcilar açisindan daha pratik v edaha üretkenligi hizlandiran bir yönetem olarak karsimiza çikmaktadir. Ancak DBMS_SQL’in array processing, cursor isleme ve control özellikleri performans konusunda belli avantajlar ortaya çikarmaktadir.

Simdi bu farkli 2 yöntemi ve kullanuimlarini ayri ayri inceleyelim.

DBMS_SQL

DBMS_SQL paketi hemen hemen her sirali dinamik SQL veya dinamik PL/SQL bloklarini çalistirabilmektedir. DBMS_SQL paketi , yapisi geregi, dinamik sorgulari veya bloklari islerken bir çok fonksyon çagirimina ihtiyaç duymaktadir. DBMS_SQL paketinin nasil çalistigini asagidaki akis diyagramini inceleyerek daha net anlayabilirsiniz.

Simdi DBMS_SQL’in nasil kullanildigini görelim.

Öncelikle örneklerimizi gerçekleştirebilmek için temp bir tablo oluşturup içine data yazalım.

CREATE TABLE DENEME
(
COLA  VARCHAR2(100 BYTE),
COLB  INTEGER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
/ — insert cümleleri
Insert into DENEME
(COLA, COLB)
Values
(‘2’, 2);
Insert into DENEME
(COLA, COLB)
Values
(‘3’, 3);
Insert into DENEME
(COLA, COLB)
Values
(‘4’, 4);
COMMIT;

Örnek tablomuzu oluşturduktan sonra DBMS_SQL paketimizi kullanmaya başlayabiliriz. Şimdi yazacağımız örnekte basit olarak dbms_sql paketinin nasıl kullanıldığını göreceğiz.

DECLARE
cursor_name      NUMBER;   — 1.
rows_processed   NUMBER;
BEGIN
cursor_name := DBMS_SQL.open_cursor; — 2. 
DBMS_SQL.parse (cursor_name,
‘DELETE FROM deneme where cola=1’,
DBMS_SQL.native
);      — 3. Adım

— DBMS_SQL.BIND_VARIABLE(cursor_name, ? , ?); — 4. 


rows_processed := DBMS_SQL.EXECUTE (cursor_name); — 5. Adım
DBMS_SQL.close_cursor (cursor_name); — 6.
DBMS_OUTPUT.put_line (rows_processed);
EXCEPTION
WHEN OTHERS
THEN
DBMS_SQL.close_cursor (cursor_name);
END;

Yukarıda yazmış olduğum Adımları sıra ile açıklamak gerekirse.

1. Adım: Dinamik sorgumuzu çalıştırma adına yapacağımız ilk adım yukarıdaki flowdan da analaşılacağı üzere cursor açabilmemiz adına bir cursor tanımalaması olacaktır. 1. Adımda bu işlemi gerçekliyoruz. Bu tanımladığımız Cursor’ın tipi integer veya number olmalıdır.

2. Adım: Bu adımda Akış diyagramında ilk işlem olarak tanımladığımız Cursor’ı açma işlemi gerçekleştiriyoruz.

3.Adım: Açtığımız Cursor’ı parametre olarak dbms_sql.parse metoduna parse edilecek sql ile beraber veriyoruz. Bu adımda verdiğimiz sql’in run time’da parse edilme işlemi gerçekleşecektir.

4. Adım: Bu adım çalıştıracağımız dinamik sorgulara göre opsiyonel olmaktadır. Yukarıdaki örnekteki gibi bir durumda 4.Adım opsyonel olacaktır. Ancak dinmaik SQL’ler günlük hayatımızda hep dışardan gelen parametreler ile şekillendiğinden bu adım dinamik SQL’lerimizde sıkça kullanılmakta.

5. Adım: Bu adımda üstteki adımlarda hazırlanan dinamik SQL’in execution’ı yapılacaktır. Bu işlemin number veya integer bir değişkene eşitlenmesi gerektiği unutulmamalıdır. Aksi takdirde run time’da hatalar alınacaktır. Execute işleminden geriye dönüp değişkenimize atanan değer o sorgu sonucunda etkilenen row sayısı olacaktır.

6. Adım: Flow’da yapmamız gerken son adım olarak gösterdiğimiz işlemi bu adımda gerçekleştiriyoruz.  Açtığımız cursor’ı kapatıyoruz.

Yukarıda yazdığımız kod parçasını çalıştırdığımızda 1 adet kayıdın silindiğini ve ekrana etkilenen satır sayısı değerinin 1 olarak basıldığını göreceğiz.

Şimdi DBMS_SQL ile bind variable ve sorgu sonucunda dinamik SQL’den nasıl değer alıcağımızı gösteren  bir örnek yazalım.

Örnek: Bu örnekte parametre olarak tablo adı ve kolon adı alıp, ilgili kolonun maximum değerini bulup deneme2 tablosuna yazan bir  procedure ve bunu test edecek bir pl/sql bloğu yazalım.

CREATE OR REPLACE FUNCTION dynamicsql (
p_table_name    VARCHAR2,
p_column_name   VARCHAR2
)
RETURN NUMBER
IS
cursor_name        NUMBER;
rows_processed     NUMBER;
column_max_value   NUMBER          := 0;
v_sql              VARCHAR2 (1000);
BEGIN
v_sql := ‘select max(‘ || p_column_name || ‘) from ‘ || p_table_name;
cursor_name := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor_name, v_sql, DBMS_SQL.v7);
DBMS_SQL.define_column (cursor_name, 1, column_max_value);
rows_processed := DBMS_SQL.EXECUTE (cursor_name);
rows_processed := DBMS_SQL.fetch_rows (cursor_name);
DBMS_SQL.COLUMN_VALUE (cursor_name, 1, column_max_value);
DBMS_OUTPUT.put_line (column_max_value);
v_sql := ‘insert into deneme2 values(”’ || p_column_name || ”’,:x)’;
DBMS_SQL.parse (cursor_name, v_sql, DBMS_SQL.v7);
DBMS_SQL.bind_variable (cursor_name, ‘:x’, column_max_value);
rows_processed := DBMS_SQL.EXECUTE (cursor_name);
DBMS_SQL.close_cursor (cursor_name);
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END;

Deneme2 tablosu ve bu prosedürü test edicek pl/sql bloğu ise aşağıdadır.

CREATE TABLE DENEME2
(
COLNAME    VARCHAR2(1000 BYTE),
MAX_VALUE  NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

/

DECLARE
x   NUMBER;
BEGIN
x := dynamicsql (‘DENEME’, ‘COLA’);
DBMS_OUTPUT.put_line (x);
END;

Yukarıdaki örnekte, dinamik sql oluşturup bu olusturduğumuz sql den dönen sonucu bir değişkene alıp bu değişkeni başka bir tabloya insert etme işlemini gerçekleştirdik. Bu işlemleri yaparken bind variable kullanımınıda DBMS_SQL paketinde nasıl kullanıldığınıda gözlemlemiş olduk. Şimdi ise DBMS_SQL paketinin en önemli işlevlerinden biri olan array_processing örneğini implemente edicez. Array processing özelliği ile execution time’dan ortalama %75 azalma elde edilebilir. Buda dinamik sorgularımızın perofrmansını arttıracak çok önemli özellik olarak karşımıza çıkmakta.

DBMS_SQL ile Array Processing Örneği: 2 adet dizi oluşturup deneme3  tablomuzun içine dinamik sql ile insert işlemini gerçekleştirelim.

create table deneme3 (cola number,colb number);

/

set timing on
DECLARE
v_array1    DBMS_SQL.number_table;
v_array2    DBMS_SQL.number_table;
mycursor    INTEGER;
processed   INTEGER;
BEGIN
— DIZILERI DOLDURUYORUZ
FOR i IN 1 .. 1000000
LOOP
v_array1 (i) := i + 5;
v_array2 (i) := i + 6;
END LOOP;

— DINAMIK SQL LI OLUSTURUYORUZ
mycursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse (mycursor,
‘INSERT INTO DENEME3(COLA,COLB) VALUES(:V1,:V2)’,
DBMS_SQL.native
);
DBMS_SQL.bind_array (mycursor, ‘:V2’, v_array2);
DBMS_SQL.bind_array (mycursor, ‘:V1’, v_array1);
processed := DBMS_SQL.EXECUTE (mycursor);
DBMS_SQL.close_cursor (mycursor);
END;

PL/SQL procedure successfully completed.
Elapsed: 00:00:03.90

Evet Oracle’ın Array Processing özelliğini kullanarak DBMS_SQL paketinin aracılığı ile tek bir satır ile 1 milyon kayıdı 3.9 sn gibi çok hızlı bir şekilde tabloya yazabildik.

Yaptığımız örneklerden de anlıyacağımız gibi DBMS_SQL paketi ile dinamik SQL çalıştırmak biraz karmaşık. Bunu yazımızın ilk başlarındada dile getirmiştik. Eğer DBMS_SQL paketi kullanma zahmetine giriyor isek Array Processing veya Bind Variables kullanıp kullanmıyacağımızı iyi analiz etmeliyiz. Aksi takdirde kodumuzu gereksiz karmaşıklaştırabiliriz.

Şimdi Dinamik SQL işlemek için diğer yöntemizi olan EXECUTE IMMEDIATE döz deyiminin nasıl kullanıldığını inceleyelim.

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE yapısı Oracle 8i’den beri varlığını sürdürmekte. DML ve DDL işlemlerimizi dinamik şekilde çalışma zamanında oluşturup çalıştırmaya müsade eden bir yapı. Yalnız çalıştıracağımız dinamik sorguların yalnızca 1 satır döndürmesi gerekmektedir. Aksi takdirde bu yapıyı kullanamamaktayız. Daha öncede bahs ettiğimiz gibi DB developer’ların en sık kullandığı dinamik SQL oluşturma ve çalıştırma yöntemi EXECUTE IMMEDIATE’dir. Implementasyonı DBMS_SQL’e göre daha az karmaşık ve geliştirmesi daha kolaydır. Şimdi  EXECUTE IMMEDIATE deyimini kullanarak dinamik SQL çalıştıralım.

DECLARE
v_column_name1   VARCHAR2 (1000) := ‘colA’;
v_column_name2   VARCHAR2 (1000) := ‘colB’;
v_table_name     VARCHAR2 (1000) := ‘DENEME3’;
v_sql            VARCHAR2 (1000);
value1           NUMBER;
value2           NUMBER;
into_1           NUMBER;
into_2           NUMBER;
BEGIN
— Ornek 1: dogrudan calistirma
v_sql := ‘truncate table ‘ || v_table_name;

EXECUTE IMMEDIATE v_sql;

— Ornek 2 : bind variable kullanimi
value1 := 100;
value2 := 150;
v_sql := ‘insert into ‘ || v_table_name || ‘ values(:1,:2)’;

EXECUTE IMMEDIATE v_sql
USING value1, value2;

COMMIT;

— Ornek 3 : select sonucu degiskenlere alinir
v_sql :=
‘select ‘
|| v_column_name1
|| ‘,’
|| v_column_name2
|| ‘ from ‘
|| v_table_name
|| ‘ where colb=150’;

EXECUTE IMMEDIATE v_sql
INTO into_1, into_2;

DBMS_OUTPUT.put_line (‘deger1: ‘ || into_1 || ‘  deger2:’ || into_2);
—  Ornek 4: hem bind variable alma, hemde disariya deger cikarma.
v_sql :=
‘select ‘
|| v_column_name1
|| ‘,’
|| v_column_name2
|| ‘ from ‘
|| v_table_name
|| ‘ where colb=:xyz’;

EXECUTE IMMEDIATE v_sql
INTO into_1, into_2
USING value2;

DBMS_OUTPUT.put_line (‘deger1: ‘ || into_1 || ‘  deger2:’ || into_2);
END;

Yukarıdaki örnekte EXECUTE IMMEDIATE’ın 4 farklı kullanımı gösterilmiştir. Kodlardan da anlıyacağınız üzere EXECUTE IMMEDIATE ile dinamik SQL oluşturmak ve çalıştırmak oldukça hızlı ve DBMS_SQL yöntemine göre daha az karmaşık. Önceden de bahs ettiğimiz gibi eğer array processing yapmıyorsak EXECUTE IMMEDIATE ile dinamik SQL yapısından faydalanmak daha hızlı ve etkili olacaktır. Ancak array processing yapısının hızını göz ardı etmeyip gerektiği yerde bu yöntemi tercih  etmeyi unutmamalıyız.

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root, Uncategorized and tagged , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s