Эффективное программирование в PL/SQL. Встроенные подпрограммы, функции, процедуры и пакеты

Программные единицы Oracle Forms

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

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

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

Мы с вами рассмотрим различные виды программных структур, представленных в Oracle Forms, а именно:

• Пакет
• Функция
• Процедура

Любая программная структура, созданная в Oracle Forms, не доступна в Базе Данных, она доступна только в модуле, в котором создана.

Функция (Function)

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

Рис. 4.1. Окно "Новая программа"

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

• HOW_DAY - возвращает количество недель определенного дня недели в текущем месяце.
• DAYS_IN_MONTH - возвращает количество дней в месяце.
• FIRST_DAY - возвращает первый день месяца.

1. Находясь в навигаторе объектов, выберите узел "Программы", затем выберите пункт меню Навигатор | Создать.
2. В окне "Программы" (рис. 4.1) введите имя первой функции - HOW_DAY и выберите радиокнопку "Функции". Подтвердите выбор для вызова PL/SQL-редактора. Вы можете ввести любое имя функции за исключением зарезервированных слов. Имя функции, указанное в окне "Программы", не окончательное, так как вы всегда можете его изменить на этапе написания функции. Поле "Имя" должно быть обязательно заполнено.
3. В появившемся PL/SQL-редакторе уже содержится небольшой фрагмент кода - это конструкция функции (рис. 4.2.).
   

   
   Рис. 4.2. Окно редактора PL/SQL. Конструкция функции
4. Фраза " RETURN _IS " - указывает на тип возвращаемого значения функцией. Вместо подчеркивания вы должны вставить тип возвращаемого значения, в нашем случае это NUMBER. То есть теперь фраза должна выглядеть как " RETURN NUMBER IS ".
5. Ниже приведен листинг функции HOW_DAY.

   "HOW_DAY"
   FUNCTION  "HOW_DAY" 
   ( dai varchar2) return number
   as cnt number; 
   begin
    Select Count (a.h_day) INTO cnt From 
    (Select Rtrim (To_char (To_date ( Rownum|| '-'||
     To_char (Sysdate, 'mon-yy')), 'DAY' )
    ) As h_day From user_all_tables
    Where Rownum Between 1 And 
      To_char (Last_day (Sysdate), 'DD')) a 
    Where a.h_day = upper(dai); 
    return cnt; 
   end;
6. Наберите этот код и скомпилируйте функцию.

Создайте самостоятельно вторую функцию - DAYS_IN_MONTH. В теле функции напишите и скомпилируйте следующий код:

"DAYS_IN_MONTH"
FUNCTION        "DAYS_IN_MONTH" ( val IN DATE
) RETURN NUMBER IS
Sum_date number := 0 ; 
cnt number:=0; 
BEGIN
 FOR i IN 1 ..31 LOOP
  IF TO_CHAR(to_date(i||substr(val,3)), 'DAY') = 'SUNDAY' 
  THEN 
   Sum_date := Sum_date+ 1 ; cnt:=sum_date;
  END IF ;
 END LOOP ; 
 RETURN cnt ; 
END ;

Создайте заключительную, третью функцию - FIRST_DAY. В теле функции напишите и скомпилируйте следующий код:

"First_day"
FUNCTION "First_day" (val date) return date
is
begin
 return to_date('01'||substr(val, 3));
end First_day;

Обращение к функциям в программе

Функции, созданные в Oracle Forms, не могут участвовать в SQL-запросах напрямую. Обойти это ограничение можно, передав значение функции какой-либо переменной, которую затем можно подставить в запрос. Например, следующий PL/SQL-блок вызовет ошибку компиляции - "Функция HOW_DAY не может быть использована в SQL":

DECLARE
Val number;
BEGIN
SELECT HOW_DAY('MONDAY') INTO Val FROM dual;
END;
Для того чтобы этой ошибки не возникало, следует поступить следующим образом:
DECLARE
Val number;
BEGIN
Val:=HOW_DAY('MONDAY');
END;

Если вам необходимо использовать значение функции в условии запроса, то вам нужно сначала присвоить это значение какой-либо переменной:

DECLARE
Val varchar2(20);
BEGIN
Val:=FIRST_DAY(SYSDATE);
SELECT col_name INTO Val FROM table_name where day=val;
/* Если же вы напишете: SELECT col_name INTO Val FROM table_name
where day= FIRST_DAY(SYSDATE) - это вызовет такую же ошибку, как и
в первом случае */
END;

Процедура

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

PROCEDURE DONT_DELETE_LAST_RECORD
IS 
BEGIN
 IF :System.Last_Record = 'TRUE' 
 AND :System.Cursor_Record ='1' THEN
  Message('Последняя запись не может быть удалена'); 
 ELSE
  Delete_Record; 
 END IF; 
END;

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

BEGIN
 DONT_DELETE_LAST_RECORD
END;

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

PROCEDURE N_Table (tab_name varchar2, n NUMBER) IS
 my_ddl VARCHAR2(2000);
BEGIN
 my_ddl := 'create table '||tab_name||'(COL1 NUMBER';
 FOR I in 2..N LOOP
  my_ddl := my_ddl||',COL'||TO_CHAR(i)||' NUMBER';
 END LOOP;
 my_ddl := my_ddl||')';
 Forms_DDL(my_ddl);
 IF NOT Form_Success THEN
  Message ('Таблица не создана');
 ELSE
  Message ('Таблица создана');
 END IF;
END;