Бобровски С. Oracle7 и вычисления клиент/сервер

Вид материала

Литература

Содержание Cascade constraints 2.4.5. Синтаксис выражений и условий в командах SQL и PL/SQL 2.4.5.2. Синтаксис выражений (expr) 2.4.5.3. Синтаксис условий (condition) 3-я форма Кафедра not null number(3) Конец not null date Select constraint_type,status,constraint_name,search_condition C enabled sys_c008549 кафедра is not null C enabled sys_c008555 начало is not null 2.4.7. О других средствах создания ограничений целостности 3. Команды манипулирования данными [nowait] ] Union, union all, intersect, minus For update 3.1.2. Фраза WHERE Or replace AS subquery With check option

Подобный материал:

• Дипломная работа студента 545 группы, 334.18kb.
• «sql*net», 239.02kb.
• Вопросы к экзамену по курсу "Компьютерные системы и сети", 25.77kb.
• Приложения литература, 256.14kb.
• Программа предназначена для управления бд по технологии «клиент-сервер», 26.71kb.
• Тема: 14. Введение технологии клиент сервер. Новые возможности и средства разработки., 1083.16kb.
• Iv-я Всероссийская научная конференция «Нечеткие системы, мягкие вычисления и интеллектуальные, 92.67kb.
• Вопросы для сдачи экзамена по ктп 2008 год, 51.83kb.
• Яковлев Владимир Леонидови, кафедра "Автоматизированные информационные системы" мгту, 2163.4kb.
• Задачи восстановление пропущенных значений в реляционных бд. 20. Oltp и olap. Многомерные, 28.62kb.

2.4.4.5. Удаление таблицы (DROP TABLE)

Для удаления описания таблицы и содержащихся в ней данных используется оператор DROP TABLE, синтаксис которого имеет вид:


DROP TABLE [schema.]table [CASCADE CONSTRAINTS] ;

где

[schema.]table — полное имя удаляемой таблицы; если таблица расположена в схеме пользователя, то имя схемы можно опустить;

CASCADE CONSTRAINTS — используется для удаления всех внешних ключей, которые ссылаются на удаляемую таблицу (иначе таблица не сможет быть удалена без предварительного удаления внешних ключей какими-либо другими средствами).


2.4.5. Синтаксис выражений и условий в командах SQL и PL/SQL


2.4.5.1. Иерархия операторов

Оператор Описание

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

() Подавляет обычные правила старшинства операций.

** NOT Возведение в степень и логическое отрицание.

+ - Знак, предшествующий числовому выражению.

* / Умножение и деление.

+ - Сложение и вычитание.

|| Сочленение текстовых выражений и (или) констант.

:= Присвоение значения переменной пользователя.


=, <>, <,

>, <=, >=, Операторы сравнения,

IS NULL, используемые при

LIKE, IN, построении условий

BETWEEN


AND Логическое "И"

OR Логическое "ИЛИ"


2.4.5.2. Синтаксис выражений (expr)

1-я форма: { [[schema.]{table | view | snapshot }.]

{column | pseudo-column | ROWLABEL}

| 'text' | number | sequence.{CURRVAL | NEXTVAL} | NULL }


2-я форма: function_name [( [DISTINCT | ALL] expr [, expr] ... )]


3-я форма: { (expr) | +expr | -expr | PRIOR expr | expr * expr | expr / expr

| expr + expr | expr - expr | expr || expr }


Decode_expr: DECODE( expr, search, result [, search, result] ... [, default] )

Если значение expr = search, то возвращает значение result, иначе

значение default или NULL (при отсутствии default).


2.4.5.3. Синтаксис условий (condition)

1-я форма: { expr {= | <> | > | < | >= | <=}

{expr | (subquery)} | expr_list {= | <>} (subquery) }


2-я форма: { expr {= | <> | > | < | >= | <=}

{ANY | SOME | ALL} {expr_list | (subquery)} | expr_list {= | <>}

{ANY | SOME | ALL} ( { expr_list [, expr_list] ... | subquery} ) }


3-я форма: { expr [NOT] IN {expr_list | (subquery)}

| expr_list [NOT] IN ( { expr_list [, expr_list]... | subquery} ) }


4-я форма: expr [NOT] BETWEEN expr AND expr


5-я форма: expr IS [NOT] NULL


6-я форма: EXISTS (subquery)


7-я форма: char1 [NOT] LIKE char2 [ESCAPE 'c']


8-я форма: { ( условие ) | NOT условие

| условие AND условие | условие OR условие }


2.4.6. О системных таблицах (словаре данных) Oracle

Почти вся информация о структуре базы данных и ее объектах размещена в системных таблицах Oracle, принадлежащих пользователь с именем SYS. Oracle обращается к этим таблицам при любых запросах для получения необходимой для их выполнения информации.

Любой пользователь имеет возможность получить из этих таблиц (точнее созданных по этим таблицам представлений) некоторую информацию о принадлежащих ему объектах. Для этого он должен знать имя и структуру того представления, в котором хранятся нужные ему сведения. Имена и краткое описание представлений приведены в документе "Краткий справочник по Oracle 7", а их структуру можно получить с помощью команды SQL*Plus - DESCribe (допускается сокращение имени команды до четырех символов). Она позволяет получить краткое описание любой пользовательской таблицы, например


SQL> desc Вып_каф


Имя Отсут.? Ввод

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

КАФЕДРА NOT NULL NUMBER(3)

ФАКУЛЬТ NOT NULL CHAR(5)

СПЕЦИАЛ NOT NULL VARCHAR2(6)

СТАТУС NOT NULL CHAR(9)

КУРС NOT NULL NUMBER(1)

ВАРИАНТ NOT NULL CHAR(3)

НАЧАЛО NOT NULL DATE

КОНЕЦ NOT NULL DATE

ФОРМА NOT NULL VARCHAR2(8)

НАЧ_ДИПЛ DATE

НОМ_ПЛАН NOT NULL NUMBER(4)

ПОЛЬЗОВ NUMBER(4)

ИЗМЕН DATE


Информация из системного представления выводится, как и из любой таблицы, с помощью оператора SELECT (см. [5]). Например, по запросу


SELECT CONSTRAINT_TYPE,STATUS,CONSTRAINT_NAME,SEARCH_CONDITION

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'ВЫП_КАФ';


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


C STATUS CONSTRAINT_NAME SEARCH_CONDITION

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

C ENABLED SYS_C008549 КАФЕДРА IS NOT NULL

C ENABLED SYS_C008550 ФАКУЛЬТ IS NOT NULL

C ENABLED SYS_C008551 СПЕЦИАЛ IS NOT NULL

C ENABLED SYS_C008552 СТАТУС IS NOT NULL

C ENABLED SYS_C008553 КУРС IS NOT NULL

C ENABLED SYS_C008554 ВАРИАНТ IS NOT NULL

C ENABLED SYS_C008555 НАЧАЛО IS NOT NULL

C ENABLED SYS_C008556 КОНЕЦ IS NOT NULL

C ENABLED SYS_C008557 ФОРМА IS NOT NULL

C ENABLED Должен быть 1-6 ! Курс IN (1,2,3,4,5,6)

C ENABLED Должен быть Осн,Ин1,Ин2,... ! Вариант IN ('Осн','Ин1','Ин2','Ин3',

'Ин4','Ин5','Ин6','Ин7','Ин8','

C ENABLED Начало Вып_каф > конца ! Начало <= Конец

C ENABLED Вечерняя, дневная,заочная ! Форма IN ('вечерняя', 'дневная',

'заочная')

C ENABLED Не между началом и концом ! Нач_дипл BETWEEN Начало AND Конец

P ENABLED Номер плана введен неверно !

U ENABLED Уникальность строк Вып_каф ?

R ENABLED Такой кафедры нет !

R ENABLED Код факультета в Вып_каф?

R ENABLED Этих Специал-Статус нет!


21 строк выбрано.


Здесь CONSTRAINT_TYPE (C) тип ограничения (P — первичный ключ, U — уникальность и C — проверка). Так как мы «поленились» дать имена ограничениям NOT NULL, то Oracle присвоил им собственные имена, начинающиеся с сочетания SYS_C (SYS_C008549, SYS_C008550, ...).


2.4.7. О других средствах создания ограничений целостности

С помощью команд CREATE TABLE и ALTER TABLE нельзя реализовать любые ограничения целостности. Например, мы не сможем обеспечить с их помощью такие ограничения таблицы Студенты, как: «дата рождения должна быть в пределах от (Текущая дата - 40 лет) до (Текущая дата - 15 лет)» и «первые буквы фамилии, имени и отчества должны быть большими, а остальные — малыми». Действительно, в CHECK нельзя включать обращения к текущей дате (SYSDATE) и сравнительно сложным процедурам для проверки правильности ввода, например, таких фамилий как Смирнов-Сокольский, Жан-Жак Руссо, Гай Юлий Цезарь и т.п.

Указанные выше и многие другие ограничения целостности можно вводить с помощью триггеров. Триггер — это сочетание хранимой в базе данных программы и события, которое заставляет ее выполняться. Такими событиями могут быть: ввод новой строки таблицы, изменение значений одного или нескольких ее столбцов и (или) удаление строки таблицы. При любом из этих событий автоматически запускаются один или несколько заранее созданных триггеров, которые производят проверку запрограммированных в них условий и если они не выполняются, отменяют ввод, изменение или удаление, посылая об этом заранее подготовленное сообщение пользователю.

Только с помощью триггеров мы сможем обеспечить «автоматический» ввод в таблицы таких атрибутов, как Пользов и Измен, «автоматическое» наращивание значений Номер и Строка, а также заполнение таблицы Вед_изм по данным удаляемых и изменяемых строк таблицы Ведомость.

Как уже отмечалось выше, для написания текста триггера надо овладеть языком PL/SQL и рядом команд языка SQL. Рассмотрим эти команды.


3. Команды манипулирования данными


3.1. Выборка данных


3.1.1. Команда SELECT

Для выборки данных из таблиц, представлений и моментальных копий (снимков) используется команда SELECT, основные конструкции которой подробно описаны в [5] и изучаются в лабораторном практикуме. Здесь мы приведем и кратко обсудим полный синтаксис команды SELECT СУБД Oracle:


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] ] ;


где

DISTINCT — используется для исключения дубликатов выводимых строк;

ALL — используется для вывода всех строк (устанавливается по умолчанию, на что указывает сочетание !!under!!);

* — используется для выбора строк, в которых присутствуют все столбцы из всех таблиц, представлений и снимков, указанных в фразе FROM (об объектах вида представление - view и снимок - snapshot будет рассказано ниже);

table.*, view.* или snapshot.* — используются для выбора всех столбцов из указанной таблицы, представления или снимка; для выбора данных из таблицы (представления или снимка), расположенных в другой схеме, используется спецификатор schema.;

expr — используется для описания выражения, составленного из столбцов (входящих в объекты, указанные в фразе FROM) и (или) любых констант; чаще всего это просто имя одного из столбцов; если имя какого-либо столбца одного из объектов в фразе FROM совпадает с именем столбца в другом объекте, то их имена должны предваряться спецификатором вида [schema.]table (view или snapshot);

c_alias или AS c_alias — используется для присвоения столбцу или выражению альтернативного имени (псевдонима); это имя будет выводиться в заголовке столбца (выражения) и может использоваться в фразе ORDER BY;

schema — необязательный параметр, идентифицирующий схему, в которой находится таблица, представление или снимок (по умолчанию - схема пользователя);

table, view, snapshot — имя таблицы, представления или снимка, из которых выбираются данные;

dblink — имя канала связи с удаленной базой данных, в которой размещена таблица (представление или снимок);

subquery — текст подзапроса, являющегося в данном контексте динамическим представлением, из которого выбираются данные аналогично тому, как они выбираются из явно указанных объектов (таблиц, представлений или снимков); синтаксис подзапроса приведен ниже;

t_alias — используется для присвоения другого имени (псевдонима) таблице, представлению или снимку; после такого присвоения в тексте запроса должен использоваться только псевдоним;

WHERE — содержит условия, оговаривающие признаки тех строк, которые должны выводится (или не должны выводиться) по данному запросу; если WHERE фраза отсутствует, то выводятся все строки таблицы (представления или снимка) или декартово произведение таблиц (представлений и снимков), указанных в фразе FROM;

[START WITH condition] CONNECT BY condition — используются для построения запроса, позволяющего выводить результат в виде древовидной (иерархической) структуры; подробнее о этом расширении стандарта языка SQL будет рассказано ниже;

GROUP BY — используется для группировки выбранных строк по указанному перечню и получения для каждой группы единственной строки с итоговой информацией: минимумом (MAX), максимумом (MIN), количеством (COUNT), суммой (SUM) и (или) средним значением (AVG);

HAVING — содержит условия, оговаривающие признаки тех итоговых строк (строк, полученных с помощью GROUP BY), которые должны выводится (или не должны выводиться) по данному запросу; если HAVING фраза отсутствует, то выводятся все итоговые строки;

UNION, UNION ALL, INTERSECT, MINUS — используются для комбинирования нескольких команд SELECT в одном запросе; по UNION выводятся все различимые строки объединяемых SELECT; по UNION ALL выводятся все строки объединяемых SELECT; по INTERSECT выводятся только те строки, которые находятся во всех пересекаемых SELECT; по MINUS выводятся строки выбранные первым SELECT и отсутствующие во втором;

ORDER BY — используется для упорядочения выводимых запросом строк; список упорядочения может содержать перечень столбцов, их псевдонимов (c_alias) или номеров позиций (position) в списке выбора SELECT; для каждого элемента списка может быть указан порядок упорядочения: по возрастанию - ASC (устанавливается по умолчанию) или по убыванию - DESC;

FOR UPDATE — используется для блокировки выбираемых строк на момент выполнения запроса;

NOWAIT — отменяет выполнение запроса, если SELECT пытается блокировать строку, которая ранее заблокирована другим пользователем; при отсутствии этой фразы Oracle ждет, пока строка не будет доступна и затем возвращает результаты SELECT.

Следует также отметить, что в SELECT, UPDATE, DELETE, subquery и в любой их части можно вставлять специальные комментарии - подсказки (hints) оптимизатору Oracle. Оптимизатор, преобразующий любой запрос в набор низкоуровневых процедур и составляющий план его выполнения, использует пользовательские подсказки для корректировки плана.

Пример 1. Получить аббревиатуры и названия факультетов.

Это можно сделать с помощью запроса:


SELECT Факульт, Факультет

FROM Факультеты;


или, учитывая что Факульт и Факультет это все столбцы таблицы Факультеты, используя спецификатор "*":


SELECT * FROM Факультеты;


Пример 2. Получить список неповторяющихся имен студентов.


SELECT DISTINCT Имя

FROM Студенты;


3.1.2. Фраза WHERE

Для выбора определенных строк таблицы (представления или снимка) используют фразу WHERE, синтаксис которой имеет вид:


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


где

condition — условие, синтаксис которого строится по правилам п. 2.4.5.3;

AND, OR, AND NOT и OR NOT — логические операторы, используемые для соединения нескольких условий.

Для получения желаемого результата WHERE фразы с множеством условий их надо вводить в правильном порядке, который можно организовать с помощью скобок. Надо также учитывать приоритет AND над OR.

Пример 1. Получить список тех студентов групп 250 - 255, которые по разным причинам прекратили или прервали обучение в 1998/1999 учебном году (данные на 4.7.1999). Список должен содержать Номер, Фамилию, Имя, Отчество, Группу, Признак, Начало и быть упорядочен по номеру группы и фамилии:


SELECT s.Номер, Фамилия, Имя, Отчество, Группа, Признак, Начало

FROM Студенты s, Ученик u

WHERE s.Номер = u.Номер

AND Группа BETWEEN 250 AND 255

AND Признак <> ’обучен’

AND ’4.7.1999’ BETWEEN Начало AND Конец

ORDER BY Группа, Фамилия ;


Пример 2. Вместо оператора BETWEEN (между) для указания групп можно использовать оператор IN (принадлежит), а для задания даты операторы сравнения:


SELECT s.Номер, Фамилия, Имя, Отчество, Группа, Признак, Начало

FROM Студенты s, Ученик u

WHERE s.Номер = u.Номер

AND Группа IN (250,251,252,253,254,255)

AND Признак <> ’обучен’

AND Начало <= ’4.7.1999’ AND Конец >= ’4.7.1999’

ORDER BY Группа, Фамилия ;


3.1.3. Представление

Представление (view) — это логическая (виртуальная) таблица, определяемая перечнем тех столбцов таблиц и признаками тех их строк, которые хотелось бы в ней увидеть. Представление является как бы "окном" в одну или несколько базовых или логических таблиц. Оно создается с помощью команды:


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

[(alias [,alias]...)] AS subquery

[ WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT constraint] ] ;

где

OR REPLACE — используется для перезаписи существующего представления (иначе перед созданием новой версии представления надо уничтожить его старую версию с помощью команды DROP VIEV);

FORCE — служит для создания текста представления по пока несуществующим или недоступным для данного пользователя таблицам; перед работой с таким представлением необходимо создать указанные в subquery таблицы и (или) получить права на их использование;

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

schema — необязательный параметр, идентифицирующий схему, в которой должно быть создано представление (по умолчанию - схема пользователя);

view — имя представления (строится по тем же правилам, что и для таблицы); выбираются данные;

(alias [,alias]...) — имена столбцов представления, в которые будут записаны значения из выражений (столбцов) подзапроса (subquery); если этот перечень отсутствует, то столбцам представления будут присвоены имена столбцов (выражений или псевдонимов) из подзапроса;

AS subquery — определение подзапроса, генерирующего данные в представление при указании имени этого представления в тексте какого-либо запроса или родительского подзапроса;

WITH READ ONLY — запрещает модификацию представления и, следовательно, тех таблиц, на базе которых создано данное представление;

WITH CHECK OPTION — запрещает модифицировать строки, которые затем не смогут быть выбраны посредством данного представления;

constraint — имя ограничения вида CHECK.

Таблица (таблицы), на базе которой создается представление, называется основной таблицей.

Представления используются для:

• обеспечения дополнительного уровня защиты основной таблицы путем ограничения доступа к определенному набору ее строк и (или) столбцов;
  
• скрытия сложности данных, например, за счет размещения в одном представлении данных из нескольких таблиц и других представлений;
  
• создания различного видения одних и тех же данных, возможно в одно и то же время, различными категориями пользователей;
  
• обеспечения независимости прикладных программ от изменения логической структуры базы данных (например, при необходимости расщепления какой-либо таблицы на несколько новых таблиц можно создать по ним представление, дав ему имя расщепленной таблицы).
  

К недостаткам представлений можно отнести:

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

Пример 1. Создать представление со списком обучающихся в данный момент контрактных студентов групп 350 - 355, содержащий номер группы, номер студента, номер его зачетной книжки, фамилию, имя и отчество:


CREATE OR REPLACE Контр_групп_350_355 AS

SELECT Группа, s.Номер, НЗК, Фамилия, Имя, Отчество

FROM Студенты s, Ученик u, Контр_студ k

WHERE s. Номер = u. Номер AND s. Номер = k. Номер AND Признак = 'обучен'

AND SYSDATE BETWEEN u.Начало AND u.Конец AND Группа BETWEEN 350 AND 355;


Так как подзапрос, используемый для описания представления, не может содержат фразы ORDER BY (см. п. 3.1.4), то упорядочение выводимых данных придется осуществлять в запросе на получение данных из представления.

Пример 2. Используя представление Контр_групп_350_355 получить список контрактных студентов групп 350 и 353, упорядоченный по номеру группы и фамилии:


SELECT * FROM Контр_групп_350_355

WHERE Группа IN (350, 353)

ORDER BY Группа, Фамилия ;


3.1.4. Подзапрос

Во многих командах SQL используются подзапросы (subquery). Подзапрос это несколько урезанная команда SELECT (без фраз ORDER BY, FOR UPDATE и NOWAIT), синтаксис которой имеет вид:


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 ]

[ WITH READ ONLY | WITH CHECK OPTION ]

[ {UNION | UNION ALL | INTERSECT | MINUS} subquery ] ] ;


Выше уже были описаны все фразы, составляющие это описание. Поэтому рассмотрим здесь лишь для чего могут использоваться подзапросы:

• для определения набор строк, которые должны вставляться в таблицу по командам INSERT или CREATE TABLE (фраза AS subquery);
  
• для описания строк, включаемое в представление или снимок по командам CREATE VIEW или CREATE SNAPSHOT;
  
• для определения значений, которые будут заменять существующие по команде UPDATE;
  
• для отбора нужных строк в фразах WHERE, HAVING и WITH START команд SELECT, UPDATE и DELETE;
  
• для использования в качестве динамического представления, заменяющего имя таблицы или представления в фразе FROM команды SELECT или подзапроса (subquery), а также в командах INSERT, UDPATE и DELETE.
  

В одной команде SELECT, INSERT, UDPATE или DELETE может содержаться множество подзапросов. Каждый подзапрос может содержать подзапрос (множество подзапросов) и уровень вложенности подзапросов не ограничен.

Пример 1. Определить наиболее часто встречающееся студенческое имя и вывести это имя, а также количество студентов с таким именем.

Существует множество вариантов решения этой задачи. Рассмотрим один из них, использующий вложенные подзапросы:


SELECT Имя, COUNT(Имя) Кол_во FROM Студенты GROUP BY Имя

HAVING COUNT(Имя) = (SELECT MAX(Кол_во) FROM

(SELECT Имя, COUNT(Имя) Кол_во FROM Студенты GROUP BY Имя) );


В первой строке формируется таблица, содержащая все неповторяющиеся имена и количество этих имен (псевдоним Кол_во). Во второй строке производится отбор той строки этой таблицы, Кол_во которой равно максимальному Кол_во из той же таблицы, сформированной внутри подзапроса (третья строка).