存储过程

最新推荐文章于 2025-04-23 15:47:01 发布

mid120

最新推荐文章于 2025-04-23 15:47:01 发布

阅读量306

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库 oracle 文章标签：存储 crm 编辑器

本文链接：https://blog.youkuaiyun.com/mid120/article/details/52193596

数据库同时被 2 个专栏收录

12 篇文章

订阅专栏

oracle

6 篇文章

订阅专栏

存储过程记录日志模板

开始部分

   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;
“`