PL/SQL ma’lumotlar bazasi tili

PL/SQL
Semantikasi imperativ, obyektga yo‘naltirilgan dasturlash
Til toifasi dasturlash tili
Bajarilish turi protseduraviy
Paydo bo‘lgan yili 1992
Tiplash tizimi qat’iy, statik
Ta’sirida shakllangan til Ada
Ta’sir ko‘rsatgan til PL/pgSQL
Sayti oracle.com/techno… (ingl.)

PL/SQL (Procedural Language / Structured Query Language) — dasturlash tili, protseduraviy jihatdan kengaytirilgan SQL, uni ishlab chiqqan korporatsiya — Oracle. Unga asos bo‘lgan til — Ada.

PL/SQL quyidagi ma’lumotlar bazasini boshqarish tizimlariga (MBBT) o‘rnatilgan: Oracle Database (7-versiyadan boshlab), TimesTen[ingl.] (11.2.1-versiyadan boshlab) va IBM DB2 (9.7-versiyadan boshlab). PL/SQL, shuningdek, quyidagilarda ichki dasturlash tili sifatida qo‘llanadi: ilovalarni tezkor ishlab chiqish vositasi Oracle Forms, hisobotlar yaratish vositasi Oracle Reports hamda Oracle Application Express.

Imkoniyatlari

PL/SQL quyidagi imkoniyatlarni taqdim etadi: o‘zgaruvchilar, operatorlar, massivlar, kursorlar va istisnolar. 8-versiyadan boshlab obyektga yo‘naltirilgan model ham mavjud.

Standart SQL ixtisoslashgan deklarativ dasturlash tilidir. Bu tilda muayyan cheklovlar mavjud. Masalan, sikllar bevosita qo‘llab-quvvatlanmaydi. PL/SQL esa Tyuring bo‘yicha to‘liq til sifatida dasturchilarga relyatsion ma’lumotlar bazasidagima’lumotlarni imperativ dasturlash uslubiorqali qayta ishlash imkonini beradi. SQL operatorlarini PL/SQL protsedurasi, funksiyasi yoki triggeri ichidan to‘g‘ridan-to‘g‘ri chaqirish mumkin (ba’zan ayrim cheklovlar bilan).

Kodning asosiy tuzilishi

PL/SQL dasturi bloklardan (anonim yoki nomlangan) iborat. Blok ichida boshqa bloklar ham bo‘lishi mumkin; ular ba’zan ichki bloklar deb ataladi. PL/SQL blokining umumiy shakli:

DECLARE
-- Описания блока, переменные, типы, курсоры и т. п. (опционально)
BEGIN
-- Непосредственно код программы
EXCEPTION
-- Обработка исключений (опционально)
END;
/* Многострочные
комментарии… */
-- Однострочный комментарий

PL/SQL tilida quyidagi turdagi nomlangan bloklarni aniqlash mumkin:

  • protseduralar;
  • funksiyalar;
  • obyektlar;
  • paketlar.

Ularning barchasi kompilyatsiya qilinishi va ma’lumotlar bazasi obyektlari sifatida uning muayyan sxemasida saqlanishi mumkin. Paketlardan tashqari barcha nomlangan kod bloklari chaqiruvlar orasida o‘zining ichki holatini saqlab qolmaydi.

Paketlar yirik loyihalarda modullilikni ta’minlaydi: ular nomlangan kod bloklarini guruhlash imkonini beradi. Bundan tashqari, paketlarda ma’lumotlar bazasi sessiyasi davomida holatni saqlash mumkin; bu holatdan paket tarkibidagi funksiya va protseduralar foydalana oladi. PL/SQL paketlari spetsifikatsiya va tanadan iborat. Paket spetsifikatsiyasida konstantalar, o‘zgaruvchilar va ma’lumotlar tiplari aniqlanishi, protsedura hamda funksiyalar e’lon qilinishi mumkin. Paket tanasida spetsifikatsiyada e’lon qilingan protsedura va funksiyalar ta’riflanadi. Shuningdek, unda paketni boshlang‘ich holatga keltiruvchi kod bloki hamda ichki konstantalar, o‘zgaruvchilar, ma’lumotlar tiplari, protsedura va funksiyalar ta’riflari bo‘lishi mumkin. Spetsifikatsiyada e’lon qilingan paketning barcha komponentlaridan paket tashqarisida foydalanish mumkin, paket tanasi esa ularning amalga oshirilishini inkapsulyatsiya qiladi va tashqaridan bevosita foydalanish uchun ochiq bo‘lmaydi. Paket tanasi va spetsifikatsiyasini bir-biridan mustaqil ravishda o‘zgartirish, kompilyatsiya qilish va saqlash mumkin.

