smallboby
- 浏览: 191448 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
wg1214：
CommonRcFileCompression 这个类里的DA ...
普通文本压缩成RcFile的通用类 -
wdt1988520：
要导入哪些jar包呀？ 有完整的源码吗？
普通文本压缩成RcFile的通用类 -
yiqi1943：
timeStampDate函数没找到
RcFile存储和读取操作 -
yiqi1943：
您用的hadoop和hive的版本是什么呢？
RcFile存储和读取操作 -
心如止水13：
有没有源码呢！？学习一下哎！
JBPM4.3总结三
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;
分享到：
发表评论
相关推荐
在IT行业中，数据库管理是至关重要的任务，而...综上所述，使用存储过程备份数据库在C# ASP.NET环境下可以实现高效、可控的数据库备份流程。同时，结合良好的编程习惯和管理策略，能够确保数据的安全性和业务的连续性。
当我们谈论“存储过程备份恢复”时，这涉及到对这些数据库对象的保护和恢复策略，确保在系统故障或数据丢失后能够迅速恢复正常运行。 首先，了解SQL备份是至关重要的。SQL备份通常包括完整备份、差异备份、日志备份...
1、修改表名 2、创建新表 3、按时间条件插入数据 由于数据太大、用的改表名的方式备份分表，通过传入表名和条件字段名备份相应的表
在本场景中，我们关注的是如何利用存储过程进行数据库的备份与还原，这在数据管理中至关重要，因为数据的安全性和可恢复性是任何业务的基础。 首先，理解存储过程的优势：它们提高了性能，因为一旦编译，就无需每次...
sql 存储过程 自动备份指定时间内A数据库表数据到B数据库表
ORACLE存储过程备份数据库表
根据提供的信息，我们可以详细解析如何使用SQL Server中的存储过程来实现数据库备份功能。该存储过程主要涉及以下几个关键点：输入参数、输出参数、路径检查、以及执行备份命令。 ### 标题解读：“存储过程实现备份...
这个存储过程用于创建一个数据库的备份，它接受三个参数：`@flag`用于记录过程运行状态，`@backup_db_name`指定了需要备份的数据库名称，`@filename`则是备份文件的保存路径和文件名。在存储过程中，首先检查该存储...
3、备份策略是存储过程，方便执行，每次备份表都有时间戳。 4、若备份文件过大，可以考虑增加备份频率或分批备份。 5、如遇到表正在被使用或锁定的情况，可能导致备份失败。建议在备份前确保表处于可备份状态。 6、...
另外，除了SQL Server自带的存储过程，还可以通过编写自定义的存储过程来自动化备份和恢复流程，例如定时执行备份任务，或者在特定事件触发时自动执行恢复操作。这需要对T-SQL编程有深入理解，以确保脚本的正确性和...
【Java存储过程实现Oracle远程逻辑备份与恢复】 Oracle数据库是一个基于网络计算的系统，它内建了对Java的支持，包括Oracle JVM（Java Virtual Machine），这使得开发者能够在数据库服务器端执行复杂的计算任务，...
根据提供的信息，我们可以总结出以下关于“SQL Server定时备份存储过程”的相关知识点： ### SQL Server 定时备份存储过程 #### 1. 存储过程介绍 存储过程`Pro_AddTask`是一个用户自定义的过程，它被设计用于在SQL...
### 自动备份SQL Server数据库的作业的脚本及存储过程详解 #### 一、背景介绍 在企业级应用中，数据库备份是一项非常重要的任务。它不仅能够帮助组织防止数据丢失，还能确保数据的一致性和可用性。对于SQL Server...
根据提供的文件信息，本文将详细解释“数据库备份和还原（完全备份和差额备份）- 存储过程”这一主题中的关键知识点。 ### 一、理解数据库备份的重要性 在介绍具体的存储过程之前，我们首先来了解一下数据库备份的...
1.把原存储过程备份到本地DATA/SourceFile文件夹下 2.把存储过程导出加密文件DATA/EncryptionFile文件夹下 3.把存储过程删除 4.把刚导出DATA/EncryptionFile的文件导入数据库完成加密功能 5.加密完成后CMD窗体自动...
存储备份的基本体系结构主要包括以下几个关键组件和过程： 1. **数据源**：这是存储备份的起点，包括服务器、数据库、文件系统、应用程序等，它们是产生需要备份的数据的地方。 2. **备份软件**：备份软件负责调度...
存储备份与灾难备份是IT领域中至关重要的概念，它们主要关注如何保护数据免受意外损失，确保在系统出现故障时能迅速恢复运作。本文将详细介绍完全备份、增量备份和差分备份三种常见备份策略，以及灾难恢复的重要性。...
根据提供的文件信息，本文将详细解析“备份数据库存储过程txt版”的内容，主要涉及SQL Server中的备份与恢复机制。此存储过程使用了SQL Server的T-SQL语言编写，旨在为数据库提供备份功能，并且还提供了恢复数据库的...
本主题将深入探讨利用JDBC进行触发器创建、存储过程执行、表的构建，以及数据库的备份和恢复操作。 首先，**JDBC创建触发器** 是一种数据库级别的事件响应机制，允许在特定数据库事件（如插入、更新或删除记录）...