Функции в MySQL

Размещено на http://www.allbest.ru/

Практическая работа

Функции в MySQL

Задание 1. Встроенные функции

математический функция программирование

Функции - это операции, позволяющие манипулировать данными. В MySQL можно выделить несколько групп встроенных функций:

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

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

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

Функции даты и времени. Используются для управления значениями даты и времени, например, для возвращения разницы между датами.

Системные функции. Возвращают служебную информацию СУБД.

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

Давайте в качестве примера рассмотрим интернет-магазин.

Концептуальная модель:



Реляционная модель:

Итак, смотрим на последнюю схему и создаем БД - shop.

create database shop;

Выбираем ее для работы:

use shop;

И создаем в ней 8 таблиц, как в схеме: Покупатели (customers), Поставщики (vendors), Покупки (sale), Поставки (incoming), Журнал покупок (magazine_sales), Журнал поставок (magazine_incoming), Товары (products), Цены (prices). Один нюанс, наш магазин будет торговать книгами, поэтому в таблицу Товары мы добавим еще один столбец - Автор (author), в принципе это необязательно, но так как-то привычнее.



Обратите внимание, что в таблицах Журнал покупок, Журнал поставок и Цены первичные ключи - составные, т.е. их уникальные значения состоят из пар значений (в таблице не может быть двух строк с одинаковыми парами значений). Названия столбцов этих пар значений и указываются через запятую после ключевого слова PRIMARY KEY.

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



Итак, в нашем магазине 24 наименования товара, привезенные в трех поставках от трех поставщиков, и совершенно три продажи. Все готово, можем приступать к изучению встроенных функций MySQL.

Задание 2. Итоговые функции, вычисляемые столбцы и представления

Итоговые функции еще называют статистическими, агрегатными или суммирующими. Эти функции обрабатывают набор строк для подсчета и возвращения одного значения. Таких функций всего пять:

AVG() Функция возвращает среднее значение столбца.

COUNT() Функция возвращает число строк в столбце.

MAX() Функция возвращает самое большое значение в столбце.

MIN() Функция возвращает самое маленькое значение в столбце.

SUM() Функция возвращает сумму значений столбца.

С одной из них - COUNT() - мы уже познакомилисьhttp://www.site-do.ru/db/sql8.php. Сейчас познакомимся с остальными. Предположим, мы захотели узнать минимальную, максимальную и среднюю цену на книги в нашем магазине. Тогда из таблицы Цены (prices) надо взять минимальное, максимальное и среднее значения по столбцу price. Запрос простой:

SELECT MIN(price), MAX(price), AVG(price) FROM prices;

Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик "Дом печати" (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:

Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком "Дом печати" (id=2):

Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:

Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:



В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца. Возможность создания таких столбцов предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового столбца отделяется словом AS:

Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик "Дом печати". Синтаксис для использования функции SUM() следущий:

SELECT SUM(имя_столбца) FROM имя_таблицы;

Имя столбца нам известно - summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать? Для таких случаев в MySQL существуют Представления. Представление - это запрос на выборку, которому присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования.

Синтаксис создания представления следующий:

CREATE VIEW имя_представления AS запрос;

Давайте сохраним наш запрос, как представление с именем report_vendor:

CREATE VIEW report_vendor AS

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price,

magazine_incoming.quantity*prices.price AS summa

FROM magazine_incoming, prices

WHERE magazine_incoming.id_product= prices.id_product AND id_incoming=

(SELECT id_incoming FROM incoming WHERE id_vendor=2);

Вот теперь можно использовать итоговую функцию SUM():

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

Вычисляемые поля (столбцы)

На примере мы рассмотрели математическое вычисляемое поле. Здесь хотелось бы добавить, что использовать можно не только операцию умножения (*), но и вычитание (-), и сложение (+), и деление (/). Синтаксис следующий:

SELECT имя_столбца_1, имя_столбца_2, имя_столбца_1*имя_столбца_2 AS имя_вычисляемого_столбца

FROM имя_таблицы;

Второй нюанс - ключевое слово AS, мы его использовали для задания имени вычисляемого столбца. На самом деле с помощью этого ключевого слова задаются псевдонимы для любых столбцов. Зачем это нужно? Для сокращения и читаемости кода. Например, наше представление могло бы выглядеть так:

CREATE VIEW report_vendor AS

SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa

FROM magazine_incoming AS A, prices AS B

WHERE A.id_product= B.id_product AND id_incoming=

(SELECT id_incoming FROM incoming WHERE id_vendor=2);

Согласитесь, что так гораздо короче и понятнее.

Представления

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

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

Строковые функции Sql

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

CONCAT(str1,str2...)

Возвращает строку, созданную путем объединения аргументов (аргументы указываются в скобках - str1,str2...). Например, в нашей таблице Поставщики (vendors) есть столбец Город (city) и столбец Адрес (address). Предположим, мы хотим, чтобы в результирующей таблице Адрес и Город указывались в одном столбце, т.е. мы хотим объединить данные из двух столбцов в один. Для этого мы будем использовать строковую функцию CONCAT(), а в качестве аргументов укажем названия объединяемых столбцов - city и address:

Обратите внимание, объединение произошло без разделения, что не очень читабельно. Давайте подправим наш запрос, чтобы между объединяемыми столбцами был пробел:

Как видите, пробел считается тоже аргументом и указывается через запятую. Если объединяемых столбцов было бы больше, то указывать каждый раз пробелы было бы нерационально. В этом случае можно было бы использовать строковую функцию CONCAT_WS(разделитель, str1,str2...), которая помещает разделитель между объединяемыми строками (разделитель указывается, как первый аргумент). Наш запрос тогда будет выглядеть так:

SELECT CONCAT_WS(' ', city, address) FROM vendors;

Результат внешне не изменился, но если бы мы объединяли 3 или 4 столбца, то код значительно бы сократился.

INSERT(str, pos, len, new_str)

Возвращает строку str, в которой подстрока, начинающаяся с позиции pos и имеющая длину len символов, заменена подстрокой new_str. Предположим, мы решили в столбце Адрес (address) не отображать первые 3 символа (сокращения ул., пр., и т.д.), тогда мы заменим их на пробелы:



То есть три символа, начиная с первого, заменены тремя пробелами.

LPAD(str, len, dop_str) Возвращает строку str, дополненную слева строкой dop_str до длины len. Предположим, мы хотим, чтобы при выводе городов поставщиков они располагались бы справа, а пустое пространство заполнялось бы точками:

Обратите внимание, значение len ограничивает количество выводимых символов, т.е. если название города будет длиннее 15 символов, то оно будет обрезано.

RPAD(str, len, dop_str)

Возвращает строку str, дополненную справа строкой dop_str до длины len. Предположим, мы хотим, чтобы при выводе городов поставщиков они располагались бы слева, а пустое пространство заполнялось бы точками:



Обратите внимание, значение len ограничивает количество выводимых символов, т.е. если название города будет длиннее 15 символов, то оно будет обрезано.

LTRIM(str)

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

SELECT LTRIM(city) FROM vendors;

В нашем случае лишних пробелов не было, поэтому и результат внешне мы не увидим.

RTRIM(str)

Возвращает строку str, в которой удалены все конечные пробелы:

SELECT RTRIM(city) FROM vendors;

В нашем случае лишних пробелов не было, поэтому и результат внешне мы не увидим.

TRIM(str)

Возвращает строку str, в которой удалены все начальные и конечные пробелы:

SELECT TRIM(city) FROM vendors;

LOWER(str)

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

Видите, какая абракадабра получилась. А вот с латиницей все в порядке:

UPPER(str)

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



LENGTH(str)

Возвращает длину строки str. Например, давайте узнаем сколько символов в наших адресах поставщиков:

LEFT(str, len)

Возвращает len левых символов строки str. Например, пусть в городах поставщиков выводится только первые три символа:

RIGHT(str, len)

Возвращает len правых символов строки str. Например, пусть в городах поставщиков выводится только последние три символа:

REPEAT(str, n)

Возвращает строку str n-количество раз. Например:

REPLACE(str, pod_str1, pod_str2)

Возвращает строку str, в которой все подстроки pod_str1 заменены подстроками pod_str2. Например, пусть мы хотим, чтобы в городах поставщиков вместо длинного 'Санкт-Петербург' выводилось короткое 'СПб':



REVERSE(str)

Возвращает строку str, записанную в обратном порядке:

LOAD_FILE(file_name)

Эта функция читает файл file_name и возвращает его содержимое в виде строки. Например, создайте файл proverka.txt, напишите в нем какой-нибудь текст (лучше латиницей, чтобы не было проблем с кодировками), сохраните его на диске С и сделайте следующий запрос:

Обратите внимание, необходимо указывать абсолютный путь к файлу.

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

Задание 3. Функции даты и времени

Эти функции предназначены для работы с календарными типами данных. Рассмотрим наиболее применимые.

CURDATE(), CURTIME() и NOW()

Первая функция возвращает текущую дату, вторая - текущее время, а третья - текущую дату и время. Сравните:

Функции CURDATE() и NOW() удобно использовать для добавления в базу данных записей, использующих текущее время. В нашем магазине все поставки и продажи используют текущее время. Поэтому для добавления записей о поставах, и продажах удобно использовать функцию CURDATE(). Например, пусть в наш магазин пришел товар, давайте добавим информацию об этом в таблицу Поставка (incoming):



Если бы мы хранили дату поставки с типом datatime, то нам больше подошла бы функция NOW().

ADDDATE(date, INTERVAL value) Функция возвращает дату date, к которой прибавлено значение value. Значение value может быть отрицательным, тогда итоговая дата уменьшится. Давайте посмотрим, когда наши поставщики делали поставки товара:

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

В качестве значения value могут выступать не только дни, но и недели (WEEK), месяцы (MONTH), кварталы (QUARTER) и годы (YEAR). Давайте для пример уменьшим дату поставки для второго поставщика на 1 неделю:

В нашей таблице Поставки (incoming) мы использовали для столбца Дата поставки (date_incoming) тип date. Этот тип данных предназначен для хранения только даты. А вот если бы мы использовали тип datatime, то у нас отображалась бы не только дата, но и время. Тогда мы могли бы использовать функцию ADDDATE и для времени. В качестве значения value в этом случае могут выступать секунды (SECOND), минуты (MINUTE), часы (HOUR) и их комбинации:

минуты и секунды (MINUTE_SECOND),

часы, минуты и секунды (HOUR_SECOND),

часы и минуты (HOUR_MINUTE),

дни, часы, минуты и секунды (DAY_SECOND),

дни, часы и минуты (DAY_MINUTE),

дни и часы (DAY_HOUR),

года и месяцы (YEAR_MONTH).

Например, давайте к дате 15 апреля 2011 года две минуты первого прибавим 2 часа 45 минут:



SUBDATE(date, INTERVAL value)

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

PERIOD_ADD(period, n)

функция добавляет n месяцев к значению даты period. Нюанс: значение даты должно быть представлено в формате YYYYMM. Давайте к февралю 2011 (201102) прибавим 2 месяца:

TIMESTAMPADD(interval, n, date)

функция добавляет к дате date временной интервал n, значения которого задаются параметром interval. Возможные значения параметра interval:

FRAC_SECOND - микросекунды

SECOND - секунды

MINUTE - минуты

HOUR - часы

DAY - дни

WEEK - недели

MONTH - месяцы

QUARTER - кварталы

YEAR - годы

TIMEDIFF(date1, date2)

вычисляет разницу в часах, минутах и секундах между двумя датами.

DATEDIFF(date1, date2)

вычисляет разницу в днях между двумя датами. Например, мы хотим узнать, как давно поставщик 'Вильямс' (id=1) поставлял нам товар:

PERIOD_DIFF(period1, period2)

функция вычисляет разницу в месяцах между двумя датами, представленными в формате YYYYMM. Давайте узнаем разницу между январем 2010 и августом 2011:

TIMESTAMPDIFF(interval, date1, date2)

функция вычисляет разницу между датами date2 и date1 в единицах, указанных в параметре interval. Возможные значения параметра interval:

FRAC_SECOND - микросекунды

SECOND - секунды

MINUTE - минуты

HOUR - часы

DAY - дни

WEEK - недели

MONTH - месяцы

QUARTER - кварталы

YEAR - годы

SUBTIME(date, time)

функция вычитает из времени date время time:

DATE(datetime)

возвращает дату, отсекая время. Например:

TIME(datetime)

возвращает время, отсекая дату. Например:

TIMESTAMP(date)

функция принимает дату date и возвращает полный вариант со временем. Например:

DAY(date) и DAYOFMONTH(date)

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

DAYNAME(date), DAYOFWEEK(date) и WEEKDAY(date)

функции возвращают день недели, первая - его название, вторая - номер дня недели (отсчет от 1 - воскресенье до 7 - суббота), третья - номер дня недели (отсчет от 0 - понедельник, до 6 - воскресенье:

WEEK(date), WEEKOFYEAR(datetime)

обе функции возвращают номер недели в году, первая для типа date, а вторая - для типа datetime, у первой неделя начинается с воскресенья, у второй - с понедельника:

MONTH(date) и MONTHNAME(date)

обе функции возвращают значения месяца. Первая - его числовое значение (от 1 до 12), вторая - название месяца:



QUARTER(date)

функция возвращает значение квартала года (от 1 до 4):

YEAR(date) функция возвращает значение года (от 1000 до 9999):

DAYOFYEAR(date)

возвращает порядковый номер дня в году (от 1 до 366):



HOUR(datetime)

возвращает значение часа для времени (от 0 до 23):

MINUTE(datetime)

возвращает значение минут для времени (от 0 до 59):

SECOND(datetime)

возвращает значение секунд для времени (от 0 до 59):



EXTRACT(type FROM date)

возвращает часть date определяемую параметром type:

TO_DAYS(date) и FROM_DAYS(n)

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

UNIX_TIMESTAMP(date) и FROM_UNIXTIME(n)

взаимообратные функции. Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату:

TIME_TO_SEC(time) и SEC_TO_TIME(n)

взаимообратные функции. Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время:

MAKEDATE(year, n)

функция принимает год и номер дня в году и преобразует их в дату:



Задание 4. Функции форматирования даты и времени

Эти функции также предназначены для работы с календарными типами данных. Рассмотрим их подробнее.

DATE_FORMAT(date, format)

форматирует дату date в соответствии с выбранным форматом formate. Эта функция очень часто используется. Например, в MySQL дата имеет формат представления YYYY-MM-DD (год-месяц-число), а нам привычнее формат DD-MM-YYYY (число-месяц-год). Поэтому для привычного нам отображения даты ее необходимо переформатировать. Давайте сначала приведем запрос, а затем разберемся, как задавать формат:

Теперь дата выглядит для нас привычно. Для задания формата даты используются специальные определители. Для удобства перечислим их в таблице.



Опред	Описание
%a	Сокращенное наименование дня недели (Mon - понедельник, Tue - вторник, Wed - среда, Thu - четверг, Fri - пятница, Sat - суббота, Sun - воскресенье).
%b	Сокращенное наименование месяцев (Jan - январь, Feb - февраль, Mar - март, Apr - апрель, May - май, Jun - июнь, Jul - июль, Aug - август, Sep - сентябрь, Oct - октябрь, Nov - ноябрь, Dec - декабрь).
%c	Месяц в числовой форме (1 - 12).
%d	День месяца в числовой форме с нулем (01 - 31).
%D	День месяца в английском варианте (1st, 2nd...).
%e	День месяца в числовой форме без нуля (1 - 31).
%H	Часы с ведущим нулем от 00 до 23.
%h	Часы с ведущим нулем от 00 до 12.
%i	Минуты от 00 до 59.
%j	День года от 001 до 366.
%k	Часы c ведущим нулем от 0 до 23.
%l	Часы без ведущим нуля от 1 до 12.
%M	Название месяца без сокращения.
%M	Название месяца без сокращения.
%m	Месяц в числовой форме с ведущим нулем (01 - 12).
%p	АМ или РМ для 12-часового формата.
%r	Время в 12-часовом формате.
%s	Секунды от 00 до 59.
%T	Время в 24-часовом формате.
%u	Неделя (00 - 52), где первым днем недели считается понедельник.
%U	Неделя (00 - 52), где первым днем недели считается воскресенье.
%W	Название дня недели без сокращения.
%w	Номер дня недели (0 - воскресенье, 6 - суббота).
%Y	Год, 4 разряда.
%y	Год, 2 разряда.

STR_TO_DATE(date, format)

функция обратная предыдущей, она принимает дату date в формате format, а возвращает дату в формате MySQL.

Как видите, сама функция GET_FORMAT() возвращает формат представления, а вместе с функцией DATE_FORMAT() выдает дату в нужном формате. Сделайте сами запросы со всеми пятью стандартами и посмотрите на разницу.

TIME_FORMAT(time, format)

функция аналогична функции DATE_FORMAT(), но используется только для времени:

GET_FORMAT(date, format)

функция возвращает строку форматирования, соответствующую одному из пяти форматов времени:

EUR - европейский стандарт

USA - американский стандарт

JIS - японский индустриальный стандарт

ISO - стандарт ISO (международная организация стандартов)

INTERNAL - интернациональный стандарт

Эту функцию хорошо использовать совместно с предыдущей -

DATE_FORMAT()

Посмотрим на примере:



Как видите, сама функция GET_FORMAT() возвращает формат представления, а вместе с функцией DATE_FORMAT() выдает дату в нужном формате. Сделайте сами запросы со всеми пятью стандартами и посмотрите на разницу.

Ну вот, теперь вы знаете о работе с датами и временем в MySQL практически все. Это вам очень пригодится при разработке различных web-приложений. Например, если пользователь в форму на сайте вводит дату в привычном ему формате, вам не составит труда применить нужную функцию, чтобы в БД дата попала в нужном формате.

Задание 5. Хранимые процедуры

Как правило, мы в работе с БД используем одни и те же запросы, либо набор последовательных запросов. Хранимые процедуры позволяют объединить последовательность запросов и сохранить их на сервере. Это очень удобный инструмент, и сейчас вы в этом убедитесь. Начнем с синтаксиса:

CREATE PROCEDURE

имя_процедуры (параметры)

begin

операторы

end

Параметры это те данные, которые мы будем передавать процедуре при ее вызове, а операторы - это собственно запросы. Давайте напишем свою первую процедуру и убедимся в ее удобстве. Когда мы добавляли новые записи в БД shop, мы использовали стандартный запрос на добавление вида:

INSERT INTO customers (name, email) VALUE ('Иванов Сергей', 'sergo@mail.ru');

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

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50))

begin

insert into customers (name, email) value (n, e);

end

Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с ; на "//", чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора DELIMITER //:

Таким образом, мы указали СУБД, что выполнять команды теперь следует после //. Следует помнить, что переопределение разделителя осуществляется только на один сеанс работы, т.е. при следующем сеансе работы с MySql разделитель снова станет точкой с запятой и при необходимости его придется снова переопределять. Теперь можно разместить процедуру:

Итак, процедура создана. Теперь, когда нам понадобится ввести нового покупателя нам достаточно ее вызвать, указав необходимые параметры. Для вызова хранимой процедуры используется оператор CALL, после которого указывается имя процедуры и ее параметры. Давайте добавим нового покупателя в нашу таблицу Покупатели (customers):

Согласитесь, что так гораздо проще, чем писать каждый раз полный запрос. Проверим, работает ли процедура, посмотрев, появился ли новый покупатель в таблице Покупатели (customers):



Появился, процедура работает, и будет работать всегда, пока мы ее не удалим с помощью оператора DROP PROCEDURE название_процедуры.

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

Казалось бы, проще всего взять уже написанные представление и запрос к нему, объединить в хранимую процедуру и сделать идентификатор поставщика (id_vendor) входным параметром, вот так:

Но так процедура работать не будет. Все дело в том, что в представлениях не могут использоваться параметры. Поэтому нам придется несколько изменить последовательность запросов. Сначала мы создадим представление, которое будет выводить идентификатор поставщика (id_vendor), идентификатор продукта (id_product), количество (quantity), цену (price) и сумму (summa) из трех таблиц Поставки (incoming), Журнал поставок (magazine_incoming), Цены (prices):

А потом создадим запрос, который просуммирует суммы поставок интересующего нас поставщика, например, с id_vendor=2:

SELECT SUM(summa) FROM report_vendor WHERE id_vendor=2;

Вот теперь мы можем объединить два этих запроса в хранимую процедуру, где входным параметром будет идентификатор поставщика (id_vendor), который будет подставляться во второй запрос, но не в представление:

Проверим работу процедуры, с разными входными параметрами:

Как видите, процедура срабатывает один раз, а затем выдает ошибку, говоря нам, что представление report_vendor уже имеется в БД. Так происходит потому, что при обращении к процедуре в первый раз, она создает представление. При обращении во второй раз, она снова пытается создать представление, но оно уже есть, поэтому и появляется ошибка. Чтобы избежать этого возможно два варианта.

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

Проверяем работу:

call sum_vendor(1)//

call sum_vendor(2)//

call sum_vendor(3)//



Второй вариант - прямо в процедуре дописать команду, которая будет удалять представление, если оно существует:

Перед использованием этого варианта не забудьте удалить процедуру sum_vendor, а затем проверить работу:

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

Задание 6. Хранимые процедуры

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

SHOW PROCEDURE STATUS - позволяет просмотреть список имеющихся хранимых процедур. Правда просматривать этот список не очень удобно, т.к. по каждой процедуре выдается информация об имени БД, к которой процедура принадлежит, ее типе, учетной записи, от имени которой была создана процедура, о дате создания и изменения процедуры и т.д. И все-таки, если вам необходимо посмотреть, какие процедуры у вас есть, то стоит воспользоваться этим оператором.

SHOW CREATE PROCEDURE имя_процедуры - позволяет получить информацию о конкретной процедуре, в частности просмотреть ее код. Вид для просмотра также не очень удобный, но разобраться можно.

Попробуйте оба оператора в действии, чтобы знать, как это выглядит. А теперь рассмотрим более удобный вариант получения подобной информации. В системной базе данных MySQL есть таблица proc, где и хранится информация о процедурах. Так вот мы может сделать SELECT-запрос к этой таблице. Причем, если мы создадим привычный запрос:

SELECT * FROM mysql.proc//

То получим нечто такое же нечитабельное, как и при использовании операторов SHOW. Поэтому мы будем создавать запросы с условиями. Например, если мы создадим вот такой запрос:

SELECT name FROM mysql.proc//

То получим имена всех процедур всех баз данных, имеющихся на сервере. Нас, например, на данный момент интересуют только процедуры базы данных shop, поэтому изменим запрос:

SELECT name FROM mysql.proc WHERE db='shop'//

Вот теперь мы получили то, что хотели:

Если же мы хотим посмотреть только тело конкретной процедуры (т.е. от begin до end), то мы напишем такой запрос:

SELECT body FROM mysql.proc WHERE name='sum_vendor'//

И увидим вполне читабельный вариант:



Вообще, чтобы извлекать из таблицы proc необходимую вам информацию, надо просто знать, какие столбцы она содержит, а для этого можно воспользоваться знакомым нам оператором describe имя_таблицы, в нашем случае describe mysql.proc. Правда, вид у нее тоже не очень читабельный, поэтому приведем здесь названия наиболее востребованных столбцов:

db - имя БД, в которую сохранена процедура.

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

param_list - список параметров процедуры.

body - тело процедуры.

comment - комментарий к хранимой процедуре.

Столбцы db, name и body мы уже использовали. Запрос, извлекающий параметры процедуры sum_vendor составьте самостоятельно. А вот про комментарии к хранимым процедурам мы сейчас поговорим подробнее.

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

Создавать комментарии крайне просто. Для этого сразу после списка параметров, но еще до начала тела хранимой процедуры указываем ключевое слово COMMENT 'здесь комментарий'. Давайте удалим нашу процедуру sum_vendor и создадим новую, с комментарием:

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

Вообще-то, чтобы добавить комментарий, вовсе не обязательно было удалять старую процедуру. Можно было отредактировать имеющуюся хранимую процедуру с помощью оператора ALTER PROCEDURE. Давайте посмотрим, как это сделать, на примере процедуры ins_cust из прошлого задания. Эта процедура вводит информацию о новом покупателе в таблицу Покупатели (customers). Давайте добавим комментарий к этой процедуре:

ALTER PROCEDURE ins_cust COMMENT

Вводит информацию о новом покупателе в таблицу Покупатели.'//

И сделаем запрос к комментарию, чтобы проверить:

SELECT comment FROM mysql.proc WHERE name='ins_cust'//

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

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

Задание 7. Хранимые процедуры

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

Начнем изучение с операторов IF...THEN...ELSE. Если вы знакомы с каким-нибудь языком программирования, то эта конструкция вам знакома. Напомним, что условный оператор IF позволяет организовать ветвление программы. В случае хранимых процедур этот оператор позволяет выполнять разные запросы, в зависимости от входных параметров. На примере, как всегда, будет понятнее. Но для начала синтаксис:

Логика работы проста: если условие истинно, то выполняется запрос 1, в противном случае - запрос 2.

Предположим, каждый день мы устраиваем в нашем магазине счастливые часы, т.е. делаем скидку 10% на все книги в последний час работы магазина. Чтобы иметь возможность выбирать цену книги, нам необходимо иметь два ее варианта - со скидкой и без. Для этого, нам понадобится создать хранимую процедуру с оператором ветвления. Так как мы имеем всего два варианта цены, то удобнее в качестве входящего параметра иметь булево значение, которое, как вы помните, может принимать либо 0 - ложь, либо 1 - истина. Код процедуры может быть таким:



Т.е. на входе у нас параметр, который может являться, либо 1 (если скидка есть), либо 0 (если скидки нет). В первом случае будет выполнен первый запрос, во втором - второй. Давайте посмотрим, как работает наша процедура в обоих вариантах:

call discount(1)//

call discount(0)//



Оператор IF позволяет выбирать и большее количество вариантов запросов, в таком случае используется следующий синтаксис:

CREATE PROCEDURE имя_процедуры (параметры)

begin

IF(условие) THEN

запрос 1;

ELSEIF(условие) THEN

запрос 2;

ELSE

запрос 3;

END IF;

end

//

Причем блоков ELSEIF может быть несколько. Предположим, что мы решили делать скидки нашим покупателям в зависимости от суммы покупки, до 1000 рублей скидки нет, от 1000 до 2000 рублей - скидка 10%, более 2000 рублей - скидка 20%. Входным параметром для такой процедуры должна быть сумма покупки. Поэтому сначала нам надо написать процедуру, которая будет ее подсчитывать. Сделаем это по аналогии с процедурой sum_vendor, созданной в уроке 15, которая подсчитывала сумму товара по идентификатору поставщика.

Необходимые нам данные хранятся в двух таблицах Журнал покупок (magazine_sales) и Цены (prices).

CREATE PROCEDURE sum_sale(IN i INT)

COMMENT 'Возвращает сумму покупки по ее идентификатору.'

begin

DROP VIEW IF EXISTS sum_sale;

CREATE VIEW sum_sale AS SELECT magazine_sales.id_sale,

magazine_sales.id_product, magazine_sales.quantity,

prices.price, magazine_sales.quantity*prices.price AS summa

FROM magazine_sales, prices

WHERE magazine_sales.id_product=prices.id_product;

SELECT SUM(summa) FROM sum_sale WHERE id_sale=i;

end

//

Здесь перед параметром у нас появилось новое ключевое слово IN. Дело в том, что мы можем, как передавать данные в процедуру, так и передавать данные из процедуры. По умолчанию, т.е. если опустить слово IN, параметры считаются входными (поэтому раньше мы это слово и не использовали). Здесь же мы явно указали, что параметр i является входным. Если же нам понадобится извлечь какие-нибудь данные из хранимой процедуры, то мы будем использовать ключевое слово OUT, но об этом чуть позже.

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

Теперь нам надо написать процедуру, которая пересчитает итоговую сумму с учетом предоставляемой скидки. Здесь нам и понадобится оператор ветвления:

Т.е. мы передаем процедуре два входных параметра сумму (sm) и идентификатор покупки (i) и в зависимости от того, какая это сумма, выполняется запрос к представлению sum_sale на подсчет итоговой суммы покупки, умноженной на нужный коэффициент.

Осталось только сделать так, чтобы сумма покупки автоматически передавалась в эту процедуру. Для этого процедуру sum_discount хорошо бы вызвать прямо из процедуры sum_sale. Выглядеть это будет примерно вот так:

Вопросительный знак при вызове процедуры sum_discount поставлен, т.к. не понятно, как результат предыдущего запроса (т.е. итоговой суммы) передать в процедуру sum_discount. Кроме того, не понятно, как процедура sum_discount вернет результат своей работы. Вы, наверно, уже догадались, что для решения второго вопроса нам как раз и понадобится параметр с ключевым словом OUT, т.е. параметр, который будет возвращать данные из процедуры. Давайте введем такой параметр ss, и так как сумма может быть и дробным числом, зададим ему тип DOUBLE:

Итак, в обе процедуры мы ввели выходной параметр ss. Теперь вызов процедуры CALL sum_discount(?, i, ss); означает, что передавая два первых параметра, мы ждем возврата третьего параметра в процедуру sum_sale. Осталось только понять, как внутри самой процедуры sum_discount присвоить этому параметру какое-либо значение. Нам надо, чтобы в этот параметр передавался результат одного из запросов. И, конечно, в MySQL предусмотрен такой вариант, для этого используется ключевое слово INTO:

С помощью ключевого слова INTO, мы указали, что результат запроса надо передать в параметр ss.

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

Переменные позволяют сохранить результат текущего запроса для использования в следующих запросах. Объявление переменной начинается с символа собачки (@), за которой следует имя переменной. Объявляются они при помощи оператора SET. Например, объявим переменную z и зададим ей начальное значение 20.

SET @z='20'//

Переменная с таким значение теперь есть в нашей БД, можете проверить, сделав соответствующий запрос:

SELECT @z//

Переменные действуют только в рамках одного сеанса соединения с сервером MySQL. То есть после разъединения переменная перестанет существовать.

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

DECLARE имя_переменной тип DEFAULT значение_по_умолчанию_если_есть

Итак, давайте в нашей процедуре объявим переменную s, в которую будем сохранять значение суммы покупки с помощью ключевого слова INTO:

Эта переменная и будет первым входным параметром для процедуры sum_discount. Итак, окончательный вариант наших процедур выглядит так:

На случай, если вы запутались, давайте посмотрим алгоритм работы нашей процедуры sum_sale:

Мы вызываем процедуру sum_sale, указывая в качестве входного параметра идентификатор интересующей нас покупки, например id=1, и указывая, что второй параметр - выходной, переменный, являющийся результатом работы процедуры sum_discount:

call sum_sale(1, @sum_discount)//

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

Затем выполняется запрос к этому представлению на итоговую сумму по покупке с нужным идентификатором, и результат записывается в переменную s.

Теперь вызывается процедура sum_discount, в которой в качестве первого параметра выступает переменная s (сумма покупки), в качестве второго - идентификатор покупки i, а в качестве третьего указывается параметр ss, который выступает, как выходной, т.е. в него вернется результат действия процедуры sum_discount.

В процедуре sum_discount проверяется, какому условию соответствует входная сумма, и выполняется соответствующий запрос, результат записывается в выходной параметр ss, который возвращается в процедуру sum_sale.

Чтобы увидеть результат работы процедуры sum_sale нужно сделать запрос:

select @sum_discount//

Давайте убедимся, что наша процедура работает:



Сумма наших обеих покупок меньше 1000 рублей, поэтому скидки нет. Можете самостоятельно ввести покупки с разными суммами и посмотреть, как будет работать наша процедура.

Возможно, этот урок показался вам достаточно трудным или запутанным. Не расстраивайтесь. Во-первых, все приходит с опытом, а во-вторых, справедливости ради, надо сказать, что и переменные, и операторы ветвления в MySQL используются крайне редко. Предпочтение отдается языкам типа PHP, Perl и т.д., с помощью которых и организуется ветвление, а в саму БД посылаются простые процедуры.

Задание 8. Хранимые процедуры

Сегодня узнаем, как работать с циклами, т.е. выполнять один и тот же запрос несколько раз. В MySQL для работы с циклами применяются операторы WHILE, REPEAT и LOOP.

Оператор цикла WHILE

Сначала синтаксис:

WHILE условие DO

запрос

END WHILE

Запрос будет выполняться до тех пор, пока условие истинно. Давайте посмотрим на примере, как это работает. Предположим, мы хотим знать названия, авторов и количество книг, которые поступили в различные поставки. Интересующая нас информация хранится в двух таблицах - Журнал Поставок (magazine_incoming) и Товар (products). Давайте напишим интересующий нас запрос:

А что, если нам необходимо, чтобы результат выводился не в одной таблице, а по каждой поставке отдельно? Конечно, можно написать 3 разных запроса, добавив в каждый еще одно условие:



Но гораздо короче сделать это можно с помощью цикла WHILE:

Т.е. мы ввели переменную i, по умолчанию равную 3, сервер выполнит запрос с id поставки равным 3, затем уменьшит i на единицу (SET i=i-1), убедится, что новое значение переменной i положительно (i>0) и снова выполнит запрос, но уже с новым значением id поставки равным 2. Так будет происходить, пока переменная i не получит значение 0, условие станет ложным, и цикл закончит свою работу.

Чтобы убедиться в работоспособности цикла создадим хранимую процедуру books и поместим в нее цикл:



Теперь вызовем процедуру:

Теперь у нас 3 отдельные таблицы (по каждой поставке). Согласитесь, что код с циклом гораздо короче трех отдельных запросов. Но в нашей процедуре есть одно неудобство, мы объявили количество выводимых таблиц значением по умолчанию (DEFAULT 3), и нам придется с каждой новой поставкой менять это значение, а значит код процедуры. Гораздо удобнее сделать это число входным параметром. Давайте перепишем нашу процедуру, добавив входной параметр num, и, учитывая, что он не должен быть равен 0:

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

Итак, мы снабдили наш цикл меткой wet вначале (wet:) и в конце, а также добавили еще одно условие - если входной параметр больше 10 (число 10 взято произвольно), то цикл с меткой wet следует закончить (IF (i>10) THEN LEAVE wet). Таким образом, если мы случайно вызовем процедуру с большим значением num, наш цикл прервется после 10 итераций (итерация - один проход цикла).

Циклы в MySQL, так же как и операторы ветвления, на практике в web-приложениях почти не используются. Поэтому для двух других видов циклов приведем лишь синтаксис и отличия. Вряд ли вам доведется их использовать, но знать об их существовании все-таки надо.

Оператор цикла REPEAT

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

REPEAT

запрос

UNTIL условие

END REPEAT

Оператор цикла LOOP

Этот цикл вообще не имеет условий, поэтому обязательно должен иметь оператор LEAVE. Синтаксис следующий:

LOOP

запрос

END LOOP

На этом мы заканчиваем изучение SQL. Конечно, мы рассмотрели не все возможности этого языка запросов, но в реальной жизни вам вряд ли придется столкнуться даже с тем, что вы уже знаете.

Размещено на Allbest.ru

...