Курсорды құру

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

PL/SQL курсорлардың екі типін қстай алады: анықталған және анықталмаған.

Анықталған курсор құрушы арқылы жарияланады, ал анықталмаған курсор жариялануды қажет етпейді.

Курсор бір жолды, бірнеше жолды немесе ешқандай да жолды қайтармауы мүмкін.

Бір жолдан артық қайтаратын сілтемелер үшін тек қана анықталған курсорды пайдалануға болады.

Курсор PL/SQL кез келген блогының, ішкі программаларының немесе пакеттің хабарландыру секцияларында қолданылы алады.

Анықталған курсорға басқару жүргізу үшін CURSOR , OPEN , FETCH және CLOSE операторлары қолданылады.

CURSOR операторы анықталған курсордың жариялануын жүзеге асырады

OPEN операторы курсорды ашады.

FETCH операторы нәтижелік жиынтықтан басынан бастап аяғына дейін тізбектік алып алу жасайды.

CLOSE операторы курсорды жабады және ол қолданатын ресурстардан босатылады.

Анықталған курсорды жариялау үшін CURSOR операторы қолданылады, ол келесі формальды бейнеленулерден тұра алады:

CURSOR cursor_name

[(parameter[,parameter]...)]

[RETURN return_type]

IS select_statement;

Әрбір параметр parameter келесідей анықталады:

cursor_parameter_name [IN]

datatype [{:= | DEFAULT} expr]

return_type параметрі деректер базасының қатарын немес жазбаны анықтайды. Қайтаратын мәндер SELECT операторында тізбектелген қатарларға сәйкес келуі қажет. Параметрлер тізімі курсор параметрлерін анықтайды, ол серверге OPEN операторы әрбір орындалған сайын беріліп отырады.

Нәтижиынтықты құрумен қоса бір мезгілде таңдалынатын жолдарға шектеу қоюға болады. Бұл үшін SELECT операторында FOR UPDATE сөзін көрсету қажет.

Курсор параметрлерін беру үшін позициялық та және аты бар нотация да келе береді.

Курсормен жұмыс істеу үшін келесі атрибуттарды қолдануға болады, олар курсор атынан кейін көрсетіледі:

• %ISOPEN – егер курсор ашық болатын болса, TRUE мәнін қайтарады.
• %FOUND – шартты қанағаттанадыратындай дол табылды ма екендігін анықтайды
• %NOTFOUND – егер дол табылмаған болса, TRUE мәнін қайтарады.
• %ROWCOUNT –сол мезеттегі жолдың нөмерін қайтарады.

FETCH операторы LOOP-END LOOP циклінде орындала алады. Бұл тізбекті түрде OPEN операторымен ашылған барлық нәтижелік жиынтықты қарауға мүмкіндік береді .

Мысалы:

DECLARE

CURSOR c1 IS SELECT f1, f2, f3, f4

FROM tbl1

WHERE f4 > 100;

CURSOR c2 RETURN tbl2%ROWTYPE IS

SELECT * FROM tbl2

WHERE f1_t2 = 10;

- Список параметров

- курсора

CURSOR c3 (p1 INTEGER DEFAULT 10,

p2 INTEGER DEFAULT 1300)

IS SELECT f1, f2, f3, f4

FROM tbl1

WHERE f4 > p1 AND f2 = p2;

- ...

BEGIN

OPEN c1; - c1 курсорының ашылуы

LOOP

- Бір жолды таңдау

FETCH c1 INTO rec1;

- жол жақсы таңдалған

EXIT WHEN c1%NOTFOUND;

END LOOP;

- Курсордың жабылуы

CLOSE c1;

- c3 курсорының ашылуы

OPEN c3(10,700);

- ...

END;

Нәтижелік жиынтықты курсор параметрлерінің басқа мәндері үшін қайта құру барысында ең алдымен жабу керек, сосын қайтадан ашу керек.

SQL-оператордың орындалуы барысында, ол үшін анықталған курсор жарияланбаған болса, Oracle автоматты түрде анықталмаған курсор ашады.

Анықталмаған курсорды пайдалану кезінде OPEN , FETCH және CLOSE басқару операторларын қолдануға болмайды.

Егер анықталмаған курсор кезінде нәтижелік жинаққа бір жолдан артық жазылатын болса, онда TOO_MANY_ROWS шығарып тастауын Oracle инициализациялайды.

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

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

