Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Язык PL/SQL
PL/SQL — это собственный язык Oracle, предназначенный для разработки приложений баз данных. В дополнение ко всем SQL-операциям, поддерживаемым в Oracle SQL, PL/SQL расширен такими программными средствами, как обработка условных операторов, циклы, переменные, операции с курсорами, абстрактные типы данных, модуляризация, инкапсуляция, переопределение и др.
Достоинства PL/SQL:
Многие приложения, в которых применяется архитектура клиент/сервер, роднит одна черта — сложность поддер жания бизнес-правил приложения. Когда бизнес-правила распределяются по всему приложению (децентрализуются), разработчикам приходится вносить различные изменения и проверять систему на достаточность этих изменений. Однако при напряженном графике работы первое в процессе внедрения, от чего почти наверняка придется отказаться,— это проверка. В подобной ситуации следует изменить логи ческий проект так, чтобы логические схемы приложения концентрировались в одном месте, что упростит внесение изменений. В системах, использующих базу данных Oracle, "средний уровень" прикладной логики можно построить с помощью PL/SQL.
Децентрализация вычислений повысила возможности организаций по предостав лению быстродействующих и простых в применении приложений для своих клиентов. Однако изменить схемы бизнес-логики, хранимые в клиентском приложении, можно, только запрограммировав эти изменения, перекомпилировав клиентское приложение (возможно, на нескольких различных платформах) и установив его новые исполняемые версии на рабочем столе каждого пользователя. Кроме того, следует учесть и накладные расходы на связь и техническую поддержку, гарантирующие правильность версии приложения у всех пользователей.
Централизация даже небольшого уровня повышает эффективность работы, позволяя избежать распространения изменений в схемах бизнес-логики и давая возможность разработчикам сосредоточить все свое внимание на создании клиентского приложения. Централизованное хранение прикладной логики, например в виде хра нимых процедур PL/SQL, требует лишь однократной компиляции изменения, после чего оно становится доступным всем пользователям приложения.
На рис. 1 приведен пример, демонстрирующий различия между централизован ным и децентрализованным способами управления программными схемами бизнес-логики.
Рис. 1 Сравнение централизованного и децентрализованного управления программными схемами бизнес-логики
Программные конструкции PL/SQL
В PL/SQL имеется множество различных программных конструкций, начиная с модулей разных видов и заканчивая логическими конструкциями, управляющими потоком информации. Пользуясь PL/SQL, разработчик может создавать программные модули многора зового применения, скрывающие сложность исполнения операции за ее именем. Например, процесс добавления записи о служащем в базу данных компании может быть весьма сложен, обязывая ввести записи в несколько различных таблиц для нескольких различных приложений. Добавить записи в каждую из систем можно с помощью хранимых процедур, а пользователю будет казаться, что нужно выполнить лишь одну операцию на экране — ввести данные.
Рис.2 Модульность и PL/SQL
В действительности же ввод данных на экране может привести к вызову десятков отдельных процедур, каждая из которых предназначена для обработки одного маленького компонента общего процесса добавления сведений о служащем. Иногда эти компоненты являются много кратно используемыми программными блоками ввода данных, которые берутся из различных приложений для работы с людскими ресурсами и изменяются на новом экране в соответствии с вводимыми данными. На рис. 2 показано, как модульность реализуется в блоках PL/SQL.
Программные конструкции PL/SQL в Oracle бывают двух видов Во-первых, это именованные блоки PL/SQL, которые хранятся в базе данных и к которым могут обращаться по имени другие блоки PL/SQL или пользователь из командной строки SQL*Plus. При передаче именованного программного блока в Oracle база данных подвергает его грамматическому разбору и компилирует его, а затем сохраняет с присвоенным ему именем.
Кроме именованных, существуют и неименованные, или анонимные, блоки PL/SQL. Анонимный блок состоит из нескольких команд PL/SQL, которые все сразу передаются в Oracle для грамматического разбора и исполнения. Анонимные блоки не хранятся в базе данных Oracle так, как именованные блоки. Oracle подвергает операторы грамматическому разбору и исполняет их при передаче блока.
Именованные и анонимные блоки PL/SQL состоят из трех компонентов: раздела объявлений переменных (variable declaration section), исполняемого раздела (executable section) и обработчика исключительных ситуаций (exception handler). В необязательном разделе объявлений описываются все конструкции переменных, которые будут использоваться в программном блоке. Переменная может иметь любой тип данных, доступный в базе данных Oracle, а также ряд других типов, при меняемых только в PL/SQL. Исполняемый раздел блока PL/SQL обязателен и начи нается с ключевого слова begin, заканчиваясь либо ключевым словом end для всего программного блока, либо ключевым словом exception. Последний компо нент блока PL/SQL — это обработчик исключительных ситуаций, где описываются все ошибки, которые могут произойти в блоке, и указываются способы их обработки. В PL/SQL обработчик исключительных ситуаций необязателен.
|
Анонимный блок |
Именованный блок |
||
|
DECLARE …. BEGIN …. EXCEPTION …. END |
/* Начало раздела объявлений. */ /* Начало исполняемого раздела программного блока. */ /* Начало обработчика исключительных ситуаций. */ |
IS …. BEGIN …. EXCEPTION …. END |
/* Начало раздела объявлений. */ /* Начало исполняемого раздела программного блока. */ /* Начало обработчика исключительных ситуаций. */ |
Блоки PL/SQL могут быть вложенными друг в друга, так что конструкция begin-exception-end может находиться внутри другого блока.
В анонимном блоке PL/SQL проще всего идентифицировать раздел объявлении, так как перед ним указывается ключевое слово declare. В таком блоке тоже име ются исполняемый раздел и обработчик исключительных ситуаций.
Пример анонимного блока PL/SQL:
DECLARE /* Начало раздела объявлений анонимного блока. */ my_convert_amt NUMBER(10) ;
my_convert_currency VARCHAR2(5) ;
iny_old_currency VARCHAR2(5) ;
bad_data EXCEPTION;
BEGIN /* Начало исполняемого раздела программного блока. */
IF my_convert_amt=6 THEN
…..
…..
END IF;
EXCEPTION /* Начало обработчика исключительных ситуаций. */
WHEN bad_data then
DBMS_OUTPUT. PUT_LINE (‘Error’) ;
END;
(DBMS_OUTPUT.put_line() в программных блоках вызывается для записи строки выходной информации в интерфейс SQL*Plus. Для просмотра выходных строк применяется команда set serveroutput on).
Именованные программные блоки PL/SQL подразделяются на четыре класса: хранимые процедуры, функции, модули (пакеты) и триггеры.
Процедура - именованный программный блок PL/SQL, который состоит из группы операторов, принимающих и/или возвращающих нуль или более переменных. Пример:
CREATE OR REPLACE PROCEDURE money_converter (amount IN NUMBER,
from_currency IN VARCHAR2,
to_currency IN VARCHAR2,
return_val IN OUT NUMBER )
IS /* Начало раздела объявлений. */
my_new_amt number(10) := 0;
bad_data exception;
BEGIN /* Начало исполняемого раздела программного блока. */
IF ny_new_amt > 3 THEN
DBMS_OUTPUT.PUT_LINE(‘Do this’);
ELSE
DBMS__OUTPUT. PUT_LINE(‘Do that’);
END IF;
retum_val := my_new_amt ;
EXCEPTION /* Начало обработчика исключительных ситуаций. */
WHEN bad_data THEN
DBMS_OUTPUT. PUT_LINE (‘Error’);
END;
Функция - именованный программный блок PL/SQL, который состоит из группы операторов, принимающих нуль или более переменных и возвращающих одно значение. Пример:
CREATE OR REPLACE FUNCTION convert_money
(amount IN NUMBER,
from_currency IN VARCHAR2 ,
to_currency IN VARCHAR2 )
RETURN NUMBER
IS /* Начало раздела объявлений. */
my_new_amt number (10) := 0;
bad_data exception;
BEGIN /* Начало исполняемого раздела программного блока. */
IF my_new_amt > 3 THEN
DBMS_OUTPUT.PUT_LINE (‘Do this’);
ELSE
DBMS_OUTPUT. PUT_LINE (‘Do that’);
END IF;
RETURN my_new_amt;
EXCEPTION /* Начало обработчика исключительных ситуаций. */
WHEN bad_data THEN
DBMS_OUTPUT. PUT_LINE (‘Error’);
END;
Модуль (или пакет) - именованный программный блок PL/SQL, состоящий из совокупности именованных процедур и функций и имеет две части Первая часть - это спецификация, или описание (specification), где перечисляются применяемые процедуры и функции, а также их параметры, константы и объявления пользовательских типов. Вторая часть — тело (body), в котором содержится реальный программный текст процедур и функций. Пример:
-- Спецификация модуля
CREATE OR REPLACE PACKAGE money_pkg IS
EOF constant varchar2(30) := ‘~~+~~+~~’;
FUNCTION convert_money
(amount IN NUMBER,
from_currency IN VARCHAR2,
to_currency IN VARCHAR2 )
RETURN NUMBER;
PROCEDURE money_converter
(amount IN NUMBER,
from_currency IN VARCHAR2,
to_currency IN VARCHAR2,
return_val IN OUT NUMBER );
END;
-- Тело модуля
CREATE OR REPLACE PACKAGE BODY money_pkg IS
FUNCTION convert_money
(amount IN NUMBER,
from_currency IN VARCHAR2,
to_currency IN VARCHAR2 )
RETURN NUMBER
IS /* Начало раздела объявлений. */
my_new_amt number(10) := 0;
bad_data exception;
BEGIN /* Начало исполняемого раздела программного блока. */
IF my_new_aint > 3 THEN
DBMS_OUTPUT. PUT_LINE (‘Do this’);
ELSE
DBMS_OUTPUT. PUT_LINE (‘Do that’);
END IF;
RETURN my_new_amt ;
EXCEPTION /* Начало обработчика исключительных ситуаций. */
WHEN bad_data THEN
DBMS_OUTPUT.PUTJLINE(‘Error’);
END;
PROCEDURE money_converter
(amount IN number,
from_currency IN VARCHAR2,
to_currency IN VARCHAR2,
retum_val IN OUT NUMBER )
IS /* Начало раздела объявлений. */
my_new_amt number (10) : = 0;
bad_data exception;
BEGIN /* Начало исполняемого раздела программного блока. */
IF my_new_amt > 3 THEN
DBMS_OUTPUT. PUT_LINE (‘Do this’);
ELSE
DBMS_OUTPUT.PUT_J,INE(‘DO that’);
END IF;
return_val := my_new_amt;
EXCEPTION /* Начало обработчика исключительных ситуаций. */
WHEN bad_data THEN
DBMS_OUTPUT.PUT_LINE(‘Error’);
END;
END;
Триггер - именованный программный блок PL/SQL, который состоит из rpyппы операторов, связанных с таблицей базы данных. Всякий раз при возникновении активизирующего события, например UPDATE, INSERT или DELETE, срабатывает соответствующий триггер.
В ответ на активизирующее событие триггеры могут срабатывать один раз для всей таблицы или для каждой строки, которая модифицируется активизирующим событием. Можно также устанавливать триггеры для активизации только в ответ на изменение одного столбца строки. Пример:
CREATE OR REPLACE TRIGGER rate_hist_trigger_01
BEFORE delete ON exch_rate
BEGIN
INSERT INTO exch_rate_hist (chg_user, chg_date_time, comment)
VALUES (user, to_char(sysdate,'YYYY-MM-DD HH:MIAM'),'текущая дата');
END;
Базисные элементы языка PL/SQL.
Набор символов: программа PL/SQL представляет собой строки текста, использующего следующий набор символов:
PL/SQL не чувствителен к регистру, так что символы нижнего регистра эквивалентны соответствующим символам верхнего регистра, за исключением текста и символьных литералов.
Лексические модули: текст строки PL/SQL содержит группы символов, известные как лексические модули, которые делятся на:
Чтобы улучшить восприятие текста, можно разделять лексические модули пробелами. Смежные идентификаторы необходимо отделять пробелом или знаками пунктуации. Но нельзя включать пробелы в лексические модули, кроме строковых литералов и комментариев.
Разделитель – простой (табл.1) или составной символ (табл.2), который имеет специальное значение в PL/SQL. Простые разделители состоят из одного символа. Составные состоят из двух символов.
Таблица 1.Простые разделители PL/SQL
|
Разделитель |
Значение |
|
+ |
Сложение |
|
- |
Вычитание |
|
* |
Умножение |
|
/ |
Деление |
|
= |
равно — оператор отношения |
|
< |
меньше — оператор отношения |
|
> |
больше — оператор отношения |
|
( |
разделитель выражения или списка |
|
) |
разделитель выражения или списка |
|
; |
окончание оператора |
|
% |
индикатор атрибута |
|
, |
разделитель элементов |
|
. |
составляющая выделения |
|
@ |
индикатор удаленного доступа |
|
‘ |
разделитель символьной строки |
|
‘‘ |
разделитель цитируемого идентификатора |
|
: |
индикатор главной переменной |
Таблица 2. Составные разделители PL/SQL
|
Составной символ |
Значение |
|
** |
возведение в степень |
|
<> |
оператор отношения |
|
!= |
оператор отношения |
|
~= |
оператор отношения |
|
^= |
оператор отношения |
|
<= |
оператор отношения |
|
>= |
оператор отношения |
|
:= |
оператор присваивания |
|
=> |
оператор ассоциации |
|
.. |
оператор размерности |
|
|| |
оператор сцепления |
|
« |
определитель метки (начало) |
|
» |
определитель метки (конец) |
|
-- |
индикатор однострочного комментария |
|
/* |
определитель многострочного комментария (начало) |
|
*/ |
определитель многострочного комментария (конец) |
Идентификаторы используются для именования объектов про граммы PL/SQL и модулей: констант, переменных, исключений, курсо ров, переменных курсоров, подпрограмм и пакетов.
Идентификатор состоит из символа, необязательно сопровождае мого другими символами, числами, знаком доллара, подчеркиванием и знаком номера. Использование других символов, например, дефиса, слэ ша и пробела, запрещено. Для написания идентификатора можно исполь зовать верхний, нижний или оба регистра вместе.Длина идентификатора не может превышать 30 символов.
Некоторые идентификаторы, обозначаемые зарезервированными словами, имеют специальное синтаксическое значение в PL/SQL и по этому не могут быть переопределены. Например, слова BEGIN и END, определяющие начало и конец блока или подпрограммы, являются заре зервированными. При переопределении зарезервированного слова выво дится ошибка трансляции.
PL/SQL позволяет включать идентификаторы внутри двойных ка вычек. Такие идентификаторы редко являются необходимыми, но иногда они могут быть полезны. Они могут содержать любую последователь ность печатаемых символов, включая пробелы, за исключением двойных кавычек. Максимальная длина идентификатора в кавычках - 30 символов, без учета двойных кавычек.
Пример использования идентификатора, заключенного в двойные кавычки: некоторые зарезервированные PL/SQL слова не зарезервированы SQL. Можно использовать зарезервированное PL/SQL слово TYPE в утверждении CREATE TABLE в качестве имени столбца базы данных. Но если утверждение SQL в программе обращается к этому столбцу, на экран выводится ошибка трансляции. Чтобы предотвратить ошибку, необходимо имя столбца в запросе ввести в верхнем регистре и заключить его в двойные кавычки следующим образом:
SELECT acct, "TYPE", bal INTO ...
Литерал - это числовое, символьное, строковое или логиче ское значение, не представляемое идентификатором.
В арифметических выражениях могут использоваться два вида чи словых литералов: целые и действительные. Целочисленный литерал - целое число без десятичной точки. Действительный литерал - целое или десятичное число с десятичной точкой.
Числовые литералы не могут содержать знак доллара или запятые, но могут быть написаны в формате с плавающей запятой, например: -9.5е-3.
Символьный литерал - это единичный символ, заключенный в одиночные кавычки (апострофы). Символьные литералы включают все печатаемые символы в наборе символов PL/SQL: символы, записи числа, пробелы и специальные символы. PL/SQL чувствителен к регистру внут ри символьных литералов. Символьные литералы '0' .. '9' не являются целочисленными литералами, но могут использоваться в арифметических выражениях, потому что они неявно обратимы в целые числа.
Строка литералов является последовательностью от нуля до не которого количества символов, заключенных в одиночные кавычки. Все строковые литералы за исключением нулевой (пустой) строки (") имеют тип данных CHAR.
Для того, чтобы вставить апостроф в строчный литерал, необходи мо ввести рядом две одиночные кавычки. PL/SQL чувствителен к регистру внутри строчных литералов.
Булевским литералом является одно из предопределенных зна чений - TRUE, FALSE и значение NULL, определяющее отсутствующее, неизвестное или неподходящее значение. Логический литерал не являет ся строкой.
Транслятор PL/SQL игнорирует комментарии, но их необходимо добавлять к программе для включения пояснений к тексту программы. PL/SQL поддерживает два стиля комментария: однострочный и много строчный. Комментарии с одиночной строкой начинаются с двойного дефиса (--) в произвольной позиции на строке и продолжаются до конца строки. Например:
--начало программы
SELECT sal INTO salary FROM emp -- определение значения salary
WHERE empno = emp_ id;
bonus:= salary * 0.15; -- расчет комиссионных
Многострочные комментарии размещаются на нескольких строках между символами / * и * /. Следующие три примера иллюстрируют некоторые популярные форматы:
/* вычисление площади круга */
area:= pi * radius**2;
/***********************************
* вычисление площади круга *
********************************************/
area:= pi * radius**2;
/* вычисление площади круга
*/
area:= pi * radius**2;
Для исключения из текста программы сразу нескольких рядом стоящих операторов можно использовать разделители многострочного комментария.
Нельзя использовать однострочные комментарии в блоке PL/SQL, ко торый будет обработан динамически программой Oracle Precompiler, потому что символы конца строки данной программой игнорируются, и коммента рий в этом случае продолжается до конца блока, а не до конца строки. Такие однострочные комментарии следует заменять многострочными.
Объявление переменных и констант.
Переменные и константы объявляют в разделе объявлений программного блока. Объявление переменной:
birthday date;
Объявление константы аналогично объявлению переменной за исключением того, что необходимо добавить ключевое слово CONSTANT и сразу назначить значение константе. С этого момента никакие другие значения константы не допускаются. Рассмотрим функцию, с помощью которой вычисляется пло щадь круга,— произведение числа пи на квадрат радиуса. Объявление пи в качестве константы проясняет назначение этого числа в программном блоке. Допустим, что в базе данных Oracle существует таблица CIRCLE со столбцом RADIUS, тип данных которого в функции нужно определить как number:
CREATE FUNCTION find_circle_area
(p_radius IN number)
RETURN NUMBER IS
my_area number(10) := 0;
pi CONSTANT number(15,14) := 3.14159265358;
BEGIN
my_area := (p_radius*p_radius)* pi;
return (my_area);
END;
Начальное значение пере менной можно присвоить в разделе объявлений программного блока, а также в любой момент исполнения программы символом присваивания — двоеточием, за которым следует знак равенства. Присвоить значение переменной в исполняемом разделе можно по-разному, например воспользовавшись значением, которое возвращает функция, вызванная для заполнения переменной, или текущим значением переменной в ариф метическом уравнении, создающем новое значение. Пример:
DECLARE
my_area number(10):= 0;
BEGIN
my_area:= find_circle_area(493);
my_area:= my_агеа + 45;
END;
В разделе объявлений можно присвоить значение переменной с помощью ключевого слова DEFAULT. Например, объявление в предыдущем программном блоке можно изменить так:
my_area number(10) default 0
Можно описать переменную так, чтобы в нее не разрешалось вводить NULL-значения, вставив ключевые слова NOT NULL в ее объявление. Например:
my_area number(4) NOT NULL :=9999;
В строке текста, идентифицирующей переменную, могут присутствовать буквенно-цифровые символы A-Z, a-z и 0-9, а также специальные символы $,? и_, но не амперсенды (&), дефисы (-) или пробелы. Нужно также отказаться от использования в качестве имен переменных зарезервированных слов PL/SQL, иначе при компиляции блока Oracle возвратит сообщение об ошибке.
Область действия и область видимости переменных: Чаще всего переменные, объявленные и используемые в родительском блоке, локальны для него и глобальны для подблока, а переменные, объявленные и используемые в подблоке, лишь локальны для подблока. Хотя в главном блоке может быть несколько равноправных подблоков, такие пере менные не будут видимы в других подблоках и в главном блоке.
Динамическое объявление типа переменных PL/SQL: Как правило, переменные для столбцов таблицы имеют тот же тип данных и ту же длину, что и сам столбец. Определить столбец таблицы, типу данных которого должен соответствовать тип данных конкретной переменной, можно с помощью специального синтаксического средства PL/SQL с использованием ключевого слова %type. Для этого нужно знать имена столбца и таблицы, которым соответ ствует переменная. Кроме того, переменную можно объявлять с инициализационным значением, установив ее равной некоторому значению в разделе объявлений. Пример:
DECLARE
my_empid emp.Empid%TYPE;
my_lastname emp.lastname%TYPE;
my_firstname emp. Firstname%TYPE;
my_other_empid emp.empid%type := ‘00000’;
my_salary emp.salary %TYPE;
BEGIN
SELECT empid, lastname, firstname, salary
INTO my_empid, my_lastname, my_firstname, my_salary
FROM employee
WHERE empid = 49594;
END;
Объявление подтипов: Помимо использования стандартных типов данных PL/SQL, можно объявить пользовательский тип в качестве подтипа стандартного типа. Для этого служит команда:
SUBTYPE mуdatatype IS oracledatatype, где
mydatatype — имя определяемого подтипа,
oracledatatype — имя стандартного типа данных Oracle.
После этого можно объявить переменную с описанным подтипом:
DECLARE
SUBTYPE birthdates IS DATE;
stacy_birthday birthdates;
BEGIN
…..
Если базовый тип для подтипов одинаков, они совместимы друг с другом. Следовательно, если объявить два разных подтипа с базовым типом DATE и две переменных с каждым из подтипов, можно присвоить значение одной переменной одного подтипа переменной другого подтипа.
Типы данных PL/SQL, которыми можно пользоваться для объявления переменных в PL/SQL:
• DEC, DECIMAL, REAL, DOUBLE_PRECISION Эти числовые типы данных являются подмножеством типа NUMBER, используемого для объявления переменных в PL/SQL.
• INTEGER, INT, SMALLINT, NATURAL, POSITIVE, NUMERIC Эти числовые типы данных являются подмножеством типа NUMBER, используемого для объявления переменных в PL/SQL.
• BINARY_INTEGER, NLS_INTEGER Эти типы данных используются для хранения целых чисел. Переменную любого из этих типов нельзя сохранить в базе данных без предварительного преобразования.
• CHARACTER Другое название типа данных CHAR.
• VARCHAR Другое название типа данных VARCHAR2.
• BOOLEAN Используется для хранения логических (TRUE/FALSE) значений.
• TABLE/RECORD Табличный тип данных используются для хранения эквивалента массива, а тип данных «запись» — для хранения переменных с составными типами данных.