Arkadaşlar bu makalemde sizlere dinamik SQL konusundan söz edeceğim.

Dinamik SQL Nedir ?

Dinamik SQL, senaryosu çalışma zamanından once bilinmeyen SQL cümlecikleri ile program yazma imkanı sağlayan bir tekniktir. Dinamik SQL’in detaylarına girmeden önce sizlere statik SQL den bahsetmek istiyorum. Böylelikle ikisi arasındaki farkı daha net anlayacağınızı düşünüyorum. Statik SQL komutları çalışmadan çalışmaya değişmez. Komutun tüm senaryosu derlendiği anda bilinir ve herhangi bir alter işlemi söz konusu olmadıkça öylece sabitlenir ve şu avantajları sağlar :

1) Hatasız derlenmiş bir SQL komutunda tüm objelerin veritabanında geçerli olduğu bilinir,

2) Hatasız derlenmiş bir SQL komutunda tüm yetkilendirmelerin yerli yerinde olduğu bilinir.

3) Statik SQL komutlarının sağladığı performans dinamik SQL’e göre daha üstündür.

Bu avantajlara baktığımızda dinamik SQL’I ancak, static SQL komutlarıyla başaramadığımız işlerde veya static SQL komutlarının kullanışsız kaldığı durumlarda kullanmayı tercih etmeliyiz.Her ne kadar statik SQL in kısıtlamaları olsa da performans bakımından dinamik SQL e nazaran üstündür. İçinizdeki soruyu duyar gibiyim, “e pekala neden dinamik SQL ?”…

Neden Dinamik SQL ?

Programlarınızda bazı anlarda yazacağınız SQL komutlarının full içeriğini bilmeyebilirsiniz. Yani SQL sorgunuz, kullanıcıdan gelecek değişik input değerlerine gore şekillenme ihtiyacı duyabilir. Lafı karıştırmadan hemen bir örnek verelim;

Bir datawarehouse ortamınız var ve siz bu ortamdaki bir raporlama modülünüzdeki tablo isimlerini derlendiği tarihe gore atamak istiyorsunuz. Mesela INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997 gibi. Böyle durumlarda raporlama modülünüzde dinamik SQL kullanarak tablolarınızı çalışma zamanında ‘create’ edebilirsiniz.

Veya programınızda bir listeleme modülü var ve sıralamayı kullanıcının belirlemesini istiyorsunuz. Böyle bir durumda ‘order by’ fonksiyonu değişken olacaktır. Yani kullanıcı hangi kritere gore sıralama yapmak istiyorsa ‘order by’ o kritere gore şekillenecektir. Böyle bir durumda da dinamik SQL kullanabilirsiniz.

PL/SQL Blokları İçinde DDL ve SCL Kullanımı

PL/SQL içinde şu tipleri static SQL’ e tercihen kullanabiliriz :

- DDL(Data Definition Language) cümleleri. ‘Create’, ‘Drop’, ‘Grant’, ‘Revoke’ gibi…

- SCL(Session Control Language) cümleleri. ‘Alter Session’, ‘Set Role’ gibi..

Bir örnek verecek olursak :

CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))

/

CREATE TYPE t_emplist AS TABLE OF t_emp

/

CREATE TABLE dept_new (id NUMBER, emps t_emplist)

NESTED TABLE emps STORE AS emp_table;




INSERT INTO dept_new VALUES (

10,

t_emplist(

t_emp(1, 'SCOTT'),

t_emp(2, 'BRUCE')));

DECLARE

deptid NUMBER;

ename  VARCHAR2(20);

BEGIN

EXECUTE IMMEDIATE 'SELECT d.id, e.name

FROM dept_new d, TABLE(d.emps) e  -- not allowed in static SQL

-- in PL/SQL

WHERE e.id = 1'

INTO deptid, ename;

END;



Dinamik SQL Kullanım Alanları

- Kullanıcıya çalışma zamanında sorgu seçimi veya kritere gore sıralama v.s işlem seçeneği sunan uygulamalar,

- Çalışma anında veri girişi veya optimizasyon seçeneği sunan uygulamalar,

- Veritabanındaki tablo tanımlamalarının (DDL statements) sürekli değişken olduğu uygulamalar,

- İsteğe ve ihtiyaca gore çalışma anında create edilen tablo vb. objeler içeren uygulamalar.

Konunun başında vermiş olduğumuz datawarehouse örneğine bakarsak, tablo tanımını normalde biliyoruz fakat ismini çalışma anında vermek istiyoruz. Dinamik SQL bu problemi çözüyor.

CREATE OR REPLACE PROCEDURE query_invoice(

month VARCHAR2,

year VARCHAR2) IS

TYPE cur_typ IS REF CURSOR;

c cur_typ;

query_str VARCHAR2(200);

inv_num NUMBER;

inv_cust VARCHAR2(20);

inv_amt NUMBER;

BEGIN

query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year

|| ' WHERE invnum = :id';

OPEN c FOR query_str USING inv_num;

LOOP

FETCH c INTO inv_num, inv_cust, inv_amt;

EXIT WHEN c%NOTFOUND;

-- process row here

END LOOP;

CLOSE c;

END;

/