- /**
- * 从柜台同步客户数据至临时表
- */
- create or replace PROCEDURE SP_SYNC_CUSTOMER_TEMP (UPDATE_TOTAL OUT NUMBER) IS
- INDEX_COUNT NUMBER;
- INDEX_TOTAL NUMBER;
- CURSOR CR IS
select a.client_id, -- 客户编号
a.branch_no, -- 分支机构
a.id_no, -- 证件号码
a.client_name, -- 客户姓名
a.client_status, -- 客户状态
a.open_date, -- 开户日期
c.fund_account, -- 资金账号
c.main_flag, -- 主账标识
c.asset_prop, -- 资产属性
b.birthday, -- 生日日期
b.address, -- 地址
b.home_tel, -- 家庭电话
b.e_mail, -- 邮箱
b.fax, -- 传真
b.mobile_tel, -- 手机号码
b.office_tel, -- 单位电话
b.zipcode, -- 邮政编码
b.account_data -- 开户规范信息
- from hs_asset.client@HSAJ216 a
- inner join (select client_id,
- birthday,
- address,
- home_tel,
- e_mail,
- fax,
- mobile_tel,
- office_tel,
- zipcode,
- account_data
- from hs_asset.clientinfo@HSAJ216
- union all
- select client_id,
- '19000101' as birthday,
- address,
- contact_tel as home_tel,
- e_mail,
- fax,
- mobile_tel,
- contact_tel as office_tel,
- zipcode,
- 'A' as account_data
- from hs_asset.organinfo@HSAJ216) b
- on a.client_id = b.client_id
- inner join hs_asset.fundaccount@HSAJ216 c
- on a.client_id = c.client_id
- where c.asset_prop = '0';
- BEGIN
- insert into t_coll_result (id, CREATE_DATE, REMARK)
- values (seq_t_coll_result_id.nextval, sysdate, 'START - 现在开始执行 SP_SYNC_CUSTOMER_TEMP 清空客户同步临时表 TEMP_SYNC_CUSTOMER...');
- EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SYNC_CUSTOMER';
- insert into t_coll_result (id, CREATE_DATE, REMARK)
- values (seq_t_coll_result_id.nextval, sysdate, 'OVER-SP_SYNC_CUSTOMER_TEMP 清空客户同步临时表 TEMP_SYNC_CUSTOMER 已完成...');
来源: http://www.linuxidc.com/Linux/2018-03/151426.htm