Функции, выдающие более одного значения.

Ранее рассматривались функции, которые умели выдать одно единственное значение. В этой главе будет рассказано, как выдать в качестве результата функции сразу несколько значений.

Создадим справочник регионов и заполним его данными:

create table kk_region

(

region_id number,

region_name varchar2(60),

super_region_name varchar2(60)

);

insert into kk_region(region_id, region_name, super_region_name) values(1,'Москва','Центральный федеральный округ');

insert into kk_region(region_id, region_name, super_region_name) values(2,'Ярославская область','Центральный федеральный округ');

insert into kk_region(region_id, region_name, super_region_name) values(3,'Воронежская область','Центральный федеральный округ');

insert into kk_region(region_id, region_name, super_region_name) values(4,'Санкт-Петербург','Северо-Западный федеральный округ');

insert into kk_region(region_id, region_name, super_region_name) values(5,'Псковская область','Северо-Западный федеральный округ');

insert into kk_region(region_id, region_name, super_region_name) values(6,'Татарстан','Поволжский федеральный округ');

insert into kk_region(region_id, region_name, super_region_name) values(7,'Нижегородская область','Поволжский федеральный округ');

insert into kk_region(region_id, region_name, super_region_name) values(8,'Самарская область','Поволжский федеральный округ');

Создадим справочник заводов и заполним его данными:

create table kk_factory

(

factory_name varchar2(60),

region_id number

);

insert into kk_factory(factory_name, region_id) values ('Завод Красный Октябрь', 1);

insert into kk_factory(factory_name, region_id) values ('ЯМЗ', 2);

insert into kk_factory(factory_name, region_id) values ('КАМАЗ', 6);

Создадим вспомогательный тип для будущей функции:

create or replace type type_region as object

(

region_name varchar2(60),

super_region_name varchar2(60)

);

Создадим функцию, которая будет по region_id возвращать сразу два результата - region_name и super_region_name:

create or replace function get_region_info(in_region_id in varchar2)

return type_region as

v_result type_region;

v_region_name varchar2(60);

v_super_region_name varchar2(60);

begin

begin

select region_name, super_region_name

into v_region_name, v_super_region_name

from kk_region

where region_id = in_region_id;

exception

when others then

v_region_name := 'n/a';

v_super_region_name := 'n/a';

end;

v_result := type_region(v_region_name, v_super_region_name);

return v_result;

end get_region_info;

Создадим вьюшку, которая будет использовать вышенаписанную функцию с несколькими результатами:

create or replace view vw_kk_factory as

select factory_name,

(v).region_name region_name,

(v).super_region_name super_region_name

from (select factory_name, get_region_info(region_id) v from kk_factory) x;

Посмотрим на результаты:

select * from vw_kk_factory;

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