Ma’lumotlar tiplari

PL/SQL tili quyidagi tip toifalarini qo‘llab-quvvatlaydi:

  • ichki ma’lumotlar tiplari, jumladan kolleksiyalar va yozuvlar;
  • obyektga oid ma’lumotlar tiplari.

Boshqaruv operatorlari

  • tanlash operatorlari:
IF - THEN - END IF;
IF - THEN - ELSE - END IF;
IF - THEN - ELSIF - END IF;
IF - THEN - ELSIF - ELSE - END IF;
CASE - WHEN - THEN - END;
CASE - WHEN - THEN - ELSE - END;
  • sikl operatorlari:
LOOP - END LOOP;
WHILE - LOOP - END LOOP;
FOR - LOOP - END LOOP;
CONTINUE;
EXIT;
EXIT WHEN;
  • shartsiz o‘tish operatorlari:
GOTO;
NULL;
<<labels>>

Dastur namunasi

Konsolga SQL*Plus orqali «Hello, World!» satrini oldindan qiymat berilgan o‘zgaruvchi yordamida chiqaradigan dastur.

set serveroutput on

declare
    v_hello varchar2(50) := 'Hello, world!';
begin
    dbms_output.put_line(v_hello);
end;

Ma’lumotlar bazasi bilan ishlash

Statik SQL

PL/SQL kodiga tayyor SQL ifodalarini bevosita kiritish mumkin. Bunday holatda ifodaning to‘g‘riligi kod kompilyatsiya qilinayotganda tekshiriladi. Masalan, so‘rovda ishlatilgan jadval mavjud bo‘lmasa, xatolik aynan shu bosqichda, ya’ni kompilyatsiya jarayonida aniqlanadi.

Ma’lumotlar bazasidan bitta satrni so‘rash

Bunda SELECToperatoriga INTObandi qo‘shiladi. Bu bandda so‘ralgan ma’lumotlar yoziladigan o‘zgaruvchilar ko‘rsatiladi. Ularning soni va tipi maydonlarning soni (Oracle 9-versiyasigacha, shu versiya ham qo‘shib hisoblanganda, o‘zgaruvchilar soni ko‘proq bo‘lishi mumkin edi) va tipiga mos kelishi kerak (ayrim nomuvofiqliklarda esa ular avtomatik ravishda boshqa tipga aylantirilishi mumkin).

So‘rov natijasida birorta ham satr qaytmasa, NO_DATA_FOUNDistisnosi yuzaga keladi. Agar bittadan ortiq satr qaytsa, TOO_MANY_ROWSistisnosi yuzaga keladi. Odatda bunday istisnolarni qayta ishlash kerak. Biroq istisno yuzaga kelmasligi aniq bo‘lgan hollarda bunga ehtiyoj yo‘q. Masalan, jadvaldan birlamchi kalit orqali ma’lumot so‘ralganda TOO_MANY_ROWS istisnosini qayta ishlovchi qism kerak emas. Shuningdek, MAX() yoki MIN() kabi agregat funksiyalar ishlatilganda NO_DATA_FOUND istisnosi yuzaga kelmaydi, natijada o‘zgaruvchiga NULL qiymati beriladi

DECLARE
    empname   VARCHAR2(200);
BEGIN
    SELECT ename 
        INTO empname        
        FROM scott.emp
        WHERE empno = 7439;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('No records found!');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.put_line('Found more than one string!');
END;

Ma’lumotlar bazasidan bir nechta satrni so‘rash

Bir nechta satrni ketma-ket o‘qish uchun PL/SQL kursorlaridan foydalanish mumkin. Kursor deganda so‘rov natijasidagi navbatdagi satrni ko‘rsatuvchi ko‘rsatkich tushuniladi. Kursorni ochish va yopish OPEN va CLOSEoperatorlari yordamida bajariladi. Kursor ko‘rsatgan qiymatlarni o‘qish va keyingi satrga o‘tish uchun esa quyidagi operator ishlatiladi: FETCH.

