Oracle тип record пример

Обновлено: 09.01.2025

PL/SQL (Programming Language for SQL) — язык программирования от компании Oracle, предоставляющий средства для сложной обработки данных. В этой статье мы рассмотрим основы PL/SQL. Начнём с программных единиц.

Программные единицы PL/SQL

К программным единицам в PL/SQL относят:

1. Процедуру — подпрограмму, выполняющую специфическое действие (CREATE PROCEDURE);

2. Функцию — подпрограмму, вычисляющую значение (CREATE FUNCTION).

3. PL/SQL-пакеты — объект БД, группирующий логически связанные типы, подпрограммы и программные объекты PL/SQL. Как правило, пакеты состоят из 2 частей — тела и спецификации. Спецификация представляет собой интерфейс с вашими приложениями (объявляет типы, константы, переменные, исключения, подпрограммы, курсоры — всё, что доступно для использования в пакете). Вторая часть — это тело пакета, полностью определяющая подпрограммы и курсоры, реализуя тем самым спецификацию пакета.

4. Динамический SQL :

a. Native Dynamic SQL (NDS);

5. Опции компилятора .

6. Триггеры — хранимые процедуры особого типа, которые юзер не вызывает непосредственно, т. к. их исполнение обусловлено действиями по модификации данных. Триггеры бывают разные: BEFORE INSERT, BEFORE UPDATE, AFTER INSERT и пр.

7. Управление зависимостями .

8. Хинты либо подсказки (Oracle Hints) — средства, которые позволяют оказывать явное влияние на план запроса. Подсказки определяют общие подходы и цели, оптимизирующие план исполнения запроса, в том числе методы и правила доступа к данным (например, указание метода и порядка соединения таблиц, индекса для доступа к таблице).

Блочная структура PL/SQL

В блоке PL/SQL может содержаться до 4-х разделов, но лишь один считается обязательным.

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

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

3. Раздел исключений . Служит для обработки исключений (предупреждений и ошибок).

4. Исполняемый раздел . Речь идёт о командах, которые выполняются ядром PL/SQL в процессе работы приложения. Обратите внимание, что это ОБЯЗАТЕЛЬНЫЙ раздел.

Давайте посмотрим на структуру PL/SQL-блока для процедуры:

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

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

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

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

Рассмотрим пример спецификации:

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

Что касается тела процедуры, то это блок кода PL/SQL.

Хранимые функции

Функция PL/SQL напоминает процедуру PL/SQL: у неё тоже есть тело и спецификация. Основное различие — функция нужна для возврата значения, которое можно применять в более крупном операторе SQL.

Триггеры

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

К примеру, мы можем писать триггеры, которые срабатывают в процессе выполнения над таблицей операций UPDATE, INSERT либо DELETE; во время выдачи команд DDL; во время входа пользователя в систему либо его выхода; во время запуска либо остановки БД; во время возникновения ошибок.

Существуют 3 различия между процедурами PL/SQL и триггерами:

1. Триггеры невозможно вызвать из кода программы. Они вызываются автоматически как ответ на некоторое событие.

2. У триггеров нет списка параметров.

3. Спецификации триггера и процедуры немного отличаются.

Переменные в PL/SQL

Переменными называют именованные контейнеры. Они способны содержать информацию разных видов. С учётом помещаемой информации данных они имеют разные типы данных, а чтобы отличать эти данные друг от друга, присваиваются имена. К примеру, числа в PL/SQL хранятся в переменных типа NUMBER, текст — в переменных CHAR либо VARCHAR2. Что касается синтаксиса объявления переменной, то в PL/SQL он имеет любую из нижеперечисленных форм записи:

Здесь имя_переменной — любой правильный PL/SQL идентификатор. Такой идентификатор должен:

1. Иметь в длину не больше 30 символов, причём не иметь в записи пробелов и знаков табуляции.

2. Начинаться с буквы.

3. Состоять лишь из букв, цифр 0-9, символа подчеркивания, знака доллара и знака фунта.

4. Не совпадать с зарезервированными словами SQL и PL/SQL, имеющими спецзначение. Пример неправильной записи для переменной — слово BEGIN, обозначающее начало выполняемой секции основного PL/SQL-блока.

Следующее, это тип_данных — любой допустимый тип данных в SQL либо PL/SQL. Здесь обратите внимание, что модификатору NOT NULL требуется, чтобы переменная имела значение. И если модификатор указан, то переменной должно присвоиться значение по умолчанию.

Объявляем константы PL/SQL

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

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

Записи в PL/SQL

Говоря о записи в PL/SQL, мы говорим о наборе данных базовых типов. К записи мы можем обращаться, как к единому целому. При этом для доступа к отдельным полям записи используют нотацию имя_записи_имя_поля. Записи бывают одного из 3-х нижеперечисленных типов:

1. Table-based (основанные на таблице). Такие записи имеют поля, которые совпадают по типу и имени со столбцами таблицы. Когда курсор выбирает всю строку — к примеру, оператором SELECT * FROM примерная_таблица , то возвращаемые записи можно копировать в переменную, имеющую тип записи, которая основана на таблице примерная_таблица .

2. Cursor-based (основанные на курсоре). Что касается полей этих записей, то они совпадают по имени, порядку и типу с заключительным списком столбцов в курсорном операторе SELECT.

3. Programmer-defined (определённые программистом). Тут речь идёт про записи, тип которых определяет программист, то есть вы сами.

Record PL/SQL

Давайте посмотрим, как применять спецификатор записи Record в PL/SQL с синтаксисом.

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

Определяем и объявляем Record в PL/SQL

Объявить запись поможет атрибут %ROWTYPE , представляющий строку в таблице БД без непосредственного перечисления всех столбцов. При этом ваш код будет продолжать функционировать даже после добавления в таблицу столбцов. Но если вы желаете представить подмножество столбцов в таблице либо столбцы из различных таблиц, можно определить VIEW либо объявить CURSOR в целях выбора правильных столбцов и любых нужных объединений, а потом задействовать атрибут %ROWTYPE к CURSOR либо VIEW.

Посмотрим на синтаксис объявления переменной в PL/SQL и определения типа Record:

Познакомившись, достаточно близко с курсорами, я думаю, у вас хватило терпения разобраться со всем этим! :) Далее давайте разберем один довольно интересный тип данных, который очень напоминает структуры в языке C. Обычные скалярные типы, такие как VARCHAR2, NUMBER и т.д. предварительно определены в модуле STANDART. По этому для их использования программе требуется лишь объявить переменную, данного типа, например вот так:

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

Вот таким образом объявляется составной тип RECORD. Как видите для полей записи, можно указывать ограничение NOT NULL, но подобно описанию, переменной вне записи исходное значение и ограничение NOT NULL не обязательны! Давайте запишем для примера вот такой блок:

После его прогона в SQL*Plus получаем:

Как видно процедура успешно выполнилась и вернула значения. Здесь хорошо видно, как мы проинициализировали переменные записи внутри объявления. Так же одно из полей объявленное как NOT NULL сразу получило значение, вследствие того, что при этих условиях это необходимо! Давайте так же рассмотрим блок, где показано, как с использованием правил обращения к полям записи, а именно через точечную нотацию (как в языке Pascal), так же можно переопределить исходные значения полей записи:

После обработки получаем:

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

Что и следовало ожидать, получаем "PLS-00382: выражение неправильного типа"! Все верно, типы то разные! И такое представление не проходит! Но вот присвоение полей этих записей между собой вполне приемлимо! Вот так:

Получаем после обработки:

Вот теперь все правильно! Типы полей совпадают и присвоение проходит нормально! Присвоить значения полям, базы можно и с помощью оператора SELECT, выбрав одну запись из таблицы. Например, давайте запишем такой блок: Здесь объявлена запись на основе типов таблицы CUSTOMERS, и выбрана одна запись из этой таблицы с помощью, оператора INTO, все это отправлено в переменные записи:

Вот таким образом, работает составной тип RECORD. Да, кстати я тут нашел, кое-какие ошибочки и недочеты в прошлых уроках. Я за них извиняюсь, так как материал, очень объемный и сложный! Вы сами можете мне на них указать, я буду рад! При этом сразу оговорюсь ДЕЛАЮ КАК МОГУ И СТАРАЮСЬ ПОМОЧЬ ВСЕМ В ИЗУЧЕНИИ ORACLE! А, отпускать в мой адрес ехидные заковырки, может каждый, а вот реально что-то сделать в этой жизни способны не многие! По этому ошибки в частности в шаге 17 исправлены и ничего страшного я в этом не вижу. :) Что ж, удачи всем и жду конструктивных советов и замечаний!

Кроме типов данных Oracle7, PL/SQL поддерживает несколько дополнительных типов данных и позволяет использовать в своих конструкциях некоторые основные типы с большим диапазоном.

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

Таблицы PL/SQL

Для описания типа данных TABLE используется синтаксис:

TYPE type_name IS TABLE OF < column_type | variable%TYPE |
table.column%TYPE > [NOT NULL] INDEX BY BINARY_INTEGER;

Имя (например, name_plsql_table), которое описывается табличным типом данных, называется таблицей PL/SQL. Это описание, размещаемое в разделе DECLARE, имеет вид: name_plsql_table type_name;
Ссылки на строки таблицы PL/SQL осуществляются аналогично ссылкам на элементы одномерного массива: name_plsql_table(index), где index принадлежит типу BINARY_INTEGER. Например, для ссылки на третью строку в таблице PL/SQL «ename_tab» следует написать: ename_tab(3).

Для присвоения значения конкретной строке таблицы PL/SQL используется синтаксис: name_plsql_table(index) := expr;

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

Записи PL/SQL

Если создаваемая запись (sotr) соответствует описанию столбцов какой-либо базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе DECLARE с помощью атрибута %ROWTYPE: sotr kadry%ROWTYPE;

В противном случае для объявления записи необходимо сначала определить ее тип данных. Для описания типа данных RECORD используется синтаксис:

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

TYPE SotrRecTyp IS RECORD (nomer NUMBER(4) NOT NULL := 1001,

familiy CHAR(20), dolgnost CHAR(14), otdel NUMBER(3) := 102);

Объявление создаваемой записи (например, name_plsql_record) производится в разделе DECLARE и имеет вид: name_plsql_record type_name;
Ссылки на отдельные поля записи осуществляются так: name_plsql_record.field_name;
Для присвоения значения конкретному полю записи используется синтаксис: name_plsql_record.field_name := expr;

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

Переменные, константы и выражения PL/SQL

В программах PL/SQL могут использоваться переменные и константы, описываемые в разделе DECLARE с помощью конструкции вида: variable_name [CONSTANT] type_name [NOT NULL] [ < := | DEFAULT >expr ]
Например:

Запись опубликована 08.04.2010 в 5:50 дп и размещена в рубрике Oracle7 краткий справочник. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

Группирует один или несколько столбцов для формирования нового типа данных.
Эти столбцы будут иметь свое имя и тип данных
Тип записи может принимать данные
- Как одна запись, состоящая из множества столбцов ИЛИ
- Может принимать значение для одного конкретного столбца записи
Синтаксис для объявления на уровне базы данных:

В первом синтаксисе мы видим ключевое слово «CREATE TYPE», которое инструктирует компилятору создать тип записи с именем «type_name_db» с указанным столбцом в качестве объекта базы данных.

Это дается как отдельное утверждение, а не внутри какого-либо блока.

Синтаксис для объявления на уровне подпрограммы:

В синтаксисе мы создаем тип записи с именем «type_name» только внутри подпрограммы.

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

Пример 1: Тип записи как объект базы данных
- EMP_NO (NUMBER)
- EMP_NAME (VARCHAR2 (150))
- МЕНЕДЖЕР (НОМЕР)
- Заработная плата (НОМЕР)
- Приведенный выше код создаст тип emp_det в качестве объекта базы данных.
- Он будет иметь 4 столбца emp_no, emp_name, manager и salary, как определено.
- Теперь emp_det похож на другой тип данных (например, NUMBER, VARCHAR @ и т. Д.) И виден во всей базе данных. Следовательно, это можно использовать во всей базе данных для объявления переменной этого типа.
Вывод:

Создан тип emp_det в качестве типа записи на уровне базы данных.

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

Мы собираемся создать тип записи emp_det на уровне подпрограммы, и мы будем использовать то же самое для заполнения и отображения данных из него.

Вывод:

Объяснение кода:

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

Объяснение кода:

Вывод:

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

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