Хранимые процедуры (компонент database engine)

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

Создадим процедуру с именем GetAllProducts() для получения списка всех продуктов из таблицы.

Для этого нужно загрузить mysql-клиент и выполнить следующие команды:

Команда DELIMITER //
не входит в хранимые процедуры. DELIMITER – специальная команда, изменяющая стандартный разделитель запросов (по умолчанию «;») на указанный после нее. Этой командой изменим его на 2 слеша (//).

Если не изменить разделитель, то mysql ошибочно интерпретирует процедуру и выдаст ошибку. После END используется разделитель // и с помощью команды DELIMITER возвращается значение разделителя «;».

Зарезервированные слова CREATE PROCEDURE указывают mysql, что нужно СОЗДАТЬ ПРОЦЕДУРУ. После этих слов нужно указать название хранимой процедуры (в примере GetAllProducts). Пустые скобки «()» после названия процедуры означает, что процедура не принимает никаких переменных.

Команды BEGIN и END соответственно открывают и закрывают блок кода SQL.

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

В MySQL Workbench
процедура создается нажатием правой кнопкой мыши на Routines и выбором в выпадающем меню пункта Create Procedure…

Можно просмотреть полный код, который отправится в MySQL, перед тем, как он запишется в базу данных. Если ошибок нет, нажимаем Apply
.

После компиляции MySQL записывает процедуру в каталог. После завершения записи нажимаем кнопку Finish
.

Можно увидеть созданную хранимую процедуру в списке Routines.

Зачем изучать SQL в 2022 году?

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

1. Он имеет множество возможностей обработки, которые определены в наборах, таких как язык определения данных (создание, изменение и удаление схем), язык манипулирования данными (обновление, вставка, удаление, получение таблиц), язык управления данными (отзыв разрешений или их предоставление). .
2. Он позволяет выполнять проверку целостности баз данных и нормализовать базы данных, чтобы удалить избыточность данных и другие проблемы, связанные с базами данных на основе файловой системы.
3. Просто используя одну строку команды, вы можете получить тщательно отфильтрованные данные вместе с несколькими записями.
4. Когда вы освоите язык, вы сможете использовать сложные запросы, чтобы лучше разбираться в данных.
5. Он имеет простой синтаксис, хорошо масштабируется, обеспечивает высокую безопасность и имеет большое сообщество.
6. Он интерактивен, легко переносится, может создавать несколько представлений таблицы для работы и экспериментов и имеет четко определенный стандарт.

Во-вторых, создайте хранимые процедуры и функции.

Процесс создания хранимых процедур и функций очень похож.

2.1 Создание хранимой процедуры

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

Где in представляет входной параметр, out представляет выходной параметр, inout представляет как вход, так и выход; param_name представляет имя параметра; type представляет тип параметра

Тип может быть любым типом в базе данных MYSQL

Доступны следующие значения:

LANGUAGE SQL : Объясните, что часть подпрограммы_body состоит из операторов SQL, язык, поддерживаемый текущей системой, — это SQL, а SQL — единственное значение функции LANGUAGE.

DETERMINISTIC : Укажите, правильный ли результат выполнения хранимой процедуры. ДЕТЕРМИНИСТИЧЕСКИЙ означает, что результат определен. Каждый раз, когда выполняется хранимая процедура, один и тот же ввод будет

ДЕТЕРМИНИСТИЧЕСКИЙ означает, что результат неопределен, и один и тот же вход может иметь разные выходные данные. Если значение не указано, по умолчанию используется ДЕТЕРМИНИСТИЧЕСКИЙ

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA: Укажите ограничение на использование операторов SQL в подпрограммах.

CONTAINS SQL указывает, что подпрограмма содержит операторы SQL, но не содержит операторов, считывающих и записывающих данные;

NO SQL указывает, что подпрограмма не содержит операторов SQL;

READS SQL DATA: указывает, что подпрограмма содержит операторы, которые читают данные;

MODIFIES SQL DATA указывает, что подпрограмма содержит операторы для записи данных.

По умолчанию система укажет CONTAINS SQL

SQL SECURITY : Укажите, кто имеет разрешение на выполнение. DEFINER означает, что только определитель может выполнять

INVOKER означает, что вызывающий с разрешением может выполнять. По умолчанию система обозначена как DEFINER

COMMENT’string ‘: информация комментария, которая может использоваться для описания хранимых процедур или функций.

подпрограмма_body — это содержимое кода SQL, вы можете использовать BEGIN . END, чтобы указать начало и конец кода SQL.

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

Логика здесь 1. Сначала определите, существует ли хранимая процедура countOrder (), и отбросьте ее, если она есть. 2. Создайте хранимую процедуру countOrder (). 3. Выполните хранимую процедуру countOrder ().

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

Создайте функцию с помощью оператора CREATE FUNCTION:

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

Тип RETURNS представляет тип возвращаемого значения функции; тело_программы — это тело функции, а тело функции должно содержать оператор значения RETURN.

Курсоры (MySQL Cursors)

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

 CREATE TABLE `tags` ( `id` INT NOT NULL AUTO_INCREMENT , `tag` VARCHAR(255) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM

Сюда мы будем записывать все тэги из всех тем. Хранимая процедура будет выглядеть примерно так:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

CREATE PROCEDURE `procedure3`()BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE sTag VARCHAR(255);

  DECLARE iCount INT DEFAULT 0;

 

  DECLARE rCursor CURSOR FOR SELECT `tag` FROM `threads` WHERE 1;

  DECLARE CONTINUE HANDLER FOR SQLSTATE `02000` SET done=1;

 

  OPEN rCursor;

  FETCH rCursor INTO sTag;

 

  WHILE done = 0 DO

    SELECT COUNT(*) INTO iCount FROM `tags` WHERE `tag` = sTag;

    IF iCount = 0 THEN

      INSERT INTO `tags` (`tag`) VALUES (sTag);

    END IF;

 

    FETCH rCursor INTO sTag;

  END WHILE;

 

  CLOSE rCursor;

END

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

Курсор для запроса SELECT, который выберет теги из всех тем (WHERE 1). После курсора объявляем что-то вроде исключения — что делать, когда результаты кончатся (SQLSTATE «02000′ означает это окончание). В этом случае мы в переменную done запишем 1, чтобы в последствии выйти из цикла.

Открываем курсор, и получаем первую запись. Дальше в цикле — Выбираем количество совпадений из таблицы тегов для текущего тега и помещаем результат в переменную iCount. Если результатов нет, то запросом INSERT вставляем новый тег.

В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.

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

Хранимая подпрограмма представляет собой процедуру или функцию. Хранимые подпрограммы создаются с помощью выражений 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 PROCEDURE имя_процедуры (]) тело_подпрограммыCREATE FUNCTION имя_функции (])RETURNS тип тело_подпрограммыпараметр_процедуры:  имя_параметра типпараметр_функции: имя_параметра типтип: Любой тип данных 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 ''BEGINselect 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;-> //Query OK, 0 rows affected (0.00 sec)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)

