|
|
 |
Краткий справочник по 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
10. Предложения (команды) SQL Oracle7ALTER 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
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 ] ...
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
|
