Oracle вызов процедуры. Вызов хранимых процедур Oracle SQL и выполнение

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

Общий формат процедуры PL/SQL выглядит так:

PROCEDURE [схема.]имя[(параметр[, параметр...]) ] IS [объявления] BEGIN исполняемые команды [ EXCEPTION обработчики исключений] END [имя];

Основные элементы этой структуры:

• схема - имя схемы, которой будет принадлежать процедура (необязательный аргумент). По умолчанию применяется имя схемы текущего пользователя. Если значение схемы отлично от имени схемы текущего пользователя, то этот пользователь должен обладать привилегиями для создания процедуры в другой схеме.
• имя - имя процедуры.
• параметр - необязательный список параметров, которые применяются для передачи данных в процедуру и возврата информации из процедуры в вызывающую программу.
• AUTHID - определяет, с какими разрешениями будет вызываться процедура: создателя (владельца) или текущего пользователя. В первом случае процедура выполняется с правами создателя, во втором - с правами вызывающего.
• объявления - объявления локальных идентификаторов этой процедуры. Если объявления отсутствуют, между ключевыми словами IS и BEGIN не будет никаких выражений.
• ACCESSIBLE BY (Oracle Database 12c) - ограничивает доступ к процедуре программными модулями, перечисленными в круглых скобках.
• исполняемые команды - команды, выполняемые процедурой при вызове. Между ключевыми словами BEGIN и END или EXCEPTION должна находиться по крайней мере одна исполняемая команда.
• обработчики исключений - необязательные обработчики исключений для процедуры. Если процедура не обрабатывает никаких исключений, слово EXCEPTION можно опустить и завершить исполняемый раздел ключевым словом END .

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

Рис. 1

Вызов процедуры

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

BEGIN apply_discount(new_company_id, 0.15); END;

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

Display_store_summary; display_store_summary();

Заголовок процедуры

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

• Имя процедуры.
• Условие AUTHID (если имеется).
• Список параметров (если имеется).
• Список ACCESSIBLE BY (если имеется - новая возможность Oracle Database 12c).

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

Заголовок процедуры apply_discount из предыдущего раздела выглядит так:

PROCEDURE apply_discount (company_id_in IN company.company_id%TYPE , discount_in IN NUMBER)

Он состоит из типа модуля, имени и списка из двух параметров.

Тело процедуры

В теле процедуры содержится код, необходимый для реализации этой процедуры; тело состоит из объявления, исполняемого раздела и раздела исключений этой процедуры. Все, что следует за ключевым словом IS, образует тело процедуры. Разделы исключений и объявлений не являются обязательными. Если обработчики исключений отсутствуют, опустите ключевое слово EXCEPTION и завершите процедуру командой END .

Метка END

Вы можете указать имя процедуры за завершающим ключевым словом END:

PROCEDURE display_stores (region_in IN VARCHAR2) IS BEGIN ... END display_stores;

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

Команда RETURN

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

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

Стр. 19-1

Глава 19

Написание программных модулей

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

О подпрограммах с именем пользователя19 - 2

Создание подпрограммы с именем пользователя19 - 3

Вызов подпрограмм с именем пользователя19 - 7

Определение параметров19 - 8

Пакеты PL/SQL19 - 10

Стр. 19-2

О подпрограммах с именем пользователя

Подпрограмма с именем пользователя - это функция или процедура PL/SQL с именем, которую вы пишете в модуле формы, меню или библиотеки. Хотя первичным способом добавления программного управления в прикладные программы Oracle Forms являются триггеры, подпрограммы с именем пользователя дополняют триггеры, позволяя вам повторно использовать программу, не вынуждая повторно вводить ее в несколько триггеров.

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

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

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

В большинстве прикладных программ операторы могут выполнять одни и те же операции более чем одним способом и из более чем одного окна или меню. Например, прикладная программа ввода заказов может позволять оператору вычислять итог по заказам нажатием клавиши, щелчком кнопки или выбором из меню элемента меню. Без подпрограммы с именем пользователя вам пришлось бы писать одну и ту же формулу вычисления в триггере Key, триггере When-Button-Pressed и команде элемента меню.

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

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

