PL / SQL — Обзор

Язык программирования PL / SQL был разработан корпорацией Oracle в конце 1980-х годов как процедурный язык расширения для SQL и реляционной базы данных Oracle. Ниже приведены некоторые известные факты о PL / SQL —

• PL / SQL — полностью переносимый, высокопроизводительный язык обработки транзакций.

• PL / SQL предоставляет встроенную, интерпретируемую и независимую от ОС среду программирования.

• PL / SQL также можно напрямую вызывать из интерфейса командной строки SQL * Plus .

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

• Общий синтаксис PL / SQL основан на синтаксисе языков программирования ADA и Pascal.

• Помимо Oracle, PL / SQL доступен в базе данных оперативной памяти TimesTen и в IBM DB2 .

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

PL / SQL предоставляет встроенную, интерпретируемую и независимую от ОС среду программирования.

PL / SQL также можно напрямую вызывать из интерфейса командной строки SQL * Plus .

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

Общий синтаксис PL / SQL основан на синтаксисе языков программирования ADA и Pascal.

Помимо Oracle, PL / SQL доступен в базе данных оперативной памяти TimesTen и в IBM DB2 .

Особенности PL / SQL

PL / SQL имеет следующие особенности —

• PL / SQL тесно интегрирован с SQL.
• Он предлагает обширную проверку ошибок.
• Он предлагает множество типов данных.
• Он предлагает множество структур программирования.
• Он поддерживает структурированное программирование через функции и процедуры.
• Поддерживает объектно-ориентированное программирование.
• Поддерживает разработку веб-приложений и серверных страниц.

Преимущества PL / SQL

PL / SQL имеет следующие преимущества —

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

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

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

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

• Приложения, написанные на PL / SQL, полностью переносимы.

• PL / SQL обеспечивает высокий уровень безопасности.

• PL / SQL обеспечивает доступ к предопределенным пакетам SQL.

• PL / SQL обеспечивает поддержку объектно-ориентированного программирования.

• PL / SQL обеспечивает поддержку разработки веб-приложений и серверных страниц.

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

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

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

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

Приложения, написанные на PL / SQL, полностью переносимы.

PL / SQL обеспечивает высокий уровень безопасности.

PL / SQL обеспечивает доступ к предопределенным пакетам SQL.

PL / SQL обеспечивает поддержку объектно-ориентированного программирования.

PL / SQL обеспечивает поддержку разработки веб-приложений и серверных страниц.

PL / SQL — Настройка среды

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

Для запуска программ PL / SQL на вашем компьютере должен быть установлен сервер Oracle RDBMS. Это позаботится о выполнении команд SQL. Самая последняя версия СУБД Oracle — 11g. Вы можете скачать пробную версию Oracle 11g по следующей ссылке —

Скачать Oracle 11g Express Edition

Вам придется загрузить 32-битную или 64-битную версию установки в соответствии с вашей операционной системой. Обычно есть два файла. Мы скачали 64-битную версию. Вы также будете использовать аналогичные шаги в своей операционной системе, не важно, Linux это или Solaris.

• win64_11gR2_database_1of2.zip

• win64_11gR2_database_2of2.zip

win64_11gR2_database_1of2.zip

win64_11gR2_database_2of2.zip

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

Шаг 1

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

Шаг 2

Вы будете перенаправлены на следующий экран; снимите флажок и нажмите кнопку Продолжить , чтобы продолжить.

Шаг 3

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

Шаг 4

Мы предполагаем, что вы устанавливаете Oracle с основной целью обучения и устанавливаете его на свой ПК или ноутбук. Таким образом, выберите опцию Desktop Class и нажмите кнопку Next для продолжения.

Шаг 5

Укажите место, где вы будете устанавливать сервер Oracle. Просто измените базу Oracle, и другие местоположения будут установлены автоматически. Вы также должны будете предоставить пароль; это будет использоваться системным администратором базы данных. Как только вы предоставите необходимую информацию, нажмите кнопку Далее , чтобы продолжить.

Шаг 6

Снова, нажмите кнопку Далее , чтобы продолжить.

Шаг 7

Нажмите кнопку Готово , чтобы продолжить; это запустит фактическую установку сервера.

Шаг 8

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

Шаг 9

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

Шаг 10

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

Шаг 11

После установки у вас будет следующее заключительное окно.

Заключительный этап

Пришло время проверить вашу установку. В командной строке используйте следующую команду, если вы используете Windows —

sqlplus "/ as sysdba"

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

Текстовый редактор

Запуск больших программ из командной строки может привести к непреднамеренной потере части работы. Всегда рекомендуется использовать командные файлы. Чтобы использовать командные файлы —

• Введите свой код в текстовом редакторе, таком как « Блокнот», «Блокнот +» или « EditPlus» и т. Д.

• Сохраните файл с расширением .sql в домашнем каталоге.

• Запустите командную строку SQL * Plus из каталога, в котором вы создали файл PL / SQL.

• Введите @file_name в командной строке SQL * Plus, чтобы выполнить вашу программу.

Введите свой код в текстовом редакторе, таком как « Блокнот», «Блокнот +» или « EditPlus» и т. Д.

Сохраните файл с расширением .sql в домашнем каталоге.

Запустите командную строку SQL * Plus из каталога, в котором вы создали файл PL / SQL.

Введите @file_name в командной строке SQL * Plus, чтобы выполнить вашу программу.

Если вы не используете файл для выполнения сценариев PL / SQL, просто скопируйте код PL / SQL и щелкните правой кнопкой мыши черное окно, в котором отображается приглашение SQL; используйте параметр вставки, чтобы вставить полный код в командной строке. Наконец, просто нажмите Enter, чтобы выполнить код, если он еще не выполнен.

PL / SQL — основной синтаксис

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

S.No	Разделы и описание
1	Объявления Этот раздел начинается с ключевого слова DECLARE . Это необязательный раздел, в котором определены все переменные, курсоры, подпрограммы и другие элементы, которые будут использоваться в программе.
2	Исполняемые команды Этот раздел заключен между ключевыми словами BEGIN и END и является обязательным разделом. Он состоит из исполняемых инструкций PL / SQL программы. Он должен иметь как минимум одну исполняемую строку кода, которая может быть просто командой NULL, указывающей, что ничего не должно выполняться.
3	Обработка исключений Этот раздел начинается с ключевого слова EXCEPTION . Этот необязательный раздел содержит исключение (я), которые обрабатывают ошибки в программе.

Объявления

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

Исполняемые команды

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

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

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

Каждый оператор PL / SQL заканчивается точкой с запятой (;). Блоки PL / SQL могут быть вложены в другие блоки PL / SQL, используя BEGIN и END . Ниже приведена основная структура блока PL / SQL:

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Пример «Hello World»

DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/ 

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

Hello World  

PL/SQL procedure successfully completed.

Идентификаторы PL / SQL

Идентификаторы PL / SQL — это константы, переменные, исключения, процедуры, курсоры и зарезервированные слова. Идентификаторы состоят из буквы, за которой следуют дополнительные буквы, цифры, знаки доллара, подчеркивания и цифры, и не должны превышать 30 символов.

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

Разделители PL / SQL

Разделитель — это символ с особым значением. Ниже приведен список разделителей в PL / SQL:

Разделитель	Описание
+, -, *, /	Сложение, вычитание / отрицание, умножение, деление
%	Индикатор атрибута
‘	Разделитель символьных строк
,	Селектор компонентов
(,)	Выражение или разделитель списка
:	Индикатор переменной хоста
,	Разделитель элементов
«	Разделитель идентификатора в кавычках
знак равно	Оператор связи
@	Индикатор удаленного доступа
;	Терминатор заявления
знак равно	Оператор присваивания
=>	Оператор ассоциации
||	Оператор конкатенации
**	Оператор экспонирования
<<, >>	Разделитель меток (начало и конец)
/ *, * /	Многострочный разделитель комментариев (начало и конец)
—	Индикатор однострочного комментария
..	Оператор дальности
<,>, <=,> =	Реляционные операторы
<>, ‘=, ~ =, ^ =	Разные версии НЕ РАВНО

Комментарии к PL / SQL

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

PL / SQL поддерживает однострочные и многострочные комментарии. Все символы, доступные внутри любого комментария, игнорируются компилятором PL / SQL. Однострочные комментарии PL / SQL начинаются с разделителя — (двойной дефис), а многострочные комментарии заключаются в / * и * /.

DECLARE 
   -- variable declaration 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   /* 
   *  PL/SQL executable statement(s) 
   */ 
   dbms_output.put_line(message); 
END; 
/

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

Hello World

PL/SQL procedure successfully completed.

Программные блоки PL / SQL

Единица PL / SQL — это одно из следующего:

• PL / SQL блок
• функция
• пакет
• Корпус
• Процедура
• Спусковой крючок
• Тип
• Тип кузова

Каждый из этих блоков будет обсуждаться в следующих главах.

PL / SQL — типы данных

В этой главе мы обсудим типы данных в PL / SQL. Переменные, константы и параметры PL / SQL должны иметь допустимый тип данных, который определяет формат хранения, ограничения и допустимый диапазон значений. Мы сосредоточимся на типах данных SCALAR и LOB в этой главе. Два других типа данных будут рассмотрены в других главах.

S.No	Категория и описание
1	скаляр Отдельные значения без внутренних компонентов, такие как NUMBER, DATE или BOOLEAN .
2	Большой объект (LOB) Указатели на крупные объекты, которые хранятся отдельно от других элементов данных, таких как текст, графические изображения, видеоклипы и звуковые сигналы.
3	композитный Элементы данных, которые имеют внутренние компоненты, к которым можно обращаться по отдельности. Например, коллекции и записи.
4	Ссылка Указатели на другие элементы данных.

скаляр

Отдельные значения без внутренних компонентов, такие как NUMBER, DATE или BOOLEAN .

Большой объект (LOB)

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

композитный

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

Ссылка

Указатели на другие элементы данных.

Скалярные типы данных PL / SQL и подтипы

Скалярные типы данных и подтипы PL / SQL подпадают под следующие категории:

S.No	Тип даты и описание
1	числовой Числовые значения, над которыми выполняются арифметические операции.
2	символ Буквенно-цифровые значения, которые представляют отдельные символы или строки символов.
3	логический Логические значения, над которыми выполняются логические операции.
4	Datetime Даты и время

числовой

Числовые значения, над которыми выполняются арифметические операции.

символ

Буквенно-цифровые значения, которые представляют отдельные символы или строки символов.

логический

Логические значения, над которыми выполняются логические операции.

Datetime

Даты и время

PL / SQL предоставляет подтипы типов данных. Например, тип данных NUMBER имеет подтип INTEGER. Вы можете использовать подтипы в вашей PL / SQL-программе, чтобы сделать типы данных совместимыми с типами данных в других программах, в то же время внедряя код PL / SQL в другую программу, такую ​​как Java-программа.

Числовые типы данных и подтипы PL / SQL

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

S.No	Тип данных и описание
1	PLS_INTEGER Целое число со знаком в диапазоне от -2 147 483 648 до 2 147 483 647, представленное в 32 битах
2	BINARY_INTEGER Целое число со знаком в диапазоне от -2 147 483 648 до 2 147 483 647, представленное в 32 битах
3	BINARY_FLOAT Число с плавающей запятой в формате IEEE 754 одинарной точности
4	BINARY_DOUBLE Число с плавающей точкой в ​​формате IEEE 754 двойной точности
5	НОМЕР (предварительный, масштаб) Число с фиксированной запятой или с плавающей запятой с абсолютным значением в диапазоне от 1E-130 до (но не включая) 1.0E126. Переменная NUMBER также может представлять 0
6	DEC (предварительно, шкала) Специфичный для ANSI тип с фиксированной запятой с максимальной точностью 38 десятичных цифр
7	ДЕСЯТИЧНЫЙ (предварительно, масштаб) Специфичный для IBM тип с фиксированной точкой с максимальной точностью 38 десятичных цифр
8	ЧИСЛЕННЫЙ (до, в секундах) Плавающий тип с максимальной точностью 38 десятичных цифр
9	ДВОЙНАЯ ТОЧНОСТЬ Специфичный для ANSI тип с плавающей точкой с максимальной точностью 126 двоичных цифр (приблизительно 38 десятичных цифр)
10	FLOAT Специфичный для ANSI и IBM тип с плавающей запятой с максимальной точностью 126 двоичных цифр (приблизительно 38 десятичных цифр)
11	INT Специфичный целочисленный тип ANSI с максимальной точностью 38 десятичных цифр
12	INTEGER Специфичный целочисленный тип ANSI и IBM с максимальной точностью 38 десятичных цифр
13	SMALLINT Специфичный целочисленный тип ANSI и IBM с максимальной точностью 38 десятичных цифр
14	РЕАЛЬНЫЙ Тип с плавающей точкой с максимальной точностью 63 двоичных знака (приблизительно 18 десятичных знаков)

PLS_INTEGER

Целое число со знаком в диапазоне от -2 147 483 648 до 2 147 483 647, представленное в 32 битах

BINARY_INTEGER

Целое число со знаком в диапазоне от -2 147 483 648 до 2 147 483 647, представленное в 32 битах

BINARY_FLOAT

Число с плавающей запятой в формате IEEE 754 одинарной точности