CURSOR cursor_name [(parameter

[, parameter]...)]

RETURN return_type;

мысалы:

- пакетті құру

CREATE PACKAGE p1 AS

- курсорды жариялау

CURSOR c1 RETURN tbl1%ROWTYPE;

END p1;

- пакеттің денесін құру

CREATE PACKAGE BODY p1 AS

- курсор спецификациясы

CURSOR c1 RETURN tbl1%ROWTYPE

IS SELECT * FROM tbl1

WHERE f3 > 700;

END p1;

Курсорды FOR циклінде қолдану

Курсорды OPEN , FETCH және CLOSE операторларымен басқарудың орнына PL/SQL тілі тізбектелген курсорды FOR циклінде қолдануға мүмкіндік береді. Бұл келесі іс әрекеттерді орындайды:

1. Цикл авйнымалысын %ROWTYPE жазбасы ретінде жариялайды.
2. Курсорды ашады.
3. Әрбір итерация кезінде нәтижелік жиынтықтың ішінен келесі жолды жарияланған жазбалар үшін алып отырады.
4. Нәтижелік жиынтықтың аяғаны жеткен кезде курсор жабылады.

Мысалы:

DECLARE

CURSOR c1 IS

SELECT f1, f2 FROM tbl2

WHERE f1 = 10;

BEGIN

- rec1 жариялау

FOR rec1 IN c1 LOOP

/* келесі таңдалған

Кестенің жолы */

INSERT INTO temp_tbl

VALUES (rec1.f2);

END LOOP;

COMMIT;

END;

REF CURSOR типі

Курсор айнымалысын жариялау барысында REF CURSOR типіндегі көрсеткіш құрылады. REF CURSOR типінің айнымалысы RPC (вызовы удаленных процедур) механизмі арқылы клиенттік қосымша мен Oracle сервері арасында беріле алады. Нәтижелік жиынтықта PL/SQL ішкі программалары мен әртүрлі клиенттік қосымшалар арасында сақталатын курсор айнымалысын қолдану әрбір қосымшаны жұмыстық аймаққа бөліп тастайды және ол нәтижелік аймақта орналасқан болып саналады. Бұл бір мезгілде бір жұмыстық аймаққа Oracle Formда, Visual Studioда немесе Delphiде құрылған клиенттік қосымша ретінде сілтеме жасауға көмектеседі.

Айнымалы курсорды құру екі деңгейден тұрады: ең алдымен REF CURSOR типі анықталады, сосын осы типтің айнымалысы жарияланады.

REF CURSOR типін анықтауды PL/SQL кез келген блогында, ішкі программаларда, немесе пакеттерде істеуге болады.

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

TYPE ref_type_name IS REF

CURSOR [ RETURN return_type ];

ref_type_name параметрі құрылатын типтің атын береді, ал return_type параметрі жазбаны анықтау қажет немесе деректер базасындағы кестенің жолын анықтау керек.

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

Курсордың айнымалысы PL/SQL тілінде C++ тілінің көрсеткішімен сай келеді.

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

• Анықталған курсор, жұмыстық аймаққа ат береді;
• Курсордың айнымалысы, осы жұмыстық аймаққа сілтеме жасайды.

Бірақ та анықталған курсор әрқашан бір ғана жұмыстық аймаққа сілтеме жасайды, ал курсор айнымалысы әртүрлі жұмыстық аймақтарға сілтеме жасала алады.

Жұмыстық аймақ кем дегенде бір курсор айнымалысы сілтеме жасағанға дейін қол жетімді болып тұрады

Айнымалы курсоры процедура мен функциялар үшін формальды параметр ретінде қолданыла алады.

Курсор айнымалысын басқару үшін OPEN-FOR, FETCH және CLOSE операторлары қолданылады.

OPEN-FOR операторы курсор айнымалысын сілтемемен байланыстырады, сұранысты орындайды және нәтижелік жиынтықты алады.

OPEN-FOR операторы келесі формальды бейнеленулерден тұра алады:

OPEN {cursor_variable_name |

:host_cursor_variable_name}

FOR select_statement;

Параметр айнымалы курсорға host_cursor_variable_name арқылы көрсетеді, ол негізгі тілдің айнымалысы ретінде жарияланған. Хост- айнымалыға сілтеме жасау үшін оның алдынан қос нүкте символы қойылады.