Стр. 19-3

Создание подпрограмм с именем пользователя

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

Чтобы создать подпрограмму с именем пользователя:

Находясь в Навигаторе, выберите нужный узел Program Units, и затем выберите Navigator->Create.

Появляется диалоговое окно New Program Unit.

Информацию о командах PL/SQL Editor вы найдете в Главе 2 “Работа с инструментами конструирования”.

Для создания укажите имя Name программного модуля, затем определите его тип (Procedure, Function, Package Spec или Package Body).

Наберите, отредактируйте и скомпилируйте определяемую вами процедуру или функцию.

Наберите полное определения процедуры или функции, пользуясь стандартной структурой и синтаксисом PL/SQL. Информацию см. в разделах “Синтаксис процедуры” и “Синтаксис функции” далее в этой главе.

Для компиляции программного модуля выберите Compile.

Примечание : Oracle Forms автоматически применит ваши изменения, когда вы закроете редактор, даже если вы скомпилировали программный модуль неудачно.

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

calculate_totals (Procedure Body)

И спецификация пакета будет выглядеть так:

Error Handlers (Package Specification)

Стр. 19-4

Синтаксис процедур

Спецификация процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком формальных параметров. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END с необязательным последующим именем этой процедуры.

Синтаксис :

PROCEDURE procedure_name IS

BEGIN

statements

END;

где:

procedure_name	Уникальное, определенное пользователем имя процедуры. Это имя должно соответствовать условиям именования ORACLE. Дополнительную информацию вы найдете в SQL Language Reference Manual .
argument list	где:
var_name
mode	Это {!!под!!IN|OUT|IN OUT}.
type
value	Выражение PL/SQL.
local_variable_declaration
statements
exception_handler	Обратитесь к .

Стр. 19-5

Синтаксис функций

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

Синтаксис:

FUNCTION function_name IS

BEGIN

statements

RETURN (rezult);

RETURN (rezult);

END;

где:

function_name	Уникальное, определенное пользователем имя функции. Это имя должно соответствовать условиям именования ORACLE.
argument list	Это ({var_nametype[:=value]}[,...]) где:
var_name	Уникальное имя локальной переменной.
mode	Это {IN|OUT|IN OUT}.
type	Это }(,...)]
statements	Исполняемые операторы PL/SQL.
exception_handler	Обратитесь к PL/SQL User’s Guide and Reference .
result	Выражение, обозначающее значение функции. Значение выражения должно быть совместимо с типом, указанным в спецификации функции.

Стр. 19-6

Пример процедуры:

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

PROCEDURE do_order (units_ordered IN NUMBER,

prod_id IN NUMBER,

warehouse IN NUMBER) IS

units_in_stockNUMBER;

BEGIN

SELECT amount_in_stock INTO units_in_stock FROM inventory

WHERE product_id = prod_id

AND warehouse_id = warehouse;

IF units_in_stock >= units_in_stock - units_ordered THEN

UPDATE inventory

SET amount_in_stock = units_in_stock - units_ordered

WHERE product_id = prod_id;

ELSE

Message(‘Запаса недостаточно’)

RAISE Form_Trigger_Failure;

END IF;

END;

Вы можете воспользоваться этой процедурой в триггере следующим образом:

do_order (:order.units, :order.prod_id, :warehouse.id);

В этом примере значения трех формальных параметров - это текущие значения элементов в форме, указанные стандартным синтаксисом связной переменной:

:block_name.item_name

Пример функции:

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

FUNCTION get_inventory (product NUMBER, warehouse NUMBER)

RETURN NUMBER IS

amount NUMBER;

BEGIN

SELECT amount_in_stock INTO amount FROM inventory

WHERE product_id = product and warehouse_id = warehouse;

RETURN amount;

EXCEPTION

WHEN OTHERS THEN

Message(‘Неверное название изделия или идентификатор склада’);

RETURN (-1);

END;

Стр. 19-7

Вы можете использовать эту функцию в триггере следующим образом:

DECLARE

invNUMBER;

