经销商导入存储过程(oracle)
来源:互联网 发布:windows资源管理器占用 编辑:程序博客网 时间:2024/04/30 02:32
create or replace procedure proc_validate_import_dealer(v_batchId in varchar2,v_userId in varchar2,resultCode out varchar2,rsMsg out varchar2)
/**
* create by songjz
* 经销商导入
*/
as
repeatCount number; --重复记录数
tmpCount number;
tmpUserCount number;
begin
rsMsg:='验证成功';
resultCode:='1';
/*验证临时表联系人是否存在)*/
--临时表记录总数
select count(*)
into tmpCount
from t_dealer_tmp;
select count(*)
into tmpUserCount
from t_dealer_tmp a
inner join cas.sys_user b on a.contacts_name = b.user_abbr_name;
if tmpCount <> tmpUserCount then
rsMsg:='excel里存在无效的经销商联系人!';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
return;
end if;
/*验证临时表重复性(经销商编码+联系人确定唯一)*/
select count(*)
into repeatCount
from (select t.dealer_code
from t_dealer_tmp t
group by t.dealer_code, t.contacts_name
having count(*) > 1);
if repeatCount >0 then
rsMsg:='excel里存在重复经销编码和联系人,请确认无重复后再导入!';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
return;
end if;
/*验证正式表与临时表重复性(经销商编码+联系人确定唯一)*/
select count(*)
into repeatCount
from t_dealer t
inner join t_dealer_tmp tmp on t.dealer_code = tmp.dealer_code
and t.contacts_id = (select u.user_id from cas.sys_user u where u.user_abbr_name = tmp.contacts_name);
if repeatCount >0 then
rsMsg:='不能导入重复的经销商信息!';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
return;
end if;
--dbms_output.put_line(repeatCount);
/*通过验证复制数据到到正式表*/
--插入基础信息表
insert into t_dealer d
(DEALER_ID,
COOP_STATE_VAL,
COOP_MEMO,
OLD_DEALER_CODE,
DEALER_CODE,
CHANNEL_ID,
PROVINCE_ID,
CITY_ID,
MANF_ID,
BRAND_ID,
SHOP_NAME,
ADDRESS,
DEALER_NATURE_VAL,
MESH_DETAIL,
CONTACTS_ID,
SEX_VAL,
POSITION,
DEL_PERIOD,
DEL_DETAIL_REASON,
CREATOR_ID,
CREATION_TIME,
MODIFIER_ID,
MODIFICATION_TIME)
select tmp.dealer_id,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.coop_state_name and ct.p_code = 'PARAM_COOPERATION'),
tmp.coop_memo,
tmp.dealer_code,
tmp.dealer_code,
(select c.channel_id from t_channel c where c.channel_name = tmp.channel_name),
(select pr.province_id from t_province pr where pr.province_name = tmp.province_name),
(select ci.city_id from t_city ci where ci.city_name = tmp.city_name),
(select f.manf_id from t_manf f where f.manf_name = tmp.manf_name),
(select br.brand_id from t_brand br where br.brand_name = tmp.brand_name),
tmp.shop_name,
tmp.address,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.dealer_nature and ct.p_code = 'PARAM_DEALER_NATURE'),
tmp.mesh_detail,
(select u.user_id from cas.sys_user u where u.user_abbr_name = tmp.contacts_name),
(select ct.code_val from t_code_table ct where ct.code_name = tmp.sex and ct.p_code = 'PARAM_GENDER'),
tmp.position,
tmp.del_period,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.del_detail_reason and ct.p_code = 'PARAM_DEALER_DEL_DETAIL_REASON'),
v_userId,
sysdate,
v_userId,
sysdate
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础背景表
insert into t_dealer_background db
(DEALER_ID,
DEALER_BACKGROUND_ID,
DEVELOPER_ID,
DEVELOPER_TIME,
DEVELOP_WAY_VAL,
INDIVIDUAL,
CONTACT_TIME_WAY,
OPERATION_STATE_VAL,
GROUP_ID)
select tmp.dealer_id,
tmp.dealer_id,
(select u.user_id from cas.sys_user u where u.user_name = tmp.developer_name),
to_date(tmp.developer_time,'yyyy/MM/dd'),
tmp.develop_way,
--(select ct.code_val from t_code_table ct where ct.code_name = tmp.develop_way and ct.p_code = 'PARAM_DEVELOP_WAY'),
tmp.individual,
tmp.contact_time_way,
(select n.operation_state_val from t_network n where n.dealer_code = tmp.dealer_code),
(select n.group_id from t_network n where n.dealer_code = tmp.dealer_code)
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础费用表
insert into t_dealer_cost dc
(DEALER_ID,
DEALER_COST_ID,
WEEK_FLAG,
HALF_PRICE_FLAG,
TERMINAL_FLAG,
SALE_SCALE_FLAG,
BUSINESS_POLICY_FLAG)
select tmp.dealer_id,
tmp.dealer_id,
decode(tmp.week_flag,'是',1,'否',0,0),
decode(tmp.half_price_flag,'是',1,'否',0,0),
decode(tmp.terminal_flag,'是',1,'否',0,0),
decode(tmp.sale_scale_flag,'是',1,'否',0,0),
decode(tmp.business_policy_flag,'是',1,'否',0,0)
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础联系表
insert into t_dealer_contact dco
(DEALER_ID,
DEALER_CONTACT_ID,
TEL1,
EMAIL1,
QQ_FAX1)
select tmp.dealer_id,
tmp.dealer_id,
tmp.tel,
tmp.email,
tmp.qq_fax
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础帐号表
insert into t_dealer_account da
(DEALER_ID,
DEALER_ACCOUNT_ID,
BANK_VAL,
BANK_ACCOUNT,
ACCOUNT_NAME,
BANK_NAME,
ID_CARD)
select tmp.dealer_id,
tmp.dealer_id,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.bank and ct.p_code = 'PARAM_BANK'),
tmp.bank_account,
tmp.account_name,
tmp.bank_name,
tmp.id_card
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
commit;
exception
when others then
rollback;
rsMsg:='错误';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
commit;
dbms_output.put_line(SQLCODE || ',' || SQLERRM );
end;
/**
* create by songjz
* 经销商导入
*/
as
repeatCount number; --重复记录数
tmpCount number;
tmpUserCount number;
begin
rsMsg:='验证成功';
resultCode:='1';
/*验证临时表联系人是否存在)*/
--临时表记录总数
select count(*)
into tmpCount
from t_dealer_tmp;
select count(*)
into tmpUserCount
from t_dealer_tmp a
inner join cas.sys_user b on a.contacts_name = b.user_abbr_name;
if tmpCount <> tmpUserCount then
rsMsg:='excel里存在无效的经销商联系人!';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
return;
end if;
/*验证临时表重复性(经销商编码+联系人确定唯一)*/
select count(*)
into repeatCount
from (select t.dealer_code
from t_dealer_tmp t
group by t.dealer_code, t.contacts_name
having count(*) > 1);
if repeatCount >0 then
rsMsg:='excel里存在重复经销编码和联系人,请确认无重复后再导入!';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
return;
end if;
/*验证正式表与临时表重复性(经销商编码+联系人确定唯一)*/
select count(*)
into repeatCount
from t_dealer t
inner join t_dealer_tmp tmp on t.dealer_code = tmp.dealer_code
and t.contacts_id = (select u.user_id from cas.sys_user u where u.user_abbr_name = tmp.contacts_name);
if repeatCount >0 then
rsMsg:='不能导入重复的经销商信息!';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
return;
end if;
--dbms_output.put_line(repeatCount);
/*通过验证复制数据到到正式表*/
--插入基础信息表
insert into t_dealer d
(DEALER_ID,
COOP_STATE_VAL,
COOP_MEMO,
OLD_DEALER_CODE,
DEALER_CODE,
CHANNEL_ID,
PROVINCE_ID,
CITY_ID,
MANF_ID,
BRAND_ID,
SHOP_NAME,
ADDRESS,
DEALER_NATURE_VAL,
MESH_DETAIL,
CONTACTS_ID,
SEX_VAL,
POSITION,
DEL_PERIOD,
DEL_DETAIL_REASON,
CREATOR_ID,
CREATION_TIME,
MODIFIER_ID,
MODIFICATION_TIME)
select tmp.dealer_id,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.coop_state_name and ct.p_code = 'PARAM_COOPERATION'),
tmp.coop_memo,
tmp.dealer_code,
tmp.dealer_code,
(select c.channel_id from t_channel c where c.channel_name = tmp.channel_name),
(select pr.province_id from t_province pr where pr.province_name = tmp.province_name),
(select ci.city_id from t_city ci where ci.city_name = tmp.city_name),
(select f.manf_id from t_manf f where f.manf_name = tmp.manf_name),
(select br.brand_id from t_brand br where br.brand_name = tmp.brand_name),
tmp.shop_name,
tmp.address,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.dealer_nature and ct.p_code = 'PARAM_DEALER_NATURE'),
tmp.mesh_detail,
(select u.user_id from cas.sys_user u where u.user_abbr_name = tmp.contacts_name),
(select ct.code_val from t_code_table ct where ct.code_name = tmp.sex and ct.p_code = 'PARAM_GENDER'),
tmp.position,
tmp.del_period,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.del_detail_reason and ct.p_code = 'PARAM_DEALER_DEL_DETAIL_REASON'),
v_userId,
sysdate,
v_userId,
sysdate
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础背景表
insert into t_dealer_background db
(DEALER_ID,
DEALER_BACKGROUND_ID,
DEVELOPER_ID,
DEVELOPER_TIME,
DEVELOP_WAY_VAL,
INDIVIDUAL,
CONTACT_TIME_WAY,
OPERATION_STATE_VAL,
GROUP_ID)
select tmp.dealer_id,
tmp.dealer_id,
(select u.user_id from cas.sys_user u where u.user_name = tmp.developer_name),
to_date(tmp.developer_time,'yyyy/MM/dd'),
tmp.develop_way,
--(select ct.code_val from t_code_table ct where ct.code_name = tmp.develop_way and ct.p_code = 'PARAM_DEVELOP_WAY'),
tmp.individual,
tmp.contact_time_way,
(select n.operation_state_val from t_network n where n.dealer_code = tmp.dealer_code),
(select n.group_id from t_network n where n.dealer_code = tmp.dealer_code)
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础费用表
insert into t_dealer_cost dc
(DEALER_ID,
DEALER_COST_ID,
WEEK_FLAG,
HALF_PRICE_FLAG,
TERMINAL_FLAG,
SALE_SCALE_FLAG,
BUSINESS_POLICY_FLAG)
select tmp.dealer_id,
tmp.dealer_id,
decode(tmp.week_flag,'是',1,'否',0,0),
decode(tmp.half_price_flag,'是',1,'否',0,0),
decode(tmp.terminal_flag,'是',1,'否',0,0),
decode(tmp.sale_scale_flag,'是',1,'否',0,0),
decode(tmp.business_policy_flag,'是',1,'否',0,0)
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础联系表
insert into t_dealer_contact dco
(DEALER_ID,
DEALER_CONTACT_ID,
TEL1,
EMAIL1,
QQ_FAX1)
select tmp.dealer_id,
tmp.dealer_id,
tmp.tel,
tmp.email,
tmp.qq_fax
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--插入基础帐号表
insert into t_dealer_account da
(DEALER_ID,
DEALER_ACCOUNT_ID,
BANK_VAL,
BANK_ACCOUNT,
ACCOUNT_NAME,
BANK_NAME,
ID_CARD)
select tmp.dealer_id,
tmp.dealer_id,
(select ct.code_val from t_code_table ct where ct.code_name = tmp.bank and ct.p_code = 'PARAM_BANK'),
tmp.bank_account,
tmp.account_name,
tmp.bank_name,
tmp.id_card
from t_dealer_tmp tmp
where tmp.batch_id = v_batchId;
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
commit;
exception
when others then
rollback;
rsMsg:='错误';
resultCode:='0';
--清空临时表
delete t_dealer_tmp tt where tt.batch_id=v_batchId;
commit;
dbms_output.put_line(SQLCODE || ',' || SQLERRM );
end;
0 0
- 经销商导入存储过程(oracle)
- 使用Oracle存储过程导入导出数据文件
- Oracle批量导入数据库存储过程
- SQL2005导入存储过程
- e) 导入存储过程
- 会员导入存储过程
- sql2005导入导出存储过程
- plsql导出导入存储过程
- 获取导入模板存储过程
- 存储过程和数据导入
- pl/sql导入存储过程
- oracle 创建表并创建存储过程,通过存储过程将其他用户的信息导入到新建表中
- Oracle:存储过程,存储函数
- Oracle-存储过程 存储函数
- Oracle存储过程、存储函数
- oracle 存储函数,存储过程
- Oracle存储过程
- 调试oracle存储过程。
- uva 10100 Longest Match(最长公共子序列)
- 关于Vector作为函数的返回值
- 【编程拾遗】预编译头文件来自编译器的早期版本
- Android的volley框架心得
- 第一章:初识lucene
- 经销商导入存储过程(oracle)
- iOS更改NavigationBar Title颜色字体
- 对图中一些概念的区分
- ADB常用命令笔记
- 实现实现易信的圆形图像和对话列表的图像显示部分
- Android Toolbar跟随ListView滑动隐藏和现实
- jQuery在线手册
- 数据结构专题——线性表
- clientHeight、offsetHeight、scrollHeight