存储过程备份

CREATE OR REPLACE PROCEDURE up_hb_henanyongle
(
   av_return                        OUT VARCHAR2                   -- 成功标识
)
AS
-- ## Define Variable(eternal)
  n_mark_flag                NUMBER(3)            := 0;                      -- DUBEG: MARK Flag

-- flag                       NUMBER;                                           -- 标识



BEGIN

   --v_sql                      := 'truncate table err_hb_henanyongle';
   --EXECUTE IMMEDIATE v_sql;
   --v_sql                      := 'truncate table mid_hb_henanyongle';
   --EXECUTE IMMEDIATE v_sql;


   -- 1. 根据会员卡号统计品类购买情况
   n_mark_flag					:= 20;
   for obj in (
      select 
  		  MEMBER_CARD_NUMBER     ,
		  sum(CASE when trim(PURCHASE_DEPT)='彩电' then commodity_count end) as   	caidian,
		  sum(CASE when trim(PURCHASE_DEPT)='冰箱' then commodity_count end) as   	bingxiang,
		  sum(CASE when trim(PURCHASE_DEPT)='冰柜' then commodity_count end) as   	binggui,
		  sum(CASE when trim(PURCHASE_DEPT)='洗衣机' then commodity_count end) as   xiyiji,
		  sum(CASE when trim(PURCHASE_DEPT)='空调' then commodity_count end) as   	kongtiao,
		  sum(CASE when trim(PURCHASE_DEPT)='手机' then commodity_count end) as   	shouji,
		  sum(CASE when trim(PURCHASE_DEPT)='电脑' then commodity_count end) as   	diannao,
		  sum(CASE when trim(PURCHASE_DEPT)='照相机' then commodity_count end) as  	zhaoxiangji,
		  sum(CASE when trim(PURCHASE_DEPT)='摄像机' then commodity_count end) as  	shexiangji,
		  sum(CASE when trim(PURCHASE_DEPT)='白色小件' then commodity_count end) as	baisexiaojian,
		  sum(CASE when trim(PURCHASE_DEPT)='黑色小件' then commodity_count end) as heisexiaojian,
		  sum(CASE when trim(PURCHASE_DEPT)='微波炉' then commodity_count end) as 	weibolu,
		  sum(CASE when trim(PURCHASE_DEPT)='热水器' then commodity_count end) as 	reshuiqi,
		  sum(CASE when trim(PURCHASE_DEPT)='消毒柜' then commodity_count end) as 	xiaodugui,
		  sum(CASE when trim(PURCHASE_DEPT)='抽油烟机' then commodity_count end) as chouyouyanji,
		  sum(CASE when trim(PURCHASE_DEPT)='灶具' then commodity_count end) as  	zaoju,
		  sum(CASE when trim(PURCHASE_DEPT)='洗碗机' then commodity_count end) as  	xiwanji,
		  sum(CASE when trim(PURCHASE_DEPT)='音响、碟机' 
        			or trim(PURCHASE_DEPT)='碟机' 
        			or trim(PURCHASE_DEPT)='音响' 
    				then commodity_count end) as  									yinxiangdieji,
  		  sum(CASE when trim(PURCHASE_DEPT)='OA' 
			        OR trim(PURCHASE_DEPT)='点钞机'
			        OR trim(PURCHASE_DEPT)='保管箱'
			        OR trim(PURCHASE_DEPT)='保险柜'
			        OR trim(PURCHASE_DEPT)='打印机'
			        OR trim(PURCHASE_DEPT)='复印机'
			        OR trim(PURCHASE_DEPT)='传真机'
			        OR trim(PURCHASE_DEPT)='扫描仪'
			        OR trim(PURCHASE_DEPT)='碎纸机'
			        OR trim(PURCHASE_DEPT)='一体机'
			        OR trim(PURCHASE_DEPT)='碎纸机'
			        OR trim(PURCHASE_DEPT)='墨盒'
			        OR trim(PURCHASE_DEPT)='硒鼓'
			        OR trim(PURCHASE_DEPT)='电话机'
			        OR trim(PURCHASE_DEPT)='有线电话'
			        OR trim(PURCHASE_DEPT)='办公家具'
			        OR trim(PURCHASE_DEPT)='计算器'
			        OR trim(PURCHASE_DEPT)='其他耗材' 
      					then commodity_count end) as  									OA,
		  sum(CASE when trim(PURCHASE_DEPT)='会员商品' 
			      or trim(PURCHASE_DEPT)='会员礼品' 
			      or trim(PURCHASE_DEPT)='会员定制品' 
			    	then commodity_count end) as  										huiyuanshangpin,
		  sum(CASE when trim(PURCHASE_DEPT)='医疗保健器材' then commodity_count end) as  yiliaobaojianqicai,
		  sum(CASE when trim(PURCHASE_DEPT)='厨卫小件' then commodity_count end) as 	chuweixiaojian,
		  sum(CASE when trim(PURCHASE_DEPT)='数码黑小' then commodity_count end) as 	shumaheixiao,
		  sum(CASE when trim(PURCHASE_DEPT)='传统配件'
		 		OR trim(PURCHASE_DEPT)='彩电配件'
		        OR trim(PURCHASE_DEPT)='空调配件'
		        OR trim(PURCHASE_DEPT)='音响配件'
		        OR trim(PURCHASE_DEPT)='冰洗配件'
		  		then commodity_count end) as 											chuangtongpeijian,
		  sum(CASE when trim(PURCHASE_DEPT)='摄照配件' then commodity_count end) as 	shexiangpeijian,
		  sum(CASE when trim(PURCHASE_DEPT)='电脑配件' then commodity_count end) as 	diannaopeijian,
		  sum(CASE when trim(PURCHASE_DEPT)='手机配件' then commodity_count end) as 	shoujipeijian,
		  sum(CASE when trim(PURCHASE_DEPT)='延保' then commodity_count end) as 		yanbao,
		  sum(CASE when trim(PURCHASE_DEPT)='运营商' then commodity_count end) as 		yunyingshang
  from bi.mid_hb_qingdao where member_card_number is not null group by MEMBER_CARD_NUMBER
   ) loop
   
		insert into ODS_HB_QINGDAO(
				  MEMBER_CARD_NUMBER        ,
				  TV                        ,
				  FRIDGE                    ,
				  FREEZER                   ,
				  WASHING_MACHINE           ,
				  AIR_CONDITIONING          ,
				  MOBILE                    ,
				  PC                        ,
				  CAMERA                    ,
				  VIDEO_CAMERA              ,
				  WHITE_SMALL               ,
				  BLACK_SMALL               ,
				  MICROWAVE_OVENS           ,
				  WATER_HEATER              ,
				  DISINFECTION_CABINET      ,
				  HOOD                      ,
				  STOVE                     ,
				  DISHWASHER                ,
				  AUDIO_CHANGER             ,
				  OA                        ,
				  MEMBER_COMMODITY          ,
				  HEALTH_CARE_EQUIPMENT     ,
				  SMALL_KITCHEN             ,
				  DIGITAL_BLACK_SMALL ,     ,
				  CHUANTONG_ACCESSORIES     ,
				  CAMERA_PHOTO_ACCESSORIES  ,
				  COMPUTER_ACCESSORIES      ,
				  MOBILE_ACCESSORIES        ,
				  WARRANTY                  ,
				  OPERATORS                 
			) values(                       
		    	obj.MEMBER_CARD_NUMBER		,
				obj.caidian					,
				obj.bingxiang				,
				obj.binggui					,
				obj.xiyiji					,
				obj.kongtiao				,
				obj.shouji					,
				obj.diannao					,
				obj.zhaoxiangji				,
				obj.shexiangji				,
				obj.baisexiaojian			,
				obj.heisexiaojian			,
				obj.weibolu					,
				obj.reshuiqi				,
				obj.xiaodugui				,
				obj.chouyouyanji			,
				obj.zaoju					,
				obj.xiwanji					,
				obj.yinxiangdieji			,
				obj.OA						,
				obj.huiyuanshangpin			,
				obj.yiliaobaojianqicai		,
				obj.chuweixiaojian			,
				obj.shumaheixiao			,
				obj.chuangtongpeijian		,
				obj.shexiangpeijian			,
				obj.diannaopeijian			,
				obj.shoujipeijian			,
				obj.yanbao					,
				obj.yunyingshang	
		);
		
   END LOOP;
   COMMIT;
   
   -- 2. 单品类购买情况 标签
   n_mark_flag					:= 21;		
   		-- 2.1 彩电,冰箱,冰柜,洗衣机,电脑,照相机>5 为异常1
   			UPDATE bi.ods_hb_qingdao SET
				SINGLE_CATEGORY_NUMBER = '异常①' 
			WHERE  TV>5 
			OR FRIDGE>5
			OR FREEZER>5 
			OR WASHING_MACHINE >5 
			OR PC>5 
			OR CAMERA>5;
			--2.2 空调>10次,为异常2
			UPDATE bi.ods_hb_qingdao SET
				SINGLE_CATEGORY_NUMBER = '异常②'
			WHERE AIR_CONDITIONING >10 AND SINGLE_CATEGORY_NUMBER is null;
			--2.2 两者都符合,标记异常1,2
			UPDATE ods_hb_qingdao SET
				SINGLE_CATEGORY_NUMBER = '异常①②'
			WHERE AIR_CONDITIONING >10 AND SINGLE_CATEGORY_NUMBER ='异常①';
			
			COMMIT;
			
	-- 3. 购买次数大约20次的,标记违规 标签更新
	n_mark_flag					:= 22;	
		UPDATE bi.ods_hb_qingdao SET CATEGORY_TOTAL_NUMBER = '违规' 
		WHERE
			NVL(TV,0)+
			NVL(FRIDGE,0)+
			NVL(FREEZER,0)+
			NVL(WASHING_MACHINE,0)+
			NVL(AIR_CONDITIONING,0)+
			NVL(MOBILE,0)+
			NVL(PC,0)+
			NVL(CAMERA,0)+
			NVL(VIDEO_CAMERA,0)+
			NVL(WHITE_SMALL,0)+
			NVL(BLACK_SMALL,0)+
			NVL(MICROWAVE_OVENS,0)+
			NVL(WATER_HEATER,0)+
			NVL(DISINFECTION_CABINET,0)+
			NVL(HOOD,0)+
			NVL(STOVE,0)+
			NVL(DISHWASHER,0)+
			NVL(AUDIO_CHANGER,0)+
			NVL(OA,0)+
			NVL(MEMBER_COMMODITY,0)+
			NVL(HEALTH_CARE_EQUIPMENT,0)+
			NVL(SMALL_KITCHEN,0)+
			NVL(DIGITAL_BLACK_SMALL,0)+
			NVL(CHUANTONG_ACCESSORIES,0)+
			NVL(CAMERA_PHOTO_ACCESSORIES,0)+
			NVL(COMPUTER_ACCESSORIES,0)+
			NVL(MOBILE_ACCESSORIES,0)+
			NVL(WARRANTY,0)+
			NVL(OPERATORS,0) > 20;
			
		COMMIT;
		
	
	-- 4. 更新客户购买频度标签	(频度=购买的总数量/最后消费日期-登记日期(月))
	n_mark_flag					:= 23;	
	UPDATE bi.ods_hb_qingdao c SET 
		FREQUENCY_CUSTOMER_PURCHASES=ROUND(c.CONSUME_ALL_DEGREE / MONTHS_BETWEEN(c.END_CONSUME_DATE,c.ENTER_DATE),2) 
		WHERE END_CONSUME_DATE IS NOT NULL AND ENTER_DATE IS NOT NULL 
	  	AND  MONTHS_BETWEEN(END_CONSUME_DATE,ENTER_DATE) <> 0;
	COMMIT;
	
	-- 5. 根据会员卡号,更新会员基本信息
	n_mark_flag					:= 24;	
	for obj in (
     select 
		    MEMBER_CARD_NUMBER          ,
		    OLD_MEMBER_CARD_NUMBER      ,
		    MEMBER_TYPE                 ,
		    NAME                        ,
		    CONSUME_ALL_MONEY           ,
		    LEAVE_ALL_INTEGRAL          ,
		    CONSUME_ALL_INTEGRAL        ,
		    CONSUME_ALL_DEGREE          ,
		    SEX                         ,
		    BRITHDAY                    ,
		    CREDENTIALS_ID              ,
		    IMPORTANT_PHONE             ,
		    HOME_PHONE                  ,
		    OTHER_PHONE                 ,
		    MEMBER_AREA                 ,
		    ADDRESS                     ,
		    POSTALCODE                  ,
		    REMARK                      ,
		    ENTER_DATE                  ,
		    BUILD_CARD_SHOP             ,
		    END_CONSUME_DATE   			,
		    ATTESTATION_DATE			,
		    V_OLD_CLASS
  	 	from(select 
						MEMBER_CARD_NUMBER          ,
						OLD_MEMBER_CARD_NUMBER      ,
						MEMBER_TYPE                 ,
						NAME                        ,
						CONSUME_ALL_MONEY           ,
					    LEAVE_ALL_INTEGRAL          ,
					    CONSUME_ALL_INTEGRAL        ,
					    CONSUME_ALL_DEGREE          ,
					    SEX                         ,
					    BRITHDAY                    ,
					    CREDENTIALS_ID              ,
					    IMPORTANT_PHONE             ,
					    HOME_PHONE                  ,
					    OTHER_PHONE                 ,
					    MEMBER_AREA                 ,
	    				ADDRESS                     ,
	    				POSTALCODE                  ,
	    				REMARK                      ,
	    				ENTER_DATE 					,
	    				BUILD_CARD_SHOP             ,
	    				END_CONSUME_DATE			,
	    				ATTESTATION_DATE			,
	    				V_OLD_CLASS,
    					row_number() over (PARTITION BY member_card_number ORDER BY end_consume_date) id
    					FROM bi.mid_hb_qingdao) 
    	WHERE id<=1;
   ) LOOP
	
	UPDATE bi.ods_hb_qingdao m
	SET
	  		m.OLD_MEMBER_CARD_NUMBER	=		obj.OLD_MEMBER_CARD_NUMBER 										,
      		m.MEMBER_TYPE           	=		obj.MEMBER_TYPE            										,
      		m.NAME                  	=		obj.NAME                   										,
      		m.CONSUME_ALL_MONEY     	=		obj.CONSUME_ALL_MONEY      										,
      		m.LEAVE_ALL_INTEGRAL    	=		obj.LEAVE_ALL_INTEGRAL     										,
      		m.CONSUME_ALL_INTEGRAL  	=		obj.CONSUME_ALL_INTEGRAL   										,
      		m.CONSUME_ALL_DEGREE    	=		obj.CONSUME_ALL_DEGREE     										,
      		m.SEX                   	=		obj.SEX                    										,
      		m.BRITHDAY              	=		obj.BRITHDAY               										,
      		m.CREDENTIALS_ID        	=		obj.CREDENTIALS_ID         										,
      		m.IMPORTANT_PHONE       	=		obj.IMPORTANT_PHONE        										,
      		m.HOME_PHONE            	=		obj.HOME_PHONE             										,
      		m.OTHER_PHONE           	=		obj.OTHER_PHONE            										,
      		m.MEMBER_AREA           	=		obj.MEMBER_AREA            										,
      		m.ADDRESS               	=		obj.ADDRESS                										,
      		m.POSTALCODE            	=		obj.POSTALCODE             										,
      		m.REMARK                	=		obj.REMARK                 										,
      		m.ENTER_DATE            	=		to_date(substr(obj.ENTER_DATE,1,10),'YYYY-mm-dd')               ,
      		m.BUILD_CARD_SHOP       	=		obj.BUILD_CARD_SHOP       										,
      		m.END_CONSUME_DATE      	=		to_date(substr(obj.END_CONSUME_DATE ,1,10),'YYYY-mm-dd')		,
      		m.ATTESTATION_DATE      	=		obj.ATTESTATION_DATE											,
      		m.age           			=		obj.V_OLD_CLASS
    where   m.MEMBER_CARD_NUMBER 		= 		obj.MEMBER_CARD_NUMBER;	

   END LOOP;
   COMMIT;

	-- 6. 服务请求 标签
	n_mark_flag					:= 25;	
	for obj in (
	     SELECT     
		      phone,
		      zixun_numbers 
      	FROM bi.mid_zixun;
	   ) LOOP
	   -- 更新
		UPDATE bi.ods_hb_qingdao m
	 	SET
	      m.CONSULTATION_REQUEST = a.zixun_numbers
	   	 WHERE m.IMPORTANT_PHONE = a.phone
	  			OR m.HOME_PHONE  = a.phone
	  			OR m.OTHER_PHONE = a.phone;
	END LOOP;
    COMMIT;
    
    -- 7. 投诉 标签 
    n_mark_flag					:= 26;
	for obj in (
	     SELECT     
		      phone,
		      tousu_numbers 
      	FROM mid_tousu;
	   ) LOOP
	   --更新
		UPDATE ods_hb_qingdao m
 			 SET
      			  m.COMPLAINTS = a.tousu_numbers
    		WHRER m.IMPORTANT_PHONE  = a.phone
  					OR m.HOME_PHONE  = a.phone
  					OR m.OTHER_PHONE = a.phone;

	END LOOP;
    COMMIT;
    
    -- 8 退货标签
    n_mark_flag					:= 26;
    for obj in (
	    SELECT     
     		 phone,
     		 TUIHUANHUO_NUMBER,TYPE 
      	FROM tuihuanhuo_temp WHERE trim(TYPE) = '退货';
	   ) LOOP
	   --更新
		UPDATE ods_hb_qingdao m
  		SET
      		   m.RETURNS = a.tuihuanhuo_number
   		 where m.IMPORTANT_PHONE  = a.phone
    			 OR m.home_phone  = a.phone 
     			 OR m.other_phone = a.phone;

	END LOOP;
    COMMIT;

	-- 9. 换货标签
	n_mark_flag					:= 26;
	for obj in (
	   SELECT     
      		phone,
      		TUIHUANHUO_NUMBER,TYPE 
      FROM tuihuanhuo_temp WHERE trim(TYPE) = '换货';
	   ) LOOP
	   --更新
		 UPDATE ods_hb_qingdao m
 		 SET
      		m.REPLACEMENT = a.tuihuanhuo_number
    	WHERE m.IMPORTANT_PHONE  = a.phone
     			OR m.home_phone  = a.phone 
     			OR m.other_phone = a.phone;

	END LOOP;
    COMMIT;
   
DBMS_OUTPUT.put_line( n_mark_flag );

EXCEPTION
   -- Capture Oracle SQL Abort.(固定)
   WHEN OTHERS THEN
      ROLLBACK;
      av_return               := '-1';
      COMMIT;
      RETURN;
END;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值