amount_requiredNUMBER:= :order_block.order_item;

BEGIN

inv:= Get_inventory(:product_id, :warehouse.id);

IF inv < 0 THEN

Здесь обработка ошибки

ELSE IF inv >= amount_required THEN

Здесь обработка заказа

END IF;

END;

Вызов подпрограмм с именем пользователя

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

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

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

· подпрограмма с именем пользователя, определенная в модуле формы, может вызываться только из триггеров и других подпрограмм с именем пользователя в том же модуле

· подпрограмма с именем пользователя, определенная в модуле меню, может вызываться только из команд элемента меню и программы запуска в том же модуле

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

Стр. 19-8

Вы не можете создавать библиотеку подпрограмм с именем пользователя путем написания процедур и функций прямо в библиотечном модуле или копирования в библиотеку подпрограмм из других модулей. Информацию о библиотеке см. в Главе 20 “Работа с библиотеками”.

Определение параметров

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

Типы данных параметров Параметры могут объявляться как типы VARCHAR2, DATE, NUMBER или BOOLEAN, или любыми типами объектов Oracle Forms. Создавая функцию или процедуру, вводите тип данных после имени каждого формального параметра:

PROCEDURE display_overdue_shipments (dept_id NUMBER);

FUNCTION credit_ok (customer VARCHAR2) RETURN BOOLEAN;

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

Параметр OUT возвращает значение из подпрограммы. Действительный параметр OUT может быть локальной переменной (не связной) и подпрограмма должна присвоить этой переменной значение. Если этого не произойдет, то будет вызвано предварительно определенное исключение PL/SQL NO_DATA_FOUND.

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

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

Если вы в спецификации процедуры или функции явно не обозначите режим формального параметра явно, то этот параметр объявляется как параметр IN. Таким образом, следующие спецификации процедуры эквивалентны:

PROCEDURE calculate_discount(multiplier NUMBER);

PROCEDURE calculate_discount(multiplier IN NUMBER);

Стр. 19-9

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

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

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

Присвоение значений по умолчанию параметрам IN Вы можете присвоить значение по умолчанию формальному параметру IN во время объявления этого параметра. Многие из встроенных подпрограмм Oracle Forms используют такую технологию. Когда вы вызываете такие подпрограммы, то вам не обязательно вводить действительный параметр для формального параметра по умолчанию, если только вы не хотите перезаписать значение параметра по умолчанию.

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

PROCEDURE calculate_discount(multiplier IN NUMBER:= 15);

Вызывая эту процедуру, вы можете параметр multiplier опустить, если хотите, чтобы процедура исполнилась со значением по умолчанию (15):

calculate_discount;

Или вы можете перезаписать значение по умолчанию, предоставив действительный параметр:

calculate_discount(10);

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

Стр. 19-10

Пакеты PL/SQL

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

Спецификация - это интерфейс к пакету; она объявляет типы, переменные, константы, исключения, курсоры и подпрограммы (процедуры и функции), доступные для использования. Тело полностью определяет курсоры и подпрограммы и исполняет таким образом спецификацию.

Oracle Forms предоставляет вам для использования несколько предварительно определенных пакетов, или вы можете сами определять в модулях формы, меню или библиотеки свои собственные пакеты. Вы можете также определять пакеты в Oracle7Server и затем обращаться к их содержимому из прикладных программ со стороны клиента. Информацию об использовании сохраненных Oracle Forms процедур вы найдете в Главе 2 “Сохраненные процедуры и триггеры базы данных” в Oracle Forms Advanced Techniques Manual .

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

PACKAGE name IS --спецификация

Объявления типа общие и объектов

Спецификации подпрограмм

END ;

PACKAGE BODY name IS -- тело (скрытая часть)

Объявления типа public и объектов

Тела подпрограмм

END ;

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

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

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

Стр. 19-11

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

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

Пакеты могут содержать переменные, которые постоянны для всего сеанса пользователя (сродни и такие же полезные, как переменная GLOBAL на стороне сервера), а также курсоры, которые могут оставаться открытыми для вызова функций и процедур в пакете.

