Что такое код alter procedure


Содержание

Sql error: «CREATE/ALTER PROCEDURE» должен быть первым оператором в пакете запросов?

Я пытаюсь создать sql script, но получаю сообщение об ошибке:

‘CREATE/ALTER PROCEDURE’ должен быть первым оператором в запросе пакетный??

Запустите свой оператор в следующей форме:

Обратите внимание на разделитель партий GO после DROP PROCEDURE

Сообщение об ошибке, которое вы получаете, является правильным. Вы можете завершить пакет (и запустить другой) с помощью ключевого слова GO.

Поместите GO прямо перед оператором Create procedure. Вывод GO должен быть на отдельной строке.

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

Эта логика позволила бы мне создать что-то только в том случае, если оно НЕ существует, но, к моему большому разочарованию, SQL Server также предотвращает это.

Я легко обойду эту проблему следующим образом:

Пропустив команду create proc в виде строки и поместив ее в инструкцию exec, мы обходим глупое правило, которое не позволяет делать это в первую очередь.

Что такое код alter procedure

Posted via ActualForum NNTP Server 1.3

20 ��� 06, 11:38����[2583421] �������� | ���������� �������� ����������
Re: ALTER Procedure [new]
egghead
����������. ������ ��� ��������� ALTER. ����� ����� ��� ��������� ���������� ���������. � ���� ������ ������ ���� ��������. ����� ����� ���-�� �������� ����� ��������� �������� � ����? ��� ��������� ����������. �������

����� � ���������� set nocount on ���� ������� ������, �� ������ ����� ��������� ���� ���� ����������. 20 ��� 06, 11:41����[2583448] �������� | ���������� �������� ����������
Re: ALTER Procedure [new]

Выполнение процедуры

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE :

Удаление процедуры

Для удаления процедуры применяется команда DROP PROCEDURE :

/привет/мир/etc

Периодические заметки о программировании

среда, 12 августа 2015 г.

Трассировка и профилирование PL/SQL в Oracle 11g, часть I

Что такое трассировка (tracing), профилирование (profiling) и чем они отличаются друг от друга?

Трассировка — это прогон программы c регистрацией того, какие строки (команды) каких модулей (подпрограмм) в какой последовательности были выполнены.

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

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

Я рассмотрю средства трассировки и профилирования, которые Oracle 11g предоставляет разработчику PL/SQL. И начну с трассировки.

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

Если в БД трассировка ни разу не выполнялась, то трассировочные таблицы нужно вначале создать. Для этого нужно выполнить скрипт $ORACLE_HOME/rdbms/admin/tracetab.sql как пользователь SYS . Скрипт создает две таблицы и сиквенс для генерирования уникальных номеров трассировок:

PLSQL_TRACE_RUNS Трассировки.
PLSQL_TRACE_EVENTS Зарегистрированные в ходе трассировки события.
PLSQL_TRACE_RUNNUMBER Генератор номеров трассировок.

Предоставлю доступ к трассировочным таблицам всем пользователям БД:

Пользователю AY , под которым буду выполнять трассировку, предоставлю право удалять трассировочные данные:

Трассировка включается процедурой DBMS_TRACE.SET_PLSQL_TRACE и отключается процедурой DBMS_TRACE.CLEAR_PLSQL_TRACE .

Процедура DBMS_TRACE.SET_PLSQL_TRACE принимает в качестве параметра уровень трассировки, задаваемый с помощью констант пакета DBMS_TRACE :

TRACE_ALL_CALLS Трассировать все вызовы.
TRACE_ALL_EXCEPTIONS Трассировать все исключения.
TRACE_ALL_SQL Трассировать все команды SQL.
TRACE_ALL_LINES Трассировать все строки.
TRACE_ENABLED_CALLS Трассировать активированные вызовы.
TRACE_ENABLED_EXCEPTIONS Трассировать активированные исключения.
TRACE_ENABLED_SQL Трассировать активированные команды SQL.
TRACE_ENABLED_LINES Трассировать активированные строки.
NO_TRACE_ADMINISTRATIVE Не трассировать события виртуальной машины PL/SQL.
NO_TRACE_HANDLED_EXCEPTIONS Не трассировать обрабатываемые исключения.

Под активированными (enabled) событиями понимаются события, связанные с выполнением кода только тех процедур, функций и пакетов, которые были скомпилированы для отладки. Так, уровень TRACE_ALL_CALLS включает трассировку всех вызовов в любом выполняемом коде PL/SQL, а уровень TRACE_ENABLED_CALLS — трассировку только вызовов в коде модулей, скомпилированных для отладки:

Другой способ откомпилировать PL/SQL модуль для отладки — использовать параметр PLSQL_DEBUG на уровне системы ( alter system set plsql_debug=true ), сеанса ( alter session set plsql_debug=true ) или непосредственно при компиляции:

Однако, параметр PLSQL_DEBUG в версии Oracle 11g объявлен устаревшим, поэтому первый способ предпочтительнее (к тому же, компиляция с PLSQL_DEBUG=TRUE в версии 11g генерирует предупреждения о понижении уровня оптимизации компилируемого кода.)

Посмотрим, как изменяются свойства модуля PL/SQL, доступные через вью USER_PLSQL_SETTINGS , при компиляции с опцией DEBUG и без нее. Создам процедуру HELLO , содержащую команду SQL, вызов другой процедуры, и возбуждающую исключение во время выполнения — это понадобится для экспериментов с трассировкой специфических событий:

Разобравшись с тем, как активировать код PL/SQL для целей трассировки, вернемся собственно к трассировке.

В качестве параметра для DBMS_TRACE.SET_PLSQL_TRACE можно использовать как отдельную константу пакета DBMS_TRACE , так и сумму нескольких констант. В последнем случае включается трассировка всех событий, соответствующих просуммированным константам. Например, DBMS_TRACE.TRACE_ALL_CALLS + DBMS_TRACE.TRACE_ENABLED_SQL включит трассировку всех вызовов и только активированных команд SQL.

Перехожу к экспериментам. Для начала, оттрассирую все выполняемые строки процедуры HELLO и изучу результат:

В таблице PLSQL_TRACE_RUNS найду номер только что выполненной трассировки и по этому номеру выберу зарегистрированные события из PLSQL_TRACE_EVENTS :

Очень много данных! В трассировочных таблицах зарегистрированы события, связанные не только с выполнением процедуры HELLO , но также

  • с выполнением кода пакета DBMS_TRACE , с помощью которого я включал и отключал трассировку,
  • c запуском и остановкой виртуальной машины PL/SQL,
  • с управлением транзакциями при помощи пакета DBMS_TRANSACTION .

Хочется сосредоточиться на работе HELLO и исключить сбор данных, не связанных с выполнением этой процедуры. Воспользуюсь константой DBMS_TRACE.NO_TRACE_ADMINISTRATIVE , чтобы отключить регистрацию лишних событий:


Исчезли запуски и остановы виртуальной машины PL/SQL, но все еще много лишних событий, не относящихся к выполнению кода процедуры HELLO . Попробую трассировать только активированные события, для чего откомпилирую процедуру HELLO с опцией DEBUG :

Отлично! Были зарегистрированы только выполнения строк процедуры HELLO и ничего кроме. А следующий запрос покажет строки процедуры HELLO , связанные с зарегистрированными событиями:

Достаточно наглядно. Только строка 2 процедуры HELLO не была выполнена — в этой строке объявление переменной a pls_integer; . Можно сделать вывод, что данное объявление переменной не является исполняемой командой, а память под переменную выделяется в ходе загрузки программы в виртуальную машину PL/SQL.

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

Проделаю еще один эксперимент с трассировкой. На этот раз я хочу видеть только активированные вызовы и выполнения команд SQL.

А теперь — с исходным кодом строк процедуры HELLO :

Что ж, получен ожидаемый результат.

Помимо уже упомянутых констант, в пакете DBMS_TRACE имеются и другие, предназначенные для настройки уровня трассировки. Кроме того, имеются «управляющие» константы, которые также могут передаваться при вызове DBMS_TRACE.SET_PLSQL_TRACE в качестве параметра:

Константа Описание Альтернатива использованию константы
TRACE_PAUSE Приостановить трассировку, не меняя уровень трассировки. DBMS_TRACE.PAUSE_PLSQL_TRACE
TRACE_RESUME Возобновить приостановленную трассировку. DBMS_TRACE.RESUME_PLSQL_TRACE
TRACE_STOP Остановить трассировку, очистив уровень трассировки. DBMS_TRACE.CLEAR_PLSQL_TRACE
TRACE_LIMIT Ограничить количество событий, хранимых в трассировочных таблицах, 8192 последними событиями. DBMS_TRACE.LIMIT_PLSQL_TRACE

Константы TRACE_PAUSE , TRACE_RESUME , TRACE_STOP нельзя комбинировать с другими константами при вызове DBMS_TRACE.SET_PLSQL_TRACE .

Как видно из таблицы, использованию «управляющих» констант с DBMS_TRACE.SET_PLSQL_TRACE имеется альтернатива — вызов соответствующей процедуры пакета DBMS_TRACE , приводящий к такому же результату: приостановка, возобновление и остановка трассировки.

Чтобы узнать больше о возможностях трассировки, рекомендую просмотреть спецификацию пакета DBMS_TRACE — в ней больше функций и констант, чем описано в официальном справочнике Database PL/SQL Packages and Types Reference для Oracle 11gR2.

В заключение, очищу трассировочные таблицы и удалю процедуру HELLO :

Во второй части статьи будет продемонстрирована работа с иерархическим профайлером — главным средством профлилирования программ PL/SQL в СУБД Oracle 11g.

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

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

Общие сведения

Итак, хранимые процедуры в SQL — это аналог функций в других языках программирования. Хранимые процедуры могут выполнять действия над данными автоматически: вывод данных, удаление, изменение — то, что мы делали в прошлых уроках вручную.

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

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

Хранимые процедуры в СУБД MySQL можно создать несколькими способами, мы кратко опишем 2 способа через утилиту phpmyadmin:

    Через консоль SQL запросов

Также как и все запросы SELECT, INSERT и т.д код создания хранимых процедур можно прописывать на вкладке SQL.

Через специальную вкладку процедуры

Второй способ возможен тогда, когда мы выбрали определенную БД, и конкретно для нее появляется вкладка процедур, как на скриншоте ниже.

Далее в этой вкладке следует нажать кнопку «Добавить процедуру».

Итак, где создавать процедуры описали, а что конкретно прописывать пока непонятно. Как раз к этому и перейдем.

Создание процедур в SQL

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

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

