Benefits of using a stored procedure
It can be easily modified: We can easily modify the code inside the stored procedure without the need to restart or deploying the application. For example, If the T-SQL queries are written in the application and if we need to change the logic, we must change the code in the application and re-deploy it. SQL Server Stored procedures eliminate such challenges by storing the code in the database. so, when we want to change the logic inside the procedure we can just do it by simple ALTER PROCEDURE statement.
Reduced network traffic: When we use stored procedures instead of writing T-SQL queries at the application level, only the procedure name is passed over the network instead of the whole T-SQL code.
Reusable: Stored procedures can be executed by multiple users or multiple client applications without the need of writing the code again.
Security: Stored procedures reduce the threat by eliminating direct access to the tables. we can also encrypt the stored procedures while creating them so that source code inside the stored procedure is not visible. Use third-party tools like ApexSQL Decrypt to decrypt the encrypted stored procedures.
Performance: The SQL Server stored procedure when executed for the first time creates a plan and stores it in the buffer pool so that the plan can be reused when it executes next time.
I am creating sample tables that will be used in the examples in this article.
|
1 |
CREATETABLEProduct (ProductIDINT,ProductNameVARCHAR(100)) GO CREATETABLEProductDescription (ProductIDINT,ProductDescriptionVARCHAR(800)) GO INSERTINTOProductVALUES(680,’HL Road Frame — Black, 58′) ,(706,’HL Road Frame — Red, 58′) ,(707,’Sport-100 Helmet, Red’) GO INSERTINTOProductDescriptionVALUES(680,’Replacement mountain wheel for entry-level rider.’) ,(706,’Sturdy alloy features a quick-release hub.’) ,(707,’Aerodynamic rims for smooth riding.’) GO |
Использование sp_executesql с параметрами
Вы можете использовать sp_executesql для задания параметров в вашей инструкции. Это в конечном итоге делает ваш код более легким для чтения и предоставляет некоторые преимущества оптимизации, поскольку оператор может быть скомпилирован один раз и повторно использован многократно.
Инструкция принимает форму:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Итак, давайте поясним детали.
- @statement — это SQL, который мы хотим выполнить.
- @parameterDefinition — это строка, содержащая определение всех параметров, указанных в @statement. Перечислен каждый параметр и тип, найденный @statement. Имя и тип разделяются пробелом. Несколько параметров разделяются запятой.
Затем мы устанавливаем значения параметров, задавая параметры и желаемое значение. Параметры перечислены в порядке, определенном в строке @parameterDefinition.
- @ parm1 — это первый параметр, определенный в строке @parameterDefinition. Value — это значение, которое вы хотите установить.
- @ parm2 — это второй параметр, если он определен, как указано в параметре @parameterDefinition.
- и так далее…
Вот простой пример, который добавляет два числа, чтобы попробовать:
DECLARE @statement NVARCHAR(4000)
DECLARE @parameterDefinition NVARCHAR(4000)
SET @statement = N’SELECT @a + @b’
SET @parameterDefinition = N’@a int, @b int’
EXECUTE sp_executesql @statement, @parameterDefinition, @a=10, @b=5
Выделены различные части инструкции:
@statement (жирный шрифт) — обратите внимание, что он включает в себя 2 параметра: @a и @b. Также обратите внимание, что они не заявлены в TSQL
Скорее, они установлены в определении параметра.
@parameterDefinition (курсив) — каждый указанный параметр определяется как тип int.
Значения параметров (жирный шрифт+курсив) — здесь мы устанавливаем значение параметра.
Для этого в этом примере у нас есть динамически исполняемый оператор SQL, который добавляет два параметра.
Эти параметры определяются как целые числа. Значение каждого параметра устанавливается в команде sp_executesql.
Реализация
Точная и правильная реализация хранимых процедур зависит от системы базы данных и варьируется от одного к другому. Основные поставщики баз данных поддерживают их в той или иной форме. Хранимые процедуры могут быть реализованы на разных языках программирования (зависит от системы базы данных), например, SQL, Java, C или C ++. Смотрите следующую систему баз данных и язык реализации:
| Система баз данных | Язык реализации |
|---|---|
| CUBRID | Джава |
| MySQL | Собственные хранимые процедуры, строго придерживаясь стандарта SQL / PSM. |
| PostgreSQL | PL / pgSQL, также может использовать собственные языки функций, такие как pl / perl или pl / php |
| оракул | PL / SQL или Java |
| жар-птица | PSQL (Fyracle также поддерживает части Oracle PL / SQL) |
| Informix | SPL или Java |
| DB2 | SQL PL (близкий к стандарту SQL / PSM) или Java |
| Sybase ASE | Transact-SQL |
| Microsoft SQL Server | Transact-SQL и различные языки .NET Framework |
Пример: процедура SQL
Вот простой пример, который принимает в качестве входных данных регистрационный номер студента, итоговые оценки и количество предметов и обновляет процент оценок:
Код SQL:
Объяснение:
- Имена процедуры STUDENT_MARKS
- Определяет параметр STUDENT_REG_NO (символьный тип данных длиной 15), TOTAL_MARKS (десятичный тип данных) и NO_SUBJECTS (целочисленный тип), которые являются входными параметрами.
- Указывает, что процедура является процедурой SQL, которая изменяет данные SQL.
- Определяет тело процедуры как один оператор UPDATE. Когда процедура вызывается, оператор UPDATE выполняется с использованием значений, переданных для STUDENT_REG_NO, TOTAL_MARKS и NO_SUBJECTS
- Определяет тело процедуры
Комплексные возвращаемые значения
У многих людей знания по хранимым процедурам заключаются в том, что мы успели обсудить выше. И все. Если бы это было все, на что способны хранимые процедуры, то они бы не были способны заменить другие механизмы удаленного выполнения каких-либо действий. На самом деле хранимые процедуры куда более мощные, чем это может показаться на первый взгляд.
Когда вы выполняете любой SQL-запрос, СУБД создает объект базы данных, который принято называть курсором (cursor). Он используется для итеративного изъятия записей (рядов таблицы), возвращаемых этим запросом. Объект класса ResultSet – это представление такого курсора на момент времени. Благодаря этому вы можете, без участия базы данных, проходить по содержимому ResultSet.
Некоторые СУБД позволяют возвращать ссылки на курсоры, возвращаемые при вызове хранимых процедур. Спецификация JDBC этого не поддерживает, но JDBC-драйвера от Oracle, PostgreSQL и DB2 имеют встроенную возможность преобразования указателя на курсор в объект класса ResultSet.
Для следующего примера, возможность выбора списка поэтов, которые так и не достигли пенсионного возраста (60 лет). Процедура для выполнения следующей выборки приведена ниже. Она будет возвращать курсор. Язык используем все тот же (PostgreSQL pl/pgsql):
createprocedurelist_early_deaths()returnrefcursorasdeclaretoesuprefcursor;beginopentoesupforSELECTpoets.name,deaths.ageFROMpoets,deathsWHEREpoets.id=deaths.mort_idANDdeaths.age<60;returntoesup;end;' language plpgsql';
Далее следует Java-метод, который вызывает эту процедуру, получает результат и выводит полученные записи в объект PrintWriter:
Поскольку возвращение курсоров напрямую не предусмотрено в спецификации JDBC, мы используем Types.OTHER для того, чтобы объявить имя возвращаемого процедурой типа и уже после получить результат из вызова с помощью метода getObject().
Java-метод, который вызывает процедуру, является очень хорошим примером маппинга (mapping). Маппинг – это способ абстрагирования операций на набор записей (к примеру). Вместо того чтобы возвращать сам набор из этой процедуры, мы можем просто передать операцию, которая с этим набором будет что-то делать. В этом случае, наша операция состоит в печати содержимого ResultSet на поток вывода. Вот пример переработанного метода:
Это позволяет выполнять произвольные операции над данными объекта ResultSet без необходимости изменять или дублировать код метода, который получает этот объект ResultSet
И это очень важно. Если мы захотим, то можем переписать метод sendEarlyDeaths:
Этот метод вызывает mapEarlyDeaths, которому передает анонимный экземпляр класса ProcessPoetDeaths. Этот экземпляр класса реализует метод sendDeath, который выводит информацию о поэтах в поток вывода, как и в предыдущем примере. Конечно, этот прием не является частью спецификации хранимых процедур, но иногда очень нужно совместить возможность возвращать объекты ResultSet и хранимые процедуры. В итоге мы имеем удивительно мощный инструмент.
5 последних уроков рубрики «Разное»
-
Выбрать хороший хостинг для своего сайта достаточно сложная задача. Особенно сейчас, когда на рынке услуг хостинга действует несколько сотен игроков с очень привлекательными предложениями. Хорошим вариантом является лидер рейтинга Хостинг Ниндзя — Макхост.
-
Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов
Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.
-
Создание вебсайта — процесс трудоёмкий, требующий слаженного взаимодействия между заказчиком и исполнителем, а также между всеми членами коллектива, вовлечёнными в проект. И в этом очень хорошее подспорье окажет онлайн платформа Wrike.
-
Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.
Против
- Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая — на клиентской.
- Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
- Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
- Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL — это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
Общие рекомендации по временным таблицам (temporary tables):
-
Не используйте конструкцию
Transact-SQL
SELECT *
1 SELECT* Не стоит заблуждаться, что данный пункт не столь важен. Вы не заметите как ваша выборка, после применения нескольких JOIN, превратиться в огромный объём данных, который сильно замедлит выполнения ваших запросов и увеличит нагрузку на tempdb.
-
Обязательно фильтруйте выборку
Transact-SQL
WHERE
1 WHERE Выбирайте только нужные данные, чтобы их хранение и скорость обработки были на должном уровне.
- Создавайте индексы на временных таблицах (temporary tables) после загрузки в них данных. Если индексы будут созданы до загрузки данных, то на момент обращениях к временной таблице, её статистика может быть не актуальной
-
Существует так называемая проблема
Transact-SQL
Tempdb Latch Contention
1 TempdbLatchContention Суть её заключается в конкуренции за tempdb. Дело в том, что только один поток, в один момент времени, может создавать и удалять объекты в tempdb, остальные обязаны ожидать завершения операции. Для обхода «Tempdb Latch Contention» достаточно иметь несколько файлов данных tempdb, но они должны быть одного размера, чтобы механизм распределения данных между файлами данных отрабатывал корректно. Для этого необходимо глобально включить флаг трассировки 1118 (одновременное увеличение размера всех файлов данных БД), создать несколько файлов данных tempdb с одинаковым размером и настроить им один размер приращения. Количество файлов должно быть равно вашему количеству ядер процессора, но при количестве процессоров более 8, необходимо эксперементально вычислить это количество. В такой конфигурации начните с 8 файлов данных tempdb и наблюдайте за сервером.
Шаг 4: Переменные
Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Давайте объявим несколько переменных:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Работа с переменными
Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
DELIMITER //
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
BEGIN
DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;
INSERT INTO table1 VALUES (a);
SET str = 'I am a string';
SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;
END //
Особенности процедур SQL
- Может содержать операторы и функции SQL Procedural Language, которые поддерживают реализацию логики потока управления вокруг традиционных статических и динамических операторов SQL.
- Легко реализовать, потому что они используют простой высокоуровневый, строго типизированный язык.
- Процедуры SQL более надежны, чем эквивалентные внешние процедуры.
- Поддержка режимов ввода, вывода и ввода-вывода параметров.
- Поддержка простой, но мощной модели обработки условий и ошибок.
- Возврат нескольких наборов результатов вызывающей стороне или клиентскому приложению.
- Позволяет легко получить доступ к значениям SQLSTATE и SQLCODE в качестве специальных переменных.
- Находятся в базе данных и автоматически сохраняются и восстанавливаются.
- Может вызываться везде, где поддерживается оператор CALL.
- Поддержка вложенных вызовов процедур для других процедур SQL или процедур, реализованных на других языках.
Типы хранимых процедур
Системные хранимые процедуры предназначены для выполнения раз-
личных административных действий. Практически все действия по адми-
нистрированию сервера выполняются с их помощью. Можно сказать, что
системные хранимые процедуры являются интерфейсом, обеспечивающим
работу с системными таблицами. Системные хранимые процедуры имеют
префикс sp_, хранятся в системной базе данных и могут быть вызваны в
контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные дейст-
вия. Хранимые процедуры – полноценный объект базы данных. Вследствие
этого каждая хранимая процедура располагается в конкретной базе дан-
ных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время,
после чего автоматически уничтожаются сервером. Они делятся на ло-
кальные и глобальные. Локальные временные хранимые процедуры могут
быть вызваны только из того соединения, в котором созданы. При созда-
нии такой процедуры ей необходимо дать имя, начинающееся с одного
символа #. Как и все временные объекты, хранимые процедуры этого типа
автоматически удаляются при отключении пользователя, перезапуске или
остановке сервера. Глобальные временные хранимые процедуры доступны
для любых соединений сервера, на котором имеется такая же процедура.
Для ее определения достаточно дать ей имя, начинающееся с символов ##.
Удаляются эти процедуры при перезапуске или остановке сервера, а также
при закрытии соединения, в контексте которого они были созданы.
Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):
Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS
Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются
Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.
Хранимая процедура
— это специальный тип пакета инструкций Transact-SQL, созданный, используя язык SQL и процедурные расширения. Основное различие между пакетом и хранимой процедурой состоит в том, что последняя сохраняется в виде объекта базы данных. Иными словами, хранимые процедуры сохраняются на стороне сервера, чтобы улучшить производительность и постоянство выполнения повторяемых задач.
Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры
предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.
При создании хранимой процедуры можно определить необязательный список параметров. Таким образом, процедура будет принимать соответствующие аргументы при каждом ее вызове. Хранимые процедуры могут возвращать значение, содержащее определенную пользователем информацию или, в случае ошибки, соответствующее сообщение об ошибке.
Хранимая процедура предварительно компилируется перед тем, как она сохраняется в виде объекта в базе данных. Предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Это свойство хранимых процедур предоставляет важную выгоду, заключающуюся в устранении (почти во всех случаях) повторных компиляций процедуры и получении соответствующего улучшения производительности. Это свойство хранимых процедур также оказывает положительный эффект на объем данных, участвующих в обмене между системой баз данных и приложениями. В частности, для вызова хранимой процедуры объемом в несколько тысяч байтов может потребоваться меньше, чем 50 байт. Когда множественные пользователи выполняют повторяющиеся задачи с применением хранимых процедур, накопительный эффект такой экономии может быть довольно значительным.
Хранимые процедуры можно также использовать для следующих целей:
для создания журнала логов о действиях с таблицами баз данных.
Использование хранимых процедур предоставляет возможность управления безопасностью на уровне, значительно превышающем уровень безопасности, предоставляемый использованием инструкций GRANT и REVOKE, с помощью которых пользователям предоставляются разные привилегии доступа. Это возможно вследствие того, что авторизация на выполнение хранимой процедуры не зависит от авторизации на модифицирование объектов, содержащихся в данной хранимой процедуре, как это описано в следующем разделе.
Хранимые процедуры, которые создают логи операций записи и/или чтения таблиц, предоставляют дополнительную возможность обеспечения безопасности базы данных. Используя такие процедуры, администратор базы данных может отслеживать модификации, вносимые в базу данных пользователями или прикладными программами.
Creating a stored procedure with an output parameter
Below is the example of a stored procedure with an output parameter. The following example retrieves the EmpID which is an auto identity column when a new employee is inserted.
| 1 | CREATETABLEEmployee (EmpIDintidentity(1,1),EmpNamevarchar(500)) |
|
1 |
CREATEPROCEDUREins_NewEmp_with_outputparamaters (@Enamevarchar(50), @EIdintoutput) AS SETNOCOUNTON INSERTINTOEmployee (EmpName)VALUES(@Ename) SELECT@EId=SCOPE_IDENTITY() |
Executing the stored procedures with output parameters is bit different. We must declare the variable to store the value returned by the output parameter.
|
1 |
declare@EmpIDINT EXECins_NewEmp_with_outputparamaters’Andrew’,@EmpIDOUTPUT SELECT@EmpID |
Назначение хранимых процедур
Давайте рассмотрим основные примеры применения хранимых процедур:
- Упрощение сложных запросов: соединение их в один блок, запускающийся одной командой
- Сохранение целостности данных за счёт минимизации количества выполняемых операций. Чем меньше операций, тем меньше потенциальных ошибок, чем меньше ошибок, тем меньше вероятность внести неверные данные в базу и впоследствии наткнуться на противоречия. К тому же, если все разработчики проекта используют одинаковую процедуру, результаты более предсказуемы, т.к. исключается человеческий фактор
- Также снижается риск повреждения данных за счёт того, что прямого доступа посредством запросов к ним нет, а если изменения необходимы, они вносятся только в саму хранимую процедуру
- Обычно хранимые процедуры хранятся в БД в скомпилированном виде, это снижает время на их обработку, а значит, повышает производительность кода
- Хранимые процедуры дают возможность писать более гибкий и мощный код, реализовывать сложные операции с данными
Итак, основные преимущества хранимых процедур — это увеличение производительности, безопасности и простота. Это действительно важные качества для любой программы, однако существуют и значительные минусы.
Не можете написать — просто используйте
Хранимые процедуры написать сложнее, чем обычные SQL-запросы, велик шанс допустить ошибку. Также сложности могут возникнуть с переносимостью кода, т.к. синтаксис хранимых процедур сильно различается в разных СУБД. Поэтому администраторы нередко ограничивают доступ обычным пользователям на их создание. К счастью, это не запрещает использовать уже готовые процедуры, если таковые имеются в вашей базе данных.
Русские Блоги
Первое реальное знакомство с хранимыми процедурами, в предыдущей работе, хотя я тоже был разоблачен, но все вызывают хранимые процедуры, написанные другими для выполнения. Не понял это в глубине. К счастью, в моей текущей работе, когда я использую sql для решения проблемы, я обнаружил, что единственное число sql не уверено. Моя первая реакция — использовать хранимые процедуры. Учитесь продавать сейчас, изучает простые хранимые процедуры и решает проблемы на работе. Тогда я поделюсь с вами шагами, которые я узнал. Как вы перешли от хранимой процедуры к новичку хранимой процедуры? Что касается Даниэля, он еще не достиг этого уровня. Тревожно есть горячий тофу, шаг за шагом. У начинающих не должно быть высоких глаз и низких рук .
3.3.7. Изменение функций
Вы можете изменять функцию с помощью оператора ALTER FUNCTION. Общий вид для каждого варианта функции отличается. Давайте рассмотрим каждый из них.
1. Общий вид команды изменения скалярной функции:
ALTER FUNCTION function_name
( } ] )
RETURNS scalar_return_data_type
]
BEGIN
function_body
RETURN scalar_expression
END
2. Общий вид изменения функции, возвращающей таблицу:
ALTER FUNCTION function_name
( } ] )
RETURNS TABLE
]
RETURN select-stmt
3. Общий вид команды изменения функции с множеством операторов, возвращающей таблицу.
ALTER FUNCTION function_name
( } ] )
RETURNS @return_variable TABLE
]
BEGIN
function_body
RETURN
END
::=
{ ENCRYPTION | SCHEMABINDING }
:: =
( { column_definition | table_constraint } )
Следующий пример показывает упрощенный вариант команды, изменяющей функцию:
ALTER FUNCTION dbo.tbPeoples AS -- Новое тело функции
Управление привилегиями и создание синонимов хранимых процедур
Созданную вами программу на PL/SQL обычно не может выполнять никто, кроме вас или администратора базы данных. Предоставить право на ее применение другому пользователю можно с помощью инструкции :
Инструкция лишает пользователя этой привилегии:
Привилегия выполнения также может быть представлена роли:
а также всем пользователям Oracle:
Если привилегия представляется отдельному пользователю (например, с идентификатором ), затем — роли, в которую входит этот пользователь (например, ), и наконец, — всем пользователям, Oracle запомнит все три варианта ее предоставления. Любой из них позволит пользователю выполнять программу. Но если вы захотите лишить данного пользователя этой возможности, то сначала следует отменить привилегию пользователя с идентификатором , а затем аннулировать привилегию на выполнение функции для всех пользователей () и роли (или же исключить пользователя из этой роли).
Для просмотра списка привилегий, предоставленных другим пользователям и ролям, можно запросить информацию представления . Имена программ в этом представлении почему-то выводятся в столбце :
Если пользователь имеет привилегию на выполнение программы , он, возможно, захочет создать для нее синоним, чтобы ему не приходилось указывать перед именем программы префикс с именем схемы:
Теперь пользователь может выполнять программу, ссылаясь на ее синоним:
Так удобнее, потому что в случае изменения владельца программы достаточно будет изменить только ее синоним, а не все те хранимые процедуры, из которых она вызывается.
Синоним можно определить для процедуры, функции, пакета или пользовательского типа. В синонимах процедур, функций и пакетов может скрываться не только схема, но и база данных; синонимы для удаленных программ создаются так же просто, как и для локальных. Однако синонимы могут скрывать только идентификаторы схем и баз данных; синоним не может использоваться вместо пакетной подпрограммы.
Созданный синоним удаляется простой командой:
Использование хранимых процедур
JDBC поддерживает вызов хранимых процедур с помощью класса CallableStatement. Этот класс является фактическим подклассом класса PreparedStatement. Представим, что у нас есть база данных поэтов. В базе данных содержится хранимая процедура для задания возраста поэта во время его смерти (т.е. во сколько лет умер тот или иной поэт). Далее приведен пример вызова хранимой процедуры для внесения в базу данных информации о старом алкоголике Дилане Томасе:
Строка, которая подается в качестве параметра методу prepareCall() – это спецификация вызова процедуры. Она определяет имя вызываемой процедуры и символы ‘?’, которые определяют необходимые параметры.
Интеграция с JDBC – это огромное достоинство для хранимых процедур, поскольку для того, чтобы ее вызывать из вашего приложения, не нужно изменять классы или использовать какие-либо конфигурационные файлы. Все что нужно – это выбрать подходящий JDBC-драйвер для вашей СУБД.
Итак, при выполнении приведенного выше кода, вызывается процедура базы данных. В этом примере мы не пытаемся получить какой бы то ни было результат, поскольку его просто не будет. Узнать была ли процедура выполнена успешно, либо возникла какая-то внештатная ситуация можно с помощью выбрасываемого в этом случае исключения. Ошибка может проявиться в двух ипостасях: либо непосредственно при выполнении процедуры (например, когда тип одного из переданных параметров не соответствует ожидаемому процедурой типу) или же на уровне приложения (например, выбрасываемое исключение, сообщающее о том, что “Dylan Thomas” не найден в базе данных поэтов).
Типы хранимых процедур
Пользовательские процедуры
Пользовательские процедуры могут быть созданы в пользовательской базе данных или любых системных базах данных, за исключением базы данных Resource . Процедура может быть разработана в Transact-SQL или в качестве ссылки на метод Microsoft платформа .NET Framework CLR.
Временные процедуры
Временные процедуры — это один из видов пользовательских процедур. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных tempdb. Существует два вида временных процедур: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных процедур начинаются с одного знака диеза (#); они видны только текущему соединению пользователя и удаляются, когда закрывается соединение. Имена глобальных временных процедур начинаются с двух знаков диеза (##); они видны любому пользователю и удаляются после окончания последнего сеанса, использующего процедуру.
Системные функции
Системные процедуры включены в SQL Server. Физически они хранятся во внутренней скрытой базе данных Resource . Логически они отображаются в схеме sys каждой системной и пользовательской базы данных. В дополнение к этому, база данных msdb также содержит системные хранимые процедуры в схеме dbo . Эти процедуры используются для планирования предупреждений и заданий. Поскольку названия системных процедур начинаются с префикса sp_ , этот префикс не рекомендуется использовать при создании пользовательских процедур. Полный список системных процедур см. в разделе Системные хранимые процедуры (Transact-SQL).
SQL Server поддерживает системные процедуры, предоставляющие интерфейс от SQL Server до внешних программ для различных действий по обслуживанию. Эти расширенные процедуры имеют префикс xp_. Полный список расширенных процедур см. в разделе Общие расширенные хранимые процедуры (Transact-SQL).
Расширенные пользовательские процедуры
Расширенные процедуры позволяют создавать внешние подпрограммы на языке программирования, таком как C. Эти процедуры представляют собой библиотеки DLL, которые экземпляр SQL Server может динамически загружать и запускать.
Примечание
Расширенные хранимые процедуры будут удалены в следующей версии SQL Server. Не используйте его при работе над новыми приложениями и как можно быстрее измените приложения, в которых он в настоящее время используется. Вместо них рекомендуется создавать процедуры CLR. Этот метод более надежен и безопасен, чем использование расширенных хранимых процедур.
3.3.4. Функция, возвращающая таблицу
В следующем примере мы создаем функцию, которая будет возвращать в качестве результата таблицу. В качестве примера, создадим функцию, которая будет возвращать таблицу товаров, и для каждой строки рассчитаем произведение колонок количества и цены:
CREATE FUNCTION GetPrice()
RETURNS TABLE
AS
RETURN
(
SELECT Дата, , Цена,
Количество, Цена*Количество AS Сумма
FROM Товары
)
Начало функции такое же, как у скалярной – указываем оператор CREATE FUNCTION и имя функции. Я специально создал эту функцию без параметров, чтобы вы увидели, как это делается. Не смотря на то, что параметров нет, после имени должны идти круглые скобки, в которых не надо ничего писать. Если не указать скобок, то сервер вернет ошибку и функция не будет создана.
Разница есть и в секции RETURNS, после которой указывается тип TABLE, что говорит о необходимости вернуть таблицу. После этого идет ключевое слово AS и RETURN, после которого должно идти возвращаемое значение. Для функции данного типа в секции RETURN нужно в скобках указать запрос, результат которого и будет возвращаться функцией.
Когда пишете запрос, то все его поля должны содержать имена. Если одно из полей не имеет имени, то результатом выполнения оператора CREATE FUNCTION будет ошибка. В нашем примере последнее поле является результатом перемножения полей «Цена» и «Количество», а такие поля не имеют имени, поэтому мы его задаем с помощью ключевого слова AS.
Посмотрим, как можно использовать такую функцию с помощью оператора SELECT:
SELECT * FROM GetPrice()
Так как мы используем простой оператор SELECT, то мы можем и ограничивать вывод определенными строками, с помощью ограничений в секции WHERE. Например, в следующем примере выбираем из результата функции только те строки, в которых поле «Количество» содержит значение 1:
SELECT * FROM GetPrice() WHERE Количество=1
Функция возвращает в качестве результата таблице, которую вы можете использовать как любую другую таблицу базы данных. Давайте создадим пример в котором можно будет увидеть использование функции в связи с таблицами. Для начала создадим функцию, которая будет возвращать идентификатор работников таблицы tbPeoples и объединенные в одно поле ФИО:
CREATE FUNCTION GetPeoples() RETURNS TABLE AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM tbPeoples )
Функция возвращает нам идентификатор строки, с помощью которого мы легко можем связать результат с таблицей телефонов. Попробуем сделать это с помощью простого SQL запроса:
SELECT * FROM GetPeoples() p, tbPhoneNumbers pn WHERE p.idPeoples=pn.idPeoples
Как видите, функции, возвращающие таблицы очень удобны. Они больше, чем процедуры похожи на объекты просмотра, но при этом позволяют принимать параметры. Таким образом, можно сделать так, чтобы сама функция возвращала нам только то, что нужно. Вьюшки такого не могут делать по определению. Чтобы получить нужные данные, вьюшка должна выполнить свой SELECT запрос, а потом уже во внешнем запросе мы пишем еще один оператор SELECT, с помощью которого ограничивается вывод до необходимого. Таким образом, выполняется два запроса SELECT, что для большой таблицы достаточно накладно. Функция же может сразу вернуть только то, что нужно.
Рассмотрим пример, функция GetPeoples у нас возвращает все строки таблицы. Чтобы получить только нужную фамилию, нужно писать запрос типа:
SELECT * FROM GetPeoples() WHERE FIO LIKE 'ПОЧЕЧКИН%'
В этом случае будут выполняться два запроса: этот и еще один внутри функции. Но если передавать фамилию в качестве параметра в функцию и там сделать секцию WHERE, то можно обойтись и одним запросом SELECT:
CREATE FUNCTION GetPeoples1(@Famil varchar(50)) RETURNS TABLE AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM tbPeoples WHERE vcFamil=@Famil )
sp_executesql против EXECUTE
Возможно, вам интересно, зачем использовать sp_executesql в сравнении с EXECUTE. Каковы различия между ними?
Вот несколько причин, по которым Microsoft рекомендует использовать sp_executesql для запуска динамического SQL:
- С помощью EXECUTE все параметры могут быть преобразованы из своего исходного типа в Unicode. Это затрудняет способность оптимизатора сопоставлять динамически построенный SQL с уже существующим планом.
- Используя sp_executesql, оптимизатор распознает параметры в динамическом SQL, что упрощает оптимизатор для соответствия планам.
- Легче читать параметризованные запросы, чем читать кучу объединяющего их текста.
- Параметрированные запросы менее подвержены атакам SQL-инъекций.
Заключение
В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