So‘rovdan ma’lumotlarni o‘qish siklko‘rinishida tashkil qilinadi. Kursor so‘rov natijalarining oxiriga yetganida, FETCH operatorining navbatdagi chaqiruvi yangi ma’lumotlarni o‘qimaydi va <имя_курсора>%NOTFOUND atributi TRUEqiymatini oladi. Bu holatdan siklni to‘xtatish uchun foydalaniladi.

Bunday vaziyatda istisnolarni qayta ishlovchi qismlar shart emas: ma’lumot topilmasa, sikl biror marta ham bajarilmaydi.

DECLARE
    empname VARCHAR2(200);
    CURSOR c1 IS
        SELECT ename
            FROM scott.emp;
BEGIN
    OPEN c1;
        LOOP
            FETCH c1 INTO empname;
            EXIT WHEN c1%NOTFOUND;
            -- работа со значением empname
        END LOOP;
    CLOSE c1;
END;

Kursor ko‘rsatkichlaridan foydalanish

Moslashuvchanlikni oshirish uchun kursor o‘rniga unga oid ko‘rsatkich ishlatish qulay; u turli kursorlarga murojaat qila oladi. So‘rovga ega kursor ko‘rsatkich uchun OPEN operatori chaqirilayotganda FORbandi orqali yashirin tarzda aniqlanadi. Bitta kursor ko‘rsatkichini ko‘plab kursorlar va, demak, turli so‘rovlar bilan ishlatish mumkin.

DECLARE
    TYPE GenericCursor IS REF CURSOR;
    с1 GenericCursor;
    empname VARCHAR2(200);
BEGIN
    OPEN c1 FOR SELECT ename FROM scott.emp;
        LOOP
            FETCH c1 INTO empname;
            EXIT WHEN c1%NOTFOUND;
            -- работа со значением empname
        END LOOP;
    CLOSE c1;
END;

Bog‘langan o‘zgaruvchilardan foydalanish

Kursorlar yoki ularning ko‘rsatkichlaridan foydalanganda so‘rov tuzishda unga aniq konstantalarni kiritmaslik tavsiya etiladi (bu konstantalar shu turdagi barcha so‘rovlarda o‘zgarmas bo‘lib qoladigan holatlar bundan mustasno). Sababi shundaki, faqat konstantasi bilan farqlanadigan ikkita so‘rov ketma-ket bajarilsa (masalan, SELECT ename FROM employees WHERE id = 20 va SELECT ename FROM employees WHERE id = 21), ma’lumotlar bazasini boshqarish tizimi ularning har birini alohida tahlil qiladi, aslida esa bu so‘rovlarning bajarilish rejasi bir xil. So‘rovlarni qayta-qayta tahlil qilish ilovaning unumdorligini sezilarli darajada pasaytirishi mumkin.

Ortiqcha tahlillarning oldini olish uchun bog‘langan o‘zgaruvchilar (ingl. bind variables) usuli qo‘llanadi. Bunda o‘zgaruvchilar bevosita so‘rov matnida turadi, ularning qiymatlari esa so‘rov uchun kursor ochilgandagina qo‘yiladi. Bog‘langan o‘zgaruvchi nomi oldidan ikki nuqta belgisi qo‘yiladi. Kursor ochilayotganda o‘zgaruvchilar qiymatlari USINGbandi orqali beriladi. Bog‘langan o‘zgaruvchilar ishlatilgan kod qismi birinchi marta bajarilganda so‘rov ma’lumotlar bazasini boshqarish tizimi tomonidan tahlil qilinadi va uning bajarilish rejasi tuziladi (bu nisbatan ko‘proq vaqt oladi). Keyingi bajarilishlarda tayyor reja ishlatiladi va so‘rov natijalarni tezda qaytaradi.

Bog‘langan o‘zgaruvchilardan foydalanadigan funksiya namunasi:

FUNCTION get_employee_name (empid INTEGER, empcity VARCHAR2) RETURN VARCHAR2 IS
    TYPE GenericCursor IS REF CURSOR;
    c1 GenericCursor;
    empname VARCHAR2(200);
BEGIN
    OPEN c1 FOR 'SELECT ename FROM employees WHERE id = :id AND city = :city' USING empid, empcity;
        -- цикл не используется, так как запрос вернёт не более одной строки
        FETCH c1 INTO empname;
    CLOSE c1;

    RETURN empname;
END get_employee_name;

Siklda kursorni oshkormas tarzda aniqlash

