Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 0
Feb 022014
 

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:

Data po WITH

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).

 Posted by at 16:37

 Leave a Reply

(required)

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>