Mysql встроенные функции. Хранимые процедуры и триггеры

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

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

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

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

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

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

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

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

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

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

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

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

create database shop;

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

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

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

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

В этой части статьи допишем начатую в предыдущей статье хранимую процедуру и научимся создавать хранимые mysql функции .

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

Создание функции

Для начала закрываем текущую форму создания процедуры, нажав на кнопку c надписью Go. Затем в этом же окне снова нажимаем на надпись Add routine, появится знакомая форма, заполним её.

Имя - GetPostID Тип - функция Parameters - ComID BIGINT(20) UNSIGNED Return type (возвращаемый тип) - BIGINT Return length/values - 20 Return options - UNSIGNED Definition: BEGIN RETURN (SELECT comment_post_ID FROM wp_comments WHERE comment_ID = ComID); END;

Так же можно указать дополнительные параметры:

Is deterministic — детерминированная функция всегда возвращает один и тот же результат при одинаковых входных параметрах иначе она является не детерминированной. В нашем случае ставим галочку.

Definer и Security type параметры безопасности, в данном примере оставим их без изменений.

SQL data access имеет несколько значений:

NO SQL - не содержит sql.

Contains SQL - содержит встроенные sql функции или операторы, которые не читают, не пишут и не изменяют данные в базе данных. Например, установка значения переменной: SET name = значение;

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

MODIFIES SQL DATA - изменение или внесение данных, в базу данных, указывается для запросов: INSERT, UPDATE, но при этом не должен присутствовать запрос SELECT.

В нашей функции используется запрос SELECT, укажем READS SQL DATA.

Comment комментарий.

После того как все поля заполнены, нажимаем на кнопку с надписью Go.

Возвращаемся на вкладку Routines и отредактируем нашу процедуру, нажав на кнопку edit.

Присвоим переменой PostID в качестве значения результат, который вернёт функция GetPostID.

SET postID = GetPostID(ComID);

В результате окончательное тело процедуры будет таким

BEGIN DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; DECLARE Email varchar(100); DECLARE Date DATETIME DEFAULT NOW(); DECLARE ParentCom varchar(20); DECLARE Approved varchar(20); DECLARE PostID BIGINT(20); IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF; SET ParentCom = ComID ; SET Email = "[email protected]"; SET PostID = GetPostID(ComID); INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (Author, Email, Content, Date, Date, PostID, ParentCom, Approved, UserID); END;

Остальные поля формы оставим без изменений, нажимаем на кнопку Go. Процедура создана.

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

Зная это можно установить значения для этих переменных следующим образом:

BEGIN -- Объявляем переменные DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; DECLARE Email varchar(100); -- выполнение запроса и установка значений для переменных SELECT user_login, user_email, ID INTO Author, Email, UserID FROM wp_users WHERE user_login LIKE "adm%"; END;

Вызов хранимой процедуры

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

Затем узнаем id добавленного комментария

Возвращаемся на вкладку Routines и нажимаем на надпись Execute

Появится форма

Указываем значения передаваемых параметров: текст ответа и id комментария, после чего нажимаем на кнопку с надписью Go.

What are functions?

MySQL can do much more than just store and retrieve data . We can also perform manipulations on the data before retrieving or saving it. That"s where MySQL Functions come in. Functions are simply pieces of code that perform some operations and then return a result. Some functions accept parameters while other functions do not accept parameters.

Let" briefly look at an example of MySQL function. By default, MySQL saves date data types in the format "YYYY-MM-DD". Suppose we have built an application and our users want the date to be returned in the format "DD-MM-YYYY", we can use MySQL built in function DATE_FORMAT to achieve this. DATE_FORMAT is one of the most used functions in MySQL. We will look at it in more details as we unfold the lesson.

Why use functions?

Based on the example given in the introduction, people with experience in computer programming may be thinking "Why bother MySQL Functions? The same effect can be achieved with scripting/programming language?" It"s true we can achieve that by writing some procedures/function in the application program.

Getting back to our DATE example in the introduction, for our users to get the data in the desired format, business layer will have to do necessary processing.

This becomes a problem when the application has to integrate with other systems. When we use MySQL functions such as the DATE_FORMAT, then we can have that functionality embedded into the database and any application that needs the data gets it in the required format. This reduces re-work in the business logic and reduce data inconsistencies.

Another reason why we should consider using MySQL functions is the fact that it can help reducing network traffic in client/server applications . Business Layer will only need to make call to the stored functions without the need manipulate data .On average, the use of functions can help greatly improve overall system performance.

Types of functions

Built-in functions

MySQL comes bundled with a number of built in functions. Built in functions are simply functions come already implemented in the MySQL server. These functions allow us to perform different types of manipulations on the data. The built in functions can be basically categorized into the following most used categories.

• Strings functions - operate on string data types
• Numeric functions - operate on numeric data types
• Date functions - operate on date data types
• Aggregate functions - operate on all of the above data types and produce summarized result sets.
• Other functions - MySQL also supports other types of built in functions but we will limit our lesson to the above named functions only.

Let"s now look at each of the functions mentioned above in detail. We will be explaining the most used functions using our "Myflixdb".

String functions

We already looked at what string functions do. We will look at a practical example that uses them. In our movies table, the movie titles are stored using combinations of lower and upper case letters. Suppose we want to get a query list that returns the movie titles in upper case letters. We can use the "UCASE" function to do that. It takes a string as a parameter and converts all the letters to upper case. The script shown below demonstrates the use of the "UCASE" function.

SELECT `movie_id`,`title`, UCASE(`title`) FROM `movies`;

• UCASE(`title`) is the built in function that takes the title as a parameter and returns it in upper case letters with the alias name `upper_case_title`.

Executing the above script in MySQL workbench against the Myflixdb gives us the following results shown below.

MySQL supports a number of string functions. For a complete list of all the built in string functions, refere to this link http://dev.mysql.com/doc/refman/5.0/en/string-functions.html on MySQL website.

Numeric functions

As earlier mentioned, these functions operate on numeric data types. We can perform mathematic computations on numeric data in the SQL statements.

Arithematic operators

MySQL supports the following arithmatic operators that can be used to perform computations in the SQL statements.

Let"s now look at examples of each of the above operator

Integer Division (DIV)

SELECT 23 DIV 6 ;

Division operator (/)

Let"s now look at the division operator example. We will modify the DIV example.

Executing the above script gives us the following results.

Subtraction operator (-)

Let"s now look at the subtraction operator example. We will use the same values as in the previous two examples

Executing the above script gives us 17

Addition operator (+)

Let"s now look at the addition operator example. We will modify the previous example.

Executing the above script gives us 29

Multiplication operator (*)

Let"s now look at the multiplication operator example. We will use the same values as in the previous examples.

SELECT 23 * 6 AS `multiplication_result`;

Executing the above script gives us the following results.

Modulo operator (-)

The modulo operator divides N by M and gives us the reminder. Let"s now look at the modulo operator example. We will use the same values as in the previous examples.

SELECT 23 MOD 6 ;

Executing the above script gives us 5

Let"s now look at some of the common numeric functions in MySQL.

Floor - this function removes decimals places from a number and rounds it to the nearest lowest number. The script shown below demonstrates its usage.

SELECT FLOOR(23 / 6) AS `floor_result`;

Executing the above script gives us the following results.

Round - this function rounds a number with decimal places to the nearest whole number. The script shown below demonstrates its usage.

SELECT ROUND(23 / 6) AS `round_result`;

Executing the above script gives us the following results.

Rand - this function is used to generate a random number, its value changes every time that the function is called. The script shown below demonstrates its usage.

SELECT RAND() AS `random_result`;

Stored functions

Stored functions are just like built in functions except that you have to define the stored function yourself. Once a stored function has been created, it can be used in SQL statements just like any other function. The basic syntax for creating a stored function is as shown below

CREATE FUNCTION sf_name () RETURNS data type DETERMINISTIC STATEMENTS

• "CREATE FUNCTION sf_name () " is mandatory and tells MySQL server to create a function named `sf_name" with optional parameters defined in the parenthesis.
• "RETURNS data type" is mandatory and specifies the data type that the function should return.
• "DETERMINISTIC" means the function will return the same values if the same arguments are supplied to it.
• "STATEMENTS" is the procedural code that the function executes.

Let"s now look at a practical example that implements a built in function. Suppose we want to know which rented movies are past the return date. We can create a stored function that accepts the return date as the parameter and then compares it with the current date in MySQL server. If the current date is less than the return movie date, then we return "No" else we return "Yes". The script shown below helps us to achieve that.

DELIMITER | CREATE FUNCTION sf_past_movie_return_date (return_date DATE) RETURNS VARCHAR(3) DETERMINISTIC BEGIN DECLARE sf_value VARCHAR(3); IF curdate() > return_date THEN SET sf_value = "Yes"; ELSEIF curdate() <= return_date THEN SET sf_value = "No"; END IF; RETURN sf_value; END|

Executing the above script created the stored function `sf_past_movie_return_date`.

Let"s now test our stored function.

SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) FROM `movierentals`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results.

User-defined functions

MySQL also supports user defined functions that extend MySQL. User defined functions are functions that you can create using a programming language such as C, C++ etc. and then add them to MySQL server. Once added, they can be used just like any other function.

Summary

• Functions allow us to enhance the capabilities of MySQL.
• Functions always return a value and can optionally accept parameters.
• Built in functions are functions that are shipped with MySQL. They can be categorized according to the data types that they operate on i.e. strings, date and numeric built in functions.
• Stored functions are created by the user within MySQL server and can be used in SQL statements.
• User defined functions are created outside MySQL and can be incorporated into MySQL server.

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

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

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

Функции в MySQL

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

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

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

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

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

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

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

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

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

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

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

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

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

create database 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=

Вот теперь можно использовать итоговую функцию 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. Предположим, мы хотим, чтобы при выводе городов поставщиков они располагались бы справа, а пустое пространство заполнялось бы точками:

RPAD(str, len, dop_str)

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

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

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

SELECT LTRIM(city) FROM vendors;

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

SELECT RTRIM(city) FROM vendors;

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

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

SELECT TRIM(city) FROM vendors;

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

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

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

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

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

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

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

REPLACE(str, pod_str1, pod_str2)

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

Возвращает строку 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).

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 - минуты

WEEK - недели

MONTH - месяцы

QUARTER - кварталы

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 - минуты

WEEK - недели

MONTH - месяцы

QUARTER - кварталы

SUBTIME(date, time)

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

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

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

функция принимает дату 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), вторая - название месяца:

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

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

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

возвращает значение часа для времени (от 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 (число-месяц-год). Поэтому для привычного нам отображения даты ее необходимо переформатировать. Давайте сначала приведем запрос, а затем разберемся, как задавать формат:

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

STR_TO_DATE(date, format)

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

.

TIME_FORMAT(time, format)

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

GET_FORMAT(date, format)

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

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

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

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

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

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

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

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

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

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

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

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

CREATE PROCEDURE

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

операторы

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

INSERT INTO customers (name, email) VALUE ("Иванов Сергей", "[email protected]");

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

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

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

Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с; на "//", чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора 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 имя_процедуры (параметры)

IF(условие) THEN

ELSEIF(условие) THEN

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

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

CREATE PROCEDURE sum_sale(IN i INT)

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

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;

Здесь перед параметром у нас появилось новое ключевое слово 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.

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

Переменные действуют только в рамках одного сеанса соединения с сервером 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

Запрос будет выполняться до тех пор, пока условие истинно. Давайте посмотрим на примере, как это работает. Предположим, мы хотим знать названия, авторов и количество книг, которые поступили в различные поставки. Интересующая нас информация хранится в двух таблицах - Журнал Поставок (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, а в конце, т.е. хотя бы один раз, но цикл выполняется. Сам же цикл выполняется, пока условие ложно. Синтаксис следующий:

UNTIL условие

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

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

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

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

Подобные документы

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

лабораторная работа , добавлен 16.11.2008


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

курсовая работа , добавлен 25.04.2013


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

презентация , добавлен 12.12.2012


Рассмотрение особенностей объявления функций на языке СИ. Определение понятий аргументов функции и их переменных (локальных, регистровых, внешних, статических). Решение задачи программным методом: составление блок-схемы, описание функций main и sqr.

презентация , добавлен 26.07.2013


Правили создания и алгоритм применения собственной функции пользователя в стандартном модуле редактора VBA. Изучение структуры кода функции. Перечень встроенных математических функций редактора Visual Basic. Определение области видимости переменной.

практическая работа , добавлен 07.10.2010


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

дипломная работа , добавлен 16.02.2016


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

реферат , добавлен 20.05.2010


Функции, позволяющие работать с базой данных MySQL средствами РНР. Соединение с сервером и его разрыв. Создание и выбор базы данных. Доступ к отдельному полю записи. Комплексное использование информационных функций. Запросы, отправляемые серверу MySQL.

лекция , добавлен 27.04.2009


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

курсовая работа , добавлен 14.04.2019


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