PL/SQL

PL/SQL
PL/SQL
Семантыка	імператыўнае, аб’ектна-арыентаванае праграмаванне
Тып выканання	працэдурная
З’явілася ў	1992
Тыпізацыя даных	моцная, статычная
Зведала ўплыў	Ada
Паўплывала на	PL/pgSQL
Сайт	oracle.com/techno… (англ.)

PL/SQL (Procedural Language / Structured Query Language) — мова праграмавання, працэдурнае пашырэнне мовы SQL, распрацаванае карпарацыяй Oracle. Грунтуецца на мове Ада^[1].

PL/SQL убудаваны ў наступныя СКБД: Oracle Database (пачынаючы з версіі 7), TimesTen (з версіі 11.2.1) і IBM DB2 (з версіі 9.7)^[2]. Таксама PL/SQL выкарыстоўваецца як убудаваная мова для сродка хуткай распрацоўкі Oracle Forms, інструмента распрацоўкі справаздач Oracle Reports і ў Oracle Application Express.

Функцыянальнасьць

PL/SQL дае магчымасць выкарыстоўваць пераменныя, аператары, масівы, курсоры і выключэнне. Пачынаючы з версіі 8 даступная і аб’ектна-арыентаваная мадэль.

Стандартны SQL з’яўляецца спецыялізаваным дэкларатыўнай мовай праграмавання. На мову накладзеныя пэўныя абмежаванні, такія як, напрыклад, адсутнасць прамой падтрымкі цыклаў. PL/SQL жа, як поўная па Цьюрынгу мова, дазваляе распрацоўнікам апрацоўваць даныя ў рэляцыйнай базе, выкарыстоўваць імператыўны стыль праграмавання. Аператары SQL могуць быць лёгка выкліканыя непасрэдна з PL/SQL-працэдуры, функцыі або трыгера (часам з некаторымі абмежаваннямі).

Базавая структура кода

Праграма на PL/SQL складаецца з блокаў (ананімных або пайменаваных). Блок можа ўтрымліваць укладзеныя блокі, якія часам называюць падблокамі. Агульная форма PL/SQL-блока:

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

Мова PL/SQL дазваляе вызначаць наступныя тыпы найменных блокаў:

працэдуры;
функцыі;
аб’екты;
пакеты.

Усе яны могуць быць скампіляваны і захаваны як аб’екты базы даных у некаторай яе схеме. Усе найменныя блокі кода, акрамя пакетаў, не захоўваюць ўнутраны стан ад выкліку да выкліку.

Пакеты забяспечваюць модульнасць для вялікіх праектаў, дазваляючы згрупаваць наборы найменных блокаў кода, акрамя таго, у пакетах магчыма захоўванне стану на час жыцця сесіі базы даных, даступнае для функцый і працэдур, якія ўваходзяць у пакет. Пакеты ў PL/SQL утрымліваюць спецыфікацыю і цела. Спецыфікацыя пакета можа ўтрымліваць вызначэнне канстант, пераменных, тыпаў даных, аб’яву працэдур і функцый. Цела пакета вызначае абвешчаныя ў спецыфікацыі працэдуры і функцыі, а таксама можа ўтрымліваць блок кода ініцыялізацыі пакета, вызначэнні ўнутраных канстант, пераменных, тыпаў даных, працэдур і функцый. Усе кампаненты пакета, абвешчаныя ў яго спецыфікацыі, могуць быць даступныя для выкарыстання звонку пакета, а цела пакета інкапсулюе рэалізацыю гэтых кампанентаў, і звонку недаступна. Цела і спецыфікацыя пакета могуць мадыфікавацца, кампілявацца і захоўвацца незалежна адзін ад аднаго.

Тыпы даных

Мова PL/SQL падтрымлівае наступныя катэгорыі тыпаў:

убудаваныя тыпы даных, уключаючы калекцыі і запісы;
- скалярныя;
- састаўныя;
- спасылачныя;
- LOB-тыпы;
аб’ектныя тыпы даных.

Аператары кіравання

аператары выбару:

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;

аператары цыклу:

