Thursday, October 21, 2010

ORCL - How cool it is!

在oracle中把连串字符转变为table fields,原来要做loop很麻烦,现在用expression就行了。


真爽!特此记录,怕以后忘了,呵呵。

if A_PROSPECTIDS is not null

then

-- remove from prospects_main any not in list of ids:

T_UPDATESQL := 'delete from prospects_main pm where pm.id_number

not in (( select lpad(trim(regexp_substr(a_prospectids,'[^,]+{1}',1,level)), 10, 0) ids from dual

connect by level <= length(regexp_replace(a_prospectids,'[^,]*'))+1 ) ';

execute immediate T_UPDATESQL;

commit;

exception

when others then

RAISE_APPLICATION_ERROR(-20999,

priority_prospects: Error removing prospects: ' ||

sqlcode || ': ' || sqlerrm);

end;

end if;


另外在用动态的SQL (dynamic SQL)时,变量要用加串,两个双引号相当于一个单引号:

lpad(trim(regexp_substr(' ||''''|| t_formattedids ||'''' || ',' || '''' || '[^,]+{1}' || '''' || ',1,level)), 10, 0) ids from dual

connect by level <= length(regexp_replace(' ||''''|| t_formattedids ||'''' || ',' || '''' || '[^,]*' || '''' || '))+1