BINARY_DOUBLE

Число с плавающей точкой в ​​формате IEEE 754 двойной точности

НОМЕР (предварительный, масштаб)

Число с фиксированной запятой или с плавающей запятой с абсолютным значением в диапазоне от 1E-130 до (но не включая) 1.0E126. Переменная NUMBER также может представлять 0

DEC (предварительно, шкала)

Специфичный для ANSI тип с фиксированной запятой с максимальной точностью 38 десятичных цифр

ДЕСЯТИЧНЫЙ (предварительно, масштаб)

Специфичный для IBM тип с фиксированной точкой с максимальной точностью 38 десятичных цифр

ЧИСЛЕННЫЙ (до, в секундах)

Плавающий тип с максимальной точностью 38 десятичных цифр

ДВОЙНАЯ ТОЧНОСТЬ

Специфичный для ANSI тип с плавающей точкой с максимальной точностью 126 двоичных цифр (приблизительно 38 десятичных цифр)

FLOAT

Специфичный для ANSI и IBM тип с плавающей запятой с максимальной точностью 126 двоичных цифр (приблизительно 38 десятичных цифр)

INT

Специфичный целочисленный тип ANSI с максимальной точностью 38 десятичных цифр

INTEGER

Специфичный целочисленный тип ANSI и IBM с максимальной точностью 38 десятичных цифр

SMALLINT

Специфичный целочисленный тип ANSI и IBM с максимальной точностью 38 десятичных цифр

РЕАЛЬНЫЙ

Тип с плавающей точкой с максимальной точностью 63 двоичных знака (приблизительно 18 десятичных знаков)

Ниже приведена действительная декларация —

DECLARE 
   num1 INTEGER; 
   num2 REAL; 
   num3 DOUBLE PRECISION; 
BEGIN 
   null; 
END; 
/ 

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

PL/SQL procedure successfully completed 

Типы и подтипы символьных данных PL / SQL

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

S.No	Тип данных и описание
1	CHAR Строка символов фиксированной длины с максимальным размером 32 767 байт
2	VARCHAR2 Строка символов переменной длины с максимальным размером 32 767 байт
3	RAW Двоичная или байтная строка переменной длины с максимальным размером 32 767 байт, не интерпретируется PL / SQL
4	NCHAR Строка национального символа фиксированной длины с максимальным размером 32 767 байт
5	NVARCHAR2 Строка национального символа переменной длины с максимальным размером 32 767 байт
6	ДОЛГО Строка символов переменной длины с максимальным размером 32 760 байт
7	ДЛИННАЯ СЫРЬЯ Двоичная или байтная строка переменной длины с максимальным размером 32 760 байт, не интерпретируется PL / SQL
8	ROWID Физический идентификатор строки, адрес строки в обычной таблице
9	UROWID Универсальный идентификатор строки (физический, логический или внешний идентификатор строки)

CHAR

Строка символов фиксированной длины с максимальным размером 32 767 байт

VARCHAR2

Строка символов переменной длины с максимальным размером 32 767 байт

RAW

Двоичная или байтная строка переменной длины с максимальным размером 32 767 байт, не интерпретируется PL / SQL

NCHAR

Строка национального символа фиксированной длины с максимальным размером 32 767 байт

NVARCHAR2

Строка национального символа переменной длины с максимальным размером 32 767 байт

ДОЛГО

Строка символов переменной длины с максимальным размером 32 760 байт

ДЛИННАЯ СЫРЬЯ

Двоичная или байтная строка переменной длины с максимальным размером 32 760 байт, не интерпретируется PL / SQL

ROWID

Физический идентификатор строки, адрес строки в обычной таблице

UROWID

Универсальный идентификатор строки (физический, логический или внешний идентификатор строки)

Булевы типы данных PL / SQL

Тип данных BOOLEAN хранит логические значения, которые используются в логических операциях. Логическими значениями являются логические значения TRUE и FALSE и значение NULL .

Однако SQL не имеет типа данных, эквивалентного BOOLEAN. Следовательно, булевы значения не могут быть использованы в —

• Операторы SQL
• Встроенные функции SQL (такие как TO_CHAR )
• Функции PL / SQL, вызываемые из операторов SQL

Типы даты и времени PL / SQL и интервалов

Тип данных DATE используется для хранения даты и времени фиксированной длины, которые включают время дня в секундах с полуночи. Действительные даты колеблются с 1 января 4712 г. до н.э. до 31 декабря 9999 г. н.э.

Формат даты по умолчанию устанавливается параметром инициализации Oracle NLS_DATE_FORMAT. Например, значением по умолчанию может быть «DD-MON-YY», которое включает двузначное число для дня месяца, сокращение названия месяца и две последние цифры года. Например, 01-ОКТЯБРЬ-12.

Каждая ДАТА включает в себя век, год, месяц, день, час, минуту и ​​секунду. В следующей таблице приведены допустимые значения для каждого поля —

Имя поля	Допустимые значения даты и времени	Допустимые значения интервала
ГОД	От -4712 до 9999 (исключая год 0)	Любое ненулевое целое число
МЕСЯЦ	01 до 12	От 0 до 11
ДЕНЬ	С 01 по 31 (ограничено значениями MONTH и YEAR в соответствии с правилами календаря для локали)	Любое ненулевое целое число
ЧАС	От 00 до 23	От 0 до 23
МИНУТЫ	От 00 до 59	От 0 до 59
ВТОРОЙ	От 00 до 59,9 (n), где 9 (n) — точность долей времени в секундах	От 0 до 59,9 (n), где 9 (n) — точность интервала долей секунд
TIMEZONE_HOUR	От -12 до 14 (диапазон учитывает изменения летнего времени)	Непригодный
TIMEZONE_MINUTE	От 00 до 59	Непригодный
TIMEZONE_REGION	Найдено в динамическом представлении производительности V $ TIMEZONE_NAMES	Непригодный
TIMEZONE_ABBR	Найдено в динамическом представлении производительности V $ TIMEZONE_NAMES	Непригодный

Типы данных больших объектов PL / SQL (LOB)

Типы данных больших объектов (LOB) относятся к большим элементам данных, таким как текст, графические изображения, видеоклипы и звуковые сигналы. Типы данных LOB обеспечивают эффективный случайный кусочный доступ к этим данным. Ниже приведены предопределенные типы данных PL / SQL LOB.

Тип данных	Описание	Размер
BFILE	Используется для хранения больших двоичных объектов в файлах операционной системы за пределами базы данных.	В зависимости от системы. Не может превышать 4 гигабайта (ГБ).
большой двоичный объект	Используется для хранения больших двоичных объектов в базе данных.	От 8 до 128 терабайт (ТБ)
CLOB	Используется для хранения больших блоков символьных данных в базе данных.	От 8 до 128 ТБ
NCLOB	Используется для хранения больших блоков данных NCHAR в базе данных.	От 8 до 128 ТБ

Пользовательские подтипы PL / SQL

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

PL / SQL предопределяет несколько подтипов в пакете STANDARD . Например, PL / SQL предопределяет подтипы CHARACTER и INTEGER следующим образом:

SUBTYPE CHARACTER IS CHAR; 
SUBTYPE INTEGER IS NUMBER(38,0);

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

DECLARE 
   SUBTYPE name IS char(20); 
   SUBTYPE message IS varchar2(100); 
   salutation name; 
   greetings message; 
BEGIN 
   salutation := 'Reader '; 
   greetings := 'Welcome to the World of PL/SQL'; 
   dbms_output.put_line('Hello ' || salutation || greetings); 
END; 
/ 

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

Hello Reader Welcome to the World of PL/SQL 
 
PL/SQL procedure successfully completed. 

NULL в PL / SQL

Значения PL / SQL NULL представляют отсутствующие или неизвестные данные, и они не являются целыми числами, символами или любыми другими конкретными типами данных. Обратите внимание, что NULL не совпадает с пустой строкой данных или нулевым символьным значением ‘\ 0’ . Нуль может быть назначен, но он не может быть приравнен ни к чему, включая себя.

PL / SQL — переменные

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

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

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

Объявление переменных в PL / SQL

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

Синтаксис объявления переменной —

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

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

sales number(10, 2); 
pi CONSTANT double precision := 3.1415; 
name varchar2(25); 
address varchar2(100);

Когда вы предоставляете ограничение размера, масштаба или точности для типа данных, оно называется ограниченным объявлением . Ограниченные объявления требуют меньше памяти, чем неограниченные объявления. Например —

sales number(10, 2); 
name varchar2(25); 
address varchar2(100); 

Инициализация переменных в PL / SQL

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

• Ключевое слово ПО УМОЛЧАНИЮ

• Оператор присваивания

Ключевое слово ПО УМОЛЧАНИЮ

Оператор присваивания

Например —

counter binary_integer := 0; 
greetings varchar2(20) DEFAULT 'Have a Good Day';

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

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

DECLARE 
   a integer := 10; 
   b integer := 20; 
   c integer; 
   f real; 
BEGIN 
   c := a + b; 
   dbms_output.put_line('Value of c: ' || c); 
   f := 70.0/3.0; 
   dbms_output.put_line('Value of f: ' || f); 
END; 
/  

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

Value of c: 30 
Value of f: 23.333333333333333333  

PL/SQL procedure successfully completed. 

Область переменных в PL / SQL

PL / SQL допускает вложение блоков, т. Е. Каждый программный блок может содержать другой внутренний блок. Если переменная объявлена ​​во внутреннем блоке, она не доступна для внешнего блока. Однако, если переменная объявлена ​​и доступна для внешнего блока, она также доступна для всех вложенных внутренних блоков. Существует два типа переменной области видимости —

• Локальные переменные — переменные, объявленные во внутреннем блоке и недоступные для внешних блоков.

• Глобальные переменные — переменные, объявленные во внешнем блоке или пакете.

Локальные переменные — переменные, объявленные во внутреннем блоке и недоступные для внешних блоков.

Глобальные переменные — переменные, объявленные во внешнем блоке или пакете.

В следующем примере показано использование локальных и глобальных переменных в простом виде.

DECLARE 
   -- Global variables  
   num1 number := 95;  
   num2 number := 85;  
BEGIN  
   dbms_output.put_line('Outer Variable num1: ' || num1); 
   dbms_output.put_line('Outer Variable num2: ' || num2); 
   DECLARE  
      -- Local variables 
      num1 number := 195;  
      num2 number := 185;  
   BEGIN  
      dbms_output.put_line('Inner Variable num1: ' || num1); 
      dbms_output.put_line('Inner Variable num2: ' || num2); 
   END;  
END; 
/ 

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

Outer Variable num1: 95 
Outer Variable num2: 85 
Inner Variable num1: 195 
Inner Variable num2: 185  

PL/SQL procedure successfully completed. 

Присвоение результатов SQL-запросов переменным PL / SQL

Вы можете использовать инструкцию SELECT INTO SQL для присвоения значений переменным PL / SQL. Для каждого элемента в списке SELECT должна быть соответствующая, совместимая с типом переменная в списке INTO . Следующий пример иллюстрирует концепцию. Давайте создадим таблицу с именем CUSTOMERS —

( Для операторов SQL, пожалуйста, обратитесь к учебнику по SQL )

CREATE TABLE CUSTOMERS( 
   ID   INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);  

Table Created  

Давайте теперь вставим некоторые значения в таблицу —

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 
 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 ); 

Следующая программа присваивает значения из вышеприведенной таблицы переменным PL / SQL, используя предложение SQL SELECT INTO —

DECLARE 
   c_id customers.id%type := 1; 
   c_name  customers.name%type; 
   c_addr customers.address%type; 
   c_sal  customers.salary%type; 
BEGIN 
   SELECT name, address, salary INTO c_name, c_addr, c_sal 
   FROM customers 
   WHERE id = c_id;  
   dbms_output.put_line 
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); 
END; 
/  

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

Customer Ramesh from Ahmedabad earns 2000  

PL/SQL procedure completed successfully

PL / SQL — константы и литералы

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

Объявление константы

Константа объявляется с использованием ключевого слова CONSTANT . Это требует начального значения и не позволяет этому значению быть измененным. Например —

PI CONSTANT NUMBER := 3.141592654; 
DECLARE 
   -- constant declaration 
   pi constant number := 3.141592654; 
   -- other declarations 
   radius number(5,2);  
   dia number(5,2);  
   circumference number(7, 2); 
   area number (10, 2); 
BEGIN  
   -- processing 
   radius := 9.5;  
   dia := radius * 2;  
   circumference := 2.0 * pi * radius; 
   area := pi * radius * radius; 
   -- output 
   dbms_output.put_line('Radius: ' || radius); 
   dbms_output.put_line('Diameter: ' || dia); 
   dbms_output.put_line('Circumference: ' || circumference); 
   dbms_output.put_line('Area: ' || area); 
END; 
/ 

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

Radius: 9.5 
Diameter: 19 
Circumference: 59.69 
Area: 283.53  

Pl/SQL procedure successfully completed. 

Литералы PL / SQL

Литерал — это явное числовое, символьное, строковое или логическое значение, не представленное идентификатором. Например, TRUE, 786, NULL, ‘tutorialspoint’ — это все литералы типа Boolean, number или string. PL / SQL, литералы чувствительны к регистру. PL / SQL поддерживает следующие виды литералов —

