Главная страница

Методичка. Базы данных (ПОВТ) 2009. Методические указания для студентов дневного отделения специальности "Программное обеспечение вычислительной техники" Барнаул 2009 2


Скачать 2 Mb.
НазваниеМетодические указания для студентов дневного отделения специальности "Программное обеспечение вычислительной техники" Барнаул 2009 2
АнкорМетодичка. Базы данных (ПОВТ) 2009.pdf
Дата04.10.2019
Размер2 Mb.
Формат файлаpdf
Имя файлаМетодичка. Базы данных (ПОВТ) 2009.pdf
ТипМетодические указания
#13290
страница9 из 11

Подборка по базе: Программа обучения студентов (Syllabus) по дисциплине Основы пра, методичка по цитологии 2019 для студентов.pdf, Методические указания к самостоятельной работе по дисциплине «Ме, Метод указания-19.docx, Методические указания по практике .doc, Методические указания по практике .doc, ГАОУ СПО Десмургия - Методические рекомендации для студентов по .
1   2   3   4   5   6   7   8   9   10   11
PART_NAME
FROM SUPPLIER S, PART P
WHERE S.SUPPLIER_ID = P.SUPPLIER_ID;
SUPPLIERJD SUPPLIER_NAME
PART_NBR PART_NAME
101 Pacific Disks, Inc.
HD211 20 GB Hard Disk
102 Silicon Valley Microchips
P3000 3000 MHz Processor
Обратите внимание на то, что, хотя поставщиков трое, запрос выводит только двоих, потому что третий поставщик (Blue River Electronics) в данный момент ничего не поставляет.
Когда Oracle выполняет объединение между таблицами SUPPLIER и PART, сопоставляются столбцы SUPPLIER_ID этих двух таблиц (как указано в условии объединения). Так как для
SUPPLIER_ID = 103 не существует соответствующих записей в таблице PART, этот поставщик не включается в результирующее множество. Такой тип объединения является наиболее естественным и называется внутренним объединением.
Понятие внутреннего объединения легче пояснить в терминах декартова произведения. При выполнении объединения таблиц SUPPLIER и PART сначала формируется декартово произведение (физически оно не материализуется), а затем условия
PART_NBR NAME SUPPLIER_ID STATUS INVENTORY_QTY UNIT_COST RESUPPLY_DATE
HD211 20
GB
Hard
Disk
101 ACTIVE
5 2000 12-DEC-OO
P3000 3000
MHz
Processor
102 ACTIVE
12 600 03-NOV-OO