Напомню, что мы работаем с уже созданной БД и ее структуру с данными можно посмотреть по этим ссылкам на предыдущие уроки:


Итак, весь код создания процедуры приведем сразу:

Мы используем 1 способ создания процедуры — через консоль SQL для нашей базы данных. Поясним некоторые моменты кода выше:

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

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

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

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

Очевидно, для использования процедуры, ее следует вызвать и передать входные аргументы, если они требуются. Вызов хранимой процедуры в SQL производится с помощью оператора CALL. Вызов созданной выше процедуры будет произведен следующим образом:

Таким образом, в выводе получим список покупателей, проживающих в Москве.

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

Итак, хранимые процедуры это достаточно мощный инструмент. И в SQL хранимые процедуры также могут использовать различные операторы, вложенные запросы и т.д. То есть, все, что мы изучали в предыдущих статьях по SQL.

Также, существуют еще особенности хранимых процедур. В хранимых процедурах есть возможность использовать условные операторы и циклы, такие как IF-ELSE, CASE и WHILE. Далее, приведем пример использования конструкции CASE.

Создать хранимую процедуру, которая выведет продавцов, оформивших заказы, по диапазонам. Если аргумент имеет значение «Маленькая суммы», то диапазон продаж от 0 до 1000, «Средние суммы» — от 1000 до 1500, «Большие суммы» — свыше 1500.

Вызвать созданную процедуру можно с тремя различными аргументами:

Соответственно, в выводе будут разные данные в зависимости от аргумента.

Примеры

1. Создать хранимую процедуру, которая определяет лучшего продавца по суммарному значению продаж за период дат, полученных в аргументах.

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

Заключение

Итак, мы познакомились с хранимыми процедурами в SQL. Очевидно, что примеров на их использование можно придумать очень много. Конкретно эта статья посвящена созданию и освоению базовых принципов в работе с хранимыми процедурами в SQL программировании.

ALTER PROCEDURE

English-Russian SQL Server dictionary . 2013 .

Смотреть что такое «ALTER PROCEDURE» в других словарях:

Civil procedure in Canada — For rules of civil procedure by country, see civil procedure. In Canada the rules of civil procedure are administered by each jurisdiction (federal and each province) and thus each has its own set of rules. Most provinces base their civil… … Wikipedia

adversary procedure — In Anglo American law, the principal method of offering ev >Universalium

Stored procedure — Хранимая процедура объект базы данных, представляющий собой набор DDL, так и Содержание 1 Реализация хранимых процедур 2 Назначение и преимущества хранимых процедур[2] … Википедия

goto — This article is about the programming statement. For other uses, see Goto (disambiguation). goto is a statement found in many computer programming languages. It is a combination of the English words go and to. It performs a one way transfer of… … Wikipedia

mental disorder — Any illness with a psychological origin, manifested either in symptoms of emotional distress or in abnormal behaviour. Most mental disorders can be broadly >Universalium

Life Sciences — ▪ 2009 Introduction Zoology In 2008 several zoological studies prov >Universalium

Corneal tattooing — is the practice of tattooing the cornea of the human eye. Reasons for this practice include the improvement of cosmetic appearance and the improvement of sight. Many different methods and procedures exist today, and there are varying opinions… … Wikipedia

Labioplastik — Verkleinerung der inneren Schamlippen: vor (links) und nach (rechts) der Operation Als Labioplastik (synonym: Labioplastie, Schamlippenplastik, Schamlippenkorrektur) werden Operationen der Plastischen Chirurgie zur Reduzierung, Modifizierung,… … Deutsch Wikipedia

Article One of the United States Constitution — United States of America This article is part of the series: United States Constitution Original text of the Constitution Preamble Articles of the Constitution I … Wikipedia

France — /frans, frahns/; Fr. /frddahonns/, n. 1. Anatole /ann nann tawl /, (Jacques Anatole Thibault), 1844 1924, French novelist and essayist: Nobel prize 1921. 2. a republic in W Europe. 58,470,421; 212,736 sq. mi. (550,985 sq. km). Cap.: Paris. 3.… … Universalium

Процедура создана с CREATE PROCEDURE Defaults с ALTER PROCEDURE


У меня есть SQL-скрипт, который создает процедуру, которая выглядит следующим образом:

И это создает процедуру, которая выглядит следующим образом:

Моя проблема заключается в том, что созданная процедура не создает таблицу, но когда я удалить строку

Это, очевидно, работает отлично.

Как я могу создать сценарий, который производит процедуру без этой линии?

Похоже, что вы по сценарию из хранимой процедуры somehow— возможно в SQL Server Management Studio (SSMS), щелкнув правой кнопкой мыши прок в окне обозревателя объектов и нажать кнопку «Изменить». Когда вы сделаете это, SSMS генерирует скрипт для вас, который изменит существующую хранимую процедуру.

Операторы DDL ниже создают процедуру, но они не являются частью процедуры:

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

Сам код хранимой процедуры еще есть:

Обратите внимание, что если эта хранимая процедура выполняется в два раза, это не получится, потому что таблица MyTable будет уже существовать. Чтобы избежать этой ошибки, процедура может первым проверить, существует ли таблица:

Введение в базы данных

Алексей Федоров, Наталия Елманова

В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.

В первой статье данного цикла, опубликованной в № 3’2000 нашего журнала, мы отмечали, что большинство современных серверных СУБД поддерживают представления, триггеры и хранимые процедуры. Представления также поддерживаются и многими настольными СУБД, например Access, dBase, Clipper.

Следует отметить, что триггеры и хранимые процедуры обычно пишутся на языках программирования, представляющих собой процедурные расширения языка SQL. Эти расширения содержат операторы, позволяющие описывать алгоритмы, например do…while, if…then…else, отсутствующие в самом языке SQL (если вы помните, SQL — непроцедурный язык, и на нем можно сформулировать задание, но нельзя описывать алгоритмы его выполнения). В отличие от языка SQL, подчиняющегося стандарту, его процедурные расширения никак не стандартизованы, и разные СУБД используют разные синтаксические конструкции для реализации одних и тех же алгоритмических конструкций, но обсуждение различий в синтаксисе расширений SQL для разных СУБД выходит за рамки данной статьи.

Для иллюстрации того, как можно использовать представления, триггеры и хранимые процедуры, мы выбрали Microsoft SQL Server 7.0 и базу данных NorthWind, входящую в комплект поставки этой СУБД.

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

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

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

Представления

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

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

Ниже перечислены основные характеристики представлений:

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

Для создания представления мы можем использовать SQL-предложение CREATE VIEW, для его модификации — предложение ALTER VIEW, а для удаления его — предложение DROP VIEW.

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

Предложение CREATE VIEW

Синтаксис предложения для создания представления напоминает SQL-предложение SELECT с несколькими дополнительными ключевыми словами. Ниже приведен его упрощенный синтаксис:

Аргумент view_name указывает на имя представления. Ключевое слово [WITH ENCRYPTION], используемое в Microsoft SQL Server, позволяет скрыть исходный текст предложения CREATE VIEW в таблице syscomments.

Ключевое слово AS указывает, какой запрос SELECT реально будет выполняться при обращении к представлению. Обратите внимание на то, что этот запрос не может содержать ключевых слов ORDER BY, COMPUTE или COMPUTE BY, INTO и не может ссылаться на временную таблицу.

Для модификации созданного ранее представления следует использовать предложение ALTER VIEW, кратко описанное в следующем разделе.

Предложение ALTER VIEW

Предложение ALTER VIEW имеет тот же синтаксис, что и предложение CREATE VIEW. Его основное назначение — внести изменения в уже имеющееся представление.


Если представление нам больше не нужно, мы можем удалить его с помощью оператора DROP VIEW, описанного ниже.

Предложение DROP VIEW

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

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

Создание и использование представлений

Предложение CREATE VIEW используется для создания представлений, позволяющих извлекать данные, удовлетворяющие определенным требованиям. Представление создается в текущей базе данных и хранится как ее отдельный объект.

Наилучший способ создать представление — создать запрос SELECT и, проверив его, добавить недостающую часть предложения CREATE VIEW. Давайте рассмотрим исходный текст представления Products by Category в базе данных NorthWind (листинг 1).

Первая строка, выделенная жирным шрифтом, представляет собой то, чем отличается SQL-предложение для создания представления от обычного запроса SELECT, выполняющего работу по выбору данных. Предложение SELECT, содержащееся в этом представлении, выбирает поля из двух таблиц — поле CategoryName из таблицы CATEGORIES и поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued из таблицы PRODUCTS. После этого данные двух таблиц связываются по полю CategoryID, и только те продукты, которые еще имеются на складе (см. критерий после ключевого слова WHERE), включаются в результирующий набор данных. Результат обращения к этому представлению показан на рис. 1.

Теперь давайте создадим представление, показывающее все территории восточного региона. Это представление базируется на следующем запросе (листинг 2).

Убедившись в том, что предложение SELECT возвращает результаты, которые нам нужны, мы добавляем оператор CREATE VIEW и присваиваем создаваемому представлению имя EASTTERR (листинг 3).

Вместо создания текста представления вручную можно использовать визуальные инструменты, обычно входящие в состав СУБД. На рис. 2 показано, как то же самое представление может быть создано с помощью инструмента View Designer, который является составной частью Enterprise Manager, входящего в Microsoft SQL Server.

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

Прежде чем мы закончим краткий обзор представлений, поговорим немного о том, как получить дополнительную информацию о них. В Microsoft SQL Server 7.0 мы можем использовать следующие системные хранимые процедуры:

  • для получения сведений о представлении можно использовать системную хранимую процедуру sp_help. Например, sp_help EastTerr вернет сведения о только что созданном представлении;
  • для получения исходного текста представления можно использовать хранимую процедуру sp_helptext;
  • для того чтобы найти список таблиц, от которого зависит представление, можно использовать системную хранимую процедуру sp_depends;
  • для переименования представления можно использовать системную хранимую процедуру sp_rename.

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

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

Хранимая процедура — это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Хранимые процедуры могут принимать и возвращать параметры. Когда пользователь создает хранимую процедуру, сервер компилирует ее и помещает в разделяемый кэш, после чего скомпилированный код может быть применен несколькими пользователями. Когда приложение использует хранимую процедуру, оно передает ей параметры, если таковые требуются, и сервер выполняет процедуру без перекомпиляции.

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

Хранимые процедуры автоматически перекомпилируются, если с объектами, на которые они влияют, произведены какие-либо изменения; иными словами, они всегда актуальны. Как уже было сказано выше, хранимые процедуры могут принимать параметры, что позволяет разным приложениям использовать одну и ту же процедуру, применяя различные наборы входных данных.

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

Для создания, изменения и удаления процедур существуют специальные SQL-предложения — CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Мы рассмотрим их в следующем разделе.

Предложение CREATE PROCEDURE

Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:

Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.

Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.

Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию — это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, [] и [^]).

Ключевое слово OUTPUT показывает, что это возвращаемый параметр.

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

Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст — в таблице syscomments.

Для изменения созданной ранее хранимой процедуры следует использовать предложение ALTER PROCEDURE, кратко описанное в следующем разделе.

Предложение ALTER PROCEDURE

Это SQL-предложение применяется для изменения уже существующей хранимой процедуры. Предложение ALTER PROCEDURE имеет тот же синтаксис, что и предложение CREATE PROCEDURE. Один из примеров, когда нужно изменить хранимую процедуру, — зашифровать ее исходный текст после того, как процедура отлажена, с целью скрыть ее исходный текст от других пользователей.


Если хранимая процедура больше не нужна, мы можем удалить ее с помощью предложения DROP PROCEDURE, которое будет обсуждаться ниже.

Предложение DROP PROCEDURE

Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент — имя удаляемой процедуры.

При удалении хранимой процедуры сведения о ней удаляются из системных таблиц sysobjects и syscomments.

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

В разделе, посвященном представлениям, мы обращали внимание на то, что было бы удобно, если бы мы могли передать в представление параметр, содержащий значение RegionID для выбора одного из четырех регионов в базе данных NorthWind. Давайте еще раз рассмотрим запрос, возвращающий список территорий региона:

Чтобы выбрать другой регион, нам нужно изменить условие в предложении WHERE в последней строке запроса. Следовательно, если мы используем переменную (назовем ее RegID), мы сможем выбрать один из четырех регионов без изменения других частей запроса.

В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:

Обратите внимание на то, что мы оставили почти весь текст запроса SELECT нетронутым (он выделен курсивом) и только добавили предложение CREATE PROCEDURE с именем вновь созданной хранимой процедуры (в первой строке), объявление параметра (во второй строке) и ключевое слово AS, указывающее на начало предложений, реально выполняющих действия.

Результат выполнения созданной процедуры в SQL Server Query Analyzer для Reg >

Очевидно, что мы можем применять хранимые процедуры не только для реализации расширенных версий представлений или «интеллектуальных» запросов SELECT. Хранимые процедуры предоставляют механизмы, позволяющие автоматизировать многие рутинные задачи.

В Microsoft SQL Server 7.0 мы также можем использовать системные хранимые процедуры для работы с обычными хранимыми процедурами:

  • sp_stored_procedures — показывает список хранимых процедур;
  • sp_helptext — показывает исходный текст хранимой процедуры;
  • sp_depends — показывает сведения о зависимостях хранимых процедур;
  • sp_procoption — устанавливает опции хранимых процедур или устанавливает их;
  • sp_recompile — перекомпилирует процедуру в момент ее следующего вызова;
  • sp_rename — меняет имя процедуры.

Системные хранимые процедуры

Поскольку мы говорим о Microsoft SQL Server, следует отметить огромное количество системных хранимых процедур, реализованных в нем. Имена системных хранимых процедур начинаются с SP_ или XP_ и хранятся в базе данных master. Выше мы уже описывали некоторые из часто используемых системных хранимых процедур.

В табл. 1 приведен краткий список системных хранимых процедур.

Системные хранимые процедуры можно использовать в клиентских приложениях. Для этого следует обратиться к базе данных master и выбрать имя соответствующей процедуры.

Последний раздел в этой статье посвящен еще одному типу объектов в базах данных — триггерам.

Триггеры

В первой статье данного цикла мы кратко описывали, что такое триггеры и как они используются в базах данных. Дадим определение триггера. Tриггер — это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. В зависимости от того, какое из событий, связанных с изменением данных, инициирует запуск триггера, он называется insert trigger, delete trigger или update trigger.

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

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

Поскольку триггер вызывается автоматически самой СУБД (в нашем случае Microsoft SQL Server), его нельзя вызвать из клиентского приложения.

Триггеры могут опосредованно вызывать другие триггеры. Например, если триггер, выполняющийся в данный момент, содержит код, модифицирующий другую таблицу, имеющую собственный триггер, последний будет запущен. В свою очредь, он может вызвать следующий триггер и так далее. Такая последовательность тригеров называется термином nested triggers. Microsoft SQL Server поддерживает до 32 уровней вложения таких триггеров.

Как и многие другие объекты баз данных, триггеры создаются с помощью предложения CREATE. Чтобы изменить триггер, мы используем предложение ALTER, а для удаления триггера — предложение DROP. В последующих разделах мы рассмотрим синтаксис этих предложений и затем создадим триггер, который может быть использован для записи сведений о мдификациях данных.

Предложение CREATE TRIGGER

Это предложение создает новый триггер для определеной таблицы для любого из предложений INSERT, DELETE или UPDATE. Ниже приведен упрощенный синтаксис такого предложения:

Аргумент trigger_name указывает имя триггера. Это имя должно быть уникально для базы данных. Аргумент table_name — имя таблицы, при модификации которой выполняется данный триггер. Таблица должна быть «настоящей» — нельзя указать имя представления вместо таблицы.

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

Необязательное ключевое слово [WITH ENCRYPTION] может быть использовано для сокрытия исходного кода триггера — в этом случае триггер не будет виден в таблице syscomments.

Ключевое слово AS указывает на действие, которое должен выполнить триггер, в виде любого количества SQL-предложений и предложений на процедурном расширении SQL, характерном для данного сервера. В них можно выполнять любые SQL-предложения, кроме перечисленных в табл. 2.

Обратите внимание на то, что триггеры не должны возвращать пользователю данные.


В предложении CREATE TRIGGER можно использовать две специальные таблицы. Например, таблицы deleted и inserted имеют ту же структуру, что и таблица, для которой определен триггер, и содержат старое и новое значения записей, измененных пользователем. Например, мы можем использовать следующее SQL-предложение для поиска удаленных записей:

SELECT * FROM deleted

В табл. 3 показано содержимое таблиц deleted и inserted для всех возможных изменений данных.

Для изменения имеющегося триггера следует использовать предложение ALTER TRIGGER. Мы поговорим о нем в следующем разделе.

Предложение ALTER TRIGGER

Данное предложение имеет тот же синтаксис, что и предложение CREATE TRIGGER, которое мы обсудим выше. Это предложение применяется для изменения текста имеющегося триггера. Например, если мы решили, что какой-либо триггер, определенный для всех трех типов модификаций данных, теперь не должен выполняться, когда записи удаляются, нам следует использовать тот же самый код, с помощью которого мы создавали триггер, но удалить ключевое слово UPDATE в предложении FOR.

Если триггер больше не нужен, мы можем удалить его из базы данных. Для этого применяется предложение DROP TRIGGER, которое мы рассмотрим ниже.

Предложение DROP TRIGGER

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

Создание и использование триггеров

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

Для начала нам нужно добавить к таблице два новых поля, в которых будут содержаться эти сведения. Назовем их UpdatedBy (имя менеджера, обновившего запись последним) и UpdatedWhen (время, когда была изменена запись). Затем создадим триггер с именем KeepTrack. Вот его код:

Как видно из исходного текста триггера, он выполняется после каждой операции INSERT и UPDATE в таблице Customers. Этот триггер будет сохранять имя менеджера (пользователя базы данных) в поле Customers.UpdatedBy и дату и время изменения — в поле Customers.UpdatedWhen. Эти данные извлекаются из временной таблицы inserted.

Как видим, этот триггер позволяет следить за изменениями и вставкой новых записей в таблице.

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

Заключение

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

  • Представление — это виртуальная таблица, обычно создаваемая как подмножество столбцов одной или нескольких таблиц. Для создания представления применяется предложение CREATE VIEW, для модификации — предложение ALTER VIEW, а для удаления — предложение DROP VIEW.
  • Хранимая процедура — это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения — ALTER PROCEDURE, а для удаления — DROP PROCEDURE.
  • Tриггер — это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления — предложение DROP TRIGGER.

Предупреждения при компиляции в PL/SQL

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

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

Пример

Очень полезное предупреждением компилятора PLW-06002 сообщает о наличии недостижимого кода. Рассмотрим следующую программу. Так как переменная salary инициализируется значением 10 000, условная команда всегда будет отправлять меня на строку 9. Строка 7 выполняться не будет:

Если откомпилировать этот код в любой версии до Oracle Database 10g , компилятор просто сообщит о том, что процедура создана. Но если включить предупреждения компиляции в сеансе этой или более поздней версии, то при попытке откомпилировать процедуру будет получен следующий ответ от компилятора:

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

Включение предупреждений компилятора PL/SQL

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

  • Критичные — ситуации, которые могут привести к неожиданному поведению или получению неверных результатов (как, например, проблемы с псевдонимами параметров).
  • Производительные — ситуации, способные вызвать проблемы с производительностью (например, указание значения VARCHAR2 для столбца NUMBER в команде UPDATE ).
  • Информационные — ситуации, не влияющие на производительность или правильность выполнения кода, но которые стоит изменить ради того, чтобы упростить сопровождение.

Oracle позволяет включать и отключать предупреждения конкретной категории, всех категорий и даже конкретные предупреждения. Для этого используется команда ALTER DDL или встроенный пакет DBMS_WARNING .

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

А следующая команда, например, включает предупреждения в вашей системе для всех категорий:

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


Чтобы включить предупреждения в сеансе только для критичных проблем, введите следующую команду:

А для изменения настройки предупреждений компилятора для конкретной, уже откомпилированной программы вводится команда следующего вида:

Обязательно включите секцию REUSE SETTINGS , чтобы команда ALTER не влияла на все остальные настройки (например, уровень оптимизации).

Объединяя разные параметры, можно уточнять настройки с очень высоким уровнем детализации. Допустим, я хочу знать обо всех проблемах, относящихся к производительности, на данный момент не желаю отвлекаться на серверные проблемы, а предупреждение PLW-05005 (выход из функции без RETURN ) должно рассматриваться как ошибка компиляции. Для этого вводится следующая команда:

Особенно полезна возможность интерпретации предупреждений как ошибок. Возьмем предупреждение PLW-05005; если оставить его без внимания при компиляции функции no_return (см. ниже), программа откомпилируется, и я смогу использовать ее в приложении:

Если теперь изменить интерпретацию ошибки приведенной выше командой ALTER SESSION и перекомпилировать no_return , компилятор немедленно остановит попытку:

Кстати говоря, настройки также можно изменить только для конкретной программы и пометить предупреждение как ошибку командой следующего вида:

Во всех этих разновидностях команды ALTER ключевое слово ALL может использоваться как простое и удобное обозначение всех категорий предупреждений:

Oracle также предоставляет пакет DBMS_WARNING со сходными возможностями по установке и изменению параметров компиляции через PL/SQL API . В отличие от команды ALTER, DBMS_WARNING позволяет вносить изменения в конфигурацию тех предупреждений, которые вас интересуют, оставляя другие без изменений. Также после завершения работы можно легко восстановить исходные настройки.

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

Некоторые полезные предупреждения PL/SQL

В следующих разделах я представлю небольшую подборку предупреждений, реализованных Oracle , — с примерами кода, для которого они выдаются, и описаниями особенно интересного поведения.

Чтобы просмотреть полный список предупреждений для любой конкретной версии Oracle , найдите раздел PLW в книге «Error Messages» документации Oracle.

PLW-05000: несовпадение в NOCOPY между спецификацией и телом

Рекомендация NOCOPY сообщает базе данных Oracle, что вы, если это возможно, предпочли бы не создавать копии аргументов IN OUT . Отказ от копирования может повысить производительность программ, передающих большие структуры данных — например, коллекции или CLOB .

Рекомендация NOCOPY должна быть включена как в спецификацию, так и в тело программы (актуально для пакетов и объектных типов). Если рекомендация не присутствует в обоих местах, база данных применяет настройку, указанную в спецификации.

Пример кода, генерирующего это предупреждение:

Предупреждения компилятора отображаются в следующем виде:

PLW-05001: предыдущее использование строки противоречит этому использованию

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

Возможно, вы скажете: «Да, я видел эту ошибку, но это именно ошибка компиляции, а не предупреждение». Собственно, вы правы — следующая программа не откомпилируется:

Компилятор выдает ошибку PLS-00371 (в разделе объявлений разрешено не более одного объявления ‘A’).

Почему же для этой ситуации создано предупреждение? Попробуем удалить присваивание переменной с именем a:

Программа откомпилируется! База данных не выдает ошибку PLS-00371 , потому что я не использую ни одну из переменных в своем коде. Предупреждение PLW -05001 устраняет этот недостаток, сообщая о том, что я объявляю одноименные переменные без использования:

PLW-05003: фактический параметр с IN и NOCOPY может иметь побочные эффекты

Используя NOCOPY с параметром IN OUT , вы приказываете PL/SQL передавать аргумент по ссылке, а не по значению. Это означает, что любые изменения в аргументе вносятся непосредственно в переменную во внешней области действия. С другой стороны, при передаче «по значению» (ключевое слово NOCOPY отсутствует, или компилятор игнорирует рекомендацию NOCOPY ) изменения вносятся в локальную копию параметра IN OUT . Когда программа завершается, изменения копируются в фактический параметр. (Если произойдет ошибка, измененные значения не копируются в фактический параметр.) Рекомендация NOCOPY повышает вероятность совмещения имен аргументов, то есть ссылки на один блок памяти по двум разным именам.

Совмещение имен усложняет понимание и отладку кода; предупреждение компилятора, выявляющее эту ситуацию, будет чрезвычайно полезным.

Возьмем следующую программу:

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

Хотя процедура very_confusing продолжает выполняться, присваивание arg2 не отражается на значении аргумента arg1 . Однако когда значение присваивается arg3 , значение arg1 (аргумент IN) заменяется на « Third value »! Более того, при завершении very_confusing присваивание arg2 было применено к переменной str . Таким образом, при возврате управления во внешний блок переменной str присваивается значение « Second value », фактически заменяющее результат присваивания « Third value ».

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

PLW-05004: идентификатор также объявлен в пакете STANDARD или является встроенным в SQL

Многие разработчики PL/SQL не знают о пакете STANDARD и его влиянии на код PL/ SQL . Например, многие программисты считают, что такие имена, как INTEGER и TO_CHAR , являются зарезервированными словами языка PL/SQL . Однако на самом деле это тип данных и функция, объявленные в пакете STANDARD .


standard — один из двух пакетов по умолчанию в PL/SQL (другой — DBMS_STANDARD ). Поскольку STANDARD является пакетом по умолчанию, вам не нужно уточнять ссылки на такие типы данных, как INTEGER, NUMBER, PLS_INTEGER и т. д., именем STANDARD — хотя при желании это можно сделать.

Предупреждение PLW-5004 сообщает об объявлении идентификатора с таким же именем, как у элемента STANDARD (или встроенным именем SQL; многие встроенные имена, хотя и не все, объявляются в STANDARD ).

Рассмотрим эту процедуру:

Для этой процедуры компилятор выводит следующие предупреждения:

Старайтесь избегать использования имен элементов, определенных в пакете STANDARD , если только у вас нет для этого очень веских причин.

PLW-05005: функция возвращает управление без значения

Очень полезное предупреждение — функция, не возвращающая значение, явно очень плохо спроектирована. Это одно из предупреждений, которые я бы рекомендовал интерпретировать как ошибку (синтаксис « ERROR:5005 ») в настройках PLSQL_WARNINGS . Мы уже рассматривали один пример такой функции: no_return. Тот код был тривиальным; во всем исполняемом разделе не было ни одной команды RETURN . Конечно, код может быть и более сложным. Тот факт, что команда RETURN не выполняется, может быть скрыт за завесой сложной условной логики.

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

База данных обнаружила логическую ветвь, не приводящую к выполнению RETURN , поэтому для программы выдается предупреждение. Файл plw5005.sql на сайте github содержит более сложную условную логику, которая демонстрирует, что предупреждение выдается и в более сложных программных структурах.

PLW-06002: недостижимый код

База данных Oracle теперь умеет проводить статический анализ программы для выявления строк кода, которые ни при каких условиях не получат управление во время выполнения. Это исключительно ценная информация, но иногда компилятор предупреждает о наличии проблемы в строках, которые на первый взгляд недостижимыми вовсе не являются. Более того, в описании действий, предпринимаемых для этой ошибки, говорится, что «предупреждение следует отключить, если большой объем кода был сделан недостижимым намеренно, а предупреждение приносит больше раздражения, чем пользы».

Пример такого предупреждения приводился ранее в разделе «Пример». Теперь рассмотрим следующий код:

В Oracle Database 10g и выше для этой программы выдаются следующие предупреждения:

Понятно, почему строка 7 помечена как недостижимая: l_checking присваивается значение FALSE , поэтому строка 7 выполняться не будет. Но почему строка 5 помечена как недостижимая? На первый взгляд этот код будет выполняться всегда! Более того, строка 13 тоже должна выполняться всегда, потому что GOTO передает управление этой строке по метке. И все же эта строка тоже помечена как недостижимая.

Такое поведение объясняется тем, что до выхода Oracle Database 11g предупреждение о недостижимости кода генерируется после его оптимизации. В Oracle Database 11g и выше анализ недостижимого кода стал намного более понятным и полезным.

Компилятор не вводит вас в заблуждение; говоря, что строка N недостижима, он сообщает, что она никогда не будет выполняться в соответствии со структурой оптимизированного кода.

Некоторые ситуации с недостижимым кодом не обнаруживаются компилятором. Пример:

Разумеется, вызов DBMS_OUTPUT.PUT_LINE недостижим, но в настоящее время компилятор не обнаруживает это обстоятельство — до версии 12.1.

PLW-07203: рекомендация NOCOPY может принести пользу в объявлении параметра

Как упоминалось ранее в отношении PLW-05005 , использование NOCOPY для сложных, больших параметров IN OUT может улучшить производительность программ в некоторых условиях. Это предупреждение выдается для программ, у которых включение NOCOPY для параметров IN OUT может повысить эффективность выполнения. Пример такой программы:

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

PLW-07204: преобразование типа столбца может привести к построению неоптимального плана запроса

Предупреждение выдается при вызове команд SQL из PL/SQL, при котором происходят неявные преобразования. Пример:

С этим предупреждением тесно связано предупреждение PLW-7202 (тип передаваемого параметра приводит к преобразованию типа столбца).

PLW-06009: обработчик OTHERS не завершается вызовом RAISE или RAISE_APPLICATION_ERROR

Это предупреждение (добавленное в Oracle Database 11g) выводится тогда, когда в обработчике исключений OTHERS не выполняется та или иная форма RAISE (повторное инициирование того же исключения или инициирование другого исключения), и не вызывается RAISE_APPLICATI0N_ERR0R . Другими словами, существует большая вероятность того, что программа «поглощает» исключение и игнорирует его. Ситуации, в которых ошибки действительно должны игнорироваться, встречаются довольно редко. Чаще исключение должно передаваться во внешний блок:

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

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

Итак, имеется хранимая процедура, созданная пользователем А.
Мы даем к ней доступ пользователю Б.

GRANT EXECUTE ON PROCEDURE

Пользователь Б может пользоваться этой процедурой. Все рады, музыка, шампанское.
После фуршета пользователь A вспоминает, что хорошо бы чуток подправить процедуру, чтобы она работала быстрее.
И подправляет. Наутро пользователь Б обнаруживает, что больше не может выполнять эту процедуру.
Все в трауре, миллионные потери, разработчики проекта уволены, занавес.

Что же произошло?
Дело в том, что люди из MySQL почему-то не подумали, что необходимость поменять процедуру возникает очень часто.
И не сделали возможность изменять код процедуры. ALTER PROCEDURE дает менять какие-то мало вразумительные настройки и не дает менять тела процедуры. Вместо этого ребята из Мускула предлагают делать ей DROP и потом CREATE с новым текстом: «you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure». А при удалении процедуры все GRANT’ы на нее, понятное дело, исчезают.

Можно ли было что-то сделать?
Как оказалось, можно.
Текст процедур хранится в таблице proc базы mysql.
И хотя товарищи из Мускула не рекомендуют не лезть в эту таблицу руками, но иного выбора не предоставляют.
В этой таблице нас интересуют два поля — body и body_utf8.
В них содержится текст нашей процедуры.
Берем за хобот нашего DBA и делаем UPDATE на этих полях, занося в них новый текст процедуры.

Казалось бы, время опять устраивать фуршет по поводу успешной борьбы с Мускулом.
Ан нет. Процедура начнет работать по-новому только для новых сессий пользователя Б.
А как быть, если коннекты перманентные и надо сделать изменения доступными и для них?
А вот тут поможет тот самый ALTER PROCREDURE.

Банальное изменения текста комментария к процедуре (например, изменение номера ее ревизии) делает доступными изменения для всех сессий:

Илон Маск рекомендует:  Подводные камни JavaScript
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL