Дипломная работа студента 544 группы icon

Дипломная работа студента 544 группы


Смотрите также:
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 544 группы...
Дипломная работа студента 545 группы...



Загрузка...
скачать


САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

Математико-механический факультет


Кафедра системного программирования


Реализация пакета для работы с временными рядами в СУБД Oracle.

Дипломная работа студента 544 группы

Подкорытова Сергея Алексеевича



Научный руководитель

………………
/ подпись /

к.ф.-м.н., доцент Графеева Н.Г.

Рецензент

………………
/ подпись /

Ст.преподаватель,

Калинина-Шувалова Н.Л.

“Допустить к защите”
заведующий кафедрой,

………………

/ подпись /

д.ф.-м.н., проф. Терехов А.Н.



Санкт-Петербург

2010

^ ST. PETERSBURG STATE UNIVERSITY

Faculty of Mathematics and Mechanics


Chair of Software Engineering


Package for efficient time series processing in RDBMS Oracle.


by


Sergey Podkorytov


Master’s thesis



Supervisor

………………

associate professor N.G.Grafeeva



Reviewer

………………

Senior Lecturer

N.L. Kalinina-Shuvalova


“Approved by”
Head of Department

………………

Professor A. N. Terekhov



Saint Petersburg

2010

Оглавление.


Оглавление. 4

Введение. 5

Постановка задачи. 6

Глава 1. Обзор существующих технологий 7

^ Глава 3. Алгоритм выборки данных 18

Алгоритмы соединения. 18

Реализованное решение. 20

Заключение 25

Список литературы. 28

Приложение 1. Исходный текст функции SelectSeries 29

Приложение 2. Полный перечень процедур, функций и типов пакета. 31




Введение.


Сегодня при повсеместном распространении компьютеров сложно найти серьёзную организацию или предприятие, не имеющих своей информационной системы. Как правило, основой любой информационной системы является база данных, и эффективность работы всей системы напрямую зависит от эффективности работы самой базы. Каждый день современные информационные системы накапливают и обрабатывают данные. Какие-то из этих данных останутся невостребованными, какие-то будут использованы в ближайшее время, а некоторые необходимы для принятия важных решений уже сегодня. Примером таких данных могут служить данные о производстве электроэнергии, необходимые для формирования цен на так называемом рынке “на сутки вперёд”.

Одной из лучших баз данных является Oracle. Достоинствами СУБД Oracle являются:

  • Возможность хранить и обрабатывать большие объемы данных.

  • Многопользовательский режим работы с данными, сотни тысяч пользователей.

  • Возможность on-line администрирования.

  • Поддержка логической целостности на уровне базы данных.

  • Гибкие возможности по организации распределенной обработки данных.

  • Поддержка распределенных баз данных.

  • Переносимость приложений между платформами.

Основным козырем Oracle, с точки зрения разработчика приложений для систем управления базами данных, является наличие языка PL\SQL, являющегося процедурным расширением стандартного языка SQL, грамотное использование которого позволяет добиваться гибкой и эффективной работы сервера базы данных.

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

В данной дипломной работе рассмотрены недостатки существующих решений для обработки временных рядов в системах управления баз данных и описано реализованное автором решение.
^

Постановка задачи.


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

  • Структурирование данных.

  • Выборку данных.

  • Агрегирование данных.

При этом при создании пакета следует учитывать, что на его основе будет создан генератор отчетов.
^

Глава 1. Обзор существующих технологий

OLAP Решения


Модель Entity-Relation (Сущность-Связь), используемая в большинстве систем управления базами данных, предполагает хранение данных в хорошо нормализованных таблицах. Такая модель хорошо зарекомендовала себя в тех случаях, когда пользователю необходима максимально быстрая реакция на изменения в реальном времени. Однако, при исполнении сложных многотабличных запросов время работы оставляет желать лучшего.

Использование так называемой OLAP (online analytical processing) технологии, сейчас позволяет добиться ускорения выполнения запросов в 1000 раз по сравнению с аналогичными запросами в обычную реляционную базу. В основе большинства OLAP систем лежит следующий прием. Система делает копию текущего состояния данных и преобразует их в более удобный для вычисления сложных запросов вид, так называемый OLAP куб. Последующие запросы от пользователей обрабатываются уже с использование куба. Очевидным минусом подобного подхода является невозможность отражать изменения данных в реальном времени. Однако в некоторых случаях это не является серьёзным недостатком, например, в случае отсутствия необходимости анализировать данные в день их появления в системе, построение куба в ночное время и работа с утра рабочего дня с вчерашними данными.
^

“Обычные” генераторы отчетов.


Большинство генераторов отчетов предоставляют лишь графический интерфейс для составления запросов для людей, не владеющих языком SQL и совсем не обязательно имеющих понятие о работе систем управлениями баз данных. Более того, генераторы отчетов нацелены на использование произвольной системы управлениями базами данных, поэтому не могут воспользоваться преимуществами, предоставляемыми написанным на PL\SQL кодом. Как будет показано в главе 3, выполнение обычных SQL-запросов, в определённых случаях, не может сравниться по скорости работы с PL\SQL кодом.

^ Глава 2. Описание предложенного решения.

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

Описание структуры базы.


Предлагаемое решение: создается таблица-каталог рядов и несколько таблиц для хранения данных. Так как предполагается, что данные, которые обрабатываются информационной системой, являются показателями, генерируемыми какими-то реальными объектами, например филиалами предприятия, торговыми точками сети, подстанциями и т.д., логично хранить рядом с данными ещё и информацию о том, что это за данные и откуда (характер данных). Фактические есть перечень объектов (Objects) и их параметров (Parameters). Каждый параметр в базе может быть представлен одним или нескольким рядами (Series).

Предлагаемая схема изображена на рисунке 1. Далее следует её описание.

.



Рисунок 1. Схема базы

^ Таблица Objects:

create table Objects(

objectId number CONSTRAINT pkObjects PRIMARY KEY,

objectName varchar(80) NOT NULL);


Хранит данные об объектах, которые описываются этой базой данных. В простейшем случае имеет всего два поля: objectId – первичный ключ и objectName – строку с именем объекта. Возможно расширение таблицы полями parentId number, isLeaf number для поддержки древовидной структуры объектов, которая может быть использована для более наглядного изображения объектов непосредственно в клиентском приложении.


^ Таблица ParamNames:

create table ParamNames(

nameId number CONSTRAINT pkNames PRIMARY KEY,

name varchar(80) NOT NULL);


Словарь имен для параметров, на который ссылается таблица Parameters.


^ Таблица Parameters:

create table Parameters(

paramId number CONSTRAINT pkParam PRIMARY KEY,

objectId number CONSTRAINT fkObjectId

REFERENCES Objects(objectId) ON DELETE CASCADE,

nameId number CONSTRAINT fkNameId

REFERENCES ParamNames(nameId) ON DELETE CASCADE );


Описание параметров, которые описывают объекты из таблицы Objects. Поле ojectId - внешний ключ, указывающий на таблицу Objects. NameId – внешний ключ к таблице ParamNames.

Таблица Series:

create table Series(

seriesId number CONSTRAINT pkSeries PRIMARY KEY,

interval number,

paramId number CONSTRAINT fkParamId

REFERENCES Parameters(paramId) ON DELETE CASCADE);


Поле interval описывает временной промежуток между соседними значениями ряда. ParamId – внешний ключ к таблице Parameters.

^ Таблицы *Values.

create table YearValues(

valueId number CONSTRAINT pkValueIdY PRIMARY KEY,

value number CONSTRAINT nnValueY NOT NULL,

valueDate Date CONSTRAINT nnDateY NOT NULL,

seriesID number CONSTRAINT fkSeriesIdY REFERENCES Series(seriesId) ON DELETE CASCADE );