71
инструкции WHERE ограничивают результат только теми строками, в которых совпадают значения SUPPLIER_ID.
Но хотелось бы получить полный список поставщиков, включающий и тех, кто в данный момент ничего не поставляет. Oracle предоставляет специальный тип объединения, который позволяет включать в результирующее множество строки одной таблицы, для которых не найдены соответствующие строки в другой таблице. Такое объединение называется внешним (outer). Внешнее объединение позволит вывести строки для всех поставщиков, а если поставщик в настоящий момент поставляет какие-то детали, то и соответствующие строки деталей. Если в настоящий момент поставщик не поставляет детали, в результирующем множестве в столбцах таблицы PART будут возвращены значения
NULL.
Синтаксис внешнего объединения несколько отличается от синтаксиса внутреннего объединения. Применяется специальный оператор, называемый оператором внешнего
объединения, который выглядит как знак «плюс», заключенный в круглые скобки, то есть
(+). Этот оператор используется в условии объединения инструкции WHERE вслед за именем поля той таблицы, которую вы хотите рассматривать как необязательную. В рассматриваемом примере про детали и поставщиков таблица PART не содержит информацию об одном поставщике. Просто добавляем оператор (+) к условию объединения со стороны таблицы PART. Запрос и результирующее множество будут выглядеть следую- щим образом:
SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME
PART_NAME
FROM SUPPLIER S, PART P
WHERE S.SUPPLIER_ID = P.SUPPLIER_ID (+);
SUPPLIER_ID SUPPLIER_NAME
PART_NBR PART_NAME
101 Pacific Disks, Inc.
HD211 20 GB Hard Disk
102 Silicon Valley Microchips
P3000 3000 MHz Processor
103 Blue
River
Electronics
Заметьте, что оператор (+) следует за Р.SUPPLIER_ID, что делает таблицу PART необязательной (в данном объединении). Если поставщик ничего не поставляет в настоящий момент, Oracle создаст для данного поставщика запись в таблице PART со значениями NULL во всех ячейках. Результирующее множество теперь содержит всех поставщиков независимо от состояния их текущих поставок. Как видите, столбцы PART для поставщика с идентификатором 103 содержат NULL.
Оператор внешнего объединения (+) может появляться как в левой, так и в правой части условия объединения. Вы только должны быть уверены в том, что применяете оператор к соответствующей таблице (в контексте данного запроса). Например, если поменять местами части оператора равенства из предыдущего примера, это никак не пов- лияет на результат:
SELECT S.SUPPLIER_ID, S.NAME SUPPLIER.NAME, P.PART_NBR, P.NAME
PART.NAME
FROM SUPPLIER S, PART P
WHERE P.SUPPLIER_ID (+) = S.SUPPLIER_ID;
SUPPLIER_ID SUPPLIERJAME
PART_NBR PART_NAME
101 Pacific Disks, Inc.
HD211 20 GB Hard Disk
102 Silicon Valley Microchips
P3000 3000 MHz Processor
103 Blue
River
Electronics

72
Ограничения, налагаемые на внешние объединения
Существует ряд правил и ограничений, относящихся к использованию внешних объединений в запросах. Если в запросе выполняется внешнее объединение, Oracle не разрешает использовать в этом же запросе некоторые другие операции. Далее мы поговорим о таких ограничениях и о некоторых способахих обхода.
• Оператор внешнего объединения может присутствовать только в одной части условия объединения. При попытке использовать его в обеих частях возникает ошибка ORA-
1468. Например:
SELECT S.SUPPLIER.ID, S.NAME SUPPLIER.NAME, P.PART.NBR, P.NAME PART.
NAME
FROM SUPPLIER S, PART P
WHERE S.SUPPLIER.ID (+) = P,SUPPLIER.ID (+);
WHERE S.SUPPLIER.ID (+) = P.SUPPLIER_ID (+)
*
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table
• Если в объединении участвует более двух таблиц, то каждая из таблиц в запросе не может участвовать во внешнем объединении с более чем одной другой таблицей.
• В условии внешнего объединения, содержащем оператор (+), запрещено использование оператора IN. Например:
SELECT E.LNAME, J.FUNCTION
FROM EMPLOYEE E, JOB J
WHERE E.JOB.ID (+) IN (66B, 670, 667);
WHERE E.JOB.ID (+) IN (668, 670, 667)
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
Условие внешнего объединения, содержащее оператор (+), нельзя комбинировать с другими условиями при помощи оператора OR. Например:
SELECT E.LNAME, D.NAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT.ID = D.DEPT.ID (+) OR D.DEPT.ID =10;
WHERE E.DEPT_ID = D.DEPT_ID (+)
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
• Условие внешнего объединения, содержащее оператор (+), не может содержать подзапрос. Например:
SELECT E.LNAME
FROM EMPLOYEE E
WHERE E.DEPT_ID (+) =
(SELECT DEPT_ID FROM DEPARTMENT WHERE NAME = 'ACCOUNTING');
(SELECT DEPT_ID FROM DEPARTMENT WHERE NAME = 'ACCOUNTING')
ERROR at line 4:
ORA-01799: a column may not be outer-joined to a subquery
Чтобы достичь желаемого эффекта и избежать ошибки, можно использовать встроенное представление:
SELECT E.LNAME
FROM EMPLOYEE E,
(SELECT DEPT_ID FROM DEPARTMENT WHERE NAME = 'ACCOUNTING') V
WHERE E.DEPTJD (+) = V.DEPT_ID;

