常见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;

-------------------------------------------------------------------------------------------------------------------------------------------------

巴特西

常见sql操作

最新文章

热门文章