| 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;
- skalyar;
- murakkab;
- havolali;
- LOB tiplari;
- 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.