73
Групповые операции
В повседневной работе SQL-программист часто имеет дело с групповыми операциями. Используя SQL для доступа к базе данных, часто задаются вопросы, подобные перечисленным ниже:
• Какова максимальная заработная плата в данном подразделении?
• Сколько в каждом подразделении менеджеров?
• Сколько заказчиков существует для каждого продукта?
• Можно ли вывести среднемесячное значение продаж для каждого региона?
Для ответа на такие вопросы необходимы групповые операции. Oracle предоставляет широкий спектр возможностей по обработке групповых операций, в том числе обобщающие функции, инструкции GROUP BY и HAVING, функцию GROUPING и расширения инструкции GROUP BY: ROLLUP и CUBE.
Обобщающие функции
Если говорить по существу, обобщающая функция (aggregate function) суммирует результаты выражения для некоторого количества строк, возвращая одно значение.
Синтаксис большинства обобщающих функций таков:
обобщающая_функция([DISTINCT | ALL] выражение)
Приведем перечень элементов конструкции:
обобщающая функция
Указывает имя функции, например SUM, COUNT, AVG, MAX, MIN и др.
DISTINCT
Указывает, что обобщающая функция должна учитывать только неповторяющиеся значения выражения.
ALL
Указывает, что обобщающая функция должна учитывать все значения выражения, в том числе и все дублирующиеся. По умолчанию считается, что использовано ALL.
выражение
Указывает столбец или любое другое выражение, по которому необходимо выполнить обобщение.
Давайте рассмотрим простой пример. Для нахождения максимальной зарплаты сотрудников SQL-оператор использует функцию МАХ:
SELECT MAX(SALARY) FROM EMPLOYEE;
MAX(SALARY)
5000
Инструкция GROUP BY
Инструкция GROUP BY, используемая совместно с обобщающими функциями, разбивает результирующее множество на несколько групп, а затем для каждой группы выдается одна строка сводной информации. Например, если нужно вычислить общее количество заказов каждого клиента, выполним следующий запрос:
SELECT CUST.NBR, COUNT(ORDER_NBR)
FROM CUST.ORDER
GROUP BY CUST.NBR;
Запрос выдает одну сводную строку для каждого клиента. В этом заключается суть запроса GROUP BY. Мы просим Oracle сгруппировать (GROUP) результаты по номеру клиента (BY CUST_NBR), поэтому для каждого уникального значения CUST_NBR порождается одна строка вывода. Каждое значение для определенного клиента представляет собой сводную информацию по всем строкам данного клиента.
Необобщенное выражение CUST_NBR из списка SELECT присутствует и в инструкции GROUP BY. Если в списке SELECT присутствует смесь обобщенных и необобщенных значений, SQL считает, что вы собираетесь выполнить операцию GROUP