• Числовые литералы
• Символьные литералы
• Строковые литералы
• Булевы литералы
• Дата и время Литералы

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

S.No	Литеральный тип и пример
1	Числовые литералы 050 78 -14 0 +32767 6,6667 0,0 -12,0 3,14159 +7800,00 6E5 1,0E-8 3,14159e0 -1E38 -9,5e-3
2	Символьные литералы ‘A’ ‘%’ ‘9’ » ‘z’ ‘(‘
3	Строковые литералы ‘Привет, мир!’ «Учебное пособие» ’19 -NOV-12′
4	Булевы литералы TRUE, FALSE и NULL.
5	Дата и время Литералы ДАТА «1978-12-25»; TIMESTAMP ‘2012-10-29 12:01:01’;

Числовые литералы

050 78 -14 0 +32767

6,6667 0,0 -12,0 3,14159 +7800,00

6E5 1,0E-8 3,14159e0 -1E38 -9,5e-3

Символьные литералы

‘A’ ‘%’ ‘9’ » ‘z’ ‘(‘

Строковые литералы

‘Привет, мир!’

«Учебное пособие»

’19 -NOV-12′

Булевы литералы

TRUE, FALSE и NULL.

Дата и время Литералы

ДАТА «1978-12-25»;

TIMESTAMP ‘2012-10-29 12:01:01’;

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

DECLARE 
   message  varchar2(30):= 'That''s tutorialspoint.com!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/  

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

That's tutorialspoint.com!  

PL/SQL procedure successfully completed.

PL / SQL — операторы

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

• Арифметические операторы
• Реляционные операторы
• Операторы сравнения
• Логические операторы
• Строковые операторы

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

Арифметические Операторы

В следующей таблице приведены все арифметические операторы, поддерживаемые PL / SQL. Предположим, что переменная A содержит 10, а переменная B содержит 5, тогда —

Показать примеры

оператор	Описание	пример
+	Добавляет два операнда	А + Б даст 15
—	Вычитает второй операнд из первого	А — Б даст 5
*	Умножает оба операнда	А * Б даст 50
/	Делит числитель на числитель	А / Б даст 2
**	Оператор возведения в степень, возводит один операнд в степень другого	А ** Б даст 100000

Операторы отношений

Реляционные операторы сравнивают два выражения или значения и возвращают логический результат. Следующая таблица показывает все реляционные операторы, поддерживаемые PL / SQL. Предположим, что переменная A содержит 10, а переменная B содержит 20, тогда —

Показать примеры

оператор	Описание	пример
знак равно	Проверяет, равны ли значения двух операндов или нет, если да, тогда условие становится истинным.	(A = B) не соответствует действительности.
знак равно <> ~ =	Проверяет, равны ли значения двух операндов или нет, если значения не равны, тогда условие становится истинным.	(A! = B) верно.
>	Проверяет, больше ли значение левого операнда, чем значение правого операнда, если да, тогда условие становится истинным.	(A> B) не соответствует действительности.
<	Проверяет, меньше ли значение левого операнда, чем значение правого операнда, если да, тогда условие становится истинным.	(A <B) верно.
> =	Проверяет, больше ли значение левого операнда или равно значению правого операнда, если да, тогда условие становится истинным.	(A> = B) не соответствует действительности.
<=	Проверяет, меньше ли значение левого операнда или равно значению правого операнда, если да, тогда условие становится истинным.	(A <= B) верно

знак равно

<>

~ =

Операторы сравнения

Операторы сравнения используются для сравнения одного выражения с другим. Результат всегда равен TRUE, FALSE или NULL .

Показать примеры

оператор	Описание	пример
ЛАЙК	Оператор LIKE сравнивает символ, строку или значение CLOB с шаблоном и возвращает значение ИСТИНА, если значение соответствует шаблону, и значение ЛОЖЬ, если это не так.	Если «Zara Ali», например «Z% A_i», возвращает логическое значение «истина», тогда как «Nuha Ali», например «Z% A_i», возвращает логическое значение «ложь».
МЕЖДУ	Оператор BETWEEN проверяет, находится ли значение в указанном диапазоне. x между a и b означает, что x> = a и x <= b.	Если x = 10, то x от 5 до 20 возвращает true, x от 5 до 10 возвращает true, а x от 11 до 20 возвращает false.
В	Оператор IN проверяет членство в наборе. x IN (set) означает, что x равен любому члену множества.	Если x = ‘m’, то x in (‘a’, ‘b’, ‘c’) возвращает логическое значение false, но x in (‘m’, ‘n’, ‘o’) возвращает логическое значение true.
НУЛЕВОЙ	Оператор IS NULL возвращает значение BOOLEAN TRUE, если его операнд равен NULL, или FALSE, если он не равен NULL. Сравнения со значениями NULL всегда дают NULL.	Если x = ‘m’, то ‘x is null’ возвращает логическое значение false.

Логические Операторы

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

Показать примеры

оператор	Описание	Примеры
а также	Вызывается логическим оператором AND. Если оба операнда имеют значение true, условие становится истинным.	(А и В) ложно.
или же	Вызывается оператор логического ИЛИ. Если любой из двух операндов является истинным, тогда условие становится истинным.	(А или В) это правда.
не	Вызывается логическим оператором НЕ. Используется для изменения логического состояния своего операнда. Если условие истинно, то оператор Логический НЕ сделает его ложным.	не (А и В) верно.

Приоритет оператора PL / SQL

Приоритет оператора определяет группировку терминов в выражении. Это влияет на то, как оценивается выражение. Некоторые операторы имеют более высокий приоритет, чем другие; например, оператор умножения имеет более высокий приоритет, чем оператор сложения.

Например, х = 7 + 3 * 2 ; здесь x назначено 13 , а не 20, потому что оператор * имеет более высокий приоритет, чем +, поэтому он сначала умножается на 3 * 2, а затем прибавляется к 7 .

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

Приоритет операторов выглядит следующим образом: =, <,>, <=,> =, <>,! =, ~ =, ^ =, IS NULL, LIKE, BETWEEN, IN.

Показать примеры

оператор	операция
**	экспоненцирование
+, —	личность, отрицание
*, /	умножение, деление
+, -, ||	сложение, вычитание, объединение
сравнение
НЕ	логическое отрицание
А ТАКЖЕ	конъюнкция
ИЛИ ЖЕ	включение

PL / SQL — Условия

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

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

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

S.No	Заявление и описание
1	ЕСЛИ — ТОГДА утверждение Оператор IF связывает условие с последовательностью операторов, заключенных в ключевые слова THEN и END IF . Если условие истинно, операторы выполняются, а если условие ложно или NULL, то оператор IF ничего не делает.
2	IF-THEN-ELSE заявление Оператор IF добавляет ключевое слово ELSE, за которым следует альтернативная последовательность операторов. Если условие ложно или равно NULL, то выполняется только альтернативная последовательность операторов. Это обеспечивает выполнение любой из инструкций.
3	IF-THEN-ELSIF заявление Это позволяет выбирать между несколькими альтернативами.
4	Заявление о ситуации Как и оператор IF, оператор CASE выбирает одну последовательность операторов для выполнения. Однако для выбора последовательности оператор CASE использует селектор, а не несколько логических выражений. Селектор — это выражение, значение которого используется для выбора одной из нескольких альтернатив.
5	Искал CASE заявление В искомом операторе CASE нет селектора , и его предложения WHEN содержат условия поиска, которые дают логические значения.
6	вложенный IF-THEN-ELSE Вы можете использовать один оператор IF-THEN или IF-THEN-ELSIF внутри другого оператора (ов) IF-THEN или IF-THEN-ELSIF .

Оператор IF связывает условие с последовательностью операторов, заключенных в ключевые слова THEN и END IF . Если условие истинно, операторы выполняются, а если условие ложно или NULL, то оператор IF ничего не делает.

Оператор IF добавляет ключевое слово ELSE, за которым следует альтернативная последовательность операторов. Если условие ложно или равно NULL, то выполняется только альтернативная последовательность операторов. Это обеспечивает выполнение любой из инструкций.

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

Как и оператор IF, оператор CASE выбирает одну последовательность операторов для выполнения.

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

В искомом операторе CASE нет селектора , и его предложения WHEN содержат условия поиска, которые дают логические значения.

Вы можете использовать один оператор IF-THEN или IF-THEN-ELSIF внутри другого оператора (ов) IF-THEN или IF-THEN-ELSIF .

PL / SQL — циклы

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

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

Оператор цикла позволяет нам выполнять оператор или группу операторов несколько раз, и в большинстве языков программирования ниже приводится общая форма инструкции цикла.

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

S.No	Тип и описание петли
1	PL / SQL Basic LOOP В этой структуре цикла последовательность операторов заключена между операторами LOOP и END LOOP. На каждой итерации выполняется последовательность операторов, а затем управление возобновляется в верхней части цикла.
2	PL / SQL WHILE LOOP Повторяет оператор или группу операторов, пока данное условие выполняется. Он проверяет условие перед выполнением тела цикла.
3	PL / SQL FOR LOOP Выполнить последовательность операторов несколько раз и сократить код, который управляет переменной цикла.
4	Вложенные циклы в PL / SQL Вы можете использовать один или несколько циклов внутри любого другого базового цикла, в то время как или для цикла.

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

Повторяет оператор или группу операторов, пока данное условие выполняется. Он проверяет условие перед выполнением тела цикла.

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

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

Маркировка цикла PL / SQL

Циклы PL / SQL могут быть помечены. Метка должна быть заключена в двойные угловые скобки (<< и >>) и указываться в начале оператора LOOP. Имя метки также может отображаться в конце оператора LOOP. Вы можете использовать метку в операторе EXIT для выхода из цикла.

Следующая программа иллюстрирует концепцию —

DECLARE 
   i number(1); 
   j number(1); 
BEGIN 
   << outer_loop >> 
   FOR i IN 1..3 LOOP 
      << inner_loop >> 
      FOR j IN 1..3 LOOP 
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j); 
      END loop inner_loop; 
   END loop outer_loop; 
END; 
/

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

i is: 1 and j is: 1 
i is: 1 and j is: 2 
i is: 1 and j is: 3 
i is: 2 and j is: 1 
i is: 2 and j is: 2 
i is: 2 and j is: 3 
i is: 3 and j is: 1 
i is: 3 and j is: 2 
i is: 3 and j is: 3  

PL/SQL procedure successfully completed. 

Операторы управления циклом

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

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

S.No	Контрольное заявление и описание
1	Заявление на выход Оператор Exit завершает цикл, и управление передается оператору сразу после END LOOP.
2	ПРОДОЛЖЕНИЕ заявления Заставляет петлю пропускать оставшуюся часть своего тела и немедленно проверять свое состояние перед повторением.
3	GOTO заявление Передает управление помеченному выражению. Хотя не рекомендуется использовать оператор GOTO в вашей программе.

Оператор Exit завершает цикл, и управление передается оператору сразу после END LOOP.

Заставляет петлю пропускать оставшуюся часть своего тела и немедленно проверять свое состояние перед повторением.

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

PL / SQL — Строки

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

• Строки фиксированной длины — в таких строках программисты задают длину при объявлении строки. Строка дополняется справа пробелами до указанной длины.

• Строки переменной длины. В таких строках указывается максимальная длина строки до 32 767, и заполнение не выполняется.

• Большие символьные объекты (CLOB) — это строки переменной длины, которые могут быть длиной до 128 терабайт.

Строки фиксированной длины — в таких строках программисты задают длину при объявлении строки. Строка дополняется справа пробелами до указанной длины.

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

Большие символьные объекты (CLOB) — это строки переменной длины, которые могут быть длиной до 128 терабайт.

Строки PL / SQL могут быть переменными или литералами. Строковый литерал заключен в кавычки. Например,

'This is a string literal.' Or 'hello world'

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

'this isn''t what it looks like'

Объявление строковых переменных

База данных Oracle предоставляет множество типов строковых данных, таких как CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB и NCLOB. Типы данных с префиксом ‘N’ являются типами данных ‘набор национальных символов’ , в которых хранятся данные символов Unicode.

Если вам нужно объявить строку переменной длины, вы должны указать максимальную длину этой строки. Например, тип данных VARCHAR2. В следующем примере показано объявление и использование некоторых строковых переменных:

DECLARE 
   name varchar2(20); 
   company varchar2(30); 
   introduction clob; 
   choice char(1); 
BEGIN 
   name := 'John Smith'; 
   company := 'Infotech'; 
   introduction := ' Hello! I''m John Smith from Infotech.'; 
   choice := 'y'; 
   IF choice = 'y' THEN 
      dbms_output.put_line(name); 
      dbms_output.put_line(company); 
      dbms_output.put_line(introduction); 
   END IF; 
END; 
/

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

John Smith 
Infotech
Hello! I'm John Smith from Infotech.  

PL/SQL procedure successfully completed

Чтобы объявить строку фиксированной длины, используйте тип данных CHAR. Здесь вам не нужно указывать максимальную длину для переменной фиксированной длины. Если вы отмените ограничение длины, Oracle Database автоматически использует максимальную требуемую длину. Следующие два объявления идентичны —

red_flag CHAR(1) := 'Y'; 
 red_flag CHAR   := 'Y';

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

PL / SQL предлагает оператор конкатенации (||) для объединения двух строк. В следующей таблице представлены строковые функции, предоставляемые PL / SQL —

S.No	Функция и цель
1	ASCII , (х); Возвращает значение ASCII символа x.
2	CHR , (х); Возвращает символ со значением ASCII x.
3	КОНКАТ (х, у); Объединяет строки x и y и возвращает добавленную строку.
4	INITCAP (х); Преобразует начальную букву каждого слова в x в верхний регистр и возвращает эту строку.
5	INSTR (x, find_string [, start] [, вхождение]); Ищет find_string в x и возвращает позицию, в которой это происходит.
6	INSTRB (х); Возвращает расположение строки в другой строке, но возвращает значение в байтах.
7	ДЛИНА (х); Возвращает количество символов в х.
8	LENGTHB (х); Возвращает длину строки символов в байтах для однобайтового набора символов.
9	НИЖНИЙ (х); Преобразует буквы в x в нижний регистр и возвращает эту строку.
10	LPAD (x, width [, pad_string]); Заполняет x пробелами слева, чтобы довести общую длину строки до ширины символов.
11	LTRIM (x [, trim_string]); Обрезает символы слева от x .
12	NANVL (x, значение); Возвращает значение, если x соответствует специальному значению NaN (не число), в противном случае возвращается x .
13	NLS_INITCAP (х); То же, что и функция INITCAP, за исключением того, что она может использовать другой метод сортировки, указанный в NLSSORT.
14	NLS_LOWER (x); То же, что и функция LOWER, за исключением того, что она может использовать другой метод сортировки, указанный в NLSSORT.
15	NLS_UPPER (х); То же, что и функция UPPER, за исключением того, что она может использовать другой метод сортировки, указанный в NLSSORT.
16	NLSSORT (х); Изменяет метод сортировки символов. Должен быть указан перед любой функцией NLS; в противном случае будет использоваться сортировка по умолчанию.
17	NVL (x, значение); Возвращает значение, если x равно нулю; в противном случае возвращается x.
18	NVL2 (x, значение1, значение2); Возвращает значение1, если х не ноль; если x нуль, значение2 возвращается.
19	REPLACE (x, search_string, replace_string); Ищет x для search_string и заменяет его на replace_string.
20	RPAD (x, width [, pad_string]); Колодки х справа.
21	RTRIM (x [, trim_string]); Обрезает х справа.
22	SOUNDEX (x); Возвращает строку, содержащую фонетическое представление x .
23	SUBSTR (x, start [, length]); Возвращает подстроку x, которая начинается в позиции, указанной в start. Дополнительная длина для подстроки может быть предоставлена.
24	SUBSTRB (х); То же, что SUBSTR, за исключением того, что параметры выражаются в байтах вместо символов для однобайтовых систем символов.
25	TRIM ([trim_char FROM) x); Обрезает символы слева и справа от x .
26	ВЕРХНИЙ (х); Преобразует буквы в x в верхний регистр и возвращает эту строку.

ASCII , (х);

Возвращает значение ASCII символа x.

CHR , (х);

Возвращает символ со значением ASCII x.

КОНКАТ (х, у);

Объединяет строки x и y и возвращает добавленную строку.

INITCAP (х);

Преобразует начальную букву каждого слова в x в верхний регистр и возвращает эту строку.

INSTR (x, find_string [, start] [, вхождение]);

Ищет find_string в x и возвращает позицию, в которой это происходит.

INSTRB (х);

Возвращает расположение строки в другой строке, но возвращает значение в байтах.

ДЛИНА (х);

Возвращает количество символов в х.

LENGTHB (х);

Возвращает длину строки символов в байтах для однобайтового набора символов.

НИЖНИЙ (х);

Преобразует буквы в x в нижний регистр и возвращает эту строку.

LPAD (x, width [, pad_string]);

Заполняет x пробелами слева, чтобы довести общую длину строки до ширины символов.

LTRIM (x [, trim_string]);

Обрезает символы слева от x .

NANVL (x, значение);

Возвращает значение, если x соответствует специальному значению NaN (не число), в противном случае возвращается x .

NLS_INITCAP (х);

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

NLS_LOWER (x);

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

NLS_UPPER (х);

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

NLSSORT (х);

Изменяет метод сортировки символов. Должен быть указан перед любой функцией NLS; в противном случае будет использоваться сортировка по умолчанию.

NVL (x, значение);

Возвращает значение, если x равно нулю; в противном случае возвращается x.

NVL2 (x, значение1, значение2);

Возвращает значение1, если х не ноль; если x нуль, значение2 возвращается.

REPLACE (x, search_string, replace_string);

Ищет x для search_string и заменяет его на replace_string.

RPAD (x, width [, pad_string]);

Колодки х справа.

RTRIM (x [, trim_string]);

Обрезает х справа.

SOUNDEX (x);

Возвращает строку, содержащую фонетическое представление x .

SUBSTR (x, start [, length]);

Возвращает подстроку x, которая начинается в позиции, указанной в start. Дополнительная длина для подстроки может быть предоставлена.

SUBSTRB (х);

То же, что SUBSTR, за исключением того, что параметры выражаются в байтах вместо символов для однобайтовых систем символов.

TRIM ([trim_char FROM) x);

Обрезает символы слева и справа от x .

ВЕРХНИЙ (х);

Преобразует буквы в x в верхний регистр и возвращает эту строку.

Давайте теперь потренируемся на нескольких примерах, чтобы понять концепцию —

Пример 1

DECLARE 
   greetings varchar2(11) := 'hello world'; 
BEGIN 
   dbms_output.put_line(UPPER(greetings)); 
    
   dbms_output.put_line(LOWER(greetings)); 
    
   dbms_output.put_line(INITCAP(greetings)); 
    
   /* retrieve the first character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, 1, 1)); 
    
   /* retrieve the last character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, -1, 1)); 
    
   /* retrieve five characters,  
      starting from the seventh position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 7, 5)); 
    
   /* retrieve the remainder of the string, 
      starting from the second position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 2)); 
     
   /* find the location of the first "e" */ 
   dbms_output.put_line ( INSTR (greetings, 'e')); 
END; 
/ 

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

HELLO WORLD 
hello world 
Hello World 
h 
d 
World 
ello World 
2  

PL/SQL procedure successfully completed.

Пример 2

DECLARE 
   greetings varchar2(30) := '......Hello World.....'; 
BEGIN 
   dbms_output.put_line(RTRIM(greetings,'.')); 
   dbms_output.put_line(LTRIM(greetings, '.')); 
   dbms_output.put_line(TRIM( '.' from greetings)); 
END; 
/

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

......Hello World  
Hello World..... 
Hello World  

PL/SQL procedure successfully completed. 

PL / SQL — Массивы

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

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

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

Каждый элемент в массиве имеет индекс, связанный с ним. Он также имеет максимальный размер, который можно динамически изменять.

Создание типа Varray

Тип varray создается с помощью оператора CREATE TYPE . Вы должны указать максимальный размер и тип элементов, хранящихся в varray.

Основной синтаксис для создания типа VARRAY на уровне схемы —

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Куда,

• varray_type_name является допустимым именем атрибута,
• n — количество элементов (максимум) в массиве,
• element_type — это тип данных элементов массива.

Максимальный размер varray можно изменить с помощью инструкции ALTER TYPE .

Например,

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); 
/ 

Type created.

Основной синтаксис для создания типа VARRAY в блоке PL / SQL:

TYPE varray_type_name IS VARRAY(n) of <element_type>

Например —

TYPE namearray IS VARRAY(5) OF VARCHAR2(10); 
Type grades IS VARRAY(5) OF INTEGER;

Давайте теперь потренируемся на нескольких примерах, чтобы понять концепцию —

Пример 1

Следующая программа иллюстрирует использование varrays —

DECLARE 
   type namesarray IS VARRAY(5) OF VARCHAR2(10); 
   type grades IS VARRAY(5) OF INTEGER; 
   names namesarray; 
   marks grades; 
   total integer; 
BEGIN 
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || names(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 
/

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

Total 5 Students 
Student: Kavita  Marks: 98 
Student: Pritam  Marks: 97 
Student: Ayan  Marks: 78 
Student: Rishav  Marks: 87 
Student: Aziz  Marks: 92 

PL/SQL procedure successfully completed. 

Пожалуйста, обратите внимание —

• В среде Oracle начальный индекс для varrays всегда равен 1.

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

• Varrays — это одномерные массивы.

• Varray автоматически становится NULL, когда он объявлен, и должен быть инициализирован, прежде чем на его элементы можно будет ссылаться.

В среде Oracle начальный индекс для varrays всегда равен 1.

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

Varrays — это одномерные массивы.

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

Пример 2

Элементами varray также может быть% ROWTYPE любой таблицы базы данных или% TYPE любого поля таблицы базы данных. Следующий пример иллюстрирует концепцию.

Мы будем использовать таблицу CUSTOMERS, хранящуюся в нашей базе данных, как —

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 

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

DECLARE 
   CURSOR c_customers is 
   SELECT  name FROM customers; 
   type c_list is varray (6) of customers.name%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter + 1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter)); 
   END LOOP; 
END; 
/ 

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

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed. 

PL / SQL — Процедуры

В этой главе мы обсудим процедуры в 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, именованные блоки также будут иметь следующие три части:

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

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

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

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

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

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

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

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

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

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name; 

Куда,

• имя-процедуры определяет имя процедуры.

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

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

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

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

имя-процедуры определяет имя процедуры.

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

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

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

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

пример

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

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/

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

Procedure created.

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

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

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

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

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

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

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

EXECUTE greetings;

Вышеуказанный вызов будет отображаться —

Hello World

PL/SQL procedure successfully completed.

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

BEGIN 
   greetings; 
END; 
/

Вышеуказанный вызов будет отображаться —

Hello World  

PL/SQL procedure successfully completed. 

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

Отдельная процедура удаляется с помощью инструкции DROP PROCEDURE . Синтаксис для удаления процедуры —

DROP PROCEDURE procedure-name; 

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

DROP PROCEDURE greetings; 

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

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

S.No	Параметр Mode & Description
1	В Параметр IN позволяет передавать значение в подпрограмму. Это параметр только для чтения . Внутри подпрограммы параметр IN действует как константа. Ему нельзя присвоить значение. Вы можете передать константу, литерал, инициализированную переменную или выражение в качестве параметра IN. Вы также можете инициализировать его значением по умолчанию; тем не менее, в этом случае он исключается из вызова подпрограммы. Это режим передачи параметров по умолчанию. Параметры передаются по ссылке .
2	ИЗ Параметр OUT возвращает значение вызывающей программе. Внутри подпрограммы параметр OUT действует как переменная. Вы можете изменить его значение и ссылаться на значение после его присвоения. Фактический параметр должен быть переменным, и он передается по значению .
3	ВНЕ Параметр IN OUT передает начальное значение подпрограмме и возвращает обновленное значение вызывающей стороне. Ему может быть присвоено значение, и значение может быть прочитано. Фактический параметр, соответствующий формальному параметру IN OUT, должен быть переменной, а не константой или выражением. Формальному параметру должно быть присвоено значение. Фактический параметр передается по значению.

В

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

ИЗ

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

ВНЕ

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

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

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

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

DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 23; 
   b:= 45; 
   findMin(a, b, c); 
   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END; 
/

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

Minimum of (23, 45) : 23  

PL/SQL procedure successfully completed. 

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

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

DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  
BEGIN 
   a:= 23; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
/

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

Square of (23): 529 

PL/SQL procedure successfully completed.

Методы для передачи параметров

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

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

Позиционная запись

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

findMin(a, b, c, d);

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

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

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

findMin(x => a, y => b, z => c, m => d);

Смешанная запись

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

Следующий вызов является законным —

findMin(a, b, c, m => d);

Однако это не законно:

findMin(x => a, b, c, d); 

PL / SQL — Функции

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

Создание функции

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

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

Куда,

• имя-функции указывает имя функции.

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

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

• Функция должна содержать инструкцию возврата .

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

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

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

имя-функции указывает имя функции.

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

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

Функция должна содержать инструкцию возврата .

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

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

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

пример

В следующем примере показано, как создать и вызвать автономную функцию. Эта функция возвращает общее количество ЗАКАЗЧИКОВ в таблице клиентов.

Мы будем использовать таблицу CUSTOMERS, которую мы создали в главе « Переменные PL / SQL» —

Select * from customers; 
 
+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+  

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 
    
   RETURN total; 
END; 
/ 

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

Function created.

Вызов функции

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

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

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

DECLARE 
   c number(2); 
BEGIN 
   c := totalCustomers(); 
   dbms_output.put_line('Total no. of Customers: ' || c); 
END; 
/

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

Total no. of Customers: 6  

PL/SQL procedure successfully completed. 

пример

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

DECLARE 
   a number; 
   b number; 
   c number; 
FUNCTION findMax(x IN number, y IN number)  
RETURN number 
IS 
    z number; 
BEGIN 
   IF x > y THEN 
      z:= x; 
   ELSE 
      Z:= y; 
   END IF;  
   RETURN z; 
END; 
BEGIN 
   a:= 23; 
   b:= 45;  
   c := findMax(a, b); 
   dbms_output.put_line(' Maximum of (23,45): ' || c); 
END; 
/ 

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

Maximum of (23,45): 45   

PL/SQL procedure successfully completed. 

PL / SQL рекурсивные функции

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

Чтобы проиллюстрировать концепцию, давайте вычислим факториал числа. Факториал числа n определяется как —

n! = n*(n-1)! 
   = n*(n-1)*(n-2)! 
      ... 
   = n*(n-1)*(n-2)*(n-3)... 1 

Следующая программа вычисляет факториал данного числа, вызывая себя рекурсивно —

DECLARE 
   num number; 
   factorial number;  
   
FUNCTION fact(x number) 
RETURN number  
IS 
   f number; 
BEGIN 
   IF x=0 THEN 
      f := 1; 
   ELSE 
      f := x * fact(x-1); 
   END IF; 
RETURN f; 
END;  

BEGIN 
   num:= 6; 
   factorial := fact(num); 
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); 
END; 
/

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

Factorial 6 is 720 
  
PL/SQL procedure successfully completed.

PL / SQL — курсоры

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

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

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

• Неявные курсоры
• Явные курсоры

Неявные курсоры

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

Всякий раз, когда выполняется оператор DML (INSERT, UPDATE и DELETE), с этим оператором связывается неявный курсор. Для операций INSERT курсор содержит данные, которые необходимо вставить. Для операций UPDATE и DELETE курсор определяет строки, которые будут затронуты.

В PL / SQL вы можете ссылаться на самый последний неявный курсор как на курсор SQL , который всегда имеет атрибуты, такие как % FOUND,% ISOPEN,% NOTFOUND и % ROWCOUNT . Курсор SQL имеет дополнительные атрибуты % BULK_ROWCOUNT и % BULK_EXCEPTIONS , предназначенные для использования с оператором FORALL . В следующей таблице приведено описание наиболее часто используемых атрибутов.

S.No	Атрибут и описание
1	%НАЙДЕННЫЙ Возвращает TRUE, если инструкция INSERT, UPDATE или DELETE затронула одну или несколько строк или инструкция SELECT INTO вернула одну или несколько строк. В противном случае он возвращает FALSE.
2	%НЕ НАЙДЕНО Логическая противоположность% FOUND. Он возвращает TRUE, если инструкция INSERT, UPDATE или DELETE не затронула строки, или инструкция SELECT INTO не вернула строки. В противном случае он возвращает FALSE.
3	%ОТКРЫТ Всегда возвращает FALSE для неявных курсоров, потому что Oracle автоматически закрывает курсор SQL после выполнения соответствующего оператора SQL.
4	% ROWCOUNT Возвращает количество строк, затронутых оператором INSERT, UPDATE или DELETE или возвращенных оператором SELECT INTO.

%НАЙДЕННЫЙ

Возвращает TRUE, если инструкция INSERT, UPDATE или DELETE затронула одну или несколько строк или инструкция SELECT INTO вернула одну или несколько строк. В противном случае он возвращает FALSE.

%НЕ НАЙДЕНО

Логическая противоположность% FOUND. Он возвращает TRUE, если инструкция INSERT, UPDATE или DELETE не затронула строки, или инструкция SELECT INTO не вернула строки. В противном случае он возвращает FALSE.

%ОТКРЫТ

Всегда возвращает FALSE для неявных курсоров, потому что Oracle автоматически закрывает курсор SQL после выполнения соответствующего оператора SQL.

% ROWCOUNT

Возвращает количество строк, затронутых оператором INSERT, UPDATE или DELETE или возвращенных оператором SELECT INTO.

Любой атрибут курсора SQL будет доступен как sql% attribute_name, как показано ниже в примере.

пример

Мы будем использовать таблицу CUSTOMERS, которую мы создали и использовали в предыдущих главах.

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

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

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected '); 
   END IF;  
END; 
/      

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

6 customers selected  

PL/SQL procedure successfully completed. 

Если вы проверите записи в таблице клиентов, вы увидите, что строки были обновлены —

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 | 
|  2 | Khilan   |  25 | Delhi     |  2000.00 | 
|  3 | kaushik  |  23 | Kota      |  2500.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7000.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9000.00 | 
|  6 | Komal    |  22 | MP        |  5000.00 | 
+----+----------+-----+-----------+----------+

Явные курсоры

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

Синтаксис для создания явного курсора —

CURSOR cursor_name IS select_statement; 

Работа с явным курсором включает в себя следующие шаги:

• Объявление курсора для инициализации памяти
• Открытие курсора для выделения памяти
• Выбор курсора для получения данных
• Закрытие курсора для освобождения выделенной памяти

Объявление курсора

Объявление курсора определяет курсор с именем и соответствующим оператором SELECT. Например —

CURSOR c_customers IS 
   SELECT id, name, address FROM customers; 

Открытие курсора

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

OPEN c_customers; 

Извлечение курсора

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

FETCH c_customers INTO c_id, c_name, c_addr; 

Закрытие курсора

Закрытие курсора означает освобождение выделенной памяти. Например, мы закроем вышеуказанный курсор следующим образом:

CLOSE c_customers;

пример

Ниже приведен полный пример, иллюстрирующий понятия явных курсоров & minua;

DECLARE 
   c_id customers.id%type; 
   c_name customerS.No.ame%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

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

1 Ramesh Ahmedabad  
2 Khilan Delhi  
3 kaushik Kota     
4 Chaitali Mumbai  
5 Hardik Bhopal   
6 Komal MP  
  
PL/SQL procedure successfully completed. 

PL / SQL — Записи

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

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

PL / SQL может обрабатывать следующие типы записей —

• Таблица на основе
• Курсорные записи
• Пользовательские записи

Табличные записи

Атрибут% ROWTYPE позволяет программисту создавать записи на основе таблиц и на основе курсора .

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

DECLARE 
   customer_rec customers%rowtype; 
BEGIN 
   SELECT * into customer_rec 
   FROM customers 
   WHERE id = 5;  
   dbms_output.put_line('Customer ID: ' || customer_rec.id); 
   dbms_output.put_line('Customer Name: ' || customer_rec.name); 
   dbms_output.put_line('Customer Address: ' || customer_rec.address); 
   dbms_output.put_line('Customer Salary: ' || customer_rec.salary); 
END; 
/

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

Customer ID: 5 
Customer Name: Hardik 
Customer Address: Bhopal 
Customer Salary: 9000 
 
PL/SQL procedure successfully completed.

Записи на основе курсора

Следующий пример иллюстрирует концепцию основанных на курсоре записей. Мы будем использовать таблицу CUSTOMERS, которую мы создали и использовали в предыдущих главах —

DECLARE 
   CURSOR customer_cur is 
      SELECT id, name, address  
      FROM customers; 
   customer_rec customer_cur%rowtype; 
BEGIN 
   OPEN customer_cur; 
   LOOP 
      FETCH customer_cur into customer_rec; 
      EXIT WHEN customer_cur%notfound; 
      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); 
   END LOOP; 
END; 
/

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

1 Ramesh 
2 Khilan 
3 kaushik 
4 Chaitali 
5 Hardik 
6 Komal  

PL/SQL procedure successfully completed. 

Пользовательские записи

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

• заглавие
• автор
• Предмет
• ID книги

Определение записи

Тип записи определяется как —

TYPE 
type_name IS RECORD 
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION], 
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION], 
   ... 
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION); 
record-name  type_name;

Книжная запись объявляется следующим образом —

DECLARE 
TYPE books IS RECORD 
(title  varchar(50), 
   author  varchar(50), 
   subject varchar(100), 
   book_id   number); 
book1 books; 
book2 books; 

Доступ к полям

Чтобы получить доступ к любому полю записи, мы используем оператор точки (.) . Оператор доступа к элементу закодирован как точка между именем переменной записи и полем, к которому мы хотим получить доступ. Ниже приведен пример, объясняющий использование записи:

DECLARE 
   type books is record 
      (title varchar(50), 
      author varchar(50), 
      subject varchar(100), 
      book_id number); 
   book1 books; 
   book2 books; 
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;  
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
  
  -- Print book 1 record 
   dbms_output.put_line('Book 1 title : '|| book1.title); 
   dbms_output.put_line('Book 1 author : '|| book1.author); 
   dbms_output.put_line('Book 1 subject : '|| book1.subject); 
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 
   
   -- Print book 2 record 
   dbms_output.put_line('Book 2 title : '|| book2.title); 
   dbms_output.put_line('Book 2 author : '|| book2.author); 
   dbms_output.put_line('Book 2 subject : '|| book2.subject); 
   dbms_output.put_line('Book 2 book_id : '|| book2.book_id); 
END; 
/

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

Book 1 title : C Programming 
Book 1 author : Nuha Ali 
Book 1 subject : C Programming Tutorial 
Book 1 book_id : 6495407 
Book 2 title : Telecom Billing 
Book 2 author : Zara Ali 
Book 2 subject : Telecom Billing Tutorial 
Book 2 book_id : 6495700  

PL/SQL procedure successfully completed. 

Записи в качестве параметров подпрограммы

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

DECLARE 
   type books is record 
      (title  varchar(50), 
      author  varchar(50), 
      subject varchar(100), 
      book_id   number); 
   book1 books; 
   book2 books;  
PROCEDURE printbook (book books) IS 
BEGIN 
   dbms_output.put_line ('Book  title :  ' || book.title); 
   dbms_output.put_line('Book  author : ' || book.author); 
   dbms_output.put_line( 'Book  subject : ' || book.subject); 
   dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 
   
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;
   
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
   
   -- Use procedure to print book info 
   printbook(book1); 
   printbook(book2); 
END; 
/  

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

Book  title : C Programming 
Book  author : Nuha Ali 
Book subject : C Programming Tutorial 
Book  book_id : 6495407 
Book title : Telecom Billing 
Book author : Zara Ali 
Book subject : Telecom Billing Tutorial 
Book book_id : 6495700  

PL/SQL procedure successfully completed. 

PL / SQL — исключения

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

• Системные исключения
• Пользовательские исключения

Синтаксис для обработки исключений

Общий синтаксис для обработки исключений следующий. Здесь вы можете перечислить столько исключений, сколько вы можете обработать. Исключение по умолчанию будет обработано, используя WHEN THEN —

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

пример

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

DECLARE 
   c_id customers.id%type := 8; 
   c_name customerS.Name%type; 
   c_addr customers.address%type; 
BEGIN 
   SELECT  name, address INTO  c_name, c_addr 
   FROM customers 
   WHERE id = c_id;  
   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!'); 
END; 
/

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

No such customer!  

PL/SQL procedure successfully completed. 

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

Возбуждение исключений

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

DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END; 

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

Пользовательские исключения

PL / SQL позволяет вам определять ваши собственные исключения в соответствии с потребностями вашей программы. Определяемое пользователем исключение должно быть объявлено, а затем явно вызвано с помощью оператора RAISE или процедуры DBMS_STANDARD.RAISE_APPLICATION_ERROR .

Синтаксис объявления исключения:

DECLARE 
   my-exception EXCEPTION; 

пример

Следующий пример иллюстрирует концепцию. Эта программа запрашивает идентификатор клиента, когда пользователь вводит неверный идентификатор, возникает исключение invalid_id .

DECLARE 
   c_id customers.id%type := &cc_id; 
   c_name customerS.Name%type; 
   c_addr customers.address%type;  
   -- user defined exception 
   ex_invalid_id  EXCEPTION; 
BEGIN 
   IF c_id <= 0 THEN 
      RAISE ex_invalid_id; 
   ELSE 
      SELECT  name, address INTO  c_name, c_addr 
      FROM customers 
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
   END IF; 

EXCEPTION 
   WHEN ex_invalid_id THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!');  
END; 
/

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

Enter value for cc_id: -6 (let's enter a value -6) 
old  2: c_id customers.id%type := &cc_id; 
new  2: c_id customers.id%type := -6; 
ID must be greater than zero! 
 
PL/SQL procedure successfully completed. 

Предопределенные исключения

PL / SQL предоставляет множество предопределенных исключений, которые выполняются, когда программа нарушает какое-либо правило базы данных. Например, предопределенное исключение NO_DATA_FOUND возникает, когда инструкция SELECT INTO не возвращает строк. В следующей таблице перечислены некоторые важные предопределенные исключения:

исключение	Ошибка Oracle	SQLCODE	Описание
ACCESS_INTO_NULL	06530	-6530	Он возникает, когда нулевому объекту автоматически присваивается значение.
CASE_NOT_FOUND	06592	-6592	Он возникает, когда не выбран ни один из вариантов в предложении WHEN оператора CASE, и отсутствует предложение ELSE.
COLLECTION_IS_NULL	06531	-6531	Он вызывается, когда программа пытается применить методы сбора, отличные от EXISTS, к неинициализированной вложенной таблице или varray, или программа пытается присвоить значения элементам неинициализированной вложенной таблицы или varray.
DUP_VAL_ON_INDEX	00001	-1	Он вызывается, когда повторяющиеся значения пытаются сохранить в столбце с уникальным индексом.
INVALID_CURSOR	01001	-1001	Он вызывается, когда предпринимаются попытки выполнить недопустимую операцию курсора, например, закрытие неоткрытого курсора.
НЕПРАВИЛЬНЫЙ НОМЕР	01722	-1722	Он возникает, когда преобразование символьной строки в число завершается неудачно, поскольку строка не представляет допустимое число.
LOGIN_DENIED	01017	-1017	Он возникает, когда программа пытается войти в базу данных с неверным именем пользователя или паролем.
ДАННЫЕ НЕ НАЙДЕНЫ	01403	+100	Он вызывается, когда инструкция SELECT INTO не возвращает строк.
NOT_LOGGED_ON	01012	-1012	Он возникает, когда выполняется вызов базы данных без подключения к базе данных.
PROGRAM_ERROR	06501	-6501	Он возникает, когда PL / SQL имеет внутреннюю проблему.
ROWTYPE_MISMATCH	06504	-6504	Он вызывается, когда курсор выбирает значение в переменной, имеющей несовместимый тип данных.
SELF_IS_NULL	30625	-30625	Он вызывается при вызове метода-члена, но экземпляр типа объекта не был инициализирован.
STORAGE_ERROR	06500	-6500	Он возникает, когда PL / SQL исчерпал память или память была повреждена.
TOO_MANY_ROWS	01422	-1422	Он вызывается, когда инструкция SELECT INTO возвращает более одной строки.
VALUE_ERROR	06502	-6502	Он возникает, когда возникает ошибка арифметики, преобразования, усечения или ограничения размера.
ZERO_DIVIDE	01476	1476	Он возникает, когда делается попытка разделить число на ноль.

PL / SQL — Триггеры

В этой главе мы обсудим триггеры в PL / SQL. Триггеры — это хранимые программы, которые автоматически выполняются или запускаются при возникновении некоторых событий. Фактически триггеры записываются для выполнения в ответ на любое из следующих событий:

• Оператор манипулирования базой данных (DML) (DELETE, INSERT или UPDATE)

• Оператор определения базы данных (DDL) (CREATE, ALTER или DROP).

• Операция базы данных (SERVERERROR, LOGON, LOGOFF, STARTUP или SHUTDOWN).

Оператор манипулирования базой данных (DML) (DELETE, INSERT или UPDATE)

Оператор определения базы данных (DDL) (CREATE, ALTER или DROP).

Операция базы данных (SERVERERROR, LOGON, LOGOFF, STARTUP или SHUTDOWN).

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

Преимущества триггеров

Триггеры могут быть написаны для следующих целей —

• Генерация некоторых производных значений столбцов автоматически
• Обеспечение ссылочной целостности
• Регистрация событий и хранение информации о доступе к таблице
• Аудиторская проверка
• Синхронная репликация таблиц
• Внедрение авторизации безопасности
• Предотвращение недействительных транзакций

Создание триггеров

Синтаксис для создания триггера —

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

Куда,

• CREATE [OR REPLACE] TRIGGER trigger_name — Создает или заменяет существующий триггер на trigger_name .

• {ДО | ПОСЛЕ | INSTEAD OF} — указывает, когда будет выполнен триггер. Предложение INSTEAD OF используется для создания триггера в представлении.

• {ВСТАВИТЬ [ИЛИ] | ОБНОВЛЕНИЕ [ИЛИ] | DELETE} — указывает операцию DML.

• [OF col_name] — указывает имя столбца, который будет обновляться.

• [ON table_name] — указывает имя таблицы, связанной с триггером.

• [ССЫЛКА СТАРЫЙ КАК НОВАЯ КАК НЕТ] — Это позволяет ссылаться на новые и старые значения для различных операторов DML, таких как INSERT, UPDATE и DELETE.

• [FOR EACH ROW] — указывает триггер на уровне строки, т. Е. Триггер будет выполняться для каждой затрагиваемой строки. В противном случае триггер будет выполнен только один раз при выполнении оператора SQL, который называется триггером уровня таблицы.

• WHEN (условие) — обеспечивает условие для строк, для которых сработает триггер. Это предложение действительно только для триггеров уровня строки.

CREATE [OR REPLACE] TRIGGER trigger_name — Создает или заменяет существующий триггер на trigger_name .

{ДО | ПОСЛЕ | INSTEAD OF} — указывает, когда будет выполнен триггер. Предложение INSTEAD OF используется для создания триггера в представлении.

{ВСТАВИТЬ [ИЛИ] | ОБНОВЛЕНИЕ [ИЛИ] | DELETE} — указывает операцию DML.

[OF col_name] — указывает имя столбца, который будет обновляться.

[ON table_name] — указывает имя таблицы, связанной с триггером.

[ССЫЛКА СТАРЫЙ КАК НОВАЯ КАК НЕТ] — Это позволяет ссылаться на новые и старые значения для различных операторов DML, таких как INSERT, UPDATE и DELETE.

[FOR EACH ROW] — указывает триггер на уровне строки, т. Е. Триггер будет выполняться для каждой затрагиваемой строки. В противном случае триггер будет выполнен только один раз при выполнении оператора SQL, который называется триггером уровня таблицы.

WHEN (условие) — обеспечивает условие для строк, для которых сработает триггер. Это предложение действительно только для триггеров уровня строки.

пример

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

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 

Следующая программа создает триггер уровня строки для таблицы клиентов, который будет запускаться для операций INSERT или UPDATE или DELETE, выполняемых над таблицей CUSTOMERS. Этот триггер отобразит разницу в зарплате между старыми и новыми значениями —

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/ 

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

Trigger created.

Следующие пункты должны быть рассмотрены здесь —

• Ссылки OLD и NEW недоступны для триггеров на уровне таблицы, вы можете использовать их для триггеров на уровне записи.

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

• Вышеупомянутый триггер был написан таким образом, что он сработает перед любой операцией DELETE, INSERT или UPDATE в таблице, но вы можете записать свой триггер на одну или несколько операций, например, BEFORE DELETE, которая будет срабатывать при каждой записи будет удален с помощью операции УДАЛИТЬ на столе.

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

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

Вышеупомянутый триггер был написан таким образом, что он сработает перед любой операцией DELETE, INSERT или UPDATE в таблице, но вы можете записать свой триггер на одну или несколько операций, например, BEFORE DELETE, которая будет срабатывать при каждой записи будет удален с помощью операции УДАЛИТЬ на столе.

Запуск триггера

Давайте выполним некоторые операции DML над таблицей CUSTOMERS. Вот одна инструкция INSERT, которая создаст новую запись в таблице:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 ); 

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

Old salary: 
New salary: 7500 
Salary difference:

Поскольку это новая запись, старая зарплата недоступна, а приведенный выше результат равен нулю. Теперь выполним еще одну операцию DML над таблицей CUSTOMERS. Оператор UPDATE обновит существующую запись в таблице —

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2; 

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

Old salary: 1500 
New salary: 2000 
Salary difference: 500 

PL / SQL — пакеты

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

Пакет будет состоять из двух обязательных частей —

• Спецификация упаковки
• Тело пакета или определение

Спецификация упаковки

Спецификация — это интерфейс к пакету. Он просто ОБЪЯВЛЯЕТ типы, переменные, константы, исключения, курсоры и подпрограммы, на которые можно ссылаться извне пакета. Другими словами, он содержит всю информацию о содержимом пакета, но исключает код для подпрограмм.

Все объекты, размещенные в спецификации, называются открытыми объектами. Любая подпрограмма, не указанная в спецификации пакета, но закодированная в теле пакета, называется закрытым объектом.

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

CREATE PACKAGE cust_sal AS 
   PROCEDURE find_sal(c_id customers.id%type); 
END cust_sal; 
/

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

Package created.

Корпус

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

Оператор CREATE PACKAGE BODY используется для создания тела пакета. В следующем фрагменте кода показано объявление тела пакета для пакета cust_sal , созданного выше. Я предположил, что у нас уже есть таблица CUSTOMERS, созданная в нашей базе данных, как упомянуто в главе PL / SQL — переменные .

CREATE OR REPLACE PACKAGE BODY cust_sal AS  
   
   PROCEDURE find_sal(c_id customers.id%TYPE) IS 
   c_sal customers.salary%TYPE; 
   BEGIN 
      SELECT salary INTO c_sal 
      FROM customers 
      WHERE id = c_id; 
      dbms_output.put_line('Salary: '|| c_sal); 
   END find_sal; 
END cust_sal; 
/

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

Package body created.

Использование элементов пакета

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

package_name.element_name;

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

DECLARE 
   code customers.id%type := &cc_id; 
BEGIN 
   cust_sal.find_sal(code); 
END; 
/

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

Enter value for cc_id: 1 
Salary: 3000 

PL/SQL procedure successfully completed. 

пример

Следующая программа предоставляет более полный пакет. Мы будем использовать таблицу CUSTOMERS, хранящуюся в нашей базе данных, со следующими записями:

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 | 
|  2 | Khilan   |  25 | Delhi     |  3000.00 | 
|  3 | kaushik  |  23 | Kota      |  3000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9500.00 | 
|  6 | Komal    |  22 | MP        |  5500.00 | 
+----+----------+-----+-----------+----------+

Спецификация пакета

CREATE OR REPLACE PACKAGE c_package AS 
   -- Adds a customer 
   PROCEDURE addCustomer(c_id   customers.id%type, 
   c_name  customerS.No.ame%type, 
   c_age  customers.age%type, 
   c_addr customers.address%type,  
   c_sal  customers.salary%type); 
   
   -- Removes a customer 
   PROCEDURE delCustomer(c_id  customers.id%TYPE); 
   --Lists all customers 
   PROCEDURE listCustomer; 
  
END c_package; 
/

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

Package created.

Создание тела пакета

CREATE OR REPLACE PACKAGE BODY c_package AS 
   PROCEDURE addCustomer(c_id  customers.id%type, 
      c_name customerS.No.ame%type, 
      c_age  customers.age%type, 
      c_addr  customers.address%type,  
      c_sal   customers.salary%type) 
   IS 
   BEGIN 
      INSERT INTO customers (id,name,age,address,salary) 
         VALUES(c_id, c_name, c_age, c_addr, c_sal); 
   END addCustomer; 
   
   PROCEDURE delCustomer(c_id   customers.id%type) IS 
   BEGIN 
      DELETE FROM customers 
      WHERE id = c_id; 
   END delCustomer;  
   
   PROCEDURE listCustomer IS 
   CURSOR c_customers is 
      SELECT  name FROM customers; 
   TYPE c_list is TABLE OF customers.Name%type;  
   name_list c_list := c_list(); 
   counter integer :=0; 
   BEGIN 
      FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter)); 
      END LOOP; 
   END listCustomer;
   
END c_package; 
/

Приведенный выше пример использует вложенную таблицу . Мы обсудим концепцию вложенной таблицы в следующей главе.

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

Package body created.

Использование пакета

Следующая программа использует методы, объявленные и определенные в пакете c_package .

DECLARE 
   code customers.id%type:= 8; 
BEGIN 
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500); 
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500); 
   c_package.listcustomer; 
   c_package.delcustomer(code); 
   c_package.listcustomer; 
END; 
/  

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

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal 
Customer(7): Rajnish 
Customer(8): Subham 
Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal
Customer(7): Rajnish 

PL/SQL procedure successfully completed 

PL / SQL — Коллекции

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

PL / SQL предоставляет три типа коллекций —

• Индексные таблицы или ассоциативный массив
• Вложенный стол
• Массив переменного размера или Varray

Документация Oracle содержит следующие характеристики для каждого типа коллекций:

Тип коллекции	Количество элементов	Тип нижнего индекса	Плотный или разреженный	Где создано	Может быть атрибутом типа объекта
Ассоциативный массив (или индексированная таблица)	неограниченный	Строка или целое число	Или	Только в блоке PL / SQL	нет
Вложенный стол	неограниченный	целое число	Начинается плотно, может стать разреженным	Либо в блоке PL / SQL, либо на уровне схемы	да
Массив переменных размеров (Varray)	ограниченный	целое число	Всегда плотный	Либо в блоке PL / SQL, либо на уровне схемы	да

Мы уже обсуждали varray в главе «Массивы PL / SQL» . В этой главе мы обсудим таблицы PL / SQL.

Оба типа таблиц PL / SQL, т. Е. Таблицы index-by и вложенные таблицы, имеют одинаковую структуру, и доступ к их строкам осуществляется с использованием индексной записи. Однако эти два типа таблиц отличаются в одном аспекте; вложенные таблицы могут храниться в столбце базы данных, а таблицы индекса по — нет.

Индекс по таблице

Таблица index-by (также называемая ассоциативным массивом ) — это набор пар ключ-значение . Каждый ключ уникален и используется для поиска соответствующего значения. Ключ может быть целым числом или строкой.

Индексная таблица создается с использованием следующего синтаксиса. Здесь мы создаем таблицу index-by с именем table_name , ключи которой будут иметь тип subscript_type, а связанные значения будут иметь тип element_type.

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

пример

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

DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- adding elements to the table 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- printing the table 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/

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

Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

пример

Элементами таблицы index-by также может быть % ROWTYPE любой таблицы базы данных или % TYPE любого поля таблицы базы данных. Следующий пример иллюстрирует концепцию. Мы будем использовать таблицу CUSTOMERS, хранящуюся в нашей базе данных, как —

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+  

DECLARE 
   CURSOR c_customers is 
      select name from customers; 

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
   name_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/ 

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

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

Вложенные таблицы

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

• Массив имеет объявленное количество элементов, а вложенная таблица — нет. Размер вложенной таблицы может динамически увеличиваться.

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

Массив имеет объявленное количество элементов, а вложенная таблица — нет. Размер вложенной таблицы может динамически увеличиваться.

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

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

TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name; 

Это объявление аналогично объявлению таблицы index-by , но в ней нет предложения INDEX BY .

Вложенная таблица может храниться в столбце базы данных. Кроме того, его можно использовать для упрощения операций SQL, когда вы объединяете таблицу с одним столбцом и таблицу большего размера. Ассоциативный массив не может быть сохранен в базе данных.

пример

Следующие примеры иллюстрируют использование вложенной таблицы —

DECLARE 
   TYPE names_table IS TABLE OF VARCHAR2(10); 
   TYPE grades IS TABLE OF INTEGER;  
   names names_table; 
   marks grades; 
   total integer; 
BEGIN 
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i IN 1 .. total LOOP 
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
   end loop; 
END; 
/  

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

Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed. 

пример

Элементы вложенной таблицы также могут быть % ROWTYPE любой таблицы базы данных или% TYPE любого поля таблицы базы данных. Следующий пример иллюстрирует концепцию. Мы будем использовать таблицу CUSTOMERS, хранящуюся в нашей базе данных, как —

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 

DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/ 

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

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed. 

Методы сбора

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

S.No	Название метода и цель
1	EXISTS (п) Возвращает TRUE, если n-й элемент в коллекции существует; в противном случае возвращает FALSE.
2	COUNT Возвращает количество элементов, которые в данный момент содержит коллекция.
3	ПРЕДЕЛ Проверяет максимальный размер коллекции.
4	ПЕРВЫЙ Возвращает первые (самые маленькие) индексные числа в коллекции, которая использует целочисленные индексы.
5	ПРОШЛОЙ Возвращает последние (самые большие) индексы в коллекции, которая использует целочисленные индексы.
6	Предшествующий уровень (п) Возвращает номер индекса, который предшествует индексу n в коллекции.
7	NEXT (п) Возвращает номер индекса, который следует за индексом n.
8	ПРОДЛИТЕ Добавляет один нулевой элемент в коллекцию.
9	ПРОДЛИТЕ (п) Добавляет в коллекцию n пустых элементов.
10	ПРОДЛИТЕ (п, я) Добавляет n копий i- го элемента в коллекцию.
11	ОТДЕЛКА Удаляет один элемент из конца коллекции.
12	TRIM (п) Удаляет n элементов из конца коллекции.
13	УДАЛЯТЬ Удаляет все элементы из коллекции, устанавливая COUNT в 0.
14	DELETE (п) Удаляет n- й элемент из ассоциативного массива с числовым ключом или вложенной таблицей. Если ассоциативный массив имеет строковый ключ, элемент, соответствующий значению ключа, удаляется. Если n равно нулю, DELETE (n) ничего не делает.
15	DELETE (т, п) Удаляет все элементы в диапазоне m..n из ассоциативного массива или вложенной таблицы. Если m больше чем n или если m или n равно нулю, DELETE (m, n) ничего не делает.

EXISTS (п)

Возвращает TRUE, если n-й элемент в коллекции существует; в противном случае возвращает FALSE.

COUNT

Возвращает количество элементов, которые в данный момент содержит коллекция.

ПРЕДЕЛ

Проверяет максимальный размер коллекции.

ПЕРВЫЙ

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

ПРОШЛОЙ

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

Предшествующий уровень (п)

Возвращает номер индекса, который предшествует индексу n в коллекции.

NEXT (п)

Возвращает номер индекса, который следует за индексом n.

ПРОДЛИТЕ

Добавляет один нулевой элемент в коллекцию.

ПРОДЛИТЕ (п)

Добавляет в коллекцию n пустых элементов.

ПРОДЛИТЕ (п, я)

Добавляет n копий i- ^го элемента в коллекцию.

ОТДЕЛКА

Удаляет один элемент из конца коллекции.

TRIM (п)

Удаляет n элементов из конца коллекции.

УДАЛЯТЬ

Удаляет все элементы из коллекции, устанавливая COUNT в 0.

DELETE (п)

Удаляет n- ^й элемент из ассоциативного массива с числовым ключом или вложенной таблицей. Если ассоциативный массив имеет строковый ключ, элемент, соответствующий значению ключа, удаляется. Если n равно нулю, DELETE (n) ничего не делает.

DELETE (т, п)

Удаляет все элементы в диапазоне m..n из ассоциативного массива или вложенной таблицы. Если m больше чем n или если m или n равно нулю, DELETE (m, n) ничего не делает.

Коллекция исключений

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

Коллекция Исключение	Поднятый в ситуациях
COLLECTION_IS_NULL	Вы пытаетесь работать с атомарно нулевой коллекцией.
ДАННЫЕ НЕ НАЙДЕНЫ	Индекс обозначает элемент, который был удален, или несуществующий элемент ассоциативного массива.
SUBSCRIPT_BEYOND_COUNT	Индекс превышает количество элементов в коллекции.
SUBSCRIPT_OUTSIDE_LIMIT	Индекс находится за пределами допустимого диапазона.
VALUE_ERROR	Индекс нулевой или не преобразуемый в тип ключа. Это исключение может возникнуть, если ключ определен как диапазон PLS_INTEGER , а нижний индекс находится за пределами этого диапазона.

PL / SQL — Транзакции

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

Успешно выполненный оператор SQL и зафиксированная транзакция не совпадают. Даже если оператор SQL выполняется успешно, если транзакция, содержащая этот оператор, не зафиксирована, его можно откатить, а все изменения, сделанные оператором (ами), можно отменить.

Начало и завершение транзакции

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

• Первый оператор SQL выполняется после подключения к базе данных.

• При каждом новом операторе SQL, который выдается после завершения транзакции.

Первый оператор SQL выполняется после подключения к базе данных.

При каждом новом операторе SQL, который выдается после завершения транзакции.

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

• Выдается оператор COMMIT или ROLLBACK .

• Выдается оператор DDL , например оператор CREATE TABLE ; потому что в этом случае COMMIT выполняется автоматически.

• Выдается инструкция DCL , такая как инструкция GRANT ; потому что в этом случае COMMIT выполняется автоматически.

• Пользователь отключается от базы данных.

• Пользователь выходит из SQL * PLUS , выполнив команду EXIT , автоматически выполняется COMMIT.

• SQL * Plus завершается ненормально, автоматически выполняется ROLLBACK .

• Оператор DML терпит неудачу; в этом случае автоматически выполняется ROLLBACK для отмены этого оператора DML.

Выдается оператор COMMIT или ROLLBACK .

Выдается оператор DDL , например оператор CREATE TABLE ; потому что в этом случае COMMIT выполняется автоматически.

Выдается инструкция DCL , такая как инструкция GRANT ; потому что в этом случае COMMIT выполняется автоматически.

Пользователь отключается от базы данных.

Пользователь выходит из SQL * PLUS , выполнив команду EXIT , автоматически выполняется COMMIT.

SQL * Plus завершается ненормально, автоматически выполняется ROLLBACK .

Оператор DML терпит неудачу; в этом случае автоматически выполняется ROLLBACK для отмены этого оператора DML.

Передача транзакции

Транзакция становится постоянной с помощью команды SQL COMMIT. Общий синтаксис команды COMMIT:

COMMIT;

Например,

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 ); 

COMMIT;

Откат транзакций

Изменения, внесенные в базу данных без COMMIT, можно отменить с помощью команды ROLLBACK.

Общий синтаксис команды ROLLBACK —

ROLLBACK [TO SAVEPOINT < savepoint_name>]; 

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

ROLLBACK;

Точки сохранения

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

Общий синтаксис команды SAVEPOINT —

SAVEPOINT < savepoint_name >;

Например

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 ); 
SAVEPOINT sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000; 
ROLLBACK TO sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 7; 
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 8; 

COMMIT;

ROLLBACK TO sav1 — этот оператор откатывает все изменения до точки, где вы отметили точку сохранения sav1.

После этого начнутся новые изменения.

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

Чтобы выполнить COMMIT автоматически при выполнении команды INSERT, UPDATE или DELETE , вы можете установить переменную среды AUTOCOMMIT как —

SET AUTOCOMMIT ON; 

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

SET AUTOCOMMIT OFF;

PL / SQL — дата и время

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

• Типы данных Datetime
• Интервальные типы данных

Типы данных Datetime —

• ДАТА
• TIMESTAMP
• TIMESTAMP с зоной времени
• TIMESTAMP с локальной зоной времени

Типы данных Interval —

• ИНТЕРВАЛ ГОДА В МЕСЯЦ
• ИНТЕРВАЛЬНЫЙ ДЕНЬ ВТОРОЙ

Значения полей для типов данных Datetime и Interval

Типы данных datetime и interval состоят из полей . Значения этих полей определяют значение типа данных. В следующей таблице перечислены поля и их возможные значения для даты и времени.

Имя поля	Допустимые значения даты и времени	Допустимые значения интервала
ГОД	От -4712 до 9999 (исключая год 0)	Любое ненулевое целое число
МЕСЯЦ	01 до 12	От 0 до 11
ДЕНЬ	С 01 по 31 (ограничено значениями MONTH и YEAR в соответствии с правилами календаря для локали)	Любое ненулевое целое число
ЧАС	От 00 до 23	От 0 до 23
МИНУТЫ	От 00 до 59	От 0 до 59
ВТОРОЙ	От 00 до 59,9 (n), где 9 (n) — точность долей времени в секундах 9 (n) часть не относится к DATE.	От 0 до 59,9 (n), где 9 (n) — точность интервала долей секунд
TIMEZONE_HOUR	От -12 до 14 (диапазон учитывает изменения летнего времени) Не применимо для DATE или TIMESTAMP.	Непригодный
TIMEZONE_MINUTE	От 00 до 59 Не применимо для DATE или TIMESTAMP.	Непригодный
TIMEZONE_REGION	Не применимо для DATE или TIMESTAMP.	Непригодный
TIMEZONE_ABBR	Не применимо для DATE или TIMESTAMP.	Непригодный

От 00 до 59,9 (n), где 9 (n) — точность долей времени в секундах

9 (n) часть не относится к DATE.

От -12 до 14 (диапазон учитывает изменения летнего времени)

Не применимо для DATE или TIMESTAMP.

От 00 до 59

Не применимо для DATE или TIMESTAMP.

Типы данных Datetime и функции

Ниже приведены типы данных Datetime —

ДАТА

Он хранит информацию о дате и времени в типах символов и чисел. Он состоит из информации о столетии, году, месяце, дате, часах, минутах и ​​секундах. Это указано как —

TIMESTAMP

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

TIMESTAMP с зоной времени

Это вариант TIMESTAMP, который включает в себя имя региона часового пояса или смещение часового пояса. Смещение часового пояса — это разница (в часах и минутах) между местным временем и UTC. Этот тип данных полезен для сбора и оценки информации о дате по географическим регионам.

TIMESTAMP с локальной зоной времени

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

В следующей таблице представлены функции Datetime (где x имеет значение datetime) —

S.No	Название и описание функции
1	ADD_MONTHS (x, y); Добавляет y месяцев к x .
2	LAST_DAY (х); Возвращает последний день месяца.
3	MONTHS_BETWEEN (x, y); Возвращает количество месяцев между x и y .
4	NEXT_DAY (x, день); Возвращает дату и время следующего дня после x .
5	НОВОЕ ВРЕМЯ; Возвращает значение времени / дня из часового пояса, указанного пользователем.
6	КРУГЛЫЙ (х [, единица измерения]); Раунды х .
7	SYSDATE (); Возвращает текущую дату и время.
8	TRUNC (x [, единица измерения]); Усеченные х .

ADD_MONTHS (x, y);

Добавляет y месяцев к x .

LAST_DAY (х);

Возвращает последний день месяца.

MONTHS_BETWEEN (x, y);

Возвращает количество месяцев между x и y .

NEXT_DAY (x, день);

Возвращает дату и время следующего дня после x .

НОВОЕ ВРЕМЯ;

Возвращает значение времени / дня из часового пояса, указанного пользователем.

КРУГЛЫЙ (х [, единица измерения]);

Раунды х .

SYSDATE ();

Возвращает текущую дату и время.

TRUNC (x [, единица измерения]);

Усеченные х .

Функции отметки времени (где x имеет значение отметки времени) —

S.No	Название и описание функции
1	CURRENT_TIMESTAMP (); Возвращает TIMESTAMP WITH TIME ZONE, содержащую текущее время сеанса вместе с часовым поясом сеанса.
2	EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} | {TIMEZONE_HOUR | TIMEZONE_MINUTE} | {TIMEZONE_REGION |} TIMEZONE_ABBR) ОТ x) Извлекает и возвращает год, месяц, день, час, минуту, секунду или часовой пояс из x .
3	FROM_TZ (x, часовой пояс); Преобразует TIMESTAMP x и часовой пояс, заданный time_zone, в TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP (); Возвращает TIMESTAMP, содержащий местное время в часовом поясе сеанса.
5	SYSTIMESTAMP (); Возвращает TIMESTAMP WITH TIME ZONE, содержащую текущее время базы данных вместе с часовым поясом базы данных.
6	SYS_EXTRACT_UTC (х); Преобразует TIMESTAMP WITH TIMEZONE x в TIMESTAMP, содержащий дату и время в формате UTC.
7	TO_TIMESTAMP (x, [формат]); Преобразует строку x в TIMESTAMP.
8	TO_TIMESTAMP_TZ (x, [формат]); Преобразует строку x в TIMESTAMP WITH TIMEZONE.

