Как удалить процедуру sql
Procedures are defined using PL/SQL. Refer to Oracle Database PL/SQL Language Reference for complete information on creating, altering, and dropping procedures.
Use the DROP PROCEDURE statement to remove a standalone stored procedure from the database. Do not use this statement to remove a procedure that is part of a package. Instead, either drop the entire package using the DROP PACKAGE statement, or redefine the package without the procedure using the CREATE PACKAGE statement with the OR REPLACE clause.
The procedure must be in your own schema or you must have the DROP ANY PROCEDURE system privilege.
Specify the schema containing the procedure. If you omit schema , then Oracle Database assumes the procedure is in your own schema.
Specify the name of the procedure to be dropped.
When you drop a procedure, Oracle Database invalidates any local objects that depend upon the dropped procedure. If you subsequently reference one of these objects, then the database tries to recompile the object and returns an error message if you have not re-created the dropped procedure.
Dropping a Procedure: Example
The following statement drops the procedure remove_emp owned by the user hr and invalidates all objects that depend upon remove_emp :
SQL — Урок 15. Хранимые процедуры. Часть 1.
Параметры это те данные, которые мы будем передавать процедуре при ее вызове, а операторы — это собственно запросы. Давайте напишем свою первую процедуру и убедимся в ее удобстве. В уроке 10, когда мы добавляли новые записи в БД shop, мы использовали стандартный запрос на добавление вида:
Т.к. подобный запрос мы будем использовать каждый раз, когда нам необходимо будет добавить нового покупателя, то вполне уместно оформить его в виде процедуры:
Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с ; на «//», чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора DELIMITER // :
Таким образом, мы указали СУБД, что выполнять команды теперь следует после //. Следует помнить, что переопределение разделителя осуществляется только на один сеанс работы, т.е. при следующем сеансе работы с MySql разделитель снова станет точкой с запятой и при необходимости его придется снова переопределять. Теперь можно разместить процедуру:
Итак, процедура создана. Теперь, когда нам понадобится ввести нового покупателя нам достаточно ее вызвать, указав необходимые параметры. Для вызова хранимой процедуры используется оператор CALL , после которого указывается имя процедуры и ее параметры. Давайте добавим нового покупателя в нашу таблицу Покупатели (customers):
Согласитесь, что так гораздо проще, чем писать каждый раз полный запрос. Проверим, работает ли процедура, посмотрев, появился ли новый покупатель в таблице Покупатели (customers):
Появился, процедура работает, и будет работать всегда, пока мы ее не удалим с помощью оператора DROP PROCEDURE название_процедуры .
Как было сказано в начале урока, процедуры позволяют объединить последовательность запросов. Давайте посмотрим, как это делается. Помните в уроке 11 мы хотели узнать, на какую сумму нам привез товар поставщик «Дом печати»? Для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. А если мы захотим узнать, на какую сумму нам привез товар другой поставщик? Придется составлять новые запросы, объединения и т.д. Проще один раз написать хранимую процедуру для этого действия.
Казалось бы, проще всего взять уже написанные в уроке 11 представление и запрос к нему, объединить в хранимую процедуру и сделать идентификатор поставщика (id_vendor) входным параметром, вот так:
Но так процедура работать не будет. Все дело в том, что в представлениях не могут использоваться параметры . Поэтому нам придется несколько изменить последовательность запросов. Сначала мы создадим представление, которое будет выводить идентификатор поставщика (id_vendor), идентификатор продукта (id_product), количество (quantity), цену (price) и сумму (summa) из трех таблиц Поставки (incoming), Журнал поставок (magazine_incoming), Цены (prices):
А потом создадим запрос, который просуммирует суммы поставок интересующего нас поставщика, например, с id_vendor=2:
Вот теперь мы можем объединить два этих запроса в хранимую процедуру, где входным параметром будет идентификатор поставщика (id_vendor), который будет подставляться во второй запрос, но не в представление:
Проверим работу процедуры, с разными входными параметрами:
Как видите, процедура срабатывает один раз, а затем выдает ошибку, говоря нам, что представление report_vendor уже имеется в БД. Так происходит потому, что при обращении к процедуре в первый раз, она создает представление. При обращении во второй раз, она снова пытается создать представление, но оно уже есть, поэтому и появляется ошибка. Чтобы избежать этого возможно два варианта.
Первый — вынести представление из процедуры. То есть мы один раз создадим представление, а процедура будет лишь к нему обращаться, но не создавать его. Предварительно не забудет удалить уже созданную процедуру и представление:
Второй вариант — прямо в процедуре дописать команду, которая будет удалять представление, если оно существует:
Перед использованием этого варианта не забудьте удалить процедуру sum_vendor, а затем проверить работу:
Как видите, сложные запросы или их последовательность действительно проще один раз оформить в хранимую процедуру, а дальше просто обращаться к ней, указывая необходимые параметры. Это значительно сокращает код и делает работу с запросами более логичной.
Научись программировать на Python прямо сейчас!
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Procedures SQL Server
В SQL Server процедура представляет собой хранимую программу, в которую вы можете передавать параметры. Она не возвращает значение, как функция. Тем не менее, она может вернуть статус успеха / отказа в процедуру, вызвавшую ее.
Create Procedure
Вы можете создавать свои собственные хранимые процедуры в SQL Server (Transact-SQL). Давайте посмотрим поближе.
Синтаксис
Синтаксис Procedures в SQL Server (Transact-SQL):
Параметры или аргументы
schema_name — имя схемы, которой принадлежит хранимая процедура.
procedure_name — имя для назначения этой процедуры в SQL Server.
@parameter — один или несколько параметров передаются в процедуру.
type_schema_name — схема, которая владеет типом данных, если это применимо.
Datatype — тип данных для @parameter .
VARYING — задается для параметров курсора, когда результирующий набор является выходным параметром.
default — значение по умолчанию для назначения параметру @parameter .
OUT – это означает, что @parameter является выходным параметром.
OUTPUT — это означает, что @parameter является выходным параметром.
READONLY — это означает, что @parameter не может быть перезаписана хранимой процедурой.
ENCRYPTION — это означает, что источник хранимой процедуры не будет сохранен как обычный текст в системных представлениях SQL Server.
RECOMPILE — это означает, что план запросов не будет кэшироваться для этой хранимой процедуры.
EXECUTE AS — устанавливает контекст безопасности для выполнения хранимой процедуры.
FOR REPLICATION — Это означает, что хранимая процедура выполняется только во время репликации.
Пример
Рассмотрим пример создания хранимой процедуры в SQL Server (Transact-SQL).
Ниже приведен простой пример процедуры:
Как удалить процедуру или функцию из базы данных
При разработке инструментов, которые взаимодействуют с разными СУБД, приходится учитывать много нюансов, потому что одни и теже вещи реализованы по-разному и стандарт SQL поддерживается по-разному. Если рассмотреть достаточное количество СУБД, то окажется, что нет универсального способа удалить функцию или процедуру.
В статье рассматриваются только реляционные СУБД.
PostgreSQL
В PostgreSQL можно создавать два типа функций: обычные и агрегатные. Процедуры можно создавать начиная с 11 версии, выпущенной в конце 2018 года. Поддержка функций, в том числе и агрегатных, существует с ранних версий.
Удалить любую функцию или процедуру можно с помощью запроса DROP ROUTINE . Синтаксис запроса следующий:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Могут существовать функции и процедуры с одинаковыми именами, но разными аргументами. Создание функций с одинаковым именами называется перегрузкой функций. Аргументы нужно указывать, чтобы устранить неоднозначность при удалении процедуры или функции. Если функция или процедура не имеет перегрузок, то при ее удалении можно не указывать типы аргументов:
Существуют отдельные запросы для удаления агрегатных функций, обычных функций и процедур соответственно:
Синтаксис этих запросов такой же как у DROP ROUTINE .
Запросы DROP ROUTINE и DROP PROCEDURE появились вместе с процедурами в 11 версии. Для удаления функций в ранних версиях нужно использовать запросы DROP AGGREGATE и DROP FUNCTION .
В запросе DROP AGGREGATE нужно обязательно указывать типы аргументов.
В запросе DROP FUNCTION типы аргументов необязательны для не перегруженных функций, начиная с версии 10 (дата релиза: 2017-10-05).
Oracle
Для удаления процедур в Oracle используется следующий запрос:
Для удаления функций аналогичный запрос:
Функции и процедуры могут быть самостоятельными, а могут быть объединены в коллекцию с помощью пакета. Удалить функции и процедуры из пакета можно только удалением или пересозданием пакета.
Самостоятельные функции и процедуры нельзя перегружать, поэтому при удалении не нужно указывать типы аргументов.
MySQL and MariaDB
Для удаления процедур в MySQL и MariaDB используется следующий запрос:
Для удаления функций аналогичный запрос:
MySQL и MariaDB не поддерживают перегрузку процедур и функций, поэтому при удалении указывается только имя.
В MariaDB начиная с версии 10.3.3 (23 декабря 2017) можно создавать агрегатные функции. Агрегатные функции удаляются, как и обычные, запросом DROP FUNCTION .
SQLite
SQLite не поддерживает создание процедур и функций, соответственно нет возможности их удалить.
MS SQL Server
В MS SQL Server для удаления процедур, обычных функций и агрегатных функций используются следующие запросы соответственно:
MS SQL Server не поддерживает перегрузку процедур и функций, поэтому при удалении указывается только имя.
Агрегатные функции в MS SQL Server, в отличии от процедур и обычных функций, только лишь ссылаются на внешнюю реализацию.
Netezza
В Netezza для удаления процедур, обычных функций и агрегатных функций используются следующие запросы соответственно:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Типы аргументов нужны, так как Netezza поддерживает перегрузку процедур и функций. Типы аргументов обязательны даже если нет перегрузок. Если аргументов нет, то должны быть пустые скобки.
Пример создания и удаления процедуры в Netezza:
Функции (в том числе аггрегатные), в отличии от процедур, только лишь ссылаются на внешнюю реализацию.
Informix
В Informix для удаления процедур, обычных функций и агрегатных функций используются следующие запросы соответственно:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Типы аргументов не нужно указывать для агрегатных функций.
Процедуры можно перегружать, поэтому одно имя может быть у нескольких процедур. Чтобы их различать, можно, при создании процедуры, задать ей уникальное имя. То же справедливо для обычных функций. Уникальное имя можно использовать для удаления процедур и обычных функций с помощью следующих запросов:
Если процедура или обычная функция не имеет перегрузок, то при их удалении можно не указывать типы аргументов:
Следующий запрос позволяет удалить процедуру или обычную функцию:
или в случае отсутствия перегрузок:
Такой запрос пригодится, когда неизвестно, что требуется удалить, процедуру или функцию.
Кроме того, для удаления можно использовать уникальное имя:
IBM Db2
В IBM Db2 для удаления процедур и функций используются следующие запросы соответственно:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Процедуры можно перегружать, поэтому одно имя может быть у нескольких процедур. Чтобы их различать, можно, при создании процедуры, задать ей уникальное имя. То же справедливо для функций. Уникальное имя можно использовать для удаления процедур и функций с помощью следующих запросов:
Если процедура или функция не имеет перегрузок, то при ее удалении можно не указывать типы аргументов:
AWS Athena
Athena не поддерживает создание процедур и функций, соответственно нет возможности их удалить.
Teradata
В Teradata для удаления процедур и функций используются следующие запросы соответственно:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Teradata позволяет перегружать функции (но не процедуры). При перегрузке, в запросе CREATE FUNCTION, кроме имени функции, обязательно нужно указать уникальное имя функции. Функцию можно удалить, используя уникальное имя, с помощью следующего запроса:
Если функция не имеет перегрузок, то при ее удалении можно не указывать типы аргументов:
Teradata поддерживает макросы, которые чем-то похожи на процедуры. Для удаления макросов используется следующий запрос:
Vertica
Vertica позволяет создавать внешние процедуры (запрос CREATE PROCEDURE ), которые просто ссылаются на внешний исполняемый файл. Хранимые процедуры не поддреживается.
Для удаления процедур используется следующий запрос:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Процедуры можно перегружать. Типы аргументов обязательны, даже если процедура не имеет перегрузок.
Vertica поддерживает большое количество типов функций:
- Агрегатные функции ( CREATE AGGREGATE ),
- Аналитические функции ( CREATE ANALYTIC FUNCTION ),
- Load filter functions ( CREATE FILTER )
- Load parser functions ( CREATE PARSER )
- Load source functions ( CREATE SOURCE )
- Функции трансформации ( CREATE TRANSFORM FUNCTION )
- Скалярные функции ( CREATE FUNCTION )
- SQL-функции ( CREATE FUNCTION )
В скобках указаны запросы, с помощью которых создаются функции.
Все функции, кроме SQL-функций, имеют внешнюю реализацию, то есть ссылаются на внешнюю динамическую библиотеку.
SQL-функции хранятся в базе, но могут использовать только простые выражения.
Для каждого типа функций, кроме аналитических, есть соответсвующий запрос для удаления функций:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Запросом DROP FUNCTION можно удалить любую функцию, кроме агрегатной и функции трансформации. Функции можно перегружать, поэтому для удаления нужно указывать типы аргументов. Они обязательны даже если функция не имеет перегрузок.
SAP HANA
В SAP HANA для удаления процедур и функций используются следующие запросы соответственно:
Перегрузка процедур и функций не поддерживается, поэтому при удалении указывается только имя.
Apache Impala
Impala позволяет создавать функции (скалярные и агрегатные) с внешней реализацией. Скалярные функции могут быть реализованы на языках C++ и Java. Агрегатные — только на C++.
Для удаления агрегатных функций используется следующий запрос:
- типы_аргументов — типы аргументов, перечисленные через запятую.
Запрос для удаления скалярной функции зависит от языка реализации.
Для удаления скалярных функций на C++ используется следующий запрос:
Для удаления скалярных функций на Java используется следующий запрос:
Impala позволяет перегружать функции, поэтому при удалении нужно указывать типы аргументов. Функции на Java перегружаются средствами самой Java, поэтому аргументы не нужно указывать.
Более широкие возможности по использованию процедур и функций предоставляет HPL/SQL.
Apache Hive
Hive позволяет создавать функции с внешней реализаций на Java. Функции могут быть временными и постоянными. Временные функции существуют только в текущей сессии.
Для удаления постоянных функций используется следующий запрос:
Для удаления временных функций используется следующий запрос:
Hive позволяет создавать временные макросы, которые могут содержать простые выражения. Для удаления макросов используется следующий запрос:
Более широкие возможности по использованию процедур и функций предоставляет HPL/SQL.
