Oracle-存储过程常用

--字符串以逗号拆分

select regexp_substr('1001010009,1001010008,1001010007,1001010006', '[^,]+', 1, level)
        from dual
      connect by level <=
                 length('1001010009,1001010008,1001010007,1001010006') - length(REPLACE('1001010009,1001010008,1001010007,1001010006', ',', '')) + 1;

 
--存储过程中的循环

DECLARE
 CURSOR v_record_cusr IS
 select regexp_substr(p_ids, '[^,]+', 1, level)
from dual
 connect by level <=
length(p_ids) - length(REPLACE(p_ids, ',', '')) + 1;


begin
open v_record_cusr;
loop
 fetch v_record_cusr
into v_id;
 exit when v_record_cusr%NOTFOUND;
 --...
 --TODO
 --...
end loop;
close v_record_cusr;


exception
--错误异常处理部分
when others then
 dbms_output.put_line('[ERROR]:' || p_error_info); --打印错误信息
 rollback;
end;

--存储过程中变量动态赋值
  

select user_id
    into v_user_id
    from xxx
   where login_id = trim(p_login_id);