Pokračování z minula, kde byla řeč o novince, která umožní deklarovat PL/SQL funkci v rámci sql selectu. Dnes hint PLSQL_WITH a o tom, jak je to možné použít v DELETE, INSERT, UPDATE. Nějaká omáčka, byla minule takže rovnou příklady a ukázky. Nejprve klasicky zakládám nějakou tabulku na které to bude dobře vidět. Často když se tu snažím něco ukázat tak použivám typicky CONNECT BY LEVEL<xxx na získání xxx-1 řádků a CTAS (create table as select) z duálu. Dnes je tam navíc PL/SQL funkce z minulé novinky. Tedy ano, WITH a PL/SQL funkci mohu použít v CTASu. A v rámci cvičení takto činním (nomrálně raději pomocí RPAD):
create table tbl_test_function as
with function with_f_add_xxx(p_a varchar2,p_b number) return varchar2
is
ls_result varchar2(20);
begin
ls_result:=p_a;
for i in 1..p_b loop
ls_result:=ls_result || 'x';
end loop;
return ls_result;
end;
select with_f_add_xxx('F',MOD(ROWNUM,10)) as sloupec from dual connect by level<101;
Červeně výše zmíněná novinka z minula, druhá novinka, která tu není vidět, jsou počítané statistiky v rámci CTASu. Takže to by byla nějaká taková testovací tabulka, data asi takto:
Takže zkusme DELETE:
delete /*+ with_plsql */ tbl_test_function my_alias
where sloupec=(with function print_delete(p_what varchar2) return varchar2
is begin
dbms_lock.sleep(0.25);
dbms_output.put_line('Mazani: '||p_what);
return p_what;
end;
select print_delete(my_alias.sloupec) from dual
);
Funkce (respektive její provolání z duálu) vrací zpět tu samou hodnotu, takže dojde ke smazání všech řádků. Po každém řádku se čeká 250ms a na output je vytištěna konkrétní hodnota. To by byl DELETE, tabulka je prázdná, takže se nabízí zkusit si to zase na INSERT:
insert /*+ with_plsql */ into tbl_test_function (sloupec)
with function just_return_x return varchar2 is
begin
return 'x';
end;
select just_return_x from dual;
Což insertne jeden řádek s hodnotou “x” v jednom jedinném sloupci. UPDATE:
update /*+ with_plsql */ tbl_test_function my_alias set
sloupec=(with function set_upper(p_sl varchar2) return varchar2
is
begin
return upper(p_sl);
end;
select set_upper(my_alias.sloupec) from dual)
where sloupec=(with function just_return_x return varchar2 is
begin
return 'x';
end;
select just_return_x from dual);
Funkci jde deklarovat jak v set klauzuli (u mě set_upper, která vrací pouze UPPER), tak ve where podmínce (u mě just_return_x, která vrací pouze ‘x’. A poslední věc, která chybí je MERGE:
alter table tbl_test_function add (sloupec_2 varchar2(10));
merge /*+ with_plsql */ into tbl_test_function my_alias
using (with function return_big_x(p_rownum number) return varchar2 is
begin
if p_rownum=1 then return 'X'; else return 'A'; end if;
end;
select return_big_x(rownum) fn_result from dual connect by level <= 1) s
on (my_alias.sloupec = s.fn_result)
when matched then update set my_alias.sloupec_2 =
(with function just_return_2x return varchar2 is
begin
return 'xx';
end;
select just_return_2x from dual)
when not matched then insert (my_alias.sloupec) values ('P');
Tak a to by byl příklad za merge. Kupodivu jsem nanašel, že by to Oracle podporovalo i v insert části MERGE. Jinak to jde pl/sql funkci napsat snad všude 
ALTER table předtím, protože nelze udělat tento merge nad tabulkou s jedním sloupcem (nelze updatovat sloupec, který je vzároven v on klauzuli).