LOOP - END LOOP;
WHILE - LOOP - END LOOP;
FOR - LOOP - END LOOP;
CONTINUE;
EXIT;
EXIT WHEN;

аператары безумоўнага пераходу:

GOTO;
NULL;
<<labels>>

Прыклад праграмы

Праграма, якая выводзіць у кансолі SQL*Plus радок «Hello, World!» з выкарыстаннем ініцыялізаванай пераменнай.

set serveroutput on

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

Праца з базай даных

Статычны SQL

У PL/SQL дапускаецца ўключаць гатовыя SQL-запыты непасрэдна ў код. У такім выпадку праверка запыта на карэктнасць ажыццяўляецца ўжо пры кампіляцыі кода. Так, напрыклад, калі выкарыстоўваная ў запыце табліца не існуе, то памылка будзе выдадзена ўжо на этапе кампіляцыі.

Запыт аднаго радка з базы даных

Выкарыстоўваецца SQL-выраз SELECT, дапоўнены прапановай INTO, у якой паказваюцца пераменныя, куды запішуцца запытаныя даныя. Колькасць і тып гэтых пераменных павінны адпавядаць колькасці (да версіі Oracle 9 уключна пераменных магло быць больш) і тыпу палёў (хоць пры пэўных неадпаведнасцях тыпаў можа адбыцца іх невідавочнае прывядзенне).

У выпадку, калі запыт вярнуў нулявы лік радкоў, выкідваецца выключэнне NO_DATA_FOUND. У выпадку, калі радкоў больш, чым адзін, выкідваецца выключэнне TOO_MANY_ROWS. Гэтыя выключэнні варта апрацоўваць у адпаведнай частцы блока за выключэннем выпадкаў, калі мяркуецца, што яны не могуць быць выкінутыя. Напрыклад, пры запыце даных з табліцы па іх першаснаму ключу апрацоўшчык выключэння TOO_MANY_ROWS не патрэбны. Таксама ў выпадку выкарыстання агрэгаваных функцый, напрыклад MAX () або MIN () — выключэнне NO_DATA_FOUND не будзе згенеравана, а пераменная атрымае ў якасці выніку NULL.

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;

Запыт некалькіх радкоў з базы даных

Для паслядоўнага счытвання некалькіх радкоў можна выкарыстоўваць курсоры PL/SQL. Пад курсорам маецца на ўвазе паказальнік на чарговы радок у выніках запыту. Адкрыццё і закрыццё курсора ажыццяўляецца аператарамі OPEN і CLOSE. Счытванне значэнняў, на якія паказвае курсор, і яго перавод на наступны радок ажыццяўляецца аператарам FETCH.

Счытванне даных з запыту афармляецца як цыкл. Калі курсор дойдзе да канца вынікаў запыту, чарговы выклік аператара FETCH не счытае новых даных, а атрыбут < імя_курсора>%NOTFOUND прымае значэнне TRUE. Гэта падзея выкарыстоўваецца для прыпынення працы цыклу.

Апрацоўшчыкаў выключэнняў у гэтым выпадку не патрабуецца, калі даныя не будуць знойдзеныя, то цыкл не будзе выкананы ні разу.

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;

Выкарыстанне паказальнікаў на курсоры

Для большай гнуткасці зручна замест курсора выкарыстоўваць паказальнік на курсор з рознымі курсорамі. У такім выпадку курсор з запытам вызначаюцца няяўна пры выкліку аперацыі OPEN для паказальніка на курсор з дапамогай прапановы FOR. Адзін паказальнік на курсор можна выкарыстоўваць з многімі курсорамі і, адпаведна, з многімі запытамі.

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;

Выкарыстанне звязаных пераменных

Як пры выкарыстанні курсораў, так і пры выкарыстанні паказальнікаў на курсоры рэкамендуецца пры фарміраванні запытаў не ўключаць туды канкрэтныя канстанты (акрамя тых выпадкаў, калі гэтыя канстанты сапраўды будуць захоўвацца ва ўсіх падобных запытах). Звязана гэта з тым, што пры паслядоўным выкананні двух запытаў, якія адрозніваюцца толькі канстантай (напрыклад, SELECT ename FROM employees WHERE id = 20 і SELECT ename FROM employees WHERE id = 21), СКБД робіць разбор кожнага запыту асобна, хоць на самай справе план выканання ў такіх запытаў агульны. Такія паўторныя разборы могуць істотна знізіць прадукцыйнасць прыкладання.