create table SeriesValues(

valueId number CONSTRAINT pkValueIdS PRIMARY KEY,

value number CONSTRAINT nnValueS NOT NULL,

valueDate Date CONSTRAINT nnDateS NOT NULL,

seriesID number CONSTRAINT fkSeriesIdS REFERENCES Series(seriesId) ON DELETE CASCADE,

valueNumber number CONSTRAINT nnNumber NOT NULL);

Для хранения непосредственно данных используются 4 таблицы DayValues, YearValues, MonthValues и SeriesValues. В них распределяются значения рядов, шаг которых составляет день, месяц, год или произвольное значение соответсвенно. Это сделано с целью увеличить скорость поиска среди них. Отличительная особенность таблицы SeriesValues - это наличие дополнительного столбца valueNumber. Как подсказывает его название, он используется для хранения порядкового номера значения внутри одного дня. Это позволяет избегать дополнительных трудностей для данных относящихся к дням, в которые осуществляется переход на летнее или зимнее время. Так же для таблиц с данными создаются индексы:

create index dv_valueDate_idx on DayValues(valueDate);

сreate index mv_valueDate_idx on MonthValues(valueDate);

create index yv_valueDate_idx on YearValues(valueDate);

create index sv_valueDate_idx on SeriesValues(valueDate);

create index sv_valueNum_idx on SeriesValues(valueNumber);

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

Описание основных возможностей пакета.


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

  • Function AddObject(Name varchar) return number;

Создает в таблице Objects новую запись с соответствующим именем и возвращает id этой записи.

  • Function AddParameter(paramName varchar, objectId number) return number;

Создает в таблице Parameters новую запись. Соответствующий objectId должен существовать в таблице Object. Также создает и указывает на или же просто указывает на уже существующую запись в таблице ParamNames. Возвращает id созданной записи.

  • Function AddSeries(paramId number, interval number, unit varchar) return number;

Создает в таблице Series новую запись, заполняя поля paramId, interval и unit, и возвращает id созданной записи.

Для удаления объектов созданы процедуры:

  • Procedure DeleteObject(id number)

Удаляет из таблицы Objects запись с ключем id и удаляет, все относящиеся к этому объекту параметру.

  • Procedure DeleteSeries(seriesId number);

Удаляет из таблицы Series запись с соответствующим значением seriesId и удаляет все ссылающиеся на неё записи из таблиц с данными.

  • Procedure DeleteParameter(paramId number);

Удаляет соответствующий параметр и все описывающие его ряды.
^

Агрегирование данных.


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

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

  • Суммирование

  • Минимум

  • Максимум

  • Среднее

Агрегирование возможно в двух измерениях.

Агрегирование по времени: объединение в одно значение нескольких соседних по времени значений принадлежащих одному ряду. В таблице 1 приведен пример агрегирования суммированием по времени шагом три дня.

Табл. 1. Пример агрегирования по времени.

DATE

VALUE







DATE

SUM

15.05.10

1000













16.05.10

198







16.05.10

1343

17.05.10

145













18.05.10

345













19.05.10

543







19.05.10

1233

20.05.10

345













21.05.10

456













22.05.10

654







22.05.10

1455

23.05.10

345















Агрегирование по объектам: объединение в одно значение несколько значений соответствующих одному моменту времени, но относящихся к разным параметрам. В таблице 2 приведен пример агрегирования трёх рядов суммированием.

Табл. 2. Пример агрегирования нескольких параметров.

VALUEDATE

X1

X2

X3







DATE

SUM(X1,X2,X3)

15.05.10

123

947

100







15.05.10

1170

16.05.10

234

578

617







16.05.10

1429

17.05.10

379

597

354







17.05.10

1330

18.05.10

23

846

448







18.05.10

1317

19.05.10

234

234

4669







19.05.10

5137



По аналогии с алгоритмом выборки агрегация N параметров реализуется за N проходов по таблице с данными и M редактирований временной таблицы, где M -количество исходных агрегируемых значений.

Агрегирование по объектам реализовано процедурами вида

Procedure calcAvg(seriesIds NumberArray, whereStr varchar2, destId Number);

Данная процедура вычисляет среднее значение за каждый момент времени, удовлетворяющий условию whereStr рядов, указанных в SeriesIds, а результат вычисления дописывает к ряду destId. Так же для предоставления большей свободы при использовании пакета для каждой агрегирующей процедуры создана функция, результатом работы которой является курсор, возвращающий агрегированные данные из временной таблицы.

Function getAvg(seriesIds NumberArray, whereStr varchar2)return serCurType;

Агрегирование по времени реализовано в виде перегруженных вариантов функций, агрегирующих по времени, например:

Function getMax(id number, whereStr varchar2) return number;
^

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


Выборка данных из одного ряда осуществляется функцией Function getRef(id number, whereStr varchar2);

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

Выборка же из нескольких рядов реализована в виде функции:

Function SelectSeries(seriesIds NumberArray, whereStr varchar2) return serCurType;

Результатом работы этой функции является курсор, возвращающий из временной таблицы значения рядов, id которых указан в массиве seriesId, лежащих в указных в параметре whereStr пределах. Работе этой функции посвящена глава 3.

^ Комбинирование данных.

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

Почленное умножение и аналогичные бинарные алгебраические операции реализованы в виде следующей процедуры:

Procedure combine(firstId number, secondId number, whereStr varchar2, method number, destId number);

Данная процедура создает новый ряд destId, заполняя его результатами умножения (или аналогичной операции) рядов fisrtId и secondId. Параметр method принимает значения 1, 2, 3, 4 выбирая в качестве операции сложение, вычитание, умножение или деление соответственно.
^

Глава 3. Алгоритм выборки данных


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

Однако вычисление всех операций соединения оказывается бутылочным горлышком при увеличении числа запрашиваемых параметров и временного интервала.
^

Алгоритмы соединения.


В реляционной алгебре операция объединения определяется следующим образом: пусть есть два отношения R и S, и условие С. Тогда результатом соединения будет подмножество декартова произведения R × S удовлетворяющее условию C. Это определение легко обобщить до произвольного числа отношений.

Как правило, в СУБД непосредственно декартово произведение не вычисляется, а для нахождения результата соединения используются более быстрые алгоритмы. Рассмотрим три использующихся в СУБД Oracle варианта реализации операции соединения.
^

Соединение вложенными циклами (Nested loops join)


В простейшем случаем алгоритм выглядит следующим образом: на входе два отношения R и S, а так же атрибут A.

Для каждого r из R

Для каждого s из S

Если (r.A == s.A)

Тогда добавить к результату ()


Главным недостатком этого алгоритма является то, что отношение S просматривается |R| раз. При этом если добавить к соединению третье отношение T, оно будет просматриваться уже |R|*|S| раз.
^

Соединение с хешированием (Hash join)


Данный алгоритм создает для меньшего из двух отношений хеш-таблицу и использует её при просмотре второго отношения для нахождения его элементам пары из первого.

Для каждого r из R

Добавить r в хэш-таблицу;

Для каждого s из S

Найти в хэш-таблице пару;

Если пара найдена, то добавить её к результату.


Соединение хешированием существенно быстрее соединения вложенными циклами, однако требует памяти для хранения в хеш-таблице элементов первого отношения. Эффективность данного алгоритма лучше всего проявляется, когда мощность одного из отношений заметно меньше другого, однако такой сценарий маловероятен для описываемой задачи.
^

Соединение слиянием отсортированных списков (merge join)


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

while (R.сurrent != null && S.current!= null)

{

if (R.A == S.A)

{

Output ();

R.next();

S.next();

}

else if (R.A < S.A)

R.next();

else S.next();

}


Фактически, при реальной работе оптимизатор запросов Oracle предлагает только два из описанных выше алгоритмов: соединение с хешированием и соединение слиянием отсортированных списков.
^

Реализованное решение.


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

Создается временная таблица со столбцами valueDate, valueNumber и переменным количеством столбцов вида col1, col2 и т.д., равному количеству рядов из которых необходимо извлечь данные. Далее поочерёдно выбираются данные, соответствующие каждому запрашиваемому параметру, и записываются во временную таблицу. Каждое значение будет занимать одну строку во временной таблице, оставляя часть полей пустыми.



Рис. 2. Содержимое временной таблицы перед завершением работы SelectSeries.

Теперь запросом, суммирующим по стобцам со значениями и группируя (group by) по дате и номеру, из временной таблицы можно извлечь желаемый результат. Функция возвращает курсор открытый именно на такой запрос.



Рис. 3. Результат выполнения запроса, сформированного функцией SelectSeries.

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

Сравним выполнение простого запроса и работу выборки функции SelectSeries. Были сгенерированы данные: 4 ряда по 2000 значений в каждом. Полный листинг скрипта генерации смотри в приложении. Работа функции SelectSeries и проход по всем данным возвращаемым курсором заняло ~1 секунду. Запрос к временной таблице, сформированный функцией, в этом случае выглядит так:

select valueDate, valueNumber, sum(col457), sum(col458), sum(col459), sum(col460) from temp group by valueDate, valueNumber order by valueDate, valueNumber;

Здесь столбцы col457, col458, col459, col460 – столбцы временной таблицы, соответствующие рядам, созданным скриптом генерации.

Запрос, созданный c помощью Toad For Oracle Query Builder, выглядит так:

select d1.valuedate, d1.value, d2.value, d3.value, d4.value

from dayvalues d1, dayvalues d2, dayvalues d3, dayvalues d4

where d1.valuedate = d2.valuedate and d2.valuedate = d3.valuedate

and d3.valuedate = d4.valuedate

and d1.seriesid = 457 and d2.seriesid = 458

and d3.seriesid = 459 and d4.seriesid = 460;

Здесь, числа 457, 458, 459, 460 – идентификаторы рядов созданных ранее. Выполнение этого запроса заняло 20 секунд. Рассмотрим план выполнения этого запроса, предлагаемый оптимизатором:




Рис. 4. План выполнения запроса в Toad for Oracle.

Как видно из плана при обработке запроса дважды используется соединения с хешированием и один раз, что совершенно неприемлемо для нашей задачи, оптимизатор строит соединение, вычисляя полное декартово произведение – пункт 6 MERGE JOIN CARTESIAN. Именно эта операция так сильно сказывает на производительности.

Однако хотелось бы отметить, что в серии последующих испытаний, оптимизатор уже не использовал MERGE JOIN CARTESIAN, а только соединение хешированием.
^

Серийные испытания.


Для сравнения производительности предложенного подхода с использованием простых запросов были сгенерированы следующие данные: один объект, имеющий десять параметров. Каждый параметр описывается одним рядом, интервал между значениями ряда – один день. Для каждого ряда сгенерировано 100000 значений.

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

Табл. 3. Результаты первого набора испытаний.

^ Количество соединяемых рядов

4

7

10

Время выполнения запроса, сек

43

59

68

^ Время выполнения предложенного подхода, сек

16

23

38



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

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

Табл. 4. Результаты второго набора испытаний.

^ Размер выбираемого интервала

10000

50000

80000

Время выполнения запроса, сек

6

30

45

^ Время выполнения предложенного подхода, сек

4

17

30



Полученные результаты показывают, преимущества предложенного метода, по сравнению со стандартным подходом – выигрыш составляет как минимум 150%.

Заключение

Выводы


Целью дипломной работы было создание пакета содержащего необходимые компоненты для эффективной работы с временными рядами.

В пакете реализовано:

  • Хранение и структурирование данных о рядах

  • Эффективный алгоритм выборки данных

  • Функции агрегации данных

  • Комбинирование данных


^

Интегрирование пакета в работающую систему.


При необходимости данная реализация может быть использована в уже существующей информационной системе. Все обращения к данным реализованы через функцию getRef, которая в свою очередь обращается к одной из таблиц DayValues, YearValues, MonthValues и SeriesValues. Вследствие этого, для корректной работы необходимо создать представления аналогичные этим четырём таблицам, через которые будут выбираться данные, а также создать представление для каталога рядов, то есть таблицы Series.

Стоит отметить, что распределение данных по четырём таблицам носит не обязательный характер, и возможно упростить схему до одной таблицы SeriesValues. Однако при этом нужно позаботиться о том, что у всех рядов в таблице SeriesValues значение interval было меньше 60.

Так же, по собственному усмотрению, администратор может расширять таблицы Objects, Parameters и Series необходимыми для работы конкретной системы атрибутами.
^

Дальнейшие перспективы.


Реализованный пакет включает в себя лишь основные моменты работы с временными рядами. Было бы логичным его расширение реализацией аналитических методов на уровне системы управления базой данных. Примерами подобных методов могут служить:

  • Выделение тренда (аналитическими и алгоритмическими методами).

  • Выделение сезонных колебаний (суточных, недельных, месячных и т.д.)

  • Построение краткосрочных прогнозов.

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

  • Отслеживание пиковых значений.

Реализация подобных вычислений на уровне СУБД позволит создавать более функциональные генераторы отчетов, которые не будут уступать в возможностях таким пакетам, как Statistica или EViews, и при этом будут иметь возможность работать с гораздо большими объемами данных.


^

Список литературы.


  1. Кайт Том. Oracle для профессионалов. Пер. с англ. – ТомКайт – СПб: ООО «ДиаСофтЮП», 2003.

  2. Кониор МкД. Oracle PL-SQL для профессионалов: практические решения – Пер. с англ. – СПб: ООО «ДиаСофтЮП», 2005.

  3. Миллсап.К. Oracle. Оптимизация производительности – пер. с англ. – СПб: Символ-Плюс, 2006.

  4. Документация Oracle, официальный сайт Oracle, www.oracle.com

  5. Surajit Chaudhuri, Umeshwar Dayal. An overview of data warehousing and OLAP technology, 1997.

  6. Сайт Тома Кайта, asktom.oracle.com
^

Приложение 1. Исходный текст функции SelectSeries


Function SelectSeries(seriesIds NumberArray, whereStr varchar2)

return serCurType

is

colName varchar(10):='col';

i number;

j number;

columnStr varchar(300) :='create global temporary table ' || tempTableName || '(valueDate Date, valueNumber Number';

typeStr varchar(6) :='number';

selectStr varchar(300) :='select valueDate';

fromStr varchar(300):= ' from ' || tempTableName || ' group by valueDate, valueNumber order by valueDate, valueNumber';

insertStr varchar(300):='insert into ' || tempTableName || '(valueDate, valueNumber, col';

c1 serCurType;

r seriesType;

begin

select count(1) into i from user_tables where upper(table_name) = upper(tempTableName);

if i <> 0 then

execute immediate 'truncate table ' || tempTableName;

execute immediate 'drop table ' || tempTableName;

end if;


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

for i in seriesIds.first..seriesIds.last

loop

columnStr:= columnStr || ', ' || colName || to_char(seriesIds(i)) || ' ' || typeStr;

selectStr:= selectStr || ', SUM(' ||colName ||to_char(seriesIds(i)) || ')';

end loop;

columnStr:= columnStr || ') on commit preserve rows';

EXECUTE IMMEDIATE columnStr;

--извлекаем данные и складываем во временную таблицу

for j in seriesIds.first..seriesIds.last

loop

c1 := SERIESFUNCTIONS.GETREF(seriesIds(j), whereStr);

loop

fetch c1 into r;

exit when c1%notfound;

^ EXECUTE IMMEDIATE

insertStr || to_char(seriesIds(j)) ||')

values(:valDate, :valN, :val)' using tempTableName, r.valueDate, r.valueNumber, r.value;

end loop;

end loop;

close c1;


open c1 for selectStr || fromStr;

return c1;

end;
^

Приложение 2. Полный перечень процедур, функций и типов пакета.


type NumberArray is table of number;

Массив переменных типа Number.

type seriesType is record (value number, valueDate Date, valueNumber number);

Запись, позволяющая хранить одно из значений произвольного ряда.

type serCurType is ref cursor;

Указатель на курсор.

tempTableName varchar(20);

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

Function AddObject(Name varchar) return number;

Создает в таблице Objects новую запись с соответствующим именем и возвращает id этой записи.

Function AddParameter(paramName varchar, objectId number) return number;

Создает в таблице Parameters новую запись. Соответствующий objectId должен существовать в таблице Object. Также создает и указывает на или же просто указывает на соответствующую имени запись в таблице ParamNames. Возвращает id созданной записи.

Function AddSeries(paramId number, interval number, unit varchar) return number;

Создает в таблице Series новую запись, заполняя поля paramId, interval и unit, и возвращает id созданной записи.

Function SelectSeries(seriesIds NumberArray, whereStr varchar2) return serCurType;

Возвращает курсор на созданную временную таблицу с результатами выборки рядов, id содержится в массиве seriesIds и удовлетворяющих условию whereStr.

Procedure SelectSeries(seriesIds NumberArray, whereStr varchar2, result IN OUT serCurType);

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

Function getRef(id number, whereStr varchar2) return serCurType;

Возвращает курсор на данные ряда id, ограниченные условием в строке whereStr;

Procedure calcAvg(seriesIds NumberArray, whereStr varchar2, destId Number);

Вычисляет среднее значение за каждый момент времени, удовлетворяющий условию whereStr рядов, указанных в SeriesIds, а результат вычисления дописывает к ряду destId.

Procedure insertValue(Id number, value number, valueDate date, valueNumber number default -1);

Добавляет к ряду id значение value с датой valueDate и номером valueNumber.

Function getSum(Id number, whereStr varchar2) return number;

Возвращает сумму значений ряда за указанный промежуток времени.

Function getMin(seriesIds NumberArray, whereStr varchar2) return serCurType;

Возвращает курсор на ряд из минимальных значений рядов указанных в массиве seriesIds за каждый момент времени, удовлетворяющий условию whereStr.

Function getMin(id number, whereStr varchar2) return number;

Возвращает минимальное из значений ряда id удовлетворяющих условию whereStr.

Procedure calcMin(seriesIds NumberArray, whereStr varchar2, destId Number);

Вычисляет ряд из минимальных значений, удовлетворяющих условию whereStr рядов, указанных в SeriesIds, а результат вычисления дописывает к ряду destId.

Function getMax(seriesIds NumberArray, whereStr varchar2) return serCurType;

Возвращает курсор на ряд из максимальных значений, удовлетворяющих условию whereStr рядов, указанных в SeriesIds.

Function getMax(id number, whereStr varchar2) return number;

Возвращает минимальное из значений ряда id удовлетворяющих условию whereStr.

Procedure calcMax(seriesIds NumberArray, whereStr varchar2, destId Number);

Вычисляет ряд из максимальных значений, удовлетворяющих условию whereStr рядов, указанных в SeriesIds, а результат вычисления дописывает к ряду destId.

Function combine(firstId number, secondId number, whereStr varchar2, method number) return SerCurType;

Складывает, вычитает, умножает, делит 2 ряда и возвращает курсор на результат.

Параметр method принимает значения 1, 2, 3, 4 соответственно.





Скачать 221,91 Kb.
оставить комментарий
Дата29.03.2012
Размер221,91 Kb.
ТипДиплом, Образовательные материалы
Добавить документ в свой блог или на сайт

отлично
  1
Ваша оценка:
Разместите кнопку на своём сайте или блоге:
rudocs.exdat.com

Загрузка...
База данных защищена авторским правом ©exdat 2000-2017
При копировании материала укажите ссылку
обратиться к администрации
Анализ
Справочники
Сценарии
Рефераты
Курсовые работы
Авторефераты
Программы
Методички
Документы
Понятия

опубликовать
Загрузка...
Документы

наверх