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

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

Литература

Содержание 3.2.1. Команда INSERT VALUES — определяет строку значений, которые будут вставлены в таблицу или представление; subquery_2 3.2.2. Команда DELETE 3.2.3. Команда TRANCATE Drop storage 3.2.4. Команда UPDATE 4. Другие средства создания ограничений целостности 4.2. Анонимные блоки (сценарии SQL) Create or replace procedure Create or replace function

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

• Дипломная работа студента 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.

3.2. Модификация данных


3.2.1. Команда INSERT

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

Синтаксис команды имеет вид:


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

[ (column [, column] ...) ] { VALUES(expr [, expr] ...) | subquery_2 } ;


где

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

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

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

subquery_1 — текст подзапроса, определяющего имена столбцов и таблицы, в которую вводятся новые строки;

column [, column] ... — список имен столбцов таблицы (представления), в которые должны быть вставлены данные из списка выражений (expr [, expr] ...) фразы VALUES или подзапроса (subquery_2); если в списке отсутствуют какие-либо столбцы, то в них после выполнения команды будут содержаться NULL-значения или значения по умолчанию, определенные при создании таблицы в CREATE TABLE; если же список отсутствует, то во фразе VALUES или подзапросе необходимо определить значения для всех столбцов таблицы (представления) и перечислить из в том порядке, в котором они перечислены при создании таблицы (представления);

VALUES — определяет строку значений, которые будут вставлены в таблицу или представление;

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

Пример 1. Студентка Орлова (номер 12345) стала с 7.10.1998 Воробьевой. Для сохранения старой фамилии в таблице Фамилии можно выполнить команду:


INSERT INTO Фамилии

VALUES(12345, 'Орлова', '7.10.1998') ;


или


INSERT INTO Фамилии

SELECT Номер, Фамилия, '7.10.1998'

FROM Студенты

WHERE Номер = 12345 ;


если в таблице Студенты еще храниться старая фамилия.

Пример 2. Перед окончанием осеннего семестра 1999/2000 учебного года (например, 1.12.1999) надо поместить в таблицу Ведомость данные, содержащие номера всех фактически обучаемых в данный момент студентов (Признак = 'обучен') и коды дисциплин (План), по которым они должны отчитаться во время зимней зачетной и экзаменационной сессий:


INSERT INTO Ведомость (Номер, План)

SELECT x.Номер, y.План

FROM Ученик x, План y

WHERE x.Ном_план = y.Ном_план

AND Признак = 'обучен'

AND '1.12.1999' BETWEEN Начало AND Конец

AND y.Ном_план IN (SELECT Ном_план

FROM Вып_каф

WHERE Начало = '1.9.1999')

AND Дисципл IN (SELECT Дисципл

FROM Дисцип

WHERE Семестр IN (1,3,5,7,9,11)) ;


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


INSERT INTO (SELECT Номер, План FROM Ведомость)

SELECT x.Номер, y.План

FROM Ученик x, План y

...


3.2.2. Команда DELETE

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

Синтаксис команды имеет вид:


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

[WHERE condition] ;


где

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

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

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

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

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

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

Пример 1. Удалить все данные из таблицы Вед_изм:


DELETE FROM Вед_изм ;

или

DELETE Вед_изм ;


Пример 2. Удалить из таблицы Вед_изм строки введенные до 1.1.1998:


DELETE Вед_изм

WHERE Дата < '1.1.1998' ;


3.2.3. Команда TRANCATE

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

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


TRUNCATE TABLE [schema.]table [ { !!under!!DROP | REUSE } STORAGE ] ;


где

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

table — имя таблицы, из которой удаляются все строки;

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

REUSE STORAGE — используется для сохранения памяти для повторной загрузки того же объема данных на ранее отведенное для таблицы место;

Пример 1. Удалить все данные из таблицы Вед_изм:


TRUNCATE TABLE Вед_изм ;


Пример 2. Удалить все данные из таблицы Вед_изм и сохранить в памяти место для повторной ее загрузки:


TRUNCATE TABLE Вед_изм REUSE STORAGE ;


3.2.4. Команда UPDATE

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

Синтаксис команды имеет вид:


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

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

| column = { expr | (subquery_3) } }

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

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

[WHERE condition] ;