Для прадухілення лішніх разбораў выкарыстоўваецца тэхніка звязаных пераменных (англ.: bind variables), гэта значыць пераменныя непасрэдна ў целе запыту, значэнни якіх падстаўляюцца толькі пры адкрыцці курсора для запыту. Звязаныя пераменныя абазначаюцца імем, папярэднім сімвалам двукроп’я. Пры адкрыцці курсора значэнни пераменных паказваюцца з дапамогай прапановы USING. Пры першым выкананні ўчастка кода, які выкарыстоўвае тэхніку звязаных пераменных, запыт будзе разабраны ў СКБД, для яго будзе створаны план выканання (гэта будзе адбывацца параўнальна доўга); пры наступных выкананнях будзе выкарыстоўвацца ўжо створаны план выканання, і запыт будзе хутка вяртаць значэння.

Прыклад функцыі са звязанымі пераменнымі:

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;

Невідавочнае вызначэнне курсора ў цыкле

Часам замест таго, каб аб’яўляць курсор або паказальнік на яго, зручна скарыстацца нявідавочным вызначэннем курсора і нявідавочным вызначэннем пераменнай тыпу запіс (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;

Пакетны запыт многіх радкоў

Пры запыце вялікага ліку радкоў можна павялічыць прадукцыйнасць, калі замест пачарговага зачытвання радкоў выніку, зачытаць іх усіх адразу, значна знізіўшы тым самым колькасць пераключэнняў кантэксту ад PL/SQL да SQL і назад. Для пакетнага чытання неабходна забяспечыць аператар FETCH інструкцыя BULK COLLECT. Даныя пры гэтым павінны запісвацца не ў пераменныя, а ў асацыятыўныя калекцыі:

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;

Пакетны запыт многіх значэнняў

Таксама можна запытаць шмат значэнняў і змясціць іх у загадзя падрыхтаваную калекцыю (або некалькі калекцый). Для гэтага ў нас павінен быць абвешчаны адпаведны тып калекцыі, напрыклад калекцыя радкоў:

create or replace type t_str_coll as table of varchar2(2000 char);

Тады, мы можам змясціць усе ename з таблічкі employees ў нашу калекцыю такім чынам:

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

Выкананне аперацый DML

Аперацыя DML, як правіла, выконваюцца сапраўды гэтак жа, як і ў SQL:

DECLARE

BEGIN
  UPDATE employees SET hire_date = SYSDATE WHERE id != 1;
  INSERT INTO employees (name, city) VALUES ('SMITH', 'Гродна');
  COMMIT;
END;

Дынамічны SQL

Дынамічныя запыты

Для большай гнуткасці часта статычныя запыты замяняюцца запытамі, якія фарміруюцца дынамічна. Недахоп дынамічнага SQL у тым, што дынамічныя запыты не могуць быць правераны на этапе кампіляцыі. Калі, напрыклад, табліцы, якая выкарыстоўваецца ў запыце, не існуе, то пры выкананні аперацыі OPEN паўстане выключэнне.

Класічная задача, якая патрабуе прымянення дынамічнага канструявання SQL-запытаў, — справаздачы ў інтэрфейсах, дзе карыстальнік можа выбраць розныя ўмовы, па якіх варта сфармаваць справаздачу.

Ніжэй прыведзены ананімны блок кода, які ў залежнасці ад нейкай умовы запытвае імя супрацоўніка альбо па ключы, альбо па горадзе.

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;

Дынамічныя DML- і DDL-аперацыі

Дынамічныя аперацыі DML і DDL выконваюцца з дапамогай аператара EXECUTE IMMEDIATE.

DECLARE

BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM employees';
  EXECUTE IMMEDIATE 'DROP TABLE employees';
  -- COMMIT або ROLLBACK не патрэбен, бо DDL-аперацыя скончыла транзакцыю
END;