10. Динамикалық SQL операторларын құру

Динамикалық SQL операторлары – икемделген SQL операторларымен салыстырғанда олар компиляциялау деңгейінде құрылады. Динамикалық SQL ODBC API мен немесе SQL-99 стандарты деңгейіне сәйкес келетін SQL/CLI маңайында қолданылады

Динамикалық SQL қолдауы бастапқы кезеңде SQL-92 стандартына сәйкес келуі шартты емес

Динамикалық SQL операторлары тексттік айнымалылар ретінде құрылады.

Мысалы:

Stmt1:='SELECT * FROM tbl1';

Оператордың динамикалық құрылуы үшін тізбектелген жолдар бірігуін орындауға болады.

Динамикалық SQL операторларын келесідей қолдануға болады:

  • Бір реттік, бір қадамнан кейін компиляция жасау және оператордың орындалуы. Осындай қолдануды бір қадамды интерфейс деп атаймыз;
  • Көп реттік, оператор компилияциясын процесстерге бөлу арқылы, бұл процесстерде орындалу жоспары құрылады және оператордың орындалуы қарастырылады. Мұндай қолдануды көп қадамдық интерфейс деп атаймыз.

Бір қадамдық интерфейс

Бір қадамдық интерфейс EXECUTE IMMEDIATE SQL-операторымен жүзеге асырылады. Ол SQL-92 стандартында келесі формальды бейнелемелерді қолданады:

EXECUTE IMMEDIATE :variable;

Айнымалыға көрсететін операторға келесідей шектеулер қойылады:

  • оператор INTO- айнымалыларды қолдана алмайды;
  • оператор айнымалы байланыстарды қолдана алмайды.

Келесі мысал динамикалық SQL операторын бір қадамдық интерфейсте қалай қолдануға болатындығы жайында көрсетеді:

stmt_str := 'INSERT INTO ' || table_name ||

' values (:f1, :f2, :f3)';

EXEC SQL EXECUTE IMMEDIATE :stmt_str;

Көп қадамды интерфейс

EXECUTE IMMEDIATE операторы бір реттік орындау үшін ыңғайлы, бірақ бір реттк емес орындаулар үшін қолданылады. Мысалы, бір оператордың циклінде, бірақ параметрлері әртүрлі болған жағдайда PREPARE және EXECUTE арқылы жүзеге асырылған көп қадамды интерфейс қолданған ыңғайлы.

PREPARE операторын орындау барысында оның көрсеткен SQL-операторы ДББЖ беріледі. Сосын оператордың синтаксистік талдауы жүргізіледі және орындау жоспары құрылады. Осыдан кейін EXECUTE операторы орындалған кезде "қайта компилицияланған" SQL-операторы қолданылады, ол оның өнімділігін арттырады. Қосымша EXECUTE опетаорыныңорындалуы кезінде серверге WHERE сөйлемі предикатын есептеуге қажетті байланыс айнымалылары беріледі.

PREPARE операторы SQL-92 стандартында келесі формальды бейнелемелерді қолданады:

PREPARE [ GLOBAL | LOCAL ] operator_sql FROM string_variable;

operator_sql параметрі SQL-операторының идентификаторын анықтайды, ол EXECUTE операторында орындауды көрсетеді немесе ALLOCATE CURSOR не DECLARE CURSOR операторларының курсорда жануы себеп болады.

string_variable параметрі SQL-операторының динамикалық құрылған текст жолына сілтеме жасайды.

Мысалы:

stmt_str := 'INSERT INTO ' || table_name ||

' values (:f1, :f2, :f3)';

EXEC SQL PREPARE GLOBAL stmt1 FROM :stmt_str;

GLOBAL және LOCAL сөздері оператордың көріну аймағын анықтайды: GLOBAL жұмыс істеп тұрған ДББЖ сеансында берілген оператор барлық жұмысқа қол жетімділікке ие дегенді білдіреді, ал LOCAL орындалушы модуль маңайында қолжетімділікке шектеу қояды.

Егер бірдей атпен екі оператор құрылатын болса, бірақ оның бірі GLOBAL, ал екіншісі LOCAL болса, онда ДББЖ екі жеке орындалу жоспарын екі түрлі операторға құрғандай етіп құрады. Қарама қарсы жағдайда оператордың компилияциялануы барысында тек оператордың орындалуының жаңа жоспары құрылады.

Дайындалып қойған SQL-операторды босату үшін DEALLOCATE PREPARE операторы қолданылады, ол дайындалып қойылған SQL-оператордың барлық ресурстарын босатыды.

Мысалы:

EXEC SQL DEALLOCATE PREPARE GLOBAL stmt1;

Қайта компиляцияланған SQL-оператордың орындалуы үшін EXECUTE операторын қолданады. Ол SQL-92 стандартында келесі формальды бейнелемелері бар:

EXECUTE [ GLOBAL | LOCAL ] operator_sql

[ INTO {variable .,:}

| { SQL DESCRIPTOR [ GLOBAL | LOCAL ]

descriptor_name } ]

[ USING {variable .,:}

| { SQL DESCRIPTOR [ GLOBAL | LOCAL ]

descriptor_name } ]

INTO сөзі орындалатын SQL-оператор бір жолды қайтаратын сұранысты қолданған.

Динамикалық параметрлер

Динамикалық параметрлердің мәні серверге қайта компиляцияланған SQL-оператор қолданылған сайын беріледі. Динамикалық параметр ретінде байланыс айнымалылары да, сонымен қатар, INTO- айнымалылары да қолданыла алады.

Динамикалық параметрлерді енгізілген SQL де, сонымен қатар динамикалық SQL де де қолдануға болады.

Динамикалық параметрлер SQL-операторы текстінде "сұрақ белгісі" ретінде беріледі. Стандартта динамикалық параметрлердің болу мүмкін максималды саны анықталмаған. Ереже бойынша, ДББЖ енгізілетін SQL-оператор көлеміне ғана шектеу қоя алады.

Мысалы:

stmt_str :='INSERT INTO tbl1

VALUES (?, ?, ?)';

EXEC SQL PREPARE stmt2 FROM :stmt_str;

Осы берілген қайта компиляцияланған оператордың динамикалық параметрлер орнына қолданылған кезде берілген SQL-оператордағы EXECUTE немесе SQL-дескриптор аймағында берілген рет бойынша қолданылады.

Динамикалық параметрлер үшін арналған мәндер тізімі келесідей:

  • EXECUTE операторының USING сөзі - қайта компиляцияланған оператордың INTO сөйлеміне көрсетпейтін динамикалық параметрлер үшін;
  • EXECUTE операторының INTO сөзі - қайта компиляцияланған оператордың INTO сөйлемінде көрсетілетін динамикалық параметрлер үшін;

Мысалы:

stmt_str1 :='INSERT INTO tbl1 (f1,f2,f3)

VALUES (?, ?, ?)';

EXEC SQL PREPARE stmt2 FROM :stmt_str1;

EXEC SQL EXECUTE stmt2 USING :f1, :f2, :f3;

Негізгі программалау тілінің f1, f2 және f3 айнымалыларының мәндері қайта компиляцияланған оператордың stmt2 идентификаторы арқылы серверге беріледі.

Сонымен қатар қайта компиляцияланған оператор динамикалық параметрден тұру мүмкіндігі берілген және ол SELECT операторының INTO сөзінде, предикатта да бола алады.

Мысалы:

stmt_str2 :='SELECT f1, f2, f3

FROM tbl1 INTO ?, ?, ?

WHERE f2= ?';

EXEC SQL PREPARE stmt3 FROM :stmt_str2;

EXEC SQL EXECUTE stmt3 INTO :f1, :f2, :f3

USING :f4;

Негізгі тілдің f1, f2 және f3 айнымалылары INTO- айнымалылар секілді қолданылады, ал f4 айнымалысының мәні қайта компиляцияланған оператордың stmt3 идентификаторы арқылы орындалу үшін серверге беріледі.

