На основе кода примера создать процедуры для выборки и изменения атрибута нового типа-массива в таблице;
create or replace PROCEDURE pas_dan_insert(
out_res out integer, -- код результата
in_id IN sotrudnik.id_sotr%type,
in_sotr IN varchar2
) IS
l_sotr t_varchar_array; -- Локальная переменная массив
begin
out_res:=0; -- 0 - все нормально
IF in_id IS NULL THEN
out_res:=1;
ELSE
-- выборка сохраненного ранее значения
select pas_dan into l_sotr from sotrudnik where id_sotr = in_id;
if ( l_sotr IS NULL) then
BEGIN
DBMS_OUTPUT.put_line('Запись пуста! Создание новой записи');
update sotrudnik set pas_dan = t_varchar_array(in_sotr) where id_sotr = in_id;
RETURN;
END;
else dbms_output.put_line( l_sotr.count);
end if;
-- увеличение массива на 1 элемент
l_sotr.extend(1);
dbms_output.put_line( l_sotr.count);
-- присвоение последнему элементу нового значения
l_sotr( l_sotr.count) := in_sotr;
-- обновление записи
update sotrudnik set pas_dan = l_sotr where id_sotr = in_id;
commit;
END IF;
end;
Выполнение процедуры и её результат приведены на рисунках ниже:
Работа с табличным типом:
Создание типов: 04_t_org_po.Tps и 05_tt_org_po.Tps;
CREATE OR REPLACE TYPE t_sotr_klient as object (
sotr_id number(38),
klient_id number(38)
);
CREATE OR REPLACE TYPE tt_sotr_klient as table of t_sotr_klient;
Разработать процедуры по аналогии со следующими:
payment_order_select
payment_order_select_1
payment_order_select_2
payment_order_select_p
payment_order_select_p1
payment_order_select_p2
Процедура поиска сотрудников по их должности
create or replace procedure sotr_select( out_rc out sys_refcursor,
out_res out integer, -- код результата
in_id in sotrudnik.id_sotr%type, -- вх/вых параметр - идентификатор
in_name in sotrudnik.dolgnost%TYPE ) is
begin
open out_rc for
select s.id_sotr, s.fio,s.dolgnost, s.adres,
z.id_z, z.kod_rab, z.data_v, z.number_sotr
from sotrudnik s, zakaz_na_rem z
where s.id_sotr = z.number_sotr -- соединение таблиц
-- условия
and (s.id_sotr = in_id or in_id is null)
and (s.dolgnost = in_name or in_name is null)
order by s.id_sotr;
end;
Выполнение процедуры и её результат приведены на рисунках ниже:
Процедура поиска сотрудников по ид. и должности.
Изменение 1: добавить в выходные параметры общее количество найденных должностей и ФИО сотрудников
Недостатки: дублирование кода с условиями.
create or replace
procedure sotr_select1(
out_rc out sys_refcursor, -- код результата
in_id in sotrudnik.id_sotr%type, -- вх/вых параметр - идентификатор
in_name in sotrudnik.dolgnost%TYPE,
out_dolgnost_count out integer, -- количество найденых должностей
out_fio_count out integer -- количество найденых ФИО сотрудников
) is
begin
open out_rc for
select s.id_sotr, s.fio,s.dolgnost, s.adres,
z.id_z, z.kod_rab, z.data_v, z.number_sotr
from sotrudnik s, zakaz_na_rem z
where s.id_sotr = z.number_sotr -- соединение таблиц
-- условия
and (s.id_sotr = in_id or in_id is null)
and (s.dolgnost = in_name or in_name is null)
order by s.id_sotr;
-- подсчет количества
select count(z.number_sotr), count(distinct(s.fio))
into out_dolgnost_count, out_fio_count
from sotrudnik s, zakaz_na_rem z
where s.id_sotr = z.number_sotr -- соединение таблиц
-- условия
and (s.id_sotr = in_id or in_id is null)
and (s.dolgnost = in_name or in_name is null);
end;/
Выполнение процедуры и её результат приведены на рисунках ниже:
Процедура поиска сотрудников по ид. и должности.
Изменение 1: добавить в выходные параметры общее количество найденных должностей и ФИО сотрудников.
Вариант с хранением промежуточных результатов в переменной табличного типа.
Исключено дублирование кода условий.
create or replace
procedure sotr_select_2(
out_rc out sys_refcursor, -- код результата
in_id in sotrudnik.id_sotr%type, -- вх/вых параметр - идентификатор
in_name in sotrudnik.dolgnost%TYPE,
out_dolgnost_count out integer, -- количество найденых должностей
out_fio_count out integer -- количество найденых ФИО сотрудников
) is
l_t tt_sotr_klient;
begin
select t_sotr_klient(s.id_sotr, z.number_sotr) -- конструируются экземпляры типа t_sotr_klient
bulk collect into l_t -- и собираются в переменную табличного типа
from sotrudnik s, zakaz_na_rem z
where s.id_sotr = z.number_sotr -- соединение таблиц
-- условия
and (s.id_sotr = in_id or in_id is null)
and (s.dolgnost = in_name or in_name is null);
open out_rc for
select s.id_sotr, s.fio,s.dolgnost, s.adres,
z.id_z, z.kod_rab, z.data_v, z.number_sotr
from sotrudnik s, zakaz_na_rem z, table(l_t) t
where s.id_sotr = t.sotr_id -- вместо условий - соединение с заранее подготовленной выборкой
and z.number_sotr = t.klient_id
order by z.number_sotr;
-- подсчет количества
select count(t.sotr_id), count(distinct(t.klient_id))
into out_dolgnost_count, out_fio_count
from table(l_t) t;
end; /
Выполнение процедуры и её результат приведены на рисунках ниже:
Процедура поиска с листованием
create or replace procedure sotr_select_list(
out_rc out sys_refcursor, -- код результата
in_page_size in integer, -- размер страницы данных
in_page_number in integer, -- номер страницы, начиная с 1
in_id in sotrudnik.id_sotr%type,
in_name in sotrudnik.dolgnost%TYPE
) is
begin
open out_rc for
select * from(
select rownum as rn,
s.id_sotr, s.fio,s.dolgnost, s.adres,
z.id_z, z.kod_rab, z.data_v, z.number_sotr
from sotrudnik s, zakaz_na_rem z
where s.id_sotr = z.number_sotr -- соединение таблиц
-- условия
and (s.id_sotr = in_id or in_id is null)
and (s.dolgnost = in_name or in_name is null)
order by s.id_sotr)
where rn > in_page_size*(in_page_number-1)
and rn<=in_page_size*in_page_number;
end;
Выполнение процедуры и её результат приведены на рисунках ниже:
Процедура поиска с листованием
Изменение 1: фильтрация данных до соединения.
create or replace
procedure sotr_select_list2(
out_rc out sys_refcursor, -- код результата
in_page_size in integer, -- размер страницы данных
in_page_number in integer, -- номер страницы, начиная с 1
in_id in sotrudnik.id_sotr%type, -- вх/вых параметр - идентификатор
in_name in sotrudnik.dolgnost%TYPE
) is
begin
open out_rc for
select * from(
select rownum as rn,
s.id_sotr, s.fio,s.dolgnost, s.adres,
z.id_z, z.kod_rab, z.number_rem_kart, z.data_v, z.number_sotr
from ( -- отфильтрованная по условию выборка клубов
select s.id_sotr, s.fio,s.dolgnost, s.adres
from sotrudnik s
where (s.id_sotr = in_id or in_id is null)
and (s.dolgnost = in_name or in_name is null)
) s,
( -- отфильтрованная по условию выборка игроков
select z.id_z, z.kod_rab, z.number_rem_kart, z.data_v, z.number_sotr
from zakaz_na_rem z
where (z.number_sotr = in_id or in_id is null)
) z
where z.number_sotr = s.id_sotr
order by z.number_sotr)
where rn > in_page_size*(in_page_number-1)
and rn<=in_page_size*in_page_number;
end;
Выполнение процедуры и её результат приведены на рисунках ниже:
Процедура поиска с листованием
Изменение 2: хранение промежуточных результатов выборки в переменной табличного типа.
create or replace
procedure sotr_select_list3(
out_rc out sys_refcursor, -- код результата
in_page_size in integer, -- размер страницы данных
in_page_number in integer, -- номер страницы, начиная с 1
in_id in sotrudnik.id_sotr%type, -- вх/вых параметр - идентификатор
in_name in sotrudnik.dolgnost%TYPE
) is
l_t tt_sotr_klient;
begin
select t_sotr_klient(id_sotr, number_sotr) bulk collect into l_t
from(
select rownum as rn,
s.id_sotr,
z.number_sotr
from sotrudnik s, zakaz_na_rem z
where s.id_sotr = z.number_sotr
and (s.id_sotr = in_id or in_id is null)
and (s.dolgnost = in_name or in_name is null)
order by z.number_sotr)
where rn > in_page_size*(in_page_number-1)
and rn<=in_page_size*in_page_number;
open out_rc for
select s.id_sotr, s.fio,s.dolgnost, s.adres,
z.id_z, z.kod_rab, z.number_rem_kart, z.data_v, z.number_sotr
from ( select distinct s.id_sotr, s.fio,s.dolgnost, s.adres
from sotrudnik s, table(l_t) t
where s.id_sotr = t.sotr_id
) s,
( select z.id_z, z.kod_rab, z.number_rem_kart, z.data_v, z.number_sotr
from zakaz_na_rem z, table(l_t) t
where z.number_sotr = t.klient_id
) z
where s.id_sotr = z.number_sotr
order by z.number_sotr;
end;/
Выполнение процедуры и её результат приведены на рисунках ниже:
