Что можно приготовить из кальмаров: быстро и вкусно

Внимание! Данная работа построена на основе перевода раздела «17.1. Stored Routines and the Grant Tables» описания ПО MySQL 5.0.19, «Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision:995)»
``Сначала прочти все, а потом пробуй примеры"

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

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

Хранимые программы (процедуры и функции) поддерживаются в MySQL 5.0. Хранимые процедуры - набор SQL -выражений, который может быть сохранен на сервере. Как только это сделано, клиенту уже не нужно повторно передавать запрос, а требуется просто вызвать хранимую программу.

Это может быть полезным тогда, когда:

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

Хранимые процедуры и функции (подпрограммы) могут обеспечить лучшую производительность потому, что меньше информации требуется для пересылки между клиентом и сервером. Выбор увеличивает нагрузку на сервер БД, но снижает затраты на стороне клиента. Используйте это, если много клиентских машин (таких как Веб-серверы) обслуживаются одной или несколькими БД.

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

MySQL следует в синтаксисе за SQL:2003 для хранимых процедур, который уже используется в IBM"s DB2.

От слов к делу…

При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицей mysql.proc

Начиная с MySQL 5.0.3 требуются следующие привилегии:

CREATE ROUTINE для создания хранимых процедур

ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю процедуры (функции)

EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.

Синтаксис хранимых процедур и функций

Хранимая подпрограмма представляет собой процедуру или функцию. Хранимые подпрограммы создаются с помощью выражений CREATE PROCEDURE или CREATE FUNCTION . Хранимая подпрограмма вызывается, используя выражение CALL , причем только возвращающие значение переменные используются в качестве выходных. Функция может быть вызвана подобно любой другой функции и может возвращать скалярную величину. Хранимые подпрограммы могут вызывать другие хранимые подпрограммы.

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

• Когда подпрограмма вызывается, то подразумевается, что надо произвести вызов USE db_name (и отменить использование базы, когда подпрограмма завершилась, и база больше не потребуется)
• Вы можете квалифицировать обычные имена с именем базы данных. Это может быть использовано, чтобы ссылаться на подпрограмму, которая - не в текущей базе данных. Например, для выполнения хранимой процедуры p или функции f которые связаны с БД test , вы можете сказать интерпретатору команд так: CALL test.p() или test.f() .
• Когда база данных удалена, все загруженные подпрограммы связанные с ней тоже удаляются. В MySQL 5.0.0, загруженные подпрограммы - глобальные и не связанны с базой данных. Они наследуют по умолчанию базу данных из вызывающего оператора. Если USE db_name выполнено в пределах подпрограммы, оригинальная текущая БД будет восстановлена после выхода из подпрограммы (Например текущая БД db_11 , делаем вызов подпрограммы, использующей db_22 , после выхода из подпрограммы остается текущей db_11)

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

CREATE PROCEDURE - создать хранимую процедуру.

CREATE FUNCTION - создать хранимую функцию.

Синтаксис:

CREATE FUNCTION имя_функции ([параметр_функции[,...]])
RETURNS тип
[характеристика...] тело_подпрограммы

параметр_процедуры:
[ IN | OUT | INOUT ] имя_параметра тип
параметр_функции:
имя_параметра тип

тип:
Любой тип данных MySQL

характеристика:
LANGUAGE SQL
| DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT "string"

тело_подпрограммы:
Правильное SQL выражение.

Рассмотрим все на практике.

Сначала создадим хранимую процедуру следующим запросом:

CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""
BEGIN
select val1+val2 into "t" from `my` LIMIT 0,1;
END;

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

После этого вызовем ее:

CALL my_proc(@a);
SELECT @a;

Для отделения внутреннего запроса от внешнего всегда используют разделитель отличный от обычно (для задания используют команду DELIMITER <строка/символ>)

Вот еще один пример с учетом всех требований.

Mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //

mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Весь процесс можно пронаблюдать на рисунке ниже:

Триггеры

Поддержка триггеров появилась в MySQL начиная с версии 5.0.2.

Триггер - поименованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, события связанного с этой таблицей.

Например, нижеприведенный код создает таблицу и INSERT триггер. Триггер суммирует значения, вставляемые в один из столбцов таблицы.

Mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

Объявим переменную sum и присвоим ей значение 1. После этого при каждой вставке в таблицу account значение этой переменной будет увеличивать согласно вставляемой части.

Замечание . Если значение переменной не инициализировано, то триггер работать не будет!

Синтаксис создания триггера

Если с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.

время_триггера

Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.

событие_срабатывания_триггера

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

• INSERT: т.е. при операциях вставки или аналогичных ей выражениях (INSERT, LOAD DATA, и REPLACE)
• UPDATE: когда сущность (строка) модифицирована
• DELETE: когда запись удаляется (запросы, содержащие выражения DELETE и/или REPLACE)

SQL - Урок 10. Встроенные функции

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


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


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


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


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

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

Create database shop;

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

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

create table customers (id_customer int NOT NULL AUTO_INCREMENT, name char(50) NOT NULL, email char(50) NOT NULL, PRIMARY KEY (id_customer)); create table vendors (id_vendor int NOT NULL AUTO_INCREMENT, name char(50) NOT NULL, city char(30) NOT NULL, address char(100) NOT NULL, PRIMARY KEY (id_vendor)); create table sale (id_sale int NOT NULL AUTO_INCREMENT, id_customer int NOT NULL, date_sale date NOT NULL, PRIMARY KEY (id_sale), FOREIGN KEY (id_customer) REFERENCES customers (id_customer)); create table incoming (id_incoming int NOT NULL AUTO_INCREMENT, id_vendor int NOT NULL, date_incoming date NOT NULL, PRIMARY KEY (id_incoming), FOREIGN KEY (id_vendor) REFERENCES vendors (id_vendor)); create table products (id_product int NOT NULL AUTO_INCREMENT, name char(100) NOT NULL, author char(50) NOT NULL, PRIMARY KEY (id_product)); create table prices (id_product int NOT NULL, date_price_changes date NOT NULL, price double NOT NULL, PRIMARY KEY (id_product, date_price_changes), FOREIGN KEY (id_product) REFERENCES products (id_product)); create table magazine_sales (id_sale int NOT NULL, id_product int NOT NULL, quantity int NOT NULL, PRIMARY KEY (id_sale, id_product), FOREIGN KEY (id_sale) REFERENCES sale (id_sale), FOREIGN KEY (id_product) REFERENCES products (id_product)); create table magazine_incoming (id_incoming int NOT NULL, id_product int NOT NULL, quantity int NOT NULL, PRIMARY KEY (id_incoming, id_product), FOREIGN KEY (id_incoming) REFERENCES incoming (id_incoming), FOREIGN KEY (id_product) REFERENCES products (id_product));

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

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

INSERT INTO vendors (name, city, address) VALUES ("Вильямс", "Москва", "ул.Лесная, д.43"), ("Дом печати", "Минск", "пр.Ф.Скорины, д.18"), ("БХВ-Петербург", "Санкт-Петербург", "ул.Есенина, д.5"); INSERT INTO customers (name, email) VALUES ("Иванов Сергей", "[email protected]"), ("Ленская Катя", "[email protected]"), ("Демидов Олег", "[email protected]"), ("Афанасьев Виктор", "[email protected]"), ("Пажская Вера", "[email protected]"); INSERT INTO products (name, author) VALUES ("Стихи о любви", "Андрей Вознесенский"), ("Собрание сочинений, том 2", "Андрей Вознесенский"), ("Собрание сочинений, том 3", "Андрей Вознесенский"), ("Русская поэзия", "Николай Заболоцкий"), ("Машенька", "Владимир Набоков"), ("Доктор Живаго", "Борис Пастернак"), ("Наши", "Сергей Довлатов"), ("Приглашение на казнь", "Владимир Набоков"), ("Лолита", "Владимир Набоков"), ("Темные аллеи", "Иван Бунин"), ("Дар", "Владимир Набоков"), ("Сын вождя", "Юлия Вознесенская"), ("Эмигранты", "Алексей Толстой"), ("Горе от ума", "Александр Грибоедов"), ("Анна Каренина", "Лев Толстой"), ("Повести и рассказы", "Николай Лесков"), ("Антоновские яблоки", "Иван Бунин"), ("Мертвые души", "Николай Гоголь"), ("Три сестры", "Антон Чехов"), ("Беглянка", "Владимир Даль"), ("Идиот", "Федор Достоевский"), ("Братья Карамазовы", "Федор Достоевский"), ("Ревизор", "Николай Гоголь"), ("Гранатовый браслет", "Александр Куприн"); INSERT INTO incoming (id_vendor, date_incoming) VALUES ("1", "2011-04-10"), ("2", "2011-04-11"), ("3", "2011-04-12"); INSERT INTO magazine_incoming (id_incoming, id_product, quantity) VALUES ("1", "1", "10"), ("1", "2", "5"), ("1", "3", "7"), ("1", "4", "10"), ("1", "5", "10"), ("1", "6", "8"), ("1", "18", "8"), ("1", "19", "8"), ("1", "20", "8"), ("2", "7", "10"), ("2", "8", "10"), ("2", "9", "6"), ("2", "10", "10"), ("2", "11", "10"), ("2", "21", "10"), ("2", "22", "10"), ("2", "23", "10"), ("2", "24", "10"), ("3", "12", "10"), ("3", "13", "10"), ("3", "14", "10"), ("3", "15", "10"), ("3", "16", "10"), ("3", "17", "10"); INSERT INTO prices (id_product, date_price_changes, price) VALUES ("1", "2011-04-10", "100"), ("2", "2011-04-10", "130"), ("3", "2011-04-10", "90"), ("4", "2011-04-10", "100"), ("5", "2011-04-10", "110"), ("6", "2011-04-10", "85"), ("7", "2011-04-11", "95"), ("8", "2011-04-11", "100"), ("9", "2011-04-11", "79"), ("10", "2011-04-11", "49"), ("11", "2011-04-11", "105"), ("12", "2011-04-12", "85"), ("13", "2011-04-12", "135"), ("14", "2011-04-12", "100"), ("15", "2011-04-12", "90"), ("16", "2011-04-12", "75"), ("17", "2011-04-12", "90"), ("18", "2011-04-10", "150"), ("19", "2011-04-10", "140"), ("20", "2011-04-10", "85"), ("21", "2011-04-11", "105"), ("22", "2011-04-11", "70"), ("23", "2011-04-11", "65"), ("24", "2011-04-11", "130"); INSERT INTO sale (id_customer, date_sale) VALUES ("2", "2011-04-11"), ("3", "2011-04-11"), ("5", "2011-04-11"); INSERT INTO magazine_sales (id_sale, id_product, quantity) VALUES ("1", "1", "1"), ("1", "5", "1"), ("1", "7", "1"), ("2", "2", "1"), ("3", "1", "1"), ("3", "7", "1");

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

В этой части статьи допишем начатую в предыдущей статье хранимую процедуру и научимся создавать хранимые 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.

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

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

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

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

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

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

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

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

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

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

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

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

create database shop;

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

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

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

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