SQL-дескрипторлар

SQL-дескриптор – компиляцияланған SQL-оператордың параметрлері жайындағы ақпаратты сақтау үшін ДББЖ уақытша құрылған аймақ.

SQL-дескриптор USING сөзінде параметрлерді бейнелеу үшін, сонымен қатар, EXECUTE операторының INTO сөзінде бейнелеу үшін де қолданылады.

Әрбір компиляцияланған SQL-оператор үшін SQL-дескриптора дың өз аймағы құрылады.

SQL-дескрипторлар келесі мақсаттар үшін қолданыла алады:

  • SQL-оператордың орындалуы барысындағы параметрлерді анықтау үшін;
  • INTO-айнымалылары жайындағы ақпараттарды алу үшін.;
  • Динамикалық курсорларда қолдану үшін.

SQL-дескриптор элементтерден тұрады. Әрбір динамикалық параметр элементпен бейнеленеді. Олардың әрқайсысы аймақ тізімінен тұратын құрылымнан құралады. Кейде олардың кейбірелері болмауы мүмкін немесе қолданылмайды (элементтің типіне немесе реализациялауға байланысты). SQL-дескриптор элементінің шынымен бар аймақтардағы бейнеленуін көрсетейік

Аймақ аты

Бейнеленуі

TYPE

Параметр типін анықтайтын бүтін мән.

Бұл аймақ келесі мәндерді қабылдай алады:

CHARACTER - 1

DECIMAL – 3

INTEGER – 4

SMALLINT – 5

FLOAT – 6

REAL – 7

DOUBLE PRECISION - 8

DATE – 9

TIME – 9

TIMESTAMP - 9

INTERVAL - 10

CHARACTER VARYING - 12

BIT – 14

BIT VARYING - 15

Мәнге байланысты TYPE аймағы PRECISION және SCALE аймақтарында қолданыла алады.

NAME

Берілген элементке сәйкес келетін параметр идентификаторы. Бұл мән болмауы да мүмкін. Мысалы, есептелінетін қатарлар үшін. Мұндай жағдайда UNNAMED аймағы қолданылады.

UNNAMED

Егер NAME аймағында параметр идентификаторы көрсетілмеген болса, онда берілген аймақтың мәні 1; егер параметр идентификаторы бар болса, онда мәні 0.

DATA

Параметр мәнінен тұрады.

NULLABLE

Параметрдәі NULL мәнін қабылдай алады ма екендігін анықтайды: 0 - мүмкін, 1 – мүмікін емес

INDICATOR

Индикаторлық айнымалыға сәйкес қолданылады.

SQL-дескриптормен жұмыс істеу келесі деңгейлерден тұрады:

  1. SQL-дескрипторды ALLOCATE DESCRIPTOR операторы арқылы құру;
  2. SQL-дескриптор мәндері үшін берілген міндеттер орындала алады, егер:
    • SET DESCRIPTOR операторының орындалуы кезінде;
    • DESCRIBE операторын мәндерді автоматты түрде орнату үшін қолданған кезде;
  3. Динамикалық параметрлердің мәндерін қолдану:
    • Параметр мәндерін GET DESCRIPTOR операторы орындалуы кезінде алу;
    • Параметр мәндерін EXECUTE оператооры орындалу барысында қолдану;
  4. SQL-дескрипторды DEALLOCATE DESCRIPTOR операторы арқылы босатып алу.

SQL-дескриптормен жұмыс жасаудың барлық деңгейлерін қарастырайық.

SQL-дескрипторды құру ALLOCATE DESCRIPTOR операторы арқылы жүзеге асырылады. Ол оператордың SQL-92 стандартында келесі бейнелемелері бар:

ALLOCATE DESCRIPTOR descriptor_name

[ WITH MAX count_of_inctances ];

Бұл оператор SQL-дескриптор аймаған құрады. Ол динамикалық параметрлер жайындағы ақпаратты сақтау үшін қажет.

WITH MAX сөзі SQL-дескриптор элементтері үшін максималды бола алатын санды орнату үшін қолданылады.

