常见sql操作
1、
select '`'||b.mrchno 商户号,
b.name 商户名称,
b.contact3 注册地址联系人,
'`'||b.telno1 邮寄地址联系电话,
a.MRCHT_NAME X商户名称,
a.ADDRESS 商户联系地址,
a.ADD_DATE 增加时间,
a.AGENT 经办人姓名,
decode（a.ID_TYPE,
'1','居民身份证',
'2', '户口本',
'3','军人身份证',
'4','武装警察身份证',
'5','往来内地通行证',
'6','往来大陆通行证',
'7','护照',
'8','其他','' ） 经办人证件类型,
'`'||a.ID_NO 经办人证件号码,
to_char(a.ID_VALIDITY, 'yyyymmdd') 经办人证件有效期,
a.LEGAL_REP 法定代表姓名,
decode（a.LR_ID_TYPE,
'1','居民身份证',
'2','户口本',
'3','军人身份证',
'4','武装警察身份证',
'5','往来内地通行证',
'6','往来大陆通行证',
'7','护照',
'8','其他','' ） 法定代表证件类型,
'`'||a.LR_ID_NO 法定代表证件号码,
to_char(a.LR_ID_VALIDITY, 'yyyymmdd') 法定代表证件有效期,
a.MAN_NAME 实际控制人姓名,
decode（a.ID_TYPE1,
'1','居民身份证',
'2','户口本',
'3','军人身份证',
'4','武装警察身份证',
'5','往来内地通行证',
'6','往来大陆通行证',
'7','护照',
'8','其他','' ） 实际控制人证件类型,
'`'||a.ID_NO1 实际控制人证件号码,
to_char(a.ID_DEADLINE1, 'yyyymmdd') 实际控制人证件有效期,
'`'||a.BUS_LIC_NO 营业执照号,
a.ACC_X_NAME 营业执照名称,
to_char(a.BUS_LIC_VALIDITY, 'yyyymmdd') 营业执照年检时间,
'`'||a.TAX_ID 税务登记证编号,
to_char(a.TAX_ID_VALIDITY, 'yyyymmdd') 税务登记证年检时间,
'`'||a.ORG_ID 组织机构证编号,
to_char(a.ORG_VALIDITY, 'yyyymmdd') 组织机构证年检时间,
'`'||a.ACCNO 企业账号,
t.ACC_NAME 结算账户开户名,
t.short_nick_name 商户账号简称,
t.acc_nick_name 商户账号全称,
'`'||t.bank_no 联行号,
'`'||t.ACCNO 结算账号,
t.bank_name 开户银行名称,
--to_char(t.acc_add_date, 'yyyymmdd') 账号添加日期
to_char(t.acc_add_date, 'yyyy-MM-dd HH:mm:ss') 账号添加日期
from merchant_x a, mrch_acc_x t, merchant b
where a.mrchno = b.mrchno
and t.MERCHANT_ID = b.id
and t.mrchno = b.mrchno
and substr(a.add_date, 0, 4) = '2017';
2、
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SYNCHRONOUS_TB_CON_HIS3(BEGINDATE IN VARCHAR2,ENDDATE IN VARCHAR2) IS
/******************************************
-- Author : GL
-- Created : 2018/01/23
-- Purpose : 按日期导入商户信息tb_con_his反洗钱
*********************************************/
--定义变量
MERCHANT_NOO VARCHAR2(256); --商户号
ACCNOO VARCHAR2(256); --结算账号
ACC_NICK_NAMEOO VARCHAR2(256); --银行账户名称
NUMM NUMBER; -- 购卡消费间隔天数
--定义游标
CURSOR cur_ids IS
select distinct a.mrchno mrchno, t.accno accno, t.acc_nick_name acc_nick_name
from merchant_x@DBLINK_TO_CORTEX a,
mrch_acc_x@DBLINK_TO_CORTEX t,
merchant@DBLINK_TO_CORTEX b,
termpos_x@DBLINK_TO_CORTEX c
where t.mrchno = a.mrchno
and t.MERCHANT_ID = b.id
and c.settle_mrch_acc_id = t.id
and a.add_date between BEGINDATE and ENDDATE;
--定义变量 VARCHAR2(200);
MRCHNO VARCHAR2(200);
MRCHT_NAME VARCHAR2(200);
ADD_DATE VARCHAR2(200);
ADDRESS VARCHAR2(200);
ORG_ID VARCHAR2(200);
BUS_LIC_NO VARCHAR2(200);
BUS_LIC_VALIDITY VARCHAR2(200);
LEGAL_REP VARCHAR2(200);
LR_ID_NO VARCHAR2(200);
LR_ID_VALIDITY VARCHAR2(200);
AGENTR VARCHAR2(200);
ID_NO VARCHAR2(200);
ID_VALIDITY VARCHAR2(200);
ACCNO VARCHAR2(200);
ACC_NICK_NAME VARCHAR2(200);
MAN_NAMEO VARCHAR2(200);
--ID_TYPE1O VARCHAR2(200);
ID_TYPE1OO VARCHAR2(200);
ID_NO1O VARCHAR2(200);
ID_DEADLINE1O VARCHAR2(200);
ACC_NAMEO VARCHAR2(200);
ID_TYPE2O VARCHAR2(200);
ID_TYPE3O VARCHAR2(200);
BEGIN
--循环开始
LOOP
IF NOT cur_ids%ISOPEN THEN
OPEN cur_ids;
END IF;
FETCH cur_ids INTO MERCHANT_NOO, ACCNOO, ACC_NICK_NAMEOO;
--退出循环的条件
EXIT WHEN cur_ids%NOTFOUND OR cur_ids%NOTFOUND IS NULL;
select count(*) into NUMM
from tb_con_his T1
where T1.JOIN_CODE = MERCHANT_NOO and T1.SELF_ACC_NO = ACCNOO and T1.PROF_TYPE='11' and T1.BANK_ACC_NAME=substr(ACC_NICK_NAMEOO, 0, 26)
and T1.OPEN_TIME between substr(BEGINDATE,0,8) and substr(ENDDATE,0,8);
insert into tb_con_his_now_error (JOIN_CODE,SELF_ACC_NO,BANK_ACC_NAME,ID) values(MERCHANT_NOO,ACCNOO,substr(ACC_NICK_NAMEOO, 0, 26),tb_con_his_now_error_seq_id.nextval);
SELECT E.MRCHNO,
E.MRCHT_NAME,
E.ADD_DATE,
E.ADDRESS,
E.ORG_ID,
E.BUS_LIC_NO,
E.BUS_LIC_VALIDITY,
E.LEGAL_REP,
E.LR_ID_NO,
E.LR_ID_VALIDITY,
E.IAGENT,
E.ID_NO,
E.ID_VALIDITY,
E.ACCNO,
E.ACC_NICK_NAME,
E.MAN_NAME,
E.ID_TYPE1,
E.ID_NO1,
E.ID_DEADLINE1,
E.ACC_X_NAME,
E.ID_TYPE2,
E.ID_TYPE3
INTO MRCHNO,
MRCHT_NAME,
ADD_DATE,
ADDRESS,
ORG_ID,
BUS_LIC_NO,
BUS_LIC_VALIDITY,
LEGAL_REP,
LR_ID_NO,
LR_ID_VALIDITY,
AGENTR,
ID_NO,
ID_VALIDITY,
ACCNO,
ACC_NICK_NAME,
MAN_NAMEO,
ID_TYPE1OO,
ID_NO1O,
ID_DEADLINE1O,
ACC_NAMEO,
ID_TYPE2O,
ID_TYPE3O
FROM
(select distinct a.mrchno mrchno,
substr(a.mrcht_name, 0, 26) mrcht_name,
substr(a.add_date, 0, 8) add_date,
a.address address,
substr(a.org_id, 0, 10) org_id,
a.bus_lic_no bus_lic_no,
to_char(a.bus_lic_validity, 'yyyymmdd') bus_lic_validity,
a.legal_rep legal_rep,
a.lr_id_no lr_id_no,
to_char(a.lr_id_validity, 'yyyymmdd') lr_id_validity,
a.agent iagent,
a.id_no id_no,
to_char(a.id_validity, 'yyyymmdd') id_validity,
t.accno accno,
substr(t.acc_nick_name, 0, 26) acc_nick_name,
substr(a.man_name,0,10) man_name,
--a.id_type1 id_type1,
decode（a.id_type1,'1','11','2','19','3','12','4','12','5','13','6','13','7','14','8','19',''） id_type1,
a.id_no1 id_no1,
to_char(a.id_deadline1,'yyyymmdd') id_deadline1,
a.acc_x_name acc_x_name,
decode（a.lr_id_type,'1','11','2','19','3','12','4','12','5','13','6','13','7','14','8','19',''） id_type2,
decode（a.id_type,'1','11','2','19','3','12','4','12','5','13','6','13','7','14','8','19',''） id_type3
from merchant_x@DBLINK_TO_CORTEX a,
mrch_acc_x@DBLINK_TO_CORTEX t,
merchant@DBLINK_TO_CORTEX b,
termpos_x@DBLINK_TO_CORTEX c
where t.mrchno = a.mrchno
and t.MERCHANT_ID = b.id
and c.settle_mrch_acc_id = t.id
and a.add_date between BEGINDATE and ENDDATE
and a.mrchno = MERCHANT_NOO
and t.acc_nick_name = ACC_NICK_NAMEOO
and t.accno = ACCNOO) e;
if NUMM=0 then
insert into tb_con_his
(JOIN_CODE,
ACC_NAME1,
OPEN_TIME,
ADDRESS,
ORG_NO,
LICENSE,
LICENSE_DEADLINE,
REP_LEGAL,
ID_NO2,
ID_DEADLINE2,
HANDLER_NAME,
ID_NO3,
ID_DEADLINE3,
SELF_ACC_NO,
BANK_ACC_NAME,
ACC_NAME,
MAN_NAME,
ID_TYPE1,
ID_NO1,
ID_DEADLINE1,
ID_TYPE2,
ID_TYPE3,
PROF_TYPE,
ACC_TYPE,
BORD_FLAG,
NATION,
CLOSE_TIME,
OPERATE,
SET_FILE,
CODE,
ACC_TYPE1)
values
(MRCHNO,
MRCHT_NAME,
ADD_DATE,
ADDRESS,
ORG_ID,
BUS_LIC_NO,
BUS_LIC_VALIDITY,
LEGAL_REP,
LR_ID_NO,
LR_ID_VALIDITY,
AGENTR,
ID_NO,
ID_VALIDITY,
ACCNO,
ACC_NICK_NAME,
ACC_NAMEO,
MAN_NAMEO,
ID_TYPE1OO,
--'@N',
ID_NO1O,
ID_DEADLINE1O,
ID_TYPE2O,
ID_TYPE3O,
'11',
'12',
'11',
'CHN',
'@N',
'@N',
'11',
--'11',
--'11',
'@N',
'12');
else
update tb_con_his
set JOIN_CODE = MRCHNO,
ACC_NAME1 = MRCHT_NAME,
OPEN_TIME = ADD_DATE,
ADDRESS = ADDRESS,
ORG_NO = ORG_ID,
LICENSE = BUS_LIC_NO,
LICENSE_DEADLINE = BUS_LIC_VALIDITY,
REP_LEGAL = LEGAL_REP,
ID_NO2 = LR_ID_NO,
ID_DEADLINE2 = LR_ID_VALIDITY,
HANDLER_NAME = AGENTR,
ID_NO3 = ID_NO,
ID_DEADLINE3 = ID_VALIDITY,
SELF_ACC_NO = ACCNO,
BANK_ACC_NAME = ACC_NICK_NAME,
MAN_NAME = MAN_NAMEO,
ID_TYPE1 = ID_TYPE1OO,
ID_NO1 = ID_NO1O,
ID_DEADLINE1 = ID_DEADLINE1O,
ACC_NAME = ACC_NAMEO,
ID_TYPE2 = ID_TYPE2O,
ID_TYPE3 = ID_TYPE3O
where JOIN_CODE = MERCHANT_NOO
and SELF_ACC_NO = ACCNOO
and BANK_ACC_NAME = ACC_NICK_NAMEOO
and OPEN_TIME between substr(BEGINDATE,0,8) and substr(ENDDATE,0,8)
and PROF_TYPE='11';
end if;
END LOOP;
COMMIT;
END SYNCHRONOUS_TB_CON_HIS3;
-------------------------------------------------------------------------------------------------------------------------------------------------
