Oracle PL/SQL Dinamik SQL Eğitimi: Anında ve DBMS_SQL'i Çalıştırma

Dinamik SQL nedir?

Hareketlilik SQL SQL, çalışma zamanında ifadeler oluşturmak ve çalıştırmak için kullanılan bir programlama metodolojisidir. Esas olarak, tablo adları, sütun listeleri veya WHERE koşulları program çalışana kadar bilinmediğinde olduğu gibi, gereksinime bağlı olarak SQL ifadelerinin çalışma zamanında oluşturulduğu ve yürütüldüğü genel amaçlı ve esnek programlar yazmak için kullanılır.

Dinamik SQL Yazmanın Yolları

PL/SQL, dinamik SQL yazmak için iki yol sunar:

1. NDS – Yerel Dinamik SQL (EXECUTE IMMEDIATE ve OPEN-FOR ifadeleri)
2. DBMS_SQL (tedarik edilen bir paket)

Genel kural basittir: Giriş ve çıkış değişkenlerinin sayısı ve veri tipleri derleme zamanında biliniyorsa, daha hızlı olduğu ve daha az kod gerektirdiği için Yerel Dinamik SQL'i kullanın. Bu bilgiler yalnızca çalışma zamanında biliniyorsa, DBMS_SQL paketini kullanın.

NDS (Yerel Dinamik SQL) – Anında Yürüt

Yerel Dinamik SQL, dinamik SQL yazmanın daha kolay bir yoludur. Çalışma zamanında SQL oluşturmak ve yürütmek için EXECUTE IMMEDIATE komutunu kullanır. Bu yaklaşımı kullanmak için, çalışma zamanında kullanılan değişkenlerin veri türü ve sayısı önceden bilinmelidir. Ayrıca, DBMS_SQL'e kıyasla daha iyi performans ve daha düşük karmaşıklık sağlar.

Sözdizimi

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• dinamik_sql_dizesi: Tek bir SQL ifadesi veya PL/SQL bloğu içeren bir dize ifadesi (VARCHAR2 veya CHAR, NVARCHAR2/NCHAR değil).
• INTO maddesi: İsteğe bağlı. Yalnızca dinamik SQL tek satırlı bir SELECT sorgusu olduğunda kullanılır; döndürülen değerleri değişkenlere veya bir kayda kaydeder. Seçilen her sütun için tür uyumlu bir değişken gereklidir.
• KULLANIM maddesi: İsteğe bağlı. Bağlama değişkenleri sağlar. Varsayılan mod IN'dir; OUT ve IN OUT, değerleri geri almak için kullanılır.
• RETURNING INTO maddesi: RETURNING yan tümcesi içeren DML ifadeleriyle birlikte, etkilenen satır değerlerini bağlama argümanlarına yakalamak için kullanılır.

Örnek 1: Bu örnekte, bir bağlama değişkeni içeren bir NDS ifadesi kullanarak emp_no '1001' için emp tablosundan verileri çekiyoruz.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

Çıktı

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Açıklama:

• 2-6 satırlar: Değişkenleri tanımlama.
• Çizgi 8: SQL sorgusu çalışma zamanında oluşturuluyor. SQL sorgusu, WHERE koşulunda ':empno' bağlama değişkenini içeriyor.
• 9-11 satırlar: Çerçevelenmiş SQL sorgusu EXECUTE IMMEDIATE ile yürütülüyor. INTO yan tümcesindeki değişkenler getirilen değerleri tutarken, USING yan tümcesi bağlama değişkeni :empno için değeri sağlıyor.
• 12-15 satırlar: Alınan değerler görüntüleniyor.

DDL için Dinamik SQL Kullanımı

Statik PL/SQL, CREATE, ALTER veya DROP gibi DDL komutlarını doğrudan çalıştıramaz. EXECUTE IMMEDIATE, bu sorunu komutları bir dize olarak oluşturarak çözer; bu, çalışma zamanında bir nesne adı verildiğinde de kullanışlıdır:

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Nesne adları (tablo, sütun, şema) bağlama değişkeni olarak geçirilemez, bu nedenle dizeye birleştirilmeleri gerekir. SQL enjeksiyonunu önlemek için bu tür girdileri her zaman doğrulayın, örneğin DBMS_ASSERT.SIMPLE_SQL_NAME ile.

Dinamik SQL için DBMS_SQL

PL/SQL, sorgunun yapısı çalışma zamanına kadar bilinmediğinde dinamik SQL ile çalışmak için DBMS_SQL paketini sağlar. Dinamik SQL oluşturma ve yürütme süreci aşağıdaki adımları içerir:

• İMLECİ AÇIN: Dinamik SQL, şu şekilde çalışır: imleçSQL sorgusunu çalıştırmak için öncelikle imleci açmamız gerekir.
• SQL'i ayrıştırma: Dinamik SQL'i ayrıştırın. Bu işlem sözdizimini kontrol eder ve sorguyu yürütülmeye hazır halde tutar.
• BAĞLANTI DEĞİŞKENİ Değerleri: Varsa, bağlama değişkenleri için değerleri atayın.
• SÜTUN TANIMLA: Her sütunu, SELECT sorgusundaki göreceli konumunu kullanarak tanımlayın.
• UYGULAMAK: Ayrıştırılan sorguyu yürütün.
• DEĞERLERİ AL: Çalıştırılan değerleri getir.
• İMLECİ KAPAT: Sonuçlar alındıktan sonra imleci kapatın.

Örnek 1: Bu örnekte, DBMS_SQL ifadesi kullanarak '1001' numaralı çalışanın verilerini emp tablosundan çekiyoruz. EXCEPTION bloğu, bir hata oluşsa bile imleci kapatır.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

Çıktı

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Açıklama:

• 1-8 satırlar: Değişken bildirimi.
• Çizgi 10: SQL sorgusunun çerçevesini oluşturma.
• Çizgi 11: DBMS_SQL.OPEN_CURSOR fonksiyonu kullanılarak imleç açılır ve açılan imlecin kimliği döndürülür.
• Çizgi 12: İmleç açıldıktan sonra SQL sorgusu ayrıştırılır.
• Çizgi 13: ':empno' yerine '1001' bağlama değeri atanmıştır.
• 14-17 satırlar: Sütunları göreceli konumlarına göre tanımlayın: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• Çizgi 18: Sorguyu DBMS_SQL.EXECUTE ile çalıştırarak işlenen kayıt sayısını döndürüyoruz.
• 19-32 satırlar: Kayıtlar bir döngü içinde alınıyor. FETCH_ROWS, hiçbir satır kalmadığında 0 değerini döndürerek döngüyü sonlandırıyor.
• İSTİSNA bloğu: Hata oluşması durumunda açık imleçlerin sızıntı yapmasını önlemek için imlecin kapatılmasını sağlar.

NDS ve DBMS_SQL: Hangisini Ne Zaman Kullanmalı?

Her iki yaklaşım da çalışma zamanında SQL çalıştırır, ancak farklı durumlara uygundurlar:

• Yerel Dinamik SQL kullanın (EXECUTE IMMEDIATE / OPEN-FOR) Giriş ve çıkışların sayısı ve veri tipleri derleme zamanında bilindiğinde, daha hızlı, okunması daha kolay ve daha az kod gerektirir.
• DBMS_SQL kullanın. Yapı çalışma zamanına kadar bilinmediğinde, örneğin seçilen sütun veya bağlama değişkeni sayısı değişen bir sorgu (yöntem-4 dinamik SQL olarak bilinir) veya tek bir 32K VARCHAR2 değişkenine sığmayacak kadar büyük bir ifade söz konusu olduğunda.

SSS