Построение запросов Графеева Н Г 2014 2

Построение запросов Графеева Н. Г. 2014

2 подхода к построению запросов • Императивный подход (процедурный стиль) • Декларативный (SQL запросы)

Причины возникновения проблемы • В связи с тем, что в современных СУБД в процедурных расширениях языка SQL появились возможности для встраивания в SQL-запросы результатов работы функций, возникает большой соблазн использовать эту возможность …. Однако, оказывается, что это не всегда уместно. • Для начала научимся в принципе писать функции, выдающие множество строк…

Пример (табличная функция ORACLE) • • • • • DROP TYPE t_tf_tab; DROP TYPE t_tf_row; CREATE TYPE t_tf_row AS OBJECT ( id NUMBER, description VARCHAR 2(50) ); / CREATE TYPE t_tf_tab IS TABLE OF t_tf_row; / -- Build the table function itself. CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS l_tab t_tf_tab : = t_tf_tab(); BEGIN FOR i IN 1. . p_rows LOOP l_tab. extend; l_tab(l_tab. last) : = t_tf_row(i, 'Description for ' || i); END LOOP; RETURN l_tab; END; / -- Test it. SELECT * FROM TABLE(get_tab_tf(10)) ORDER BY id DESC;

Пример (pipe line функция ORACLE) • • • -- Build a pipelined table function. CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS BEGIN FOR i IN 1. . p_rows LOOP PIPE ROW(t_tf_row(i, 'Description for ' || i)); END LOOP; RETURN; END; / • • -- Test it. SELECT * FROM TABLE(get_tab_ptf(10)) ORDER BY id DESC;

Упражнение 1 • Напишите в разных стилях запрос (к демонстрационной базе ORACLE), в котором будет отображено сколько наименований следующих видов продукции: • Bag, Blouse, Jacket • было отправлено в каждый штат.

Вариант 1 • • • • select STATE_NAME, (SELECT SUM(QUANTITY) FROM DEMO_ORDER_ITEMS, DEMO_ORDERS, DEMO_CUSTOMERS WHERE DEMO_ORDERS. ORDER_ID = DEMO_ORDER_ITEMS. ORDER_ID AND DEMO_ORDERS. CUSTOMER_ID = DEMO_CUSTOMERS. CUSTOMER_ID AND DEMO_CUSTOMERS. CUST_STATE = DEMO_STATES. ST AND DEMO_ORDER_ITEMS. PRODUCT_ID = (SELECT MIN(DEMO_PRODUCT_INFO. PRODUCT_ID) FROM DEMO_PRODUCT_INFO WHERE PRODUCT_NAME = 'Bag')) BAG, (SELECT SUM(QUANTITY) FROM DEMO_ORDER_ITEMS, DEMO_ORDERS, DEMO_CUSTOMERS WHERE DEMO_ORDERS. ORDER_ID = DEMO_ORDER_ITEMS. ORDER_ID AND DEMO_ORDERS. CUSTOMER_ID = DEMO_CUSTOMERS. CUSTOMER_ID AND DEMO_CUSTOMERS. CUST_STATE = DEMO_STATES. ST AND DEMO_ORDER_ITEMS. PRODUCT_ID = (SELECT MIN(DEMO_PRODUCT_INFO. PRODUCT_ID) FROM DEMO_PRODUCT_INFO WHERE PRODUCT_NAME = 'Jacket')) JACKET, (SELECT SUM(QUANTITY) FROM DEMO_ORDER_ITEMS, DEMO_ORDERS, DEMO_CUSTOMERS WHERE DEMO_ORDERS. ORDER_ID = DEMO_ORDER_ITEMS. ORDER_ID AND DEMO_ORDERS. CUSTOMER_ID = DEMO_CUSTOMERS. CUSTOMER_ID AND DEMO_CUSTOMERS. CUST_STATE = DEMO_STATES. ST AND DEMO_ORDER_ITEMS. PRODUCT_ID = (SELECT MIN(DEMO_PRODUCT_INFO. PRODUCT_ID) FROM DEMO_PRODUCT_INFO WHERE PRODUCT_NAME = 'Blouse')) BLOUSE from DEMO_STATES

