EXECUTE ilə iç-içə prosedur çağırışları: SQL kodunun dinamik icrası

Praktikaya keçməzdən əvvəl gəlin cavab verək: dinamik SQL nədir axı? Təsəvvür elə, sənə unikal adı olan bir cədvəl yaratmaq lazımdır və bu ad parametr kimi ötürülür. Və ya proqram işləyərkən adı müəyyən olunan cədvələ sorğu göndərmək istəyirsən. Burda sadə statik SQL bəs eləmir — bax, dinamik icra burada köməyə gəlir.

PL/pgSQL-də EXECUTE komandası var, bu da sənə string kimi ötürülən SQL sorğunu icra etməyə imkan verir. Bu, sənə "uçan" SQL kodu qurmağa və işlətməyə imkan verir, yəni sorğular parametrlərdən asılı olaraq fərqli olur.

Dinamik SQL-in faydalı ola biləcəyi səbəblər:

1. Elastiklik: Sorğuları daxil olan verilənlərə görə dinamik qurmaq imkanı. Məsələn, adları əvvəlcədən məlum olmayan cədvəllər və ya sütunlar üzərində əməliyyatlar aparmaq.
2. Avtomatlaşdırma: Unikal adlarla cədvəllər və ya indekslər yaratmaq.
3. Universal yanaşma: Proseduru təkrar yazmadan müxtəlif verilənlər strukturu ilə işləmək imkanı.

Real həyatdan nümunə: təsəvvür elə, sən analitika sistemi hazırlayırsan və hər yeni müştəri üçün onların məlumatlarını saxlamaq üçün ayrıca cədvəl yaratmaq lazımdır. Bütün bunları EXECUTE ilə avtomatlaşdıra bilərsən.

EXECUTE sintaksisi

Dinamik SQL-i EXECUTE ilə işlətmək belə görünür:

EXECUTE 'SQL-stringi';

Sadə sorğu nümunəsi:

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

Bu kod bloku test_table cədvəlini yaradacaq. Hər şey sadədir, amma gəlin bir az daha çətin hallara baxaq.

EXECUTE istifadəsinə nümunələr

1. Dinamik adla cədvəl yaratmaq

Tutaq ki, sənə cari tarixdən asılı olan adlarla cədvəllər yaratmaq lazımdır. Bunu belə edə bilərsən:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Cədvəl adını generasiya edirik
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Dinamik adla cədvəl yaradırıq
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- Yoxlama üçün mesaj çıxarırıq
  RAISE NOTICE 'Cədvəl % uğurla yaradıldı', table_name;
END $$;

Burada dinamik ad cari tarixdən generasiya olunur və yekun SQL-string EXECUTE-ə ötürülür.

2. Dinamik parametrlərlə sorğu icra etmək

Tutaq ki, sənə cədvəldən məlumat çıxarmaq lazımdır və cədvəlin adı parametr kimi ötürülür. Bunun üçün funksiya yaradaq:

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

Funksiyanı çağırmaq:

SELECT * FROM get_data_from_table('employees');

Bu yanaşma universal utilitlər, məsələn, dinamik hesabat sistemləri üçün əladır.

Dinamik SQL-in problemləri və məhdudiyyətləri

Dinamik SQL kodunun icrası sənə böyük azadlıq verir, amma həyatda olduğu kimi, azadlığın da məsuliyyəti var. Bax, burada çətinliklər çıxa bilər:

1. SQL injection: Əgər sən string parametrləri sorğuya emal etmədən ötürürsənsə, pis niyyətli biri istədiyi SQL kodunu icra edə bilər.

   Zəif kod nümunəsi:

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   Əgər user_input belə bir stringdirsə: '; DROP TABLE users; --, onda sorğu users cədvəlini siləcək.

2. Debug etmək çətindir: Dinamik kodu analiz və debug etmək çətindir, çünki sorğu icra zamanı qurulur və işlədilir.

3. Performans itkisi: Dinamik sorğular PostgreSQL-də execution plan caching mexanizmini pozur və bu da performansın azalmasına səbəb ola bilər.

SQL injection-dan necə qorunmalı

SQL injection hücumlarından qaçmaq üçün dinamik sorğularda sadə string birləşməsi əvəzinə parametrizasiya istifadə et. PL/pgSQL-də bunun üçün quote_literal() (string parametrlər üçün) və quote_ident() (identifikatorlar, məsələn, cədvəl və sütun adları üçün) funksiyalarından istifadə olunur.

Təhlükəsiz kod nümunəsi:

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

İcra: cədvəllərin dinamik yenilənməsi

Burada nümunə prosedur var, hansı ki, adı parametr kimi ötürülən cədvəldə dəyərləri yeniləyir:

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

Funksiyanı çağırmaq:

SELECT update_table_data('test_table', 1, 'Yenilənmiş dəyər');

Nümunə: müştəri üçün hesabat yaratmaq

Tutaq ki, sən müştərilərə görə sifarişləri qeyd edirsən və hər müştəri üçün hesabat cədvəli yaratmaq prosesini avtomatlaşdırmaq istəyirsən.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Hesabat cədvəlinin adını formalaşdırırıq
  table_name := 'client_report_' || client_id;

  -- Hesabat üçün cədvəl yaradırıq
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Cədvəli məlumatla doldururuq
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Müştəri üçün hesabat % yaradıldı: cədvəl %', client_id, table_name;
END $$ LANGUAGE plpgsql;

Dinamik SQL və EXECUTE — bu, PL/pgSQL-də avtomatlaşdırma və elastiklik üçün çox güclü alətdir. Amma istifadə edəndə SQL injection risklərini unutma. Sorğularının etibarlı və təhlükəsiz olmasını istəyirsənsə, quote_ident() və quote_literal() funksiyalarından istifadə et.

Növbəti mühazirədə biz kompleks prosedurların yaradılmasına, data validation, yazıların yenilənməsi və əməliyyatların loglanmasına daha dərindən baxacağıq. Hazır ol ki, dinamik sorğularla işləmək belə tapşırıqlar üçün əsas olacaq!

1

Sorğu/viktorina

, səviyyə, dərs

Əlçatan deyil

Yerləşdirilmiş tranzaksiyalar

Yerləşdirilmiş tranzaksiyalar

Açıq