Процедура SQL

Введение

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

Возможности процедур SQL

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

Недостатки

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

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

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

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

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

 СОЗДАТЬ ПРОЦЕДУРУ имя_процесса ([{[IN | OUT | INOUT] [имя_параметра] тип данных [AS LOCATOR]  [РЕЗУЛЬТАТ]}[, ... ]]) [ВОЗВРАЩАЕТ тип данных [КАК ЛОКАТОР] ЯЗЫК {ADA |  C |  FORTRAN |  МАМПЫ |  ПАСКАЛЬ |  PLI |  SQL} [RETURN NULL ON NULL INPUT |  ВЫЗОВ НА NULL INPUT] [ДИНАМИЧЕСКИЕ НАБОРЫ РЕЗУЛЬТАТОВ int] блок_кода 

Пояснение:

СОЗДАТЬ ПРОЦЕДУРУ имя_процесса: Создает новую хранимую процедуру с именем proc_ name.

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

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

Код SQL:

  [{IN |  ВНЕ |  INOUT}] имя_параметра_1 тип данных, [{IN |  ВНЕ |  INOUT}] имя_параметра_2 тип данных, [{IN |  ВНЕ |  INOUT}] имя_параметра_3 тип данных, [...]  

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

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

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

datatype: указывает тип данных параметра (ов).

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

[RETURN NULL ON NULL INPUT | CALL ON NULL INPUT]: Когда RETURNS NULL ON NULL INPUT (опция используется с основным языком, который не поддерживает NULL) устанавливает, функция немедленно возвращает значение NULL, если ей передано значение NULL. CALL ON NULL INPUT указывает, что функция должна быть вызвана, если какие-либо или все значения аргументов имеют значение NULL, что делает функцию ответственной за проверку значений аргументов NULL. Функция может возвращать нулевое или ненулевое значение.

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

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

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

Реализация

Точная и правильная реализация хранимых процедур зависит от системы базы данных и может быть разной. от одного к другому. Основные поставщики баз данных поддерживают их в той или иной форме. Хранимые процедуры могут быть реализованы на различных языках программирования (в зависимости от системы баз данных), например, SQL, Java, C или C ++. См. Следующую систему базы данных и язык реализации:

Пример: процедура SQL

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

Код SQL:

  СОЗДАТЬ ПРОЦЕДУРУ STUDENT_MARKS  (В STUDENT_REG_NO CHAR (15), IN TOTAL_MARKS DECIMAL (7,2), NO_SUBJECTS INT (3)) LANGUAGE SQL МОДИФИЦИРУЕТ ОБНОВЛЕНИЕ ДАННЫХ SQL STUDENTMAST.MARKS SET PERCENTAGE = TOTAL_MARKS/NO_SUBJECT WHERE 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} имя_объекта [({имя_параметра тип данных} [, ... ])] [ИМЯ имя_нового_объекта] [ЯЗЫК {ADA |  C |  FORTRAN |  МАМПЫ |  ПАСКАЛЬ |  PLI |  SQL}] [СТИЛЬ ПАРАМЕТРА {SQL |  GENERAL}] [NO SQL |  СОДЕРЖИТ SQL |  ЧИТАЕТ ДАННЫЕ SQL |  МОДИФИЦИРУЕТ ДАННЫЕ SQL] [ВОЗВРАЩАЙТЕ NULL ПРИ НУЛЕВОМ ВХОДЕ |  ВЫЗОВ НА NULL INPUT] 
 [НАБОРЫ ДИНАМИЧЕСКИХ РЕЗУЛЬТАТОВ int] [КАСКАД |  RESTRICT]  

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

 DROP PROCEDURE proc_name 

proc_name: имя процедуры.

См. наше руководство по процедурам MySQL

Практические упражнения SQL

• SQL-упражнения, практика, решение
• SQL Получение данных из таблиц [33 упражнения]
• Логические операторы SQL и реляционные операторы [12 упражнений]
• Подстановочные знаки SQL и специальные операторы [22 упражнения]
• Агрегатные функции SQL [25 упражнений]
• Выходные данные запроса форматирования SQL [10 упражнений]
• SQL-запросы к нескольким таблицам [7 упражнений]
• ФИЛЬТРАЦИЯ и СОРТИРОВКА в базе данных HR [38 упражнений]
• SQL JOINS
• SQL JOINS [29 упражнений]
• SQL JOINS в базе данных HR [27 упражнений]
• SQL SUBQUERIES
• ПОДПРОСЫ SQL [39 упражнений]
• ПОДПРОСЫ SQL в базе данных HR [55 упражнений]
• SQL Объединение [9 упражнений]
• Просмотр SQL [16 упражнений]
• Управление учетными записями пользователей SQL [16 упражнений]
• База данных фильмов
• БАЗОВЫЕ запросы к базе данных фильмов [10 упражнений]
• ПОДПРОСЫ в базе данных фильмов [16 упражнений]
• ПРИСОЕДИНЯЕТСЯ к базе данных фильмов [24 упражнения]
• Soccer Database
• Введение
• БАЗОВЫЕ запросы к базе данных футбола [29 упражнений]
• ПОДзапросы к базе данных футбола [33 упражнения]
• ПРИСОЕДИНЯЕТСЯ к запросам в базе данных футбола [61 упражнения]
• База данных больниц
• Введение
• BASIC, SUBQUERIES и JOINS [39 упражнений]
• База данных сотрудников
• БАЗОВЫЕ запросы к базе данных сотрудников [115 упражнений]
• ПОДПРОСЫ в базе данных сотрудников [77 упражнений]
• Еще не все!

Хотите улучшить статью выше? Публикуйте свои заметки/комментарии/примеры через Disqus.