Что такое процедуры в sql

Русские Блоги

MySql хранимые процедуры и пользовательские функции

1. Обзор хранимых процедур

Хранимая процедура (хранимая процедура) — это набор операторов SQL для выполнения определенной функции, хранящейся в базе данных, после первой компиляции она вызывается снова без повторной компиляции. Пользователь указывает имя хранимой процедуры и дает параметры (если Хранимая процедура принимает параметры) для ее выполнения. Подобно методу в Java, его можно вызывать несколько раз после определения.

Хранимая процедура имеет следующие преимущества

  1. Обычно хранимые процедуры помогают повысить производительность приложений. При создании хранимая процедура компилируется и сохраняется в базе данных.
  2. Может использоваться для улучшения возможности повторного использования кода и упрощения операций
  3. Сохраненная программа безопасна

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

Синтаксическая структура для создания хранимых процедур

Когда хранимая процедура имеет один и только один оператор SQL, BEGIN с END Может быть опущено. Параметры в процессе хранения можно разделить на 3 части, а именно [Режим параметров] [Имя параметра] [Тип параметра] Такие как IN name VARCHAR[20] 。

Режимы параметров также делятся на 3 типа, а именно:

IN : Входные параметры
OUT : Может использоваться как параметр возвращаемого значения
INOUT : Может использоваться как входной параметр или как параметр возвращаемого значения

DELIMITER [имя хранимой процедуры] Он не имеет ничего общего с синтаксисом хранимой процедуры и используется для обозначения конца хранимой процедуры. Последняя команда ( DELIMITER ; ) Измените разделитель обратно на точку с запятой ( ; )。

Вызов хранимой процедуры для использования CALL [имя хранимой процедуры] ([параметр 1], [параметр 2] . ); Утверждение.

Исключить использование хранимых процедур DROP PROCEDURE IF EXISTS [имя хранимой процедуры]; 。

В-третьих, простое использование хранимых процедур

Создать тестовую таблицу

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

Создайте хранимую процедуру (чтобы t_user Вставьте два фрагмента данных в таблицу данных)

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

3.2 Создание IN Сохраненная процедура для параметров режима

Создайте хранимую процедуру (согласно входящей id Получить логин)

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

3.3 Создание OUT Сохраненная процедура для параметров режима

Создайте хранимую процедуру (согласно входящей id Получить имя пользователя, назначенное выходной переменной)

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

3.4 Создание INOUT Сохраненная процедура для параметров режима

Создать хранимую процедуру (передать целочисленный параметр и вернуть после увеличения значения в 2 раза)

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

В-четвертых, обзор пользовательских функций.

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

Синтаксис настраиваемой функции

Переменная в пользовательской функции состоит всего из двух частей, а именно [Имя переменной] [Тип переменной] ,Такие как username VARCHAR[20] . С помощью приведенного выше сравнения синтаксиса я не знаю, сможете ли вы найти, что пользовательская функция в MySql очень похожа на функцию возвращаемого значения в Java.

Синтаксис для вызова пользовательской функции: ВЫБРАТЬ [имя функции] ([параметр 1], [параметр 2] . ); 。

Синтаксис для удаления пользовательской функции: DROP FUNCTION IF EXISTS [имя функции]; 。

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

5.1 Создание пользовательской функции без параметров

Создайте настраиваемую функцию (запрос t_user Количество всех записей в и возврат)

Вызов пользовательской функции и просмотр результата

5.2 Создание пользовательской функции с параметрами

Создайте собственную функцию (по мнению пользователя id Получить имя пользователя, вернуться после назначения)

Вызов пользовательской функции и просмотр результата

Шесть, резюме

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

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

Интеллектуальная рекомендация

Разработка Android NDK (1)

Подтверждение: я прошел настоящий тест. Кстати, многие люди в интернете не тестировали случайные волосы. Структура файлового каталога: app / src / main / java / com / hd / jni 1. Определите интерфейс .

Хранимые процедуры в SQL

Ключевые слова
• IN (Input) – входной параметр
• OUT (Output) – выходной параметр
• INOUT – входной и выходной, а также поле (без параметров)
• DYNAMIC RESULT SET показывает, что процедура может открыть указанное число курсоров, которые останутся открытыми после возврата из процедуры

Примечания
Не рекомендуется использовать много параметров в хранимых процедурах (в первую очередь больших чисел и символьных строк) из-за перегрузки сети и стека. На практике в существующих диалектах Transact-SQL, PL/SQL и Informix наблюдается существенное отличие от стандарта, как в объявлении и использовании параметров, объявлении переменных, так и в вызове подпрограмм. Microsoft рекомендует применять следующую аппроксимацию для оценки размера КЭШа хранимых процедур:
=(максимальное количество одновременно работающих пользователей)*(размер самого большого плана выполнения)*1.25. Определение размера плана выполнения в страницах можно сделать с помощью команды: DBCC MEMUSAGE.

Вызов процедуры

Во многих существующих СУБД вызов хранимых процедур выполняется с помощью оператора:

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

Пример объявления процедуры

CREATE PROCEDURE Proc1 AS //объявляем процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //объявляем курсор
OPEN Cur1 //открываем курсор
FETCH NEXT FROM Cur1 //считываем данные из курсора
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //закрываем курсор
DEALLOCATE Cur1
EXECUTE Proc1 //запускаем процедуру

Полиморфизм
Две подпрограммы с одним и тем же именем могут быть созданы в одной и той же схеме, если параметры этих двух подпрограмм являются в такое мере отличными друг от друга, чтобы их можно было различать. Для того, чтобы различать две подпрограммы с одним и тем же именем в одной схеме, каждой из них дается альтернативное и уникальное имя (specific name). Такое имя может быть явно указано, когда определяется подпрограмма. При вызове подпрограмм при наличии нескольких одинаковых имен определение нужной подпрограммы осуществляется в несколько шагов:
• Первоначально определяются все процедуры с указанным именем, а если таковых нет, то все функции с заданным именем.
• Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).
• Для них отбираются те, у которых число параметров соответствует числу аргументов вызова. Проверяются указанные типы данных у параметров и их позиции.
• Если осталось более одной подпрограммы, то выбирается та, квалификационное имя которой короче.
На практике в Oracle полиморфизм поддерживается для функций, объявленных только в пакете, DB@ — в разных схема, а в Sybase и MS SQL Server перегрузка запрещена.

Удаление и изменение процедур
Для удаления процедуры используется оператор:

Для изменения процедуры используется оператор:

ALTER PROCEDURE [([<IN|OUT|INOUT>] )]
BEGIN [ATOMIC]
<SQL-операторы>
END

Привилегии на выполнение процедур

GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]

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

MySQL хранимые процедуры

Хранимые процедуры появились начиная с 5 версии MySQL. Они позволяют автоматизировать сложные процессы на уровне MySQL, нежели использовать для этого внешние скрипты. Это даёт нам наиболее высокую скорость выполнения, т.к. мы не гоняем большое количество запросов, а всего лишь один раз вызываем ту или иную процедуру (или функцию).

Что для этого нужно? Установите MySQL сервер версии 5 или выше (dev.mysql.com/downloads). Процедуры можно создавать как запросы, например через командную строку MySQL, но для удобства советую скачать MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Данный пакет включает в себя три программы — MySQL Administrator, MySQL Query Browser и MySQL Migration Toolkit. Нам понадобятся первые две. (Хотя можно обойтись одним MySQL Query Browser, но все эти $$ в хранимых процедурах иногда могут сбить с толку).

Первая хранимая процедура

Итак, открываем MySQL Administrator, подключаемся к серверу MySQL и создаем новую схему (базу данных): щелкните Catalogs, выберите Create New Schema в области Schemata (Ctrl+N). Назовите ее как-нибудь (например db). Откройте только что созданную схему, выберите вкладку Stored procedures и щелкните кнопку Create Stored Proc. Назовите свою процедуру procedure1. В тело процедуры (между BEGIN и END) впишите следующее:

И нажмите Execute SQL — процедура создана. Откройте MySQL Query Browser, выберите свою схему (db) и впишите следующий запрос:

Переменные в MySQL

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

Простые переменные

Системные переменные

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

Параметры в хранимых процедурах

Здесь тоже всё достаточно просто. Изменяем первую строку, объявляющая саму процедуру:

Здесь, ключевое слово IN указывает на то, что параметр указан только для чтения. Далее с этим параметром работаем как с обычной переменной внутри процедуры:

Условия, Циклы. IF THEN ELSE, WHILE

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

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

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

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

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

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

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

Курсоры (MySQL Cursors)

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

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

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

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

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

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

Извлечение данных

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

Объявляем две переменных — iTags — количество тегов, и iThreads — общее количество тем.

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

Добавить комментарий

Ваш адрес email не будет опубликован.