PL / SQL — курсоры

В этой главе мы обсудим курсоры в PL / SQL. Oracle создает область памяти, известную как область контекста, для обработки оператора SQL, которая содержит всю информацию, необходимую для обработки оператора; например, количество обработанных строк и т. д.

Курсор — это указатель на эту контекстную область. PL / SQL управляет контекстной областью с помощью курсора. Курсор содержит строки (одну или несколько), возвращаемые оператором SQL. Набор строк, которые содержит курсор, называется активным набором .

Вы можете назвать курсор так, чтобы на него можно было ссылаться в программе для выборки и обработки строк, возвращаемых оператором SQL, по одной за раз. Есть два типа курсоров —

• Неявные курсоры
• Явные курсоры

Неявные курсоры

Неявные курсоры автоматически создаются Oracle при каждом выполнении оператора SQL, когда для оператора нет явного курсора. Программисты не могут контролировать неявные курсоры и информацию в них.

Всякий раз, когда выполняется оператор DML (INSERT, UPDATE и DELETE), с этим оператором связывается неявный курсор. Для операций INSERT курсор содержит данные, которые необходимо вставить. Для операций UPDATE и DELETE курсор определяет строки, которые будут затронуты.

В PL / SQL вы можете ссылаться на самый последний неявный курсор как на курсор SQL , который всегда имеет атрибуты, такие как % FOUND,% ISOPEN,% NOTFOUND и % ROWCOUNT . Курсор SQL имеет дополнительные атрибуты % BULK_ROWCOUNT и % BULK_EXCEPTIONS , предназначенные для использования с оператором FORALL . В следующей таблице приведено описание наиболее часто используемых атрибутов.

%НАЙДЕННЫЙ

Возвращает TRUE, если инструкция INSERT, UPDATE или DELETE затронула одну или несколько строк или инструкция SELECT INTO вернула одну или несколько строк. В противном случае он возвращает FALSE.

%НЕ НАЙДЕНО

Логическая противоположность% FOUND. Он возвращает TRUE, если инструкция INSERT, UPDATE или DELETE не затронула строки, или инструкция SELECT INTO не вернула строки. В противном случае он возвращает FALSE.

%ОТКРЫТ

Всегда возвращает FALSE для неявных курсоров, потому что Oracle автоматически закрывает курсор SQL после выполнения соответствующего оператора SQL.

% ROWCOUNT

Возвращает количество строк, затронутых оператором INSERT, UPDATE или DELETE или возвращенных оператором SELECT INTO.

Любой атрибут курсора SQL будет доступен как sql% attribute_name, как показано ниже в примере.

пример

Мы будем использовать таблицу CUSTOMERS, которую мы создали и использовали в предыдущих главах.

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

Следующая программа обновит таблицу и увеличит зарплату каждого клиента на 500 и использует атрибут SQL% ROWCOUNT, чтобы определить количество затронутых строк:

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected '); 
   END IF;  
END; 
/      

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат —

6 customers selected  

PL/SQL procedure successfully completed. 

Если вы проверите записи в таблице клиентов, вы увидите, что строки были обновлены —

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 | 
|  2 | Khilan   |  25 | Delhi     |  2000.00 | 
|  3 | kaushik  |  23 | Kota      |  2500.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7000.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9000.00 | 
|  6 | Komal    |  22 | MP        |  5000.00 | 
+----+----------+-----+-----------+----------+

Явные курсоры

Явные курсоры являются программируемыми курсорами для получения большего контроля над областью контекста . Явный курсор должен быть определен в разделе объявлений блока PL / SQL. Он создается в операторе SELECT, который возвращает более одной строки.

Синтаксис для создания явного курсора —

CURSOR cursor_name IS select_statement; 

Работа с явным курсором включает в себя следующие шаги:

• Объявление курсора для инициализации памяти
• Открытие курсора для выделения памяти
• Выбор курсора для получения данных
• Закрытие курсора для освобождения выделенной памяти

Объявление курсора

Объявление курсора определяет курсор с именем и соответствующим оператором SELECT. Например —

CURSOR c_customers IS 
   SELECT id, name, address FROM customers; 

Открытие курсора

Открытие курсора выделяет память для курсора и делает его готовым для извлечения в него строк, возвращаемых оператором SQL. Например, мы откроем определенный выше курсор следующим образом:

OPEN c_customers; 

Извлечение курсора

Выборка курсора вовлекает доступ к одной строке за один раз. Например, мы будем выбирать строки из открытого выше курсора следующим образом:

FETCH c_customers INTO c_id, c_name, c_addr; 

Закрытие курсора

Закрытие курсора означает освобождение выделенной памяти. Например, мы закроем вышеуказанный курсор следующим образом:

CLOSE c_customers;

пример

Ниже приведен полный пример, иллюстрирующий понятия явных курсоров & minua;

DECLARE 
   c_id customers.id%type; 
   c_name customerS.No.ame%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат —