Связь объектов PL/SQL с таблицами базы данных (Курсоры)

Чтобы программа PL/SQL могла работать с информацией, содержащейся в базах данных, необходимо организовать обмен между значениями столбцов таблиц баз данных и переменными PL/SQL.
Известно, что для выбора информации из таблиц используется SQL предложение SELECT. При его выполнении Oracle создает специальную рабочую область, содержащую информацию о самом SELECT, данные, которые требуются для его выполнения (например, результаты подзапросов), и, наконец, окончательный результат выполнения SELECT. PL/SQL имеет несколько механизмов доступа к этой рабочей области. Одним из них является курсор, с помощью которого можно присвоить имя этой рабочей области и манипулировать содержащейся в ней информацией, последовательно выбирая строки результата и пересылая значения столбцов текущей строки в переменные PL/SQL. Существуют и другие механизмы, не требующее создания явного курсора.

Явный курсор

Курсор — это средство языка SQL, позволяющее с помощью команд OPEN, FETCH и CLOSE получить построчный доступ к результату запроса к базе данных. (Будем также называть курсором и сам набор строк, полученный в результате выполнения запроса.) Для использования курсора его надо сначала объявить, т.е. дать ему имя и указать (с помощью предложения SELECT), какие столбцы и строки базовых таблиц должны быть помещены в набор строк, названный этим именем. Команда OPEN инициализирует получение указанного набора и установку перед первой его строкой указателя текущей строки. Команда FETCH служит для установки указателя текущей строки на следующую запись (первый раз на строку с номером 1) и выборки из текущей строки курсора значений указанных столбцов с пересылкой их в переменные PL/SQL. (Выполнением FETCH в цикле можно последовательно выбрать информацию из всех строк курсора.) Наконец, команда CLOSE позволяет закрыть (удалить из памяти) набор строк (при этом описание курсора сохраняется и его можно снова открыть командой OPEN).

Существует модификация ("Курсор в цикле FOR"), позволяющая организовать последовательный выбор строк объявленного курсора без явного использования команд OPEN, FETCH и CLOSE.

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

Перед работой с курсором его следует объявить в разделе DECLARE или другом допустимом разделе, используя синтаксис:

CURSOR cursor_name [ (parameter [, parameter, ... ] ) ] IS SELECT ...

где  
cursor_name — имя курсора;
SELECT …  — предложение SELECT, определяющее строки курсора;
parametr    — имеет следующий синтаксис:

variable_name [IN] type_name [ { := | DEFAULT } value ]

а type_name — любой тип (подтип) данных PL/SQL без указания ограничений (например, длины символьных значений).

Формальные параметры курсора используются только для передачи значений в WHERE фразу предложения SELECT с целью отбора нужных строк запроса. Передача таких значений производится во время открытия курсора командой OPEN. Если значения формальных параметров отсутствуют в команде OPEN и не заданы по умолчанию (:= value или DEFAULT value), то выдается ошибка. При наличии тех и других используются параметры из команды OPEN.

В следующем примере использованы оба способа задания значений по умолчанию параметрам курсора:

DECLARE CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',
           tdat DATE := '1.1.1996') IS
  SELECT (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka
    FROM shtat x, dolgnosti y, grup_dolg z
   WHERE x.dolgn = y.dolgn  AND y.grup_dolg = z.grup_dolg  AND otdel = otd
     AND tdat BETWEEN nachalo AND konec  AND imya_grup_dolg = grup
   ORDER BY razr_dolg DESC;

Кроме того, в нем выражению "(TO_CHAR(razr)||’ ‘||imya_dolg)" дан псевдоним "razr_dolg", использованный во фразе ORDER BY. Oracle рекомендует создавать псевдонимы для всех выражений фразы SELECT с целью ссылки на них при работе с курсором.

Открытие курсора (OPEN)

Команда OPEN имеет следующий синтаксис

OPEN cursor_name [ (value [,value]...) ];

где список значений ("value") используется для передачи параметров курсора и должен по числу и типу данных совпадать с описанием этих параметров.

Команда выполняет объявленный в курсоре SELECT … , используя (если есть параметры) передаваемые из OPEN значения или значения, указанные при объявлении курсора, создавая набор строк и устанавливая указатель текущей строки перед первой из них. Так, по команде

OPEN s1;

будет создан набор:

                                 razr_dolg                stavka
  Указатель текущей строки ---> ------------------------ ------
                                17 Профессор               1.75
                                16 Профессор               3.
                                15 Доцент                  9.75
                                14 Доцент                  4.
                                13 Старший преподаватель   8.75
                                11 Ассистент               1.

где использовались значения параметров, заданные при описании, а по команде:

OPEN s1 (102,'Специалисты','1.1.1996');

будет создан другой набор:

                                  razr_dolg                stavka
  Указатель текущей строки ---> ------------------------ ------
                                13 Ведущий программист      1.
                                13 Ведущий электроник       2.
                                11 Электроник 1 категории   1.5
                                10 Программист 2 категории  3.5
                                 8 Инженер 2 категории      2.
                                 4 Лаборант                 3.

Выборка строк из курсора (FETCH)

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

FETCH cursor_name INTO {variable_name1[,variable_name2]...} | record_name;

Для каждого значения столбца, возвращенного запросом, в списке INTO должна иметься переменная или поле записи соответствующего типа данных. Такие переменные или записи должны быть заранее описаны в декларативной части блока PL/SQL. Например:

CREATE PROCEDURE pr_shtat IS
CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',
           tdat DATE := '1.1.1996') IS
  SELECT (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka
    FROM shtat x, dolgnosti y, grup_dolg z
   WHERE x.dolgn = y.dolgn  AND y.grup_dolg = z.grup_dolg  AND otdel = otd
     AND tdat BETWEEN nachalo AND konec  AND imya_grup_dolg = grup
   ORDER BY razr DESC;
  sh_raz  VARCHAR2(45);       -- переменная для хранения значения razr_dolg
  sh_stav shtat.stavka%TYPE;  -- переменная для хранения значения stavka
  raz VARCHAR(500);           -- переменная, в которой будет последовательно
                              -- накапливаться текст "разряд-должность"
BEGIN OPEN s1;
         LOOP FETCH s1 INTO sh_raz,sh_stav;
            EXIT WHEN s1%NOTFOUND;  -- выход при отсутствии возвращаемой строки
            raz := raz||sh_raz||';';
         END LOOP;
      CLOSE s1;
END pr_shtat;

Внутри цикла можно использовать значения переменных sh_raz и sh_stav, которые после открытия курсора равны "17 Профессор" и "1.75", после первого прохождения цикла – "16 Профессор" и "3.", после второго – "15 Доцент" и "9.75" и т.д.

При выборке значений текущей строки в запись, например, с именем ShRec надо немного изменить как описание, так и тело блока процедуры:

   ORDER BY razr DESC;  
  TYPE ShRecTyp IS RECORD (raz_dol VARCHAR(45), -- описание типа данных
  stav shtat.stavka%TYPE);                      -- записи ShRec
  ShRec ShRecTyp;                               -- объявление записи ShRec
  raz VARCHAR(500);           -- переменная, в которой будет последовательно
                              -- накапливаться текст "разряд-должность"
BEGIN OPEN s1;
         LOOP FETCH s1 INTO ShRec;
            EXIT WHEN s1%NOTFOUND;  -- выход при отсутствии возвращаемой строки
            raz := raz||ShRec.raz_dol||';';

Теперь значения, получаемые ранее из sh_raz и sh_stav, можно получать из полей ShRec.raz_dol и ShRec.stav записи ShRec.
Целесообразность использования записей возрастает с увеличением количества ее полей или возможности создания описания по типу уже существующей записи с помощью атрибута %ROWTYPE.
Наконец, если при выполнении цикла осуществлен переход за последнюю строку набора, то значения переменных FETCH-списка будут неопределены.

Закрытие курсора (CLOSE)

Команда CLOSE используется для освобождения всех ресурсов, которые поддерживались открытым курсором (при этом описание курсора сохраняется и его
можно снова открыть командой OPEN). Синтаксис команды CLOSE имеет вид:

CLOSE cursor_name;

Использование курсора в цикле FOR

В большинстве ситуаций, которые требуют явного курсора, текст программы может быть упрощен при использовании "курсора в цикле FOR", заменяющего команды OPEN, FETCH и CLOSE. Курсор в цикле FOR:
  - неявно объявляет индекс цикла записью, поля которой соответствуют столбцам (псевдонимам) предложения SELECT … из описания курсора;
   – передает параметры курсора (если они есть) и открывает курсор;
   – выбирает в цикле строки из полученного набора в индекс цикла (поля записи);
   – закрывает курсор после обработки всех строк набора или досрочному выходу из него с помощью команд EXIT или GOTO.
  Синтаксис курсора в цикле FOR имеет вид:

FOR var_rec_name IN cursor_name [ (value [,value]...) ] LOOP
   ТЕЛО ЦИКЛА
END LOOP;

где – var_rec_name индекс цикла, в котором при первом прохождении цикла хранится первая строка набора, при втором прохождении цикла – вторая строка и т.д.;
    – список значений ("value") используется для передачи параметров курсора (он заменяет в данном случае список из команды OPEN);
    – ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых используются переменные с именами var_rec_name.column_name, а column_name имя столбца из перечня столбцов предложения SELECT в описании курсора.
Например:

DROP PROCEDURE pr_shtat; CREATE PROCEDURE pr_shtat IS
CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',
           tdat DATE := '1.1.1996') IS
  SELECT otdel, (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka
    FROM shtat x, dolgnosti y, grup_dolg z
   WHERE x.dolgn = y.dolgn  AND y.grup_dolg = z.grup_dolg  AND otdel = otd
     AND tdat BETWEEN nachalo AND konec  AND imya_grup_dolg = grup
   ORDER BY razr DESC;
  raz VARCHAR(500);           -- переменная, в которой будет последовательно
                              -- накапливаться текст "разряд-должность"
BEGIN FOR s1_rec IN s1 (102,'Специалисты','1.6.1996')
   LOOP
   raz := raz||s1_rec.razr_dolg||';';
   ...
   END LOOP;
END pr_shtat;

Атрибуты явного курсора

Для анализа состояния курсора используются специальные переменные, имена которых составляются из имени курсора и суффиксов %FOUND, %NOTFOUND, %ROWCOUNT и %ISOPEN, называемых атрибутами курсора. Если курсор назван "cursor_name", то эти переменные имеют имена:

cursor_name%NOTFOUND,    cursor_name%FOUND,
cursor_nane%ROWCOUNT  и  cursor_name%ISOPEN.

Значения таких переменных анализируются при выполнении программы с помощью различных операторов управления (IF…THEN, EXIT WHEN и т.п.), которые изменяют (при необходимости) ход выполнения программы. Следует отметить, что ссылка на эти переменные до открытия курсора приводит к появлению сообщения INVALID_CURSOR.
Переменная с атрибутом %ISOPEN позволяет определить, открыт ли курсор. Если он открыт то эта переменная возвращает TRUE, иначе – FALSE. Например:

IF NOT s1%ISOPEN THEN  -- курсор не открыт ?
   OPEN s1;             -- открыть курсор !
END IF;
   FETCH ...

Переменные с %NOTFOUND и %FOUND атрибутами показывают состояние текущей позиции курсора (перед первой выборкой строки курсора обе переменных имеют значение NULL). Переменная с %NOTFOUND принимает значение FALSE тогда, когда выборка возвратила строку (при этом переменная с %FOUND принимает значение TRUE). Если же в результате выборки строка не возвращается, то переменные с %NOTFOUND и %FOUND принимают значения TRUE и FALSE, соответственно.

Переменная с атрибутом %ROWCOUNT содержит количество строк, выбранных из курсора на текущий момент (при открытии курсора эта переменная содержит ноль).
В следующем примере переменная s1%ROWCOUNT ограничивает выборку из курсора s1 десятью строками:

LOOP FETCH s1 INTO sh_raz,sh_stav;
   IF s1%ROWCOUNT > 10 THEN
   ...
   END IF;
   ...
END LOOP;

Изменение или удаление текущей строки курсора

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

UPDATE [schema.]{table | view}[@dblink] [alias]
   SET { (column [, column] ...) = (subquery)
       |  column = { expr | (subquery) } }
    [, { (column [, column] ...) = (subquery)
       |  column = { expr | (subquery) } } ] ...
    WHERE CURRENT OF cursor_name;
   DELETE [FROM] [schema.]{table | view}[@dblink] [alias]
    WHERE CURRENT OF cursor_name;

Для этого необходимо, чтобы при объявлении курсора предложение SELECT … содержало фразу

FOR UPDATE OF [[schema.]{table | view}.]column
           [, [[schema.]{table | view}.]column ] ... ;

в которой следует привести список обновляемых столбцов.

Неявный курсор (SQL курсор)

Для всех команд языка SQL, не связанных с объявлением курсора ("явным курсором"), PL/SQL открывает курсор ("неявный курсор"), на который можно ссылаться по курсорному имени SQL%. При работе с таким курсором нельзя использовать команды OPEN, FETCH и CLOSE, но можно использовать атрибуты курсора, чтобы получить информацию о текущем его состоянии.

SELECT … INTO

В тех случаях, когда программе необходимо иметь значения столбцов из одной строки таблицы, можно воспользоваться предложением SELECT … INTO, формат которого имеет вид:

SELECT [DISTINCT | !!under!!ALL]
       { [schema.]{table | view | snapshot}.expr [c_alias] }
    [, { [schema.]{table | view | snapshot}.expr [c_alias] } ] ... }
   INTO { variable_name [, variable_name ] ... } | record_name
      FROM table_list [WHERE condition]
     [GROUP BY expr [, expr] ...] [HAVING condition]
     [ {UNION | UNION ALL | INTERSECT | MINUS} SELECT command]
     [ORDER BY {expr | c_alias | position}
       [!!under!!ASC | DESC] [, {expr | c_alias | position}
       [!!under!!ASC | DESC]] ]...
     [FOR UPDATE [OF [[schema.]{table | view}.]column
                  [, [[schema.]{table | view}.]column] ...]
     [NOWAIT] ]

Практически это обычный SELECT, выполняющий присвоение выбираемых значений столбцов переменным, перечисленным во фразе INTO. Однако такое присвоение происходит только в том случае, если "WHERE condition" обеспечивает возвращение по запросу лишь одной строки и переменные заранее описаны в декларативной части блока PL/SQL.

UPDATE, DELETE и INSERT

Эти предложения отличаются от аналогичных предложений интерактивного SQL лишь тем, что в их выражениях (expr) могут использоваться переменные PL/SQL.

Атрибуты неявного курсора (SQL курсора)

Для анализа результата выполнения предложений SELECT…INTO, INSERT, UPDATE и DELETE используются три переменные: SQL%NOTFOUND, SQL%FOUND и SQL%ROWCOUNT (Oracle закрывает курсор SQL автоматически после выполнения SQL предложения, что делает бессмысленным использование переменной SQL%ISOPEN, так как ее значение всегда равно FALSE).
Перед выполнением предложений SELECT…INTO, INSERT, UPDATE и DELETE переменные SQL%NOTFOUND и SQL%FOUND имеют значение NULL. Переменная SQL%NOTFOUND принимает значение TRUE, если INSERT, UPDATE и DELETE не произвели изменений таблиц базы данных или SELECT…INTO не возвратил строк (при этом переменная SQL%FOUND принимает значение FALSE). В противном случае переменная SQL%NOTFOUND принимает значение FALSE, а переменная SQL%FOUND – TRUE. Вот один из примеров использования SQL%NOTFOUND для добавления новой строки в таблицу temp при сбое модификации:

UPDATE shtat SET stavka = stavka + 1 WHERE dolgn = 'доцент' AND razr = 15;
   IF SQL%NOTFOUND THEN            -- изменение не выполнено
      INSERT INTO temp VALUES (...);
   END IF;

                          

Оставить комментарий