Переменная курсора, в отличие от самого курсора, не может иметь параметров.

Запрос, указываемый для переменной курсора, может использовать:

• хост-айнымалылар;
• PL/SQL айнымалылары;
• параметрлер;
• функциялар.

Курсор айнымалысына жасалынатын сұраныс FOR UPDATE сөзінен тұра алмайды.

Курсор айнымалысы үшін %FOUND , %NOTFOUND , %ISOPEN және %ROWCOUNT операторларын қолдануға болады.

Мысалы:

DECLARE

TYPE VarCur IS REF CURSOR

RETURN tbl1%ROWTYPE;

- Объявление переменной курсора

t1 VarCur;

BEGIN

IF NOT t1%ISOPEN THEN

- Открываем

- переменную курсора

OPEN t1 FOR SELECT *

FROM tbl1;

OPEN t1 FOR SELECT *

FROM tbl1

- Повторно открываем

- переменную курсора

WHERE f2>100;

END IF;

Бұрын ашылған курсор айнымалысын қайтадан ашу керек болатын болса, онда аның алдындағы сұраныс жоғалады, ал айнымалы жаңа сұраныспен бірге болады.

Егер ашылып тұрған курсорды қайта ашуға талпынып көретін болса, онда Oracle CURSOR_ALREADY_OPEN шығарып тастауын инициализациялайды.

Егер курсор айнымалысы ішкі программаның формальды параметрі ретінде жарияланатын болса, мұндай параметр IN OUT лпциясымен болуы қажет.

Келесі мысал аегізні программалау тіліндегі хост айнымалы ретінде қолдануын көрсетеді.

/* Объявление хост-переменных */

EXEC SQL BEGIN DECLARE

/* Объявление переменной курсоров */

SQL_CURSOR cur1;

EXEC SQL END DECLARE SECTION;

/* Инициализация хост-переменной */

EXEC SQL ALLOCATE : cur1;

EXEC SQL EXECUTE

/* Передача хост-переменной

курсора в блок PL/SQL */

BEGIN

OPEN :cur1 FOR SELECT *

FROM emp;

- :

OPEN :cur1 FOR SELECT *

FROM temp_emp;

END;

END-EXEC;

Курсор айнымалысының қолданылуы келесі шектеулерден тұрады:

• Курсор айнымалысы пакетте жариялана алмайды;
• әртүрлі серверлер арасында курсорлар айнымалысын жіберу үшін RPC қолдануға болмайды;
• алыстағы ішкі программа басқа сервердегі курсор айнымалысынан мәндерді қабылдай алмайды;
• курсор айнымалысы үшін OPEN-FOR операторымен жасалған сұраныста FOR UPDATEсөзі болмауы қажет;
• Курсор айнымалылары үшін теңдік операторлары, салыстыру немесе NULL мәні бойынша эквиваленттеу қолданылмайды;
• Курсор айнымалысына NULL мәні меншіктелмейді;
• REF CURSOR типтері CREATE TABLE и CREATE VIEW SQL-операторларында қатарлардың типтерін анықтау үшін, сонымен қатар коллекция элементтерінің типтерін анықтау үшін қолданыла алмайды;
• Курсор айнымалылары динамикалық SQLде қолданыла алмайды;
• Курсор айнымалысын FOR-IN-LOOP циклінде курсордың орнына қолдануға болмайды.

Нәтижелік жиынтықтан жолды таңдау FETCH операторымен таңдалады, ол келесі формальды бейнеленулерден тұра алады:

FETCH {cursor_variable_name |

:host_cursor_variable_name}

INTO {variable_name

[, variable_name]... |

record_name};

Мысалы:

DECLARE

TYPE VarCur IS REF CURSOR

RETURN tbl1%ROWTYPE;

- Объявление переменной курсора

t1 VarCur;

tbl1_rec tbl1%ROWTYPE;

BEGIN

IF NOT t1%ISOPEN THEN

- Открываем

- переменную курсора

OPEN t1 FOR SELECT * FROM tbl1;

LOOP

- Извлечение строки

FETCH t1 INTO tbl1_rec;

- Проверка атрибута

EXIT WHEN t1%NOTFOUND;

END LOOP;

END IF;

CLOSE t1;