Пакеты в Oracle

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

Пакет состоит из двух компонентов:
- спецификации
- тела

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

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

Создание спецификации пакета:

create [or replace] package имя_пакета
{is | as}
спецификация_пакета
end имя_пакета;


Пример создания спецификации пакета:

create package my_package as

TYPE t_cur IS REF CURSOR;

FUNCTION get_val RETURN t_cur;

procedure update_tab_col(
par1 in tab.col1%type,
par2 in number
);

end my_package;
/


Создание тела пакета:

create [or replace] package body имя_пакета
{is | as}
тело_пакета
end имя_пакета;


Пример создания тела пакета:

create package body my_package as


FUNCTION get_val RETURN t_cur IS
cur t_cur;

BEGIN

OPEN cur FOR
SELECT col1, col2, col3  FROM tab;
return cur;

END get_val;



PROCEDURE update_tab_col(
par1 in tab.col1%type,
par2 in number
) as

var1 integer;

BEGIN

select count(*)
into var1
from tab
where col1 = par1;

if var1 = 1 then
  update tab
  set col2 = col2 * par2
  where col1 = par1;
  commit;
end if;

exception
when others then
rollback;

END update_tab_col;


end  my_package;
/


Вызов процедур и функций в пакете:

select my_package.get_val from dual;
call my_package.update_tab_col(7,  1.5);


Получить информацию о процедуре или функции из пакета можно так:

select * from user_procedures where object_name = 'MY_PACKAGE';

Удаление пакета:

DROP PACKAGE  my_package;

Функции в Oracle

create [or replace] function имя_функции
[(имя_параметра [ IN | OUT | IN OUT ]  тип [, ... ])]
{is | as}

begin
 тело_функции
end имя_функции;

in -режим по умолчанию
    входной параметр
    (параметр который к моменту выполнения уже имеет значение
     и это значение не может измениться в теле функции)

out -используется для параметров,
     значения которых устанавливаются только в теле функции.

in out -используется для параметров,
        которые могут иметь значения к моменту вызова функции,
        но эти значения могут быть изменены в теле функции.


create function func1 (
par1 in number
) return number as

var1 number := 10;
var2 number;

begin

var2 := var1 * par1;
return var2;

end func1;
/



create avg_tab_col (
par1 in integer
) return number as

var1 number;

begin

select avg(col2)
into var1
from tab
where col1 = par1;

return var1;

end avg_tab_col;
/


Вызываются функции так:

select func1(15) from dual;


select func1(par1 => 15) from dual;


select avg_tab_col(20) from dual;


Получить информацию о функции можно так:

select * from user_procedures where object_name in ('FUNC1, 'AVG_TAB_COL');


Удаление функции:

drop function  func1();

Процедуры в Oracle

create [or replace] procedure имя_процедуры
[(имя_параметра [ IN | OUT | IN OUT ]  тип [, ... ])]
{is | as}

begin
 тело_процедуры
end имя_процедуры;



in -режим по умолчанию
    входной параметр
    (параметр который к моменту выполнения уже имеет значение
     и это значение не может измениться в теле процедуры)

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

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


create procedure update_tab_col(
par1 in tab.col1%type,
par2 in number
) as

var1 integer;

begin

select count(*)
into var1
from tab
where col1 = par1;

if var1 = 1 then
  update tab
  set col2 = col2 * par2
  where col1 = par1;
  commit;
end if;

exception
when others then
rollback;

end update_tab_col;
/


Вызвать данную процедуру можно так:

позиционная запись:
(для обязательных параметров)

call update_tab_col( 5, 10);

поименная запись:
(для необязятельных параметров)

call update_tab_col( par2 => 10, par1 => 5);

смешанная запись:
(начинается с позиционного набора)

call update_tab_col( 5, par2 => 10);


Получить информацию о процедуре можно так:

select * from user_procedures where object_name = 'UPDATE_TAB_COL';


Удаление процедуры:

drop procedure  update_tab_col;