где

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

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

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

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

alias — псевдоним таблицы, представления или подзапроса, который может быть использован в других местах команды;

фраза SET определяет набор модифицируемых столбцов и их новых значений;

column [, column] ... — список имен столбцов таблицы (представления), значения которых должно быть заменено на значения, выводимые подзапросом subquery_2;

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

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

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

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

expr — выражение, определяющее новое значение изменяемого столбца;

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

Пример 1. Студент номером 10042 пересдал 10.2.1999 экзамен по дисциплине «Системное программное обеспечение» за девятый семестр и получил оценку «Отлично». Необходимо обновить соответствующую запись в таблице Ведомость.


UPDATE vedomost SET ocenka = 'отл', data = '10.2.1999'

WHERE nomer = 10042 AND plan IN (SELECT plan FROM plan WHERE discipl IN

(SELECT discipl FROM discip WHERE semestr = 9 AND kontr = 'Экз' AND kor_disc =

(SELECT kor_disc FROM imia_disc WHERE imia_disc =

'Системное программное обеспечение')));


Пример 2. Для иллюстрации возможности применения подзапроса, определяющего новые значения для списка изменяемых столбцов, обновим аналогичную оценку студента с номером 10043, используя только что измененную оценку студента с номером 10042.


UPDATE vedomost SET (ocenka, data) =

(SELECT ocenka, data FROM vedomost WHERE nomer = 10042 AND plan IN

(SELECT plan FROM plan WHERE discipl IN

(SELECT discipl FROM discip WHERE semestr = 9 AND kontr = 'Экз'

AND kor_disc =

(SELECT kor_disc FROM imia_disc WHERE imia_disc =

'Системное программное обеспечение'))))

WHERE nomer = 10043 AND plan IN (SELECT plan FROM plan WHERE discipl IN

(SELECT discipl FROM discip WHERE semestr = 9 AND kontr = 'Экз' AND kor_disc =

(SELECT kor_disc FROM imia_disc WHERE imia_disc =

'Системное программное обеспечение')));


Естественно, что мы не предлагаем использовать для обновления этот «навороченный» иллюстрационный запрос.


4. Другие средства создания ограничений целостности


4.1. О триггерах и языке PL/SQL


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

Там же отмечалось, что для написания текста триггера надо не только овладеть рядом команд языка SQL., но и языком PL/SQL. PL/SQL (PL — programmatic language) создан фирмой Oracle для расширения возможностей языка SQL при написании хранимых процедур и сценариев, вызываемых из SQL*Plus. Вот некоторые конструкции последнего:

• Блоки IF ... THEN;
  
• Циклы WHILE ... DO и FOR ... NEXT;
  
• Переменные, используемые внутри программы;
  
• Курсоры (возможность просматривать результаты запроса по одной строке за один раз, сохраняя результаты в переменных, которые можно обрабатывать);
  
• Обработка исключительных ситуаций, которая позволяет предпринимать действия, исходя из проблем, обнаруженных Oracle во время выполнения программы.
  

PL/SQL — это блочно-структурированный язык. Это означает, что основные единицы программ PL/SQL (анонимные блоки, процедуры и функции) являются логическими блоками, которые могут содержать любое число вложенных в них блоков. Структура блока представлена на рис. 4.1.



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

Ниже мы кратко (на примерах) рассмотрим основные конструкции PL/SQL, которые могут использоваться при создании триггеров и их отладки. Первые примеры будут связаны с созданием разных типов программ для проверки правильности ввода (изменения) фамилии, имени или отчества и, если необходимо, их корректировки. Фамилия (имя) должна состоять только из русских букв и символов тире и пробел. Первая буква фамилии (имени или отчества) должна быть заглавной, а остальные строчными. В двойных (тройных) фамилиях и именах каждая часть должна начинаться с заглавной буквы (например, Смирнов-Сокольский, Жан-Жак Руссо, Гай Юлий Цезарь).


4.2. Анонимные блоки (сценарии SQL)


Анонимный блок PL/SQL — это неименованная программа, обычно используемая для отладки текстов программ других видов. Довольно часто анонимные блоки называют сценариями SQL.


DECLARE -- декларативная часть анонимного блока

-- описание локальных переменных

