Хранимые процедуры, функции и триггеры
Хранимая процедура — это объект базы данных, представляющий собой набор SQL- инструкций, который компилируется один раз и хранится на сервере.
Хранимая функция отличается от хранимой пороцедуры, тем что хранимая функция всегда возвращает только скаляр(то есть единичное значение), а процедура возвращает набор значений.
Триггер (trigger) — это хранимая процедура, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер. Посмотреть существующие триггеры:
select * from pg_trigger;
PostgreSQL
Есть ли в PostgreSQL хранимые процедуры? В чистом виде нет.
Вместо CREATE RULE в 99% будет правильно использовать CREATE TRIGGER
Пример функции(PL/pgSQL), которая выводит текущую дату
CREATE OR REPLACE FUNCTION getDate() RETURNS text AS $BODY$ BEGIN RETURN CURRENT_TIMESTAMP; END $BODY$ LANGUAGE 'plpgsql' ;
Функция возвращающая много строк
CREATE OR REPLACE FUNCTION g_peer() RETURNS SETOF text AS $BODY$ DECLARE r text; BEGIN FOR r IN SELECT DISTINCT "peer_id" FROM "ratesheets" ORDER BY peer_id LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'
Функция возвращает сведения о времени последнего процесa autovacuum, autoanalyze. Возвращающей столбец записей.
CREATE OR REPLACE FUNCTION sql_last_v_a() RETURNS SETOF record AS' SELECT schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze FROM pg_stat_all_tables ORDER BY schemaname, relname; 'LANGUAGE sql;
MySQL
Триггеры Движок БД MySQL. Поддержка для триггеров включена, начиная с MySQL 5.0.2.
Триггер представляет собой именованный объект базы данных (хранимая процедура), который связан с таблицей, и он будет активизирован, когда специфическое событие INSERT, UPDATE и DELETE (вставка, обновление строки или удаление) происходит для таблицы. Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы.
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
trigger_name — название триггера trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события. trigger_event — Событие: insert — событие возбуждается операторами insert, data load, replace update — событие возбуждается оператором update delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера tbl_name — название таблицы trigger_stmt выражение, которое выполняется при активации триггера
Просмотреть все триггеры в конкретной базе данных. Чтобы вывести все триггеры, определенных для базы данных dbname, нужно выполнить запрос к таблице INFORMATION_SCHEMA.TRIGGERS:
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='dbname';
SHOW TRIGGERS FROM dbname;
Для удаления триггера нужно указывать схему
mysql> DROP TRIGGER dbname.my_trigger;
Пример MySQL триггера
Задача. Нужно после вставки (INSERT) строки в таблицу cdr, обновить (UPDATE) таблицу actions внеся в нее значение id новой записи таблицы cdr.
mysql> DELIMITER // mysql> CREATE TRIGGER `ti_cdr` AFTER INSERT ON `mbillcc`.`cdr` -> FOR EACH ROW -> BEGIN -> UPDATE actions SET state = NEW.id WHERE action = 'cdrrow_end'; -> END// Query OK, 0 ROWS affected (0.00 sec) mysql> DELIMITER ;
NEW — для доступа к новым записям; OLD — для доступа к старым записям
11 Самых Популярных Статей
- ulimit (limits.conf) управление ограничениями ресурсов ОС Linux
- 7 способов сравнения файлов по содержимому в Windows или Linux
- Что такое страны tier 1,2,3 и как правильно выбрать ГЕО для рекламной кампании
- Настройка, использование GitLab CI/CD
- Что означает «> /dev/null 2>&1» или перенаправление STDIN, STDOUT и STDERR?
- Настройка и использование сервера OpenVPN в Linux
- PostgreSQL: создать БД, пользователя, таблицу, установить права
- Виды кодировок символов
- Использование rsync в примерах
- my.cnf примеры конфигурации MySQL, MariaDB
- dig проверка DNS сервера
11 Самых Популярных Обзоров
- ТОП 4 лучших антидетект браузеров в 2023 (Бесплатные & Платные)
- Обзор и отзывы о Namecheap в 2023 году
- Хостинг Zomro (Зомро)
- Обзор браузера Dolphin
- ТОП 3 Проверенных VPN, Прокси, Хостинг VPS Турция в 2023
- Что такое абузоустойчивый хостинг (bulletproof)?
- Обзор и отзывы о 4VPS (FourServer) в 2023 году
- Обзор и отзывы AstroProxy в 2023 году
- Обзор и отзывы о PQ Hosting в 2023 году
- Обзор и отзывы о Hostinger в 2023 году: преимущества и недостатки
- Проверенные VPS / VDS хостинг провайдеры
Разница между функцией и процедурой в Oracle
Основное отличие: функции и процедуры являются подпрограммами Oracle. Основное различие между ними заключается в том, что функции всегда возвращают значение, а процедуры могут или не могут.
Oracle, как мы знаем, является широко используемым языком кодирования. Программа известна как набор кодов и инструкций в технической терминологии или терминологии кодирования. Эти программы состоят из небольших модулей или мини-программ, которые предназначены для выполнения определенных задач. Этим подпрограммам назначаются различные задачи, и они вызываются через вызывающую программу или другую подпрограмму в случае выполнения определенной указанной задачи.
Функции и процедуры — не что иное, как подпрограммы в Oracle. Они используются для выполнения набора конкретных инструкций от пользователя. Например, функция AVG () используется для вычисления среднего значения всех значений, которые предоставляются программе в качестве входных данных. Аналогичным образом, процедуры также используются для таких конкретных задач в программе. Однако поразительное различие между ними заключается в том, что функция всегда возвращает значение, а процедура не всегда возвращает его. Процедура успешно выполняет задачу, но может возвращать или не возвращать значение.
Сравнение между функцией и процедурой:
функция
Процедура
Всегда возвращает значение.
Может или не может вернуть значение.
Функция выполняет конкретную задачу.
Процедура может выполнять несколько задач.
Вызов из операторов SQL
Такой вызов возможен для функций.
Такой вызов не осуществим для процедур.
Функции обычно используются для выполнения вычислений.
Процедуры используются для выполнения бизнес-логики.
Функция не выполняется независимо. Он должен быть частью исполняемого оператора.
Сама процедура представляет собой исполняемый оператор, поэтому она может выполняться независимо.
Процедура или функция?
Подскажите пожалуйста! Что где использовать. отличие их как я понял состоит в том, что функция возвращяет значение и может быть напрямую использован в запросе, а вот с ХП так нельзя.
Ну собственно что где использовать? Если я не собираюсь использовать возвращаемое значение и не буду использовать функцию напрямую в запросе, стоит ли тогда вместо функции, воспользоватся хп?
- Вопрос задан более трёх лет назад
- 1025 просмотров
Комментировать
Решения вопроса 0
Ответы на вопрос 1
Готовые решения — не подаю, но.
Функция — когда надо получить какой-нибудь результат. Например очистка номера телефона от паразитных символов (скобки, дефисы и т.п.)
Процедура — когда надо выполнить какие-либо действия. Как образчик абстрактный документ со строками, разные действия со строками документа (добавление, удаление, изменение) и пересчет документа.
MySQL не самый показательный в этом плане, в других вариантах — функции и процедуры используются активно.
Ответ написан более трёх лет назад
Комментировать
Нравится 1 Комментировать
Ваш ответ на вопрос
Войдите, чтобы написать ответ
- Python
- +1 ещё
Как формировать сырые SQL запросы максимально эффективно?
- 1 подписчик
- 12 часов назад
- 59 просмотров
14) Хранимая процедура и функции
В этом руководстве вы увидите подробное описание того, как создавать и выполнять именованные блоки (процедуры и функции).
Процедуры и функции – это подпрограммы, которые можно создавать и сохранять в базе данных как объекты базы данных. Они могут быть вызваны или переданы внутри других блоков.
Помимо этого, мы рассмотрим основные различия между этими двумя подпрограммами. Также мы собираемся обсудить встроенные функции Oracle.
В этом уроке вы узнаете
- Терминологии в подпрограммах PL / SQL
- Что такое процедура в PL / SQL?
- Что такое функция?
- Сходство между процедурой и функцией
- Процедура против Функция: Ключевые различия
- Встроенные функции в PL / SQL
Терминологии в подпрограммах PL / SQL
Прежде чем мы узнаем о подпрограммах PL / SQL, мы обсудим различные термины, которые являются частью этих подпрограмм. Ниже приведены термины, которые мы собираемся обсудить.
Параметр:
Параметр является переменной или заполнителем любого допустимого типа данных PL / SQL, через который подпрограмма PL / SQL обменивается значениями с основным кодом. Этот параметр позволяет вводить подпрограммы и извлекать из них подпрограммы.
- Эти параметры должны быть определены вместе с подпрограммами во время создания.
- Эти параметры включены в оператор вызова этих подпрограмм для взаимодействия значений с подпрограммами.
- Тип данных параметра в подпрограмме и оператор вызова должны быть одинаковыми.
- Размер типа данных не должен упоминаться во время объявления параметра, так как размер является динамическим для этого типа.
На основании их назначения параметры классифицируются как
- IN параметр
- Выходной параметр
- Параметр IN OUT
IN параметр:
- Этот параметр используется для ввода данных в подпрограммы.
- Это переменная только для чтения внутри подпрограмм. Их значения не могут быть изменены внутри подпрограммы.
- В вызывающем операторе эти параметры могут быть переменной, литеральным значением или выражением, например, это может быть арифметическое выражение типа «5 * 8» или «a / b», где «a» и «b» являются переменными ,
- По умолчанию параметры имеют тип IN.
Выходной параметр:
- Этот параметр используется для получения выходных данных из подпрограмм.
- Это переменная чтения-записи внутри подпрограмм. Их значения могут быть изменены внутри подпрограмм.
- В операторе вызова эти параметры всегда должны быть переменной для хранения значения из текущих подпрограмм.
Параметр IN OUT:
- Этот параметр используется как для ввода, так и для получения выходных данных из подпрограмм.
- Это переменная чтения-записи внутри подпрограмм. Их значения могут быть изменены внутри подпрограмм.
- В операторе вызова эти параметры всегда должны быть переменной для хранения значения из подпрограмм.
Эти типы параметров должны быть упомянуты во время создания подпрограмм.
ВОЗВРАЩЕНИЕ
RETURN – это ключевое слово, которое инструктирует компилятору переключать элемент управления из подпрограммы в оператор вызова. В подпрограмме RETURN просто означает, что элемент управления должен выйти из подпрограммы. Как только контроллер обнаружит ключевое слово RETURN в подпрограмме, код после этого будет пропущен.
Обычно родительский или основной блок вызывает подпрограммы, а затем элемент управления переключается с родительского блока на вызываемые подпрограммы. RETURN в подпрограмме вернет элемент управления обратно в родительский блок. В случае функций оператор RETURN также возвращает значение. Тип данных этого значения всегда упоминается во время объявления функции. Тип данных может иметь любой допустимый тип данных PL / SQL.
Что такое процедура в PL / SQL?
Процедура – это подпрограмма, состоящая из группы операторов PL / SQL. Каждая процедура в Oracle имеет свое уникальное имя, по которому она может быть передана. Этот подпрограммный блок хранится в виде объекта базы данных. Ниже приведены характеристики этого подпрограммного блока.
Примечание. Подпрограмма – это не что иное, как процедура, и ее необходимо создавать вручную в соответствии с требованием. После создания они будут сохранены как объекты базы данных.
- Процедуры – это отдельные блоки программы, которые могут храниться в базе данных.
- Вызвать эти процедуры можно, обратившись к их имени, чтобы выполнить операторы PL / SQL.
- Он в основном используется для выполнения процесса в PL / SQL.
- Он может иметь вложенные блоки или может быть определен и вложен в другие блоки или пакеты.
- Он содержит часть объявления (необязательно), часть исполнения, часть обработки исключений (необязательно).
- Значения могут быть переданы в процедуру или извлечены из процедуры через параметры.
- Эти параметры должны быть включены в оператор вызова.
- Процедура может иметь инструкцию RETURN для возврата элемента управления в вызывающий блок, но она не может возвращать какие-либо значения через инструкцию RETURN.
- Процедуры нельзя вызывать напрямую из операторов SELECT. Они могут быть вызваны из другого блока или через ключевое слово EXEC.
Синтаксис:
CREATE OR REPLACE PROCEDURE ( .. . ) [ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE инструктирует компилятор создать новую процедуру. Ключевое слово «ИЛИ ЗАМЕНИТЬ» дает указание компиляции заменить существующую процедуру (если она есть) на текущую.
- Имя процедуры должно быть уникальным.
- Ключевое слово «IS» будет использоваться, когда процедура вложена в некоторые другие блоки. Если процедура автономна, то будет использоваться «AS». Кроме этого стандарта кодирования, оба имеют одинаковое значение.
Пример 1: создание процедуры и вызов ее с помощью EXEC
В этом примере мы собираемся создать процедуру, которая принимает имя в качестве входных данных и печатает приветственное сообщение в качестве выходных данных. Мы будем использовать команду EXEC для вызова процедуры.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) IS BEGIN dbms_output.put_line (‘Welcome '|| p_name); END; / EXEC welcome_msg (‘Guru99’);
Объяснение кода:
- Строка кода 1 : Создание процедуры с именем ‘welcome_msg’ и с одним параметром ‘p_name’ типа ‘IN’.
- Строка кода 4 : печать приветственного сообщения путем объединения имени входа.
- Процедура скомпилирована успешно.
- Строка кода 7 : Вызов процедуры с использованием команды EXEC с параметром «Guru99». Процедура выполняется, и сообщение распечатывается как «Welcome Guru99».
Что такое функция?
Функции – это отдельная подпрограмма PL / SQL. Подобно процедуре PL / SQL, функции имеют уникальное имя, по которому на них можно ссылаться. Они хранятся в виде объектов базы данных PL / SQL. Ниже приведены некоторые характеристики функций.
- Функции представляют собой отдельный блок, который в основном используется для целей расчета.
- Функция использует ключевое слово RETURN, чтобы вернуть значение, и тип данных этого определяется во время создания.
- Функция должна либо возвращать значение, либо вызывать исключение, т. Е. Возвращение обязательно в функциях.
- Функция без операторов DML может быть вызвана напрямую в запросе SELECT, тогда как функция с операцией DML может быть вызвана только из других блоков PL / SQL.
- Он может иметь вложенные блоки или может быть определен и вложен в другие блоки или пакеты.
- Он содержит часть объявления (необязательно), часть исполнения, часть обработки исключений (необязательно).
- Значения могут быть переданы в функцию или извлечены из процедуры через параметры.
- Эти параметры должны быть включены в оператор вызова.
- Функция также может возвращать значение через параметры OUT, кроме использования RETURN.
- Поскольку он всегда будет возвращать значение, в операторе вызова он всегда сопровождается оператором присваивания для заполнения переменных.
Синтаксис
CREATE OR REPLACE FUNCTION ( ) RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION указывает компилятору создать новую функцию. Ключевое слово «ИЛИ ЗАМЕНИТЬ» указывает компилятору заменить существующую функцию (если есть) на текущую.
- Имя функции должно быть уникальным.
- Возвращаемый тип данных должен быть упомянут.
- Ключевое слово «IS» будет использоваться, когда процедура вложена в некоторые другие блоки. Если процедура автономна, то будет использоваться «AS». Кроме этого стандарта кодирования, оба имеют одинаковое значение.
Пример 1: Создание функции и вызов ее с помощью анонимного блока
В этой программе мы собираемся создать функцию, которая принимает имя в качестве входных данных и возвращает приветственное сообщение в качестве выходных данных. Мы собираемся использовать анонимный блок и оператор select для вызова функции.
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2 IS BEGIN RETURN (‘Welcome ‘|| p_name); END; / DECLARE lv_msg VARCHAR2(250); BEGIN lv_msg := welcome_msg_func (‘Guru99’); dbms_output.put_line(lv_msg); END; SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
Объяснение кода:
- Строка кода 1 : Создание функции с именем ‘welcome_msg_func’ и одним параметром ‘p_name’ типа ‘IN’.
- Строка кода 2 : объявление типа возвращаемого значения как VARCHAR2
- Строка кода 5 : Возвращает объединенное значение «Welcome» и значение параметра.
- Строка кода 8 : анонимный блок для вызова вышеуказанной функции.
- Строка кода 9 : Объявление переменной с типом данных таким же, как и у возвращаемого типа данных функции.
- Строка кода 11 : вызов функции и заполнение возвращаемого значения в переменной ‘lv_msg’.
- Строка кода 12 : печать значения переменной. Вывод, который вы получите здесь: «Welcome Guru99»
- Строка кода 14 : вызов той же функции через оператор SELECT. Возвращаемое значение напрямую направляется на стандартный вывод.
Сходство между процедурой и функцией
- Оба могут быть вызваны из других блоков PL / SQL.
- Если исключение, возникшее в подпрограмме, не обрабатывается в разделе обработки исключений подпрограммы, оно будет распространяться на вызывающий блок.
- Оба могут иметь столько параметров, сколько требуется.
- Оба обрабатываются как объекты базы данных в PL / SQL.
Процедура против Функция: Ключевые различия
- Используется в основном для выполнения определенного процесса
- Используется в основном для выполнения некоторых расчетов
- Невозможно позвонить в операторе SELECT
- Функция, которая не содержит операторов DML, может быть вызвана в операторе SELECT.
- Используйте параметр OUT, чтобы вернуть значение
- Используйте RETURN, чтобы вернуть значение
- Не обязательно возвращать значение
- Обязательно вернуть значение
- RETURN просто выйдет из управления из подпрограммы.
- RETURN выйдет из элемента управления из подпрограммы, а также вернет значение
- Возвращаемый тип данных не будет указан при создании
- Возвращаемый тип данных является обязательным во время создания
Встроенные функции в PL / SQL
PL / SQL содержит различные встроенные функции для работы со строками и датой. Здесь мы рассмотрим часто используемые функции и их использование.
Функции преобразования
Эти встроенные функции используются для преобразования одного типа данных в другой тип данных.
Выберите TO_NUMBER (‘1234,45’, ‘9,999,99’) из двойного;
Строковые функции
Это функции, которые используются в типе символов.
- текст – основная строка
- строка – текст, который необходимо найти
- start – начальная позиция поиска (необязательно)
- соответствие – вхождение искомой строки (необязательно)
- текст – основная строка
- старт – стартовая позиция
- длина – длина, которая будет подстрокой
Функции даты
Это функции, которые используются для манипулирования датами.
Резюме
В этой главе мы узнали следующее.
- Как создать процедуру и разные способы ее вызова
- Как создать функцию и как ее вызвать
- Сходства и различия между процедурой и функцией
- Параметры и общие термины RETURN в подпрограммах PL / SQL
- Общие встроенные функции в Oracle PL / SQL