oracle 记录集变量RECORD

1、创建记录类型（RECORD）

 TYPE ClientIdentity_Ty is RECORD(

    futuresid    T_CLIENTINFO_HIS.FUTURESID%TYPE,

    companyid    T_CLIENTINFO_HIS.COMPANYID%TYPE,

    compclientid T_CLIENTINFO_HIS.COMPCLIENTID%TYPE,

    clientname   T_CLIENTINFO_HIS.CLIENTNAME%TYPE,

    clienttype   T_CLIENTINFO_HIS.CLIENTTYPE%TYPE,

    idtype       T_CLIENTINFO_HIS.IDTYPE%TYPE,

    id_no        T_CLIENTINFO_HIS.ID_ORIGINAL%TYPE,

    nocid        T_CLIENTINFO_HIS.NOCID%TYPE,

    opendate     T_CLIENTINFO_HIS.OPENDATE%TYPE,

    updatetime   T_CLIENTINFO_HIS.UPDATETIME%TYPE); --客户资料核对

 

  TYPE CHK_ClientIdentity_Ty is RECORD(

    futuresid    T_CLIENTINFO_HIS.FUTURESID%TYPE,

    companyid    VARCHAR2(4),

    compclientid VARCHAR2(18),

    clientname   VARCHAR2(72),

    clienttype   VARCHAR2(1),

    idtype       VARCHAR2(1),

    id_no        VARCHAR2(40),

    nocid        VARCHAR2(40),

    opendate     VARCHAR2(10),

    updatetime   VARCHAR2(20),

matchTag     VARCHAR2(1)); --客户资料核对

 

 

2、声明能记录多行数据的记录集变量（RECORD），因为定义过后的记录集只能存多列单行的数据。所以要变成可以储存多行的。

 

   TYPE clientidentity_list is TABLE of ClientIdentity_Ty; --客户资料核对

   TYPE chk_clientidentity_list is TABLE of CHK_ClientIdentity_Ty; --客户资料核对

 

 

 

3、声明记录类型变量：

 std_clientlist clientidentity_list; --客户资料核对

 

 chk_clientlist chk_clientidentity_list; --客户资料核对

 

 

4、给记录集赋值

 

 SELECT trim(futuresid) as futuresid,

         trim(companyid) as companyid,

         trim(compclientid) as compclientid,

         trim(chr(9) from trim(clientname)) as clientname,

         trim(clienttype) as clienttype,

         trim(idtype) as idtype,

         trim(id_original) as id_original,

         trim(nocid) as nocid,

         trim(opendate) as opendate,

         trim(updatetime) as updatetime bulk collect

    into std_clientlist

    from T_clientinfo_his

   where historyid in

         (select max(historyid)

            from T_clientinfo_his x

           where biztype <> '2'

             and historyid in

                 (select max(historyid)

                    from T_clientinfo_his a, t_check_complist b

                   where updatetime < pi_tradedate || ' 23:59:59'

                     and a.companyid = b.companyid

                   group by a.companyid, a.compclientid, a.exchangeid)

           group by x.companyid, x.compclientid);

 

  sqlstr := 'SELECT NULL as futuresid, ' ||

            '      trim(a.companyid) as companyid, ' ||

            '       trim(a.compclientid) as compclientid, ' ||

            '       trim(chr(9) from trim(a.name)) as clientname, ' ||

            '       decode(clienttype,''0'',''1'',''1'',''2'') as clientType, ' ||

            '       ''1'' AS idtype, ' ||

            '       trim(decode(clienttype,''0'',identity,NULL)) as id_original, ' ||

            '       trim(decode(clienttype,''1'',organcode,NULL)) as nocid, ' ||

            '       trim(a.REALopendate) as opendate, ' ||

            '       NULL as updatetime, ' || '       ''N'' as matchTag ' ||

            'from H_COMPCLIENTINFO  a ' || 'where a.opendate=:1 ' ||

            'and a.companyid in (' || compstr || ')';

  execute immediate sqlstr bulk collect

    into chk_clientlist

    using pi_tradedate;

 

  i := chk_clientlist.FIRST;

 

  while i is not null LOOP

    chk_clientid := chk_clientlist(i);

    chk_clienthashlist(chk_clientid.companyid || '-' || chk_clientid.compclientid) := chk_clientid;

    i := chk_clientlist.next(i);

  END LOOP;

 

  chk_clientlist.delete;

 

 

 

 

 

存储过程：

 