CURRENT_TIMESTAMP ();

Возвращает TIMESTAMP WITH TIME ZONE, содержащую текущее время сеанса вместе с часовым поясом сеанса.

EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} | {TIMEZONE_HOUR | TIMEZONE_MINUTE} | {TIMEZONE_REGION |} TIMEZONE_ABBR) ОТ x)

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

FROM_TZ (x, часовой пояс);

Преобразует TIMESTAMP x и часовой пояс, заданный time_zone, в TIMESTAMP WITH TIMEZONE.

LOCALTIMESTAMP ();

Возвращает TIMESTAMP, содержащий местное время в часовом поясе сеанса.

SYSTIMESTAMP ();

Возвращает TIMESTAMP WITH TIME ZONE, содержащую текущее время базы данных вместе с часовым поясом базы данных.

SYS_EXTRACT_UTC (х);

Преобразует TIMESTAMP WITH TIMEZONE x в TIMESTAMP, содержащий дату и время в формате UTC.

TO_TIMESTAMP (x, [формат]);

Преобразует строку x в TIMESTAMP.

TO_TIMESTAMP_TZ (x, [формат]);

Преобразует строку x в TIMESTAMP WITH TIMEZONE.

Примеры

Следующие фрагменты кода иллюстрируют использование вышеуказанных функций —

Пример 1

SELECT SYSDATE FROM DUAL; 

Выход —

08/31/2012 5:25:34 PM 

Пример 2

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL; 

Выход —

31-08-2012 05:26:14

Пример 3

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Выход —

01/31/2013 5:26:31 PM 

Пример 4

SELECT LOCALTIMESTAMP FROM DUAL; 

Выход —

8/31/2012 5:26:55.347000 PM 

Интервальные типы данных и функции

Ниже приведены типы данных Interval —

• IINTERVAL YEAR TO MONTH — хранит период времени, используя поля даты и года YEAR и MONTH.

• ИНТЕРВАЛЬНЫЙ ДЕНЬ ВТОРОГО — хранит период времени в днях, часах, минутах и ​​секундах.

IINTERVAL YEAR TO MONTH — хранит период времени, используя поля даты и года YEAR и MONTH.

ИНТЕРВАЛЬНЫЙ ДЕНЬ ВТОРОГО — хранит период времени в днях, часах, минутах и ​​секундах.

Интервальные функции

S.No	Название и описание функции
1	NUMTODSINTERVAL (x, interval_unit); Преобразует число х в ИНТЕРВАЛЬНЫЙ ДЕНЬ ВТОРОЙ.
2	NUMTOYMINTERVAL (x, interval_unit); Преобразует число x в ИНТЕРВАЛЬНЫЙ ГОД В МЕСЯЦ.
3	TO_DSINTERVAL (х); Преобразует строку x в ИНТЕРВАЛЬНЫЙ ДЕНЬ ВТОРОЙ.
4	TO_YMINTERVAL (х); Преобразует строку x в ИНТЕРВАЛЬНЫЙ ГОД В МЕСЯЦ.

NUMTODSINTERVAL (x, interval_unit);

Преобразует число х в ИНТЕРВАЛЬНЫЙ ДЕНЬ ВТОРОЙ.

NUMTOYMINTERVAL (x, interval_unit);

Преобразует число x в ИНТЕРВАЛЬНЫЙ ГОД В МЕСЯЦ.

TO_DSINTERVAL (х);

Преобразует строку x в ИНТЕРВАЛЬНЫЙ ДЕНЬ ВТОРОЙ.

TO_YMINTERVAL (х);

Преобразует строку x в ИНТЕРВАЛЬНЫЙ ГОД В МЕСЯЦ.

PL / SQL — вывод СУБД

В этой главе мы обсудим вывод СУБД на PL / SQL. DBMS_OUTPUT — это встроенный пакет, который позволяет отображать выходные данные, информацию об отладке и отправлять сообщения из блоков PL / SQL, подпрограмм, пакетов и триггеров. Мы уже использовали этот пакет на протяжении всего нашего урока.

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

BEGIN 
   dbms_output.put_line  (user || ' Tables in the database:'); 
   FOR t IN (SELECT table_name FROM user_tables) 
   LOOP 
      dbms_output.put_line(t.table_name); 
   END LOOP; 
END; 
/ 

DBMS_OUTPUT Подпрограммы

Пакет DBMS_OUTPUT имеет следующие подпрограммы —

S.No	Подпрограмма и цель
1	DBMS_OUTPUT.DISABLE; Отключает вывод сообщений.
2	DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000); Включает вывод сообщений. Значение NULL в buffer_size представляет неограниченный размер буфера.
3	DBMS_OUTPUT.GET_LINE (строка OUT VARCHAR2, статус OUT INTEGER); Получает одну строку буферизованной информации.
4	DBMS_OUTPUT.GET_LINES (строки OUT CHARARR, строки INUT OUT INTEGER); Извлекает массив строк из буфера.
5	DBMS_OUTPUT.NEW_LINE; Ставит маркер конца строки.
6	DBMS_OUTPUT.PUT (элемент IN VARCHAR2); Помещает частичную строку в буфер.
7	DBMS_OUTPUT.PUT_LINE (элемент IN VARCHAR2); Помещает строку в буфер.

DBMS_OUTPUT.DISABLE;

Отключает вывод сообщений.

DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);

Включает вывод сообщений. Значение NULL в buffer_size представляет неограниченный размер буфера.

DBMS_OUTPUT.GET_LINE (строка OUT VARCHAR2, статус OUT INTEGER);

Получает одну строку буферизованной информации.

DBMS_OUTPUT.GET_LINES (строки OUT CHARARR, строки INUT OUT INTEGER);

Извлекает массив строк из буфера.

DBMS_OUTPUT.NEW_LINE;

Ставит маркер конца строки.

DBMS_OUTPUT.PUT (элемент IN VARCHAR2);

Помещает частичную строку в буфер.

DBMS_OUTPUT.PUT_LINE (элемент IN VARCHAR2);

Помещает строку в буфер.

пример

DECLARE 
   lines dbms_output.chararr; 
   num_lines number; 
BEGIN 
   -- enable the buffer with default size 20000 
   dbms_output.enable; 
   
   dbms_output.put_line('Hello Reader!'); 
   dbms_output.put_line('Hope you have enjoyed the tutorials!'); 
   dbms_output.put_line('Have a great time exploring pl/sql!'); 
  
   num_lines := 3; 
  
   dbms_output.get_lines(lines, num_lines); 
  
   FOR i IN 1..num_lines LOOP 
      dbms_output.put_line(lines(i)); 
   END LOOP; 
END; 
/  

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

Hello Reader! 
Hope you have enjoyed the tutorials! 
Have a great time exploring pl/sql!  

PL/SQL procedure successfully completed.

PL / SQL — объектно-ориентированный

В этой главе мы обсудим объектно-ориентированный PL / SQL. PL / SQL позволяет определять тип объекта, что помогает в проектировании объектно-ориентированной базы данных в Oracle. Тип объекта позволяет создавать составные типы. Использование объектов позволяет реализовывать объекты реального мира со специфической структурой данных и методами их работы. У объектов есть атрибуты и методы. Атрибуты являются свойствами объекта и используются для хранения состояния объекта; и методы используются для моделирования его поведения.

Объекты создаются с помощью оператора CREATE [OR REPLACE] TYPE. Ниже приведен пример создания простого объекта адреса, состоящего из нескольких атрибутов.

CREATE OR REPLACE TYPE address AS OBJECT 
(house_no varchar2(10), 
 street varchar2(30), 
 city varchar2(20), 
 state varchar2(10), 
 pincode varchar2(10) 
); 
/ 

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

Type created. 

Давайте создадим еще одного объекта- клиента, где мы будем связывать атрибуты и методы, чтобы получить объектно-ориентированное чувство —

CREATE OR REPLACE TYPE customer AS OBJECT 
(code number(5), 
 name varchar2(30), 
 contact_no varchar2(12), 
 addr address, 
 member procedure display 
); 
/ 

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

Type created.

Создание объекта

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

DECLARE 
   residence address; 
BEGIN 
   residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301'); 
   dbms_output.put_line('House No: '|| residence.house_no); 
   dbms_output.put_line('Street: '|| residence.street); 
   dbms_output.put_line('City: '|| residence.city); 
   dbms_output.put_line('State: '|| residence.state); 
   dbms_output.put_line('Pincode: '|| residence.pincode); 
END; 
/ 

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

House No: 103A 
Street: M.G.Road 
City: Jaipur 
State: Rajasthan 
Pincode: 201301  

PL/SQL procedure successfully completed. 

Методы участников

Методы-члены используются для манипулирования атрибутами объекта. Вы предоставляете объявление метода-члена при объявлении типа объекта. Тело объекта определяет код для методов-членов. Тело объекта создается с помощью оператора CREATE TYPE BODY.

Конструкторы — это функции, которые возвращают новый объект в качестве его значения. Каждый объект имеет системный метод конструктора. Имя конструктора совпадает с типом объекта. Например —

residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301'); 

Методы сравнения используются для сравнения объектов. Есть два способа сравнить объекты —

Метод карты

Метод Map — это функция, реализованная таким образом, что ее значение зависит от значения атрибутов. Например, для объекта customer, если код клиента одинаков для двух клиентов, оба клиента могут быть одинаковыми. Таким образом, отношения между этими двумя объектами будут зависеть от значения кода.

Способ заказа

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

Используя метод Map

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

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 member procedure display, 
 map member function measure return number 
); 
/ 

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

Type created.

Создание типа тела —

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN  
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   MAP MEMBER FUNCTION measure return number IS 
   BEGIN 
      return (sqrt(length*length + width*width)); 
   END measure; 
END; 
/ 

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

Type body created.

Теперь с помощью объекта прямоугольника и его функций-членов —

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
   r3 rectangle; 
   inc_factor number := 5; 
BEGIN 
   r1 := rectangle(3, 4); 
   r2 := rectangle(5, 7); 
   r3 := r1.enlarge(inc_factor); 
   r3.display;  
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

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

Length: 8 
Width: 9 
Length: 5 
Width: 7  

PL/SQL procedure successfully completed. 

Используя метод заказа

Теперь того же эффекта можно достичь, используя метод заказа . Давайте воссоздадим объект прямоугольника, используя метод заказа —

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member procedure display, 
 order member function measure(r rectangle) return number 
); 
/ 

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

Type created.

Создание типа тела —

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS 
   BEGIN 
      IF(sqrt(self.length*self.length + self.width*self.width)> 
         sqrt(r.length*r.length + r.width*r.width)) then 
         return(1); 
      ELSE 
         return(-1); 
      END IF; 
   END measure; 
END; 
/ 

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

Type body created.

Использование объекта прямоугольника и его функций-членов —

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
BEGIN 
   r1 := rectangle(23, 44); 
   r2 := rectangle(15, 17); 
   r1.display; 
   r2.display; 
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

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

Length: 23 
Width: 44 
Length: 15 
Width: 17 
Length: 23 
Width: 44 

PL/SQL procedure successfully completed.

Наследование для объектов PL / SQL

PL / SQL позволяет создавать объекты из существующих базовых объектов. Для реализации наследования базовые объекты должны быть объявлены как НЕ ФИНАЛЬНЫЕ . По умолчанию установлено значение FINAL .

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

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 NOT FINAL member procedure display) NOT FINAL 
/

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

Type created.

Создание тела базового типа —

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display; 
END; 
/

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

Type body created.

Создание дочернего объекта столешницы —

CREATE OR REPLACE TYPE tabletop UNDER rectangle 
(   
   material varchar2(20), 
   OVERRIDING member procedure display 
) 
/ 

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

Type created.

Создание тела типа для таблицы дочернего объекта

CREATE OR REPLACE TYPE BODY tabletop AS 
OVERRIDING MEMBER PROCEDURE display IS 
BEGIN 
   dbms_output.put_line('Length: '|| length); 
   dbms_output.put_line('Width: '|| width); 
   dbms_output.put_line('Material: '|| material); 
END display; 
/

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

Type body created.

Использование настольного объекта и его функций-членов —

DECLARE 
   t1 tabletop; 
   t2 tabletop; 
BEGIN 
   t1:= tabletop(20, 10, 'Wood'); 
   t2 := tabletop(50, 30, 'Steel'); 
   t1.display; 
   t2.display; 
END;
/

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

Length: 20 
Width: 10 
Material: Wood 
Length: 50 
Width: 30 
Material: Steel  

PL/SQL procedure successfully completed. 

Абстрактные объекты в PL / SQL

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

Например,

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)  
 NOT INSTANTIABLE NOT FINAL 
/

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