Задача. Загрузка данных с помощью full join.

Создать таблицу вкладчиков KK_DEPOSIT(DEPOSIT_ID INTEGER, CLIENT_NAME VARCHAR2(40), DT DATE, AMOUNT NUMBER).

Создать таблицу KK_ST_DEPOSIT, по структуре как KK_DEPOSIT, но с дополнительным полем ACTIVE, принимающим значения 'Y' или 'N'.

Первичным ключом в KK_DEPOSIT и KK_ST_DEPOSIT является поле DEPOSIT_ID.

Сделать индексы на поле DEPOSIT_ID в таблицах KK_DEPOSIT и KK_ST_DEPOSIT.

Написать процедуру по загрузке данных из KK_DEPOSIT в KK_ST_DEPOSIT по следующим правилам:

- Если запись есть в KK_DEPOSIT, но нет в KK_ST_DEPOSIT, то вставить ее в KK_ST_DEPOSIT с признаком ACTIVE = 'Y'

- Если запись отсутствует в KK_DEPOSIT, но есть в KK_ST_DEPOSIT, то проставить для нее в KK_ST_DEPOSIT признак ACTIVE = 'N'

- Если запись есть в KK_DEPOSIT и есть в KK_ST_DEPOSIT, то проапдейтить все поля в KK_ST_DEPOSIT значениями из KK_DEPOSIT.

У процедуры должно быть три OUT параметра - число совершенных inserts, deletes(disactivates), updates.

Создадим таблицы и заполним их данными:

CREATE TABLE KK_DEPOSIT(DEPOSIT_ID INTEGER, CLIENT_NAME VARCHAR2(40), DT DATE, AMOUNT NUMBER);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(1,'Карапетян',to_date('30.01.2011','dd.mm.yyyy'),123.89);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(2,'Карапетян',to_date('31.01.2011','dd.mm.yyyy'),129.8);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(3,'Саахов',to_date('24.09.2011','dd.mm.yyyy'),123123.91);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(5,'Саахов',to_date('13.05.2010','dd.mm.yyyy'),8000000);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(6,'Растрелли',to_date('10.01.2012','dd.mm.yyyy'),1100000);

CREATE TABLE KK_ST_DEPOSIT(DEPOSIT_ID INTEGER, CLIENT_NAME VARCHAR2(40), DT DATE, AMOUNT NUMBER, ACTIVE CHAR(1));

insert into kk_st_deposit(deposit_id,client_name,dt,amount,active) values(1,'Карапетян',to_date('30.01.2011','dd.mm.yyyy'),123.89,'Y');

insert into kk_st_deposit(deposit_id,client_name,dt,amount,active) values(2,'Карапетян',to_date('12.06.2012','dd.mm.yyyy'),700000,'N');

insert into kk_st_deposit(deposit_id,client_name,dt,amount,active) values(3,'Саахов',to_date('24.09.2011','dd.mm.yyyy'),123123.91,'Y');

insert into kk_st_deposit(deposit_id,client_name,dt,amount,active) values(4,'Саахов',to_date('27.11.2011','dd.mm.yyyy'),90.98,'Y');

insert into kk_st_deposit(deposit_id,client_name,dt,amount,active) values(5,'Саахов',to_date('13.05.2010','dd.mm.yyyy'),100,'Y');

Расскажем немного об индексах.

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

Индексы бывают неуникальными и уникальными. Уникальные индексы запрещают вставлять в проиндексированные наборы поле дублирующиеся значения (кроме NULL).

Кроме того индексы бывают различных типов (по организации) NORMAL, BITMAP, FUNCTION-BASED и т.п. Но в нашей задаче мы не будем детально расписывать эти опции в получаемом скрипте создания индексов.

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

create unique index ix_kk_deposit on kk_deposit (deposit_id);

create unique index ix_kk_st_deposit on kk_st_deposit (deposit_id);

Напишем требуемую процедуру:

create or replace procedure load_kk_st_deposit(inserts_count out integer,

deletes_count out integer,

updates_count out integer) is

begin

inserts_count := 0;

deletes_count := 0;

updates_count := 0;

for z in (select n.deposit_id new_deposit_id,

n.client_name new_client_name,

n.dt new_dt,

n.amount new_amount,

o.deposit_id old_deposit_id

from kk_deposit n

full outer join kk_st_deposit o

on n.deposit_id = o.deposit_id) loop

if z.old_deposit_id is null then

-- Если запись есть в KK_DEPOSIT, но нет в KK_ST_DEPOSIT, то вставить ее в KK_ST_DEPOSIT с признаком ACTIVE = 'Y'

insert into kk_st_deposit

(deposit_id, client_name, dt, amount, active)

values

(z.new_deposit_id, z.new_client_name, z.new_dt, z.new_amount, 'Y');

inserts_count := inserts_count + 1;

elsif z.new_deposit_id is null then

-- Если запись отсутствует в KK_DEPOSIT, но есть в KK_ST_DEPOSIT, то проставить для нее в KK_ST_DEPOSIT признак ACTIVE = 'N'

update kk_st_deposit

set active = 'N'

where deposit_id = z.old_deposit_id;

deletes_count := deletes_count + 1;

elsif z.new_deposit_id = z.old_deposit_id then

-- Если запись есть в KK_DEPOSIT и есть в KK_ST_DEPOSIT, то проапдейтить все поля в KK_ST_DEPOSIT значениями из KK_DEPOSIT.

update kk_st_deposit

set client_name = z.new_client_name,

dt = z.new_dt,

amount = z.new_amount,

active = 'Y'

where deposit_id = z.old_deposit_id;

updates_count := updates_count + 1;

end if;

end loop;

commit;

end load_kk_st_deposit;

Вызовем ее и посмотрим dbms_output:

declare

i integer;

d integer;

u integer;

begin

load_kk_st_deposit(i, d, u);

dbms_output.put_line('Вставлено новых активных записей ' || i);

dbms_output.put_line('Дезактивировано старых записей ' || d);

dbms_output.put_line('Изменено имеющихся записей ' || u);

end;

Посмотрим, что загрузилось в kk_st_deposit:

select * from kk_st_deposit;