|
|
 |
Краткий справочник по Oracle7
Содержание
1. Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2. Имена объектов базы данных . . . . . . . . . . . . . . . . . . . . . . . 4
3. Зарезервированные слова Oracle7 и PL/SQL . . . . . . . . . . . . . . . . 4
4. Литералы, операторы и выражения . . . . . . . . . . . . . . . . . . . . . 5
4.1. Условные обозначения . . . . . . . . . . . . . . . . . . . . . . . . 5
4.2. Иерархия операторов . . . . . . . . . . . . . . . . . . . . . . . . . 6
4.3. Синтаксис выражений (expr) . . . . . . . . . . . . . . . . . . . . . 6
4.4. Синтаксис условий (condition) . . . . . . . . . . . . . . . . . . . . 6
4.5. Логические операторы . . . . . . . . . . . . . . . . . . . . . . . . 7
4.6. Операторы, используемые в предложении SELECT . . . . . . . . . . . . 7
5. Типы данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
6. Комментарии . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
7. Функции SQL и ORACLE7 . . . . . . . . . . . . . . . . . . . . . . . . . . 8
7.1. Числовые функции . . . . . . . . . . . . . . . . . . . . . . . . . . 9
7.2. Символьные функции . . . . . . . . . . . . . . . . . . . . . . . . . 9
7.3. Функции работы с датами и временем . . . . . . . . . . . . . . . . . 10
7.4. Функции преобразования . . . . . . . . . . . . . . . . . . . . . . . 11
7.5. Групповые функции . . . . . . . . . . . . . . . . . . . . . . . . . . 12
7.6. Прочие функции . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
8. Форматы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
8.1. Числовые форматы . . . . . . . . . . . . . . . . . . . . . . . . . . 13
8.2. Символьные форматы . . . . . . . . . . . . . . . . . . . . . . . . . 13
8.3. Форматы дат и времени . . . . . . . . . . . . . . . . . . . . . . . . 13
8.4. Префиксы и суффиксы, используемые в форматах . . . . . . . . . . . . 14
9. Псевдо- столбцы и таблицы . . . . . . . . . . . . . . . . . . . . . . . . 14
10. Предложения (команды) SQL Oracle7 . . . . . . . . . . . . . . . . . . . . 16
ALTER CLUSTER - модифицирует структуру индексированного или
хешированного кластера . . . . . . . . . . . . . . . . . . . . . 16
ALTER DATABASE - модифицирует структуру и (или) рабочий режим базы
данных Oracle7; позволяет восстановить базу данных . . . . . . . 16
ALTER FUNCTION - принудительно перекомпилирует функцию . . . . . . . . . 16
ALTER INDEX - модифицирует параметры хранения индекса . . . . . . . . . . 16
ALTER PACKAGE - принудительно перекомпилирует пакет . . . . . . . . . . . 16
ALTER PROCEDURE - принудительно перекомпилирует процедуру . . . . . . . . 16
ALTER PROFILE - модифицирует профиль ограничений ресурсов . . . . . . . . 17
ALTER RESOURCE COST - модифицирует веса ограничений сложного ресурса. . . 17
ALTER ROLE - изменяет пароль роли или метод аутентификации. . . . . . . . 17
ALTER ROLLBACK SEGMENT - изменяет доступность или параметры памяти
сегмента отката . . . . . . . . . . . . . . . . . . . . . . . . . 17
ALTER SEQUENCE - изменяет характеристики последовательности . . . . . . . 17
ALTER SESSION - модифицирует параметры сеанса базы данных . . . . . . . . 17
ALTER SNAPSHOT - модифицирует режим и (или) время получения "снимка"
базы данных или характеристики хранения . . . . . . . . . . . . . 17
ALTER SNAPSHOT LOG - модифицирует характеристики памяти для "снимка". . . 18
ALTER SYSTEM - динамически модифицирует некоторые текущие параметры
сервера базы данных . . . . . . . . . . . . . . . . . . . . . . . 18
ALTER TABLE - модифицирует физическую структуру таблицы, параметры
памяти и ограничения целостности . . . . . . . . . . . . . . . . 18
ALTER TABLESPACE - изменяет доступность табличной области, режим
архивации или параметры памяти, либо добавляет файлы данных
для увеличения емкости памяти табличной области . . . . . . . . . 18
ALTER TRIGGER - разрешает или запрещает запуск триггера . . . . . . . . . 18
ALTER USER - модифицирует учетные данные пользователя, пароль или
метод аутентификации . . . . . . . . . . . . . . . . . . . . . . 19
ALTER VIEW - вручную перекомпилирует представление . . . . . . . . . . . 19
ANALYZE - генерирует статистику оптимизатора либо создает для таблицы,
индекса или кластера список цепочки строк; может проверять
допустимость структуры индекса . . . . . . . . . . . . . . . . . 19
- 2 -
AUDIT - задает для системы и объектов базы данных параметры отслеживания 19
COMMENT - создает описание таблицы, представления, "снимка" или столбца . 19
COMMIT - завершает транзакцию, фиксируя изменения в базе данных . . . . . 19
CREATE CLUSTER - создает индексированный или хешированный кластер . . . . 19
CREATE CONTROLFILE - создает новый управляющий файл базы данных, 19
заменяющий испорченный управляющий файл или управляющий
файл с неверным размером . . . . . . . . . . . . . . . . . . . . 20
CREATE DATABASE - создает базу данных . . . . . . . . . . . . . . . . . . 20
CREATE DATABASE LINK - определяет имя маршрута для удаленной базы
данных; связь с такой базой данных доступна и без Distributed
Database, но только для операций чтения . . . . . . . . . . . . . 20
CREATE FUNCTION - создает хранимую функцию . . . . . . . . . . . . . . . 20
CREATE INDEX - создает индекс базы данных . . . . . . . . . . . . . . . . 20
CREATE PACKAGE - создает спецификацию хранимого пакета . . . . . . . . . 20
CREATE PACKAGE BODY - создает тело хранимого пакета . . . . . . . . . . . 20
CREATE PROCEDURE - создает хранимую процедуру . . . . . . . . . . . . . . 20
CREATE PROFILE - создает именованный профиль ограничения ресурса . . . . 20
CREATE ROLE - создает роль для группы связных полномочий . . . . . . . . 20
CREATE ROLLBACK SEGMENT - создает сегмент отката. . . . . . . . . . . . . 20
CREATE SCHEMA - создает в одном операторе несколько таблиц и
представлений для текущей учетной записи базы данных . . . . . . 21
CREATE SEQUENCE - создает именованную последовательность чисел . . . . . 21
CREATE SNAPSHOT - создает "снимок" удаленной базы данных. . . . . . . . . 21
CREATE SNAPSHOT LOG - создает для "снимка" обновленный журнал . . . . . . 21
CREATE SYNONYM - создает синоним для объекта базы данных. . . . . . . . . 21
CREATE TABLE - создает новую таблицу базы данных. . . . . . . . . . . . . 21
CREATE TABLESPACE - создает новую табличную область . . . . . . . . . . . 21
CREATE TRIGGER - создает для таблицы триггер базы данных. . . . . . . . . 21
CREATE USER - создает нового пользователя и пароль . . . . . . . . . . . 22
CREATE VIEW - создает представление для таблиц или других
представлений . . . . . . . . . . . . . . . . . . . . . . . . . . 22
DELETE - удаляет из таблицы базы данных одну или более строк . . . . . . 22
DROP CLUSTER - удаляет индексированный или хешированный кластер . . . . . 22
DROP DATABASE LINK - удаляет именованный маршрут к удаленной базе данных 22
DROP FUNCTION - удаляет хранимую функцию . . . . . . . . . . . . . . . . 22
DROP INDEX - удаляет индекс таблицы . . . . . . . . . . . . . . . . . . . 22
DROP PACKAGE - удаляет спецификацию и тело хранимого пакета . . . . . . . 22
DROP PACKAGE BODY - удаляет тело хранимого пакета . . . . . . . . . . . . 22
DROP PROCEDURE - удаляет хранимую процедуру . . . . . . . . . . . . . . . 22
DROP PROFILE - удаляет именованный профиль ограничений ресурсов . . . . . 22
DROP ROLE - удаляет роль или группу полномочий . . . . . . . . . . . . . 22
DROP ROLLBACK SEGMENT - удаляет сегмент отката . . . . . . . . . . . . . 22
DROP SEQUENCE - удаляет именованную последовательность чисел . . . . . . 22
DROP SNAPSHOT - удаляет "снимок" удаленной базы данных . . . . . . . . . 22
DROP SNAPSHOT LOG - удаляет удаляет журнал снимка . . . . . . . . . . . . 22
DROP SYNONYM - удаляет синоним таблицы или представления . . . . . . . . 22
DROP TABLE - удаляет таблицу базы данных . . . . . . . . . . . . . . . . 22
DROP TABLESPACE - удаляет табличную область . . . . . . . . . . . . . . . 22
DROP TRIGGER - удаляет триггер базы данных . . . . . . . . . . . . . . . 22
DROP USER - удаляет имя пользователя и связанные с ним объекты . . . . . 22
DROP VIEW - удаляет представление . . . . . . . . . . . . . . . . . . . . 22
EXPLAIN PLAN - помещает в таблицу базы данных стратегию оптимизации
для оператора SQL . . . . . . . . . . . . . . . . . . . . . . . . 22
GRANT - назначает для ролей и (или) пользователей роли, системные
полномочия и (или) полномочия на объекты . . . . . . . . . . . . 22
INSERT - вставляет в таблицу базы данных одну или более строк . . . . . . 22
LOCK TABLE - блокирует таблицу . . . . . . . . . . . . . . . . . . . . . 23
NOAUDIT - запрещает для системных объектов и объектов базы данных
параметры отслеживания . . . . . . . . . . . . . . . . . . . . . 23
- 3 -
RENAME - переименовывает таблицу, представление, последовательность
или синоним . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
REVOKE - отменяет для ролей и (или) пользователей назначений ролей,
системные полномочия и (или) полномочия на объекты . . . . . . . 23
ROLLBACK - отменяет изменения, внесенные текущей транзакцией . . . . . . 23
SAVEPOINT - идентифицирует промежуточную точку сохранения транзакции . . 23
SELECT - считывает все или конкретные столбцы из одной или более
строк одной или нескольких таблиц и (или) представлений . . . . . 23
SET ROLE - разрешает одну или более заданных ролей и запрещает все другие 24
SET TRANSACTION - задает характеристики транзакции . . . . . . . . . . . 24
TRUNCATE - удаляет все строки из таблицы и (необязательно) всю
выделенную для таблиц память на диске . . . . . . . . . . . . . . 24
UPDATE - обновляет все или конкретные строки таблицы . . . . . . . . . . 24
11. Фразы предложений (команд) SQL Oracle7 . . . . . . . . . . . . . . . . . 24
ARCHIVE LOG - устанавливает способ архивирования групп файлов регистрации 24
CONSTRAINT - определяет ограничения целостности таблиц . . . . . . . . . 24
DISABLE - маскирует ограничения целостности или все триггеры . . . . . . 25
DROP - удаляет ограничения целостности таблицы . . . . . . . . . . . . . 25
ENABLE - отменяет маскирование ограничений целостности или триггеров . . 25
Filespec - спецификация файла базы данных . . . . . . . . . . . . . . . . 25
RECOVER - исполняет восстановление средств информации . . . . . . . . . . 25
STORAGE - определяет характеристики хранения кластеров, табличных
пространств, таблиц, "снимков", индексов и сегментов отката . . . 25
WHERE - определяет подмножество строк . . . . . . . . . . . . . . . . . . 25
12. Язык PL/SQL - процедурные расширения языка SQL . . . . . . . . . . . . . 26
12.1. Основные конструкции языка PL/SQL . . . . . . . . . . . . . . . . . 26
12.2. Типы данных, переменные, константы и выражения . . . . . . . . . . . 26
12.2.1. Типы данных, доступные в PL/SQL . . . . . . . . . . . . . . . . 26
12.2.2. Таблицы PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . 26
12.2.3. Записи PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . 27
12.2.4. Переменные, константы и выражения . . . . . . . . . . . . . . . 28
12.3. Присваивание переменным значений из таблиц базы данных . . . . . . . 28
12.3.1. Явный курсор . . . . . . . . . . . . . . . . . . . . . . . . . 29
12.3.1.1. Объявление курсора . . . . . . . . . . . . . . . . . . . . 29
12.3.1.2. Открытие курсора (OPEN) . . . . . . . . . . . . . . . . . 30
12.3.1.3. Выборка строк из курсора (FETCH) . . . . . . . . . . . . . 30
12.3.1.4. Закрытие курсора (CLOSE) . . . . . . . . . . . . . . . . . 32
12.3.1.5. Использование курсора в цикле FOR . . . . . . . . . . . . 32
12.3.1.6. Атрибуты явного курсора . . . . . . . . . . . . . . . . . 32
12.3.1.7. Изменение или удаление текущей строки курсора . . . . . . 33
12.3.2. Неявный курсор (SQL курсор) . . . . . . . . . . . . . . . . . . 34
12.3.2.1. SELECT ... INTO . . . . . . . . . . . . . . . . . . . . . 34
12.3.2.2. INSERT, UPDATE и DELETE . . . . . . . . . . . . . . . . . 34
12.3.2.3. Атрибуты неявного курсора (SQL курсора) . . . . . . . . . 34
12.4. Операторы управления выполнением программы . . . . . . . . . . . . . 35
12.4.1. Операторы условного перехода (IF ...) . . . . . . . . . . . . . 35
12.4.2. Метки и оператор безусловного перехода (GOTO) . . . . . . . . . 35
12.4.3. Операторы цикла (LOOP, WHILE...LOOP и FOR...LOOP) . . . . . . . 36
12.4.4. Операторы EXIT, EXIT-WHEN и NULL . . . . . . . . . . . . . . . 38
12.5. Обработка ошибок . . . . . . . . . . . . . . . . . . . . . . . . . . 39
12.5.1. Встроенные исключительные ситуации . . . . . . . . . . . . . . 39
12.5.2. Исключительные ситуации, определяемые пользователем . . . . . . 42
12.5.3. Обработчик OTHERS . . . . . . . . . . . . . . . . . . . . . . . 42
12.6. Транзакции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
12.7. О программах PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . 43
12.8. Отладка программ PL/SQL . . . . . . . . . . . . . . . . . . . . . . 43
13. Представления словарей данных . . . . . . . . . . . . . . . . . . . . . . 44
14. Виртуальные представления словаря данных . . . . . . . . . . . . . . . . 47
15. Системные полномочия Oracle7 . . . . . . . . . . . . . . . . . . . . . . 48
16. Полномочия на объекты . . . . . . . . . . . . . . . . . . . . . . . . . . 50
17. Основные понятия и параметры, используемые в справочнике . . . . . . . . 50
- 4 -
1. Введение
В справочник включены краткие сведения о языке PL/SQL, синтаксис предложений
и некоторых фраз языка SQL (подробно они будут описаны в другом документе),
краткие описания конструкций, включаемых в эти языки, а также информация о
2. Имена объектов базы данных
Имена объектов базы данных (таблиц, представлений, последовательностей,
"снимков", пакетов, процедур и функций) должны содержать не более 30 символов
и начинаться с буквы. После начальной буквы имя может любые содержать буквы,
цифры и символы "$", "#", и "_", однако полученное сочетание (имя) не может
быть зарезервированным словом Oracle (см. п. 3). Если же возникает необходимость
использовать в имени любые символы кроме кавычек и (или) составлять его из
нескольких слов, то такое имя надо заключить в кавычки (например, "Это имя
столбца".
Имя базы данных не должно превышать 8 символов.
3. Зарезервированные слова Oracle7 и PL/SQL
SQL резервные слова (слова отмеченные * используются также в PL/SQL):
ACCESS* DEFAULT* INTEGER OPTION* START*
ADD* DELETE* INTERSECT* OR* SUCCESSFUL
ALL* DESC* INTO* ORDER* SYNONYM
ALTER* DISTINCT* IS* PCTFREE* SYSDATE
AND* DROP* LEVEL* PRIOR* TABLE*
ANY* ELSE* LIKE* PRIVILEGES THEN*
AS* EXCLUSIVE LOCK PUBLIC* TO*
ASC* EXISTS* LONG RAW TRIGGER
AUDIT FILE MAXEXTENTS RENAME* UID
BETWEEN* FLOAT MINUS* RESOURCE* UNION*
BY* FOR* MODE REVOKE UNIQUE*
CHAR* FROM* MODIFY ROW UPDATE*
CHECK* GRANT* NOAUDIT ROWID USER
CLUSTER* GROUP* NOCOMPRESS* ROWLABEL VALIDATE
COLUMN HAVING* NOT* ROWNUM* VALUES*
COMMENT IDENTIFIED* NOWAIT ROWS VARCHAR*
COMPRESS* IMMEDIATE NULL* SELECT* VARCHAR2*
CONNECT* IN* NUMBER* SESSION VIEW*
CREATE* INCREMENT OF* SET* WHENEVER
CURRENT* INDEX* OFFLINE SHARE WHERE*
DATE* INITIAL ON* SIZE* WITH*
DECIMAL INSERT ONLINE SMALLINT
- 5 -
PL/SQL резервные слова
ABORT CREATE GOTO PACKAGE SUM
ACCEPT CURRENT GRANT PARTITION TABAUTH
ACCESS CURSOR GROUP PCTFREE TABLE
ADD DATABASE HAVING PRAGMA TABLES
ALL DATA_BASE IDENTIFIED PRIOR TASK
ALTER DATE IF PRIVATE TERMINATE
AND DBA IN PROCEDURE THEN
ANY DEBUGOFF INDEX PUBLIC TO
ARRAY DEBUGON INDEXES RAISE TRUE
AS DECLARE INDICATOR RANGE TYPE
ASC DEFAULT INSERT RECORD UNION
ASSERT DEFINITION INTERSECT RELEASE UNIQUE
ASSIGN DELAY INTO REM UPDATE
AT DELETE IS RENAME USE
AUTHORIZA DELTA LEVEL RESOURCE VALUES
TION DESC LIKE RETURN VARCHAR
AVG DIGITS LIMITED REVERSE VARCHAR2
BEGIN DISPOSE LOOP REVOKE VARIANCE
BETWEEN DISTINCT MAX ROLLBACK VIEW
BODY DO MIN ROWNUM VIEWS
BOOLEAN DROP MINUS ROWTYPE WHEN
BY ELSE MOD RUN WHERE
CASE ELSIF NEW SAVEPOINT WHILE
CHAR END NOCOMPRESS WSCHEMA WITH
CHAR_BASE ENTRY NOT SELECT WORK
CHECK EXCEPTION NULL SEPARATE XOR
CLOSE EXCEP NUMBER SET
CLUSTER TION_INIT NUMBER_BASE SIZE
CLUSTERS EXISTS OR SPACE
COLAUTH EXIT ON SQL
COLUMNS FALSE OPEN SQLCODE
COMMIT FETCH OPTION SQLERRM
COMPRESS FOR OR START
CONNECT FORM ORDER STATEMENT
CONSTANT FROM OTHERS STDDEV
COUNT FUNCTION OUT SUBTYPE
CRASH GENERIC
4. Литералы, операторы и выражения
4.1. Условные обозначения
[ ] - в эти скобки заключаются необязательные синтаксические единицы
{ } - конструкция, заключенная в эти скобки, должна рассматриваться как
одна синтаксическая единица
| - используется для разделения альтернативных синтаксических единиц
... - указывает на то, что непосредственно предшествующая синтаксическая
единица может повторяться один или несколько раз
text - '[символ | '']...'
integer - цифра[цифра]...
number - [+|-]{цифра[цифра]...[.][цифра]...
|.цифра[цифра]...}[{e|E}[+|-]цифра[цифра]...]
expr - любое выражение
Expr_list - (expr [, expr] ...)
- 6 -
4.2. Иерархия операторов
Оператор Описание
----------- ------------------------------------------------
() Подавляет обычные правила старшинства операций.
** NOT Возведение в степень и логическое отрицание.
+ - Знак, предшествующий числовому выражению.
* / Умножение и деление.
+ - Сложение и вычитание.
|| Сочленение текстовых выражений и (или) констант.
:= Присвоение значения переменной пользователя.
=, <>, <,
>, <=, >=, Операторы сравнения,
IS NULL, используемые при
LIKE, IN, построении условий
BETWEEN
AND Логическое "И"
OR Логическое "ИЛИ"
4.3. Синтаксис выражений (expr)
1-я форма: { [[schema.]{table | view | snapshot }.]
{column | pseudo-column | ROWLABEL}
| 'text' | number | sequence.{CURRVAL | NEXTVAL} | NULL }
2-я форма: function_name [( [DISTINCT | ALL] expr [, expr] ... )]
3-я форма: { (expr) | +expr | -expr | PRIOR expr | expr * expr | expr / expr
| expr + expr | expr - expr | expr || expr }
Decode_expr: DECODE( expr, search, result [, search, result] ... [, default] )
Если значение expr = search, то возвращает значение result, иначе
значение default или NULL (при отсутствии default).
4.4. Синтаксис условий (condition)
1-я форма: { expr {= | <> | > | < | >= | <=}
{expr | (subquery)} | expr_list {= | <>} (subquery) }
2-я форма: { expr {= | <> | > | < | >= | <=}
{ANY | SOME | ALL} {expr_list | (subquery)} | expr_list {= | <>}
{ANY | SOME | ALL} ( { expr_list [, expr_list] ... | subquery} ) }
3-я форма: { expr [NOT] IN {expr_list | (subquery)}
| expr_list [NOT] IN ( { expr_list [, expr_list]... | subquery} ) }
4-я форма: expr [NOT] BETWEEN expr AND expr
5-я форма: expr IS [NOT] NULL
6-я форма: EXISTS (subquery)
7-я форма: char1 [NOT] LIKE char2 [ESCAPE 'c']
8-я форма: { ( условие )
| NOT условие
| условие AND условие
| условие OR условие }
- 7 -
4.5. Логические операторы
NOT Реверсирование результата логического выражения (условия).
NOT | true false null
------|-------------------
| false true null
AND Логическая операция "И" соединяющая два или несколько условий
(логических выражений). В зависимости от истинности (true) или
ложности (false) этих условий результат определяется по правилам:
AND | true false null
------|--------------------
true | true false null
false | false false false
null | null false null
OR Логическая операция "ИЛИ" соединяющая два или несколько условий
(логических выражений). В зависимости от истинности (true) или
ложности (false) этих условий результат определяется по правилам:
OR | true false null
------|-------------------
true | true true true
false | true false null
null | true null null
4.6. Операторы, используемые в предложении SELECT
UNION Объединение двух запросов; результат состоит из неповторяющиеся строк,
выданных каждым из объединяемых запросов.
UNION ALL Объединение двух запросов; результат состоит из всех строк, выданных
каждым из объединяемых запросов.
INTERSECT Комбинация двух запросов; результат состоит из строк, которые выданы
как первым, так и вторым запросом.
MINUS Комбинация двух запросов; результат состоит из строк, которые выданы
первым запросом, но не выданы вторым запросом.
(+) Оператор внешнего соединения.
* Заменяет список имен всех столбцов таблицы или представления.
PRIOR Используется в древовидных структурах для указания направления
просмотра: от корня до листьев или от листьев до корня.
ALL Сохраняет дубликаты строк в результате запроса (ALL устанавливается
по умолчанию).
DISTINCT Исключает дубликаты строк из результата запроса.
5. Типы данных ORACLE7
Тип данных Описание
---------------- -------------------------------------------------------------
CHAR(размер) Используется для хранения текстовых строк фиксированной длины.
По умолчанию размер 1, максимальный размер 255.
DATE Используется для хранения информации о дате и времени (дата,
час, минута и секунда). Интервал дат: 1 января 4712 до н.э.
- 31 декабря 4712 н.э.
LONG Используется для хранения больших строк переменной длины (до
2 гигабайт).
LONG RAW Используется для хранения двоичных строк (до 2 гигабайт).
- 8 -
Тип данных Описание
---------------- -------------------------------------------------------------
NUMBER(точность, Служат для хранения чисел всех типов (целые, с плавающей
масштаб) точкой и т.д.). Точность изменяется от 0 до 38 (по умолчанию
38), а масштаб от -84 до 127 (по умолчанию 0). Если задается
отрицательный масштаб, то Oracle7 округляет значение слева от
десятичной точки.
RAW(размер) Применяется для хранения небольших двоичных строк (меньше
2000 байт).
ROWID Используется для хранения шестнадцатиричных значений ROWID.
VARCHAR2(размер) Используется для хранения строк переменной длины. По
умолчанию размер 1, максимальный размер 2000.
Преобразование данных с помощью функций SQL и ORACLE7
To |
From | CHAR DATE NUMBER RAW ROWID
-------|-----------------------------------------------------------
CHAR | TO_DATE TO_NUMBER HEXTORAW CHARTOROWID
DATE | TO_CHAR
NUMBER | TO_CHAR TO_DATE
RAW | RAWTOHEX
ROWID | ROWIDTOCHAR
Допустимые преобразования
To |
From | CHAR DATE LONG NUMBER RAW ROWID VARCHAR2
---------|-----------------------------------------------------
CHAR | yes yes yes yes yes yes
DATE | yes yes yes
LONG | yes yes yes
NUMBER | yes yes yes
RAW | yes yes yes
ROWID | yes yes
VARCHAR2 | yes yes yes yes yes yes
6. Комментарии
Для включения комментариев в предложения SQL и командные блоки PL/SQL можно
либо расположить текст комментария между парами символов /* и */ (например,
UPDATE Блюда /* Это имя модифицируемой таблицы */ SET ...), либо предварить его
двумя дефисами (например, DELETE FROM Блюда -- Удаление строк таблицы Блюда).
С помощью первого способа можно включать многострочные комментарии. Для
включения многострочных комментариев вторым способом приходится помещать "--"
перед каждой их строкой. Следует заметить, что комментарии, отмеченные "--"
могут располагаться либо в отдельных строках, либо в конце строк текста
комментируемого предложения (строк текста блока PL/SQL).
7. Функции SQL и ORACLE7
Функции могут быть использованы везде, где используются переменные, столбцы
или выражения (соответствующего типа). Их обычно подразделяют на числовые, сим-
вольные, групповые (функции SQL), функции работы с датами (дата и время), функ-
ции преобразования, и прочие функции.
В описаниях функций используются следующие параметры:
char,char1,char2,... - константы в апострофах или выражения типа CHAR
d,d1,d2 - константы в апострофах или выражения типа DATE
expr,expr1,expr2,... - любые выражения
- 9 -
fmt - формат данных
k,m,n - любые числовые константы или выражения типа NUMBER.
nls - выражение вида 'NLS_SORT = name'.
raw - исходные данные
rowid - внутренний уникальный идентификатор строки
set,set1,set2 - наборы символов
z1,z2 - часовые пояса (см. "Функции работы с датами и временем")
7.1 Числовые функции
Функции Возвращаемое значение
------------- ----------------------------------------------------------------
ABS(n) Абсолютное значение n, большее или равное n.
CEIL(n) Наименьшее целое, не меньшее n.
COS(n) Косинус n, заданного в радианах.
COSH(n) Гиперболический косинус n в радианах.
EXP(n) Возведение e (exp) в степень n (где е = 2.7182818).
FLOOR(n) Наибольшее целое, меньшее или равное n.
LN(n) Натуральный логарифм n, где n > 0.
LOG(m,n) Основание m логарифма n.
MOD(m,n) Остаток от деления m на n.
POWER(m,n) m в степени n. Если n не целое, то оно усекается до целого.
ROUND(n[,m]) n, округленное до m-того десятичного знака; если m опущено, то
оно принимается равным 0. m может быть отрицательным для округ-
ления цифр левее десятичной точки.
SIGN(n) Если n<0, то -1; если n=0, то 0; если n>0, то 1.
SIN(n) Синус n, заданного в радианах.
SINH(n) Гиперболический синус n в радианах.
SQRT(n) Квадратный корень из n; если n < 0, то NULL.
TAN(n) Тангенс n, заданного в радианах.
TANH(n) Гиперболический тангенс n в радианах.
TRUNC(n[,m]) n, усеченное до m десятичных знаков; если m опущено, то оно
принимается равным 0. m может быть отрицательным для усечения
(обнуления) цифр слева от десятичной точки.
7.2 Символьные функции
Функция Возвращаемое значение
---------------- -------------------------------------------------------------
ASCII(char) Код ASCII первого символа символьной переменной "char".
CHR(n) Символ, код ASCII которого равен n
CONCAT(char1, Соединяет (конкатенирует) строку "char1" со строкой "char2".
char2) (Эквивалентна выражению: char1 || char2.)
INITCAP(char) Символьная переменная с первыми буквами слов, начинающихся
с заглавной буквы.
INSTR(char1, Позиция m-того включения "char2" в "char1" при начале поиска
char2[,n[,m]]) с позиции n. Если m опущено, по умолчанию предполагается 1;
аналогично для n. Позиции даются относительно первого знака
"char1", даже если n > 1.
INSTRB(char1, Эквивалентна INSTR, но n и результат возвращаются в байтах,
char2[,n[,m]]) а не в позициях символов. Эту функцию полезно использовать
при работе с многобайтовыми символьными строками.
LENGTH(char) Длина в знаках символьной переменной "char".
LENGTHB(char) Длина в байтах символьной переменной "char".
LOWER(char) "char", где все буквы преобразованы в строчные (маленькие).
LPAD(char1,n Строка "char1", дополненная слева до длины n последователь-
[,char2]) ностью символов из строки "char2" с повторением этой после-
довательности столько раз сколько необходимо. Если "char2"
опущено, то для заполнения используются пробелы.
- 10 -
Функция Возвращаемое значение
---------------- -------------------------------------------------------------
LTRIM(char Удаляет из "char" начальные знаки до тех пор, пока не появит-
[,set]) ся знак, отсутствующий среди знаков "set". При отсутствии
"set" из "char" удаляются все левые пробелы.
NLS_INITCAP(char Аналог INITCAP, но необязательный аргумент "nls" позволяет
[,nls]) задать используемый в функции национальный язык.
NLS_LOWER(char Аналог LOWER, но необязательный аргумент "nls" позволяет
[,nls]) задать используемый в функции национальный язык.
NLSSORT(char Байтовая строка, использованная для сортировки "char" на базе
[,nls]) языка, заданного аргументом "nls". Эту функцию полезно приме-
нять для сравнения строк в различных языках.
NLS_UPPER(char Аналог UPPER, но необязательный аргумент "nls" позволяет
[,nls]) задать используемый в функции национальный язык.
REPLACE(char1, Строка, полученная из "char1", в которой все вхождения "char2"
char2[,char3]) заменены на "char3". Если "char3" отсутствует,
то все вхождения "char2" в "char1" - удаляются.
RPAD(char1,n строка "char1", дополненная справа символами "char2", с повто-
[,char2]) рением, если необходимо; если "char2" опущена, "char1" допол-
няется пробелами.
RTRIM(char Удаляет из "char" конечные знаки до тех пор, пока не появится
[,set]) знак, отсутствующий среди знаков "set". При отсутствии
"set" из "char" удаляются все правые пробелы.
SOUNDEX(char) Фонетическое представление "char" (четырехсимвольное представ-
ление, показывающее, как звучит начало "char").
SUBSTR(char,m Подстрока, получаемая из "char", начиная с символа m. Если
[,n]) задано n, то подстрока ограничивается n символами. При отри-
цательном m символы отсчитываются с конца "char".
SUBSTRB(char,m Эквивалентно SUBSTR, но аргументы m и n выражаются не в
[,n]) символах, а в байтах. Эту функцию полезно использовать при
работе с многобайтовыми символьными строками.
TRANSLATE(char, строка, полученная трансляцией "char" в наборе "set1" в
set1, set2) наборе "set2".
UPPFR(char) строка, полученная из "char" заменой ее строчных букв на
заглавные буквы.
7.3. Функции работы с датами и временем
Функция Возвращаемое значение
----------------- ------------------------------------------------------------
ADD_MONTHS(d,n) Дата d плюс n месяцев.
LAST_DAY(d) Дата последнего дня месяца, заданного датой d.
MONTHS_BETWEEN Количество месяцев между датами d1 и d2. Eсли d1 > d2, то
(d1,d2) результат положителен, иначе отрицателен.
NEW_TIME(d,z1,z2) Преобразует дату и время, заданное d в часовом поясе z1, в
дату и время в часовом поясе z2. Символьные значения z1 и z2
выбираются из следующего списка:
AST,ADT Атлантическое стандартное и дневное время;
BST,BDT Берингово стандартное и дневное время;
CST,CDT Центральное стандартное и дневное время;
EST,EDT Восточное стандартное и дневное время;
GMT Среднее время по Гринвичу;
HST,HDT Аляски-Гаваев стандартное и дневное время;
MST,MDT Монтаны стандартное и дневное время;
NST Нью-Фаунленда стандартное время;
PST,PDT Тихоокеанское стандартное и дневное время;
YST,YDT Юкона стандартное и дневное время.
NEXT_DAY(d,char) Дата первого из дней недели, обозначенной "char", которая
больше или равна d.
- 11 -
Функция Возвращаемое значение
----------------- ------------------------------------------------------------
ROUND(d[,fmt]) Значение d, округленное до ближайшего числа в формате,
заданном "fmt" (например, год или месяц). По умолчанию DD.
SYSDATE Текущая дата и время.
TRUNC(d[,fmt]) Значение d, усеченное до ближайшего числа в формате,
заданном "fmt" (например, год или месяц). По умолчанию DD.
Форматы, используемые в TRUNC и ROUND
Используемый формат Значение
--------------------------- ----------------------------------------
CC or SCC Дата первого дня века
YYYY или SYYYY Дата первого дня года (при округлении: до или
YYY или YY или Y после 1-го июля)
Y,YYY или YEAR или SYEAR
Q Дата первого дня квартала (при округлении: до или
после 16-го числа второго месяца квартала)
MONTH или MON или MM или RM Дата первого дня месяца (при округлении: до или
после 16-го числа месяца)
WW or IW Дата первого дня недели, начинающейся не с
воскресения, а с дня недели определенного по
первому дню года (при округлении: до или после
4-го дня недели)
W Дата первого день недели, начинающейся не с
воскресения, а с дня недели определенного по
первому дню месяца (при округлении: до или после
4-го дня недели)
DDD or DD or J Номер дня
DAY or DY or D Дата первого дня недели (воскресения)
HH or HH12 or HH24 Час
MI Минута
7.4. Функции преобразования
Функция Возвращаемое значение
--------------------- --------------------------------------------------------
CHARTOROWID(char) Идентификатор строки (тип данных ROWID) из строки "char".
CONVERT(char,set1 Преобразованное "char" (по набору символов "set1"). Нео-
[,set2]) бязательный аргумент "set2" задает исходный набор символов.
HEXTORAW(char) Строка "char", преобразованная из шестнадцатиричного
представления в двоичное - удобное для включения в RAW-
столбец (столбец с исходными данными).
RAWTOHEX(raw) Строка шестнадцатиричных значений, получаемая из "raw"
(исходные данные).
ROWIDTOCHAR(rowid) Символьная строка длиной 18 символов, полученная "rowid"
(идентификатор строки).
TO_CHAR(expr[,fmt "expr" преобразуется из числового значения или даты в
[,nls]]) символьную строку по формату, заданному в "fmt". Необя-
зательный аргумент "nls" позволяет задать используемый
в функции национальный язык. Если "fmt" опущено, то чис-
ловое "expr" преобразуется в строку такой длины, кото-
рая вмещает только значащие цифры; дата же преобразуется
по формату даты согласно умолчанию: 'DD-MON-YY'.
TO_DATE(char[,fmt Преобразование даты в символьном виде в значение даты по
[,nls]]) формату, заданному в "fmt". Необязательный аргумент
"nls" позволяет задать используемый в функции нацио-
нальный язык. Если "fmt" опущена,"char" должна иметь
формат даты по умолчанию: 'DD-MON-YY'.
- 12 -
Функция Возвращаемое значение
--------------------- --------------------------------------------------------
TO_MULTI_BYTE(char) Преобразование "char" с однобайтовыми символами в
многобайтовые символы.
TO_NUMBER(char[,fmt Преобразование "char" в число по формату "fmt". Нео-
[,nls]]) обязательный аргумент "nls" позволяет задать исполь-
зуемый в функции национальный символ валюты.
TO_SINGLE_BYTE(char) Преобразование "char" с многобайтовыми символами в
однобайтовые символы.
7.5. Групповые функции
Групповые функции имеют значение только в запросах и подзапросах.
Использование DISTINCT позволяет учитывать только различающиеся значения
аргумента "expr". При указании ALL (или по умолчанию) учитываются все значения
"expr". Например, DISTINCT при нахождении среднего значения из 1,1,1, и 3
дает результат 2, тогда как ALL при этой же операции дает результат 1.5.
Функция Возвращаемое значение
------------------- ----------------------------------------------------------
AVG([DISTINCT| Среднее значение "expr", с игнорированием пустых
ALL]expr) (NULL) значений
COUNT({[DISTINCT| Количество строк, в которых "expr" не является пустым
ALL]expr|*}) (NULL) значением. Установка "*" позволяет подсчитать
все выбранные строки, включая строки с NULL значениями
MAX([DISTINCT| Максимальное значение "expr"
ALL]expr)
MIN([DISTINCT| Минимальное значение "expr"
ALL]expr)
STDDEV([DISTINCT| Среднеквадратичное (стандартное) отклонение от "expr"
ALL]expr) с игнорированием пустых (NULL) значений
SUM([DISTINCT| Cумма значений "expr"
ALL]expr)
VARIANCE([DISTINCT| Дисперсия "expr", с игнорированием пустых значений
ALL]expr)
7.6. Прочие функции
Функция Возвращаемое значение
--------------- --------------------------------------------------------------
DUMP(expr[,k Строка символов, содержащая код типа данных, длину в байтах
[,m[,n]]] ) и внутреннее представление "expr". Необязательный аргумент
k позволяет задать представление возвращаемого значения:
8 - восьмеричное, 10 - десятичное, 16 - шестнадцатиричное,
17 - одиночные символы. Необязательный аргумент m задает
начальную позицию в "expr", а необязательный аргумент n
- длину возвращаемого значения, начиная с m.
GREATEST(expr1, Наибольшее значение из перечня. Пеpед сpавнением все выра-
expr2,...) жения пpеобpазуются к типу пеpвого выpажения.
LEAST(expr1, Наименьшее значение из перечня. Пеpед сpавнением все выра-
expr2,...) жения пpеобpазуются к типу пеpвого выpажения.
NVL(n,expr) Если n равно NULL, возвpащает "expr", иначе возвращает n.
n и "expr" могут быть любого типа. Тип возвpащаемой вели-
чины такой же как для n.
UID Целое число, уникальным образом идентифицирующее текущего
пользователя.
USER Имя текущего пользователя.
- 13 -
Функция Возвращаемое значение
--------------- --------------------------------------------------------------
USERNV(char) Информация о среде текущего сеанса. Если "char" равен:
'ENTRYID' - возвращается доступный идентификатор элемента,
за которым идет слежение;
'LANGUAGE' - возвращает используемый язык;
'SESSIONID' - возвращается идентификатор сеанса пользователя;
'TERMINAL' - возвращается идентификатор терминала пользо-
вателя (в терминах операционной системы).
VSIZE(expr) Число байтов во внутpеннем пpедставлении "expr".
8. Форматы
8.1. Числовые форматы (для fmt аргумента функции TO_CHAR)
Элемент Пример Описание
------- --------- ----------------------------------------------------------
9 9999 Количество цифр, определяющих ширину вывода.
0 0999 Вывод ведущих нулей.
$ $9999 Вывод перед значением знака доллара.
B B9999 Вывод пробелов вместо ведущих нулей.
MI 9999MI Вывод знака "-" после отрицательных чисел.
S S9999 Вывод "+" для положительных чисел и "-" для отрицательных.
PR 9999PR Вывод отрицательных чисел в <угловых скобках>.
D 99D99 Вывод десятичного разделителя.
G 9G999 Вывод разделителя групп (например, триад в денежных данных).
C C999 Вывод символа интернациональной денежной единицы.
L L999 Вывод конкретного денежного символа.
Запятая 9,999 Вывод запятой в указанной позиции.
Точка 99.99 Вывод точки в указанной позиции.
V 999V99 Умножение на 10 в N-ой степени, где N - задается в виде
количества девяток после 'V'.
EEEE 9&999EEEE Вывод в экспоненциальной форме.
RN (rn) RN upper- or lower-case Roman numerals (numbers in range 1..3999).
DATE 'DATE` Returns value converted from Julian date to 'MM/DD/YY' date format.
DATE DATE Высвечивание в формате даты 'MM/DD/YY'. Для дат,хранящихся
как числовые величины (ORACLE версия 2).
8.2. Символьные форматы
Символьный формат состоит из буквы 'A' и следующего за ней указания ширины
строки, столбца или выражения в символах. Если значение не укладывается в
указанную ширину, оно будет сдвинуто или обрезано в зависимости от того была
ли выполнена установка SET WRAP или SET TRUNC.
8.3. Форматы дат и времени
Формат Описание
------------------- ----------------------------------------
SCC или СС Век; 'S'- префикс перед датой (до н.э.) с'-'.
YYYY или SYYYY Год; 'S'- префикс перед датой (до н.э.) с'-'.
YYY или YY или Y Последние 3, 2 или 1 цифра(ы) года.
IYYY 4-цифры года в стандарте ISO.
IYY или IY или I Последние 3, 2 или 1 цифра(ы) года в стандарте ISO.
Y,YYY Год с запятой в данной позиции.
SYEAR или YEAR Год прописью. 'S'-префикс перед датой (до н.э.) с'-'.
BC или AD BC/AD - (до н.э.) / ( н.э.).
B.C. или A.D. Аналогично BC/AD.
Q Квартал (четверть) года.
- 14 -
Формат Описание
------------------- ----------------------------------------
MM Номер месяца.
RM Номер месяца римскими цифрами (I..XII; JAN=I).
MONTH Имя месяца, дополненное пробелами до девяти символов.
MON Трехбуквенная аббревиатура имени месяца (JAN,FEB,...).
WW или W Неделя года (1-52) или месяца (1-5).
IW Неделя года в стандарте ISO (1..21 или 1..53).
DDD DD или D Номер дня года (1-366), месяца (1-31) или недели (1-7).
DAY Наименование дня, дополненное пробелами до 9 символов.
DY Трехбуквенная аббревиатура наименования дня.
J День по Юлианскому календарю; количество дней от
31 декабря 4713 до н.э.
AM или PM Указатель часового пояса.
A.M. или P.M. Указатель часового пояса с периодами.
HH или HH12 Время суток, интервал 1-12.
HH24 Время суток, интервал 0-23.
MI Минуты (0-59).
SS или SSSSS Секунды (0-59) или после полуночи (0-86399).
-/.,;: Пунктуация, которая воспроизводится в результате.
"...текст..." Строка в кавычках, которая воспроизводится в результате.
8.4. Префиксы и суффиксы, используемые в форматах
Префикс и суффиксы, которые могут быть добавлены к перечисленным выше кодам:
------------------------------------------------------------------------------
FM "Fill mode" (режим наполнения). Указанный перед MONTH или DAY,
подавляет пробелы, оставляя только результат фактической длины.
FX "Format exact". Указанный в начале формата для функции TO_DATE,
проверяет соответствует ли преобразуемое значение заданному
формату. При несоответствии (например, лишние пробелы, другие
разделители и т.п.) - отвергает преобразование.
TH Порядковое число (напр.,"DDTH" для "4TH").
SP Число прописью (напр.,"DDSP" для "FOUR")
SPTH или THSP Порядковое число прописью (напр., "DDSPTH") для "FOURTH").
Наличие заглавных букв в аббревиатурах или в словах прописью определяется
наличием заглавных букв в соответствующем форматном элементе.
Например, "DAY" дает "MONDAY", "Day" дает "Monday" и "day" дает "monday".
Модель формата даты может также включать в себя знаки пунктуации, такие
как дефис, косую черту, запятые и символьные константы, заключенные в кавычки
(не в апострофы). Пунктуация и константы появляются при выводе.
Например, DDth "of" Month, YYYY дает 15th of February, 1986.
9. Псевдо- столбцы и таблицы
Имя столбца Значение
---------------- ------------------------------------------------------------
sequence.CURRVAL Текущее значение последовательности для текущего сеанса
(sequence.NEXTVAL должно быть объявлено первым)
sequence.NEXTVAL Следующее значение последовательности для текущего сеанса
table.LEVEL 1 - для корня, 2 - для дочернего уровня корня и т.д.
(используется в команде SELECT ... CONNECT BY,
реализующей иерархические структуры)
- 15 -
Имя столбца Значение
---------------- ------------------------------------------------------------
[table.]ROWID Уникальный идентификатор строки таблицы, составленный из
трех шестнадцатиричных значений: BBBBBBB.RRRR.FFFF, где
BBBBBBBB - блок в файле, RRRR - номер записи в этом блоке
(начиная с 0) и FFFF - файл базы данных, содержащий эту
таблицу; (например, 0000000E.000A.0007). Для этого
идентификатора существует специальный тип данных ROWID.
ROWNUM Позиция отдельной строки среди строк, отобранных запросом.
Oracle7 выбирает строки в произвольном порядке и оценивает
ROWNUM перед сортировкой с помощью ORDER BY фразы. Однако,
если ORDER BY использует индексы, то порядок ROWNUM может
отличаться от его порядка без индекса.
Таблица DUAL
Автоматически создается Oracle для каждого пользователя. В ней один столбец
с именем DUMMY и типом данных VARCHAR2(1). В единственной строке этой
таблицы хранится значение 'X'. Отметим, что указанное значение и его описание
не имеет большого значения, так как чаще всего эта таблица используется для
вывода значения какого-либо выражения любого типа, например
SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM DUAL;
- 16 -
10. Предложения (команды) SQL Oracle7
ALTER CLUSTER [schema.]cluster
[PCTUSED integer] [PCTFREE integer] [SIZE integer [K|M] ]
[INITRANS integer] [MAXTRANS integer] [STORAGE storage_clause]
[ALLOCATE EXTENT [( [SIZE integer [K|M] ]
[DATAFILE 'filename'] [INSTANCE integer] )]
ALTER DATABASE [database]
{ MOUNT [!!under!!EXCLUSIVE | PARALLEL] | CONVERT
| OPEN [RESETLOGS | NORESETLOGS] | ARCHIVELOG
| NOARCHIVELOG | RECOVER recover_clause
| ADD LOGFILE [THREAD integer] [GROUP integer] filespec
[, [GROUP integer] filespec] ...
| ADD LOGFILE MEMBER 'filename' [REUSE]
[, 'filename' [REUSE]] ...
TO { GROUP integer
| ('filename' [,'filename'] ...)
| 'filename' }
[, 'filename' [REUSE]
[, 'filename' [REUSE]] ...
TO { GROUP integer
| ('filename' [, 'filename'] ...)
| 'filename' } ] ...
| DROP LOGFILE { GROUP integer
| ('filename' [, 'filename'] ...)
| 'filename' }
[, { GROUP integer
| ('filename' [,'filename'] ...)
| 'filename' } ] ...
| DROP LOGFILE MEMBER 'filename' [, 'filename'] ...
| RENAME FILE 'filename' [, 'filename'] ...
TO 'filename' [, 'filename'] ...
| BACKUP CONTROLFILE TO { 'filename' [REUSE]
| TRACE [!!under!!NORESETLOGS
| RESETLOGS] }
| CREATE DATAFILE 'filename' [, filename] ...
[AS filespec [, filespec] ...
| DATAFILE 'filename' { ONLINE | OFFLINE [DROP] }
| ENABLE [PUBLIC] THREAD integer
| DISABLE THREAD integer
| RENAME GLOBAL_NAME TO database[.domain]...
| RESET COMPATIBILITY
| SET { DBMAC {ON | OFF} | DBHIGH = 'text' | DBLOW = 'text' } }
ALTER FUNCTION [schema.]function COMPILE
ALTER INDEX [schema.]index
[INITRANS integer] [MAXTRANS integer] [STORAGE storage_clause]
ALTER PACKAGE [schema.]package COMPILE [!!under!!PACKAGE | BODY]
ALTER PROCEDURE [schema.]procedure COMPILE
- 17 -
ALTER PROFILE profile
LIMIT [SESSIONS_PER_USER {integer | UNLIMITED | DEFAULT}]
[CPU_PER_SESSION {integer | UNLIMITED | DEFAULT}]
[CPU_PER_CALL {integer | UNLIMITED | DEFAULT}]
[CONNECT_TIME {integer | UNLIMITED | DEFAULT}]
[IDLE_TIME {integer | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_SESSION {integer | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_CALL {integer | UNLIMITED | DEFAULT}]
[COMPOSITE_LIMIT {integer | UNLIMITED | DEFAULT}]
[PRIVATE_SGA {integer [K|M] | UNLIMITED | DEFAULT}]
ALTER RESOURCE COST [CPU_PER_SESSION integer]
[CONNECT_TIME integer]
[LOGICAL_READS_PER_SESSION integer]
[PRIVATE_SGA integer]
ALTER ROLE role { NOT IDENTIFIED | IDENTIFIED {BY password | EXTERNALLY }
ALTER ROLLBACK SEGMENT rollback_segment
{ ONLINE
| OFFLINE
| STORAGE storage_clause }
ALTER SEQUENCE [schema.]sequence
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE]
[ORDER | NOORDER]
ALTER SESSION
{ SET { SQL_TRACE = { TRUE | FALSE }
| GLOBAL_NAMES = { TRUE | FALSE }
| NLS_LANGUAGE = language
| NLS_TERRITORY = territory
| NLS_DATE_FORMAT = 'fmt'
| NLS_DATE_LANGUAGE = language
| NLS_NUMERIC_CHARACTERS = 'text'
| NLS_ISO_CURRENCY = territory
| NLS_CURRENCY = 'text'
| NLS_SORT = { sort | BINARY }
| LABEL = {'text' | DBHIGH | DBLOW | OSLABEL }
| MLS_LABEL_FORMAT = 'fmt'
| OPTIMIZER_GOAL =
{ RULE|ALL_ROWS|FIRST_ROWS|CHOOSE }
... }
| CLOSE DATABASE LINK dblink
| ADVISE {COMMIT | ROLLBACK | NOTHING}
| {ENABLE | DISABLE} COMMIT IN PROCEDURE }
ALTER SNAPSHOT [schema.]snapshot
[PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[STORAGE storage_clause]
[USING INDEX [INITRANS integer] [MAXTRANS integer]
[STORAGE storage_clause] ]
[REFRESH [FAST | COMPLETE | !!under!!FORCE]
[START WITH date] [NEXT date]]
- 18 -
ALTER SNAPSHOT LOG ON [schema.]table
[PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[STORAGE storage_clause]
ALTER SYSTEM
{ {ENABLE | DISABLE} RESTRICTED SESSION
| FLUSH SHARED_POOL
| {CHECKPOINT | CHECK DATAFILES}
[!!under!!GLOBAL | LOCAL]
| SET { RESOURCE_LIMIT = { TRUE | FALSE }
| GLOBAL_NAMES = { TRUE | FALSE }
| MTS_DISPATCHERS = 'protocol, integer'
| MTS_SERVERS = integer
| LICENSE_MAX_SESSIONS = integer
| LICENSE_SESSIONS_WARNING = integer
| LICENSE_MAX_USERS = integer } ...
| SWITCH LOGFILE
| {ENABLE | DISABLE} DISTRIBUTED RECOVERY
| ARCHIVE LOG archive_log_clause
| KILL SESSION 'integer1, integer2' }
ALTER TABLE [schema.]table
[ADD { { column [datatype] [DEFAULT expr] [column_constraint] ...
| table_constraint }
| ( { column [datatype] [DEFAULT expr] [column_constraint] ...
| table_constraint }
[, { column [datatype] [DEFAULT expr] [column_constraint] ...
| table_constraint } ] ... ) } ]
[MODIFY { column [datatype] [DEFAULT expr] [column_constraint]
| (column [datatype] [DEFAULT expr] [column_constraint]
[, column [datatype] [DEFAULT expr] [column_constraint] ] ...) } ]
[PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[STORAGE storage_clause]
[DROP drop_clause] ...
[ALLOCATE EXTENT [( [SIZE integer [K|M] ]
[DATAFILE 'filename']
[INSTANCE integer] )]
[ ENABLE enable_clause
| DISABLE disable_clause ] ...
ALTER TABLESPACE tablespace
{ ADD DATAFILE filespec [, filespec] ...
| RENAME DATAFILE 'filename' [,'filename'] ...
TO 'filename' [,'filename'] ...
| DEFAULT STORAGE storage_clause
| ONLINE
| OFFLINE [!!under!!NORMAL | TEMPORARY | IMMEDIATE]
| {BEGIN | END} BACKUP
| READ ONLY | READ WRITE }
ALTER TRIGGER [schema.]trigger { ENABLE | DISABLE }
- 19 -
ALTER USER user
[IDENTIFIED {BY password | EXTERNALLY}]
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace] ...
[PROFILE profile]
[DEFAULT ROLE { role [, role] ...
| ALL [EXCEPT role [, role] ...] | NONE}]
ALTER VIEW [schema.]view COMPILE
ANALYZE { INDEX [schema.]index
{ { COMPUTE STATISTICS
| ESTIMATE STATISTICS [SAMPLE integer
{ROWS | PERCENT}]
| DELETE STATISTICS }
| VALIDATE STRUCTURE }
| {TABLE [schema.]table | CLUSTER [schema.]cluster}
{ { COMPUTE STATISTICS
| ESTIMATE STATISTICS [SAMPLE integer
{ROWS | PERCENT}]
| DELETE STATISTICS }
| VALIDATE STRUCTURE [CASCADE]
| LIST CHAINED ROWS [INTO [schema.]table] } }
AUDIT {statement_opt | system_priv}
[, {statement_opt | system_priv} ] ...
[BY user [, user] ...]
[BY {SESSION | ACCESS}]
[WHENEVER [NOT] SUCCESSFUL]
AUDIT object_opt [, object_opt] ...
ON { [schema.]object | DEFAULT }
[BY {SESSION | ACCESS}]
[WHENEVER [NOT] SUCCESSFUL]
COMMENT ON { TABLE [schema.]{table | view | snapshot}
| COLUMN [schema.]{table | view | snapshot}.column } IS 'text'
COMMIT [WORK]
[ COMMENT 'text' | FORCE 'text' [, integer] ]
CREATE CLUSTER [schema.]cluster
(column datatype [,column datatype] ... )
[PCTUSED integer] [PCTFREE integer]
[SIZE integer [K|M] ]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[!!under!!INDEX
| [HASH IS column] HASHKEYS integer]
CREATE CONTROLFILE [REUSE]
[SET] DATABASE database
LOGFILE [GROUP integer] filespec [, [GROUP integer] filespec] ...
{RESETLOGS | NORESETLOGS}
DATAFILE filespec [, filespec] ...
[MAXLOGFILES integer] [MAXLOGMEMBERS integer] [MAXLOGHISTORY integer]
[MAXDATAFILES integer] [MAXINSTANCES integer]
[ARCHIVELOG | !!under!!NOARCHIVELOG]
- 20 -
CREATE DATABASE [database]
[CONTROLFILE REUSE]
[LOGFILE [GROUP integer] filespec [,
[GROUP integer] filespec] ...]
[MAXLOGFILES integer ] [MAXLOGMEMBERS integer] [MAXLOGHISTORY integer]
[DATAFILE filespec [, filespec] ...]
[MAXDATAFILES integer] [MAXINSTANCES integer]
[ARCHIVELOG | !!under!!NOARCHIVELOG]
[!!under!!EXCLUSIVE]
[CHARACTER SET charset]
CREATE [PUBLIC] DATABASE LINK dblink
[CONNECT TO user IDENTIFIED BY password]
[USING 'dbstring']
CREATE [OR REPLACE] FUNCTION [schema.]function
[ (argument [!!under!!IN] datatype
[, argument [!!under!!IN] datatype] ...)]
RETURN datatype
{IS | AS} pl/sql_subprogram_body
CREATE INDEX [schema.]index
ON { [schema.]table (column [!!under!!ASC|DESC]
[, column [!!under!!ASC|DESC]] ...)
| CLUSTER [schema.]cluster }
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PCTFREE integer]
[NOSORT]
CREATE [OR REPLACE] PACKAGE [schema.]package
{IS | AS} pl/sql_package_spec
CREATE [OR REPLACE] PACKAGE BODY [schema.]package
{IS | AS} pl/sql_package_body
CREATE [OR REPLACE] PROCEDURE [schema.]procedure
[ (argument [!!under!!IN | OUT | IN OUT] datatype
[, argument [!!under!!IN | OUT | IN OUT] datatype] ...)]
{IS | AS} pl/sql_subprogram_body
CREATE PROFILE profile
LIMIT [SESSIONS_PER_USER {integer | UNLIMITED | DEFAULT}]
[CPU_PER_SESSION {integer | UNLIMITED | DEFAULT}]
[CPU_PER_CALL {integer | UNLIMITED | DEFAULT}]
[CONNECT_TIME {integer | UNLIMITED | DEFAULT}]
[IDLE_TIME {integer | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_SESSION {integer | UNLIMITED | DEFAULT}]
[LOGICAL_READS_PER_CALL {integer | UNLIMITED | DEFAULT}]
[COMPOSITE_LIMIT {integer | UNLIMITED | DEFAULT}]
[PRIVATE_SGA {integer [K|M] | UNLIMITED | DEFAULT}]
CREATE ROLE role
[ !!under!!NOT IDENTIFIED
| IDENTIFIED {BY password | EXTERNALLY} ]
CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment
[TABLESPACE tablespace] [STORAGE storage_clause]
- 21 -
CREATE SCHEMA AUTHORIZATION schema
{ CREATE TABLE command | CREATE VIEW command | GRANT command } ...
CREATE SEQUENCE [schema.]sequence
[INCREMENT BY integer]
[START WITH integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | !!under!!NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE]
[ORDER | !!under!!NOORDER]
CREATE SNAPSHOT [schema.]snapshot
[ [PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause] | [CLUSTER cluster (column [, column]...) ]
[ USING INDEX [PCTFREE integer] [INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace] [STORAGE storage_clause] ]
[ REFRESH [FAST | COMPLETE | !!under!!FORCE] [START WITH date]
[NEXT date]]
AS subquery
CREATE SNAPSHOT LOG ON [schema.]table
[PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object[@dblink]
CREATE TABLE [schema.]table
( { column [datatype] [DEFAULT expr] [column_constraint] ...
| table_constraint}
[, { column [datatype] [DEFAULT expr] [column_constraint] ...
| table_constraint} ]...)
[ [PCTFREE integer] [PCTUSED integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace] [STORAGE storage_clause]
| CLUSTER cluster (column [, column]...) ]
[ ENABLE enable_clause | DISABLE disable_clause ] ...
[AS subquery]
CREATE TABLESPACE tablespace
DATAFILE filespec [, filespec] ...
[DEFAULT STORAGE storage_clause] [!!under!!ONLINE | OFFLINE]
CREATE [OR REPLACE] TRIGGER [schema.]trigger
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column [, column] ...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...
ON [schema.]table
[ [REFERENCING { OLD [AS] old [NEW [AS] new]
| NEW [AS] new [OLD [AS] old] } ]
FOR EACH ROW
[WHEN (condition)] ]
pl/sql_block
- 22 -
CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace] ...
[PROFILE profile]
CREATE [OR REPLACE] [FORCE | !!under!!NOFORCE] VIEW [schema.]view
[(alias [,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
dblink database[.domain]...[@connection_qualifier]
DELETE [FROM] [schema.]{table | view}[@dblink] [alias] [WHERE condition]
DROP CLUSTER [schema.]cluster
[INCLUDING TABLES [CASCADE CONSTRAINTS] ]
DROP [PUBLIC] DATABASE LINK dblink
DROP FUNCTION [schema.]function
DROP INDEX [schema.]index
DROP PACKAGE [BODY] [schema.]package
DROP PROCEDURE [schema.]procedure
DROP PROFILE profile [CASCADE]
DROP ROLE role
DROP ROLLBACK SEGMENT rollback_segment
DROP SEQUENCE [schema.]sequence
DROP SNAPSHOT [schema.]snapshot
DROP SNAPSHOT LOG ON [schema.]table
DROP [PUBLIC] SYNONYM [schema.]synonym
DROP TABLE [schema.]table [CASCADE CONSTRAINTS]
DROP TABLESPACE tablespace [INCLUDING CONTENTS [CASCADE CONSTRAINTS]]
DROP TRIGGER [schema.]trigger
DROP USER user [CASCADE]
DROP VIEW [schema.]view
EXPLAIN PLAN [SET STATEMENT ID = 'text'] [INTO [schema.]table[@dblink]]
FOR statement
GRANT {system_priv | role} [, {system_priv | role}] ...
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH ADMIN OPTION]
- 23 -
GRANT {object_priv | ALL [PRIVILEGES]} [ (column [, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column [, column] ...) ] ] ...
ON [schema.]object
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]
INSERT INTO [schema.]{table | view}[@dblink] [ (column [, column] ...) ]
{VALUES (expr [, expr] ...) | subquery}
LOCK TABLE [schema.]{table | view}[@dblink]
[, [schema.]{table | view}[@dblink] ]...
IN lockmode MODE
[NOWAIT]
NOAUDIT {statement_opt | system_priv}
[, {statement_opt | system_priv} ] ...
[BY user [, user] ...]
[WHENEVER [NOT] SUCCESSFUL]
NOAUDIT object_opt [, object_opt] ...
ON [schema.]object
[WHENEVER [NOT] SUCCESSFUL]
RENAME old TO new
REVOKE {system_priv | role} [, {system_priv | role}] ...
FROM {user | role | PUBLIC}
[, {user | role | PUBLIC}] ...
REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ] ...
ON [schema.]object
FROM {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[CASCADE CONSTRAINTS]
ROLLBACK [WORK]
[ TO [SAVEPOINT] savepoint | FORCE 'text' ]
SAVEPOINT savepoint
SELECT [DISTINCT | !!under!!ALL] { *
| { [schema.]{table | view | snapshot}.*
| expr [ [AS] c_alias ] }
[, { [schema.]{table | view | snapshot}.*
| expr [ [AS] c_alias ] } ] ... }
FROM { (subquery)
| [schema.]{table | view | snapshot}[@dblink] } [t_alias]
[, { (subquery)
| [schema.]{table | view | snapshot}[@dblink] } [t_alias] ] ...
[WHERE condition ]
[ [START WITH condition] CONNECT BY 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] ]
- 24 -
SET ROLE { role [IDENTIFIED BY password]
[, role [IDENTIFIED BY password] ] ...
| ALL [EXCEPT role [, role] ...]
| NONE }
SET TRANSACTION
{ READ ONLY
| READ WRITE
| USE ROLLBACK SEGMENT rollback_segment }
TRUNCATE {TABLE [schema.]table | CLUSTER [schema.]cluster}
[ {!!under!!DROP | REUSE} STORAGE]
UPDATE [schema.]{table | view}[@dblink] [alias]
SET { (column [, column] ...) = (subquery)
| column = { expr | (subquery) } }
[, { (column [, column] ...) = (subquery)
| column = { expr | (subquery) } } ] ...
[WHERE condition]
11. Фразы предложений (команд) SQL Oracle7
ARCHIVE LOG [THREAD integer]
{ { SEQ integer | CHANGE integer | CURRENT | GROUP integer
| LOGFILE 'filename' | NEXT | ALL | START }
[TO 'location'] | STOP }
CONSTRAINT clause
Column constraint:
[CONSTRAINT constraint]
{ [NOT] NULL
| {UNIQUE | PRIMARY KEY}
| REFERENCES [schema.]table [(column)]
[ON DELETE CASCADE]
| CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause] ]
[ EXCEPTIONS INTO [schema.]table
| DISABLE }
Table constraint:
[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
| FOREIGN KEY (column [,column] ...)
REFERENCES [schema.]table [(column [,column] ...)]
[ON DELETE CASCADE]
| CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause] ]
[ EXCEPTIONS INTO [schema.]table[@dblink]
| DISABLE }
- 25 -
DISABLE { { UNIQUE (column [, column] ...)
| PRIMARY KEY
| CONSTRAINT constraint }
[CASCADE]
| ALL TRIGGERS }
DROP { PRIMARY KEY
| UNIQUE (column [, column] ...)
| CONSTRAINT constraint }
[CASCADE]
ENABLE { {UNIQUE (column [, column] ...)
|PRIMARY KEY
|CONSTRAINT constraint}
[USING INDEX [INITRANS integer]
[MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[PCTFREE integer] ]
[EXCEPTIONS INTO [schema.]table ]
| ALL TRIGGERS }
Filespec: Data files:
'filename' [SIZE integer [K|M] ] [REUSE]
Redo log file groups:
{ 'filename'
| ('filename' [, 'filename'] ...)}
[SIZE integer [K|M] ] [REUSE]
RECOVER [AUTOMATIC] [FROM 'location']
{ [DATABASE] [ UNTIL CANCEL
| UNTIL TIME date
| UNTIL CHANGE integer
| USING BACKUP CONTROLFILE ]
| TABLESPACE tablespace [, tablespace] ...
| DATAFILE 'filename' [, 'filename'] ...
| LOGFILE 'filename'
| CONTINUE [DEFAULT]
| CANCEL }
STORAGE ( [INITIAL integer [K|M] ]
[NEXT integer [K|M] ]
[PCTINCREASE integer]
[MINEXTENTS integer]
[MAXEXTENTS integer]
[OPTIMAL {integer [K|M] | NULL}]
[FREELIST GROUPS integer]
[FREELISTS integer] )
WHERE [NOT] condition [{AND | OR} [NOT] condition ] ...
- 26 -
12. Язык PL/SQL - процедурные расширения языка SQL
12.1. Основные конструкции языка PL/SQL
PL/SQL - это блочно-структурированный язык. Структура блока имеет вид:
[ DECLARE
-- описание переменных, констант и пользовательских типов данных ]
BEGIN
-- тело основной программы, в которую могут вкладываться другие
-- блоки (в них также можно вкладывать блоки и т.д.)
[ EXCEPTION
-- драйверы для обработки исключительных ситуаций в программе ]
END;
Программы PL/SQL могут быть неименовынными (анонимными блоками), но чаще
всего используются именованные программы: процедуры, функции, пакеты и триггера
(см. п. 12.7).
12.2. Типы данных, переменные, константы и выражения
12.2.1. Типы данных, доступные в PL/SQL
Кроме типов данных Oracle7 (см. п. 5), PL/SQL поддерживает несколько
дополнительных типов данных и позволяет использовать в своих конструкциях
некоторые основные типы с большим диапазоном.
Тип данных Описание
-------------- ---------------------------------------------------------------
BINARY_INTEGER Этот тип данных и его подтипы NATURAL и POSITIVE применяются для
создания переменных и констант, которые хранят число со знаком.
Двоичные целые числа могут принимать значения в диапазоне от -2
в 31 степени до 2 в 31 степени минус 1.
BOOLEAN Принимается для создания переменных и констант, в которых
хранятся логические значения TRUE и FALSE.
CHAR Есть подтипы CHARACTER и STRING. Максимальный размер 32767.
NUMBER Есть подтипы DEC, DECIMAL, DOUBLE, PRECISION, FLOAT, INT,
INTEGER, NUMERIC, REAL и SMALLINT.
RECORD Используется для создания пользовательских типов записей базы
данных.
TABLE Служит для создания табличных типов данных PL/SQL.
VARCHAR2 Есть подтип VARCHAR. Максимальный размер 32767.
col%TYPE Используется для определения типа данных столбца или переменной
по типу данных другого столбца или переменной, к имени которого
или которой (col) приписан суффикс %TYPE.
tab%ROWTYPE Используется для определения типа данных записи по типу данных
столбцов таблицы, к имени которой (tab) приписан суффикс
%ROWTYPE.
Рассмотрим подробнее типы данных TABLE и RECORD, позволяющие создавать
одномерные массивы и записи, широко используемые в программах PL/SQL.
12.2.2. Таблицы PL/SQL
Таблица PL/SQL - это одномерный массив с неограниченным числом строк. Для
объявления этого массива (таблицы PL/SQL или TABLE) необходимо сначала
определить его тип данных.
- 27 -
Для описания типа данных TABLE используется синтаксис:
TYPE type_name IS TABLE OF { column_type | variable%TYPE |
table.column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER;
где "type_name" - спецификатор типа, используемый в последующих объявлениях
таблиц PL/SQL, и "column_type" - любой из скалярных типов данных: CHAR, DATE или
NUMBER. С помощью атрибута %TYPE можно установить "type_name" соответствующим
типу данных какой-либо переменной (variable) или столбца (table.column).
Имя (например, name_plsql_table), которое описывается табличным типом
данных, называется таблицей PL/SQL. Это описание, размещаемое в разделе DECLARE,
имеет вид:
name_plsql_table type_name;
Ссылки на строки таблицы PL/SQL осуществляются аналогично ссылкам на элементы
одномерного массива:
name_plsql_table(index) ,
где index принадлежит типу BINARY_INTEGER. Например, для ссылки на третью строку
в таблице PL/SQL "ename_tab" следует написать: ename_tab(3).
Для присвоения значения конкретной строке таблицы PL/SQL используется
синтаксис:
name_plsql_table(index) := expr;
Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы
или представления, а также для выборки значений из таблицы PL/SQL, необходимо
использовать цикл. (Примеры таких операций приведены в п. 12.4.)
12.2.3. Записи PL/SQL
Record PL/SQL - это совокупность полей, каждое из которых должно иметь
уникальное имя (в пределах записи). Эти поля могут принадлежать различным
типам данных.
Если создаваемая запись (sotr) соответствует описанию столбцов какой-либо
базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе
DECLARE с помощью атрибута %ROWTYPE:
sotr kadry%ROWTYPE;
В противном случае для объявления записи необходимо сначала определить ее
тип данных. Для описания типа данных RECORD используется синтаксис:
TYPE type_name IS RECORD
( field_name1 {field_type | variable%TYPE | table.column%TYPE
| table%ROWTYPE} [NOT NULL],
field_name2 {field_type | variable%TYPE | table.column%TYPE
| table%ROWTYPE} [NOT NULL],
...);
где "type_name" - спецификатор типа, используемый в последующих объявлениях
записей PL/SQL, и "field_type" - любой тип данных. С помощью атрибута %TYPE
можно установить "type_name" соответствующим типу данных какой-либо переменной
(variable) или столбца (table.column). Атрибут %ROWTYPE позволяет определить
поле как запись, соответствующую описанию столбцов какой-либо базовой таблицы.
- 28 -
При объявлении типа записи можно присвоить ее полям некоторые значения. Если
же для поля вводится ограничение NOT NULL (для предотвращения назначения пустых
значений), то этому полю надо обязательно присвоить значение. Например:
TYPE SotrRecTyp IS RECORD (nomer NUMBER(4) NOT NULL := 1001,
familiy CHAR(20), dolgnost CHAR(14), otdel NUMBER(3) := 102);
Объявление создаваемой записи (например, name_plsql_record) производится в
разделе DECLARE и имеет вид:
name_plsql_record type_name;
Ссылки на отдельные поля записи осуществляются так:
name_plsql_record.field_name;
Для присвоения значения конкретному полю записи используется синтаксис:
name_plsql_record.field_name := expr;
Примеры использования записей в программах PL/SQL приведены ниже.
Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы
или представления, а также для выборки значений из таблицы PL/SQL, необходимо
использовать цикл. (Примеры таких операций приведены в п. 12.4.)
12.2.4. Переменные, константы и выражения
В программах PL/SQL могут использоваться переменные и константы, описываемые
в разделе DECLARE с помощью конструкции вида:
variable_name [CONSTANT] type_name [NOT NULL] [ { := | DEFAULT } expr ]
Например
birthdate DATE;
emp_count SMALLINT := 0;
emp_count SMALLINT DEFAULT 0;
acct_id VARCHAR2(5) NOT NULL := 'AP001';
pi CONSTANT REAL := 3.14159;
area REAL := pi * radius**2;
valid_id BOOLEAN;
valid_id VARCHAR2(5); -- недопустимое вторичное описание valid_id
i, j, k SMALLINT; -- нельзя описывать список; надо:
-- i SMALLINT; j SMALLINT; k SMALLINT;
credit REAL(7,2);
debit credit%TYPE; -- тип данных аналогичный типу данных "credit"
Синтаксис выражения описан в п. 4.3.
12.3. Связь объектов PL/SQL с таблицами базы данных
Чтобы программа PL/SQL могла работать с информацией, содержащейся в базах
данных, необходимо организовать обмен между значениями столбцов таблиц баз
данных и переменными PL/SQL.
Известно, что для выбора информации из таблиц используется SQL предложение
SELECT. При его выполнении Oracle создает специальную рабочую область,
содержащую информацию о самом SELECT, данные, которые требуются для его
выполнения (например, результаты подзапросов), и, наконец, окончательный
результат выполнения SELECT. PL/SQL имеет несколько механизмов доступа к
- 29 -
этой рабочей области. Одним из них является курсор, с помощью которого можно
присвоить имя этой рабочей области и манипулировать содержащейся в ней
информацией, последовательно выбирая строки результата и пересылая значения
столбцов текущей строки в переменные PL/SQL. Существуют и другие механизмы,
не требующее создания явного курсора.
12.3.1. Явный курсор
Курсор - это средство языка SQL, позволяющее с помощью команд OPEN, FETCH и
CLOSE получить построчный доступ к результату запроса к базе данных. (Будем
также называть курсором и сам набор строк, полученный в результате выполнения
запроса.)
Для использования курсора его надо сначала объявить, т.е. дать ему имя и
указать (с помощью предложения SELECT), какие столбцы и строки базовых таблиц
должны быть помещены в набор строк, названный этим именем. Команда OPEN
инициализирует получение указанного набора и установку перед первой его строкой
указателя текущей строки. Команда FETCH служит для установки указателя текущей
строки на следующую запись (первый раз на строку с номером 1) и выборки из
текущей строки курсора значений указанных столбцов с пересылкой их в переменные
PL/SQL. (Выполнением FETCH в цикле можно последовательно выбрать информацию из
всех строк курсора.) Наконец, команда CLOSE позволяет закрыть (удалить из
памяти) набор строк (при этом описание курсора сохраняется и его можно снова
открыть командой OPEN).
Существует модификация ("Курсор в цикле FOR"), позволяющая организовать
последовательный выбор строк объявленного курсора без явного использования
команд OPEN, FETCH и CLOSE.
12.3.1.1. Объявление курсора
Перед работой с курсором его следует объявить в разделе 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;
- 30 -
Кроме того, в нем выражению "(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;
...
<>
INSERT INTO shtat VALUES ...
END;
- 36 -
В следующем примере управление передается вверх к помеченной
последовательности операторов:
BEGIN
...
<>
BEGIN
UPDATE shtat SET ...
...
END;
...
GOTO update_row;
...
END;
Следует отметить, что использование GOTO (особенно в тех случаях, когда метка
предшествует оператору GOTO) может привести к сложным, нераспознаваемым кодам
ошибок, которые трудно обрабатывать. Поэтому реже используйте GOTO, тем более
что этот оператор нельзя использовать для выполнения перехода:
- в IF-блок, LOOP-блок или в другой блок, не включающий текущий;
- из одного предложения IF-оператора к другому;
- из внешнего блока в SUB-блок;
- из обработчика особых ситуаций в текущий блок.
12.4.3. Операторы цикла (LOOP, WHILE...LOOP и FOR...LOOP)
Циклы служат для повторяемого выполнения последовательности команд. В PL/SQL
используются три модификации операторов цикла: LOOP, WHILE...LOOP и FOR...LOOP.
Цикл LOOP имеет следующий синтаксис:
LOOP
последовательность команд;
END LOOP;
и приводит к бесконечному повторению последовательности команд, если внутри
нее нет команд EXIT (выход из цикла), RAISE (вызов обработчика исключительных
ситуаций) или GOTO (безусловный переход). Например,
LOOP
последовательность команд;
IF условие THEN EXIT;
END LOOP;
приведет к выходу из цикла после выполнения последовательности команд, как
только условие станет истинным.
Цикл WHILE предназначен для повторения последовательности команд, пока
условие остается истинным:
WHILE условие LOOP
последовательность команд;
END LOOP;
Наиболее распространен цикл FOR, имеющий следующий синтаксис:
FOR индекс IN [REVERSE] нижняя_граница..верхняя_граница LOOP
последовательность команд;
END LOOP;
- 37 -
Здесь индекс (счетчик циклов) изменяется от нижней до верхней границы с
шагом 1, а при использовании "REVERSE" - от верхней до нижней границы с шагом
-1. Например,
FOR i IN 1..3 LOOP -- для i = 1, 2, 3
последовательность команд; -- цикл выполняется 3 раза
END LOOP;
FOR i IN REVERSE 1..3 LOOP -- для i = 3, 2, 1
последовательность команд; -- цикл выполняется 3 раза
END LOOP;
Отметим, что в последнем случае пределы диапазона указываются в возрастающем,
а не убывающем порядке.
Если нижняя граница равна верхней, последовательность выполняется один раз.
Если нижняя граница больше верхней, последовательность не выполняется, и
управление переходит к следующему за циклом оператору.
Пределы диапазона цикла могут быть литералами, переменными или выражениями,
но должны быть целыми числами. Например, допустимы следующие диапазоны:
j IN -5..5
k IN REVERSE first..last
step IN 0..TRUNC(high/low) * 2
code IN ASCII('A')..ASCII('J')
Объявлять индекс не нужно - он объявлен неявно как локальная переменная типа
integer.
PL/SQL позволяет определять диапазон цикла динамически во время выполнения.
Например:
SELECT COUNT(otdel) INTO shtat_count FROM shtat;
FOR i IN 1..shtat_count LOOP
...
END LOOP;
Значение "shtat_count" - неизвестно во времени компиляции; предложение SELECT
определяет это значение во время выполнения.
Индекс может использоваться в выражениях внутри цикла, но не может изменяться.
Например:
FOR ctr IN 1..10 LOOP
...
IF NOT finished THEN
INSERT INTO ... VALUES (ctr, ...); -- правильно
factor := ctr * 2; -- правильно
...
ELSE
ctr := 10; -- неправильно
END IF;
END LOOP;
Индекс определен только внутри цикла и на него нельзя ссылаться снаружи
цикла. После выполнения цикла индекс неопределен. Например:
FOR ctr IN 1..10 LOOP
...
END LOOP;
sum := ctr - 1; -- неверно
- 38 -
Подобно PL/SQL блокам, циклы могут быть помечены. Метка устанавливается в
начале оператора LOOP, следующим образом:
<>
LOOP
последовательность команд;
END LOOP;
Имя метки может также появляться в конце утверждения LOOP как в примере:
<>
LOOP
...
END LOOP my_loop;
Помеченные циклы используются для улучшения чтения программы (разборчивости).
С любой формой утверждения EXIT можно завершать не только текущий цикл, но и
любой внешний цикл. Для этого маркируйте внешний цикл, который надо завершить,
и используйте метку в утверждении EXIT, следующим образом:
<>
LOOP
...
LOOP
...
EXIT outer WHEN ... -- завершаются оба цикла
END LOOP;
...
END LOOP outer;
Если требуется преждевременно выйти из вложенного цикла FOR, маркируйте
цикл и используйте метку в утверждении EXIT. Например:
<>
FOR i IN 1..5 LOOP
...
FOR j IN 1..10 LOOP
FETCH s1 INTO ShRec;
EXIT outer WHEN s1%NOTFOUND; -- завершаются оба цикла
...
END LOOP;
END LOOP outer;
-- управление передается сюда
12.4.4. Операторы EXIT, EXIT-WHEN и NULL
EXIT используется для завершения цикла, когда дальнейшая обработка
нежелательна или невозможна. Внутри цикла можно помещать один или большее
количество операторов EXIT. Имеются две формы EXIT: EXIT и EXIT-WHEN.
По оператору EXIT цикл завершается немедленно и управление переходит к
следующему за END LOOP оператору. Например:
LOOP
...
IF ... THEN
...
EXIT; -- цикл завершается немедленно
END IF;
END LOOP;
-- управление переходит сюда
- 39 -
По оператору EXIT-WHEN цикл завершиться только в том случае, когда становится
истинным условие в предложении WHEN. Например:
LOOP
FETCH s1 INTO ...
EXIT WHEN s1%NOTFOUND; -- конец цикла, если условие верно
...
END LOOP;
CLOSE s1;
Оператор EXIT-WHEN позволяет завершать цикл преждевременно. Например,
следующий цикл обычно выполняется десять раз, но как только не находится
значение s1, цикл завершается независимо от того сколько раз цикл выполнился.
FOR j IN 1..10 LOOP
FETCH s1 INTO ShRec;
EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки
...
END LOOP;
NULL - пустой оператор; он передает управление к следующему за ним оператору.
Однако, к нему может передаваться управление и его наличие часто улучшает
читаемость программы. Он также полезен для создания фиктивных подпрограмм для
резервирования областей определения функций и процедур при отладке программ.
12.5. Обработка ошибок
Нельзя создать приложение, которое будет безошибочно работать в любых
ситуациях: возможны аппаратные сбои, невыявленные ошибки приложения и ошибки
из-за некорректных действий пользователей приложения (клиентов). Если при этом
программная ошибка произошла в блоке PL/SQL, вложенном в другой блок, а тот,
в свою очередь, вложен в третий блок и т.д., то она может дойти до клиентского
приложения. Чтобы устранить возможную отмену большого объема ранее выполненных
операций и трафик из-за возвращаемых клиенту ошибок, чтобы посылать клиенту
точные сообщения о причине ошибки и способе ее устранения (если она все же
дошла до клиента), разработчики приложения должны предусматривать возможные
программные ошибки и создавать процедуры, адекватно реагирующие на них.
В PL/SQL предусмотрен механизмы перехвата и обработки ошибок, возникающих
при выполнении программы. Эти механизмы называются исключительными ситуациями.
Когда программа обнаруживает заданное условие ошибки, то вызывается
соответствующая исключительная ситуация. Обработки исключительных ситуаций в
программе производится в разделе EXCEPTION (см. п. 12.1).
При обнаружении исключительной ситуации, обработка основного тела программы
останавливается и управление передается соответствующему обработчику
исключительной ситуации, который определяет дальнейшие действия.
В PL/SQL используются следующие типы исключительных ситуаций:
- встроенные исключительные ситуации;
- исключительные ситуации, определяемые пользователем;
- обработчик OTHERS.
12.5.1. Встроенные исключительные ситуации
Oracle включает четырнадцать встроенных исключительных ситуаций,
соответствующих типовым ошибкам, приведенным в следующей таблице:
- 40 -
Ошибка
Исключительная ситуация ORACLE Описание
---------------------- --------- ---------------------------------------------
CURSOR_ALREADY_OPEN ORA-06511 Попытка открытия уже открытого курсора
DUP_VAL_ON_INDEX ORA-00001 Попытка вставить дубликат значения для
уникального индекса
INVALID_CURSOR ORA-01001 Попытка выполнения запрещенной операции с кур-
сором (например, закрытие неоткрытого курсора)
INVALID_NUMBER ORA-01722 Отказ преобразования строки символов в число
LOGIN_DENIED ORA-01017 Неправильное имя пользователь/пароль
NO_DATA_FOUND ORA-01403 Предложение SELECT...INTO возвращает ноль строк
NOT_LOGGED_ON ORA-01012 Нет подключения к Oracle7
PROGRAM_ERROR ORA-06501 Внутренняя ошибка PL/SQL
STORAGE_ERROR ORA-06500 Пакет PL/SQL вышел из пределов памяти или если
память разрушена
TIMEOUT_ON_RESOURCE ORA-00051 Истекло время ожидания ресурса Oracle7
TOO_MANY_ROWS ORA-01422 Предложение SELECT...INTO возвращает более
одной строки
TRANSACTION_BACKED_OUT ORA-00061 Удаленный сервер отменил транзакцию
VALUE_ERROR ORA-06502 Арифметическая ошибка, ошибка преобразования,
усечения или ограничения
ZERO_DIVIDE ORA-01476 Попытка деления на ноль
Если в раздел EXCEPTION программы (блока) включена фраза
WHEN имя_исключения THEN
текст_обработчика_исключения;
с именем какого-либо встроенного исключения и возникла соответствующая ошибка,
то вместо прекращения исполнения программы и выдачи типового сообщения об
ошибке, будет исполняться созданный пользователем текст обработчика исключения.
Такой обработчик может, например, выяснить ситуацию, при которой произошло
деление на ноль, и выдать правдоподобный результат операции деления или
прервать исполнение программы и дать сообщение об изменении каких-либо данных.
В последнем случае это может быть не типовое сообщение "Вы пытаетесь делить на
ноль", а любое подготовленное пользователем сообщение, например, инструкцию
длиной до 2048 символов.
Для выдачи сообщения об ошибке, обеспечения возврата в среду, из которой
вызывалась текущая программа (блок) и отмены всех действий, выполненных в
текущей транзакции, целесообразно использовать процедуру
RAISE_APPLICATION_ERROR(errnum,errtext);
где errnum - отрицательное целое число в диапазоне -20000 .. -20999 и errtext
- символьная строка длиной до 2048 символов.
В приведенном ниже триггере "shtins" использованы два типа встроенных
исключительных ситуаций: NO_DATA_FOUND и TOO_MANY_ROWS.
DROP TRIGGER shtins;
CREATE TRIGGER shtins
BEFORE INSERT ON SHTAT
FOR EACH ROW
DECLARE
nach DATE;
kon DATE;
str NUMBER;
minraz NUMBER;
maxraz NUMBER;
- 41 -
nach_kon EXCEPTION;
err_str EXCEPTION;
nach_nach EXCEPTION;
err_razr EXCEPTION;
err_razr_pr EXCEPTION;
err_stavka EXCEPTION;
BEGIN
SELECT min_razr,max_razr INTO minraz,maxraz FROM dolgnosti
WHERE dolgn = :new.dolgn;
IF :new.razr NOT BETWEEN minraz AND maxraz THEN RAISE err_razr; END IF;
IF :new.razr_proc NOT BETWEEN 50 AND 100 THEN RAISE err_razr_pr; END IF;
IF :new.stavka NOT BETWEEN 0.25 AND 100 THEN RAISE err_stavka; END IF;
IF :new.nachalo > :new.konec THEN RAISE nach_kon; END IF;
SELECT MAX(stroka) INTO str FROM shtat;
IF :new.stroka <> str+1 THEN RAISE err_str; END IF;
<> -- метка блока, в котором производится поиск строк с
-- параметрами, аналогичными вводимым значениям
BEGIN
SELECT nachalo,konec INTO nach,kon FROM shtat
WHERE OTDEL = :new.otdel AND DOLGN = :new.dolgn AND RAZR = :new.razr
AND RAZR_PROC = :new.razr_proc AND KONEC =
(SELECT MAX(konec) FROM shtat
WHERE OTDEL = :new.otdel AND DOLGN = :new.dolgn
AND RAZR = :new.razr AND RAZR_PROC = :new.razr_proc);
IF :new.nachalo <= nach THEN RAISE nach_nach; END IF;
IF :new.nachalo <= kon THEN
UPDATE SHTAT SET konec = (:new.nachalo - 1)
WHERE OTDEL =:new.otdel AND DOLGN = :new.dolgn AND RAZR =:new.razr
AND RAZR_PROC = :new.RAZR_PROC AND konec = kon;
END IF;
EXCEPTION -- начало обработчика исключений блока find_strings
WHEN NO_DATA_FOUND THEN NULL; -- вызывается, если SELECT блока find_strings
-- не возвращает ни одной строки.
END find_strings;
EXCEPTION -- начало обработчика исключений основной программы
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20003,'Должности '||:new.dolgn||' не существует !');
WHEN err_razr THEN
RAISE_APPLICATION_ERROR(-20004,
'Значение разряда не попадает в "вилку" разрядов');
WHEN err_razr_pr THEN
RAISE_APPLICATION_ERROR(-20005,
'Разрядный процент должен находиться в пределах 50-100');
WHEN err_stavka THEN
RAISE_APPLICATION_ERROR(-20006,
'Число ставок должно находиться в пределах 0.25-100');
WHEN nach_nach THEN
RAISE_APPLICATION_ERROR(-20007,
'Дата начала должна быть больше '||to_char(nach));
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20008,'Много строк; обратитесь к АБД.');
WHEN nach_kon THEN
RAISE_APPLICATION_ERROR(-20009,
'Дата начала не может быть больше даты конца');
WHEN err_str THEN
RAISE_APPLICATION_ERROR(-20010,'Неправильный номер вводимой строки');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20011,'Какая-то другая ошибка');
END shtins;
- 42 -
Так как в большом приложении могут часто повторяться встроенные или
пользовательские исключительные ситуации, то целесообразно создать в базе
данных таблицу (например, USERERR) с уникальными номерами (error_number) и
текстами (error_text) исключений. Это позволит избежать определения лишних
сообщений об ошибках и сделать их согласованными во всем приложении.
При использовании такой таблицы и процедуры RAISE_APPLICATION_ERROR надо
описать в в разделе DECLARE блока две переменных (например, errnum типа NUMBER
и errtext типа VARCHAR2) и использовать в обработчике исключений конструкцию:
WHEN TOO_MANY_ROWS THEN
SELECT error_number,error_text INTO errnum,errtext FROM usererr
WHERE error_number = 20008;
RAISE_APPLICATION_ERROR(errnum,errtext);
или
WHEN TOO_MANY_ROWS THEN
SELECT error_number,error_text INTO errnum,errtext FROM usererr
WHERE errtext LIKE 'Много строк; обр%';
RAISE_APPLICATION_ERROR(errnum,errtext);
12.5.2. Исключительные ситуации, определяемые пользователем
Кроме встроенных могут быть использованы собственные исключительные
ситуации, имена которых необходимо описать в разделе DECLARE блока PL/SQL
(например, err_stavka EXCEPTION). В разделе EXCEPTION блока должен быть
описан соответствующий обработчик исключительной ситуации, например
WHEN err_stavka THEN
SELECT error_number,error_text INTO errnum,errtext FROM usererr
WHERE errtext LIKE 'Число ставок должно находиться%';
RAISE_APPLICATION_ERROR(errnum,errtext);
В теле основной программы определяемые пользователем ошибки обычно проверяются
с помощью операторов условия (IF...THEN). Для передачи управления обработчику
пользовательской исключительной ситуации в случае обнаружения ошибки
используется оператор
RAISE имя_пользовательского_исключения
Например
IF :new.stavka NOT BETWEEN 0.25 AND 100 THEN
RAISE err_stavka;
END IF;
12.5.3. Обработчик OTHERS
Если исключительная ситуация не обрабатывается явным образом в блоке и для
ее перехвата не используется обработчик OTHERS, то PL/SQL отменяет выполняемые
блоком транзакции и возвращает необработанную исключительную ситуацию обратно
в вызывающую среду.
Обработчик особых ситуаций OTHERS описывается последним в программе (блоке)
для перехвата всех исключительных ситуаций, которые не были описаны в этой
программе (блоке). Он может иметь вид
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20011,'Какая-то другая ошибка');
- 43 -
12.6. Транзакции
COMMIT - завершает транзакцию, фиксируя изменения в базе данных
ROLLBACK - отменяет изменения, внесенные текущей транзакцией
SAVEPOINT - идентифицирует промежуточную точку сохранения транзакции
(подробно будут описаны позднее)
12.7. О программах PL/SQL
Анонимные блоки, процедуры, функции, пакеты и триггера.
(подробно будут описаны позднее)
12.8. Отладка программ PL/SQL
Oracle7 не поставляется с готовой и полной средой разработки. Поэтому для
успешной разработки с применением PL/SQL нужно освоить некоторые приемы
отладки.
Перед исполнением программы необходимо выполнить две операции:
1. Исполнить команду SET SERVEROUTPUT ON разрешающую вывод на экран
информации, заданной в процедуре
DBMS_OUTPUT.PUT_LINE (текстовая_строка) .
Эта процедура является единственным средством вывода значений переменных
из программ PL/SQL. Например,
DBMS_OUTPUT.PUT_LINE('Пример '||TO_CHAR(SYSDATE,'DD')) .
2. Установить формат даты с помощью команды
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';
Если при выполнении SQL-запроса или программы PL/SQL обнаружены ошибки, то
в первую очередь надо проверить правильность написания в них имен таблиц,
столбцов и др. Для этого можно воспользоваться командой SQL*Plus DESCRIBE,
которая выводит список столбцов для таблицы или спецификацию для функции,
процедуры, пакета. Синтаксис этой команды имеет вид:
DESC[RIBE] {[user.]table [column] | [user.]object[.subobject]}
Пример
SQL> desc kadry
Name Null? Type
------------------------------- -------- ----
NOMER NOT NULL NUMBER(6)
FAMILIYA VARCHAR2(20)
IMYA VARCHAR2(15)
OTCHESTVO VARCHAR2(20)
ROZHDENIE DATE
POL CHAR(1)
IZMEN NOT NULL DATE
- 44 -
Аналогичным образом можно получить структуру любого представления словаря
данных (см. пп. 13 и 14), например, структуру user_objects, где хранится
информация о пользовательских объектах базы данных (INDEX, SEQUENCE, VIEW,
PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE, TABLE, TRIGGER):
desc user_objects
Name Null? Type
---------------- -------- ----
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(13)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(75)
STATUS VARCHAR2(7)
Для получения полного описания всех объектов можно выполнить команду:
select * from user_objects;
а для получения описания процедур
select * from user_objects where object_type = 'PROCEDURE';
OBJECT_NAME OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_T TIMESTAMP STATUS
----------- --------- ----------- ---------- ---------- ------------------- -------
PR_CURS 1928 PROCEDURE 17.11.1996 02.02.1997 1997-02-02:13:37:05 VALID
PR_PRINT 1957 PROCEDURE 24.11.1996 24.11.1996 1996-11-24:16:59:44 INVALID
PR_SHTAT 1970 PROCEDURE 02.01.1997 08.01.1997 1997-01-08:12:38:02 VALID
При возникновении ошибок в командах CREATE PACKAGE, CREATE PACKAGE BODY,
CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, CREATE VIEW их уточнение
можно выполнить с помощью команды SQL*Plus:
SHOW ERR[ORS] [{PACKAGE | PACKAGE BODY | PROCEDURE | FUNCTION |
TRIGGER | VIEW} name]
(подробности будут описаны позднее)
13. Представления словарей данных
После некоторых из перечисленных ниже имен в скобках расположен перечень
букв: (a,u), (d,u) или (a,d,u). Это означает, что такое имя является основой
для имен двух или трех словарей, получаемых за счет добавки префикса ALL_,
DBA_ или USER_. Например, TABLES(a,d,u) основа для имен словарей: ALL_TABLES,
DBA_TABLES и USER_TABLES. При этом префиксом USER снабжаются представления,
наиболее интересные для обычного пользователя, префиксом ALL снабжаются все
доступные пользователю представления и префиксом DBA - доступные пользователям
с системными полномочиями.
Таблицы, представления, синонимы, последовательности
CAT Синоним для USER_CATALOG
CATALOG(a,d,u) Информация о всех таблицах, представлениях, синонимах и
последовательностях базы данных
COL Список столбцов в таблицах пользователя.
COLS Синоним для USER_TAB_COLUMNS
- 45 -
COL_COMMENTS(a,d,u) Комментарии для столбцов таблиц и представлений
CONSTRAINTS(a,d,u) Информация об ограничениях ссылочной целостности в БД
CONS_COLUMNS(a,d,u) Информация о столбцах, участвующих в ограничениях
ссылочной целостности
SEQ Синоним для USER_SEQUENCES.
SEQUENCES(a,d,u) Информация о последовательностях БД
SNAPSHOTS(a,d,u) Информация о снимках в БД
SYN Синоним для USER_SYNONYMS.
SYNONYMS(a,d,u) Информация о синонимах БД
TAB Таблицы пользователя
TAB_COLUMNS(a,d,u) Информация о столбцах таблиц и представлений БД
TAB_COMMENTS(a,d,u) Комментарии для таблиц и представлений БД
TABLES(a,d,u) Информация о таблицах БД
TABS Синоним для USER_TABLES.
VIEWS(a,d,u) Информация о представлениях БД
Объекты
ERRORS(a,d,u) Информация об ошибках компиляции, обнаруженных в БД для
процедур, функций, спецификаций пакета и тел пакета
DEPENDENCIES(a,d,u) Информация о зависимостях объекта в БД
DEPTREE Информация о зависимостях объекта (создается UTLDTREE.SQL)
IDEPTREE Информация о зависимостях объекта (создается UTLDTREE.SQL)
OBJ Синоним для USER_OBJECTS
OBJECTS(a,d,u) Информация об объектах базы данных
OBJECT_SIZE(d,u) Информация о размерах процедур, функций, спецификаций
пакетов и тел пакетов в БД
PUBLIC_DEPENDENCY Информация о зависимостях объекта
SOURCE(a,d,u) Исходный код процедур, функций, спецификаций пакетов и
тел пакетов в БД
TRIGGERS(a,d,u) Информация о триггерах БД
Привилегии
COLUMN_PRIVILEGES Информация о полномочиях для столбцов
COL_PRIVS(a,d,u) Синоним для COLUMN_PRIVILEGES
COL_PRIVS_MADE(a,u) Информация о полномочиях, предоставляемых для столбцов
COL_PRIVS_RECD(a,u) Информация о полномочиях, полученных по конкретным
столбцам
DBA_PROFILES Информация обо всех профилях ограничений ресурсов в БД
DBA_ROLES Информация о ролях в БД
ROLE_PRIVS(d,u) Информация о назначенных пользователю ролях
ROLE_ROLE_PRIVS Информация о ролях, назначенных другим ролям
ROLE_SYS_PRIVS Информация о системных полномочиях, предоставляемых роли
ROLE_TAB_PRIVS Информация о полномочиях на объект, предоставляемых роли
SESSION_PRIVS Информация о полномочиях, предоставленных сеансу
SESSION_ROLES Информация о доступных для сеанса ролях
SYSTEM_PRIVILEGE_MAP Отображение номеров системных полномочий
SYS_PRIVS(d,u) Предоставленные пользователю системные полномочия
TAB_PRIVS(a,d,u) Информация о заданных на объекты полномочиях
TAB_PRIVS_MADE(a,u) Информация о предоставленных на объекты полномочиях
TAB_PRIVS_RECD(a,u) Информация о полученных полномочиях на объекты
TABLE_PRIVILEGES Информация о предоставленных на объекты полномочиях
TABLE_PRIVILEGE_MAP Отображение номеров полномочий
TS_QUOTAS(d,u) Информация о квотах пользователя в табличных областях
USERS(a,d,u) Информация о пользователях БД
USER_RESOURCE_LIMITS Информация о лимитах ресурсов для текущего пользователя
- 46 -
Табличные пространства, кластеры, экстенты, файлы,
CLU Синоним для USER_CLUSTERS
CLUSTERS(d,u) Информация об индексированных и хешированных кластерах
базы данных
CLU_COLUMNS(d,u) Отношение столбцов таблицы к ключам кластера
DBA_DATA_FILES Информация о файлах данных
EXTENTS(d,u) Информация об экстентах объектов в БД
FREE_SPACE(d,u) Информация о свободных экстентах в табличной области БД
SEGMENTS(d,u) Информация о сегментах БД
TABLESPACES(d,u) Информация о табличных областях БД
Индексы
INDEXES(a,d,u) Информация об индексах БД
IND_COLUMNS(a,d,u) Информация об индексах, соответствующих индексам таблицы
IND Синоним для USER_INDEXES.
INDEX_HISTOGRAM Статистическая информация об индексах, генерируемых
командой ANALYZE INDEX VALIDATE STRUCTURE
INDEX_STATS Статистическая информация об индексах, генерируемых
командой ANALYZE INDEX VALIDATE STRUCTURE
Загрузчик данных (SQL*Loader) и экспорт БД
LOADER_COL_INFO Информация о столбцах для SQL*Loader
LOADER_CONSTRAINT_INFO Информация об ограничениях целостности для SQL*Loader
LOADER_INDCOL_INFO Информация об индексированных столбцах для SQL*Loader
LOADER_IND_INFO Информация об индексах для SQL*Loader
LOADER_PARAM_INFO Информация о параметрах для SQL*Loader
LOADER-TAB_INFO Информация о таблицах для SQL*Loader
LOADER_TRIGGER-INFO Информация о триггерах для SQL*Loader
DBA_EXP_FILES Описания файлов экспорта
DBA_EXP_OBJECTS Информация об объектах, инкрементально экспортированных
с помощью утилиты Export
DBA_VERSION Номер версии для последней сессии экспорта
установленной другим сеансом
Блокировки
DBA_DDL_LOCKS Информация о блокировках в результате операций DDL
DBA_DML_LOCKS Информация о блокировках DML на сервере
DBA_LOCKS Информация обо всех блокировках DDL и DML сервера БД
DBA_WAITERS Информация о сеансах, ожидающих отмены блокировки,
DBMS_LOCK_ALLOCATED Информация об определенных пользователем блокировках,
созданных пакетом утилит DBMS_LOCK
Транзакции
DBA_2PC_NEIGHBORS Информация о входящих/исходящих подключениях для
незавершенных распределенных транзакций
DBA_2PC_PENDING Информация о распределенных транзакциях, ожидающих
завершения
DBA_BLOCKERS Сеансы, которые блокируют другие транзакции (см.
соответствующее представление DBA_WAITERS)
DBA_ROLLBACK_SEGS Информация о сегменте отката в БД
- 47 -
Словарь
DICT Синоним для DICTIONARY
DICTIONARY Информация о таблицах и представлениях словаря данных
DICT_COLUMNS Информация о столбцах словаря данных
ERROR_COLUMNS Description of columns in data dictionary tables and views.
Другие представления
DB_LINKS(a,d,u) Информация о связях в базе данных
DBMS_ALERT_INFO Информация о зарегистрированных уведомлениях, созданных
пакетом утилит DBMS_ALERT
GLOBAL_NAME Информация о глобальных именах БД
RESOURCE_COST Информация о ценовых коэффициентах для системных ресурсов
SNAPSHOT_LOGS(d,u) Информация о журналах снимков в БД
Ревизия успешных и безуспешных обращений к объектам базы данных
ALL_DEF_AUDIT_OPTS Информация о заданных по умолчанию параметрах отслеживания
объекта
AUDIT_ACTIONS Отображение номеров действия контрольного журнала и их
описания
AUDIT_OBJECT(d,u) Записи контрольного журнала для отслеживаемых объектов
базы данных
AUDIT_SESSION(d,u) Записи контрольного журнала, касающиеся контрольного
журнала базы данных
AUDIT_STATEMENT(d,u) Записи контрольного журнала для операторов отслеживания
AUDIT_TRAIL(d,u) Совокупность всех записей контрольного журнала
DBA_AUDIT_EXISTS Записи контрольного журнала, созданные по команде
AUDIT_EXISTS
DBA_PRIV_AUDIT_OPTS Информация о параметрах отслеживания для полномочий
DBA_STMT_AUDIT_OPTS Информация об установленных для операторов параметрах
отслеживания
OBJ_AUDIT_OPTS(d,u) Информация об установленных для объектов БД параметрах
отслеживания
STMT_AUDIT_OPTION_MAP Отображение номеров действия контрольного журнала
14. Виртуальные представления словаря данных (доступны пользователю SYS)
V$ACCESS Информация об используемых в настоящее время объектах
V$ARCHIVE Информация об архивированном журнале транзакций БД
V$BACKUP Информация о состоянии архивации всех оперативно доступных
табличных областей БД
V$BGPROCESS Информация о фоновых процессах сервера БД
V$CIRCUITS Информация обо всех подключениях пользователя в конфигурации
мультинитевого сервера
V$DATABASE Информация о БД из контрольного журнала БД
V$DATAFILE Информация о файлах данных
V$DBFILE Информация о файлах данных
V$DB_OBJECT_CACHE Информация об объектах в кэше объектов сервера БД, включая
таблицы, индексы и процедуры
V$DISPATCHERS Информация о текущих фоновых процессах диспетчера, работающих
в мультинитевом сервере БД
V$ENABLEDPRIVS Информация о разрешенных полномочиях
V$FILESTAT Информация о статистике ввода-вывода для файлов БД
V$INSTANCE Информация о текущем состоянии сервера БД (экземпляре)
V$LATCH Информация о внутренних блокировках на сервере БД
- 48 -
V$LATCHHOLDER Информация о сеансах, установивших на сервере БД внутренние
блокировки
V$LATCHNAME Информация о внутренних блокировках на сервере БД
V$LIBRARYCACHE Статистическая информация об управлении библиотечным кэшем
V$LICENSE Информация о лицензионных ограничениях программного
обеспечения Oracle7
V$LOADCSTAT Информация о статистике SQL*Loader, собранной при прямой
загрузке по маршруту
V$LOADTSTAT Информация о статистике SQL*Loader, собранной при прямой
загрузке по маршруту
V$LOCK Информация о блокировках DML на сервере БД
V$LOG Информация о журнале транзакций сервера БД
V$LOGHIST Информация о журнале транзакций сервера БД
V$LOG_HISTORY Информация о журнале транзакций сервера БД
V$LOGFILE Информация о файлах журнала транзакций сервера БД
V$NLS_PARAMETERS Информация о текущих значениях параметра NLS
V$OPEN_CURSOR Информация о каждом открытом курсоре сеанса БД
V$PARAMETER Информация о каждом параметре инициализации сервера БД
V$PROCESS Информация о текущих активных процессах
V$QUEUES Информация об очередях мультинитевого сервера
V$RECOVERY_LOG Информация об архивированных группах журнала транзакций,
необходимых для восстановления БД
V$RECOVER_FILE Информация о файлах, необходимых для восстановления БД
V$REQDIST Информация о состояниях, требующих восстановления файлов
данных
V$RESOURCE Информация о системных ресурсах
V$ROLLNAME Информация об оперативно доступных сегментах отката
V$ROLLSTAT Статистическая информация обо всех оперативно доступных
сегментах отката
V$ROWCACHE Статистическая информация об активности словаря данных
V$SESSION Информация о сеансах БД
V$SESSTAT Статистическая информация о сеансах БД
V$SESSION_WAIT ресурсах, которых ожидает сеанс
V$SESS_IO Информация о вводе-выводе каждого сеанса
V$SGA Информация об области памяти SGA (System Global Area) сервера
V$SGASTAT Статистическая информация об области памяти SGA сервера БД
V$SHARED_SERVERS Информация о разделяемых фоновых серверах мультинитевого
сервера БД
V$SQLAREA Информация о совместно используемых курсорах
V$SQLTEXT Информация об операторах, соответствующих совместно
используемым курсорам
V$STATNAME Информация для кодов статистики сеанса, выводимых в V$SESSTAT
V$SYSSTAT Информация о системной статистике для данных V$SESSTAT
V$THREAD Информация о нитях журнала транзакций БД
V$TIMER Текущее системное время в сотых долях секунды
V$TRANSACTION Информация о текущих транзакциях БД
V$TYPE_SIZE Информация о компонентах нижнего уровня, позволяющих
предсказать потребность в памяти на диске
V$VERSION Информация о версиях библиотек ядра программного обеспечения
сервера Oracle7
V$WAISTAT Статистическая информация о конкуренции за блоки данных
между транзакциями
15. Системные полномочия Oracle7
Для системных полномочий с префиксами в скобках указаны доступные префиксы.
Например, следующая запись таблицы:
CLUSTER(CREATE,CREATE ANY, Создает, изменяет и удаляет кластеры
ALTER ANY,DROP ANY)
- 49 -
дает такие системные полномочия
CREATE CLUSTER Создает кластеры в своей схеме
CREATE ANY CLUSTER Создает кластеры в любой схеме
ALTER ANY CLUSTER Изменяет кластеры в любой схеме
DROP ANY CLUSTER Удаляет кластеры из любой схемы
Системные полномочия Описания
--------------------------- ------------------------------------------------
ANALYZE ANY Анализирует таблицу, индекс или кластер базы
данных с помощью команды ANALYZE
AUDIT ANY Задает для любого объекта базы данных параметры
отслеживания
AUDIT SYSTEM Отслеживает системные события
CLUSTER(CREATE,CREATE ANY, Создает, изменяет и удаляет кластеры
ALTER ANY,DROP ANY)
DATABASE(ALTER) Модифицирует базу данных с помощью команды
ALTER DATABASE
DATABASE LINK(CREATE, Создает и изменяет связи базы данных
CREATE PUBLIC,DROP PUBLIC)
INDEX(CREATE,CREATE ANY, Создает, изменяет и удаляет индексы
ALTER ANY,DROP ANY)
PRIVILEGE(GRANT ANY) Последовательность каких-либо системных полномочий
PROCEDURE(CREATE,CREATE Создает, перекомпилирует, удаляет и выполняет
ANY,ALTER ANY,DROP ANY, процедуры, функции, пакеты и их глобальные
EXECUTE ANY) переменные
PROFILE(CREATE,ALTER,DROP) Создает, изменяет и удаляет профили ограничений
RESOURCE COST(ALTER) Создает для сеанса ценовой коэффициент ресурса
ROLE(CREATE,ALTER ANY, Создает, изменяет, удаляет и назначает роли
DROP ANY,GRANT ANY)
ROLLBACK SEGMENT(CREATE, Создает, изменяет и удаляет сегменты отката
ALTER,DROP)
SESSION(CREATE,ALTER, Создает и изменяет сеансы базы данных (RESTRICTED
RESTRICTED) соответствует созданию ограниченного сеанса)
SEQUENCE(CREATE,CREATE ANY, Создает, изменяет и удаляет последовательности
ALTER ANY,DROP ANY,
SELECT ANY)
SNAPSHOT(CREATE,CREATE ANY, Создает, изменяет и удаляет "снимки" базы данных
ALTER ANY,DROP ANY)
SYNONYM(CREATE,CREATE ANY, Создает и удаляет синонимы
DROP ANY,CREATE PUBLIC,
DROP PUBLIC)
SYSTEM (ALTER) Изменяет параметры базы данных с помощью ALTER SYSTEM
TABLE(CREATE,CREATE ANY, Создает, изменяет, удаляет, архивирует, блокирует,
ALTER ANY,BACKUP ANY,DROP комментирует таблицы и манипулирует ими
ANY,COMMENT ANY,LOCK ANY,
SELECT ANY,DELETE ANY,
INSERT ANY,UPDATE ANY)
TABLESPACE(CREATE,ALTER, Создает, расширяет, архивирует, управляет доступностью
DROP,MANAGE,UNLIMITED) табличных областей и удаляет их, а также использует
пространство табличных областей
TRUNCATE ANY Удаляет все строки из таблицы и (необязательно)
всю выделенную для таблиц память на диске
TRANSACTION(FORCE, Управляет исходом незавершенных системных
FORCE ANY) транзакций
TRIGGER(CREATE,CREATE ANY, Создает, изменяет и удаляет триггера
ALTER ANY,DROP ANY)
- 50 -
USER(CREATE,ALTER,DROP, Создает, изменяет и удаляет пользователей; BECOME
BECOME) требуется для выполнения с помощью утилиты Export
полного экспорта базы данных
VIEW(CREATE,CREATE ANY, Создает и удаляет представления
DROP ANY)
16. Полномочия на объекты
В таблице приведены полномочия на объекты (отмечены символом "*"). С
помощью ALL (All of the object privileges that can be applied.)
Процедуры
Предста- Последова- Функции
Объекты Таблицы ления тельности Пакеты "Снимки"
ALTER * *
DELETE * *
EXECUTE *
INDEX *
INSERT * *
REFERENCES * *
SELECT * * * *
UPDATE * *
17. Основные понятия и параметры, используемые в справочнике
c_alias - псевдоним столбца (может использоваться в SELECT фразе).
cluster -
col_element - определение столбца (для формата или элемента столбца в описании
CREATE TABLE предложения).
condition - условие (возвращает логическое значение TRUE, FALSE или UNKNOWN).
constraint - имя ограничения целостности в описании таблицы.
dblink - Database link.
defoult
expr - любое выражение.
fmt - формат данных.
location - Operating-system-dependent specification of a file's location.
Usually either a device or a directory.
integer - числовая константа или выражение типа NUMBER.
nls - выражение вида 'NLS_SORT = name', где name is either
BINARY or the name of a linguistic sort sequence
null - неопределенное значение.
subquery - подзапрос, используемый в предложениях.
rowid - внутренний уникальный идентификатор строки (тип данных ROWID).
t_alias - псевдоним таблицы. Может использоваться в SELECT предложении.
schema -
scn - номер уникальной идентификации транзакции (System Change Number).
sid,serial# - Уникальные идентификаторы сессии (Session ID and serial number).
M SQL*Plus
С О Д Е Р Ж А Н И Е
1. Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2. Ввод и редактирование команд языков SQL*Plus, SQL и PL/SQL . . . . . 4
2.1. Командная строка SQL*Plus . . . . . . . . . . . . . . . . . . . 4
2.2. Использование строкового редактора SQL*Plus . . . . . . . . . . 5
2.3. Использование внешнего (системного) редактора текста . . . . . . 7
2.4. Размещение комментариев в командных файлах . . . . . . . . . . . 7
3. Выполнение командных файлов . . . . . . . . . . . . . . . . . . . . 7
4. Динамически изменяющиеся программы . . . . . . . . . . . . . . . . . 8
4.1. О динамически изменяющихся программах . . . . . . . . . . . . . 8
4.2. Определение подставляемых переменных . . . . . . . . . . . . . . 9
4.3. Управление подстановкой переменных . . . . . . . . . . . . . . . 10
4.4. Различия в использовании префиксов & и && . . . . . . . . . . . 10
4.5. Связь с пользователем и операционной системой . . . . . . . . . 11
5. Сохранение результатов . . . . . . . . . . . . . . . . . . . . . . . 11
6. Изменение окружающей среды . . . . . . . . . . . . . . . . . . . . .
7. Работа с переменными . . . . . . . . . . . . . . . . . . . . . . . .
8. Показ столбцов таблицы . . . . . . . . . . . . . . . . . . . . . . .
9. Проектирование простых отчетов . . . . . . . . . . . . . . . . . . .
10. Соединение с другими базами данных . . . . . . . . . . . . . . . . .
11. Команды SQL*Plus
@ (at), @@ Выполняет содержимое указанного командного файла . . . . .
/ (slash) Запускает команду SQL или блок PL/SQL из буфера SQL . . .
ACCept Организует ввод текста в переменную пользователя . . . . .
Append Добавляет текст к концу текущей строки в буфере SQL . . .
BREak Определяет, какие события вызовут прерывание и какие
действия необходимо выполнить в случае прерывания . . . .
BTItle Размещает заголовок в конце каждой страницы отчета . . . .
Change Изменяет текст текущей строки в буфере SQL . . . . . . . .
CLear Очищает описание прерываний, буфер SQL, описание
столбцов и т.д. . . . . . . . . . . . . . . . . . . . . .
COLumn Позволяет форматировать значения и заголовки столбцов . .
Возможно использование следующих параметров:
ALIas псевдоним столбца или выражения . . . . . . . . . .
CLEar очистка описания столбца . . . . . . . . . . . . . .
FOLD_After перевод строки после заголовка и каждой строки . . .
FOLD_Before перевод строки перед заголовком и каждой строкой . .
FORmat формат показа значений столбца . . . . . . . . . . .
HEAding изменение заголовка столбца . . . . . . . . . . . .
JUStify выравнивание заголовка столбца . . . . . . . . . . .
LIKE копирование параметров показа другого столбца . . .
NEWLine вывод значений столбца с новой строки . . . . . . .
NEW_Value описание переменной для значения столбца . . . . . .
NOPRInt|PRInt выключение или включение печати столбца . . . . . .
NULl символ(ы) для изображения NULL-значения . . . . . .
ON|OFF включает или отключает признаки показа столбца . . .
OLD_Value описание переменной для значения столбца . . . . . .
WRApped перенос слишком широкого текста на следующую строку
WORd_wrapped аналогично WRAPPED, но без разрыва слов . . . . . .
TRUncated округление широких значений с плавающей точкой . . .
COMPute Вычисляет и печатает итоговые строки отчета . . . . . . .
CONNect Присоединяет заданного пользователя к ORACLE . . . . . . .
COPY Копирует данные из таблиц одной базы данных в другую . . .
DEFine Определяет пользовательскую переменную . . . . . . . . . .
DEL Удаляет текущую строку из буфера SQL . . . . . . . . . . .
DESCribe Показывает краткое описание таблицы . . . . . . . . . . .
DISConnect Завершает сеанс работы с базой данных (но не с SQL*Plus) .
- 2 -
EDIT Вызывает системный редактор текста . . . . . . . . . . . .
EXEcute Выполняет отдельное утверждение PL/SQL . . . . . . . . . .
EXIT Завершает работу SQL*Plus . . . . . . . . . . . . . . . .
GET Загружает файл в буфер SQL . . . . . . . . . . . . . . . .
HELP Выдает информацию о командах языка SQL*Plus, PL/SQL, SQL .
HOst Выполняет команду ОС, не выходя из SQL*Plus . . . . . . .
Input Добавляет строку после текущей строки буфера SQL . . . . .
List Показывает строки буфера SQL . . . . . . . . . . . . . . .
PAUse Показывает заданное сообщение и ожидает нажатия Enter . .
PRInt Показывает текущее значение связывающей переменной . . . .
PROMPT Посылает на экран сообщение или незаполненную строку . . .
REMark Начинает комментарий в командном файле . . . . . . . . . .
Run Запускает команду SQL или блок PL/SQL из буфера SQL . . .
RUNFORM Запускает команду SQL*Forms . . . . . . . . . . . . . . .
SAVe Сохраняет в файле содержимое буфера SQL . . . . . . . . .
SET Устанавливает окружающую среду для текущей сессии . . . .
Возможно использование следующих параметров:
ARRAYsize число строк, выбираемых одновременно из БД . . . .
AUTOcommit управляет передачей изменений в БД . . . . . . . .
BLOckterminator устанавливает символ, завершающий блок PL/SQL . .
CMDSep устанавливает символ, разделяющий команды SQL*Plus
COMpatibility определяет версию ORACLE, с которой Вы связаны . .
CONcat устанавливает символ, ??? . . . . . . . . . . . .
COPYCommit запускает команду COMMIT после копирования N строк
DEFine определяет первый символ подставляемой переменной
ECHO включает (выключает) эхо для командного файла . .
EMBEDDED устанавливает новую страницу для начала отчета . .
ESCape устанавливает ESCAPE-символ . . . . . . . . . . .
FEEDback устанавливает число высвечиваемых записей запроса
FLUsh (не)разрешает вывод сообщений . . . . . . . . . .
HEAding (не)разрешает вывод заголовков столбцов . . . . .
HEADSep устанавливает символ разделителя заголовков . . .
LINesize устанавливает ширину строки . . . . . . . . . . .
LONG устанавливает максимальную ширину LONG значений .
LONGChunksize устанавливает размер приращений LONG значений . .
MAXData устанавливает максимальную ширину строки SQL*Plus
NEWPage число строк между нижним и верхним заголовком . .
NULL символ(ы) для изображения NULL-значения . . . . .
NUMFormat формат (по умолчанию) для вывода числовых значений
NUMwidth ширина (по умолчанию) для вывода числовых значений
PAGESize устанавливает число строк на странице . . . . . .
PAUse показывает сообщение и ожидает нажатия Enter . . .
RECSEP установка символа сепаратора и места его вывода .
SCAN разрешает обработку подстановки переменной . . . .
SERVEROUTput включает (выключает) вывод переменных на экран . .
SHOWmode показывает старую и новую установку параметров . .
SPAce число пробелов между столбцами при выводе таблицы
SQLCase установка регистра для всего выводимого текста . .
SQLCOntinue символы подсказки командной строки SQL*Plus . . .
SQLNumber подсказка в виде номера строки для продолжения . .
SQLPREfix устанавливает символ префикса SQL . . . . . . . .
SQLPrompt устанавливает подсказку командной строки . . . . .
SQLTerminator устанавливает символ завершения команды SQL . . .
SUFfix расширение для имени командного файла для SQL*Plus
TAB использование символа табуляции для форматирования
TERMout разрешает (запрещает) отображение результата . . .
TIme отображает текущее время перед подсказкой . . . .
- 3 -
TIMIng выдает временную статистику по выполнению команды
TRIMout отсекает пробелы в конце каждой выводимой строки .
UNDerline устанавливает символ для подчеркивания заголовков
VERify выводит команду до и после подстановки переменной
WRAp разрешает (запрещает) перенос широких текстов . .
SHOw Изображает установку системной переменной . . . . . . . .
Отображает параметры:
ALL все параметры . . . . . . . . . . . . . . . . . . . . . .
BTItle текущее определение BTITLE . . . . . . . . . . . . . . . .
ERRors ошибки компиляции хранимой процедуры . . . . . . . . . . .
LABEL уровень безопасности для текущей сессии . . . . . . . . .
LNO текущий номер строки . . . . . . . . . . . . . . . . . . .
PNO текущий номер страницы . . . . . . . . . . . . . . . . . .
RELease номер редакции текущей версии ORACLE . . . . . . . . . . .
SPOOl является ли текущий вывод буферизированным . . . . . . . .
SQLCODE код возврата последней выполненной команды SQL . . . . . .
TTItle текущее определение TTITLE . . . . . . . . . . . . . . . .
USER имя пользователя . . . . . . . . . . . . . . . . . . . . .
SPOol Копирует выходные данные в файл или на системный принтер .
SQLPLUS Запускает SQL*Plus . . . . . . . . . . . . . . . . . . . .
STArt Выполняет содержимое указанного командного файла . . . . .
TIMIng Производит анализ производительности команд . . . . . . .
TTItle Размещает заголовок в конце каждой страницы отчета . . . .
UNDEFine Удаляет описание заданной пользовательской переменной . .
VARiable Объявляет связывающую переменную . . . . . . . . . . . . .
WHENEVER Выход из SQL*Plus при возникновении ошибки в команде
OSERROR операционной системы . . . . . . . . . . . . . . . . .
WHENEVER Выход из SQL*Plus при возникновении ошибки в команде
SQLERROR SQL или блоке PL/SQL . . . . . . . . . . . . . . . . .
А. Форматирование данных
Б. Функции
В. Псевдо-столбцы
Г. Представления словарей данных Oracle
Д.
1. Введение
SQL*Plus - это интерактивный, управляемый командами интерфейс с системой
Oracle, используемый для работы с базами данных (создание и изменение описания
таблиц, ввод и модификация данных, отладка запросов и программ, некоторые ад-
министративные функции и т.п.), а также написания несложных отчетов. Для этого
существует следующий набор команд:
@ ("at" - запуск командного файла), @@ (двойное "at" - запуск вложенного
командного файла), / (слэш - запуск команды из буфера SQL), ACCEPT (Принять),
APPEND (Добавить),BREAK (Прервать),BTITLE (Заголовок внизу),CHANGE (Изменить),
CLEAR (Очистить), COLUMN (Столбец), COMPUTE (Вычислить), CONNECT (Соединить),
COPY (Копировать), DEFINE (Определить), DEL (Удалить из буфера), DESCRIBE
(Описать), DISCONNECT (Разъединить), EDIT (Редактировать),EXECUTE (Выполнить),
EXIT (Выход), GET (Прочитать в буфер), HELP (Организовать помощь), HOST (Вы-
полнить команду операционной системы), INPUT (Ввести), LIST (Вывести список),
PAUSE (Пауза), PRINT (Печатать), PROMPT (Подсказка), REMARK (Примечание),
RUN (Выполнить), SAVE (Сохранить), SET (Установить), SHOW (Показать), SPOOL
(Буферизация ввода/вывода), SQLPLUS (Запуск SQL*Plus),START (Запуск командного
файла), TIMING (Хронометраж), TTILE (Заголовок внизу), UNDEFINE (Отменить оп-
ределение), VARIABLE (Переменная), WHENEVER OSERROR (Организовать обработку
- 4 -
ошибки операционной системы), WHENEVER SQLERROR (Организовать обработку ошибки
выполнения SQL команды).
Кроме того, в SQL*Plus можно использовать команды языка SQL и блоки языка
PL/SQL, являющегося процедурным расширением SQL Oracle.
Большинство примеров, иллюстрирующих использование команд SQL*Plus, будет
приводиться по небольшой базе данных пользователя COOK (пароль - SOUP), кратко
описанной в учебном пособии: Кириллов В.В., Громов Г.Ю. Структуризированный
язык запросов (SQL). - СПб.: ИТМО, 1995. - 92 с.
2. Ввод и редактирование команд языков SQL*Plus, SQL и PL/SQL
2.1. Командная строка SQL*Plus
После запуска SQL*Plus (см. команду SQLPLUS) на экране появится приглашение
SQL>_
для ввода командной строки. В эту строку можно ввести команду SQL, например,
SQL> SELECT Блюдо,Основа FROM Блюда;
или блок команд PL/SQL, например,
SQL> BEGIN
2 :n := 1;
3 END;
4 .
или команду SQL*Plus, например,
SQL> COLUMN ((Белки+Углев)*4.1+Жиры*9.3) HEADING 'Калорийность'
Если текст команды не помещается на одной строке, то его можно разместить
в нескольких строчках (как, кстати, размещен выше блок PL/SQL):
SQL> SELECT | SQL> COLUMN -
2 Блюдо,Основа | > ((Белки+Углев)*4.1+Жиры*9.3) -
3 FROM Блюда | > HEADING -
4 ; | > 'Калорийность'
В этом случае, каждая незавершенная строка команды SQL*Plus должна заканчи-
ваться символом "-", что инициирует вывод подсказки для строки продолжения: по
умолчанию это символ ">", который может быть изменен на любой текст с помощью
команды SET SQLCONTINUE.
Команды SQL и блоки PL/SQL опознаются по ключевому слову (SELECT, DELETE и
т.п.) и не требуют символа продолжения перед переходом на другую строку, так
как "ждут" символа завершения команды: ";" для команд SQL и "." для - PL/SQL.
В данном случае строка продолжения идентифицируется по умолчанию своим поряд-
ковым номером. С помощью команды SET SQLNUMBER можно заменить номер на пригла-
шение "SQL>", которое также может быть изменено, например, на "Введите команду"
с помощью команды SET SQLPROMPT.
Если во время ввода команды обнаружена ошибка, которая находится в строке
ввода, то для ее исправления можно стереть клавишей Backspace конец текста,
включая неправильный текст, и заново ввести правильный его вариант. Если же
ошибка обнаружена в предшествующих строках, то придется либо заново вводить
всю команду, либо воспользоваться одним из редакторов текста. Дело в том, что
текст вводимой (текущей) команды SQL или блока PL/SQL попадает в буфер SQL и
- 5 -
хранится там до ввода другой команды. Текст буфера может модифицироваться либо
с помощью команд редактирования, описанных в п. 2.2, либо с помощью системного
редактора, вызываемого командой EDIT (см. п 2.3). Команды же SQL*Plus не запо-
минаются в буфере и не могут модифицироваться текстовым редактором.
2.2. Использование строкового редактора SQL*Plus
В таблице 2-1 показаны команды, позволяющие проверять, изменять или переза-
пускать команды SQL или блоки PL/SQL без их нового ввода. Большинство из них
(кроме CLEAR, LIST, RUN и SAVE) воздействуют на единственную строку, называе-
мую текущей и помечаемую при просмотре (команда LIST) символом "*".
Таблица 2-1 Команды редактирования
Команда Абривиатура Назначение
------------ ----------- --------------------------------------------------
APPEND text A text добавляет указанный текст (text) в конец текущей
строки
CHANGE C/old/new/ заменяет старый текст (old) на новый (new) в
текущей строке
CHANGE C/text/ удаляет указанный текст (text) из текущей строки
CLEAR BUFFER CL BUFF удаляет все строки из буфера SQL
DEL (нет) удаляет текущую строку, делая текущей следующую
строку
INPUT I переводит в режим ввода строк после текущей строки
(режим заканчивается вводом пустой строки)
INPUT I text добавляет после текущей строки строку, состоящую
из текста (text)
LIST L проказывает все строки буфера SQL, делая текущей
последнюю строку
LIST n L n показывает n-ю строку и делает ее текущей
LIST m n L m n показывает строки от m-й до n-й, делая текущей
n-ю строку
RUN или / R или / запускает текущую команду SQL
SAVE file SAV file сохраняет содержимое буфера SQL в файле file
GET file GET file загружает файл в буфер SQL
Например, если был произведен построчный ввод (см. рис. 2.1,а) составляющих
команды
SELECT Блюдо FROM Блюда WHERE Основа = 'Мясо';
и при этом была допущена ошибка (FRO вместо FROM), то после получения сообще-
ния об ошибке
ORA-0093: FROM keyword not found where expeated
можно вывести с помощью команды LIST (L) содержимое буфера SQL (рис. 2.1,б) с
целью его последующей корректировки.
Сначала заметим, что в "листинге" (и в буфере SQL) отсутствует точка с за-
пятой, завершающая команду SQL. Это упрощает редактирование, позволяя добав-
лять в конец команды новую строку без перестановки точки с запятой.
Теперь для иллюстрации команд редактирования на рис. 2.1,в - 2.1,д показаны
некоторые способы исправления ошибки: добавление символа "M", изменение "FRO"
на "FROM" и, наконец, удаление строки с "FRO" и ввод за второй строкой строки
со словом "FROM". На рис. 2.1,е показан ввод по команде INPUT (I) нескольких
строк для расширения списка выводимой информации, на рис. 2.1,ж - вывод текста
буфера SQL и добавление в конец команды фразы "ORDER BY ename", а на рис.2.1,з
- "листинг" команды после внесения всех перечисленных изменений.
- 6 -
а) б) в) г)
SQL> SELECT SQL> L SQL> L 3 SQL> L 3
2 Блюдо 1 SELECT 3* FRO 3* FRO
3 FRO 2 Блюдо
4 Блюда 3 FRO SQL> A M SQL> C/FRO/FROM/
5 WHERE 4 Блюда 3* FROM 3* FROM
6 Основа='Мясо' 5 WHERE
6* Основа='Мясо'
д) е) ж) з)
SQL> L 3 SQL> L 2 SQL> L SQL> L
3* FRO 2* Блюдо 1 SELECT 1 SELECT
2 Блюдо 2 Блюдо
SQL> DEL SQL> I 3 ,Выход 3 ,Выход
3i ,Выход 4 ,Труд 4 ,Труд
SQL> L 2 4i ,Труд 5 FROM 5 FROM
2* Блюдо 5i 6 Блюда 6 Блюда
SQL> I FROM SQL> 7 WHERE 7 WHERE
8* Основа='Мясо' 8 Основа='Мясо'
SQL> I ORDER BY Блюдо 9* ORDER BY Блюдо
Рис. 2.1. Использование строкового редактора SQL*Plus
После этого можно использовать команду RUN (R) или / для выполнения команды
и получения следующего результата:
SQL> /
Блюдо Выход Труд
---------------- ----- ----
Бастурма 300. 5
Бефстроганов 210 6
Мясо с гарниром 250. 3
Салат мясной 200. 4
Суп харчо 500. 5
Заметим, что при выполнении команды RUN перед выводом результата был бы вы-
веден текст команды, аналогичный тексту рис. 2.1,з, на с "R" а не "L" в первой
строке.
Для сохранения текста исправленной команды (буфера SQL) в файле, например,
с именем "dish.sql" следует выполнить команду
SQL> SAV dish
расширение имени файла "sql", характеризующее его как файл запроса, автомати-
чески добавляется системой и может быть изменено на другое, если оно будет яв-
но указано в тексте команды (например, dish.zap).
Сохраненный запрос можно выполнить с помощью команд "START" или "@", напри-
мер,
@dish
Возможна также пересылка текста запроса из файла в буфер SQL с помощью ко-
манды GET, например
SQL> GET dish
Если не указано расширение имени файла, то SQL*Plus ищет файл с заданным име-
нем и расширением SQL (для примера dish.sql). Отредактированный текст буфера
- 7 -
можно сохранить с помощью команды SAVE и (или) выполнить с помощью команды RUN
(R) или /.
2.3. Использование внешнего (системного) редактора текста
При выполнении команды
SQL> EDIT
будет вызван текстовый редактор операционной системы, в который будет помещено
содержимое буфера SQL (например, строки 1 - 9 рис. 2.1,з). Редактирование тек-
ста буфера ничем не от редактирования любого текста в вызванном редакторе. По
окончании редактирования обновленный текст следует сохранить в буфере (команда
SAVE текстового редактора) и, если необходимо, в файле (команда SAVE AS... тек-
стового редактора). Отредактированную команду SQL можно исполнить введя в ко-
мандную строку "R", "/" или "@файл" (файл - имя командного файла, где сохранен
текст отредактированной команды).
С помощью системного редактора можно создавать и редактировать файлы, сос-
тоящие из любых допустимых последовательностей команд SQL, PL/SQL и SQL*Plus,
а затем выполнять их с помощью команд "START", "@" или "@@" (см. п. 3). Для
создания (редактирования) такого файла, обычно называемого командным файлом,
надо выполнить команду
SQL> EDIT <имя файла>
2.4. Размещение комментариев в командных файлах
Существует три способа ввода комментариев в текст командного файла: команда
REMARK SQL*Plus, ввод двух дефисов (--) перед строкой комментария и помещение
текста комментария между ограничителями начала (/*) и конца (*/) комментария.
REM и -- надо ставить перед каждой строкой многострочных комментариев, а в
ограничители можно заключать любое число строк комментария. Команда REM должна
появляться в начале строки, а -- может вводиться после текста комментируемой
команды или ее части. При этом такие комментарии не могут быть продолжены на
следующей строке.
Отметим, что строку с командой SQL*Plus нельзя завершать комментариями, на-
чинающимися на --, так как - признак незавершенности команды (см. п. 2.1).
Примеры:
REM Замена заголовка | /* Замена заголовка
-- В на Вид_блюда | В на Вид_блюда */
COL В HEA Вид_блюда | COL В HEA Вид_блюда
SELECT -- выбрать | SELECT /* выбрать */
Блюдо,Основа,Выход | Блюдо,Основа,Выход
FROM Блюда -- из Блюда | FROM Блюда /* из Блюда */
WHERE -- где | WHERE -- где
Основа='Мясо' | Основа='Мясо'
ORDER BY -- упорядочить по | ORDER BY -- упорядочить по
Блюдо; | Блюдо;
3. Выполнение командных файлов
Как уже указывалось выше, командные файлы (файлы, содержащие набор команд
языков SQL, PL/SQL и SQL*Plus) можно выполнять с помощью команд START, @ или
@@.
Команды START и @ эквивалентны. В них после имени команды указывается имя
командного файла, а затем список аргументов (если они есть). В самих командных
файлах параметры, значения которых будут замещаться значениями аргументов из
указанного выше списка, должны иметь имена &1, &2, ..., т.е. состоять из сим-
- 8 -
вола амперсанда (&), за которым следует число. Это число определяет порядковый
номер аргумента в списке аргументов команд START или @.
Если параметр представляет собой символьное значение или дату, то он должен
быть заключен в апострофы.
Например, если в таблице Блюда необходимо отыскивать блюдо по основному про-
дукту (Основа) и трудоемкости приготовления (Труд), то можно создать командный
файл osn_trud.sql с содержимым вида:
SELECT БЛ, Блюдо FROM Блюда WHERE Основа = '&1' AND Труд = &2;
Если требуется найти овощные блюда с трудоемкостью 3, то можно дать запрос:
SQL> START osn_trud "Овощи" 3 или SQL> @osn_trud "Овощи" 3
где аргументы, состоящие из несколько слов и (или) написанные русскими буквами,
следует заключать в кавычки.
Наконец, если опустить список аргументов, то при определенных условиях (см.
п. 4) SQL*Plus запросит значения аргументов при выполнении командного файла.
Например, если в файле osn_trud.sql сохранен вариант с параметрами &1 и &2 и их
значения еще не определялись в предыдущих вызовах или были удалены командой
SET UNDEFINE, то вызов
SQL> @osn_trud
приведет к появлению запроса
Enter value for 1: _
После ввода значения "Овощи" появится запрос на ввод следующего аргумента. А
затем может быть показано выполняемое преобразование аргументов (см.SET VERIFY
в п. 4) и ответ на запрос, т.е. на экране терминала будет расположены строки:
SQL> @osn_trud
Enter value for 1: "Овощи"
Enter value for 2: 3
old 1: SELECT БЛ, Блюдо FROM Блюда WHERE Основа = '&1' AND Труд = &2
new 1: SELECT БЛ, Блюдо FROM Блюда WHERE Основа = 'Овощи' AND Труд = 3
БЛ Блюдо
-- ----------------
1 Салат летний
17 Морковь с рисом
Командный файл может содержать не только команды, но и вызовы командных фай-
лов, т.е. допустимы вложенные командные файлы и глубина вложенность не лимити-
рована. Когда в процессе выполнения командного файла SQL*Plus встречает коман-
ду START файл или @файл, то он начинает поиск указанного файла в текущем ката-
логе. Если там его нет, то поиск производится во всех подкаталогах текущего
каталога.
Команда @@файл отличается от @файл (START файл) только тем, что она отыски-
вает вложенные командные файлы только в текущем каталоге и с ее помощью нельзя
передать в командный файл значения аргументов.
4. Динамически изменяющиеся программы
4.1. О динамически изменяющихся программах
В п. 3 была показана возможность получения различных результатов по одной и
той же программе. Еще большие возможности появились бы у пользователя если бы
- 9 -
он смог динамически изменять не только исходные данные, но и структуру прог-
раммы (запроса, командного файла и т.п.). SQL*Plus предоставляет такую возмож-
ность с помощью, так называемых, подставляемых переменных.
Подставляемую переменную размещают вместо какой-либо части команды SQL или
SQL*Plus (списка столбцов, WHERE-фразы и т.п.) и перед выполнением команды вво-
дят в переменную тот или иной текст этой части. Текст замещает имя переменной,
изменяя тем самым структуру команды.
Например, если существуют три переменные var1,var2 и var3, значения которых
соответственно равны: "БЛ, Блюдо", "Блюда" и "Основа = 'Овощи'", то по запросу
SQL> SELECT &var1 FROM &var2 WHERE &var3;
SQL*Plus покажет (или не покажет - см. п. 4.3) преобразование команды
old 1: SELECT &var1 FROM &var2 WHERE &var3
new 1: SELECT БЛ, Блюдо FROM Блюда WHERE Основа = 'Овощи'
и выдаст следующие результаты
БЛ Блюдо
-- ----------------
1 Салат летний
3 Салат витаминный
17 Морковь с рисом
23 Помидоры с луком
Если же создана переменная var4 со значением
"БЛ, Блюдо FROM Блюда WHERE Основа = 'Овощи'"
то по команде
SQL> SELECT &var4;
может быть показано преобразование команды
old 1: SELECT &var4
new 1: SELECT БЛ, Блюдо FROM Блюда WHERE Основа = 'Овощи'
и получен результат, аналогичный результату предыдущей команды.
Легко заметить, что при размещении переменной в тексте изменяемой команды
к ее имени должен быть добавлен префикс & (далее будет рассмотрено и использо-
вание другого префикса - &&). Встретив имя переменной с префиксом, SQL*Plus
подставляет вместо них значение этой переменной.
Замещать можно любой текст команды кроме ее первого слова (имени команды),
а также команд строкового редактора текста и команд, где замена бессмысленна,
например, REMARK, TIMING и т.п.
4.2. Определение подставляемых переменных
Для определения переменных используется команда DEFINE, которая позволяет:
1. Определить текстовую (типа CHAR) переменную пользователя.
Например, для задания используемой выше переменной var4 использовалась команда
SQL> DEFINE var4 = "БЛ, Блюдо FROM Блюда WHERE Основа = 'Овощи'" .
Если присваемое переменной значение - это строка чисто буквенных латинских сим-
волов, то ее можно не заключать в кавычки.
- 10 -
2. Просмотреть значение и тип всех или указанной переменной.
Например, если определены только указанные выше переменные var1 - var4 и надо
получить их описание, то надо дать команду DEFINE без аргументов:
SQL> DEFINE
DEFINE var1 = "БЛ, Блюдо" (CHAR)
DEFINE var2 = "Блюда" (CHAR)
DEFINE var3 = "Основа = 'Овощи'" (CHAR)
DEFINE var4 = "БЛ, Блюдо FROM Блюда WHERE Основа = 'Овощи'" (CHAR)
По команде
SQL> DEFINE var4
будет выведена последняя строка показанного выше списка, т.е. описание указан-
ной в DEFINE переменной var4.
При помощи команды DEFINE может быть определена только переменная типа CHAR.
Команда ACCEPT дает возможность неявно определить переменную типа NUMBER (см.
п. 4.5).
4.3. Управление подстановкой переменных
Существует несколько команд, позволяющих изменять стандартные (по умолчанию)
режимы показа и подстановки переменных:
SET SCAN - разрешает (ON) или запрещает (OFF) подстановку переменных.
SET DEFINE - определяет символ подстановки (по умолчанию "&") или действует
аналогично SCAN (ON/OFF).
SET VERIFY - разрешает (ON) или запрещает (OFF) показ преобразования коман-
ды в процессе подстановки переменных (см. п. 4.1).
SET CONCAT - определяет разделитель (по умолчанию - точка) текста окончания
слова и имени переменной, содержащей начало этого слова.
Поясним подробнее проблемы, возникающие при замене значением переменной на-
чала, середины или конца какого-либо слова. При дополнении конца слова разде-
лителем символов слова и имени переменной является сам символ подстановки (&).
При установке переменной в начало или середину слова нельзя без специального
разделителя выявить, где же закончилось имя переменной и где начались символы
слова. Например, если потребовалось получить из таблицы Продукты сведения о
содержании белков, жиров и углеводов в луке и муке, то можно дать запрос
SQL> SELECT Продукт,Белки,Жиры,Углев FROM Продукты
WHERE Продукт IN ('Лук','Мука');
Используя для "сокращения" текста запроса подставляемую переменную x со значе-
нием "ук", преобразуем этот запрос следующим образом
SQL> SELECT Продукт,Белки,Жиры,Углев FROM Продукты
WHERE Продукт IN ('Л&x.','М&x.а');
Здесь для разделения частей слов и именем подставляемой переменной использова-
лись символы "&" и ".". Значение последнего можно изменить командой SET CONCAT.
4.4. Различия в использовании префиксов & и &&
Если подставляемая переменная не определена к моменту ее использования, то
SQL*Plus будет запрашивать значение переменной от пользователя. Например, если
не определена переменная x и дана команда
SQL> SELECT Продукт,Белки,Жиры,Углев FROM Продукты
2 WHERE Продукт IN ('Л&x.','М&x.а');
- 11 -
на экране терминала появится запрос
Enter value for x: _
и после ввода значения "ук" вновь появится аналогичный запрос. Когда же вторич-
но будет введено значение "ук", команда будет выполнена и на экране сохранятся
строки:
SQL> SELECT Продукт,Белки,Жиры,Углев FROM Продукты
2 WHERE Продукт IN ('Л&x.','М&x.а');
Enter value for x: "ук"
Enter value for x: "ук"
old 1: SELECT Продукт,Белки,Жиры,Углев FROM Продукты
WHERE Продукт IN ('Л&x.','М&x.а')
new 1: SELECT Продукт,Белки,Жиры,Углев FROM Продукты
WHERE Продукт IN ('Лук','Мука')
ПРОДУКТ БЕЛКИ ЖИРЫ УГЛЕВ
------- ----- ---- -----
Лук 17 8 95
Мука 106 13 732
Если теперь запросить с помощью команды DEFINE список переменных, то среди них
не будет переменной x - система не сохраняет значений переменных с префиксом &,
введенных с терминала.
В тех случаях, когда вводимое значение подставляемой переменной необходимо
сохранить, следует использовать префикс &&. Например,
SQL> SELECT Продукт,Белки,Жиры,Углев FROM Продукты
2 WHERE Продукт IN ('Л&x.','М&x.а');
Enter value for x: "ук"
old 1: SELECT Продукт,Белки,Жиры,Углев FROM Продукты
WHERE Продукт IN ('Л&x.','М&x.а')
new 1: SELECT Продукт,Белки,Жиры,Углев FROM Продукты
WHERE Продукт IN ('Лук','Мука')
...
Здесь не потребовалось вторично вводить значения переменной x, так как оно
сохранилось в памяти системы после первого ввода.
4.5. Связь с пользователем и операционной системой
В предыдущем пункте было показано, как SQL*Plus запрашивает от пользователя
ввода значения неопределенной подставляемой переменной. Кроме этого SQL*Plus
имеет еще три команды для организации связи с пользователем:
PROMPT - выдает на экран заданное сообщение или подсказку для пользователя,
ACCEPT - запрашивает значение текстовой или числовой переменной,
PAUSE - выдает на экран заданное сообщение и ожидает нажатия клавиши Enter.
Существует команда HOST, позволяющая выполнять командную строку операцион-
ной системы без выхода из SQL*Plus.
Синтаксис и примеры использования всех этих команд приведены в их описании.
CREATE TABLE dept
(deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13) )
CREATE TABLE emp
(empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES emp )
CREATE TABLE bonus
(ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER,
comm NUMBER )
CREATE TABLE salgrade
(grade NUMBER,
losal NUMBER,
hisal NUMBER )
SELECT * FROM dept
DEPTNO DNAME LOC
------- ---------- ---------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO 40 OPERATIONS BOSTON
SELECT * FROM emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ------ --------- ------ ------ -------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SELECT * FROM salgrade
GRADE LOSAL HISAL
----- ----- -----
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
|
