Исходники
Статьи
Языки программирования
.NET Delphi Visual C++ Borland C++ Builder C/С++ и C# Базы Данных MySQL MSSQL Oracle PostgreSQL Interbase VisualFoxPro Веб-Мастеру PHP HTML Perl Java JavaScript Протоколы AJAX Технология Ajax Освоение Ajax Сети Беспроводные сети Локальные сети Сети хранения данных TCP/IP xDSL ATM Операционные системы Windows Linux Wap Книги и учебники
Скрипты
Магазин программиста
|
Кроме того, в нем выражению "(TO_CHAR(razr)||' '||imya_dolg)" дан псевдоним
"razr_dolg", использованный во фразе ORDER BY. Oracle рекомендует создавать
псевдонимы для всех выражений фразы SELECT с целью ссылки на них при работе с
курсором.
12.3.1.2. Открытие курсора (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.
12.3.1.3. Выборка строк из курсора (FETCH)
Команда FETCH, используемая для продвижения на один шаг указателя текущей
строки курсора и пересылки ее значений в переменные или запись, имеет следующий
синтаксис:
FETCH cursor_name INTO {variable_name1[,variable_name2]...} | record_name ;
Для каждого значения столбца, возвращенного запросом, в списке INTO должна
иметься переменная или поле записи соответствующего типа данных. Такие
- 31 -
переменные или записи должны быть заранее описаны в декларативной части блока
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; -- выход при отсутствии возвращаемой строки
-- (см. п. 12.3.1.6)
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-списка будут неопределены.
- 32 -
12.3.1.4. Закрытие курсора (CLOSE)
Команда CLOSE используется для освобождения всех ресурсов, которые
поддерживались открытым курсором (при этом описание курсора сохраняется и его
можно снова открыть командой OPEN). Синтаксис команды CLOSE имеет вид:
CLOSE cursor_name;
12.3.1.5. Использование курсора в цикле 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;
12.3.1.6. Атрибуты явного курсора
Для анализа состояния курсора используются специальные переменные, имена
которых составляются из имени курсора и суффиксов %FOUND, %NOTFOUND, %ROWCOUNT
и %ISOPEN, называемых атрибутами курсора. Если курсор назван "cursor_name", то
эти переменные имеют имена:
cursor_name%NOTFOUND, cursor_nane%FOUND,
cursor_nane%ROWCOUNT и cursor_nane%ISOPEN.
- 33 -
Значения таких переменных анализируются при выполнении программы с помощью
различных операторов управления (IF...THEN, EXIT WHEN и т.п.), которые изменяют
(при необходимости) ход выполнения программы. Следует отметить, что ссылка на
эти переменные до открытия курсора приводит к появлению сообщения INVALID_CURSOR.
Переменная с атрибутом %ISOPEN позволяет определить, открыт ли курсор. Если
он открыт то эта переменная возвращает TRUE, иначе - FALSE. Например:
IF NOT s1%ISOPEN THEN -- курсор не открыт ?
OPEN s1; -- открыть курсор !
IF END;
FETCH ...
Переменные с %NOTFOUND и %FOUND атрибутами показывают состояние текущей
позиции курсора (перед первой выборкой строки курсора обе переменных имеют
значение NULL). Переменная с %NOTFOUND принимает значение FALSE тогда, когда
выборка возвратила строку (при этом переменная с %FOUND принимает значение
TRUE). Если же в результате выборки строка не возвращается, то переменные с
%NOTFOUND и %FOUND принимают значения TRUE и FALSE, соответственно. Пример
использования %NOTFOUND был рассмотрен в п. 12.3.1.3.
Переменная с атрибутом %ROWCOUNT содержит количество строк, выбранных из
курсора на текущий момент (при открытии курсора эта переменная содержит ноль).
В следующем примере переменная s1%ROWCOUNT ограничивает выборку из курсора s1
десятью строками:
LOOP
FETCH s1 INTO sh_raz,sh_stav;
IF s1%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;
12.3.1.7. Изменение или удаление текущей строки курсора
Существует два предложения, позволяющие изменить или удалить ту строку
таблицы базы данных, на которую позиционирована текущая строка курсора:
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 ] ... ;
в которой следует привести список обновляемых столбцов.
- 34 -
12.3.2. Неявный курсор (SQL курсор)
Для всех команд языка SQL, не связанных с объявлением курсора ("явным
курсором"), PL/SQL открывает курсор ("неявный курсор"), на который можно
ссылаться по курсорному имени SQL%. При работе с таким курсором нельзя
использовать команды OPEN, FETCH и CLOSE, но можно использовать атрибуты
курсора, чтобы получить информацию о текущем его состоянии.
12.3.2.1. 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.
12.3.2.2. UPDATE, DELETE и INSERT
Эти предложения отличаются от аналогичных предложений интерактивного SQL
лишь тем, что в их выражениях (expr) могут использоваться переменные PL/SQL.
12.3.2.3. Атрибуты неявного курсора (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;
- 35 -
12.4. Операторы управления выполнением программы
12.4.1. Операторы условного перехода (IF ...)
Существует три модификации оператора условного перехода:
IF-THEN | IF-THEN-ELSIF
------- | -------------
IF условие THEN | IF условие1 THEN
последовательность команд; | 1-я последовательность команд;
END IF; | ELSIF условие2 THEN
| 2-я последовательность команд;
IF-THEN-ELSE | ...
------------ | ELSIF условиеN THEN
IF условие THEN | N-я последовательность команд;
1-я последовательность команд; | [ ELSE
ELSE | N+1-я последовательность команд; ]
2-я последовательность команд; | END IF;
END IF; |
Синтаксис условий приведен в п. 4.4. Во всех модификациях если "условие" или
"условие1" истинно (TRUE), то выполняется "последовательность команд" или
"1-я последовательность команд" и управление передается на первый оператор
после END IF. Если же оно ложно (FALSE), то:
- в модификации IF-THEN управление передается на первый оператор после END IF;
- в модификации IF-THEN-ELSE выполняется 2-я последовательность команд и
управление передается на первый оператор после END IF;
- в модификации IF-THEN-ELSIF проверяется условие 2; если оно истинно, то
выполняется 2-я последовательность команд и управление передается на первый
оператор после END IF; если условия 1 и 2 ложны, а условие 3 истинно, то
выполняется 3-я последовательность команд и управление передается на первый
оператор после END IF; наконец, если условия 1, 2, ..., N ложны, то
выполняется N+1-я последовательность команд и управление передается на первый
оператор после END IF.
Все это справедливо, если внутри последовательности команд нет операторов,
осуществляющих переход за пределы этой последовательности.
12.4.2. Метки и оператор безусловного перехода (GOTO)
В любом месте программы может быть поставлена метка, имеющая синтаксис:
<<имя_метки>>
Оператор GOTO позволяет осуществить безусловный переход к метке, имя которой
должно быть уникальным внутри программы или блока PL/SQL. Например, управление
передается вниз к помеченному оператору:
BEGIN
...
GOTO insert_row;
...
<
|
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Рейтинги
|