Дополнительную информацию о пакетах см. в PL/SQL Version 2.0 User’s Guide and Reference .

Пример :

CREATE OR REPLACE PACKAGE LIB_HR AS

FUNCTION get_ssn(theEmpNo NUMBER) RETURN NUMBER;

Нанимаем нового служащего и возвращаем его/ее табельный номер

theDept NUMBER,

theSalNUMBER,

theDate DATE,

theSSNNUMBER) RETURN NUMBER;

theReason VARCHAR2);

Устанавливаем переменную премии

PROCEDURE set_bonus(newValue NUMBER);

END LIB_HR;

CREATE OR REPLACE PACKAGE BODY LIB_HR AS

Переменные пакета --

signingBonus NUMBER:= 1000;

Курсор пакета --

CURSOR next_empid

IS SELECT empid_sequence.NEXTVAL

FROM dual;

Извлекаем номер социальной защиты служащего

FUNCTION get_ssn(theEmpNo NUMBER) RETURN NUMBER IS

tmpSSN NUMBER;

Стр. 19-12

BEGIN

SELECT ssn

INTO tmpSSN

FROM emp

WHERE empno = theEmpNo;

RETURN(tmpSSN);

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN(-1);

END;

Нанимаем нового служащего и возвращаем табельный номер

FUNCTION hire_employee(theName VARCHAR2,

theDept NUMBER,

theSalNUMBER,

theDate DATE,

theSSNNUMBER) RETURN NUMBER IS

tmpEmpNo NUMBER;

BEGIN

IF (NOT next_empid%ISOPEN) THEN

OPEN next_empid;

END IF;

FETCH next_empid INTO tmpEmpNo;

Нам не нужно закрывать курсор, так как он будет

Оставаться открытым между вызовами

INSERT INTO EMP (empno,ename,deptno,sal,

hiredate,ssn,bonus)

VALUES(tmpEmpNo,theName,theDept,theSal,

theDate,theSSN,signingBonus);

RETURN(tmpEmpNo);

ECXEPTION

WHEN OTHERS THEN

RETURN(-1);

END;

Увольняем существующего служащего

PROCEDURE fire_employee(theEmpno NUMBER,

theReason VARCHAR2) IS

BEGIN

DELETE FROM emp

WHERE empno = theEmpno;

INSERT INTO terminations(empno,reason)

VALUES(theEmpno,theReason);

END;

Стр. 19-13

Устанавливаем переменную премии

PROCEDURE set_bonus(newValue NUMBER) IS

BEGIN

signingBonus:= newValue;

END;

END LIB_HR;

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

Ограничения Oracle Forms предоставляет прямую поддержку вызова сохраненных процедур и функций, но не прямого обращения к переменным или курсорам пакета. Для манипулирования переменными и курсорами пакета в пределах программы на стороне сервера вы должны сконструировать процедуру или функцию. Допустимыми типами данных являются:

VARCHAR2 - максимум VARCHAR2(2000)

NUMBER

DATE

BOOLEAN

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

Oracle Forms требует, чтобы и Спецификация, и Тело пакета обладали допустимостью для компиляции блока PL/SQL Oracle Forms, который производит обращение к этому пакету.

Обращение к содержимому пакета

Вы можете обращаться к содержимому пакета, используя стандартное точечное обозначение PL/SQL:

package_name.object_name

package_name.program_unit_name

Например, чтобы вызвать процедуру arrange_windows , определенную в пакете win_manage , вы можете в триггере или в подпрограмме с именем пользователя написать следующее:

win_manage.arrange_windows

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

Стр. 19-14

Пакеты Oracle Forms

Oracle Forms предоставляет следующие предварительно определенные пакеты:

Standard Extensions	Пакет STANDARD Extensions содержит встроенные функции и процедуры Oracle Forms.
TOOL_ENV	Пакет TOOL_ENV позволяет вам взаимодействовать с переменными среды Oracle.
ORA_NLS	Пакет ORA_NLS дает вам возможность извлекать информацию высокого уровня о вашей языковой среде.
TOOL_RES	Пакет TOOL_RES позволяет вам извлекать цепочечные ресурсы из ресурсного файла.
ORA_FFI	Пакет ORA_FFI позволяет производить доступ к внешним функциям (3GL).
ORA_DE	Пакет ORA_DE - это внутренний пакет. Не пользуйтесь этим пакетом.
STPROC	Пакет STPROC - это внутренний пакет. Не пользуйтесь этим пакетом.
TEXT_IO
PECS	Пакет PECS позволяет вам оценивать производительность формы.
FORMS_OLE	Пакет FORMS_OLE содержит функции и процедуры OLE Oracle Forms.
	Пакет VBX содержит функции и процедуры VBX Oracle Forms.
Standard	Пакет Standard содержит функции и процедуры PL/SQL.

Хранимая процедура (stored procedure) - это программа, которая вы­полняет некоторые действия с информацией в базе данных и при этом сама хранится в базе данных. В Oracle хранимые процедуры можно мож­но писать на языках PL/SQL и Java.

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

Хранимые процедуры используются для многих целей. Хотя админи­страторы баз данных используют их для выполнения рутинных задач ад­министрирования, главной областью их применения являются все же при­ложения баз данных. Эти процедуры могут вызываться из прикладных программ, написанных на таких языках, как Java, С#, С++ или VB.Net, а также из веб-сценариев, написанных на VBScript или JavaScript. Кроме того, эти процедуры можно вызывать в интерактивном режиме из команд­ной оболочки SQL*Plus.

Можно выделить следующие преимущества хранимых процедур:

В отличие от кода приложений, хранимые процедуры никогда не пере­даются на клиентские компьютеры. Она всегда находятся в базе данных и выполняются СУБД на том компьютере, где располагается сервер базы данных. Таким образом, они более безопасны, чем распространяемый код приложения, а кроме того, снижают сетевой трафик. Хранимые процеду­ры постепенно становятся предпочтительным режимом реализации логи­ки приложения в сети Интернет и корпоративных интрасетях. Еще одно преимущество хранимых процедур заключается в том, что SQL-операторы в них могут быть оптимизированы компилятором СУБД.

Пример хранимой процедуры

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

В листинге 4.6 изображена хранимая процедура, выполняющая эту за­дачу. Процедура, которая называется Customer_Insert, принимает четы­ре параметра: newname (имя нового клиента), newareacode (код региона), newphone (телефон) и artistnationality (национальность художника). Клю­чевое слово IN указывает на то, что все эти параметры являются входны­ми. Выходные параметры (которых у этой процедуры нет) обозначаются ключевым словом OUT, а параметры, играющие роль и входных и вы­ходных, - сочетанием IN OUT. Следует обратить внимание, что для па­раметра указывается только тип данных, а длина не указывается. Oracle определит длину из контекста.

Листинг 4.6.

