Процедура SQL

Последнее обновление 15 марта 2019 07:14:04 (UTC / GMT +8 часов)

script1adsense2code

script1adsense3code

Вступление

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

Особенности процедур SQL

Может содержать операторы и функции SQL Procedural Language, которые поддерживают реализацию логики потока управления вокруг традиционных статических и динамических операторов SQL.
Легко реализовать, потому что они используют простой высокоуровневый, строго типизированный язык.
Процедуры SQL более надежны, чем эквивалентные внешние процедуры.
Поддержка режимов ввода, вывода и ввода-вывода параметров.
Поддержка простой, но мощной модели обработки условий и ошибок.
Возврат нескольких наборов результатов вызывающей стороне или клиентскому приложению.
Позволяет легко получить доступ к значениям SQLSTATE и SQLCODE в качестве специальных переменных.
Находятся в базе данных и автоматически сохраняются и восстанавливаются.
Может вызываться везде, где поддерживается оператор CALL.
Поддержка вложенных вызовов процедур для других процедур SQL или процедур, реализованных на других языках.

Недостатки

Языки хранимых процедур зависят от поставщика. Поэтому, если вы переключаетесь на базу данных другого поставщика, требуется переписать существующие хранимые процедуры.
Языки хранимых процедур от разных поставщиков имеют разный уровень сложности. Например, Oracle PL / SQL имеет больше языковых и встроенных функций, чем Microsoft T-SQL.
Поддержка инструментов для написания и отладки хранимых процедур часто не так хороша, как для других языков программирования, хотя это зависит от поставщиков и языков.

Определение процедуры SQL

Оператор CREATE PROCEDURE для процедур SQL:

Называет процедуру
Создает хранимую процедуру
Определяет параметры и их атрибуты
Предоставляет другую информацию о процедуре, которая будет использоваться при вызове процедуры
Определяет тело процедуры

Вот полный синтаксис CREATE PROCEDURE (синтаксис основан на стандарте SQL: 2003).

Синтаксис:

 СОЗДАТЬ ПРОЦЕДУРУ proc_name
([{[IN | OUT | INOUT] [имя_параметра] 
тип данных [AS LOCATOR] [RESULT]}
[, ...]])
[ВОЗВРАЩАЕТСЯ тип данных [КАК ЛОКАТОР]
ЯЗЫК {ADA | C | Фортран | MUMPS | ПАСКАЛЬ | PLI | SQL}
[ВОЗВРАЩАЙТЕ НУЛЬ НА НУЛЕВОМ ВХОДЕ | ВЫЗОВ НА НУЛЕВОЙ ВХОД]
[ДИНАМИЧНЫЕ РЕЗУЛЬТАТЫ НАБОРЫ int]
code_block

Объяснение:

CREATE PROCEDURE proc_name: создает новую хранимую процедуру с именем proc_ name.

([{[IN | OUT | INOUT] [имя_параметра] тип данных [AS LOCATOR] [RESULT]} [, ...]]): указывает количество параметров процедуры и тип данных каждого параметра. Параметр для процедуры может использоваться только для ввода, вывода или как для ввода, так и для вывода. Имя параметра должно быть уникальным в процедуре.

Вы можете объявить параметр (ы) следующим образом:

Код SQL:

[{IN | OUT | INOUT}] parameter_name_1 datatype,
[{IN | OUT | INOUT}] parameter_name_2 datatype,
[{IN | OUT | INOUT}] parameter_name_3 datatype,[...]

IN: Определяет параметр как входной параметр для процедуры.

OUT: Определяет параметр как выходной параметр, который возвращается процедурой.

INOUT: Определяет параметр как входной и выходной параметр для процедуры.

Тип данных : Определяет тип данных параметра (ов).

Подпункт AS LOCATOR (необязательный) используется для проверки внешней подпрограммы с параметром RETURNS, который представляет собой BLOB (набор двоичных данных, хранящихся в базе данных как один объект.), CLOB (Хранить символьные данные, закодированные в наборе символов базы данных .), NCLOB (Хранит символьные данные, закодированные в национальном наборе символов), ARRAY или пользовательский тип.

[ВОЗВРАЩАЙТЕ НУЛЬ НА НУЛЕВОМ ВХОДЕ | CALL ON NULL INPUT]: когда RETURNS NULL ON NULL INPUT (опция используется с языком хоста, который не может поддерживать NULL), функция немедленно возвращает значение NULL, если ей передано значение NULL. CALL ON NULL INPUT указывает, что функция должна вызываться, если какие-либо или все значения аргументов равны NULL, что делает функцию ответственной за проверку значений NULL для аргументов. Функция может возвращать нулевое или ненулевое значение.

ЯЗЫК {ADA | C | Фортран | MUMPS | ПАСКАЛЬ | PLI | SQL}: большинство платформ баз данных не поддерживают все эти языки и могут поддерживать несколько не упомянутых, таких как Java. По умолчанию используется SQL.

[ДИНАМИЧЕСКИЕ НАБОРЫ РЕЗУЛЬТАТОВ int]: Объявление динамических наборов результатов хранимой процедурой может открыть определенное количество курсоров (int), и эти курсоры становятся видимыми после возврата из процедуры. По умолчанию 0.

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

Реализация

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

CREATE PROCEDURE STUDENT_MARKS
(IN STUDENT_REG_NO CHAR(15),IN TOTAL_MARKS DECIMAL(7,2), NO_SUBJECTS INT(3))
 LANGUAGE SQL MODIFIES SQL DATA 
 UPDATE STUDENTMAST.MARKS
 SET PERCENTAGE = TOTAL_MARKS/NO_SUBJECT
 WHERE REG_NO = STUDENT_REG_NO

Объяснение:

Имена процедуры STUDENT_MARKS
Определяет параметр STUDENT_REG_NO (символьный тип данных длиной 15), TOTAL_MARKS (десятичный тип данных) и NO_SUBJECTS (целочисленный тип), которые являются входными параметрами.
Указывает, что процедура является процедурой SQL, которая изменяет данные SQL.
Определяет тело процедуры как один оператор UPDATE. Когда процедура вызывается, оператор UPDATE выполняется с использованием значений, переданных для STUDENT_REG_NO, TOTAL_MARKS и NO_SUBJECTS
Определяет тело процедуры

Вызвать процедуру

Оператор CALL используется для вызова процедуры, которая хранится в базе данных. Вот синтаксис:

 CALL sp_name ([параметр [, ...]]) 
CALL sp_name [()]

sp_name: название процедуры.

параметр, ...: список параметров, заключенных в скобки и разделенных запятыми.

Изменить процедуру

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

Код SQL:

ALTER {PROCEDURE | FUNCTION} object_name
[( {parameter_name datatype }[, ...] )]
[NAME new_object_name]
[LANGUAGE {ADA | C | FORTRAN | MUMPS | PASCAL | PLI | SQL}]
[PARAMETER STYLE {SQL | GENERAL}]
[NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA]
[RETURN NULL ON NULL INPUT | CALL ON NULL INPUT]
[DYNAMIC RESULT SETS int]
[CASCADE | RESTRICT]