9. Псевдо- столбцы и таблицы

Имя столбца Значение

---------------- ------------------------------------------------------------

sequence.CURRVAL Текущее значение последовательности для текущего сеанса

(sequence.NEXTVAL должно быть объявлено первым)

sequence.NEXTVAL Следующее значение последовательности для текущего сеанса

table.LEVEL 1 - для корня, 2 - для дочернего уровня корня и т.д.

(используется в команде SELECT ... CONNECT BY,

реализующей иерархические структуры)

- 15 -

Имя столбца Значение

---------------- ------------------------------------------------------------

[table.]ROWID Уникальный идентификатор строки таблицы, составленный из

трех шестнадцатиричных значений: BBBBBBB.RRRR.FFFF, где

BBBBBBBB - блок в файле, RRRR - номер записи в этом блоке

(начиная с 0) и FFFF - файл базы данных, содержащий эту

таблицу; (например, 0000000E.000A.0007). Для этого

идентификатора существует специальный тип данных ROWID.

ROWNUM Позиция отдельной строки среди строк, отобранных запросом.

Oracle7 выбирает строки в произвольном порядке и оценивает

ROWNUM перед сортировкой с помощью ORDER BY фразы. Однако,

если ORDER BY использует индексы, то порядок ROWNUM может

отличаться от его порядка без индекса.

Таблица DUAL

Автоматически создается Oracle для каждого пользователя. В ней один столбец

с именем DUMMY и типом данных VARCHAR2(1). В единственной строке этой

таблицы хранится значение 'X'. Отметим, что указанное значение и его описание

не имеет большого значения, так как чаще всего эта таблица используется для

вывода значения какого-либо выражения любого типа, например

SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM DUAL;

- 16 -

10. Предложения (команды) sql Oracle7

ALTER CLUSTER [schema.]cluster

[PCTUSED integer] [PCTFREE integer] [SIZE integer [K|M] ]

[INITRANS integer] [MAXTRANS integer] [STORAGE storage_clause]

[ALLOCATE EXTENT [( [SIZE integer [K|M] ]

[DATAFILE 'filename'] [INSTANCE integer] )]

ALTER DATABASE [database]

{ MOUNT [!!under!!EXCLUSIVE | PARALLEL] | CONVERT

| OPEN [RESETLOGS | NORESETLOGS] | ARCHIVELOG

| NOARCHIVELOG | RECOVER recover_clause

| ADD LOGFILE [THREAD integer] [GROUP integer] filespec

[, [GROUP integer] filespec] ...

| ADD LOGFILE MEMBER 'filename' [REUSE]

[, 'filename' [REUSE]] ...

TO { GROUP integer

| ('filename' [,'filename'] ...)

| 'filename' }

[, 'filename' [REUSE]

[, 'filename' [REUSE]] ...

TO { GROUP integer

| ('filename' [, 'filename'] ...)

| 'filename' } ] ...

| DROP LOGFILE { GROUP integer

| ('filename' [, 'filename'] ...)

| 'filename' }

[, { GROUP integer

| ('filename' [,'filename'] ...)

| 'filename' } ] ...

| DROP LOGFILE MEMBER 'filename' [, 'filename'] ...

| RENAME FILE 'filename' [, 'filename'] ...

TO 'filename' [, 'filename'] ...

| BACKUP CONTROLFILE TO { 'filename' [REUSE]

| TRACE [!!under!!NORESETLOGS

| RESETLOGS] }

| CREATE DATAFILE 'filename' [, filename] ...

[AS filespec [, filespec] ...

| DATAFILE 'filename' { ONLINE | OFFLINE [DROP] }

| ENABLE [PUBLIC] THREAD integer

| DISABLE THREAD integer

| RENAME GLOBAL_NAME TO database[.domain]...

| RESET COMPATIBILITY

| SET { DBMAC {ON | OFF} | DBHIGH = 'text' | DBLOW = 'text' } }

ALTER FUNCTION [schema.]function COMPILE

ALTER INDEX [schema.]index

[INITRANS integer] [MAXTRANS integer] [STORAGE storage_clause]

ALTER PACKAGE [schema.]package COMPILE [!!under!!PACKAGE | BODY]

ALTER PROCEDURE [schema.]procedure COMPILE

- 17 -

ALTER PROFILE profile

LIMIT [SESSIONS_PER_USER {integer | UNLIMITED | DEFAULT}]

[CPU_PER_SESSION {integer | UNLIMITED | DEFAULT}]

[CPU_PER_CALL {integer | UNLIMITED | DEFAULT}]

[CONNECT_TIME {integer | UNLIMITED | DEFAULT}]

[IDLE_TIME {integer | UNLIMITED | DEFAULT}]

[LOGICAL_READS_PER_SESSION {integer | UNLIMITED | DEFAULT}]

[LOGICAL_READS_PER_CALL {integer | UNLIMITED | DEFAULT}]

[COMPOSITE_LIMIT {integer | UNLIMITED | DEFAULT}]

[PRIVATE_SGA {integer [K|M] | UNLIMITED | DEFAULT}]

ALTER RESOURCE COST [CPU_PER_SESSION integer]

[CONNECT_TIME integer]

[LOGICAL_READS_PER_SESSION integer]

[PRIVATE_SGA integer]

ALTER ROLE role { NOT IDENTIFIED | IDENTIFIED {BY password | EXTERNALLY }

ALTER ROLLBACK SEGMENT rollback_segment

{ ONLINE

| OFFLINE

| STORAGE storage_clause }

ALTER SEQUENCE [schema.]sequence

[INCREMENT BY integer]

[MAXVALUE integer | NOMAXVALUE]

[MINVALUE integer | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE integer | NOCACHE]

[ORDER | NOORDER]

ALTER SESSION

{ SET { SQL_TRACE = { TRUE | FALSE }

| GLOBAL_NAMES = { TRUE | FALSE }

| NLS_LANGUAGE = language

| NLS_TERRITORY = territory

| NLS_DATE_FORMAT = 'fmt'

| NLS_DATE_LANGUAGE = language

| NLS_NUMERIC_CHARACTERS = 'text'

| NLS_ISO_CURRENCY = territory

| NLS_CURRENCY = 'text'

| NLS_SORT = { sort | BINARY }

| LABEL = {'text' | DBHIGH | DBLOW | OSLABEL }

| MLS_LABEL_FORMAT = 'fmt'

| OPTIMIZER_GOAL =

{ RULE|ALL_ROWS|FIRST_ROWS|CHOOSE }

... }

| CLOSE DATABASE LINK dblink

| ADVISE {COMMIT | ROLLBACK | NOTHING}

| {ENABLE | DISABLE} COMMIT IN PROCEDURE }

ALTER SNAPSHOT [schema.]snapshot

[PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[STORAGE storage_clause]

[USING INDEX [INITRANS integer] [MAXTRANS integer]

[STORAGE storage_clause] ]

[REFRESH [FAST | COMPLETE | !!under!!FORCE]

[START WITH date] [NEXT date]]

- 18 -

ALTER SNAPSHOT LOG ON [schema.]table

[PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[STORAGE storage_clause]

ALTER SYSTEM

{ {ENABLE | DISABLE} RESTRICTED SESSION

| FLUSH SHARED_POOL

| {CHECKPOINT | CHECK DATAFILES}

[!!under!!GLOBAL | LOCAL]

| SET { RESOURCE_LIMIT = { TRUE | FALSE }

| GLOBAL_NAMES = { TRUE | FALSE }

| MTS_DISPATCHERS = 'protocol, integer'

| MTS_SERVERS = integer

| LICENSE_MAX_SESSIONS = integer

| LICENSE_SESSIONS_WARNING = integer

| LICENSE_MAX_USERS = integer } ...

| SWITCH LOGFILE

| {ENABLE | DISABLE} DISTRIBUTED RECOVERY

| ARCHIVE LOG archive_log_clause

| KILL SESSION 'integer1, integer2' }

ALTER TABLE [schema.]table

[ADD { { column [datatype] [DEFAULT expr] [column_constraint] ...

| table_constraint }

| ( { column [datatype] [DEFAULT expr] [column_constraint] ...

| table_constraint }

[, { column [datatype] [DEFAULT expr] [column_constraint] ...

| table_constraint } ] ... ) } ]

[MODIFY { column [datatype] [DEFAULT expr] [column_constraint]

| (column [datatype] [DEFAULT expr] [column_constraint]

[, column [datatype] [DEFAULT expr] [column_constraint] ] ...) } ]

[PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[STORAGE storage_clause]

[DROP drop_clause] ...

[ALLOCATE EXTENT [( [SIZE integer [K|M] ]

[DATAFILE 'filename']

[INSTANCE integer] )]

[ ENABLE enable_clause

| DISABLE disable_clause ] ...

ALTER TABLESPACE tablespace

{ ADD DATAFILE filespec [, filespec] ...

| RENAME DATAFILE 'filename' [,'filename'] ...

TO 'filename' [,'filename'] ...

| DEFAULT STORAGE storage_clause

| ONLINE

| OFFLINE [!!under!!NORMAL | TEMPORARY | IMMEDIATE]

| {BEGIN | END} BACKUP

| READ ONLY | READ WRITE }

ALTER TRIGGER [schema.]trigger { ENABLE | DISABLE }

- 19 -

ALTER USER user

[IDENTIFIED {BY password | EXTERNALLY}]

[DEFAULT TABLESPACE tablespace]

[TEMPORARY TABLESPACE tablespace]

[QUOTA {integer [K|M] | UNLIMITED} ON tablespace] ...

[PROFILE profile]

[DEFAULT ROLE { role [, role] ...

| ALL [EXCEPT role [, role] ...] | NONE}]

ALTER VIEW [schema.]view COMPILE

ANALYZE { INDEX [schema.]index

{ { COMPUTE STATISTICS

| ESTIMATE STATISTICS [SAMPLE integer

{ROWS | PERCENT}]

| DELETE STATISTICS }

| VALIDATE STRUCTURE }

| {TABLE [schema.]table | CLUSTER [schema.]cluster}

{ { COMPUTE STATISTICS

| ESTIMATE STATISTICS [SAMPLE integer

{ROWS | PERCENT}]

| DELETE STATISTICS }

| VALIDATE STRUCTURE [CASCADE]

| LIST CHAINED ROWS [INTO [schema.]table] } }

AUDIT {statement_opt | system_priv}

[, {statement_opt | system_priv} ] ...

[BY user [, user] ...]

[BY {SESSION | ACCESS}]

[WHENEVER [NOT] SUCCESSFUL]

AUDIT object_opt [, object_opt] ...

ON { [schema.]object | DEFAULT }

[BY {SESSION | ACCESS}]

[WHENEVER [NOT] SUCCESSFUL]

COMMENT ON { TABLE [schema.]{table | view | snapshot}

| COLUMN [schema.]{table | view | snapshot}.column } IS 'text'

COMMIT [WORK]

[ COMMENT 'text' | FORCE 'text' [, integer] ]

CREATE CLUSTER [schema.]cluster

(column datatype [,column datatype] ... )

[PCTUSED integer] [PCTFREE integer]

[SIZE integer [K|M] ]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

[!!under!!INDEX

| [HASH IS column] HASHKEYS integer]

CREATE CONTROLFILE [REUSE]

[SET] DATABASE database

LOGFILE [GROUP integer] filespec [, [GROUP integer] filespec] ...

{RESETLOGS | NORESETLOGS}

DATAFILE filespec [, filespec] ...

[MAXLOGFILES integer] [MAXLOGMEMBERS integer] [MAXLOGHISTORY integer]

[MAXDATAFILES integer] [MAXINSTANCES integer]

[ARCHIVELOG | !!under!!NOARCHIVELOG]

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]

- 20 -

CREATE [PUBLIC] DATABASE LINK dblink

[CONNECT TO user IDENTIFIED BY password]

[USING 'dbstring']

CREATE [OR REPLACE] FUNCTION [schema.]function

[ (argument [!!under!!IN] datatype

[, argument [!!under!!IN] datatype] ...)]

RETURN datatype

{IS | AS} pl/sql_subprogram_body

CREATE INDEX [schema.]index

ON { [schema.]table (column [!!under!!ASC|DESC]

[, column [!!under!!ASC|DESC]] ...)

| CLUSTER [schema.]cluster }

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

[PCTFREE integer]

[NOSORT]

CREATE [OR REPLACE] PACKAGE [schema.]package

{IS | AS} pl/sql_package_spec

CREATE [OR REPLACE] PACKAGE BODY [schema.]package

{IS | AS} pl/sql_package_body

CREATE [OR REPLACE] PROCEDURE [schema.]procedure

[ (argument [!!under!!IN | OUT | IN OUT] datatype

[, argument [!!under!!IN | OUT | IN OUT] datatype] ...)]

{IS | AS} pl/sql_subprogram_body

CREATE PROFILE profile

LIMIT [SESSIONS_PER_USER {integer | UNLIMITED | DEFAULT}]

[CPU_PER_SESSION {integer | UNLIMITED | DEFAULT}]

[CPU_PER_CALL {integer | UNLIMITED | DEFAULT}]

[CONNECT_TIME {integer | UNLIMITED | DEFAULT}]

[IDLE_TIME {integer | UNLIMITED | DEFAULT}]

[LOGICAL_READS_PER_SESSION {integer | UNLIMITED | DEFAULT}]

[LOGICAL_READS_PER_CALL {integer | UNLIMITED | DEFAULT}]

[COMPOSITE_LIMIT {integer | UNLIMITED | DEFAULT}]

[PRIVATE_SGA {integer [K|M] | UNLIMITED | DEFAULT}]

CREATE ROLE role

[ !!under!!NOT IDENTIFIED

| IDENTIFIED {BY password | EXTERNALLY} ]

CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment

[TABLESPACE tablespace] [STORAGE storage_clause]

- 21 -

CREATE SCHEMA AUTHORIZATION schema

{ CREATE TABLE command | CREATE VIEW command | GRANT command } ...

CREATE SEQUENCE [schema.]sequence

[INCREMENT BY integer]

[START WITH integer]

[MAXVALUE integer | NOMAXVALUE]

[MINVALUE integer | !!under!!NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE integer | NOCACHE]

[ORDER | !!under!!NOORDER]

CREATE SNAPSHOT [schema.]snapshot

[ [PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause] | [CLUSTER cluster (column [, column]...) ]

[ USING INDEX [PCTFREE integer] [INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace] [STORAGE storage_clause] ]

[ REFRESH [FAST | COMPLETE | !!under!!FORCE] [START WITH date]

[NEXT date]]

AS subquery

CREATE SNAPSHOT LOG ON [schema.]table

[PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

CREATE [PUBLIC] SYNONYM [schema.]synonym

FOR [schema.]object[@dblink]

CREATE TABLE [schema.]table

( { column [datatype] [DEFAULT expr] [column_constraint] ...

| table_constraint}

[, { column [datatype] [DEFAULT expr] [column_constraint] ...

| table_constraint} ]...)

[ [PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace] [STORAGE storage_clause]

| CLUSTER cluster (column [, column]...) ]

[ ENABLE enable_clause | DISABLE disable_clause ] ...

[AS subquery]

CREATE TABLESPACE tablespace

DATAFILE filespec [, filespec] ...

[DEFAULT STORAGE storage_clause] [!!under!!ONLINE | OFFLINE]

CREATE [OR REPLACE] TRIGGER [schema.]trigger

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE [OF column [, column] ...]}

[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...

ON [schema.]table

[ [REFERENCING { OLD [AS] old [NEW [AS] new]

| NEW [AS] new [OLD [AS] old] } ]

FOR EACH ROW

[WHEN (condition)] ]

pl/sql_block

- 22 -

CREATE USER user

IDENTIFIED {BY password | EXTERNALLY}

[DEFAULT TABLESPACE tablespace]

[TEMPORARY TABLESPACE tablespace]

[QUOTA {integer [K|M] | UNLIMITED} ON tablespace] ...

[PROFILE profile]

CREATE [OR REPLACE] [FORCE | !!under!!NOFORCE] VIEW [schema.]view

[(alias [,alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

dblink database[.domain]...[@connection_qualifier]

DELETE [FROM] [schema.]{table | view}[@dblink] [alias] [WHERE condition]

DROP CLUSTER [schema.]cluster

[INCLUDING TABLES [CASCADE CONSTRAINTS] ]

DROP [PUBLIC] DATABASE LINK dblink

DROP FUNCTION [schema.]function

DROP INDEX [schema.]index

DROP PACKAGE [BODY] [schema.]package

DROP PROCEDURE [schema.]procedure

DROP PROFILE profile [CASCADE]

DROP ROLE role

DROP ROLLBACK SEGMENT rollback_segment

DROP SEQUENCE [schema.]sequence

DROP SNAPSHOT [schema.]snapshot

DROP SNAPSHOT LOG ON [schema.]table

DROP [PUBLIC] SYNONYM [schema.]synonym

DROP TABLE [schema.]table [CASCADE CONSTRAINTS]

DROP TABLESPACE tablespace [INCLUDING CONTENTS [CASCADE CONSTRAINTS]]

DROP TRIGGER [schema.]trigger

DROP USER user [CASCADE]

DROP VIEW [schema.]view

EXPLAIN PLAN [SET STATEMENT ID = 'text'] [INTO [schema.]table[@dblink]]

FOR statement

GRANT {system_priv | role} [, {system_priv | role}] ...

TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...

[WITH ADMIN OPTION]

- 23 -

GRANT {object_priv | ALL [PRIVILEGES]} [ (column [, column]...) ]

[, {object_priv | ALL [PRIVILEGES]} [ (column [, column] ...) ] ] ...

ON [schema.]object

TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...

[WITH GRANT OPTION]

INSERT INTO [schema.]{table | view}[@dblink] [ (column [, column] ...) ]

{VALUES (expr [, expr] ...) | subquery}

LOCK TABLE [schema.]{table | view}[@dblink]

[, [schema.]{table | view}[@dblink] ]...

IN lockmode MODE

[NOWAIT]

NOAUDIT {statement_opt | system_priv}

[, {statement_opt | system_priv} ] ...

[BY user [, user] ...]

[WHENEVER [NOT] SUCCESSFUL]

NOAUDIT object_opt [, object_opt] ...

ON [schema.]object

[WHENEVER [NOT] SUCCESSFUL]

RENAME old TO new

REVOKE {system_priv | role} [, {system_priv | role}] ...

FROM {user | role | PUBLIC}

[, {user | role | PUBLIC}] ...

REVOKE {object_priv | ALL [PRIVILEGES]}

[, {object_priv | ALL [PRIVILEGES]} ] ...

ON [schema.]object

FROM {user | role | PUBLIC} [, {user | role | PUBLIC}] ...

[CASCADE CONSTRAINTS]

ROLLBACK [WORK]

[ TO [SAVEPOINT] savepoint | FORCE 'text' ]

SAVEPOINT savepoint

SELECT [DISTINCT | !!under!!ALL] { *

| { [schema.]{table | view | snapshot}.*

| expr [ [AS] c_alias ] }

[, { [schema.]{table | view | snapshot}.*

| expr [ [AS] c_alias ] } ] ... }

FROM { (subquery)

| [schema.]{table | view | snapshot}[@dblink] } [t_alias]

[, { (subquery)

| [schema.]{table | view | snapshot}[@dblink] } [t_alias] ] ...

[WHERE condition ]

[ [START WITH condition] CONNECT BY condition]

[GROUP BY expr [, expr] ...] [HAVING condition]

[{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ]

[ORDER BY {expr | c_alias | position} [!!under!!ASC | DESC]

[, {expr | c_alias | position}

[!!under!!ASC | DESC]] ...]

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

[, [[schema.]{table | view}.]column] ...]

[NOWAIT] ]

- 24 -

SET ROLE { role [IDENTIFIED BY password]

[, role [IDENTIFIED BY password] ] ...

| ALL [EXCEPT role [, role] ...]

| NONE }

SET TRANSACTION

{ READ ONLY

| READ WRITE

| USE ROLLBACK SEGMENT rollback_segment }

TRUNCATE {TABLE [schema.]table | CLUSTER [schema.]cluster}

[ {!!under!!DROP | REUSE} STORAGE]

UPDATE [schema.]{table | view}[@dblink] [alias]

SET { (column [, column] ...) = (subquery)

| column = { expr | (subquery) } }

[, { (column [, column] ...) = (subquery)

| column = { expr | (subquery) } } ] ...

[WHERE condition]

11. Фразы предложений (команд) SQL Oracle7

ARCHIVE LOG [THREAD integer]

{ { SEQ integer | CHANGE integer | CURRENT | GROUP integer

| LOGFILE 'filename' | NEXT | ALL | START }

[TO 'location'] | STOP }

CONSTRAINT clause

Column constraint:

[CONSTRAINT constraint]

{ [NOT] NULL

| {UNIQUE | PRIMARY KEY}

| REFERENCES [schema.]table [(column)]

[ON DELETE CASCADE]

| CHECK (condition) }

{ [ USING INDEX [PCTFREE integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause] ]

[ EXCEPTIONS INTO [schema.]table

| DISABLE }

Table constraint:

[CONSTRAINT constraint]

{ {UNIQUE | PRIMARY KEY} (column [,column] ...)

| FOREIGN KEY (column [,column] ...)

REFERENCES [schema.]table [(column [,column] ...)]

[ON DELETE CASCADE]

| CHECK (condition) }

{ [ USING INDEX [PCTFREE integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause] ]

[ EXCEPTIONS INTO [schema.]table[@dblink]

| DISABLE }

- 25 -

DISABLE { { UNIQUE (column [, column] ...)

| PRIMARY KEY

| CONSTRAINT constraint }

[CASCADE]

| ALL TRIGGERS }

DROP { PRIMARY KEY

| UNIQUE (column [, column] ...)

| CONSTRAINT constraint }

[CASCADE]

ENABLE { {UNIQUE (column [, column] ...)

|PRIMARY KEY

|CONSTRAINT constraint}

[USING INDEX [INITRANS integer]

[MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

[PCTFREE integer] ]

[EXCEPTIONS INTO [schema.]table ]

| ALL TRIGGERS }

Filespec: Data files:

'filename' [SIZE integer [K|M] ] [REUSE]

Redo log file groups:

{ 'filename'

| ('filename' [, 'filename'] ...)}

[SIZE integer [K|M] ] [REUSE]

RECOVER [AUTOMATIC] [FROM 'location']

{ [DATABASE] [ UNTIL CANCEL

| UNTIL TIME date

| UNTIL CHANGE integer

| USING BACKUP CONTROLFILE ]

| TABLESPACE tablespace [, tablespace] ...

| DATAFILE 'filename' [, 'filename'] ...

| LOGFILE 'filename'

| CONTINUE [DEFAULT]

| CANCEL }

STORAGE ( [INITIAL integer [K|M] ]

[NEXT integer [K|M] ]

[PCTINCREASE integer]

[MINEXTENTS integer]

[MAXEXTENTS integer]

[OPTIMAL {integer [K|M] | NULL}]

[FREELIST GROUPS integer]

[FREELISTS integer] )

WHERE [NOT] condition [{AND | OR} [NOT] condition ] ...

- 26 -

12. Язык PL/SQL - процедурные расширения языка SQL

12.1. Основные конструкции языка PL/SQL

PL/SQL - это блочно-структурированный язык. Структура блока имеет вид:

[ DECLARE

-- описание переменных, констант и пользовательских типов данных ]

BEGIN

-- тело основной программы, в которую могут вкладываться другие

-- блоки (в них также можно вкладывать блоки и т.д.)

[ EXCEPTION

-- драйверы для обработки исключительных ситуаций в программе ]

END;

Программы PL/SQL могут быть неименовынными (анонимными блоками), но чаще

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

(см. п. 12.7).

12.2. Типы данных, переменные, константы и выражения

12.2.1. Типы данных, доступные в PL/SQL

Кроме типов данных Oracle7 (см. п. 5), PL/SQL поддерживает несколько

дополнительных типов данных и позволяет использовать в своих конструкциях

некоторые основные типы с большим диапазоном.

Тип данных Описание

-------------- ---------------------------------------------------------------

BINARY_INTEGER Этот тип данных и его подтипы NATURAL и POSITIVE применяются для

создания переменных и констант, которые хранят число со знаком.

Двоичные целые числа могут принимать значения в диапазоне от -2

в 31 степени до 2 в 31 степени минус 1.

BOOLEAN Принимается для создания переменных и констант, в которых

хранятся логические значения TRUE и FALSE.

CHAR Есть подтипы CHARACTER и STRING. Максимальный размер 32767.

NUMBER Есть подтипы DEC, DECIMAL, DOUBLE, PRECISION, FLOAT, INT,

INTEGER, NUMERIC, REAL и SMALLINT.

RECORD Используется для создания пользовательских типов записей базы

данных.

TABLE Служит для создания табличных типов данных PL/SQL.

VARCHAR2 Есть подтип VARCHAR. Максимальный размер 32767.

col%TYPE Используется для определения типа данных столбца или переменной

по типу данных другого столбца или переменной, к имени которого

или которой (col) приписан суффикс %TYPE.

tab%ROWTYPE Используется для определения типа данных записи по типу данных

столбцов таблицы, к имени которой (tab) приписан суффикс

%ROWTYPE.

Рассмотрим подробнее типы данных TABLE и RECORD, позволяющие создавать

одномерные массивы и записи, широко используемые в программах PL/SQL.

12.2.2. Таблицы PL/SQL

Таблица PL/SQL - это одномерный массив с неограниченным числом строк. Для

объявления этого массива (таблицы PL/SQL или TABLE) необходимо сначала

определить его тип данных.

- 27 -

Для описания типа данных TABLE используется синтаксис:

TYPE type_name IS TABLE OF { column_type | variable%TYPE |

table.column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER;

где "type_name" - спецификатор типа, используемый в последующих объявлениях

таблиц PL/SQL, и "column_type" - любой из скалярных типов данных: CHAR, DATE или

NUMBER. С помощью атрибута %TYPE можно установить "type_name" соответствующим

типу данных какой-либо переменной (variable) или столбца (table.column).

Имя (например, name_plsql_table), которое описывается табличным типом

данных, называется таблицей PL/SQL. Это описание, размещаемое в разделе DECLARE,

имеет вид:

name_plsql_table type_name;

Ссылки на строки таблицы PL/SQL осуществляются аналогично ссылкам на элементы

одномерного массива:

name_plsql_table(index) ,

где index принадлежит типу BINARY_INTEGER. Например, для ссылки на третью строку

в таблице PL/SQL "ename_tab" следует написать: ename_tab(3).

Для присвоения значения конкретной строке таблицы PL/SQL используется

синтаксис:

name_plsql_table(index) := expr;

Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы

или представления, а также для выборки значений из таблицы PL/SQL, необходимо

использовать цикл. (Примеры таких операций приведены в п. 12.4.)

12.2.3. Записи PL/SQL

Record PL/SQL - это совокупность полей, каждое из которых должно иметь

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

типам данных.

Если создаваемая запись (sotr) соответствует описанию столбцов какой-либо

базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе

DECLARE с помощью атрибута %ROWTYPE:

sotr kadry%ROWTYPE;

В противном случае для объявления записи необходимо сначала определить ее

тип данных. Для описания типа данных RECORD используется синтаксис:

TYPE type_name IS RECORD

( field_name1 {field_type | variable%TYPE | table.column%TYPE

| table%ROWTYPE} [NOT NULL],

field_name2 {field_type | variable%TYPE | table.column%TYPE

| table%ROWTYPE} [NOT NULL],

...);

где "type_name" - спецификатор типа, используемый в последующих объявлениях

записей PL/SQL, и "field_type" - любой тип данных. С помощью атрибута %TYPE

можно установить "type_name" соответствующим типу данных какой-либо переменной

(variable) или столбца (table.column). Атрибут %ROWTYPE позволяет определить

поле как запись, соответствующую описанию столбцов какой-либо базовой таблицы.

- 28 -

При объявлении типа записи можно присвоить ее полям некоторые значения. Если

же для поля вводится ограничение NOT NULL (для предотвращения назначения пустых

значений), то этому полю надо обязательно присвоить значение. Например:

TYPE SotrRecTyp IS RECORD (nomer NUMBER(4) NOT NULL := 1001,

familiy CHAR(20), dolgnost CHAR(14), otdel NUMBER(3) := 102);

Объявление создаваемой записи (например, name_plsql_record) производится в

разделе DECLARE и имеет вид:

name_plsql_record type_name;

Ссылки на отдельные поля записи осуществляются так:

name_plsql_record.field_name;

Для присвоения значения конкретному полю записи используется синтаксис:

name_plsql_record.field_name := expr;

Примеры использования записей в программах PL/SQL приведены ниже.

Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы

или представления, а также для выборки значений из таблицы PL/SQL, необходимо

использовать цикл. (Примеры таких операций приведены в п. 12.4.)

12.2.4. Переменные, константы и выражения

В программах PL/SQL могут использоваться переменные и константы, описываемые

в разделе DECLARE с помощью конструкции вида:

variable_name [CONSTANT] type_name [NOT NULL] [ { := | DEFAULT } expr ]

Например

birthdate DATE;

emp_count SMALLINT := 0;

emp_count SMALLINT DEFAULT 0;

acct_id VARCHAR2(5) NOT NULL := 'AP001';

pi CONSTANT REAL := 3.14159;

area REAL := pi * radius**2;

valid_id BOOLEAN;

valid_id VARCHAR2(5); -- недопустимое вторичное описание valid_id

i, j, k SMALLINT; -- нельзя описывать список; надо:

-- i SMALLINT; j SMALLINT; k SMALLINT;

credit REAL(7,2);

debit credit%TYPE; -- тип данных аналогичный типу данных "credit"

Синтаксис выражения описан в п. 4.3.

12.3. Связь объектов PL/SQL с таблицами базы данных

Чтобы программа PL/SQL могла работать с информацией, содержащейся в базах

данных, необходимо организовать обмен между значениями столбцов таблиц баз

данных и переменными PL/SQL.

Известно, что для выбора информации из таблиц используется SQL предложение

SELECT. При его выполнении Oracle создает специальную рабочую область,

содержащую информацию о самом SELECT, данные, которые требуются для его

выполнения (например, результаты подзапросов), и, наконец, окончательный

результат выполнения SELECT. PL/SQL имеет несколько механизмов доступа к

- 29 -

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

присвоить имя этой рабочей области и манипулировать содержащейся в ней

информацией, последовательно выбирая строки результата и пересылая значения

столбцов текущей строки в переменные PL/SQL. Существуют и другие механизмы,

не требующее создания явного курсора.

12.3.1. Явный курсор

Курсор - это средство языка SQL, позволяющее с помощью команд OPEN, FETCH и

CLOSE получить построчный доступ к результату запроса к базе данных. (Будем

также называть курсором и сам набор строк, полученный в результате выполнения

запроса.)

Для использования курсора его надо сначала объявить, т.е. дать ему имя и

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

должны быть помещены в набор строк, названный этим именем. Команда OPEN

инициализирует получение указанного набора и установку перед первой его строкой

указателя текущей строки. Команда FETCH служит для установки указателя текущей

строки на следующую запись (первый раз на строку с номером 1) и выборки из

текущей строки курсора значений указанных столбцов с пересылкой их в переменные

PL/SQL. (Выполнением FETCH в цикле можно последовательно выбрать информацию из

всех строк курсора.) Наконец, команда CLOSE позволяет закрыть (удалить из

памяти) набор строк (при этом описание курсора сохраняется и его можно снова

открыть командой OPEN).

Существует модификация ("Курсор в цикле FOR"), позволяющая организовать

последовательный выбор строк объявленного курсора без явного использования

команд OPEN, FETCH и CLOSE.

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

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

допустимом разделе, используя синтаксис:

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

где

cursor_name - имя курсора;

SELECT ... - предложение SELECT, определяющее строки курсора;

parametr - имеет следующий синтаксис:

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

а type_name - любой тип (подтип) данных PL/SQL без указания ограничений

(например, длины символьных значений).

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

WHERE фразу предложения SELECT с целью отбора нужных строк запроса. Передача

таких значений производится во время открытия курсора командой OPEN. Если

значения формальных параметров отсутствуют в команде OPEN и не заданы по

умолчанию (:= value или DEFAULT value), то выдается ошибка. При наличии тех и

других используются параметры из команды OPEN.

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

параметрам курсора:

DECLARE

CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',

tdat DATE := '1.1.1996') IS

SELECT (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka

FROM shtat x, dolgnosti y, grup_dolg z

WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd

AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup

ORDER BY razr_dolg DESC;

Кроме того, в нем выражению "(TO_CHAR(razr)||' '||imya_dolg)" дан псевдоним

"razr_dolg", использованный во фразе ORDER BY. Oracle рекомендует создавать

псевдонимы для всех выражений фразы SELECT с целью ссылки на них при работе с

курсором.

- 30 -

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

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

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

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

должен по числу и типу данных совпадать с описанием этих параметров.

Команда выполняет объявленный в курсоре SELECT ... , используя (если есть

параметры) передаваемые из OPEN значения или значения, указанные при объявлении

курсора, создавая набор строк и устанавливая указатель текущей строки перед

первой из них. Так, по команде

OPEN s1;

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

razr_dolg stavka

Указатель текущей строки ---> ------------------------ ------

17 Профессор 1.75

16 Профессор 3.

15 Доцент 9.75

14 Доцент 4.

13 Старший преподаватель 8.75

11 Ассистент 1.

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

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

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

razr_dolg stavka

Указатель текущей строки ---> ------------------------ ------

13 Ведущий программист 1.

13 Ведущий электроник 2.

11 Электроник 1 категории 1.5

10 Программист 2 категории 3.5

8 Инженер 2 категории 2.

4 Лаборант 3.

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

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

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

синтаксис:

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

Для каждого значения столбца, возвращенного запросом, в списке INTO должна

иметься переменная или поле записи соответствующего типа данных. Такие

- 31 -

переменные или записи должны быть заранее описаны в декларативной части блока

PL/SQL. Например

CREATE PROCEDURE pr_shtat IS

CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',

tdat DATE := '1.1.1996') IS

SELECT (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka

FROM shtat x, dolgnosti y, grup_dolg z

WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd

AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup

ORDER BY razr DESC;

sh_raz VARCHAR2(45); -- переменная для хранения значения razr_dolg

sh_stav shtat.stavka%TYPE; -- переменная для хранения значения stavka

raz VARCHAR(500); -- переменная, в которой будет последовательно

-- накапливаться текст "разряд-должность"

BEGIN

OPEN s1;

LOOP

FETCH s1 INTO sh_raz,sh_stav;

EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки

-- (см. п. 12.3.1.6)

raz := raz||sh_raz||';';

...

END LOOP;

CLOSE s1;

END pr_shtat;

Внутри цикла можно использовать значения переменных sh_raz и sh_stav, которые

после открытия курсора равны "17 Профессор" и "1.75", после первого прохождения

цикла - "16 Профессор" и "3.", после второго - "15 Доцент" и "9.75" и т.д.

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

немного изменить как описание, так и тело блока процедуры:

...

ORDER BY razr DESC;

TYPE ShRecTyp IS RECORD (raz_dol VARCHAR(45), -- описание типа данных

stav shtat.stavka%TYPE); -- записи ShRec

ShRec ShRecTyp; -- объявление записи ShRec

raz VARCHAR(500); -- переменная, в которой будет последовательно

-- накапливаться текст "разряд-должность"

BEGIN

OPEN s1;

LOOP

FETCH s1 INTO ShRec;

EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки

raz := raz||ShRec.raz_dol||';';

...

Теперь значения, получаемые ранее из sh_raz и sh_stav, можно получать из

полей ShRec.raz_dol и ShRec.stav записи ShRec.

Целесообразность использования записей возрастает с увеличением количества

ее полей или возможности создания описания по типу уже существующей записи с

помощью атрибута %ROWTYPE.

Наконец, если при выполнении цикла осуществлен переход за последнюю строку

набора, то значения переменных FETCH-списка будут неопределены.

- 32 -

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

Команда CLOSE используется для освобождения всех ресурсов, которые

поддерживались открытым курсором (при этом описание курсора сохраняется и его

можно снова открыть командой OPEN). Синтаксис команды CLOSE имеет вид:

CLOSE cursor_name;

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

В большинстве ситуаций, которые требуют явного курсора, текст программы может

быть упрощен при использовании "курсора в цикле FOR", заменяющего команды OPEN,

FETCH и CLOSE. Курсор в цикле FOR:

- неявно объявляет индекс цикла записью, поля которой соответствуют столбцам

(псевдонимам) предложения SELECT ... из описания курсора;

- передает параметры курсора (если они есть) и открывает курсор;

- выбирает в цикле строки из полученного набора в индекс цикла (поля записи);

- закрывает курсор после обработки всех строк набора или досрочному выходу

из него с помощью команд EXIT или GOTO.

Синтаксис курсора в цикле FOR имеет вид:

FOR var_rec_name IN cursor_name [ (value [,value]...) ] LOOP

ТЕЛО ЦИКЛА

END LOOP;

где - var_rec_name индекс цикла, в котором при первом прохождении цикла хранится

первая строка набора, при втором прохождении цикла - вторая строка и т.д.;

- список значений ("value") используется для передачи параметров курсора

(он заменяет в данном случае список из команды OPEN);

- ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых

используются переменные с именами var_rec_name.column_name, а column_name

имя столбца из перечня столбцов предложения SELECT в описании курсора.

Например,

DROP PROCEDURE pr_shtat;

CREATE PROCEDURE pr_shtat IS

CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',

tdat DATE := '1.1.1996') IS

SELECT otdel, (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka

FROM shtat x, dolgnosti y, grup_dolg z

WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd

AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup

ORDER BY razr DESC;

raz VARCHAR(500); -- переменная, в которой будет последовательно

-- накапливаться текст "разряд-должность"

BEGIN

FOR s1_rec IN s1 (102,'Специалисты','1.6.1996') LOOP

raz := raz||s1_rec.razr_dolg||';';

...

END LOOP;

END pr_shtat;

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

Для анализа состояния курсора используются специальные переменные, имена

которых составляются из имени курсора и суффиксов %FOUND, %NOTFOUND, %ROWCOUNT

и %ISOPEN, называемых атрибутами курсора. Если курсор назван "cursor_name", то

эти переменные имеют имена:

cursor_name%NOTFOUND, cursor_nane%FOUND,

cursor_nane%ROWCOUNT и cursor_nane%ISOPEN.

- 33 -

Значения таких переменных анализируются при выполнении программы с помощью

различных операторов управления (IF...THEN, EXIT WHEN и т.п.), которые изменяют

(при необходимости) ход выполнения программы. Следует отметить, что ссылка на

эти переменные до открытия курсора приводит к появлению сообщения INVALID_CURSOR.

Переменная с атрибутом %ISOPEN позволяет определить, открыт ли курсор. Если

он открыт то эта переменная возвращает TRUE, иначе - FALSE. Например:

IF NOT s1%ISOPEN THEN -- курсор не открыт ?

OPEN s1; -- открыть курсор !

IF END;

FETCH ...

Переменные с %NOTFOUND и %FOUND атрибутами показывают состояние текущей

позиции курсора (перед первой выборкой строки курсора обе переменных имеют

значение NULL). Переменная с %NOTFOUND принимает значение FALSE тогда, когда

выборка возвратила строку (при этом переменная с %FOUND принимает значение

TRUE). Если же в результате выборки строка не возвращается, то переменные с

%NOTFOUND и %FOUND принимают значения TRUE и FALSE, соответственно. Пример

использования %NOTFOUND был рассмотрен в п. 12.3.1.3.

Переменная с атрибутом %ROWCOUNT содержит количество строк, выбранных из

курсора на текущий момент (при открытии курсора эта переменная содержит ноль).

В следующем примере переменная s1%ROWCOUNT ограничивает выборку из курсора s1

десятью строками:

LOOP

FETCH s1 INTO sh_raz,sh_stav;

IF s1%ROWCOUNT > 10 THEN

...

END IF;

...

END LOOP;

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

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

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

UPDATE [schema.]{table | view}[@dblink] [alias]

SET { (column [, column] ...) = (subquery)

| column = { expr | (subquery) } }

[, { (column [, column] ...) = (subquery)

| column = { expr | (subquery) } } ] ...

WHERE CURRENT OF cursor_name;

DELETE [FROM] [schema.]{table | view}[@dblink] [alias]

WHERE CURRENT OF cursor_name;

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

содержало фразу

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

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

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

- 34 -

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

Для всех команд языка SQL, не связанных с объявлением курсора ("явным

курсором"), PL/SQL открывает курсор ("неявный курсор"), на который можно

ссылаться по курсорному имени SQL%. При работе с таким курсором нельзя

использовать команды OPEN, FETCH и CLOSE, но можно использовать атрибуты

курсора, чтобы получить информацию о текущем его состоянии.

12.3.2.1. SELECT ... INTO

В тех случаях, когда программе необходимо иметь значения столбцов из одной

строки таблицы, можно воспользоваться предложением SELECT ... INTO, формат

которого имеет вид:

SELECT [DISTINCT | !!under!!ALL]

{ [schema.]{table | view | snapshot}.expr [c_alias] }

[, { [schema.]{table | view | snapshot}.expr [c_alias] } ] ... }

INTO { variable_name [, variable_name ] ... } | record_name

FROM table_list [WHERE condition]

[GROUP BY expr [, expr] ...] [HAVING condition]

[ {UNION | UNION ALL | INTERSECT | MINUS} SELECT command]

[ORDER BY {expr | c_alias | position}

[!!under!!ASC | DESC] [, {expr | c_alias | position}

[!!under!!ASC | DESC]] ]...

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

[, [[schema.]{table | view}.]column] ...]

[NOWAIT] ]

Практически это обычный SELECT, выполняющий присвоение выбираемых значений

столбцов переменным, перечисленным во фразе INTO. Однако такое присвоение

происходит только в том случае, если "WHERE condition" обеспечивает возвращение

по запросу лишь одной строки и переменные заранее описаны в декларативной

части блока PL/SQL.

12.3.2.2. UPDATE, DELETE и INSERT

Эти предложения отличаются от аналогичных предложений интерактивного SQL

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

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

Для анализа результата выполнения предложений SELECT...INTO, INSERT, UPDATE

и DELETE используются три переменные: SQL%NOTFOUND, SQL%FOUND и SQL%ROWCOUNT

(Oracle закрывает курсор SQL автоматически после выполнения SQL предложения,

что делает бессмысленным использование переменной SQL%ISOPEN, так как ее

значение всегда равно FALSE).

Перед выполнением предложений SELECT...INTO, INSERT, UPDATE и DELETE

переменные SQL%NOTFOUND и SQL%FOUND имеют значение NULL. Переменная SQL%NOTFOUND

принимает значение TRUE, если INSERT, UPDATE и DELETE не произвели изменений

таблиц базы данных или SELECT...INTO не возвратил строк (при этом переменная

SQL%FOUND принимает значение FALSE). В противном случае переменная SQL%NOTFOUND

принимает значение FALSE, а переменная SQL%FOUND - TRUE

Вот один из примеров использования SQL%NOTFOUND для добавления новой строки

в таблицу temp при сбое модификации:

UPDATE shtat SET stavka = stavka + 1 WHERE dolgn = 'доцент' AND razr = 15;

IF SQL%NOTFOUND THEN -- изменение не выполнено

INSERT INTO temp VALUES (...);

END IF;

- 35 -

12.4. Операторы управления выполнением программы

12.4.1. Операторы условного перехода (IF ...)

Существует три модификации оператора условного перехода:

IF-THEN | IF-THEN-ELSIF

------- | -------------

IF условие THEN | IF условие1 THEN

последовательность команд; | 1-я последовательность команд;

END IF; | ELSIF условие2 THEN

| 2-я последовательность команд;

IF-THEN-ELSE | ...

------------ | ELSIF условиеN THEN

IF условие THEN | N-я последовательность команд;

1-я последовательность команд; | [ ELSE

ELSE | N+1-я последовательность команд; ]

2-я последовательность команд; | END IF;

END IF; |

Синтаксис условий приведен в п. 4.4. Во всех модификациях если "условие" или

"условие1" истинно (TRUE), то выполняется "последовательность команд" или

"1-я последовательность команд" и управление передается на первый оператор

после END IF. Если же оно ложно (FALSE), то:

- в модификации IF-THEN управление передается на первый оператор после END IF;

- в модификации IF-THEN-ELSE выполняется 2-я последовательность команд и

управление передается на первый оператор после END IF;

- в модификации IF-THEN-ELSIF проверяется условие 2; если оно истинно, то

выполняется 2-я последовательность команд и управление передается на первый

оператор после END IF; если условия 1 и 2 ложны, а условие 3 истинно, то

выполняется 3-я последовательность команд и управление передается на первый

оператор после END IF; наконец, если условия 1, 2, ..., N ложны, то

выполняется N+1-я последовательность команд и управление передается на первый

оператор после END IF.

Все это справедливо, если внутри последовательности команд нет операторов,

осуществляющих переход за пределы этой последовательности.

12.4.2. Метки и оператор безусловного перехода (GOTO)

В любом месте программы может быть поставлена метка, имеющая синтаксис:

<<имя_метки>>

Оператор GOTO позволяет осуществить безусловный переход к метке, имя которой

должно быть уникальным внутри программы или блока PL/SQL. Например, управление

передается вниз к помеченному оператору:

BEGIN

...

GOTO insert_row;

...

<>

INSERT INTO shtat VALUES ...

END;

- 36 -

В следующем примере управление передается вверх к помеченной

последовательности операторов:

BEGIN

...

<>

BEGIN

UPDATE shtat SET ...

...

END;

...

GOTO update_row;

...

END;

Следует отметить, что использование GOTO (особенно в тех случаях, когда метка

предшествует оператору GOTO) может привести к сложным, нераспознаваемым кодам

ошибок, которые трудно обрабатывать. Поэтому реже используйте GOTO, тем более

что этот оператор нельзя использовать для выполнения перехода:

- в IF-блок, LOOP-блок или в другой блок, не включающий текущий;

- из одного предложения IF-оператора к другому;

- из внешнего блока в SUB-блок;

- из обработчика особых ситуаций в текущий блок.

12.4.3. Операторы цикла (LOOP, WHILE...LOOP и FOR...LOOP)

Циклы служат для повторяемого выполнения последовательности команд. В PL/SQL

используются три модификации операторов цикла: LOOP, WHILE...LOOP и FOR...LOOP.

Цикл LOOP имеет следующий синтаксис:

LOOP

последовательность команд;

END LOOP;

и приводит к бесконечному повторению последовательности команд, если внутри

нее нет команд EXIT (выход из цикла), RAISE (вызов обработчика исключительных

ситуаций) или GOTO (безусловный переход). Например,

LOOP

последовательность команд;

IF условие THEN EXIT;

END LOOP;

приведет к выходу из цикла после выполнения последовательности команд, как

только условие станет истинным.

Цикл WHILE предназначен для повторения последовательности команд, пока

условие остается истинным:

WHILE условие LOOP

последовательность команд;

END LOOP;

Наиболее распространен цикл FOR, имеющий следующий синтаксис:

FOR индекс IN [REVERSE] нижняя_граница..верхняя_граница LOOP

последовательность команд;

END LOOP;

- 37 -

Здесь индекс (счетчик циклов) изменяется от нижней до верхней границы с

шагом 1, а при использовании "REVERSE" - от верхней до нижней границы с шагом

-1. Например,

FOR i IN 1..3 LOOP -- для i = 1, 2, 3

последовательность команд; -- цикл выполняется 3 раза

END LOOP;

FOR i IN REVERSE 1..3 LOOP -- для i = 3, 2, 1

последовательность команд; -- цикл выполняется 3 раза

END LOOP;

Отметим, что в последнем случае пределы диапазона указываются в возрастающем,

а не убывающем порядке.

Если нижняя граница равна верхней, последовательность выполняется один раз.

Если нижняя граница больше верхней, последовательность не выполняется, и

управление переходит к следующему за циклом оператору.

Пределы диапазона цикла могут быть литералами, переменными или выражениями,

но должны быть целыми числами. Например, допустимы следующие диапазоны:

j IN -5..5

k IN REVERSE first..last

step IN 0..TRUNC(high/low) * 2

code IN ASCII('A')..ASCII('J')

Объявлять индекс не нужно - он объявлен неявно как локальная переменная типа

integer.

PL/SQL позволяет определять диапазон цикла динамически во время выполнения.

Например:

SELECT COUNT(otdel) INTO shtat_count FROM shtat;

FOR i IN 1..shtat_count LOOP

...

END LOOP;

Значение "shtat_count" - неизвестно во времени компиляции; предложение SELECT

определяет это значение во время выполнения.

Индекс может использоваться в выражениях внутри цикла, но не может изменяться.

Например:

FOR ctr IN 1..10 LOOP

...

IF NOT finished THEN

INSERT INTO ... VALUES (ctr, ...); -- правильно

factor := ctr * 2; -- правильно

...

ELSE

ctr := 10; -- неправильно

END IF;

END LOOP;

Индекс определен только внутри цикла и на него нельзя ссылаться снаружи

цикла. После выполнения цикла индекс неопределен. Например:

FOR ctr IN 1..10 LOOP

...

END LOOP;

sum := ctr - 1; -- неверно

- 38 -

Подобно PL/SQL блокам, циклы могут быть помечены. Метка устанавливается в

начале оператора LOOP, следующим образом:

<>

LOOP

последовательность команд;

END LOOP;

Имя метки может также появляться в конце утверждения LOOP как в примере:

<>

LOOP

...

END LOOP my_loop;

Помеченные циклы используются для улучшения чтения программы (разборчивости).

С любой формой утверждения EXIT можно завершать не только текущий цикл, но и

любой внешний цикл. Для этого маркируйте внешний цикл, который надо завершить,

и используйте метку в утверждении EXIT, следующим образом:

<>

LOOP

...

LOOP

...

EXIT outer WHEN ... -- завершаются оба цикла

END LOOP;

...

END LOOP outer;

Если требуется преждевременно выйти из вложенного цикла FOR, маркируйте

цикл и используйте метку в утверждении EXIT. Например:

<>

FOR i IN 1..5 LOOP

...

FOR j IN 1..10 LOOP

FETCH s1 INTO ShRec;

EXIT outer WHEN s1%NOTFOUND; -- завершаются оба цикла

...

END LOOP;

END LOOP outer;

-- управление передается сюда

12.4.4. Операторы EXIT, EXIT-WHEN и NULL

EXIT используется для завершения цикла, когда дальнейшая обработка

нежелательна или невозможна. Внутри цикла можно помещать один или большее

количество операторов EXIT. Имеются две формы EXIT: EXIT и EXIT-WHEN.

По оператору EXIT цикл завершается немедленно и управление переходит к

следующему за END LOOP оператору. Например:

LOOP

...

IF ... THEN

...

EXIT; -- цикл завершается немедленно

END IF;

END LOOP;

-- управление переходит сюда

- 39 -

По оператору EXIT-WHEN цикл завершиться только в том случае, когда становится

истинным условие в предложении WHEN. Например:

LOOP

FETCH s1 INTO ...

EXIT WHEN s1%NOTFOUND; -- конец цикла, если условие верно

...

END LOOP;

CLOSE s1;

Оператор EXIT-WHEN позволяет завершать цикл преждевременно. Например,

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

значение s1, цикл завершается независимо от того сколько раз цикл выполнился.

FOR j IN 1..10 LOOP

FETCH s1 INTO ShRec;

EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки

...

END LOOP;

NULL - пустой оператор; он передает управление к следующему за ним оператору.

Однако, к нему может передаваться управление и его наличие часто улучшает

читаемость программы. Он также полезен для создания фиктивных подпрограмм для

резервирования областей определения функций и процедур при отладке программ.

12.5. Обработка ошибок

Нельзя создать приложение, которое будет безошибочно работать в любых

ситуациях: возможны аппаратные сбои, невыявленные ошибки приложения и ошибки

из-за некорректных действий пользователей приложения (клиентов). Если при этом

программная ошибка произошла в блоке PL/SQL, вложенном в другой блок, а тот,

в свою очередь, вложен в третий блок и т.д., то она может дойти до клиентского

приложения. Чтобы устранить возможную отмену большого объема ранее выполненных

операций и трафик из-за возвращаемых клиенту ошибок, чтобы посылать клиенту

точные сообщения о причине ошибки и способе ее устранения (если она все же

дошла до клиента), разработчики приложения должны предусматривать возможные

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

В PL/SQL предусмотрен механизмы перехвата и обработки ошибок, возникающих

при выполнении программы. Эти механизмы называются исключительными ситуациями.

Когда программа обнаруживает заданное условие ошибки, то вызывается

соответствующая исключительная ситуация. Обработки исключительных ситуаций в

программе производится в разделе EXCEPTION (см. п. 12.1).

При обнаружении исключительной ситуации, обработка основного тела программы

останавливается и управление передается соответствующему обработчику

исключительной ситуации, который определяет дальнейшие действия.

В PL/SQL используются следующие типы исключительных ситуаций:

- встроенные исключительные ситуации;

- исключительные ситуации, определяемые пользователем;

- обработчик OTHERS.

- 40 -

12.5.1. Встроенные исключительные ситуации

Oracle включает четырнадцать встроенных исключительных ситуаций,

соответствующих типовым ошибкам, приведенным в следующей таблице:

Ошибка

Исключительная ситуация 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)

USER(CREATE,ALTER,DROP, Создает, изменяет и удаляет пользователей; BECOME

BECOME) требуется для выполнения с помощью утилиты Export

полного экспорта базы данных

VIEW(CREATE,CREATE ANY, Создает и удаляет представления

DROP ANY)

- 50 -

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).

SQL*Plus

С О Д Е Р Ж А Н И Е