11. PL/SQL программасына сипаттама

PL/SQL – бұл процедуралық блокты- құрылымдық тіл. Ол SQL тілі кеңейтілімінен тұрады және Oracle ДББЖ жұмыс істеу үшін арналған.

PL/SQL қосымшаны құрушыға және интерактивті қолданушыға келесі мүмікіндіктерді береді:

  • Ішкі программаларды жеке блоктар ретінде жүзеге асыруға, соның ішінде салынған блоктарды қолдану;
  • Мәліметтер базасында сақталатын пакеттер, процедуралар және функциялар құру;
  • Ішкі процедуларды шақыру үшін интерфейс беру мүмкіндігі;
  • SQL мәліметтер типімен қоса PL/SQL арқылы енгізілетін типтерді де қолдана алуы;
  • Айқындалған және айқындалмаған курсорларды қолдану, сонымен қатар курсорлар үшін қолданылатын FOR циклі операторын қолдану;
  • PL/SQL айнымалылары үшін енгізу және элемент құрылымына немесе мәліметтер типіне сілтеме жасайтын атрибуттар курсорын енгізу;
  • Коллекциялардың және объекті типтерді енгізу;
  • Басқару операторларының және цикл операторының жиынтығын қолдау;
  • Шығарып тастау механизмін жүзеге асыру

PL/SQL негізгі программалық бірлігі ретінде блок қолданылады, ол салынған блоктардан тұрады, кейде оларды ішкі блоктар деп те атайды.

Блок бірдей логикамен байланысқан хабарландыру мен операторларды біріктіреді; Ол анонимді және аты бар бола алады

Блок негізгі үш бөліктен тұрады:

  • Хбарландырулар секциясы (міндетті емес бөлік);
  • Блоктың денесі;
  • Шығарып тастау өңдеушілері (міндетті емес бөлік).

PL/SQL регистрге сезімтал емес. Тек қана қатарлық айнымалылар мен тұрақтылар үшін ғана сезімтал.

PL/SQL әрбір констукциясы ; символымен аяқталуы қажет .

Бір ғана констукция бірнеше қатарларда беріле алады.

Мәліметтер типтері

PL/SQL тілі келесі типтер категориясынан тұра алады:

  • Салынған мәліметтер типі, коллекциялар мен жазбаларды қоса алғанда;
  • Мәліметтердің объектті типтері.

Мәліметтердің икемделген типтері

Мәліметтердің икемделген типтері

11.1 суретінде PL/SQL икемделген типтеті көрсетілген.

11.1.PL/SQL икемделген типтерінің тізімі

Скалярлы типтер ішкі құрамы жоқ жай мәндерді бейнелейді.

Құрамды типтер ішкі компоненттері бар құрылымды бейнелейді

Сілтемелік типтер мәндкерден тұрады. LOB типтері локатор деп аталатын мәндерден тұрады. Олар сақталған мәліметтердегі үлкен объектілердің орналасуын анықтайды (мысалы, графикалық файлдар).

Келесі кестеді PL/SQL тілінің мәліметтер типін көрсетеді

Синтаксис

Мәндер диапазоны

Сандық типтер

BINARY_INTEGER PLS_INTEGER (белгісі бар бүтін)

-2147483647 .. 2147483647. Тип PLS_INTEGER аз жадты қажет етеді және басқа сандық типтерге қарағанда тезірек өңделеді

NUMBER[(precision,scale)] (жүзу нүктесі бар)

1.0E-130 .. 9.99E125

NUMERIC (нақтыланған нүктесі бар)

38 ондық таңбаға дейін нақтыланған

FLOAT (жүзу нүктесі бар)

38 ондық таңбаға дейін нақтыланған

REAL (жүзу нүктесі бар)

38 ондық таңбаға дейін нақтыланған

Символдық типтер

CHAR[(maximum_length)] (тұрақты ұзындықтағы жолдар үшін) LONG (айнымалы ұзындықтағы жолдар үшін)

32767 байтқа дейін Мәліметтер базасының қатары үшін CHAR типінің максималды ұзындығы 2000 байт, ал LONG типінікі - 2 Гб

RAW(maximum_length) LONG RAW (екілік мәліметтер үшін немесе байттар жолы)

32767 байтқа дейін Мәліметтер базасының қатары үшін RAW типінің максималды ұзыдығы- 2000 байт.

VARCHAR2 (maximum_length) (айнымалы ұзындықтағы символдар жолы үшін)

32767 байтқа дейін

Барлық Икемделген мәліметтер типі базалық типтер болып табылады.

PL/SQL кез келген базалық типі мәндер жиынтығы және операциялар жиынтығы ретінде анықталады.

PL/SQL тілі кейбір базалық типтер мәні секілді жаңа ішкі типтерді анықтауға мүмкіндік береді. Ішкі типтер мәліметтерге қосымша операциялар енгізбейді және ешқандай жаңа типтер анықтамайды.

Ішкі типтер анықтамасы келесі формальды бейнеленулерден тұрады:

SUBTYPE subtype_name IS base_type;

Oracle мәліметтер базасындағы STANDARD пакетінде бірнеше ішкі типтер анықталған, олар кез келген блока үшін қолданылады.

Қолданушы өзінің типін секциядағы басқаша ішкі типтер секілді анықтай алады. Блок, ішкі программадағы немесе PL/SQL пакетінде анықтағандай:

Мысалы:

DECLARE SUBTYPE MyDate IS DATE;

- Основан на типе DATE

TYPE MyRec IS RECORD (time1 INTEGER,

time2 INTEGER);

SUBTYPE MyInterval IS MyRec;

- Основан на типе RECORD

SUBTYPE ID_N IS tbl1.f1%TYPE;

- Основан на типе столбца

Базалық ретінде қолданылатын типтер ұзындығына шектеу жоқ. Ұзындығына шектеу қоятын қолданушылық тип құру үшін алдын ала осындай типтегі айнымалыны хабарлап қояды және соның негізінде қолданушылық тип анықталады.

Мысалы:

DECLARE var1 VARCHAR2(6);

- Объявление переменной

- с ограничением длины

SUBTYPE string_6 IS var1%TYPE;

- Объявление

LOB-типтер

LOB-типтер үлкен объектілерді сақтау үшін қолданылады (Large Object). SQL-99 стандарты LOB-типтерді қолдауды кеңейтілген деңгейге дейінгі сәйкестікке алып келді. Бірақ та Oracle да салыстырмалы түрдегі LOB-типтер жиынтығы бар.

Oracle8 де LOB-типті мәлісеттерді 4 Гбайтқа дейінгі ұзындықта сақтауға мүмкіндік берілген.

LOB типі LONG типінен айырмашылығы кез келген LOB типтің SELECT операторы арқылы мәнін таңдаған кезде мән емес көрсеткіш қайтарылады; Сонымен қатар LOB типтер ішкі де бола алады.

Oracle үлкен объектілер үшін келесі төрт типтен тұра алады:

  • BFILE – ішкі екілік файл үшін;
  • BLOB – ішкі екілік объекті үшін;
  • CLOB – ішкі символдық объектілр үшін;
  • NCLOB – символдардың халықтық жиынтығын есептейтін ішкі символдық объектілер үшін.

LOBтың кез келген объектілері екі бөліктен тұрады: мәліметтерден және осы мәліметтерге көрсеткіштерден, оларды кейде локатор деп те атайды.

BLOB, CLOB немесе NCLOB типтері мәліметтер базасы үшін де, сонымен қатар PL/SQL айнымалылары үшін де қолданылады.

LOB объектілерін жүктеу үшін PL/SQL DBMS_LOB пакеті қарастырылған.

DBMS_LOB пакеті LOB-типтерімен жұмыс жасау үшін процедуралардан және функциялардан тұрады, олардың кейбіреулері келесі таблицада көрсетілген.

Синтаксис

Бейнеленуі

APPEND (d1,d2)

d1ге d2ні қосады

COMPARE(d1,d2,n,pos1,pos2)

Сравнивает значений d1 және d2 мәндерін n байт арқылы салыстырады

COPY (d,s,n,dp,sp)

Dдан s ке n байтты көшіреді.

FILEOPEN (bdata,m)

m параметрімен көрсетілген режимде BFILE типінің объектісін ашады

LOADFROMFILE (bdata1,data2,n,pos1,pos2)

BFILE bdata1 типінің объектісінің n байтын LOB data2 типінің кез келген объектісіне көшіреді

GETLENGTH (data)

LOB объектісінің көрсетілген ұзындығын қайтарады

READ (data,n,pos,buf)

data объектісінен n байтты оқиды

WRITE (data,n,pos,buf)

buf буферінен n байтты көшіреді

EMPTY_CLOB (), EMPTY_BLOB ()

Берілген типтің "бос" объектісін құрады

Мысалы:

DECLARE pf1 CLOB; pf2 BLOB;

buf varchar2;BEGIN CREATE TABLE tbl1 ( f1 CLOB, f2 BLOB);INSERT INTO tbl1 VALUES

(empty_clob(),empty_blob() );

SELECT f1 INTO pf1 FROM tbl1 FOR UPDATE;

buf := 'Текст, который будет вставлен в объект LOB'; DBMS_LOB.write (pf1, length(buf), 0, buf);END;

Типтерді келтіру

PL/SQL анықталған және анықталмаған типтерді келтірулерден тұрады: типтердің анықталған келтірулері икемделген функциялардың көмегімен орындалады, ал анықталмаған кетірулер PL/SQL арқылы бір типтің мәнін меншіктеумен орындалады.

Әртүрлі типтердің айнымалыларының мәні бір біріне меншіктеле алады, егер олар бір типті базадан құралса

Айнымалыларды және тұрақтыларды хабарлау

Айнымалылар SQL мәліметтерінің типінен немесе PL/SQL мәліметтерінің типінен тұра алады.

Айнымалылар PL/SQL блогындағы хабарламалар секцияларындағы ішкі программаларда және пакеттерде жарияланады.

Айнымалыны оның идентификаторынан кейін хабарлау үшін кез келген қол жетімді мәліметтер типін көрсету керек.

PL/SQLде айнымалыларды хабарлау келесі формалардан тұра алады:

var_name type;var_name type := expr;var_name type DEFAULT expr;var_name type NOT NULL := expr;var_name type_var%TYPE;var_name type_var%TYPE := expr;var_name user.table.type_col%TYPE;var_name user.table.type_col%TYPE := expr;

Айнымалыны жариялау барысында бірдей уақытта сәйкес келетін типпен инициализациялана алады. Меншіктеудің оң жағында тұрған өрнек бұрын хабарланған және инициализацияланған айнымалыныи немесе тұрақтыларды қолдана алады. PL/SQL қолданылатын сілтеменің программада оның қолдану орнынан жоғары тұрғанын талап етеді.

Айнымалыны оператор орнына жариялау барысында DEFAULT кілттік сөзі қолданыла алады.

Жарияланатын айнымалы NOT NULL секілді анықтала алады. Мұндай айнымалағы келесі жағдайларда NULL мәнін меншіктеуге болмайды.

Айнымалаыларға мәндерді екі жағдайда меншіктеуге болады – меншіктеу операторы арқылы немесе сілтеме барысында көрсетілетін INTO- айнымалысы арқылы.

Идентификаторлан кейін тұрақтыларды жариялау барысында CONSTANT кілттік сөзі қолданылуы қажет, ал типтер идентификаторынан кейін – меншіктеу операторы және тұрақтының мәні көрсетіледі.

Тұрақтыларды жариялау келесі формальды бейнеленулерден тұрады:

const_name CONSTANT type :=value;

Мысалы:

val_real CONSTANT REAL := 5000.00;

Символдық тұрақтылар бірлік тырнақшаларға жабылады.

%TYPE және %ROWTYPE атрибуттары

%TYPE атрибуты келесі деңгейлерге сәйкес келетін айнымалылар типтерін жариялауға мүмкіндік береді:

  • Басқа айнымалының типіне ;
  • Мәліметтер базасының қатарының типіне.

Мысалы:

var1 REAL(14,2);var2 var1%TYPE;

- Переменная var2 будет иметь тип как var1var_f1 user1.tbl1.f1%TYPE;

/* Переменная var_f1 будет иметь тот же

тип, что и поле f1 таблицы tbl1

пользователя user1*/

%ROWTYPE атрибуты "жазба" типіндегі айнымалыны кестедегі сәйкес келетін жолға жариялауға мүмкіндік береді. Айнымалының мұндай типі кесте аймағының атымен және типімен сәйкес келетіндей аймақтардан тұрады.

Мұндай тип жолмен программалау операциясын жеңілдетеді. Ол жолды таңдауды толығымен бір айнымалы «жазба» типіне айналдыруға, сонымен қатар, кесте құрылымы ауысқан жағдайда блоктарды қайта программалауға мүмкіндік береді.

%ROWTYPE атрибутын қолдану арқылы анықталған айнымалылар мәндері бір жазбадан басқа бір жазбаға мәндерді меншіктеу арқылы жүзеге асады, SELECT операторына INTO айнымалысын меншіктеген секілді болады.

Мысалы:

DECLARE tbl1_rec1 tbl1%ROWTYPE;

- Для строки из таблицы tbl1

tbl1_rec2 tbl1%ROWTYPE;

CURSOR c1 IS SELECT * FROM tbl1;

emp_rec2 c1%ROWTYPE;

- Для строки курсора с1,

- созданного из таблицы tbl1 emp_rec3 с1%ROWTYPE;BEGIN

SELECT * INTO tbl1_rec1 FROM tbl1

WHERE tbl1.f1=1;

emp_rec2 := emp_rec1;

- Присвоение значения всем полям записиEND

"Жазба" типіндегі айнымалы аттар квалификациясыз мәндерді таңдау үшін ғана қолданыла алады. Бірақ оны қою үшін немесе жолдың мәндері қайта жаңарту үшін қолдануға болмайды. SQL-операторда қою үшін немесе қайта жаңарту кестенің әрбір қатар үшін жазба аймағының мәні көрсетілуі қажет.

PL/SQL тілінің басқару операторлары

PL/SQL тілінің программаның орындалу барысын басқару үшін қолданылатын кез келген операторы келесі топтарда берілген операторлардың бірі бола алады:

  • Таңдау операторлары:
    • IF-THEN-END IF;
    • IF-THEN-ELSE-END IF;
    • IF-THEN-ELSIF-END IF;
  • Цикл операорлары:
    • LOOP-END LOOP;
    • WHILE-LOOP-END LOOP;
    • FOR-LOOP-END LOOP;
    • EXIT;
    • EXIT WHEN;
  • Шартсыз көшу операторлары:
    • GOTO;
    • NULL;
    • <<labels>>.

Таңдау операторлары

PL/SQL тілі таңдаудың үш формасын жүзеге асырады, олардың әрқайсысы келесі формальды бейнеленулерден тұра алады:

- 1 форма:

IF condition THEN sequence_of_statements; END IF;

- 2 форма:

IF condition THEN sequence_of_statements1;ELSE sequence_of_statements2; END IF;

- 3 форма:

IF condition1 THEN sequence_of_statements1;ELSIF condition2 THEN sequence_of_statements2; - Ключевое слово ELSIF - может повторяться многократноELSIF condition3 THEN sequence_of_statements3;ELSE sequence_of_statements4; END IF; - Ключевое слово ELSE может отсутствовать

Операторлардың тізбектелуі (sequence_of_statements) басқа қосылған таңдау операторларын қоса алады.

Мысалы:

BEGIN IF f3 = 'abc' THEN UPDATE tbl1 SET f2 = f2 + 50 WHERE f1 = 1;... ELSE UPDATE tbl1 SET f2 = f2 + 70 WHERE f1 = 1;...... END IF;

Цикл операторлары

Цикл операторы операторлардың бір тізбегін бірнеше рет қайталауға мүмікіндік бере алады. PL/SQL тілі цикл операторының үш формасын жүзеге асыра алады, олар келесі формальды бейнеленулерден тұрады:

- 1 форма – циклдан шығу шығу операторымен көрсетілуі тиіс:

LOOP sequence_of_statements; END LOOP;LOOP sequence_of_statements; EXIT WHEN boolean_expression; - Оператор выхода из циклаEND LOOP;<<label_of_loop>> - Метка циклаLOOP sequence_of_statements;END LOOP label_of_loop; - Конец помеченного цикла

- 2 форма – шын болғанға дейін цикл қайталана береді:

WHILE condition LOOP sequence_of_statements;END LOOP;

- 3 форма – берілген сан бойынша цикл қайталанады:

FOR counter IN [REVERSE] lower_bound..higher_boundLOOP sequence_of_statements;END LOOP;

Циклдан шығу үшін EXIT және EXIT-WHEN операторлары қолданылады, ал PL/SQL блогынан шығу үшін - RETURN операторы қолданылады.

FOR циклі берілген сан бойынша қайталанады, яғги санағыш мәні берілген диапазон арасында жатса, цткл орындала береді. FOR циклінің санағышының мәні цикл орындалғанға дейін тексеріледі. Мәндер диапазоны .. (екі нүкте) символынан кейін беріле алады. REVERSE параметрі айнымалы цикл үшін кері санауды анықтайды.Мәндер диапазоны өрнектермен беріле алады, бірақ цикл ішінде өзгертілмеуі қажет.

Мысалы:

- 1. цикл LOOP:

LOOP FETCH c1 INTO rec1; EXIT WHEN c1%NOTFOUND;END LOOP; - Циклдан шығу, егер жоқ болса - жолдардың көбеюі

- 2. цикл WHILE:

WHILE c1 >= 50 LOOP

- ...

c1:= c1 - 1;END LOOP;

Шартсыз көшу операторлары

GOTO операторы берлілген белгіге шартсыз көшу үшін қолданылады. Бұл оператор ішкі блокқа өту үшін қолданыла алады, бірақ оны енізілген цикл немесе ішкі блок үшін қолдануға болмайды.

Белгі екілік бұрыштық тырнақшаларда көрсетіледі және шартсыз өтуге болатын нүктені анықтау үшін немесе айнымалының атының квалификациясын білу үшін қолданылады.

NULL операторы- бұл функцияның немесе процедураның денесіндегі басып тастаудың рөлін орындау үшін қолданылатын бос оператор немесе алдына белгі көрсететін оператор.

Мысалы:

DECLARE i1 INTEGER;BEGIN

FOR i IN 1..10 LOOP

IF i1=1 THEN

GOTO end_loop;

- Циклдың соңына өту

END IF;

- <<end_loop>> NULL;

- Оператор көрсетеді

- белгіні қолдану

END LOOP; END;