Мысалы:

EXEC SQL ALLOCATE DESCRIPTOR descr1

WITH MAX 4;

Бұл оператор descr1 SQL-дескриптор құрады, ол компиляцияланытын оператор үшін қолданыла алады. Онда динамикалық параметр төрттен артық болмайды.

SQL-дескриптор инициализациялау келесі SQL-операторлардың көмегімен орындалады:

  • DESCRIBE ;
  • SET DESCRIPTOR .

Бастапқы инициализацияны DESCRIBE операторының қатысуымен істеуге болады, бұл өте жеңіл жолдың бірі болып табылады. Осыдан кейін қажетті аймақтардағаы өзгертулер SET DESCRIPTOR операторының көмегі арқылы жүзеге асады.

DESCRIBE операторы SQL-92 стандартында келесі формальды бейнелемелері бар:

DESCRIBE [ INPUT | OUTPUT ] operator_sql

USING SQL DESCRIPTOR descriptor_name;

Бұл операор қосымша құрылған SQL-дескрипторда SQL-оператор арқылы компиляцияланған динамикалық параметрлер жайындағы ақпаратты береді.

NPUT сөзі динамикалық айнымалы байланыстар үшін SQL-дескриптор арқылы инициялизацияланады дегенді білдіреді.

OUTPUT сөзі динамикалық INTO- айнымалылар үшін SQL-дескриптор арқылы инициялизацияланады. Берілгені бойынша OUTPUT опциясы қолданылады.

DESCRIBE операторының орындалуы барысында әрбір аймақ элементтеріне TYPE, NAME, UNNAMED және NULLABLE, сонымен қатар, нақты деректер нақтылығын, сонымен қатар, белгілі нақты мәліметке тура қатысы бар тіркейді. (LENGTH, PRECISION және т.б.).

Мысалы:

stmt1 :='SELECT f1, f2 FROM tbl1 INTO ?, ?

WHERE f2= 1';

EXEC SQL ALLOCATE DESCRIPTOR descr1 WITH MAX 2;

EXEC SQL DESCRIBE OUTPUT :stmt1

USING SQL DESCRIPTOR descr1;

SQL-дескриптор descr1дің инициализациялау нәтижесінде екі элемент құрылады және олардың аймақ мәндері NAME f1 және f2 бола алады.

Динамикалық айнымалылар үшін SQL-дескриптор DESCRIBE операторымен инициализациялана алатын болса да, бірақ осы айнымалылардың мәндері DATA аймақтарында сақталған болса, онда DESCRIBE операторын орнату мүмкін емес. Бұл мақсат үшін SET DESCRIPTOR операторын қолдануға болады. Ол SQL-92 стандартында келесі формальды бейнелемелерге ие:

SET DESCRIPTOR

[ GLOBAL | LOCAL ] descriptor_name

{ COUNT = integer_value }

| { VALUE number_of_element

field_of_element = value} .,:};

Бұл оператор келесі қызметтердің бірін орындай алады:

  • SQL-дескриптор арқылы берілген элементтер санын ауыстыру, ол үшін COUNTсөзі қолданылады;
  • Нақты бір элементтің мәнін өзгерту, оның нөмерін көрсететін (number_of_element).

NAME, RETURNED_LENGHT, NULLABLE, RETURNED_OCTET_LENGHT, COLLATION_CATALOG, COLLATION_SCHEMA и COLLATION_NAME аймақтары мәндерін SET DESCRIPTOR операторы көмегімен өзгертуге болмайды.

Мысалы:

stmt1 :='SELECT f1, f2, f3

FROM tbl1 INTO ?, ?, ?

WHERE f2= 1';

EXEC SQL ALLOCATE DESCRIPTOR descr1

WITH MAX 2;

EXEC SQL DESCRIBE OUTPUT :stmt1

USING SQL DESCRIPTOR descr1;

EXEC SQL SET DESCRIPTOR descr1

VALUE 3 TYPE=1, LENGTH=15;

