www.ПЕРВЫЕ ШАГИ.ru :: Шаг 54 - PL/SQL - КУРСОР - извлекаем данные, оператор FETCH

С определениями, открытиями и закрытиями мы разобрались. Давайте, наконец, получим какие-нибудь результаты, т.е. данные. Собственно для чего и нужны курсоры. Выборка данных из курсора производится с помощью оператора FETCH. Пожалуй, он является ключевой фигурой в данном случае. Так как именно с его помощью происходит выборка из активного набора, сформированного при открытии курсора. После того как курсор открыт, данные в контекстной области SGA уже готовы, их остается только извлечь. Оператор FETCH именно это и производит. Синтаксис его таков:

---------- FETCH - имя курсора - INTO - список переменных --------------
и
---------- FETCH - имя курсора - INTO - запись PL/SQL (%ROWTYPE) --------

Итак, переходим к практическим действиям. Запускаем наш старый добрый SQL*Plus. Запишем вот такой блок, используя пройденный материал:

DECLARE
	
	v_Office OFFICES.OFFICE%TYPE;
	v_City OFFICES.CITY%TYPE;
	
	CURSOR get_offices IS
		SELECT OFFICE, CITY 
		FROM OFFICES;

BEGIN

	OPEN get_offices; 
		-- Use operator FETCH to get variables!
		FETCH get_offices INTO v_Office, v_City;	
	
	CLOSE get_offices; 
	
END;
/

Получаем следующее:

SQL> DECLARE
  2  
  3   v_Office OFFICES.OFFICE%TYPE;
  4   v_City OFFICES.CITY%TYPE;
  5  
  6   CURSOR get_offices IS
  7       SELECT OFFICE, CITY
  8       FROM OFFICES;
  9  
 10  BEGIN
 11  
 12   OPEN get_offices;
 13    -- Use operator FETCH to get variables!
 14    FETCH get_offices INTO v_Office, v_City;
 15  
 16   CLOSE get_offices;
 17  
 18  END;
 19  /

Процедура PL/SQL успешно завершена.

Что ж, налицо правильная выборка, в две переменные первой строки активного набора. Но, как то не наглядно, не видно результата. Давайте немного перепишем наш предыдущий блок вот так:

SET SERVEROUTPUT ON

DECLARE
	
	v_Office OFFICES.OFFICE%TYPE;
	v_City OFFICES.CITY%TYPE;
	
	CURSOR get_offices IS
		SELECT OFFICE, CITY 
		FROM OFFICES;

BEGIN

	OPEN get_offices; 
		-- Use operator FETCH to get variables!
		FETCH get_offices INTO v_Office, v_City;	
		
		DBMS_OUTPUT.enable;
		DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
	CLOSE get_offices; 
	
END;
/

Получаем следующее:

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  2  
  3	v_Office OFFICES.OFFICE%TYPE;
  4	v_City OFFICES.CITY%TYPE;
  5  
  6	CURSOR get_offices IS
  7		SELECT OFFICE, CITY
  8		FROM OFFICES;
  9  
 10	BEGIN
 11  
 12	OPEN get_offices;
 13		-- Use operator FETCH to get variables!
 14		FETCH get_offices INTO v_Office, v_City;
 15  
 16		DBMS_OUTPUT.enable;
 17		DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
 18  
 19	CLOSE get_offices;
 20  
 21	END;
 22	/
OutPutString is: 22 Запиндрищинск                                               

Процедура PL/SQL успешно завершена.

Ура! Наконец-то мы видим результат! Давайте подробнее рассмотрим, что же произошло. Поле открытия курсора (не параметризованного) происходит выборка данных, согласно выражения SELECT курсора. Кстати легко проверить, что должен вернуть курсор просто выполнив его оператор SELECT, вот так:

SELECT OFFICE, CITY 
	FROM OFFICES
/

Получаем:

SQL> SELECT OFFICE, CITY
  2     FROM OFFICES
  3  /
  
 OFFICE CITY
------- -------------------
     22 Запиндрищинск
     11 Красный Мотоцикл
     12 Чугуевск
     13 Бубурино
     21 Котрогайка

5 строк выбрано

Это и есть ваш результирующий набор! Но здесь пять(!) строк скажете вы! Да! В таблице OFFICES пять записей, как вы помните из наших прошлых занятий. Но пример с FETCH вернул одну строку, потому что этот оператор производит выбор одной строки и смещает указатель в контекстной области на единицу. И ждет следующей команды на выборку. До тех пор, пока не будет достигнута последняя запись. Когда будет достигнута последняя запись, сработает атрибут курсора %NOTFOUND (его мы рассмотрим чуть позднее), он станет TRUE. Это значит, что все записи из результирующего набора выбраны! Но с помощью нашего примера можно выбрать и все пять записей, сделав вот так:

SET SERVEROUTPUT ON

DECLARE

	v_Office OFFICES.OFFICE%TYPE;
	v_City OFFICES.CITY%TYPE;
	
	CURSOR get_offices IS
		SELECT OFFICE, CITY 
		FROM OFFICES;

BEGIN

	OPEN get_offices; 

		DBMS_OUTPUT.enable;
		-- Use operator FETCH to get variables!
		FETCH get_offices INTO v_Office, v_City;	-- 1
		DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
		FETCH get_offices INTO v_Office, v_City;	-- 2
		DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
		FETCH get_offices INTO v_Office, v_City;	-- 3
		DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
		FETCH get_offices INTO v_Office, v_City;	-- 4
		DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
		FETCH get_offices INTO v_Office, v_City;	-- 5
		DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);

	CLOSE get_offices; 

END;
/

Получаем:

SQL> SET SERVEROUTPUT ON
SQL> 
SQL> DECLARE
  2  
  3   v_Office OFFICES.OFFICE%TYPE;
  4   v_City OFFICES.CITY%TYPE;
  5  
  6   CURSOR get_offices IS
  7       SELECT OFFICE, CITY
  8       FROM OFFICES;
  9  
 10  BEGIN
 11  
 12   OPEN get_offices;
 13  
 14    DBMS_OUTPUT.enable;
 15    -- Use operator FETCH to get variables!
 16    FETCH get_offices INTO v_Office, v_City; -- 1
 17    DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
 18    FETCH get_offices INTO v_Office, v_City; -- 2
 19    DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
 20    FETCH get_offices INTO v_Office, v_City; -- 3
 21    DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
 22    FETCH get_offices INTO v_Office, v_City; -- 4
 23    DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
 24    FETCH get_offices INTO v_Office, v_City; -- 5
 25    DBMS_OUTPUT.put_line('OutPutString is: '||TO_CHAR(v_Office)||' '||v_City);
 26  
 27   CLOSE get_offices;
 28  
 29  END;
 30  /
OutPutString is: 22 Запиндрищинск                                               
OutPutString is: 11 Красный Мотоцикл                                            
OutPutString is: 12 Чугуевск                                                    
OutPutString is: 13 Бубурино                                                    
OutPutString is: 21 Котрогайка                                                  

Процедура PL/SQL успешно завершена.

Вот все пять записей, каждый последующий FETCH вернул по одной записи! Теперь понятно? Думаю, да! И если вы уже, наверное, догадались, что здесь просто напрашивается оператор цикла! Давайте для начала (просто для примера) применим, оператор LOOP EXIT WHEN и при этом немного изменим наш курсор, применив атрибут %ROWTYPE вот так:

SET SERVEROUTPUT ON

DECLARE
	
	CURSOR get_offices IS
		SELECT * FROM OFFICES;

	v_gt get_offices%ROWTYPE;

BEGIN

	OPEN get_offices;
		
	LOOP 

		EXIT WHEN get_offices%NOTFOUND;

		DBMS_OUTPUT.enable;
		-- Use operator FETCH to get variables!
		FETCH get_offices INTO v_gt;	
		DBMS_OUTPUT.put_line('Get Data: '||TO_CHAR(v_gt.OFFICE)||' '||v_gt.CITY||' '
		||v_gt.REGION||' '||TO_CHAR(v_gt.MGR)||' '||TO_CHAR(v_gt.TARGET)||' '||TO_CHAR(v_gt.SALES));
	
	END LOOP;

	CLOSE get_offices; 

END;
/

И наконец получаем:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2  
  3   CURSOR get_offices IS
  4       SELECT * FROM OFFICES;
  5  
  6   v_gt get_offices%ROWTYPE;
  7  
  8  BEGIN
  9  
 10   OPEN get_offices;
 11  
 12   LOOP
 13  
 14    EXIT WHEN get_offices%NOTFOUND;
 15  
 16    DBMS_OUTPUT.enable;
 17    -- Use operator FETCH to get variables!
 18    FETCH get_offices INTO v_gt;
 19    DBMS_OUTPUT.put_line('Get Data: '||TO_CHAR(v_gt.OFFICE)||' '||v_gt.CITY||' '
 20    ||v_gt.REGION||' '||TO_CHAR(v_gt.MGR)||' '||TO_CHAR(v_gt.TARGET)||' '||TO_CHAR(v_gt.SALES));
 21  
 22   END LOOP;
 23  
 24   CLOSE get_offices;
 25  
 26  END;
 27  /
Get Data: 22 Запиндрищинск Запад 108 300 186,042                                
Get Data: 11 Красный Мотоцикл Восток 106 575 692,637                            
Get Data: 12 Чугуевск Восток 104 800 735,044                                    
Get Data: 13 Бубурино Восток 105 350 367,911                                    
Get Data: 21 Котрогайка Запад 108 725 835,915                                   
Get Data: 21 Котрогайка Запад 108 725 835,915                                   

Процедура PL/SQL успешно завершена.

Ура! Я вас поздравляю, мы наконец чуть забежав вперед, с применением оператора цикла и курсорного атрибута %NOTFOUND, наконец написали наш первый полноценный курсор! Который выбирает все данные из таблицы и выводит их на экран! Но, не думайте, что это все что касается курсоров! Это далеко не так! :)