В этом руководстве вы увидите подробное описание того, как создавать и выполнять именованные блоки (процедуры и функции).
Процедуры и функции - это подпрограммы, которые могут быть созданы и сохранены в базе данных как объекты базы данных. Они также могут вызываться или ссылаться на другие блоки.
Помимо этого, мы рассмотрим основные различия между этими двумя подпрограммами. Также мы собираемся обсудить встроенные функции Oracle.
В этом руководстве по хранимой процедуре Oracle вы узнаете:
- Терминология в подпрограммах PL / SQL
- Что такое процедура в PL / SQL?
- Что такое функция?
- Сходства между процедурой и функцией
- Процедура Vs. Функция: Ключевые отличия
- Встроенные функции в PL / SQL
Терминология в подпрограммах PL / SQL
Прежде чем мы узнаем о подпрограммах PL / SQL, мы обсудим различные термины, которые являются частью этих подпрограмм. Ниже приведены термины, которые мы собираемся обсудить.
Параметр:
Параметр является переменной или заполнителем любого допустимого типа данных PL / SQL, через который подпрограмма PL / SQL обменивается значениями с основным кодом. Этот параметр позволяет вводить подпрограммы и извлекать из этих подпрограмм.
- Эти параметры должны быть определены вместе с подпрограммами во время создания.
- Эти параметры включены в вызывающую инструкцию этих подпрограмм для взаимодействия значений с подпрограммами.
- Тип данных параметра в подпрограмме и вызывающей инструкции должен совпадать.
- Размер типа данных не следует указывать во время объявления параметра, поскольку размер является динамическим для этого типа.
По своему назначению параметры классифицируются как
- Параметр IN
- ВЫХОД Параметр
- Параметр IN OUT
Параметр IN:
- Этот параметр используется для ввода подпрограмм.
- Внутри подпрограмм это переменная только для чтения. Их значения нельзя изменить внутри подпрограммы.
- В вызывающем операторе эти параметры могут быть переменной, буквальным значением или выражением, например, это может быть арифметическое выражение, такое как '5 * 8' или 'a / b', где 'a' и 'b' - переменные. .
- По умолчанию параметры относятся к типу IN.
ВЫХОД Параметр:
- Этот параметр используется для получения вывода от подпрограмм.
- Это переменная для чтения-записи внутри подпрограмм. Их значения можно изменять внутри подпрограмм.
- В вызывающем операторе эти параметры всегда должны быть переменной для хранения значения из текущих подпрограмм.
Параметр IN OUT:
- Этот параметр используется как для ввода, так и для вывода подпрограмм.
- Это переменная для чтения-записи внутри подпрограмм. Их значения можно изменять внутри подпрограмм.
- В вызывающем операторе эти параметры всегда должны быть переменной для хранения значения из подпрограмм.
Эти типы параметров следует указывать во время создания подпрограмм.
ВОЗВРАЩАТЬСЯ
RETURN - это ключевое слово, которое инструктирует компилятор переключить управление с подпрограммы на вызывающий оператор. В подпрограмме RETURN просто означает, что элемент управления должен выйти из подпрограммы. Как только контроллер найдет ключевое слово RETURN в подпрограмме, код после этого будет пропущен.
Обычно родительский или главный блок вызывает подпрограммы, а затем управление переходит от этого родительского блока к вызываемым подпрограммам. RETURN в подпрограмме вернет управление обратно в их родительский блок. В случае функций оператор RETURN также возвращает значение. Тип данных этого значения всегда упоминается во время объявления функции. Типом данных может быть любой допустимый тип данных PL / SQL.
Что такое процедура в PL / SQL?
Процедура в PL / SQL является подпрограммой блок , который состоит из группы операторов PL / SQL , который может быть назван по имени. Каждая процедура в PL / SQL имеет собственное уникальное имя, по которому на нее можно ссылаться и вызывать. Этот блок подпрограммы в базе данных Oracle хранится как объект базы данных.
Примечание. Подпрограмма - это не что иное, как процедура, и ее необходимо создать вручную в соответствии с требованиями. После создания они будут храниться как объекты базы данных.
Ниже приведены характеристики модуля подпрограммы Процедура в PL / SQL:
- Процедуры - это отдельные блоки программы, которые могут храниться в базе данных.
- Вызов этих процедур PLSQL может быть выполнен путем ссылки на их имя для выполнения операторов PL / SQL.
- В основном он используется для выполнения процесса в PL / SQL.
- Он может иметь вложенные блоки или может быть определен и вложен в другие блоки или пакеты.
- Он содержит часть объявления (необязательно), часть выполнения, часть обработки исключений (необязательно).
- Значения можно передать в процедуру Oracle или получить из процедуры с помощью параметров.
- Эти параметры должны быть включены в вызывающий оператор.
- Процедура в SQL может иметь оператор RETURN для возврата управления вызывающему блоку, но не может возвращать какие-либо значения с помощью оператора RETURN.
- Процедуры не могут быть вызваны непосредственно из операторов SELECT. Их можно вызвать из другого блока или через ключевое слово EXEC.
Синтаксис:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE инструктирует компилятор создать новую процедуру в Oracle. Ключевое слово 'OR REPLACE' указывает компилятору заменить существующую процедуру (если есть) текущей.
- Имя процедуры должно быть уникальным.
- Ключевое слово IS будет использоваться, когда хранимая процедура в Oracle вложена в какие-то другие блоки. Если процедура является автономной, будет использоваться «AS». За исключением этого стандарта кодирования, оба имеют одинаковое значение.
Пример 1: Создание процедуры и ее вызов с помощью EXEC
В этом примере мы собираемся создать процедуру Oracle, которая принимает имя в качестве входных данных и печатает приветственное сообщение в качестве выходных данных. Мы собираемся использовать команду EXEC для вызова процедуры.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Пояснение к коду:
- Строка кода 1 : Создание процедуры с именем «welcome_msg» и одним параметром «p_name» типа «IN».
- Строка кода 4 : печать приветственного сообщения путем объединения имени ввода.
- Процедура составлена успешно.
- Строка кода 7 : Вызов процедуры с помощью команды EXEC с параметром Guru99. Процедура выполняется, и сообщение распечатывается как «Добро пожаловать, Guru99».
Что такое функция?
Функции - это отдельная подпрограмма PL / SQL. Как и процедура PL / SQL, функции имеют уникальное имя, по которому на них можно ссылаться. Они хранятся как объекты базы данных PL / SQL. Ниже приведены некоторые характеристики функций.
- Функции - это отдельный блок, который в основном используется для расчетов.
- Функция использует ключевое слово RETURN для возврата значения, тип данных которого определяется во время создания.
- Функция должна либо возвращать значение, либо вызывать исключение, т.е. возврат в функциях является обязательным.
- Функция без операторов DML может быть вызвана напрямую в запросе SELECT, тогда как функция с операцией DML может быть вызвана только из других блоков PL / SQL.
- Он может иметь вложенные блоки или может быть определен и вложен в другие блоки или пакеты.
- Он содержит часть объявления (необязательно), часть выполнения, часть обработки исключений (необязательно).
- Значения можно передать в функцию или получить из процедуры через параметры.
- Эти параметры должны быть включены в вызывающий оператор.
- Функция PLSQL также может возвращать значение через параметры OUT, кроме использования RETURN.
- Поскольку он всегда будет возвращать значение, в операторе вызова он всегда сопровождается оператором присваивания для заполнения переменных.
Синтаксис
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION инструктирует компилятор создать новую функцию. Ключевое слово OR REPLACE указывает компилятору заменить существующую функцию (если есть) текущей.
- Имя функции должно быть уникальным.
- Следует указать тип данных RETURN.
- Ключевое слово IS будет использоваться, когда процедура вложена в какие-то другие блоки. Если процедура является автономной, будет использоваться «AS». За исключением этого стандарта кодирования, оба имеют одинаковое значение.
Пример 1: Создание функции и ее вызов с помощью анонимного блока
В этой программе мы собираемся создать функцию, которая принимает имя в качестве входных данных и возвращает приветственное сообщение в качестве выходных данных. Мы собираемся использовать анонимный блок и оператор select для вызова функции.
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_msg := welcome_msg_func (‘Guru99’);dbms_output.put_line(lv_msg);END;SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
Пояснение к коду:
- Строка кода 1 : Создание функции Oracle с именем welcome_msg_func и одним параметром p_name типа IN.
- Строка кода 2 : объявление типа возвращаемого значения как VARCHAR2
- Строка кода 5 : возврат объединенного значения «Добро пожаловать» и значения параметра.
- Строка кода 8 : анонимный блок для вызова вышеуказанной функции.
- Строка кода 9 : Объявление переменной с таким же типом данных, что и тип данных, возвращаемый функцией.
- Строка кода 11 : Вызов функции и заполнение возвращаемого значения переменной lv_msg.
- Строка кода 12 : Печать значения переменной. Здесь вы получите «Добро пожаловать, Guru99».
- Строка кода 14 : Вызов той же функции с помощью оператора SELECT. Возвращаемое значение направляется напрямую на стандартный вывод.
Сходства между процедурой и функцией
- Оба могут быть вызваны из других блоков PL / SQL.
- Если исключение, возникшее в подпрограмме, не обрабатывается в разделе обработки исключений подпрограммы, оно распространяется на вызывающий блок.
- Оба могут иметь столько параметров, сколько требуется.
- Оба они рассматриваются как объекты базы данных в PL / SQL.
Процедура Vs. Функция: Ключевые отличия
| Процедура | Функция |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Встроенные функции в PL / SQL
PL / SQL содержит различные встроенные функции для работы со строками и типом данных даты. Здесь мы увидим наиболее часто используемые функции и их использование.
Функции преобразования
Эти встроенные функции используются для преобразования одного типа данных в другой.
| Название функции | использование | Пример |
|---|---|---|
| TO_CHAR | Преобразует другой тип данных в символьный тип данных | TO_CHAR (123); |
| TO_DATE (строка; формат) | Преобразует заданную строку в дату. Строка должна соответствовать формату. | TO_DATE ('2015-ЯНВ-15', 'ГГГГ-ПН-ДД'); Выход: 15.01.2015 |
| TO_NUMBER (текст; формат) | Преобразует текст в числовой тип заданного формата. Информат '9' обозначает количество цифр. | Выберите TO_NUMBER ('1234', '9999') из двойного; Вывод: 1234 Выберите TO_NUMBER ('1,234,45', '9,999,99') из двойного; Выход: 1234 |
Строковые функции
Это функции, которые используются с символьным типом данных.
| Название функции | использование | Пример |
|---|---|---|
| INSTR (текст, строка, начало, появление) | Дает позицию определенного текста в данной строке.
| Выберите INSTR ('AIRPLANE', 'E', 2,1) из двойного выхода : 2 Выберите INSTR ('AIRPLANE', 'E', 2,2) из двойного выхода: 9 (2- е появление E) |
| SUBSTR (текст, начало, длина) | Дает значение подстроки основной строки.
| выберите substr ('airplane', 1,7) из двойного вывода : aeropla |
| ВЕРХНИЙ (текст) | Возвращает верхний регистр предоставленного текста. | Выберите верхний ('guru99') из двойного; Выход : GURU99 |
| НИЖНИЙ (текст) | Возвращает нижний регистр предоставленного текста. | Выберите нижний («самолет») из двойного; Выход : самолет |
| INITCAP (текст) | Возвращает заданный текст с начальной буквой в верхнем регистре. | Выбрать ('guru99') из двойного выхода : Guru99 Выбрать ('моя история') из двойного выхода : Моя история |
| ДЛИНА (текст) | Возвращает длину заданной строки | Выберите ДЛИНА ('guru99') из двойного; Выход : 6 |
| LPAD (текст; длина; pad_char) | Дополняет строку в левой части заданной длины (общей строки) заданным символом | Выберите LPAD ('guru99', 10, '$') из двойного; Вывод : $$$$ guru99 |
| RPAD (текст; длина; pad_char) | Дополняет строку с правой стороны на заданную длину (общую строку) заданным символом | Выберите RPAD ('guru99', 10, '-') из двойного вывода : guru99 ---- |
| LTRIM (текст) | Обрезает начальные пробелы в тексте | Выберите LTRIM ('Guru99') из двойного; Выход : Guru99 |
| RTRIM (текст) | Обрезает конечный пробел в тексте | Выберите RTRIM ('Guru99') из двойного; Выход ; Guru99 |
Дата Функции
Это функции, которые используются для работы с датами.
| Название функции | использование | Пример |
|---|---|---|
| ADD_MONTHS (число, количество месяцев) | Добавляет указанные месяцы к дате | ДОБАВИТЬ_МЕСЯЦ ('2015-01-01', 5); Выход : 01.05.2015 |
| SYSDATE | Возвращает текущую дату и время сервера | Выберите SYSDATE из двойного; Выход : 04.10.2015 14:11:43 |
| TRUNC | Округление переменной даты до меньшего возможного значения | выберите sysdate, TRUNC (sysdate) из двойного; Выход : 04.10.2015 14:12:39 04.10.2015 |
| КРУГЛЫЙ | Округляет дату до ближайшего предела либо выше, либо ниже | Выберите sysdate, ROUND (sysdate) из двойного вывода : 10/4/2015 14:14:34 PM 10/5/2015 |
| MONTHS_BETWEEN | Возвращает количество месяцев между двумя датами. | Выберите MONTHS_BETWEEN (sysdate + 60, sysdate) из двойного выхода : 2 |
Резюме
В этой главе мы узнали следующее.
- Как создать Процедуру и разные способы ее вызова
- Как создать функцию и разные способы ее вызова
- Сходства и различия между процедурой и функцией
- Параметры и общая терминология RETURN в подпрограммах PL / SQL
- Общие встроенные функции в Oracle PL / SQL