Вариант 2 • create or replace FUNCTION GET_CUSTOM_DATA RETURN TABLE_OF_VALUES PIPELINED AS bag_count NUMBER; blouse_count NUMBER; jacket_count NUMBER; BEGIN FOR rec 0 IN (SELECT ST FROM DEMO_STATES) LOOP bag_count : = 0; blouse_count : = 0; jacket_count : = 0; FOR rec IN (SELECT PRODUCT_NAME, QUANTITY FROM DEMO_ORDER_ITEMS itms, DEMO_ORDERS ordrs, DEMO_ CUSTOMERS cstmrs, DEMO_PRODUCT_INFO info WHERE itms. ORDER_ID = ordrs. ORDER_ID AND ordrs. CUSTOMER_ID = cstmrs. CUSTOMER_ID AND cstmrs. CUST_STATE = rec 0. ST AND info. PRODUCT_ID = itms. PRODUCT_ID) LOOP case when rec. product_name = 'Bag' then bag_count : = bag_count + rec. quantity; when rec. product_name = 'Jacket' then jacket_count : = jacket_count + rec. quantity; when rec. product_name = 'Blouse' then blouse_count : = blouse_count + rec. quantity; else null; end case; END LOOP; pipe row (values_row (rec 0. st, bag_count, jacket_count, blouse_count)); END LOOP; RETURN; END;

Вариант 3 • SELECT SUM(DECODE(PRODUCT_NAME, 'Bag', QUANTITY, 0)) BAG, • SUM(DECODE(PRODUCT_NAME, 'Blouse', QUANTITY, 0)) BLOUSE, • SUM(DECODE(PRODUCT_NAME, 'Jacket', QUANTITY, 0)) JACKET • from demo_customers cstmrs, demo_order_items itms, demo_orders ordrs, demo_product_info • WHERE itms. ORDER_ID = ordrs. ORDER_ID • AND ordrs. CUSTOMER_ID = cstmrs. CUSTOMER_ID • AND info. PRODUCT_ID = itms. PRODUCT_ID group by cstmrs. cust_state

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

Запросы, использующие упорядоченность • Типичные задачи – построение нарастающих итогов и разного рода индикаторов для финансовых рядов. Например, • Нарастающие итоги: • SUM(t) = SUM(t-1) + Value(t) • Экспоненциальная скользящая средняя: • EMA (t) = EMA(t-1) + (Price(t) – EMA(t-1)) * 2/11

Упражнение 2 • Написать запрос, выдающий нарастающие итоги (в двух стилях) для таблицы c полями (PERIOD, VALUE). • select period, value, (select SUM(value) from test. In where test. In. period <= test. period) result • from test order by period

create or replace function_test return table_of_values pipelined as summ NUMBER; begin summ : = 0; for rec in (select * from test order by period) loop summ : = summ + rec. value; PIPE ROW (VALUES_ROW(to_char(rec. period, 'DD'), rec. value, summ, NULL)); • end loop; • return; • end; • • •

Упражнение 3 • В демонстрационной базе ORACLE найти customers, заказавших все существующие виды продукции.

Правила гранулярности запросов • Количество операторов SQL, передаваемых в процессе обработки одного запроса не должно зависеть от количества обрабатываемых строк. • Идентичные операторы SELECT с идентичными параметрами не должны исполняться многократно. • Если некоторая строка таблицы передается в приложение, то в этом же запросе следует передать все поля таблицы, которые могут понадобиться приложению. • Все условия на выбираемые строки таблиц следует включать в операторы SQL.

Домашнее задание 6 • • Экспортируйте 5 -мин котировки индекса RTS http: //www. finam. ru/analysis/profile 253 AC 00007/? market=1&em=3&code=SBER &df=24&mf=9&yf=2014&from=24. 10. 2014&dt=24&mt=9&yt=2014&to=24. 10. 201 4&p=7&f=SBER_141024&e=. txt&cn=SBER&dtf=1&tmf=1&MSOR=1&msti me=on&mstimever=1&sep 2=1&datf=1&at=1 • за 24 ноября 2014 года и постройте приложение, в котором будет отображаться экспоненциальное скользящее среднее в виде графика (в качестве цены можно использовать (OPEN + HIGH + LOW + CLOSE)/4) • • • Ссылку на приложение, логин и пароль для входа отправить по адресу: N. Grafeeva@spbu. ru Тема - DB_Applcation_2014_job 6