Ba’zan kursor yoki unga ko‘rsatkichni e’lon qilish o‘rniga kursor va o‘zgaruvchini oshkormas tarzda aniqlash qulay. Bu o‘zgaruvchi tipi — yozuv (RECORD):

DECLARE

BEGIN
    FOR rec IN (SELECT id, ename, 1 AS value FROM employees) LOOP
        dbms_output.put_line(rec.id || ': ' || rec.ename);
    END LOOP;
END;

Paketli so‘rov

Ko‘p sonli satrlar so‘ralganda, ularni birma-bir o‘qish o‘rniga birdaniga o‘qish orqali unumdorlikni oshirish mumkin. Bu PL/SQL va SQL o‘rtasidagi kontekst almashinuvlari sonini sezilarli darajada kamaytiradi. Buning uchun FETCH operatoriga BULK COLLECT ko‘rsatmasi qo‘shiladi; tanlangan yozuvlar oddiy o‘zgaruvchilarga emas, assotsiativ kolleksiyalarga yoziladi:

DECLARE
    TYPE GenericCursor IS REF CURSOR;
    c1 GenericCursor;
    TYPE VarcharTable IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
    -- объявили тип данных "Таблица строк", элементы которой нумеруются числами
    empnames VarcharTable;
    -- объявили переменную созданного типа
BEGIN
    OPEN c1 FOR SELECT ename FROM employees;
        FETCH c1 BULK COLLECT INTO empnames;
    CLOSE c1;
END;

Shuningdek, ko‘plab qiymatlarni so‘rab, ularni oldindan tayyorlangan bitta yoki bir nechta kolleksiyaga joylash mumkin. Buning uchun, masalan, quyidagicha tegishli kolleksiya tipi yaratiladi:

 CREATE OR REPLACE TYPE t_str_coll AS TABLE OF VARCHAR2(2000 CHAR);

so‘rov natijasini shu tipdagi kolleksiyaga quyidagicha joylash mumkin:

DECLARE
  l_str_coll t_str_coll;
BEGIN
  SELECT t.ename
    BULK COLLECT 
    INTO l_str_coll
    FROM employees t;
END;

DML amallarini bajarish

Odatda DMLamallari SQL dagi kabi bajariladi:

DECLARE

BEGIN
    UPDATE employees SET hire_date = SYSDATE WHERE id != 1;
    INSERT INTO employees (name, city) VALUES ('SMITH', 'Тикси');
    COMMIT;
END;

Dinamik SQL

Dinamik so‘rovlar

Moslashuvchanlikni oshirish uchun statik so‘rovlar ko‘pincha dinamik tarzda tuziladigan so‘rovlar bilan almashtiriladi. Dinamik SQLning kamchiligi shundaki, bunday so‘rovlarning to‘g‘riligini kompilyatsiya bosqichida tekshirib bo‘lmaydi. Masalan, so‘rovda ishlatilgan jadval mavjud bo‘lmasa, OPEN amali bajarilayotganda istisno yuzaga keladi.

SQL so‘rovlarini dinamik tuzish talab etiladigan odatiy vazifalardan biri — interfeyslardafoydalanuvchi turli shartlarni tanlab shakllantiradigan hisobotlarni yaratishdir.

Quyida biror shartga qarab xodimning ismini uning identifikatori yoki shahri bo‘yicha so‘raydigan anonim kod bloki keltirilgan.

DECLARE
    TYPE GenericCursor IS REF CURSOR;
    c1 GenericCursor;
    sel VARCHAR2(4000);
    bind_var VARCHAR2(200);
    result VARCHAR2(200);
BEGIN
    sel := 'SELECT name FROM employees WHERE 1 = 1';
    IF ... THEN
        sel := sel || ' AND id = :1';
        bind_var := 12;
    ELSE
        sel := sel || ' AND city = :1';
        bind_var := 'Магадан';
    END IF;

    OPEN c1 FOR sel USING bind_var;
        FETCH c1 INTO result;
    CLOSE c1;
END;

Dinamik DML va DDL amallari

Dinamik DML va DDL amallari quyidagi operator yordamida bajariladi: EXECUTE IMMEDIATE.

DECLARE

BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM employees';
    EXECUTE IMMEDIATE 'DROP TABLE employees';
    -- COMMIT или ROLLBACK не нужен, потому что DDL-операция завершила транзакцию
END;

Bog‘langan o‘zgaruvchilar ham ishlatilishi mumkin. Ularning qiymatlari quyidagi bandda ko‘rsatiladi: USING.