didelis dekui

"BigM" <mindaugask_NOSPAM@centras.lt> wrote in message 
news:i432s9$nts$1@trimpas.omnitel.net...
> va tau pilnas veikiantis sprendinys. Tobulinti galima atliekant papildomus 
> tikrinimus ir pan., bet juk reikia ir tau darbo palikti, ar ne? :)
>
> drop table sarysiai
> /
>
> create table sarysiai (id number, reverse number)
> /
>
> create or replace package sarysiai_statement is
>
> type t_tbl_sarysiai is table of sarysiai%rowtype index by binary_integer;
> tmp_sarysiai t_tbl_sarysiai;
> isvengti_rekursijos boolean := false;
> procedure chck_ins_relation(p_i in number);
>
> end sarysiai_statement;
> /
>
> create or replace package body sarysiai_statement is
>
> procedure chck_ins_relation(p_i in number) is
> v_tmp number;
> begin
> -- esam rekursijos pradzioje, tad uzdedam pozymi, kad neuzsiciklintu
> isvengti_rekursijos := true;
> begin
> select 1
> into v_tmp
> from sarysiai
> where id = tmp_sarysiai(p_i).reverse;
> raise too_many_rows;
> exception
> when no_data_found then
> null;
> when too_many_rows then
> raise_application_error(-20001, 'Jau egzistuoja atgalinis rysys!');
> end;
> if v_tmp is null then
> insert into sarysiai
> (id, reverse)
> values
> (tmp_sarysiai(p_i).reverse, tmp_sarysiai(p_i).id);
> end if;
> exception
> when others then
> raise_application_error(-20002, 'Kita klaida: ' || sqlerrm);
> end;
> end sarysiai_statement;
> /
>
> create or replace trigger sarysiai_trg_stm1
> before insert
> on sarysiai
> begin
> if not sarysiai_statement.isvengti_rekursijos then
> sarysiai_statement.tmp_sarysiai.delete;
> end if;
> end;
> /
>
> create or replace trigger sarysiai_trg_stm2
> after insert
> on sarysiai
> referencing new as new old as old
> for each row
> declare
> v_next number;
> begin
> if not sarysiai_statement.isvengti_rekursijos then
> v_next := nvl(sarysiai_statement.tmp_sarysiai.last, 0) + 1;
> sarysiai_statement.tmp_sarysiai(v_next).id := :new.id;
> sarysiai_statement.tmp_sarysiai(v_next).reverse := :new.reverse;
> end if;
> end;
> /
>
> create or replace trigger sarysiai_trg_stm3
> after insert
> on sarysiai
> begin
> if not sarysiai_statement.isvengti_rekursijos then
> for i in nvl(sarysiai_statement.tmp_sarysiai.first, 0) .. 
> nvl(sarysiai_statement.tmp_sarysiai.last,
> -1) loop
> sarysiai_statement.chck_ins_relation(i);
> end loop;
> sarysiai_statement.tmp_sarysiai.delete;
> end if;
> end;
> /
>
> insert into sarysiai values (1,2);
>
> commit;
>
> select * from sarysiai;
>
>
>
> Sekmes.
>
> "ledasl" <asai@pastas.lt> wrote in message 
> news:i42spp$fae$1@trimpas.omnitel.net...
>> jei nesunku, tai tikrai reiketu, nes kol kas toj vietoj is vietos nera 
>> kur judeti.
>