CREATE OR REPLACE PROCEDURE Customer_Insert (
newname IN char, newareacode IN char, newphone IN char,
artistnationality IN char
AS
rowcount integer(2);
CURSOR artistcursor IS SELECT ArtistID FROM ARTIST
WHERE Nationality = artistnationality;
BEGIN
SELECT Count(*) INTO rowcount FROM CUSTOMER
WHERE Name = newname AND AreaCode = newareacode AND PhoneNumber = newphone;
IF rowcount > 0 THEN BEGIN
DBMS_OUTPUT.PUT_LINE ("There is client in DB! Count is " I I rowcount); RETURN;
END; END IF;
INSERT INTO CUSTOMER
(CustomerlD, Name, AreaCode, PhoneNumber)
VALUES (CustID.NextVal, newname, newareacode, newphone);
FOR artist IN artistcursor LOOP
INSERT INTO CUSTOMER_ARTIST_INT (CustomerlD, ArtistID)
VALUES (CustID.CurrVal, artist.Artist ID); END LOOP;
DBMS_OUTPUT.PUT_LINE ("Client is added!");
END;
/

Раздел объявления переменных следует за ключевым словом AS. Опе­ратор SELECT определяет переменную-курсор (cursor variable) с именем artistcursor. Этот курсор выделяет из таблицы ARTIST для обработки строки всех художников заданной национальности.

В первой части процедуры проверяется, есть ли в базе информация о данном клиенте. В этом случае никакие действия не предпринимаются, а пользователю с помощью пакета Oracle DBMS_OUTPUT выводится со­ответствующее сообщение. Следует обратить внимание, что для вывода строки и значения переменной используется следующий синтаксис:

DBMS_OUTPUT.PUT_LINE ("<строка>" И <переменная>);

Пользователь получит это сообщение только в том случае, если проце­дура будет вызвана из SQL*Plus. В случае вызова процедуры иным путем, например с помощью браузера через Интернет, пользователь не увидит этого сообщения. Чтобы сообщить пользователю об ошибке, разработчик должен воспользоваться выходным параметром или сгенерировать исклю­чение.

Кроме того, чтобы такие сообщения стали видимыми, следует выпол­нить команду

Set serveroutput on;

Если при работе в SQL*Plus вы не видите сообщений, выводимых ва­шими процедурами, то, скорее всего, вы не выполнили этот оператор.

Оставшаяся часть процедуры в листинге 4.6 вставляет данные о новом клиенте и затем перебирает всех художников выбранной национальности. Обратите внимание на использование специальной конструкции PL/SQL FOR artist IN artistcursor. Эта конструкция выполняет несколько задач. Прежде всего, она открывает курсор и считывает первую строку. Затем она последовательно обрабатывает все строки под курсором и по оконча­нии обработки передает управление следующему оператору после FOR. Заметьте также, что обращение к столбцу ArtistID текущей строки про­исходит с использованием синтаксиса artist.ArtistID, где artist - это имя переменной цикла FOR, а не курсора.

После того как процедура написана, ее необходимо скомпилировать и сохранить в базе данных. Если текст процедуры сохранен в файле, то про­цедура будет скомпилирована и сохранена в базе данных автоматически после ввода команды

start Имя_файла_процедуры

Если вы что-то ввели неправильно, у вас могут возникнуть ошибки компиляции. К сожалению, SQL*Plus не покажет вам эти ошибки автома­тически, а выдаст сообщение "Warning: Procedure created with compilation errors "(Предупреждение: При компиляции процедуры обнаружены ошиб­ки). Чтобы увидеть ошибки, введите команду:

Show errors;

Если синтаксических ошибок не было, вы получите сообщение "Procedure created "(Процедура создана). Теперь вы можете вызвать эту процедуру с помощью команды EXECUTE или EXEC:

Exec Customer_Insert("Michael Bench", "203", "555-2014", "US");

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

и EXEC SP() и CALL SP() может использоваться в SQL * Plus для выполнения SP. Кстати, вы также можете использовать BEGIN SP(); END;

но есть некоторые различия.

CALL является Oracle SQL и должен работать везде. Другие клиенты БД, которые могут общаться с Oracle, могут поддерживать или не поддерживать SQL*Plus EXEC. Многие (например, Oracle SQL Developer, SQLWorkbench/J), но некоторые нет (Liquibase).

типы данных передаваемых параметров the CALL оператор должен быть SQL типов данных. Они не могут быть только типами данных PL/SQL, такими как BOOLEAN.

EXEC может использоваться для выполнения не только SP,но и произвольного оператора.

если SP не имеет параметров, вы можете использовать EXEC SP; синтаксис, но CALL требуются пустые скобки: CALL SP();

Если вы вызываете proc, который возвращает sys_refcursor с помощью Toad, существует разница между CALL и EXEC.

создать процедуру foo (i в номере, o out sys_refcursor) как начинать откройте для выберите i из dual; конец;

exec foo (1,:r); -- выводит 1 строку

вызов foo (1,: r); -- выводит 0 строк

Note: когда вы префикс параметра с двоеточием, жаба предложит вам тип (который в данном случае является курсором).

EXECUTE принимает строку в качестве параметра, который позволяет" выполнять " динамический sql. Выполнить в основном говорят... с помощью этой входной строки запустите SQL engine на содержимом.

вызов передает управление хранимой процедуре или модулю.

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

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