 |
|
Процедуры и функции
Процедуры и функции написанные на языке SQL во многом схожи с такими же понятиями в других языках. Процедура сначала:
1. создается при помощи Create or replace procedure
2. компилируется
3. сохраняется в скомпилированном виде в б.д.
4. при вызове процедуры ей можно передавать параметры
Вызовом процедуры является оператор PL\SQL и он не может быть частью выражения. В состав процедуры входит раздел объявлений, выполняемый раздел и раздел исключительных ситуаций.
Объявление процедуры
Create [ or replace] procedure имя процедуры
[ ( аргумент [{ in /out/ in out}] mun, тип аргумента, аргумента 2┘┘)]
{is/ AS}
пример
Create or replace procedure p in cena avto
P √ name in out cena avto. Name % type;
P √ god in out number;
P √ probeg in out number;
P √ ctna in out number; as
Begin
Insert into cena avto (name, god, probeg, cena)
Values (pname, pgod, pprobeg, pcena);
End
┘┘┘┘┘
exception p in cena avto (▒volga▓,2003,0,210.000);
Параметры указанные при объявлении процедуры называются формальными, а при вызове √ фактическими. При вызове процедуры формальным параметрам присваивается значение фактических. Формальные параметры бывают 3 видов
in(в) out(из) in out (в,из)
Если вид не указан то по умолчанию in
in - значение фактического параметра передается в процедуру при ее вызове. Внутри процедуры формальные параметры могут использованы толь для чтения. При завершении процедуры фактические параметры не изменяются
out-любое значение имеющее фактические параметры, при вызове процедуры игнорируется. Внутри процедуры формальные параметры рассматриваются только для записи. При завершении работы процедуры содержание формальных параметров присваиваться фактическим
in out-значение фактического параметра передается в процедуру при ее вызове. Внутри процедуры формальные параметры могут использованы толь для чтения. При завершении работы процедуры содержание формальных параметров присваиваться фактическим
Тело процедуры это блокPL\SQL, который содержит раздел объявлений, выполняемый раздел и раздел исключ. ситуаций. Раздел объявлений располагается между ключевым словам is\as и Begin. Выполняемый раздел - между Begin и exception. Раздел исключ. ситуаций √ exception и end.
Функция
В состав функции входит раздел объявлений, выполняемый раздел и раздел исключ. ситуаций.
Вызов функции - это часть некоторого выражения, а вызов процедуры √ это самостоятельный оператор.
Объявление функции
Create [ or replace] function имя функции
[ ( аргумент [{ in /out/ in out}] mun, аргумент [{ in /out/ in out}] mun)]
return возвращаемый тип {is/ AS}
тело функции
Внутри тела функции оператор return применяется для возврата результата работы функцию в вызванную среду
Return выражение, где выражение есть возвращаемое значение
Функция применяется когда требуется вернуть одно значение. Значение выражения преобразуются к типу указанному в команде return при описании функции. В функции может быть несколько операторов return, хотя выполняться будет одно из них
Create or replace function имя функции (
F √ name ctna-avto. Name % type,
F √ god ctna-avto. Name % type,
F √ cena ctna-avto. Name % type)
Return varchar 2 is
Begin
Return F √ cena + 1000;
End;
Курсоры.
В ответ на запросы пользователя SQL может возвращать многие сотни тысяч сторон, общим объемом в 10 Мбайт. В системе не каждый может иметь достаточный объем памяти и обработать их.
Кроме того, обычно клиент работает с небольшой частью данных в определенный момент времени, например с одной строкой, а не со всем набором строк.
Т.е. клиенту не нужен одновременно весь набор данных, и для него предпочтительно получать данные порциями.
Механизмом, обеспечивающим хранение результата выборки и представляют пользователю возможность доступа к любой строке выборки по ее номеру, являются курсоры.
Пользователь может работать в каждый момент времени только с одной строкой и курсор является своего рода окном, накладываемым на результат выборки. Перемещая окно, пользователь может не иметь доступ к любой строке выборки.
Для обработки SQL оператора Oracle выделяет атлас памяти, называемый контекстной областью. Она содержит информацию, необходимую для завершения обработки:
1. число строк, обработанных оператором
2. указатель на представление этого оператора
3. активный набор (набор строк, возвращение запросом).
Курсор - это указатель на контекстную область, с помощью которого PL SQL может управляться контекстной областью и ее состоянием во время обработки оператора.
Курсор - разновидность файла, которая создается во время.
Это средство погруженного SQL хранения результатов для дальнейшей обработки.
Для использования курсоров надо:
1. объявить курсор
2. открыть курсор для запроса
3. выбрать результаты в переменные PL SQL
4. закрыть курсор
1.Объявление курсора.
При объявлении курсора ему назначается имя и ставится в соответствие некоторой Select.
Cursor имя курсора IS оператор Select
Курсор должен быть объявлен (описан) до того, как на него будет произведена ссылка в нем, можно использовать любые оператор Select, в том числе соединение (joins), объединение (union), вычитание (minus).
Все переменные, которые используются в курсоре, должны быть ему видимы, т.е. они должны быть объявлены до их использования в курсоре.
Declare
V - probeg cena √ avto. probeg % type;
V - cena cena √ avto. Ceno % type;
Объявление курсора : cursor c √ cena is
Select from cena √ avto where cena = v √ cena;
Begin
V √ cena: = 2000;
Open c √ cena;
End.
2. Открытие курсора.
Open - имя курсора.
Позволяет СУБД обработать запрос курсора и записать результат в ╚курсор╩.
При открытии курсора происходит:
1. анализируется значения переменных привязки
2. на основе значений переменных привязывается определенный активный набор (т.е. набор строк, возвращаемый запросом)
3. указатель активный: наборы устанавливаются на одну строку.
Чтобы переменным привязки присвоить новые значения и курсор, работая с ними надо курсор закрыть, присвоить переменной привязки новые значения и снова открыть курсор.
3. Выбрать данные для обработки (считывание строк из курсора).
FETCH - имя курсора INTO √ список переменных
FETCH имя курсора INTO запись PL/SQL
С помощью оператора FETCH считывается 1 строка из курсора. Тип переменной курсора должен соответствовать переменной выбора запроса.
В случае если считывание идет в запись PL/SQL, то переменная типа запись объявляется
V √ cur cena √ avto % ROWTYPE
4. Закрытие курсора.
Close имя курсора. Закрытие курсора производят после того как выбран весь активный набор. При этом все отведенные ресурсы для курсора освобождают (пространство для хранения активного набора и временное пространство, используемое для определения такого набора).
Курсорные атрибуты.
Добавляются к имени курсора в блоке PL/SQL подобно атрибутам % type и % nowtype.
1. % found √ возвращает значение TRUE если строка была выбрана RALSE если не выбрана строка.
2. % notfound √ противоположно % found
3. % open используется для определения открыт или нет соответствующий курсор. Если открыт - TRUE , иначе √ FALSE.
4. % ROWCOUNT √ возвращает число строк, считанных курсором.
Циклы выборки.
Чаще всего курсоры используются для считывания всех строк активного набора с помощью циклов.
Примеры:
Для работы с курсором, триггером, процедурами необходимо установить привилегии.
Установка привилегий:
1. войти в ORACLE под именем SUSTEM пароль MANAGER
2. ввести следующую запись:
SQL grant all privileges to scott
3. зайти в SQL под именем scott
Пример:
Declare
V √ name cena_avto name % TYPE
V √ cena cena_avto cena % type
V √ probeg cena_avto probeg % type
V √ aod cena_avto god % type
/ *объявляем курсор*/
cursor curmy is
select name, probeg, cena, god from cena_avto
Where god between 1991 and 12000
Begin
Open curmy
Loop
/ *считываем информацию о машине* /
fetch aurmy into v_name v_cena v_probeg v_god;
(выйдем из цикла, когда строк для выбора больше нет)
exit when cuemy % NOFFOUND
(обработали считанные строки)
insert into table (name, probeg, cena, god)
values (v_name, v_probeg, v_ceno, v-god)
End loop;
(освободить ресурсы, используемые курсор)
close curmy
(завершим работу)
commit
End.
Пример больница:
Пациент, его атрибуты
Врачи, карточка
Create table Pazient
( FIO varchar 2 (30)
Vozrast char (20)
N polis char (20)
Address varchar 2 (30)
N kart char (20) not null
N uch char (20 not null)
Primary ney (N kart N uch))
Create table Vrach
( FIO vrach varchar 2 (30)
spezialnost char (30)
N vracha char (20) not null
Primary ney (N vrach))
Create table kartochka
( data char (20)
shialoba varchar 2 (2000)
diagnoz varchar 2 (2000)
lechenie varchar 2 (2000)
povt priem char (20)
N ZAP char (20) not null
N kart char (20)
N uch char (20)
Primary key (N ZAP)
Foreing key (N kart N uch)
Pacient (N kart N uch))
Create table uslugi
Name varchar 2 (500) not null
Cena char (10)
N vracha char (20)
N ZAP char (20)
Brimary rey (name)
Forein key (N ZAP)
Create table vizov vracha
N vrach char (20)
N ZAP char (20)
Foreing key (N vrach)
Расписание и начисление заработной платы.
Crate procedure
V_vrach (nvr in char npo in char) is begin
Declare
Cursor cur2 is select vrach. Kartochka n ZAP from vrach pacient kartochka where nach fio vrach = nvr and pacient n polis = npo and kartochka nuch = pacient nuch
(объявляем курсорную переменную)
cur2 VIZOV VRACH % type
begin
open cur2
fetch cur2 into
cur2
insert into VIZOV VRACH
values (cur2-r n vrach cur2_r nZAP )
close cur2
end
end
/
execute v_vrach (IVANOV)
selest * from VIZOV_ VRACHA
Тема 14. Ограничения целостности. Технология оперативной обработки транзакций (OLTP -технология). Информационные хранилища. OLAP - технология. Проблема создания и сжатия больших информационных массивов, информационных хранилищ и складов данных.
Лекции: 2 часа
В 60х гг. появились АИС предназначенные для хранения и обработки информации. По мере интеллектуализации АИС появилась возможность обработки текстовых документов на естественном языке (изображения и другие виды и формы представления данных).
Принципы хранения данных в системе обработки фактической и документальной информации схожи, но алгоритмы их обработки различаются. Таким образом выделяют 2 крупных класса информационных систем:
- Документальные
- Фактографические
Документальные системы служат для работы с документами на естественном языке, это монографии, публикации, сообщения, различные тексты. Они обеспечивают их смысловой анализ при неполном представлении смысла. Наиболее распространенный тип документальных систем - это информационно поисковые системы (ИПС), предназначенные для накопления, поиска по разным критериям документов на естественном языке.
Другой большой класс - фактографические системы, они оперируют фактическими сведениями, представлены специальным образом в виде организованных совокупностей формализованных записей данных. Фактографические системы используются не только для реализации справочных функций, но и для решения задач обработки данных и предоставление итоговых данных пользователям в табличной форме.