Осылайша, үшінші динамикалық параметр CHARACTER типіне тиесілі болады және символдарының ұзындығы 15 ке тең.

Егер компиляцияланған оператор үшін SQL-дескриптор құру кезінде DESCRIBE операторы қолданылмайтын болса, онда орнатудан бұрін әрбір лементтің аймақ мәніне дескриптор элементтерінің санын беру қажет. ( COUNT сөзі). Кейде бұл үшін компиляцияланған SQL-оператордың синтаксистік талдауын жасау қажет болады. Ал одан құтылудың жолы DESCRIBE операторын қолдану болып табылады.

SQL-дескриптор кез келген аймақтарының мәні GET DESCRIPTOR операторымен анықталады, ол SQL-92 стандартында келесі формальды бейнелеулерге ие:

GET DESCRIPTOR

[ GLOBAL | LOCAL ] descriptor_name

{ integer_variable= COUNT }

| { VALUE number_of_element

variable = field_ of_element } .,:};

Мысалы:

EXEC SQL SET DESCRIPTOR descr1

VALUE 3 TYPE=1, LENGTH=15;

EXEC SQL GET DESCRIPTOR descr1

VALUE 3 :var_type = TYPE,

:var_lenght = LENGTH;

Мұндай SQL-оператордың орындалуы барысында var_type айнымалысына 1 мәні енгізіледі, ал var_lenght айнымалысына - 15 мәні.

SQL-дескриптордың DATA аймағының мәндерін EXECUTE операторы орындалғаннан кейін барып қана алауға болады.

Мысалы:

str1:='SELECT f3 FROM tbl1 INTO ?

WHERE f2 = 1';

EXEC SQL PREPARE stmt1 FROM :str1;

EXEC SQL ALLOCATE DESCRIPTOR descr1

WITH MAX 1;

EXEC SQL DESCRIBE OUTPUT stmt1

USING SQL DESCRIPTOR descr1;

EXEC SQL EXECUTE stmt1 INTO

SQL DESCRIPTOR descr1;

GET DESCRIPTOR descr1

VALUE 1 :f1=DATA :fnull=NULLABLE;

Динамикалық курсорлар

Динамикалық SQLда өзіндік SQL курсорын (DECLARE CURSOR операторымен құрылатын) ғана емес, сонымен қатар қосымша тағы да екі курсор қолдануға болады:

  • Жариялайтын курсорлар (declared cursors), DECLARE CURSOR ретінде құрылады;
  • Тарататын курсорлар (allocated cursors), ALLOCATE CURSOR ретінде қрылады. Бұл курсор типін кейде ерекшеленген курсор деп те атайды.

Жариялайтын және тарататын курсорлардың динамикалық параметрлері бола алады.

Бұл курсорлардықұру үшін келесі операторлар қолданылады:

  • ALLOCATE CURSOR , SQL-оператормен бейнеленетін айнымалының идентификаооры ретінде көрсетіледі;
  • DECLARE CURSOR , компиляцияланаған SQL-оператор идентификаторы ретінде көрсетіледі.

Мысалы:

str1:='INSERT INTO tbl1 VALUES (1,10) ';

EXEC SQL ALLOCATE cur1 CURSOR FOR :str1;

EXEC SQL PREPARE stmt1 FROM :str1;

EXEC SQL DECLARE cur2 CURSOR FOR stmt1;

Динамикалық курсорлары OPEN және CLOSE операторларымен статикалық құрылатын секілді ашып жабылады. Бірақ динамикалық параметрі бар курсорларды ашу барысында USING сөзі көрсетілуі керек.

Мысалы:

str1:='SELECT f2 FROM tbl1 WHERE f1=? ';

EXEC SQL ALLOCATE cur1 CURSOR FOR :str1;

EXEC SQL OPEN cur1 USING :f2;

EXEC SQL FETCH cur1 INTO :f1;

FETCH операторының INTO сөзінде INTO-айнымалысының тізімі, сонымен қатар SQL-дескриптор да көрмсетілуі мүмкін.