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