Основы языка pl/sql

Содержание:

Процедуры

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

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

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

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

Процедуры, функции и пакеты

Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:

В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:

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

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

В листинге А.6 приведен пример простого пакета Oracle.

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

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL… 2181 просмотров Rasen Fasenger Thu, 16 Jul 2020, 06:20:48

Встроенные методы коллекций PL… 4431 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Тип данных RAW в PL/SQL 3606 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Использование записей (records… 5998 просмотров Алексей Вятский Thu, 05 Jul 2018, 07:49:43

Author: Игорь Воронов

Другие статьи автора:

Пример с совокупным присвоением

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

Oracle PL/SQL

DECLARE
dept_rec1 departments%ROWTYPE;
dept_rec2 departments%ROWTYPE;
CURSOR c1 IS SELECT department_id, location_id FROM departments;
dept_rec3 c1%ROWTYPE;
BEGIN
dept_rec1 := dept_rec2; — допустимо
— dept_rec2 относится к таблице, dept_rec3 относится к курсору
— dept_rec2 := dept_rec3; — недопустимо
END;

1
2
3
4
5
6
7
8
9
10

DECLARE

dept_rec1departments%ROWTYPE;

dept_rec2departments%ROWTYPE;

CURSORc1ISSELECTdepartment_id,location_idFROMdepartments;

dept_rec3c1%ROWTYPE;
BEGIN

dept_rec1:=dept_rec2;— допустимо

— dept_rec2 относится к таблице, dept_rec3 относится к курсору
— dept_rec2 := dept_rec3; — недопустимо

END;

В этом примере Oracle PL/SQL мы объявили переменные dept_rec1, dept_rec2 основанные на записи таблицы departments. Также мы объявили курсор c1 содержащий поля department_id, location_id таблицы departments. Т.к. таблица departments представлена в базе данных полями department_id, department_name, manager_id, location_id, то присвоение dept_rec2 := dept_rec3 недопустимо. Следующий пример показывает, как присвоить список значений столбцов записи с помощью инструкции SELECT.

Oracle PL/SQL

DECLARE
dept_rec departments%ROWTYPE;
BEGIN
SELECT * INTO dept_rec FROM departments
WHERE department_id = 30 and ROWNUM < 2;
END;

1
2
3
4
5
6

DECLARE

dept_recdepartments%ROWTYPE;
BEGIN

SELECT*INTOdept_recFROMdepartments

WHEREdepartment_id=30andROWNUM<2;

END;

В этом примере переменной dept_rec присваивается значение записи из таблицы departments со значением department_id равным 30.

Использование Nested Tables

Вложенная таблица подходит, если:

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

Данные Nested Tables хранятся в отдельной хранимой таблице, таблице базы данных, сгенерированной системой. Когда вы обращаетесь к Nested Tables, база данных join (присоединяет) Nested Tables к своей сохраненной таблице. Это делает Nested Tables подходящими для запросов и обновлений, которые затрагивают только некоторые элементы коллекции. Вы должны создать отдельную таблицу поиска с несколькими записями для каждой строки основной таблицы и получить доступ к ней через запросы присоединения.

Объявления с привязкой

При объявлении переменной тип данных очень часто задается явно:

В Oracle также существует другой метод объявления переменных, называемый объявлением с привязкой (anchored declaration). Он особенно удобен в тех случаях, когда значение переменной присваивается из другого источника данных, например из строки таблицы.

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

В PL/SQL существует два вида привязки.

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

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

имя_переменной тип_атрибута %TYPE ;
имя_переменной имя_таблицы | имя_курсора %ROWTYPE ;

Здесь имя_переменной — это имя объявляемой переменной, тип_атрибута — либо имя ранее объявленной переменной PL/SQL, либо спецификация столбца таблицы в формате таблица.столбец.

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

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

Рис. 1. Атрибут TYPE при объявлении с привязкой

Пример привязки переменной к столбцу таблицы:

Аналогичным образом выполняется привязка к переменной PL/SQL; обычно это делается для того, чтобы избежать избыточных объявлений одного и того же жестко закодированного типа данных. В таких случаях в пакете обычно создается переменная, на которую затем ссылаются в программах с помощью атрибута . (Также можно создавать в пакете подтипы ; эта тема рассматривается далее в этой главе.) В следующем примере приведен фрагмент кода пакета, упрощающего использование Oracle Advanced Queuing (AQ):

В пакете тип данных для хранения идентификаторов сообщений определен как RAW(16). Вместо того чтобы помнить эту спецификацию (и несколько раз жестко кодировать ее в приложении), можно объявить переменную для идентификатора сообщения следующим образом:

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

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

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

Синтаксис

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

TYPE type_rec_name IS Record (field_1 datatype, field_2 datatype, … field_n datatype); var_rec type_rec_name;

Параметры или аргументы

type_rec_name – имя определенного типа Recordvar_rec – имя переменной типа Recordfield_1, field_2,… field_n – поля типа Recorddatatype – тип данных для полей типа Record. Может быть любой из списка:

Типы данных БД	Описание
collection_name	Коллекция (associative array, nested table или varray), ранее объявленная в текущей области.
collection_type_name	Пользовательский тип коллекции, определенный с использованием спецификатора типа данных TABLE или VARRAY.
CONSTANT	Обозначает объявление константы. Вы должны инициализировать константу при объявлении. После инициализации значение константы не может быть изменено.
constant_name	Программная константа.
cursor_name	Явный курсор, ранее объявленный в текущей области.
cursor_variable_name	Переменная курсора PL/SQL, ранее объявленная в текущей области.
db_table_name	Таблица базы данных или представление, которые должны быть доступны при декларировании.
db_table_name.column_name	Таблица базы данных и столбец, которые должны быть доступны при декларировании.
expression	Комбинация переменных, констант, литералов, операторов и вызовов функций.Простейшее выражение состоит из одной переменной. Когда декларирование в разработке, значение выражения присваивается константе или переменной.Значение и константы или переменной должны иметь совместимые типы данных.
NOT NULL	Ограничение, которое запрещает программе присваивать значение null переменной или константе. Присвоение значения null переменной, определенной как NOT NULL, вызывает предопределенное исключение VALUE_ERROR. Ограничению NOT NULL должно следовать в предложении инициализации.
object_name	Экземпляр типа объекта, ранее объявленного в текущей области.
record_name	Пользовательская или запись %ROWTYPE, ранее объявленная в текущей области.
record_name.field_name	Поле в пользовательской или %ROWTYPE записи, ранее объявленной в текущей области.
record_type_name	Пользовательский тип записи, который определяется с помощью спецификатора типа данных RECORD.
ref_cursor_type_name	Пользовательский тип переменной курсора, определенный с помощью спецификатора типа данных REF CURSOR.
%ROWTYPE	Представляет запись, которая может содержать строку из таблицы базы данных или курсора. Поля в записи имеют одинаковые имена и типы данных в виде столбцов в строке.
scalar_datatype_name	Предопределенный скалярный тип данных, такой как BOOLEAN, NUMBER или VARCHAR2. Включает в себя любые квалификаторы для семантики размера, точности или символа в сравнении с байтом.
%TYPE	Представляет тип данных ранее объявленной коллекции, переменной курсора, полем, объекту, записи, столбцу базы данных или переменной.
variable_name	Программная переменная.

Пример объявления более одной переменной с начальным значением.

Рассмотрим, как объявить более одной переменной и присвоить начальные значения этим переменным в SQL Server. Например:

Transact-SQL

DECLARE @mysite VARCHAR(50) = ‘Пример’,
@mysite_id INT = 11;

1 2	DECLARE@mysiteVARCHAR(50)=’Пример’, @mysite_idINT=11;

В этом примере мы объявляем две переменные, и обеим этим переменным присваиваются начальные значения.

Первая переменная называется @mysite, которая определяется как VARCHAR (50) ей присваивается начальное значение «Пример».
Вторая переменная называется @mysite_id, которая объявляется как INT и ей присваивается значение начальное значение 11.