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);
Программирование в PL/SQL (ORACLE) Урок 1 (часть 1 из 3) / Илья Хохлов
5. Опции компилятора .
6. Триггеры — хранимые процедуры особого типа, которые юзер не вызывает непосредственно, т. к. их исполнение обусловлено действиями по модификации данных. Триггеры бывают разные: BEFORE INSERT, BEFORE UPDATE, AFTER INSERT и пр.
7. Управление зависимостями .
8. Хинты либо подсказки (Oracle Hints) — средства, которые позволяют оказывать явное влияние на план запроса. Подсказки определяют общие подходы и цели, оптимизирующие план исполнения запроса, в том числе методы и правила доступа к данным (например, указание метода и порядка соединения таблиц, индекса для доступа к таблице).
Блочная структура PL/SQL
В блоке PL/SQL может содержаться до 4-х разделов, но лишь один считается обязательным.
2. Раздел объявлений . Включает описания переменных, вложенных блоков и курсоров.
3. Раздел исключений . Служит для обработки исключений (предупреждений и ошибок).
4. Исполняемый раздел . Речь идёт о командах, которые выполняются ядром PL/SQL в процессе работы приложения. Обратите внимание, что это ОБЯЗАТЕЛЬНЫЙ раздел.
Давайте посмотрим на структуру PL/SQL-блока для процедуры:
Следующее изображение — это пример процедуры, содержащей все 4 раздела:
Хранимая процедура
Под хранимой процедурой понимают некоторый перечень инструкций, которые написаны на PL/SQL. Вызов такой процедуры обеспечивает выполнение содержащихся в этой процедуре инструкций. Сама она хранится в БД, поэтому и считается хранимой. Состоит из тела и спецификации.
Спецификация содержит имя процедуры, а также описание её выходных и входных данных, которые являются формальными параметрами либо формальными аргументами. Когда при вызове процедуры указывают параметры командной строки либо иные входные данные, указанные значения называют фактическими параметрами/фактическими аргументами.
Рассмотрим пример спецификации:
В примере выше перед нами процедура с 3-мя формальными параметрами. IN после имени параметра значит, что в процессе вызова процедура может из данного параметра считать входное значение. OUT значит, что процедура может применять этот параметр в целях возврата значения в ту программу, из которой она вызывалась. Комбинация IN OUT значит, что параметр может использоваться в качестве передачи значения процедуре, а также в целях возврата значения.
#08 Основы работы в PL/SQL Developer
Что касается тела процедуры, то это блок кода 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:
Источник: dzen.ru
plsql учебник
Начало работы с PLSQL
В этом разделе представлен обзор того, что такое plsql, и почему разработчик может захотеть его использовать.
Следует также упомянуть о любых крупных предметах в рамках plsql и ссылаться на связанные темы. Поскольку Documentation for plsql является новым, вам может потребоваться создать начальные версии этих связанных тем.
Определение PLSQL
PL / SQL (язык процедур / язык структурированных запросов) является процедурным расширением Oracle Corporation для SQL и реляционной базы данных Oracle. PL / SQL доступен в Oracle Database (начиная с версии 7), в базе данных TimesTen в памяти (начиная с версии 11.2.1) и IBM DB2 (начиная с версии 9.7).
Базовый блок в PL / SQL называется блоком, который состоит из трех частей: декларативной части, исполняемой части и части исключения.
DECLARE BEGIN EXCEPTION END;
Объявления. Этот раздел начинается с ключевого слова DECLARE. Это необязательный раздел и определяет все переменные, курсоры, подпрограммы и другие элементы, которые будут использоваться в программе.
Исполняемые команды — этот раздел заключен между ключевыми словами BEGIN и END, и это обязательный раздел. Он состоит из исполняемых инструкций PL / SQL программы. Он должен иметь по крайней мере одну исполняемую строку кода, которая может быть просто командой NULL, чтобы указать, что ничего не должно выполняться.
Обработка исключений — этот раздел начинается с ключевого слова EXCEPTION. Этот раздел снова является необязательным и содержит исключения (исключения), которые обрабатывают ошибки в программе.
Каждый оператор PL / SQL заканчивается точкой с запятой (;). Блоки PL / SQL могут быть вложены в другие блоки PL / SQL с помощью BEGIN и END.
В анонимном блоке требуется только исполняемая часть блока, другие части не являются обязательными. Ниже приведен пример простого анонимного кода, который не делает ничего, кроме выполнения без сообщений об ошибках.
BEGIN NULL; END; /
Отсутствие команды excecutable приводит к ошибке, поскольку PL / SQL не поддерживает пустые блоки. Например, исключение кода ниже приводит к ошибке:
BEGIN END; /
Приложение вызовет ошибку:
END; * ERROR at line 2: ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol «END» when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with
Символ «*» в строке ниже ключевого слова «END»; означает, что блок, который заканчивается этим блоком, пуст или плохо сконструирован. Каждый блок исполнения нуждается в инструкциях, даже если он ничего не делает, как в нашем примере.
Привет, мир
set serveroutput on DECLARE message constant varchar2(32767):= ‘Hello, World!’; BEGIN dbms_output.put_line(message); END; /
set serveroutput on команд set serveroutput on SQL * Plus и SQL Developer необходим для включения вывода dbms_output . Без команды ничего не отображается.
end; строка сигнализирует о завершении анонимного блока PL / SQL. Чтобы запустить код из командной строки SQL, вам может потребоваться ввести / в начале первой пустой строки после последней строки кода. Когда вышеуказанный код выполняется в приглашении SQL, он производит следующий результат:
Hello, World! PL/SQL procedure successfully completed.
О PLSQL
PL / SQL означает расширение процедурных языков для SQL.
PL / SQL доступен только как «технология разрешения» в других программных продуктах; он не существует как автономный язык. Вы можете использовать PL / SQL в реляционной базе данных Oracle, в Oracle Server и в средствах разработки приложений на стороне клиента, таких как Oracle Forms. Вот некоторые из способов использования PL / SQL:
- Чтобы создать хранимые процедуры. ,
- Чтобы создать триггеры базы данных.
- Для реализации клиентской логики в приложении Oracle Forms.
- Чтобы связать домашнюю страницу World Wide Web с базой данных Oracle.
Разница между% TYPE и% ROWTYPE.
%TYPE : Используется для объявления поля с тем же типом, что и столбец указанной таблицы.
DECLARE vEmployeeName Employee.Name%TYPE; BEGIN SELECT Name INTO vEmployeeName FROM Employee WHERE RowNum = 1; DBMS_OUTPUT.PUT_LINE(vEmployeeName); END; /
% ROWTYPE: используется для объявления записи с теми же типами, что и в указанной таблице, представлении или курсоре (= несколько столбцов).
DECLARE rEmployee Employee%ROWTYPE; BEGIN rEmployee.Name := ‘Matt’; rEmployee.Age := 31; DBMS_OUTPUT.PUT_LINE(rEmployee.Name); DBMS_OUTPUT.PUT_LINE(rEmployee.Age); END; /
Создать или заменить вид
В этом примере мы создадим представление.
Вид в основном используется как простой способ получения данных из нескольких таблиц.
Пример 1:
Просмотр с выбором на один стол.
CREATE OR REPLACE VIEW LessonView AS SELECT L.* FROM Lesson L;
Пример 2:
Просмотр с помощью выбора на нескольких таблицах.
CREATE OR REPLACE VIEW ClassRoomLessonView AS SELECT C.Id, C.Name, L.Subject, L.Teacher FROM ClassRoom C, Lesson L WHERE C.Id = L.ClassRoomId;
Чтобы вызвать эти представления в запросе, вы можете использовать оператор select.
SELECT * FROM LessonView; SELECT * FROM ClassRoomLessonView;
Создать таблицу
Ниже мы создадим таблицу с тремя столбцами.
В столбце Id должен быть заполнен, так что мы определяем его NOT NULL .
В столбце « Contract мы также добавляем чек, чтобы разрешалось только «Y» или «N». Если вставленная вставка и этот столбец не указаны во время вставки, то по умолчанию вставлен «N».
CREATE TABLE Employee ( Id NUMBER NOT NULL, Name VARCHAR2(60), Contract CHAR DEFAULT ‘N’ NOT NULL, — CONSTRAINT p_Id PRIMARY KEY(Id), CONSTRAINT c_Contract CHECK (Contract IN(‘Y’,’N’)) );
Источник: learntutorials.net
PL/SQL. Начало. Обзор основных возможностей
Данная статья – первая в цикле о языке программирования PL/SQL, использующегося для работы с базами данных Oracle. Мы рассмотрим:
- Язык PL/SQL
- PL/SQL Block
- Package and Triggers
- Переменные
- Типы переменных
- Правила именования переменных
Язык PL/SQL
SQL является декларативным языком программирования, а это в свою очередь накладывает определенные ограничения на него. В частности нет прямой поддержки циклов. Преимуществом является простота и слабые требования к мощности сервера.
Длительное время чистого sql хватало для работы с данными, но с ростом мощности серверов (увеличение процессоров, оперативной памяти, кэш-памяти и т.д.) стало понятно, что многие операции для работы с данными, можно выполнять еще на уровне базы данных. Для этой цели компания Oracle, разработала язык программирования PL/SQL (Procedural Language/SQL), который является надстройкой над языком SQL. Это дает возможность использовать чистый sql в pl/sql.
PL/SQL Block
Блок pl/sql выглядит следующим образом:
Источник: nicholasgribanov.name