存储过程oracle pg/sql

1.。。pg版本

create or replace  function pro_cust_interval_first() returns void as
$$
declare
    shop_id        bzcrm.t_crm_shop_customer_interval.customer_id%type;
    customer_id    bzcrm.t_crm_shop_customer_interval.shop_id%type;
  buy_count    bzcrm.t_crm_shop_customer_interval.interval_type%type;
  gap          bzcrm.t_crm_shop_customer_interval.interval_day%type;
  pre_create_time timestamp;
  order_status varchar(80);
  last_record_time timestamp;
       order_list_cusor refcursor;  


  v_begin_time timestamp;


  v_shop_id      bzcrm.t_crm_shop_customer_interval.customer_id%type;
  v_customer_id  bzcrm.t_crm_shop_customer_interval.shop_id%type;
  v_create_time timestamp;
  v_order_status varchar;




  
begin
--初始化赋值
shop_id :=0;
customer_id :=0;
buy_count :=1;
gap :=1;
  open order_list_cusor for  execute  'select crm_shop_id ,customer_id,create_time,order_status from   bzcrm.t_crm_order  group by crm_shop_id,customer_id,order_status,create_time order by create_time';


     loop  
       fetch order_list_cusor into v_shop_id,v_customer_id,v_create_time,v_order_status;  
         
       if found then  
           raise notice '%-%',v_shop_id,v_customer_id;  
                if shop_id =v_shop_id and customer_id =v_customer_id and order_status = v_order_status THEN 
                --相减获得间隔天数
                select  date_part('day', v_create_time - pre_create_time) into gap;
                --插入逻辑
                INSERT INTO bzcrm.T_CRM_SHOP_CUSTOMER_INTERVAL
                   ( SHOP_ID, CUSTOMER_ID,ORDER_STATUS, INTERVAL_TYPE, INTERVAL_DAY, CREATE_TIME,ACTIVE)
                VALUES
                   (V_SHOP_ID, V_CUSTOMER_ID,V_ORDER_STATUS, BUY_COUNT, GAP+1, now(),1);
                buy_count := buy_count +1;
               else
                 buy_count := 1;
               end if;
                --一次循环完逻辑。
shop_id :=v_shop_id;
customer_id :=v_customer_id;
pre_create_time :=v_create_time;
order_status :=v_order_status;
       else  
           exit;  
       end if;  




   end loop;  
   close order_list_cusor;  
   raise notice 'the end of msg...';  


exception when others then  
   raise exception 'error--(%)','errr';  
end;  
$$  

 language plpgsql; 

2.。。oracle  版本

   CREATE OR REPLACE PROCEDURE PRO_CUST_INTERVAL_FIRST IS
-- 说明：订单间隔表数据 第一次导入
--




   TEMPID         NUMBER;
   SQL_ERRM       VARCHAR2(1500);
   DETAIL_INFO    VARCHAR2(1500);
   SHOP_ID        VARCHAR2(36);
   CUSTOMER_ID    VARCHAR2(36);
   BUY_COUNT    NUMBER(19);
   GAP          NUMBER(20);
   PRE_CREATE_TIME TIMESTAMP;
   ORDER_STATUS VARCHAR2(80);
   LAST_RECORD_TIME TIMESTAMP;




   V_BEGIN_TIME TIMESTAMP;


   V_SHOP_ID      VARCHAR2(36);
   V_CUSTOMER_ID  VARCHAR2(36);
   V_CREATE_TIME TIMESTAMP;
   V_ORDER_STATUS VARCHAR2(80);


   USER_ORDER_LIST  SYS_REFCURSOR;


    SHOP_MOBILE_CREATE_TIME VARCHAR2(150) :='SO.CRM_SHOP_ID,SO.CUSTOMER_ID,SO.CREATE_TIME,SO.ORDER_STATUS' ;




BEGIN


   TEMPID := SEQ_CRM_OPT_LOG.NEXTVAL;
   INSERT INTO CRM_TASK_OPERATE_LOG
      (ID, TASK_NAME, BEGIN_TIME, STATUS, TASK_TYPE)
   VALUES
       (TEMPID, 'PRO_CUST_INTERVAL_FIRST', SYSDATE, 1, 1 );
  --删除


DELETE FROM T_DM_SHOP_CUSTOMER_INTERVAL T WHERE T.ORDER_STATUS != 'ALL';




   COMMIT;






-- 遍历游标
  CUSTOMER_ID :=0;
  SHOP_ID :=0;
  BUY_COUNT :=1;
  GAP :=0;
  V_BEGIN_TIME := SYSDATE;
--交易类型: WAIT_BUYER_PAY(下单未付款)  TRADE_CLOSED(交易自动关闭) TRADE_CLOSED_BY_TAOBAO(主动关闭交易 TRADE_FINISHED(交易成功)
OPEN USER_ORDER_LIST FOR
        'SELECT SO.CRM_SHOP_ID,SO.CUSTOMER_ID,SO.CREATE_TIME,SO.ORDER_STATUS
        FROM T_DM_SALES_ORDER SO
        WHERE SO.ORDER_STATUS IN (''WAIT_BUYER_PAY'',''TRADE_FINISHED'',''TRADE_CLOSED'',''TRADE_CLOSED_BY_TAOBAO'')
        AND SO.RECORD_STATUS = 5004
        GROUP BY SO.CRM_SHOP_ID,SO.CUSTOMER_ID,SO.ORDER_STATUS,SO.CREATE_TIME
        ORDER BY ' || SHOP_MOBILE_CREATE_TIME;




  LOOP
    FETCH USER_ORDER_LIST INTO V_SHOP_ID,V_CUSTOMER_ID,V_CREATE_TIME,V_ORDER_STATUS;
    EXIT WHEN USER_ORDER_LIST%NOTFOUND;
   IF(CUSTOMER_ID=V_CUSTOMER_ID AND SHOP_ID=V_SHOP_ID AND ORDER_STATUS=V_ORDER_STATUS) THEN
      SELECT TRUNC(V_CREATE_TIME)-TRUNC(PRE_CREATE_TIME)  INTO GAP FROM DUAL;
  -- 插入逻辑
     INSERT INTO T_DM_SHOP_CUSTOMER_INTERVAL
     (CUSTOMER_INTERVAL, SHOP_ID, CUSTOMER_ID,ORDER_STATUS, INTERVAL_TYPE, INTERVAL_DAY, CREATE_TIME,ACTIVE)
      VALUES
     (SEQ_CRM_CUST_INTERVAL.NEXTVAL, V_SHOP_ID, V_CUSTOMER_ID,V_ORDER_STATUS, BUY_COUNT, GAP+1, SYSDATE,1);
      BUY_COUNT :=BUY_COUNT +1;
  ELSE
     BUY_COUNT :=1;




   END IF;
   CUSTOMER_ID :=V_CUSTOMER_ID;
   SHOP_ID :=V_SHOP_ID;
   PRE_CREATE_TIME :=V_CREATE_TIME;
   ORDER_STATUS :=V_ORDER_STATUS;
   COMMIT;
   END LOOP;


  DETAIL_INFO := '[{"ORDER":"1","NAME":"T_DM_SHOP_CUSTOMER_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;


    PRO_CUST_INTERVAL_ALL_FIRST();


    SELECT  NVL(MAX(SO.MODIFIED_TIME),MAX(SO.CREATE_TIME)) INTO LAST_RECORD_TIME
        FROM T_DM_SALES_ORDER SO;


  INSERT INTO T_DM_SCHEDULE_MONITOR
  (SCHEDULE_MONITOR_ID,SCHEDULE_NAME,SCHEDULE_CODE,SCHEDULE_DETAIL,BEGIN_TIME,END_TIME,SCHEDULE_STATUS,LAST_RECORD_TIME,LAST_BATCH_NO)
  VALUES
  (BAOZUN_SEQUENCE.NEXTVAL,'CustomerIntervalJob','Schedule_06','',V_BEGIN_TIME,SYSDATE,1,LAST_RECORD_TIME,'');




   COMMIT;


   EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('存储过程PRO_CUST_INTERVAL_FIRST，执行异常！');
    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;




END PRO_CUST_INTERVAL_FIRST;