Как вызвать процедуру оракула

Обновлено: 21.11.2024

В этом руководстве вы увидите подробное описание того, как создавать и выполнять именованные блоки (процедуры и функции).

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

Помимо этого, мы рассмотрим основные различия между этими двумя подпрограммами. Также мы обсудим встроенные функции Oracle.

В этом учебном пособии по хранимым процедурам Oracle вы узнаете:

Терминология в подпрограммах PL/SQL

Прежде чем мы узнаем о подпрограммах PL/SQL, мы обсудим различные термины, используемые в этих подпрограммах. Ниже приведены термины, которые мы собираемся обсудить.

Параметр:

Параметр является переменной или заполнителем любого допустимого типа данных PL/SQL, посредством которого подпрограмма PL/SQL обменивается значениями с основным кодом. Этот параметр позволяет вводить данные в подпрограммы и извлекать из них данные.

  • Эти параметры должны быть определены вместе с подпрограммами во время создания.
  • Эти параметры включены в оператор вызова этих подпрограмм для взаимодействия значений с подпрограммами.
  • Тип данных параметра в подпрограмме и вызывающем операторе должен совпадать.
  • Размер типа данных не следует указывать во время объявления параметра, так как размер для этого типа является динамическим.

По своему назначению параметры классифицируются как

  1. Входной параметр
  2. Выходной параметр
  3. Параметры IN OUT

Входной параметр:

  • Этот параметр используется для ввода данных в подпрограммы.
  • Это переменная только для чтения внутри подпрограмм. Их значения нельзя изменить внутри подпрограммы.
  • В вызывающем операторе эти параметры могут быть переменной, литеральным значением или выражением, например, это может быть арифметическое выражение, такое как "5*8" или "a/b", где "a" и "b". ' являются переменными.
  • По умолчанию параметры имеют тип IN.

Выходной параметр:

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

Параметры IN OUT:

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

Этот тип параметра следует указывать во время создания подпрограмм.

ВОЗВРАТ

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

Обычно родительский или основной блок вызывает подпрограммы, а затем управление переходит от этого родительского блока к вызываемым подпрограммам. RETURN в подпрограмме вернет управление обратно в родительский блок. В случае функций оператор RETURN также возвращает значение. Тип данных этого значения всегда упоминается во время объявления функции. Тип данных может быть любым допустимым типом данных PL/SQL.

Что такое процедура в PL/SQL?

Процедура в PL/SQL — это модуль подпрограммы, состоящий из группы операторов PL/SQL, которые можно вызывать по имени. Каждая процедура в PL/SQL имеет собственное уникальное имя, по которому на нее можно ссылаться и вызывать. Этот модуль подпрограммы в базе данных Oracle хранится как объект базы данных.

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

Ниже приведены характеристики модуля подпрограммы «Процедура» в PL/SQL:

  • Процедуры – это отдельные блоки программы, которые можно хранить в базе данных.
  • Вызов этих процедур PLSQL можно выполнить, обратившись к их имени, чтобы выполнить операторы PL/SQL.
  • В основном он используется для выполнения процесса в PL/SQL.
  • Он может иметь вложенные блоки или может быть определен и вложен в другие блоки или пакеты.
  • Он содержит часть объявления (необязательно), часть выполнения, часть обработки исключений (необязательно).
  • Значения можно передать в процедуру Oracle или получить из процедуры с помощью параметров.
  • Эти параметры должны быть включены в оператор вызова.
  • Процедура в SQL может иметь оператор RETURN для возврата управления вызывающему блоку, но не может возвращать какие-либо значения с помощью оператора RETURN.
  • Процедуры нельзя вызывать непосредственно из операторов SELECT. Их можно вызывать из другого блока или через ключевое слово EXEC.

Синтаксис:

  • CREATE PROCEDURE указывает компилятору создать новую процедуру в Oracle. Ключевое слово «OR REPLACE» указывает компилятору заменить существующую процедуру (если есть) на текущую.
  • Имя процедуры должно быть уникальным.
  • Ключевое слово «IS» будет использоваться, когда хранимая процедура в Oracle вложена в некоторые другие блоки. Если процедура является автономной, будет использоваться «AS». За исключением этого стандарта кодирования, оба имеют одинаковое значение.

Пример 1: создание процедуры и ее вызов с помощью EXEC

В этом примере мы собираемся создать процедуру Oracle, которая принимает имя в качестве входных данных и печатает приветственное сообщение в качестве вывода. Мы собираемся использовать команду EXEC для вызова процедуры.

Пояснение кода:

  • Строка кода 1: создание процедуры с именем «welcome_msg» и одним параметром «p_name» типа «IN».
  • Строка кода 4: печать приветственного сообщения путем объединения входного имени.
  • Процедура успешно скомпилирована.
  • Строка кода 7: вызов процедуры с помощью команды EXEC с параметром «Guru99». Процедура выполняется, и сообщение распечатывается как «Добро пожаловать, Гуру99».

Что такое функция?

Функции — это отдельная подпрограмма PL/SQL. Подобно процедурам PL/SQL, функции имеют уникальное имя, по которому к ним можно обращаться. Они хранятся как объекты базы данных PL/SQL. Ниже приведены некоторые характеристики функций.

  • Функции — это отдельный блок, который в основном используется для расчетов.
  • Функция использует ключевое слово RETURN для возврата значения, тип данных которого определяется во время создания.
  • Функция должна либо возвращать значение, либо вызывать исключение, т. е. возврат является обязательным в функциях.
  • Функция без инструкций DML может быть вызвана напрямую в запросе SELECT, тогда как функция с операцией DML может быть вызвана только из других блоков PL/SQL.
  • Он может иметь вложенные блоки или может быть определен и вложен в другие блоки или пакеты.
  • Он содержит часть объявления (необязательно), часть выполнения, часть обработки исключений (необязательно).
  • Значения можно передать в функцию или получить из процедуры через параметры.
  • Эти параметры должны быть включены в оператор вызова.
  • Функция PLSQL также может возвращать значение через параметры OUT, отличные от RETURN.
  • Поскольку он всегда будет возвращать значение, в вызывающем операторе он всегда сопровождается оператором присваивания для заполнения переменных.

Синтаксис

  • CREATE FUNCTION указывает компилятору создать новую функцию. Ключевое слово OR REPLACE указывает компилятору заменить существующую функцию (если есть) текущей.
  • Имя функции должно быть уникальным.
  • Следует указать тип данных RETURN.
  • Ключевое слово «IS» будет использоваться, когда процедура вложена в некоторые другие блоки. Если процедура является автономной, будет использоваться «AS». За исключением этого стандарта кодирования, оба имеют одинаковое значение.

Пример 1: создание функции и вызов ее с помощью анонимного блока

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

Пояснение кода:

  • Строка кода 1. Создание функции Oracle с именем «welcome_msg_func» и одним параметром «p_name» типа «IN».
  • Строка кода 2: объявление возвращаемого типа как VARCHAR2
  • Строка кода 5. Возврат объединенного значения "Добро пожаловать" и значения параметра.
  • Строка кода 8: анонимный блок для вызова вышеуказанной функции.
  • Строка кода 9: объявление переменной с таким же типом данных, что и возвращаемый тип данных функции.
  • Строка кода 11: вызов функции и занесение возвращаемого значения в переменную lv_msg.
  • Строка кода 12: печать значения переменной. Результат, который вы получите здесь: «Добро пожаловать, Гуру99»
  • Строка кода 14: вызов той же функции с помощью инструкции SELECT. Возвращаемое значение направляется непосредственно на стандартный вывод.

Сходства между процедурой и функцией

  • Оба могут быть вызваны из других блоков PL/SQL.
  • Если исключение, сгенерированное в подпрограмме, не обрабатывается в разделе обработки исключений подпрограммы, оно будет передано в вызывающий блок.
  • Оба могут иметь столько параметров, сколько требуется.
  • Оба в PL/SQL рассматриваются как объекты базы данных.

Процедура vs. Функция: ключевые отличия

  • Используется в основном для выполнения определенного процесса.
  • Используется в основном для выполнения некоторых расчетов.
  • Невозможно вызвать оператор SELECT
  • Функция, не содержащая операторов DML, может быть вызвана в операторе SELECT
  • Используйте параметр OUT, чтобы вернуть значение
  • Используйте RETURN, чтобы вернуть значение
  • Возвращать значение не обязательно
  • Обязательно возвращать значение
  • RETURN просто выйдет из управления из подпрограммы.
  • RETURN закроет элемент управления из подпрограммы, а также вернет значение
  • Тип возвращаемых данных не будет указан во время создания
  • Тип возвращаемых данных является обязательным во время создания

Встроенные функции PL/SQL

PL/SQL содержит различные встроенные функции для работы со строками и типами данных даты. Здесь мы увидим часто используемые функции и их использование.

Функции преобразования

Эти встроенные функции используются для преобразования одного типа данных в другой тип данных.

Вывод: в этом руководстве вы узнаете, как создать, скомпилировать и выполнить процедуру PL/SQL из Oracle SQL Developer.

Синтаксис процедуры PL/SQL

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

Следующее иллюстрирует основной синтаксис создания процедуры в PL/SQL:

Заголовок процедуры PL/SQL

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

Каждый параметр может находиться в режиме IN , OUT или INOUT . Режим параметра указывает, может ли параметр быть прочитан или записан.

В

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

ВЫХОД

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

ВХОД

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

Обратите внимание, что опция OR REPLACE позволяет перезаписать текущую процедуру новым кодом.

Тело процедуры PL/SQL

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

1) Декларативная часть

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

2) Исполняемая часть

Эта часть содержит одну или несколько инструкций, реализующих определенную бизнес-логику. Он может содержать только оператор NULL.

3) Часть обработки исключений

Эта часть содержит код, обрабатывающий исключения.

Создание примера процедуры PL/SQL

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

Если процедура скомпилирована успешно, вы найдете новую процедуру в узле «Процедуры», как показано ниже:

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

Например, чтобы выполнить процедуру print_contact, которая печатает контактную информацию клиента с идентификатором 100, вы используете следующую инструкцию:

Вот результат:

Вы также можете выполнить процедуру из Oracle SQL Developer, выполнив следующие действия:

1) Щелкните правой кнопкой мыши имя процедуры и выберите пункт меню "Выполнить..."

2) Введите значение параметра in_customer_id и нажмите кнопку ОК.

3) Ниже показан результат

Редактирование процедуры

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

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

Чтобы удалить процедуру, используйте DROP PROCEDURE, за которой следует имя процедуры, которую вы хотите удалить, как показано в следующем синтаксисе:

Следующее иллюстрирует этапы удаления процедуры с помощью SQL Developer:

В этом руководстве вы узнали, как создать процедуру PL/SQL и выполнить ее из Oracle SQL Developer.

После загрузки и публикации хранимой процедуры Java ее можно вызвать. В этой главе показано, как вызывать хранимые процедуры Java в различных контекстах. Вы узнаете, как вызывать их из верхнего уровня и из триггеров базы данных, операторов SQL DML и блоков PL/SQL. Вы также узнаете, как обрабатываются исключения SQL.

Вызов Java с верхнего уровня

Выражение SQL CALL позволяет вызывать методы Java, опубликованные на верхнем уровне, в пакетах PL/SQL или в типах объектов SQL. В SQL*Plus оператор CALL можно выполнять интерактивно, используя следующий синтаксис:

где param означает следующий синтаксис:

Переменные хоста (то есть переменные, объявленные в хост-среде) должны начинаться с двоеточия. В следующих примерах показано, что переменная хоста не может дважды появляться в одном и том же операторе CALL и что подпрограмма без параметров должна вызываться с пустым списком параметров:

Перенаправление вывода

На сервере устройством вывода по умолчанию является файл трассировки, а не экран пользователя. В результате System.out и System.err печатают текущие файлы трассировки. Чтобы перенаправить вывод в текстовый буфер SQL*Plus, вызовите процедуру set_output() в пакете DBMS_JAVA следующим образом:

Минимальный размер буфера (по умолчанию) — 2 000 байт; максимальный размер — 1 000 000 байт. В следующем примере размер буфера увеличен до 5000 байт:

Вывод печатается при выходе из хранимой процедуры.

Пример 1

В следующем примере метод main принимает имя таблицы базы данных (например, 'emp') и необязательное условие предложения WHERE (например, 'sal > 1500'). Если вы опустите условие, метод удалит все строки из таблицы. В противном случае метод удаляет только те строки, которые соответствуют условию.

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

Теперь вы готовы вызвать процедуру delete_rows :

Пример 2

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

В классе Fibonacci есть один метод с именем fib , который возвращает n-е число Фибоначчи. Последовательность Фибоначчи (1, 1, 2, 3, 5, 8, 13, 21,...), впервые использованная для моделирования роста колонии кроликов, является рекурсивной. Каждый член в последовательности (после второго) является суммой двух членов, непосредственно ему предшествующих. Поскольку метод fib возвращает значение, вы публикуете его как функцию:

Затем вы объявляете две переменные хоста SQL*Plus, а затем инициализируете первую:

Наконец, вы готовы вызвать функцию fib . Помните, что в операторе CALL переменные хоста должны начинаться с двоеточия.

Вызов Java из триггеров базы данных

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

Триггер состоит из трех частей: инициирующего события (операция DML), необязательного ограничения триггера и действия триггера. Когда происходит событие, срабатывает триггер, и либо блок PL/SQL, либо оператор CALL выполняет действие. Триггер оператора срабатывает один раз до или после триггерного события. Триггер строки срабатывает один раз для каждой строки, затронутой триггерным событием.

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

Для создания триггера базы данных используется оператор SQL CREATE TRIGGER. Синтаксис этого оператора см. в Справочнике по Oracle9i SQL.Полное обсуждение триггеров базы данных см. в Руководстве разработчика приложений Oracle9i — основы

.

Пример 1

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

Затем вы создаете таблицу базы данных sal_audit следующим образом:

Наконец, вы создаете триггер базы данных, который срабатывает, когда повышение зарплаты превышает двадцать процентов:

Когда вы выполняете следующий оператор UPDATE, он обновляет все строки в таблице emp. Для каждой строки, удовлетворяющей условию триггера WHEN, срабатывает триггер, и метод Java вставляет строку в таблицу sal_audit .

Пример 2

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

где таблицы базы данных sales и mktg определены как:

Вы должны написать триггер INSTEAD OF, поскольку строки не могут быть вставлены в представление, использующее такие операторы набора, как UNION ALL. Вместо этого ваш триггер будет вставлять строки в базовые таблицы.

Сначала вы добавляете следующий метод Java в класс DBTrigger (определенный в предыдущем примере):

Метод addEmp вставляет строку в таблицу sales или mktg в зависимости от значения параметра deptName . Вы пишете спецификацию вызова для этого метода следующим образом:

Затем вы создаете триггер INSTEAD OF:

При выполнении каждого из следующих операторов INSERT срабатывает триггер, и метод Java вставляет строку в соответствующую базовую таблицу:

Вызов Java из SQL DML

Если вы публикуете методы Java как функции, вы можете вызывать их из операторов SQL SELECT, INSERT, UPDATE, DELETE, CALL, EXPLAIN PLAN, LOCK TABLE и MERGE. Например, предположим, что исполняемый файл для следующего класса Java хранится в базе данных Oracle:

В классе Formatter есть один метод с именем formatEmp , который возвращает отформатированную строку, содержащую имя сотрудника и статус работы. Во-первых, вы пишете спецификацию вызова для этого метода следующим образом:

Затем вы вызываете функцию format_emp для форматирования списка сотрудников:

Ограничения

Чтобы метод Java можно было вызывать из операторов SQL DML, он должен соответствовать следующим правилам "чистоты", предназначенным для контроля побочных эффектов:

    Когда вы вызываете его из оператора SELECT или параллельного оператора INSERT, UPDATE или DELETE, метод не может изменять какие-либо таблицы базы данных. Когда вы вызываете его из оператора INSERT , UPDATE или DELETE , метод не может запрашивать или изменять какие-либо таблицы базы данных, измененные этим оператором. Когда вы вызываете его из оператора SELECT, INSERT, UPDATE или DELETE, метод не может выполнять операторы управления транзакциями SQL (такие как COMMIT), операторы управления сеансом (такие как SET ROLE) или операторы управления системой (такие как ALTER SYSTEM). . Кроме того, он не может выполнять операторы DDL (такие как CREATE), поскольку за ними следует автоматическая фиксация.

Если какой-либо оператор SQL внутри метода нарушает правило, вы получите сообщение об ошибке во время выполнения (при анализе оператора).

Вызов Java из PL/SQL

Вы можете вызывать хранимые процедуры Java из любого блока, подпрограммы или пакета PL/SQL. Например, предположим, что исполняемый файл для следующего класса Java хранится в базе данных Oracle:

У класса Adjuster есть один метод, который повышает зарплату сотрудника на заданный процент. Поскольку метод raiseSalary недействителен, вы публикуете его как процедуру следующим образом:

В следующем примере вы вызываете процедуру raise_salary из анонимного блока PL/SQL:

В последнем примере вы вызываете метод raise_sal типа объекта Employee (определенный в разделе "Реализация методов типа объекта") из анонимного блока PL/SQL:

Вызов PL/SQL из Java

JDBC и SQLJ позволяют вызывать хранимые функции и процедуры PL/SQL. Например, предположим, что вы хотите вызвать следующую хранимую функцию, которая возвращает баланс указанного банковского счета:

В программе JDBC ваш вызов функции balance может выглядеть следующим образом:

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

Как JVM обрабатывает исключения

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

Все объекты исключений Java поддерживают метод toString() , который возвращает полное имя класса исключений, объединенное с необязательной строкой. Как правило, строка содержит зависящую от данных информацию об исключительном условии. Обычно код, создающий исключение, связывает с ним строку.

Когда хранимая процедура Java выполняет оператор SQL, любое созданное исключение материализуется в процедуре как подкласс java.sql.SQLException .Этот класс имеет методы getErrorCode() и getMessage() , которые возвращают код ошибки и сообщение Oracle соответственно.

Если хранимая процедура, вызванная из SQL или PL/SQL, выдает исключение, не перехватываемое Java, вызывающая сторона получает следующее сообщение об ошибке:

Вот как сообщается обо всех неперехваченных исключениях (включая исключения, отличные от SQL).

Я использую экспресс-выпуск oracle 10g. Он имеет приятный интерфейс для разработчиков БД. Но я столкнулся с некоторыми проблемами при выполнении хранимых процедур.

он ​​успешно создан. Но когда я выполняю:

показывает ORA-00900: неверный оператор SQL

Поэтому здесь нужна помощь

Сначала я открываю «Перейти на домашнюю страницу базы данных», затем «Главная»> «SQL»> «Команды SQL». Затем создайте процедуру, затем выполните ее.

я не использую ide. Это встроенный пользовательский интерфейс, заданный в oracle xe. Но я нашел решение. начать имя_процесса(); конец

7 ответов 7

Execute - это синтаксис sql*plus.. попробуйте обернуть вызов в begin.. end следующим образом:

(Хотя Джеффри говорит, что это не работает в APEX... но вы пытаетесь запустить это в SQLDeveloper... попробуйте там меню "Выполнить".)

Oracle 10g Express Edition поставляется со встроенным Oracle Application Express (Apex). Вы запускаете это в окне команд SQL, которое не поддерживает синтаксис SQL*Plus.

Это не имеет значения, потому что (как вы обнаружили) файл BEGIN. Синтаксис END работает в Apex.

И 'is', и 'as' являются допустимым синтаксисом. По умолчанию вывод отключен. Попробуйте процедуру, которая также включает вывод.

<р>. и вызовите его в блоке PLSQL.

<р>. поскольку SQL не является процедурным.

Я использую oracle 12, и он говорит мне, что если вам нужно вызвать процедуру, используйте ключевое слово call. В вашем случае это должно быть:

Вы пробовали исправить такой синтаксис?:

Я вижу, что вы только что изменили IS на AS и добавили точку с запятой в конце ключевого слова "конец". Хотя добавление точки с запятой в порядке, но OP говорит, что компиляция прошла успешно, так что это не может быть проблемой. Проблема в том, что OP не знал, как выполнить процедуру, которая уже была правильно решена в принятом ответе. Так что этот ответ неверен.

В Oracle SQL Developer (GUI), используя 12c, также не забудьте включить окно вывода DMBS (нажмите View => Dbms Output, затем нажмите знак "+" и выберите свое подключение), по умолчанию окно не активировано.

В этой главе мы обсудим процедуры в PL/SQL. Подпрограмма — это программный блок/модуль, выполняющий определенную задачу. Эти подпрограммы объединяются в более крупные программы. В основном это называется «Модульный дизайн». Подпрограмма может быть вызвана другой подпрограммой или программой, которая называется вызывающей программой.

Можно создать подпрограмму —

  • На уровне схемы
  • Внутри пакета
  • Внутри блока PL/SQL

На уровне схемы подпрограмма является отдельной подпрограммой. Он создается оператором CREATE PROCEDURE или CREATE FUNCTION. Он хранится в базе данных и может быть удален оператором DROP PROCEDURE или DROP FUNCTION.

Подпрограмма, созданная внутри пакета, является упакованной подпрограммой. Он хранится в базе данных и может быть удален только при удалении пакета с помощью инструкции DROP PACKAGE. Мы обсудим пакеты в главе «PL/SQL — Пакеты».

Подпрограммы PL/SQL — это именованные блоки PL/SQL, которые можно вызывать с набором параметров. PL/SQL предоставляет два вида подпрограмм —

Функции — эти подпрограммы возвращают одно значение; в основном используется для вычисления и возврата значения.

Процедуры — эти подпрограммы не возвращают значение напрямую; в основном используется для выполнения действия.

В этой главе будут рассмотрены важные аспекты процедуры PL/SQL. Мы обсудим функции PL/SQL в следующей главе.

Части подпрограммы PL/SQL

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

Декларативная часть

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

Исполняемая часть

Это обязательная часть, содержащая операторы, выполняющие указанное действие.

Обработка исключений

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

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

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

имя-процедуры указывает имя процедуры.

Опция [ИЛИ ЗАМЕНИТЬ] позволяет модифицировать существующую процедуру.

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

тело процедуры содержит исполняемую часть.

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

Пример

В следующем примере создается простая процедура, которая отображает строку "Hello World!" на экране при выполнении.

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

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

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

Использование ключевого слова EXECUTE

Вызов имени процедуры из блока PL/SQL

Вышеупомянутая процедура под названием «приветствия» может быть вызвана с ключевым словом EXECUTE как -

Приведенный выше вызов будет отображаться –

Процедура также может быть вызвана из другого блока PL/SQL —

Приведенный выше вызов будет отображаться –

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

Автономная процедура удаляется оператором DROP PROCEDURE. Синтаксис удаления процедуры —

Вы можете отказаться от процедуры приветствия, используя следующую инструкцию —

Режимы параметров в подпрограммах PL/SQL

В следующей таблице перечислены режимы параметров в подпрограммах PL/SQL —

Параметр IN позволяет передать значение подпрограмме. Это параметр только для чтения. Внутри подпрограммы параметр IN действует как константа. Ему нельзя присвоить значение. Вы можете передать константу, литерал, инициализированную переменную или выражение в качестве параметра IN. Вы также можете инициализировать его значением по умолчанию; однако в этом случае он опускается при вызове подпрограммы. Это режим передачи параметров по умолчанию. Параметры передаются по ссылке.

Параметр OUT возвращает значение вызывающей программе. Внутри подпрограммы параметр OUT действует как переменная. Вы можете изменить его значение и сослаться на значение после его назначения. Фактический параметр должен быть переменным и передается по значению.

Параметр IN OUT передает начальное значение подпрограмме и возвращает обновленное значение вызывающей программе. Ему можно присвоить значение, и значение можно прочитать.

Фактический параметр, соответствующий формальному параметру IN OUT, должен быть переменной, а не константой или выражением. Формальному параметру должно быть присвоено значение. Фактический параметр передается по значению.

Пример 1 режима IN & OUT

Эта программа находит минимальное из двух значений. Здесь процедура берет два числа, используя режим IN, и возвращает их минимум, используя параметры OUT.

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

Пример 2 режима IN & OUT

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

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

Способы передачи параметров

Фактические параметры можно передать тремя способами:

  • Позиционное обозначение
  • Именованное обозначение
  • Смешанная запись

Позиционное обозначение

В позиционной записи вы можете вызвать процедуру как -

В позиционной записи первый фактический параметр заменяется первым формальным параметром; второй фактический параметр заменяется вторым формальным параметром и т.д. Таким образом, a заменяет x, b заменяет y, c заменяет z, а d заменяет m.

Именованное обозначение

В именованной нотации фактический параметр связан с формальным параметром с помощью символа стрелки ( => ). Вызов процедуры будет выглядеть следующим образом —

Смешанная нотация

В смешанной нотации вы можете смешивать обе нотации при вызове процедуры; однако позиционное обозначение должно предшествовать именованному обозначению.

Читайте также: