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]

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