1):行转列函数:
select wmsys.wm_concat(identify_id)
into v_identifyIds
from t_identify_relevance
where PROD_INST_NUM = in_phone;
2):采用dblink连接,通过dblink使用存储过程,过程中不能含有包括commit,及rollback
3):oracle中存储过程执行动态sql
v_sql := 'select a.info_config_id from (
select ic.info_config_id
from t_info_config ic,
t_marketing_info mi,
t_strategy_config sc
where mi.info_id = ic.info_id
and sc.strategy_id = mi.strategy_id
and mi.info_status = ''2''
and sc.strategy_id = ''522''
and ic.identify_id in (' ||
v_identifyIds || ')' || ' order by ic.create_time desc' ||
' ) a where rownum = 1';
execute immediate v_sql
into v_time;
4)存储过程中采用下面方式截获异常,如果需要跟踪异常,可在异常捕获块将异常添加到表中,方便查证
exception
when others then
--rollback;
v_errormessage := sqlerrm;
--raise_application_error(-20201,v_errormessage);
end;
5)