Готово.

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

Вызовите Хранимую Процедуру С Помощью Hibernate

Начиная с Hibernate 3, у нас есть возможность использовать необработанные инструкции SQL, включая хранимые процедуры, для запроса базы данных.

В этом разделе мы рассмотрим, казалось бы, простой пример, который проиллюстрирует, как вызвать процедуру GetAllFoos() с помощью Hibernate.

3.1. Конфигурация

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

И, конечно, для всего этого – зависимостей Maven, конфигурации MySQL, конфигурации Hibernate и SessionFactory instantiation – вы можете проверить статью Hibernate .

3.2. Вызов Хранимой процедуры С Помощью метода Create Native SQL

Hibernate позволяет напрямую выражать запросы в формате native SQL . Поэтому мы можем прямо создать собственный SQL-запрос и использовать оператор CALL для вызова хранимой процедуры getAllFoos() :

Query query = session.createSQLQuery("CALL GetAllFoos()").addEntity(Foo.class);
List allFoos = query.list();

Приведенный выше запрос возвращает список, в котором каждый элемент является объектом Foo o .

Мы используем метод addEntity() для получения объектов сущностей из собственного SQL запроса, в противном случае ClassCastException будет возникать всякий раз, когда хранимая процедура возвращает необработанное значение.

3.3. Вызов Хранимой процедуры С Помощью @NamedNativeQueries

Другой способ вызова хранимой процедуры-использовать аннотацию @NamedNativeQueries .

@NamedNativeQueries используется для указания массива собственных SQL именованных запросов , относящихся к единице сохранения:

@NamedNativeQueries({ 
  @NamedNativeQuery(
    name = "callGetAllFoos", 
    query = "CALL GetAllFoos()", 
    resultClass = Foo.class) 
})
@Entity
public class Foo implements Serializable {
    // Model definition
}

Каждый именованный запрос, очевидно , имеет атрибут name , фактический SQL-запрос и класс result |, который ссылается на сопоставленную сущность Foo .

Query query = session.getNamedQuery("callGetAllFoos");
List allFoos = query.list();

Атрибут resultClass играет ту же роль, что и метод addEntity() в нашем предыдущем примере.

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

3.4. Вызов Хранимой процедуры С помощью @NamedStoredProcedureQuery

Если вы используете JPA 2.1 и Hibernate реализацию EntityManagerFactory | и EntityManager .

Аннотацию @NamedStoredProcedureQuery можно использовать для объявления хранимой процедуры:

@NamedStoredProcedureQuery(
  name="GetAllFoos",
  procedureName="GetAllFoos",
  resultClasses = { Foo.class }
)
@Entity
public class Foo implements Serializable {
    // Model Definition 
}

Чтобы вызвать наш namedstoredprocedurequery, нам нужно создать экземпляр EntityManager, и затем вызвать метод createNamedStoredProcedureQuery() для создания процедуры

StoredProcedureQuery spQuery = 
  entityManager.createNamedStoredProcedureQuery("getAllFoos");

Мы можем напрямую получить список сущностей Foo |, вызвав метод execute () в запросе хранимой процедуры | объекта.

Простой пример

Один из хороших случаев применения хранимых процедур — тогда, когда вам нужно объединить несколько запросов в один, например добавление темы в форум и увеличение общего количества тем. Допустим таблица threads

CREATE
TABLE
`threads`
(
`id`
INT NOT
NULL
AUTO_INCREMENT
,

`title`
VARCHAR(255
)
NOT
NULL
,

`tag`
VARCHAR(255
)
NOT
NULL
,

PRIMARY
KEY
( `id`
)

)
ENGINE =
MYISAM;

Здесь title у нас будет заголовком новой темы. Ну и таблица, например с различными статистическими переменными сайта, в том числе общее количество тем в форме.

CREATE
TABLE
`variables`
(
`id`
INT NOT
NULL
AUTO_INCREMENT
,

`name`
VARCHAR(255
)
NOT
NULL
,

`value`
INT NOT
NULL
DEFAULT
0,

PRIMARY
KEY
( `id`
)

)
ENGINE =
MYISAM;

Тут вроде всё понятно, допустим у нас там есть запись с name = threads и value = 0. Создадим новую хранимую процедуру procedure2.

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

CALL procedure2(«My new thread»
)
;

Таким образом, вместо того, чтобы передать два или больше запросов (например через php), мы можем передать один — оптимизация, чистый код и можно изменить в любой момент не затрагивая другие скрипты.

Шаг 2: Как работать с хранимыми процедурами

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

DELIMITER //  
  
CREATE PROCEDURE `p2` ()  
LANGUAGE SQL  
DETERMINISTIC  
SQL SECURITY DEFINER  
COMMENT 'A procedure'  
BEGIN  
    SELECT 'Hello World !';  
END//  

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

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

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

• Language: в целях обеспечения переносимости, по умолчанию указан SQL.
• Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию — NOT DETERMINISTIC.
• SQL Security: во время вызова идет проверка прав пользователя. INVOKER — это пользователь, вызывающий хранимую процедуру. DEFINER — это “создатель” процедуры. Значение по умолчанию — DEFINER.
• Comment: в целях документирования, значение по умолчанию — «»

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

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.

CALL stored_procedure_name (param1, param2, ....)  
  
CALL procedure1(10 , 'string parameter' , @parameter_var);

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

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

DROP PROCEDURE IF EXISTS p2; 

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Виды СУБД

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

• поддержка языков баз данных;
• непосредственное управление данными;
• управление буферами оперативной памяти;
• управление транзакциями;
• резервное копирование и восстановление после сбоев.

Существуют разные виды таких систем, которые разрабатывает и техногиганты, вроде Google, Microsoft и Amazon, и более нишевые студии. Разработчики стремятся сделать свой продукт лучше, чтобы их система быстрее и качественнее других обрабатывала данные. Из-за этого появились разные виды языка SQL — так называемые SQL-диалекты. У каждой СУБД диалект имеет что-то общее со всеми, а также свои особенности, которые не будут работать в другой системе.

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

PostgreSQL — это объектно-ориентированная система, то есть она обрабатывает данные как абстрактные объекты. Каждый объект, в отличие от простых табличных значений, может иметь собственные характеристики и уникальные методы взаимодействия с другими объектами. Это позволяет PostgreSQL обрабатывать более сложные структуры данных и выполнять более сложные процедуры. Например, Яндекс.Почта в свое время перешла на эту систему, чтобы поддерживать стабильное соединение десятков тысяч пользователей к одной базе.

MySQL — простая в изучении и функциональная система, которая работает с сайтами и веб-приложениями. Чаще всего используется в системах управления контентом сайтов (CMS), на сайтах с возможностью регистрации пользователей, в корпоративных системах CRM, в планировщиках, чатах и форумах. MySQL считается одним из самых безопасных и высокоскоростных решений, которое существует на рынке.

SQLite — это облегченная встраиваемая версия СУБД. В ней нет возможности поделиться правами доступа, как во многих других системах, но благодаря своему устройству эта система быстрая и мощная. SQLite подходит для обработки запросов на сайтах с низким и средним трафиком, а также в однопользовательских мобильных приложениях и играх. Преимущество такой системы — файловая структура, то есть база в SQLite состоит из одного файла, поэтому ее очень легко переносить.

Oracle — одна из первых СУБД, которая появилась еще в 1977 году и развивается до сих пор. Это кроссплатформенная система, которая может работать на Windows, Linux, MacOS, мобильных и других ОС. Система используется в крупных коммерческих проектах. Например, в России с Oracle сотрудничают операторы МТС и Теле2, банк «Открытие» и ВТБ.

Google Cloud Spanner — это облачная система управления данными, которую Google разработал для управления собственными сервисами, например AdWords и Google Play. В 2017 году систему сделали общедоступной. Cloud Spanner относят к категории NewSQL — это системы, которые совмещают в себе преимущества реляционных и нереляционных СУБД.

Важность SQL

Давайте рассмотрим несколько важных моментов, которые сделали SQL таким популярным языком запросов.

1. Это повсеместно принято

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

2. Легко научиться

Синтаксис SQL похож на простой естественный язык, и его довольно легко изучить по сравнению со сложным синтаксисом других языков программирования, таких как Java, C ++ и т. Д.

3. Он может обрабатывать большие наборы данных.

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

4. Это дает лучшее представление о наборах данных.

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

5. Это стандарт

SQL — это язык запросов, стандартизированный как ISO, так и ANSI. Он довольно стабилен, без больших обновлений синтаксиса, и как только вы его изучите, вам не нужно особо сосредотачиваться на новых выпусках.

6. Это горячий навык

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

Карьера в SQL

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

Требуется образование

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

Должности

• SQL Server является третьим по популярности сервером баз данных, и миллионы профессионалов в настоящее время работают над различными ролями, которые прямо или косвенно требуют SQL как навыка.
• Основные направления карьеры в SQL: администратор баз данных, специалисты по бизнес-аналитике, специалисты по данным, разработчики баз данных, тестировщики баз данных, разработчики ETL, разработчики приложений бизнес-аналитики, эксперты по большим данным, инженеры облачных баз данных, инженеры по миграции баз данных и т. д.

Оплата труда

• Средняя зарплата специалиста по SQL во многом зависит от выбранной им карьеры. Обычно профессионалы с опытом работы с SQL более 5 лет имеют более высокую среднюю зарплату по сравнению с новичками.
• Согласно отчету payscale за 2018 год, средняя заработная плата разработчиков SQL в США составляет около 65 тыс. Долларов США, для старших разработчиков SQL — 85 тыс. Долларов США, для старших администраторов баз данных — около 100 тыс. Долларов, для производственных администраторов баз данных средняя зарплата может достигать 130 тысяч долларов.

Связанные задачи

Сравнение со статическим SQL

Накладные расходы

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

Избегание сетевого трафика

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

Инкапсуляция бизнес-логики

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

Делегирование прав доступа

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

Некоторая защита от атак SQL-инъекций
Хранимые процедуры могут использоваться для защиты от атак путем инъекций. Параметры хранимой процедуры будут рассматриваться как данные, даже если злоумышленник вставляет команды SQL. Также некоторые СУБД будут проверять тип параметра

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

Заключение

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

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

Представляем вашему вниманию новый курс от команды The Codeby
— «Тестирование Веб-Приложений на проникновение с нуля». Общая теория, подготовка рабочего окружения, пассивный фаззинг и фингерпринт, Активный фаззинг, Уязвимости, Пост-эксплуатация, Инструментальные средства, Social Engeneering и многое другое.