74
BY, поэтому все необобщенные выражения должны быть указаны и в инструкции GROUP
BY. Если этого не сделать, SQL выдаст сообщение об ошибке.
Аналогично, если не включить все необобщенные выражения списка SELECT в инструкцию GROUP BY, то SQL выдаст такую ошибку:
SELECT CUST_NBR, SALES_EMP_ID, COUNT(ORDER_NBR)
FROM CUST_ORDER
GROUP BY CUST_NBR;
SELECT CUST_NBR, SALES_EMP_ID, COUNT(ORDER_NBR)
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Наконец, не разрешено использование групповой (обобщающей) функции в инструкции GROUP BY. При попытке такого использования, как в приведенном ниже примере, вы получите следующее сообщение об ошибке:
SELECT CUST_NBR, COUNT(ORDER_NBR)
FROM CUST_ORDER
GROUP BY CUST_NBR, COUNT(ORDER_NBR);
GROUP BY CUST_NBR, COUNT(ORDER_NBR)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
Инструкция HAVING
Инструкция HAVING тесно связана с инструкцией GROUP BY. Инструкция HAVING используется для наложения фильтра на группы, созданные инструкцией GROUP BY. Если запрос содержит инструкцию HAVING и инструкцию GROUP BY, результирующее множество будет содержать только те группы, которые удовлетворяют условию, указанному в инструкции HAVING. Давайте рассмотрим несколько примеров, иллюстрирующих вышесказанное. Приведенный ниже запрос возвращает количество заказов каждого клиента:
SELECT CUST_NBR, COUNT(ORDER_NBR)
FROM CUST_ORDER
GROUP BY CUST_NBR
HAVING CUST_NBR < 260;
CUST_NBR COUNT(ORDERJIBR)
201 2 231 6 244 2 255 6
Заметьте, что в выводе присутствуют только клиенты с номерами, меньшими, чем
260. Это объясняется тем, что в инструкции HAVING указано условие CUST_NBR < 260.
Количество заказов подсчитывается для всех клиентов, но выводятся только те группы, для которых выполнено условие инструкции HAVING.
Этот пример является не очень удачной иллюстрацией возможностей инструкции
HAVING; в данном случае она просто указывает данные, которые не должны включаться в результирующее множество. Было бы эффективнее использовать WHERE CUST.NBR < 260, а не HAVING CUST.NBR < 260, так как инструкция WHERE исключает строки из
рассмотрения до проведения группировки, a HAVING устраняет уже созданные
группы. Правильнее было бы записать предыдущий запрос так:
SELECT CUST_NBR, COUNT(OROER_NBR)
FROM CUST_ORDER

75
WHERE CUST_NBR < 260;
Следующий пример демонстрирует более удачное применение инструкции HAVING:
SELECT CUST_NBR, COUNT(OROER_NBR)
FROM CUST_ORDER
GROUP BY CUST.NBR
HAVING COUNT(ORDER_NBR) > 2;
Обратите внимание на использование в инструкции HAVING обобщающей функции.
Здесь инструкция HAVING применена надлежащим образом, так как результат выполнения обобщающей функции доступен только после проведения группировки.
Синтаксис инструкции HAVING подобен синтаксису инструкции WHERE. Но для условия инструкции HAVING существует одно ограничение. Это условие может относиться только к выражениям списка SELECT или инструкции GROUP BY. Если указать в инструкции HAVING выражение, не содержащееся ни в списке SELECT, ни в инструкции
GROUP BY, то в ответ будет выдано сообщение об ошибке. Например:
Обработка дат и времени
Разработчики баз данных постоянно имеют дело с данными, относящимися к датам и времени. Потребность в эффективной обработке значений дат и времени становится критичной на рубеже веков, когда приходится изобретать способы корректного управления двузначными значениями годов, когда они переходят от 99 к 00, а затем к 01. В эпоху глобальной электронной коммерции понятие времени актуально как никогда ранее: торговля происходит двадцать четыре часа в сутки во всех временных зонах.
База данных должна эффективно и рационально организовывать хранение, извлечение и манипулирование следующих типов данных:
• Дата
• Время
• Интервалы дат и времени
• Часовые пояса
Обработка дат и времени в Oracle продуманна и эффективна. Oracle9i обеспечивает удобную работу с датами и временем. В Oracle9i вводится новый ряд возможностей, включая поддержку долей секунды, интервалов дат и времени и часовых поясов.
Вставка дат в БД и извлечение дат из БД
В реальном мире даты не всегда представляются в формате типа данных DATE
Oracle. Постоянно будет возникать необходимость преобразования значений типа DATE в другие типы данных и наоборот. Это особенно важно при сопряжении базы данных Oracle с внешней системой, например, если данные получаются из внешней системы, в которой даты представлены символьными строками (или даже числами), или при отправке данных из базы
Oracle в другие приложения, которые не поддерживают тип DATE. Также нужно преобразовывать значения DATE при отображении дат на экране или создании отчета.
Oracle предоставляет две чрезвычайно полезные функции преобразования дат:
• TO.DATE
• TO_CHAR
Как следует из их названий, функция TO_DATE используется для преобразования символьных или числовых данных в значение типа DATE, a функция TO_CHAR выполняет преобразование значения DATE в строку символов. Обсуждаемые далее в этом разделе форматы даты хорошо приспособлены для таких преобразований.
TO_DATE
TO_DATE - это встроенная функция SQL, конвертирующая символьную строку в дату. На вход функции TO_DATE может подаваться символьная строка, переменная PL/SQL или столбец базы данных типа CHAR или VARCHAR2.

76
Вызов TO_DATE выглядит следующим образом:
ТО_DАТЕ(строка [, формат])
Перечислим элементы конструкции:
строка
Символьная строка, переменная PL/SQL или столбец базы данных, содержащий символьные (или числовые) данные, преобразуемые в дату.
формат
Задает формат преобразуемой строки. Формат должен представлять собой допустимую комбинацию элементов формата.
Указание формата даты является необязательным. Если не задавать формат, то будет считаться, что строка имеет формат по умолчанию (определяемый параметром
NLS_DATE_FORMAT).
С помощью функции TO_DATE можно преобразовать число в формат DATE. Когда вы подаете на вход функции TO_DATE число, Oracle неявно преобразует введенное число в строку, затем эта строка передается функции TO_DATE.
Использование для даты формата по умолчанию
Каждая база данных Oracle имеет формат даты по умолчанию. Если администратор базы данных не определил ничего иного, то этот формат таков:
DD-MON-YY
В Oracle Database 10g Express Edition по умолчанию установлен следующий формат даты: DD.MM.RR.
При вызове функции TO_DATE без явного указания формата даты Oracle считает, что строка ввода имеет формат даты по умолчанию. Следующий оператор INSERT преобразует строку в формате по умолчанию в значение типа DATE и вставляет его в таблицу
EMPLOYEE:
INSERT INTO EMPLOYEE
(EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY,
HIRE.DATE)
VALUES
(2304, -John', -Smith-, 20, 1258, 20000, TO_DATE('22-OCT-99'));
1 row created.
SELECT * PROM EMPLOYEE;
EMP_ID FNAME LNAME DEPT_ID MANAGER_EMP_ID
SALARY HIRE_DATE
2304 John Smith
20 1258 20000 22-OCT-99
Обратите внимание, что столбец HIRE_DATE имеет тип DATE, и символьная строка '22-OCT-99' была преобразована в дату функцией TO_DATE. В данном случае указание формата не требуется, так как вставляемая строка имеет формат даты по умолчанию. В действительности, если предлагаемая строка имеет формат даты по умолчанию, не нужна и сама функция TO_DATE. Oracle автоматически выполняет неявное преоб разование типов, как в приведенном ниже примере:
INSERT INTO EMPLOYEE
(EMP.ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY,
HIRE.DATE)
VALUES
(2304, 'John', •Smith', 20, 1258, 20000, '22-OCT-99');
1 row created.
Но несмотря на то что Oracle производит неявное преобразование типов, рекомендуем всегда использовать явное преобразование, так как неявные преобразования не очевидны и

77
могут привести к путанице. К тому же, если администратор базы данных изменит формат даты по умолчанию, неявные преобразования могут и не привести к желаемому результату.
TO_CHAR
Функция TO_CHAR является обратной по отношению к TO_DATE и преобразует дату в символьную строку. Вызов TO_CHAR выглядит следующим образом:
ТО_СНАR(дата [,формат])
Рассмотрим элементы конструкции:
дата
Переменная PL/SQL или столбец базы данных типа DATE.
формат
Указывает формат выводимой строки. Формат должен представлять собой допустимую комбинацию форматов.
Указание формата даты является необязательным. Если не задавать формат, то дата выводится в формате по умолчанию (определяемом параметром NLS_DATE_FORMAT).
В следующем примере функция TO_CHAR применяется для преобразования вводимой даты в строку с использованием формата даты по умолчанию:
1   2   3   4   5   6   7   8   9   10   11


написать администратору сайта