CREATE OR REPLACE PROCEDURE up_checkClientInfo_2(pi_tradedate IN VARCHAR2) IS

 

  /******************************************************************************

     NAME:       up_checkClientInfo

     PURPOSE:

  

     REVISIONS:

     Ver        Date        Author           Description

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

     1.0        2009-8-13          1. Created this procedure.

  

     NOTES:

  

     Automatically available Auto Replace Keywords:

        Object Name:     up_checkClientInfo

        Sysdate:         2009-8-13

        Date and Time:   2009-8-13, 16:04:13, and 2009-8-13 16:04:13

        Username:         (set in TOAD Options, Procedure Editor)

        Table Name:       (set in the "New PL/SQL Object" dialog)

  

  ******************************************************************************/

 

  TYPE ClientIdentity_Ty is RECORD(

    futuresid    T_CLIENTINFO_HIS.FUTURESID%TYPE,

    companyid    T_CLIENTINFO_HIS.COMPANYID%TYPE,

    compclientid T_CLIENTINFO_HIS.COMPCLIENTID%TYPE,

    clientname   T_CLIENTINFO_HIS.CLIENTNAME%TYPE,

    clienttype   T_CLIENTINFO_HIS.CLIENTTYPE%TYPE,

    idtype       T_CLIENTINFO_HIS.IDTYPE%TYPE,

    id_no        T_CLIENTINFO_HIS.ID_ORIGINAL%TYPE,

    nocid        T_CLIENTINFO_HIS.NOCID%TYPE,

    opendate     T_CLIENTINFO_HIS.OPENDATE%TYPE,

    updatetime   T_CLIENTINFO_HIS.UPDATETIME%TYPE); --客户资料核对

 

  TYPE CHK_ClientIdentity_Ty is RECORD(

    futuresid    T_CLIENTINFO_HIS.FUTURESID%TYPE,

    companyid    VARCHAR2(4),

    compclientid VARCHAR2(18),

    clientname   VARCHAR2(72),

    clienttype   VARCHAR2(1),

    idtype       VARCHAR2(1),

    id_no        VARCHAR2(40),

    nocid        VARCHAR2(40),

    opendate     VARCHAR2(10),

    updatetime   VARCHAR2(20),

    matchTag     VARCHAR2(1)); --客户资料核对

 

  i INT;

 

  clientid_slt T_CLIENTID_CHECKRESULT%ROWTYPE; --客户资料核对

 

  TYPE COMPLIST_TY is table of T_CLIENTID_CHECKRESULT.companyid%TYPE index by PLS_INTEGER;--可以存储单列多行的数据

  TYPE COMPCLIENTLIST_TY is table of T_CLIENTID_CHECKRESULT.compclientid%TYPE index by PLS_INTEGER;

  TYPE CHKCLITYPELIST_TY is table of T_CLIENTID_CHECKRESULT.chk_clienttype%TYPE index by PLS_INTEGER;

  TYPE CHKCLINAMELIST_TY is table of T_CLIENTID_CHECKRESULT.chk_clientname%TYPE index by PLS_INTEGER;

  TYPE CHKIDTYPELIST_TY is table of T_CLIENTID_CHECKRESULT.chk_idtype%TYPE index by PLS_INTEGER;

  TYPE CHKIDNOLIST_TY is table of T_CLIENTID_CHECKRESULT.chk_idno%TYPE index by PLS_INTEGER;

  TYPE CHKNOCIDLIST_TY is table of T_CLIENTID_CHECKRESULT.chk_nocid%TYPE index by PLS_INTEGER;

 

  complist       COMPLIST_TY;

  compclientlist COMPCLIENTLIST_TY;

  chkclitypelist CHKCLITYPELIST_TY;

  chkclinamelist CHKCLINAMELIST_TY;

  chkidtypelist  CHKIDTYPELIST_TY;

  chkidnolist    CHKIDNOLIST_TY;

  chknocidlist   CHKNOCIDLIST_TY;

 

  TYPE clientidentity_list is TABLE of ClientIdentity_Ty; --客户资料核对

 

  TYPE chk_clientidentity_list is TABLE of CHK_ClientIdentity_Ty; --客户资料核对

 

  TYPE clientidentity_hashlist is TABLE of CHK_ClientIdentity_Ty index by varchar2(100); --客户资料核对

 

  std_clientid ClientIdentity_Ty; --客户资料核对

  chk_clientid CHK_ClientIdentity_Ty; --客户资料核对

 

  std_clientlist clientidentity_list; --客户资料核对

 

  chk_clientlist chk_clientidentity_list; --客户资料核对

 

  chk_clienthashlist clientidentity_hashlist; --客户资料核对

 

  index_key varchar2(100);

 

  x varchar2(1);

 

  v_checkcomplist COMPLIST_TY;

 

  --查询条件游标

  cursor Cur_CHECKCOMP(pi_date in VARCHAR2) is

    select companyid from t_check_complist where tradedate = pi_date;

 

  checkcomp_rec Cur_CHECKCOMP%ROWTYPE; --第一一个表中的记录集变量

 

  compstr varchar2(2000); --记录查询的公司id

 

  sqlstr varchar2(2000); --动态sql

 

  FUNCTION checkID(id_1 IN VARCHAR2, id_2 IN VARCHAR2) RETURN BOOLEAN IS

    --检查账号

    ID_1_15 VARCHAR2(15);

    ID_2_15 VARCHAR2(15);

  BEGIN

  

    if (length(id_1) = 15) then

      ID_1_15 := id_1;

    elsif (length(id_1) = 18) then

      ID_1_15 := substr(ID_1, 1, 6) || substr(ID_1, 9, 9);

    else

      RETURN FALSE;

    end if;

  

    if (length(id_2) = 15) then

      ID_2_15 := id_2;

    elsif (length(id_2) = 18) then

      ID_2_15 := substr(ID_2, 1, 6) || substr(ID_2, 9, 9);

    else

      RETURN FALSE;

    end if;

  

    if (ID_1_15 = ID_2_15) then

      RETURN TRUE;

    end if;

  

    RETURN FALSE;

  

  END checkID;

 

BEGIN

 

  /*从统一开户系统中还原核对交易日当天的客户身份资料，交易编码以及银行账户信息*/

 

  --根据日志还原核查日客户身份信息

 

  open Cur_CHECKCOMP(pi_tradedate);

  LOOP

    fetch CUR_CHECKCOMP

      into checkcomp_rec;

    exit when CUR_CHECKCOMP%NOTFOUND;

    compstr := compstr || '''' || checkcomp_rec.companyid || ''',';

  END LOOP;

 

  compstr := substr(compstr, 1, length(compstr) - 1);

 

  SELECT trim(futuresid) as futuresid,

         trim(companyid) as companyid,

         trim(compclientid) as compclientid,

         trim(chr(9) from trim(clientname)) as clientname,

         trim(clienttype) as clienttype,

         trim(idtype) as idtype,

         trim(id_original) as id_original,

         trim(nocid) as nocid,

         trim(opendate) as opendate,

         trim(updatetime) as updatetime bulk collect

    into std_clientlist

    from T_clientinfo_his

   where historyid in

         (select max(historyid)

            from T_clientinfo_his x

           where biztype <> '2'

             and historyid in

                 (select max(historyid)

                    from T_clientinfo_his a, t_check_complist b

                   where updatetime < pi_tradedate || ' 23:59:59'

                     and a.companyid = b.companyid

                   group by a.companyid, a.compclientid, a.exchangeid)

           group by x.companyid, x.compclientid);

 

  sqlstr := 'SELECT NULL as futuresid, ' ||

            '      trim(a.companyid) as companyid, ' ||

            '       trim(a.compclientid) as compclientid, ' ||

            '       trim(chr(9) from trim(a.name)) as clientname, ' ||

            '       decode(clienttype,''0'',''1'',''1'',''2'') as clientType, ' ||

            '       ''1'' AS idtype, ' ||

            '       trim(decode(clienttype,''0'',identity,NULL)) as id_original, ' ||

            '       trim(decode(clienttype,''1'',organcode,NULL)) as nocid, ' ||

            '       trim(a.REALopendate) as opendate, ' ||

            '       NULL as updatetime, ' || '       ''N'' as matchTag ' ||

            'from H_COMPCLIENTINFO  a ' || 'where a.opendate=:1 ' ||

            'and a.companyid in (' || compstr || ')';

  execute immediate sqlstr bulk collect

    into chk_clientlist

    using pi_tradedate;  --给变量赋值

 

  i := chk_clientlist.FIRST;--.first方法是数组中第一个元素的下标值（序列值）

 

  while i is not null LOOP

    chk_clientid := chk_clientlist(i);

    

    chk_clienthashlist(chk_clientid.companyid || '-' || chk_clientid.compclientid) := chk_clientid;--内部资金账号

    

    i := chk_clientlist.next(i);

  END LOOP;

 

  chk_clientlist.delete;

  /*进行数据核对，对于基准客户身份数据中（来自统一开户系统）的每一条数据，做循环，

  在上报的数据库中查找相应的记录*/

 

  delete from t_clientid_checkresult

   where tradedate = pi_tradedate

     and companyid in (select companyid from t_check_complist);

 

  i := std_clientlist.FIRST;

 

  WHILE i is not null LOOP

  

    std_clientid := std_clientlist(i);

  

    index_key := std_clientid.companyid || '-' || std_clientid.compclientid;

  

    x := 'N';

  

    /*如果记录没有找到，则查看基准数据的开户日期，如果开户日期是核查当日或者更靠后，则忽略此记录

    否则，认为公司在开户后没有及时上报*/

    if (not chk_clienthashlist.exists(index_key)) then

      if (std_clientid.opendate >= pi_tradedate) then

        NULL;

      else

        clientid_slt.companyid    := std_clientid.companyid;

        clientid_slt.compclientid := std_clientid.compclientid;

        clientid_slt.clienttype   := std_clientid.clienttype;

        clientid_slt.clientname   := std_clientid.clientname;

        clientid_slt.idtype       := std_clientid.idtype;

        clientid_slt.id_original  := std_clientid.id_no;

        clientid_slt.nocid        := std_clientid.nocid;

        clientid_slt.notes        := '客户资料未上报;';

        x                         := 'Y';

      end if;

    else

      /*如果找到，则依次比较客户类型，客户名称，证件类型，身份证号码，组织机构代码证号码

      如果任何一个不匹配，则认为报送有误*/

      chk_clienthashlist(index_key).matchTag := 'Y';

    

      chk_clientid := chk_clienthashlist(index_key);

    

      if (std_clientid.clienttype <> chk_clientid.clienttype) then

        clientid_slt.notes := '客户类型不一致;';

        x                  := 'Y';

      end if;

      if (trim(to_single_byte(std_clientid.clientname)) <>

         trim(to_single_byte(chk_clientid.clientname))) then

        clientid_slt.notes := clientid_slt.notes || '客户名称不一致;';

        x                  := 'Y';

      end if;

      if (std_clientid.idtype <> chk_clientid.idtype) then

        clientid_slt.notes := clientid_slt.notes || '证件类型不一致;';

        x                  := 'Y';

      end if;

      if (std_clientid.clienttype = '1') then

      

        if (not (checkid(std_clientid.id_no, chk_clientid.id_no) and

            chk_clientid.id_no is not null and

            std_clientid.id_no is not null)) then

          x                  := 'Y';

          clientid_slt.notes := clientid_slt.notes || '证件号码不一致;';

        end if;

      else

        if (upper(replace(std_clientid.nocid, '-')) <>

           upper(replace(chk_clientid.nocid, '-')) and

           chk_clientid.nocid is not null and

           std_clientid.nocid is not null) then

          clientid_slt.notes := clientid_slt.notes || '组织机构代码不一致;';

          x                  := 'Y';

        end if;

      end if;

    

      /*如果最后更新日期为核查当日，则忽略不一致*/

      if (substr(std_clientid.updatetime, 1, 10) >= pi_tradedate) then

        x := 'N';

      end if;

    

      if (x = 'Y') then

        clientid_slt.companyid      := std_clientid.companyid;

        clientid_slt.compclientid   := std_clientid.compclientid;

        clientid_slt.clienttype     := std_clientid.clienttype;

        clientid_slt.clientname     := std_clientid.clientname;

        clientid_slt.idtype         := std_clientid.idtype;

        clientid_slt.id_original    := std_clientid.id_no;

        clientid_slt.nocid          := std_clientid.nocid;

        clientid_slt.chk_clienttype := chk_clientid.clienttype;

        clientid_slt.chk_clientname := chk_clientid.clientname;

        clientid_slt.chk_idtype     := chk_clientid.idtype;

        clientid_slt.chk_idno       := chk_clientid.id_no;

        clientid_slt.chk_nocid      := chk_clientid.nocid;

      end if;

    

    end if;

  

    if (x = 'Y') then

      select seq_chkrecid.nextval into clientid_slt.recordid from dual;

      clientid_slt.updateTime := std_clientid.updateTime;

      clientid_slt.tradedate  := pi_tradedate;

    

      insert into t_clientid_checkresult values clientid_slt;

    end if;

  

    clientid_slt.companyid      := NULL;

    clientid_slt.compclientid   := NULL;

    clientid_slt.clienttype     := NULL;

    clientid_slt.clientname     := NULL;

    clientid_slt.idtype         := NULL;

    clientid_slt.id_original    := NULL;

    clientid_slt.nocid          := NULL;

    clientid_slt.chk_clienttype := NULL;

    clientid_slt.chk_clientname := NULL;

    clientid_slt.chk_idtype     := NULL;

    clientid_slt.chk_idno       := NULL;

    clientid_slt.chk_nocid      := NULL;

  

    clientid_slt.tradedate  := NULL;

    clientid_slt.recordid   := NULL;

    clientid_slt.updateTime := NULL;

    clientid_slt.notes      := NULL;

  

    i := std_clientlist.next(i);

  

  END LOOP;

  COMMIT;

 

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    NULL;

  WHEN OTHERS THEN

    -- Consider logging the error and then re-raise

    RAISE;

END up_checkClientInfo_2;