Мы с вами, уже рассмотрели основные концепции определения и работы с курсорами. Но один аспект я упустил. А именно - неявные курсоры. Как вы уже, наверное, догадались, сам оператор SELECT представляет собой в прямом определении "КУРСОР", или еще это называют SQL-курсор! Откуда следует вывод, что любой оператор DML объемлит собой курсор. Так как, каждый оператор DML выполняется в пределах контекстной области и по этому имеет курсор указывающий на контекстную область. В отличии от явных, SQL-курсор не открывается и не закрывается. PL/SQL - сам неявно открывает SQL-курсор, обрабатывает SQL-оператор и закрывает SQL-курсор. Для SQL-курсора операторы FETCH, OPEN, CLOSE не нужны, но с ними можно использовать курсорные атрибуты, вот таким образом:
------ SQL%АТРИБУТ КУРСОРА ---------------
Давайте рассмотрим конкретный пример. В шаге 48 мы закончили работу с табличками PEOPLE и OLD_PEOPLE, если вы их удалили, то ничего страшного, просто вернитесь к шагу 46 и снова их создайте. Если они у вас остались, еще лучше! Итак, рассмотрим такой пример на основе таблички PEOPLE. Пусть нам нужно изменить поле NM в таблице PEOPLE, где поле ID содержит значение 555. Введем такой запрос, перед тем как:
SELECT * FROM PEOPLE /
Получаем (у меня так, у вас может отличаться, если таблица удалялась или вы делали что-то еще):
SQL> SELECT * FROM PEOPLE 2 / ID NM FM OT ---- ------ ---------- --------------- 7 Irvin Show Brefovich 2 Bob Jason Martovich 3 IVAN Black NULL
Но строки, где бы поле ID содержало 555, нет! Это нам и нужно, для того, чтобы продемонстрировать работу атрибута. Итак, запишем блок:
BEGIN UPDATE PEOPLE SET NM = 'Pupkin' WHERE ID = 555; IF(SQL%NOTFOUND) THEN INSERT INTO PEOPLE(ID, NM, FM, OT) VALUES(555, 'Pupkin', 'Axlamon', 'Feodosovich'); END IF; COMMIT; END; /
Получаем:
SQL> BEGIN 2 3 UPDATE PEOPLE 4 SET NM = 'Pupkin' 5 WHERE ID = 555; 6 7 IF(SQL%NOTFOUND) THEN 8 INSERT INTO PEOPLE(ID, NM, FM, OT) 9 VALUES(555, 'Pupkin', 'Axlamon', 'Feodosovich'); 10 END IF; 11 12 COMMIT; 13 14 END; 15 / Процедура PL/SQL успешно завершена.
Посмотрим, что вышло:
SELECT * FROM PEOPLE /
Получаем:
SQL> SELECT * FROM PEOPLE 2 / ID NM FM OT ---- ------ ---------- --------------- 7 Irvin Show Brefovich 2 Bob Jason Martovich 3 IVAN Black NULL 555 Pupkin Axlamon Feodosovich
Как видите, сработал атрибут SQL-курсора, %NOTFOUND. И, так как записи с таким значением поля ID не было, то с помощью оператора INSERT мы его добавили в таблицу PEOPLE! Все получилось верно! То же можно было сделать применив атрибут SQL-курсора, %ROWCOUNT. Вот так:
BEGIN UPDATE PEOPLE SET NM = 'Mirkin' WHERE ID = 888; IF(SQL%ROWCOUNT = 0) THEN INSERT INTO PEOPLE(ID, NM, FM, OT) VALUES(888, 'Mirkin', 'Lupoglaz', 'Kotletovich'); END IF; COMMIT; END; /
Получаем:
SQL> BEGIN 2 3 UPDATE PEOPLE 4 SET NM = 'Mirkin' 5 WHERE ID = 888; 6 7 IF(SQL%ROWCOUNT = 0) THEN 8 INSERT INTO PEOPLE(ID, NM, FM, OT) 9 VALUES(888, 'Mirkin', 'Lupoglaz', 'Kotletovich'); 10 END IF; 11 12 COMMIT; 13 14 END; 15 / Процедура PL/SQL успешно завершена.
Смотрим содержимое таблицы PEOPLE:
SELECT * FROM PEOPLE /
Получаем:
SQL> SELECT * FROM PEOPLE 2 / ID NM FM OT ---- ------ ---------- --------------- 7 Irvin Show Brefovich 2 Bob Jason Martovich 3 IVAN Black NULL 555 Pupkin Axlamon Feodosovich 888 Mirkin Lupoglaz Kotletovich
И здесь все сработало верно, с той разницей, что мы применили атрибут SQL-курсора %ROWCOUNT. Теперь, давайте рассмотрим более сложную ситуацию, с применением атрибута SQL-курсора %NOTFOUND. А начнем, вот с чего. Я уже показывал вам оператор SELECT - формы SELECT ... INTO. Но, не заострял внимание. Давайте немного отвлечемся и я все постараюсь объяснить. Итак, SELECT ... INTO, это как бы некий эквивалент явного курсора с применением оператора выборки FETCH. Но сам по себе он является неявным курсором с выборкой в переменную. Понятно? Если нет, идем дальше. Если явный курсор вида:
CURSOR get_people IS SELECT * FROM PEOPLE; -- Record to store the fetched data -- v_gt get_people%ROWTYPE; . . . FETCH get_people INTO v_gt;
Выбегает данные с помощью оператора FETCH, имеющего конструкцию, INTO в переменную v_gt. В свою очередь, являющейся одномерной коллекцией на основе курсора get_people. То запись вида:
-- Record to store the fetched data -- v_gt PEOPLE%ROWTYPE; SELECT * INTO v_gt FROM PEOPLE;
Абсолютно ей эквивалентна. SELECT ... INTO и есть неявный курсор с выборкой данных в одномерную коллекцию v_gt, так как они обе имеют один и тот же тип определения ROWTYPE и содержат четыре переменных вида:
v_gt.ID, v_gt.NM, v_gt.FM, v_gt.OT
С той лишь разницей, что первая объявлена на основе курсора (поля которого определены собственно выражением SELECT), а вторая на основе полей таблицы! Теперь, я надеюсь, что все неясности по поводу курсоров как явных, так и не явных у вас отпали сами собой. Безусловно, явные курсоры наиболее предпочтительны, так как более наглядны и применимы. Но если вам необходимо выполнить что-то очень простое, то можно использовать SELECT ... INTO. В остальных случаях только явные курсоры! :) Итак, собственно переходим к делу, если применить атрибут %NOTFOUND совместно с конструкцией SELECT ... INTO, то он может не сработать, так как если SELECT ... INTO не получит запись, то возникнет ошибка "ORA-1403 no data found". Для этого мы запишем вот такой блок и разберем его:
SET SERVEROUTPUT ON
DECLARE
m_PLP PEOPLE%ROWTYPE;
BEGIN
DBMS_OUTPUT.enable;
SELECT * INTO m_PLP FROM PEOPLE
WHERE ID = 999;
IF (SQL%NOTFOUND) THEN
INSERT INTO PEOPLE(ID, NM, FM, OT)
VALUES(999, 'Volopasov', 'Lobotryas', 'Oslovich');
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('No Data Found! Execute exception handler');
END;
/
Получаем:
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2
3 m_PLP PEOPLE%ROWTYPE;
4
5 BEGIN
6
7 DBMS_OUTPUT.enable;
8
9 SELECT * INTO m_PLP FROM PEOPLE
10 WHERE ID = 999;
11
12 IF (SQL%NOTFOUND) THEN
13 INSERT INTO PEOPLE(ID, NM, FM, OT)
14 VALUES(999, 'Volopasov', 'Lobotryas', 'Oslovich');
15 END IF;
16
17 COMMIT;
18
19 EXCEPTION
20 WHEN NO_DATA_FOUND THEN
21 DBMS_OUTPUT.put_line('No Data Found! Execute exception handler');
22
23 END;
24 /
No Data Found! Execute exception handler
Процедура PL/SQL успешно завершена.
Как видите, сработал блок EXCEPTION (как с ним управляться - это чуть позже). Что и должно было произойти в нашем случае. Так как блок "SELECT * INTO..." не получил ни одной записи, то сработал блок EXCEPTION с условием NO_DATA_FOUND. Вот так производится обработка атрибутов при использовании неявных курсоров.