存储过程

存储过程记录日志模板

开始部分

   tempId := SEQ_CRM_OPT_LOG.Nextval;   INSERT INTO CRM_TASK_OPERATE_LOG      (ID, TASK_NAME, BEGIN_TIME, STATUS, TASK_TYPE)   VALUES       (tempId, 'SEQ_CRM_CUST_USER_INTERVAL', SYSDATE, 1, 1 );   COMMIT; ```      结束部分       detail_info := '[{"order":"1","name":"CRM_CUST_INTERVAL表插入数据","count":"' ||                                         sql%rowcount || '"}]';       UPDATE CRM_TASK_OPERATE_LOG T       SET T.END_TIME = SYSDATE, T.STATUS = 2, T.DETAIL = detail_info       where id = tempId;           COMMIT;       exception       when others then      dbms_output.put_line('存储过程PRO_CRM_CUST_SHOP，执行异常！');      rollback;      sql_errm := sqlerrm;      update CRM_TASK_OPERATE_LOG t       set t.end_time  = sysdate,           t.status    = 3,           t.detail    = detail,           t.exception = sql_errm      where id = tempId;     commit;**if elsif else 代码**   IF a>10 then    逻辑 ;   ELSIF （a>5  and a<=10） then   逻辑   ELSE   逻辑 ;   end IF;   **多层逻辑条件 并用and 或用or 或者是嵌套多层 if **   **程序中的elseif 和这个不一样，新手容易写错**<div class="se-preview-section-delimiter"></div>------------------- - 计算相邻的两条记录您的时间差（场景：计算用户订单的时间间隔） 原理分析 ：遍历当前记录游标，把当前的时间放到变量中，然后进行下一个记录时，进行相减，重置当前时  间，当变更用户后重置起始购买次数。脚本如下：<div class="se-preview-section-delimiter"></div>

mobile :=0;
shop_id :=0;
buy_count :=1;
gap :=0;
open user_order_list for ‘select dso.shop_id,dod.mobile,dso.create_time from dwh_order_delivery_ins dod
inner join dwh_sales_order_ins dso on dso.src_sys = dod.src_sys and dso.src_id = dod.src_id
inner join crm_shop cs on cs.shop_id = dso.shop_id
where dso.cancel_time is null
and dod.mobile is not null
and dso.order_type !=422
group by dso.shop_id,dod.mobile,dso.create_time
ORDER BY ’ || shop_mobile_create_time;

LOOP
FETCH user_order_list INTO v_shop_id,v_mobile,v_create_time;
EXIT WHEN user_order_list%NOTFOUND;
IF(mobile=v_mobile and shop_id=v_shop_id) then
select trunc(v_create_time)-trunc(pre_create_time) into gap from dual;
– 插入逻辑
insert into crm_cust_interval
(id, shop_id, mobile, interval_type, interval_day, create_time)
values
(seq_crm_cust_interval.nextval, v_shop_id, v_mobile, buy_count, gap+1, SYSDATE);
buy_count :=buy_count +1;
ELSE
buy_count :=1;

end if;
mobile :=v_mobile;
shop_id :=v_shop_id;
pre_create_time :=v_create_time;

end Loop;
“`