tekst VARCHAR2(20) := 'Жан - жак руссо'; -- Проверяемый текст (фамилия,

-- имя или отчество)

vychod VARCHAR2(20); -- Результат (проверенный и может быть исправленный

-- входной текст или "0 "при неисправимой ошибке)

kol INTEGER; -- Количество символов в проверяемом тексте

BEGIN -- исполняемая часть (тело) анонимного блока

vychod := LOWER(RTRIM(LTRIM(tekst))); -- первый вариант результата,

-- полученный после удаление из входного текста начальных (LTRIM) и

-- конечных (RTRIM) пробелов, а также преобразования всех букв текста

-- в строчные (LOWER)

kol := LENGTH(vychod); -- определение числа символов, оставшихся в тексте

-- после удаления начальных и конечных пробелов

IF kol > 0 THEN -- если текст содержит символы, то его дальнейшее

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

vychod := REPLACE(vychod,' ',' '); -- замена двух пробелов на один

vychod := REPLACE(vychod,'- ','-'); -- замена тире и пробела на тире

vychod := REPLACE(vychod,' -','-'); -- замена пробела и тире на тире

kol := LENGTH(vychod); -- определение числа символов, оставшихся в тексте

FOR i IN 1..kol LOOP -- перебор всех символов текста

IF INSTR('- абвгдеёжзийклмнопрстуфхцчшщъыьэюя',SUBSTR(vychod,i,1)) = 0

THEN

-- с помощью функции INSTR определяется позиция первого включения

-- i-го символа текста (вырезанного с помощью функции SUBSTR) в

-- набор '- абвгдеёжзийклмнопрстуфхцчшщъыьэюя' и если эта позиция

-- равна 0 (i-го символа нет в наборе), то производится:

vychod := '0'; -- установка нулевого результата и

EXIT; -- выход из цикла

END IF;

END LOOP;

ELSE

vychod := '0'; -- установка нулевого результата при отсутствии

-- в тексте символов, отличных от пробелов

END IF;

IF vychod <> '0' THEN -- если текст содержит символы, то:

vychod := INITCAP(vychod); -- преобразование первых букв слов текста в

-- заглавные

END IF;

DBMS_OUTPUT.PUT_LINE(vychod); -- использование утилиты DBMS_OUTPUT пакета

-- DBMS_STANDARD для вывода на терминал

-- результата преобразования

END;

/ -- наклонная черта, указывающая на окончание текста блока PL/SQL


Этот анонимный блок можно ввести с помощью текстового редактора в файл (например, с именем an_fio.sql) или вводить строчка за строчкой прямо в SQL*Plus. В последнем случае ввод в первой строке одного из зарезервированных слов DECLARE или BEGIN переводит SQL*Plus в режим построчного ввода строк анонимного блока так, как это показано ниже.


SQL> DECLARE -- декларативная часть анонимного блока

2 -- описание локальных переменных

3 tekst VARCHAR2(20) := 'Жан - жак руссо'; -- Проверяемый текст (фамилия,

4 -- имя или отчество)

5 vychod VARCHAR2(20); -- Результат (проверенный и может быть исправленный

6 -- входной текст или "0 "при неисправимой ошибке)

7 kol INTEGER; -- Количество символов в проверяемом тексте

8 BEGIN -- исполняемая часть (тело) анонимного блока

9 vychod := LOWER(RTRIM(LTRIM(tekst))); -- первый вариант результата,

. . .

44 END;

45 /


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


Жан-Жак Руссо

Процедура PL/SQL успешно завершена.

SQL> _


Если текст анонимного блока был сохранен в файле an_fio.sql, то такой сценарий SQL можно выполнить с помощью команды @an_fio.sql или @an_fio (расширение .sql, указывающее, что данный файл является файлом запроса, можно опустить):


SQL> @an_fio

Жан-Жак Руссо

Процедура PL/SQL успешно завершена.

SQL> _


4.3. Процедуры


Недостатком выполнения файлов сценариев SQL в распределенной вычислительной среде является то, что необходимо хранить актуальную версию этих сценариев на большом числе компьютеров. Программное обеспечение намного проще хранить в базе данных (в одном месте), чтобы к нему мог обратиться каждый. Для этого используются хранимые процедуры (функции и пакеты). Команда для создания (CREATE) или замены (OR REPLACE) независимой (не входящей в состав пакета) процедуры имеет вид:


CREATE [OR REPLACE] PROCEDURE [schema.]name

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

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

pl/sql_subprogram_body

END [name];

где

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

name — имя процедуры, формируемое по правилам, описанным, например, в п. 2.4.4.1;

argument — имя параметра, формируемое по тем же правилам, что и имя процедуры;

IN | OUT | IN OUT — используются для обозначения параметров, значения которых:

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

datatype — тип данных значений параметра (типы данных PL/SQL см. в п. 4.5.1.1);

pl/sql_subprogram_body — тело процедуры.


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


DROP PROCEDURE [schema.]name ;

и

ALTER PROCEDURE [schema.]name COMPILE ;


Создадим хранимую процедуру, выполняющую те же действия, что и анонимный блок п. 4.2. Текст её тела отличается от текста тела анонимного блока лишь отсутствием обращения к утилите DBMS_OUTPUT.PUT_LINE, используемой для вывода результата:


CREATE OR REPLACE PROCEDURE

p_fio -- имя процедуры

(tekst VARCHAR2, -- входной параметр (опущена умалчиваемая мода IN)

vychod IN OUT VARCHAR2) -- параметр с модой IN OUT позволяет передавать в

-- процедуру начальное значение и возвращать обновленное значение

-- подпрограмме, вызывающей данную процедуру

IS

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

kol INTEGER;

BEGIN

. . .

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

-- отсутствием в его последней строке команды DBMS_OUTPUT.PUT_LINE(vychod)

. . .

END p_fio;

/


Также как и текст анонимного блока, текст команды по созданию процедуры можно ввести с помощью текстового редактора в файл (например, с именем p_fio.sql) и выполнить в SQL*Plus команду @p_fio.sql или вводить строчка за строчкой прямо в SQL*Plus.

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


DECLARE

vchod VARCHAR2(20) := 'Жан - жак руссо';

rezult VARCHAR2(20);

BEGIN

p_fio (vchod, rezult);

dbms_output.put_line(rezult);

END;

/

Обращение к процедуре осуществляется с использованием позиционной (как это показано выше) или именной нотации:


p_fio (vychod => rezult, tekst => vchod);


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


p_fio ('Жан - жак руссо', rezult);

или

p_fio ('Жан'||' - жак'||' руссо', rezult);


4.4. Функции

Функция PL/SQL отличается от процедуры тем, что возвращаемое значение расположено в её имени, так же, как и в стандартных функциях (например, SIN, ABS, SUBSTR). Для описания типа, расположенного в имени значения, в синтаксис функции включена фраза «RETURN datatype»:


CREATE [OR REPLACE] FUNCTION [schema.]name

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

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

pl/sql_subprogram_body

END [name];


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


DROP FUNCTION [schema.]name ;

и

ALTER FUNCTION [schema.]name COMPILE ;


Создадим хранимую функцию, выполняющую те же действия, что и процедура п. 4.3. Текст её тела отличается от текста тела процедуры наличием описания возвращаемого значения (RETURN VARCHAR2) и предложением RETURN vychod, завершающим выполнение функции и присваивающим значение её имени:


CREATE OR REPLACE FUNCTION

fio -- имя функции

(tekst VARCHAR2) -- параметр (текст фамилии, имени или отчества)

-- и тип данных этого текста

RETURN VARCHAR2 -- тип возвращаемого значения

IS

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

vychod VARCHAR2(20);

kol INTEGER;

BEGIN -- начало тела функции

. . .

-- тело функции, отличающиеся от текста тела процедуры лишь наличием

-- его последней строке команды RETURN vychod

. . .

RETURN vychod;

END fio;

/


Текст команды по созданию функции вводится в Oracle также, как и текст процедуры. Вызов функции PL/SQL можно осуществить в тех же местах, где и вызов стандартной функции. Например, в списке фразы SELECT команды:


SELECT fio('Жан - жак руссо') Фамилия FROM DUAL;

Результат её выполнения имеет вид:


ФАМИЛИЯ

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

Жан-Жак Руссо


Здесь использовалась специальная однострочная таблица DUAL, которая создается Oracle для каждой схемы и обычно используется для вывода значения каких-либо выражений.