Параметры в oracle sql

Обновлено: 21.11.2024

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

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

Итак, давайте посмотрим демонстрацию создания хранимой процедуры PL/SQL с параметрами!

Шаг 1. Создайте заголовок хранимой процедуры

В заголовке процедуры мы определяем ее подпись.

Заголовок очень похож на тот, который мы видели в предыдущем руководстве, за исключением того, что на этот раз наша процедура принимает параметры dep_id и sal_raise типа данных NUMBER.

Шаг 2. Создайте раздел выполнения хранимой процедуры

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

Для лучшего понимания я постарался сделать этот код максимально простым. В разделе выполнения у нас есть только один оператор DML — UPDATE. Используя это, мы обновляем столбец зарплаты в таблице сотрудников.

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

Эта процедура принимает два параметра: идентификатор отдела и числовое значение повышения зарплаты. Первый параметр dep_id используется для определения идентификатора отдела. Второй параметр, который называется sal_raise, станет коэффициентом умножения при повышении зарплаты.

Для более глубокого понимания этого вопроса, пожалуйста, посмотрите обучающее видео на моем канале YouTube. Там я подробно объяснил каждую строку и ключевое слово вышеприведенной хранимой процедуры.

Давайте объединим все приведенные выше фрагменты кода в один модуль PL/SQL.

Хранимая процедура повышения заработной платы в масштабах отдела

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

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

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

Статья состоит из следующих разделов:

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

Основные параметры

В общем случае параметр — это заполнитель для переменной, которая содержит некоторое значение определенного типа при выполнении запроса общего назначения или аргументы и возвращаемые значения при выполнении хранимой процедуры. Параметр представлен классом Oracle.OracleParameter. Все параметры, участвующие в выполнении запроса, составляют коллекцию, доступ к которой можно получить через свойство OracleCommand.Parameters.

Параметры должны соответствовать именам экземпляров OracleParameter в коллекции. Параметры объявляются с использованием префикса ':', за которым следует имя параметра. Вам не нужно заботиться о порядке создания параметров. Параметр может встречаться в тексте запроса более одного раза, но вам нужно создать только один его экземпляр в коллекции параметров.

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

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

При вызове хранимой процедуры необходимо создать набор параметров, который строго соответствует набору аргументов хранимой процедуры по именам, количеству и типам. Для параметров, представляющих аргументы процедуры, свойство Direction должно иметь значение Input, InputOutput или Output. Вы получаете значение, возвращаемое хранимой функцией, через параметр со свойством Direction, для которого задано значение ReturnValue.

Использование автоматической синхронизации параметров

Описанное выше поведение предполагает, что OracleCommand.ParameterCheck имеет значение false (по умолчанию). Включив его, вы включаете автоматическую синхронизацию текста запроса и коллекции OracleCommand.Parameters. В этом режиме все входные параметры проверяются на корректность, при необходимости добавляются новые, а лишние параметры удаляются. Таким образом, вам не нужно заботиться о количестве элементов в коллекции OracleCommand.Parameters, вы можете указать только те, которые вам действительно нужны. Синхронизация выполняется следующим образом:

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

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

Для хранимых процедур синхронизация происходит, когда:

  • Запрос подготовлен
  • Запрос выполнен

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

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

Если OracleCommand.ParameterCheck имеет значение true, вы можете указать только те параметры, которые вам действительно нужны. Будут созданы пропущенные параметры, которым будет присвоено значение DBNull. Вы можете установить необходимые параметры в любом порядке. Коллекция будет наполняться и сортироваться в результате синхронизации.

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

Подробнее

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

Вы можете использовать выходные параметры REF CURSOR для возврата набора результатов или нескольких наборов результатов из процедуры или блока PL/SQL. Статья Работа с курсорами посвящена этому вопросу. Вы также можете найти там пример кода, который показывает, как заполнить набор данных из выходных параметров REF CURSOR.

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

Информацию о том, как использовать параметры с таблицами PL/SQL, см. в статье Работа с таблицами PL/SQL.

Проблемы с производительностью

Как правило, установка для свойства OracleCommand.ParameterCheck значения true приводит к некоторой потере производительности.
Если OracleCommand.CommandType имеет значение «Текст», синхронизация выполняется на клиенте, поэтому производительность снижается очень незначительно.
Когда OracleCommand.CommandType имеет значение "StoredProcedure", dotConnect for Oracle отправляет на сервер дополнительные запросы, необходимые для определения количества, имен и другой информации о параметрах. Таким образом, производительность в основном зависит от того, насколько быстро выполняются эти дополнительные обращения.

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

Вы можете значительно ускорить многократное выполнение с помощью привязки массива. Информацию об этой проблеме см. в статье Работа с привязкой массива.

Примеры

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

Сначала текст запроса присваивается объекту OracleCommand. Если для параметра OracleCommand.ParameterCheck задано значение true, dotConnect for Oracle автоматически создает набор параметров, к которым можно получить доступ для установки значений. Во-вторых, вызов метода OracleCommand.Prepare выполняется для достижения наилучшей производительности. Затем в таблицу добавляются две строки, каждая из которых ссылается на отдел Sales. После этого текст запроса меняется и снова перестраивается коллекция OracleCommand.Parameters. На этот раз в нем всего один предмет.Обратите внимание, что этому параметру раньше было присвоено значение Int32, а теперь он содержит строку. Это возможно, поскольку вы не назначаете OracleParameter.OracleDbType явно. После установки значения свойства OracleDbType или DbType автоматическое определение типа данных перестает работать.

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

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

В последнем примере показано, как вызвать хранимую процедуру в режиме автосинхронизации.
Представьте, что у вас есть хранимая процедура, которая добавляет нового сотрудника в таблицу Emp. Он определяет следующий подходящий EmpNo, вставляет текущую дату и проверяет входные параметры. Если они содержат разумное значение, процедура также вставляет это значение; если параметр содержит значение NULL, используются некоторые значения по умолчанию. Вот как может выглядеть исходный код этой процедуры:

Мы вызовем эту процедуру и передадим ей единственный параметр - EmpName. Поскольку ParameterCheck имеет значение true, второй параметр будет создан в момент вызова метода ExecuteNonQuery. Таким образом, этот код приведет к добавлению нового сотрудника с именем Roger и зарплатой по умолчанию (1100).

Обратите внимание, что в режиме автосинхронизации имеет значение только имя параметра. Вам не нужно заботиться о порядке создания и нет необходимости создавать параметры, которые должны иметь значение NULL. Другими словами, если нам нужно добавить сотрудника с именем по умолчанию, но с определенной зарплатой, мы можем создать один аргумент с ParameterName, установленным на «Зарплата».

Вы можете использовать нашу настраиваемую поисковую систему на основе Google Co-op, чтобы сузить область поиска и получить точные результаты.

Календарь

Популярные публикации

Рулон блога

Проверка параметров базы данных в Oracle

Опубликовано decipherinfosys 20 марта 2007 г.

Очень простой способ проверить значения параметров для базы данных Oracle — ввести следующую команду из командной строки SQL*Plus. За ним следует сокращенный вывод.

ИМЯ ТИП ЗНАЧЕНИЕ
———————— ———— ——————————
background_core_dump string partial
background_dump_dest string D:\ORACLE \PRODUCT\10.2.0\ADMIN
\ORCL\BDUMP
backup_tape_io_slaves boolean FALSE
bitmap_merge_area_size integer 1048576
blank_trimming boolean FALSE

Для просмотра определенных значений параметров, связанных с командой SGA, необходимо

SQL>показать область параметров

ИМЯ ТИП ЗНАЧЕНИЕ
———————— ———— ——————————
bitmap_merge_area_size целое число 1048576
create_bitmap_area_size целое число 8388608
hash_area_size целое число 131072
sort_area_retained_size целое число 0
sort_area_size целое число 65536
workarea_size_policy string AUTO

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

V$Parameter — предоставляет информацию обо всех параметрах. Он также указывает, можно ли изменять параметры на уровне сеанса или на уровне системы. Ниже приведен запрос для поиска параметров, связанных с SGA.

SQL> ВЫБЕРИТЕ имя,значение,isses_modified,issys_modified
FROM v$parameter
ГДЕ имя, например ‘%sga%’;

ИМЯ ЗНАЧЕНИЕ ISSES ISSYS_MOD
————— ————— —— ———
sga_max_size 612368384 FALSE FALSE
pre_page_sga FALSE FALSE FALSE
lock_sga FALSE FALSE FALSE
sga_target 612368384 ЛОЖЬ НЕМЕДЛЕННО

V$NLS_Parameters — предоставляет информацию обо всех параметрах, связанных с настройками NLS.

В этой главе мы обсудим процедуры в 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.

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

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

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

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

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