oracle %rowtype用法

 cursor是c_product，

r_product c_product%rowtype;

r_product 是cursor查询出的所有字段，然后用r_product.claimno可以使用具体的某个字段的值，当字段特别多的时候用%rowtype相当节省功夫。

 

 

create or replace
procedure sp_convert_claim_rider(buname in char)
as
    v_row_count     integer default 0;
    v_sqlerrm       varchar2(512);   
    v_covercd       varchar2(20);
    v_claimno       varchar2(20);
    v_lifeclaim     varchar2(20);
    v_jlind         char(1);
    v_enpw          char(1);
    v_sertax        number(9,2);
    v_lupvalue      number(9,2);
    v_poltype       varchar2(20);
    v_ontrandt      date;
    v_dtaccp        date;
    v_debug         char(3);
    v_temp_char    varchar2(50);
    v_polno msclmpolcc.polno%type;
   
    cursor c_product
    is
        select cr.claimno,
            cr.polno,
            cr.covcode,
            cr.sa,
            cr.accstatus_a,
            cr.basicind,
            cr.claimtype,
            cr.statuscc,
            cr.reason,
            cr.paystat,
            cr.admitby,
            cr.dtadmit,
            nvl(cr.actamtpaid, 0.0) as actamtpaid,
            r.covercd,
            r.xmorind,
            r.xmorper,
            r.calprm,
            r.benterm,
            r.prmterm,
            r.lpdd,
            r.effect,
            r.dtorginc as trndt ,    --- (XXX) TBD
            'T' as prod_type
        from msclmpolcc cr, mpolicya r
        where cr.polno = r.polno and cr.covcode = r.covercd and r.xmorind is null
       
        union all
  
        select cr.claimno,
            cr.polno,
            cr.covcode,
            cr.sa,
            cr.accstatus_a,
            cr.basicind,
            cr.claimtype,
            cr.statuscc,
            cr.reason,
            cr.paystat,
            cr.admitby,
            cr.dtadmit,
            nvl(cr.actamtpaid, 0.0) as actamtpaid,           
            r.covercd,
            r.xmorind,
            r.xmorper,
            r.calprm,
            r.benterm,
            r.prmterm,
            r.lpdd,
            r.effect,
            r.trndt,
            'L' as prod_type
        from msclmpolcc cr, mulpola r
        where cr.polno = r.polno and cr.covcode = r.covercd and r.xmorind is null
            and r.trno =(select min(r1.trno)
                        from mulpola r1
                        where r.polno = r1.polno and r.covercd = r1.covercd);
   
    r_product c_product%rowtype;
    r_cprrid  cprrid%rowtype;
   
begin
    insert
        into dmlog
            (
                prg,
                type,
                msg,
                crntime
            )
            values
            (
                'sp_convert_rider',
                'I',
                'Starts',
                systimestamp
            );
    commit;
   
   

    open c_product;
    loop
        fetch c_product into r_product;
        exit when c_product%notfound; -- v_row_count = 100000 ;--
       
        v_row_count := v_row_count + 1;       
       
        ----------------------
        --- (1) rider header
        r_cprrid.cccbscid := null;
        r_cprrid.clmnum   := r_product.claimno;
        r_cprrid.cprpolid := null;
        r_cprrid.plcnum   := r_product.polno;
        r_cprrid.coverage := r_product.covercd;
        r_cprrid.rider    := r_product.covercd;
       
       
       
        ----------------------
        --- (2) life
        begin
            select (case when m.lanewicno is not null and trim(m.lanewicno) <> '' then m.lanewicno || 'NIC'
                                when m.laicno is not null and trim(m.laicno) <> '' then m.laicno || 'OIC'
                                else '' end ),
                m.dtdeath,
                m.enpw
            into r_cprrid.life,
                r_cprrid.mevtdte,
                v_enpw
            from msclmreg m
            where m.claimno = r_product.claimno and rownum < 2;
            exception
                when no_data_found then
                    r_cprrid.life    := '';
                    r_cprrid.mevtdte := null;
                    v_enpw           := null;
        end;
       
       
        --- rider index
        r_cprrid.rindex := r_product.polno||'-'||r_product.covcode||'-'||r_product.covcode||'-'||r_cprrid.life||r_product.covcode;
       
       
       
        ----------------------
        --- (3) jlife and tax, total amount
        begin
            select p.lifeclaim, nvl(p.sertax, 0.0), nvl(p.lupvalue, 0.0), poltype
            into v_lifeclaim, v_sertax, v_lupvalue, v_poltype
            from msclmpol p
            where p.claimno = r_product.claimno and p.polno = r_product.polno and rownum < 2;
            exception
                when no_data_found then
                    v_lifeclaim := null;
                    v_sertax    := 0.0;
                    v_lupvalue  := 0.0;
                    v_poltype   := null;
        end;
       
        if v_lifeclaim = '1' then
            r_cprrid.jlife := '0';
        else
            r_cprrid.jlife := '1';
        end if;      
       
       
        ----------------------
        --- (4) rider code
        r_cprrid.pcurf      := '';
        r_cprrid.pcurt      := '';
        r_cprrid.tranno     := null;
        r_cprrid.validflag  := '2';
        r_cprrid.prodcode   := r_product.covercd;
        r_cprrid.currency   := 'MYR';
        r_cprrid.sumins     := r_product.sa;
        r_cprrid.orgsumins  := r_product.sa;
        r_cprrid.adjsumins  := r_product.sa;
       
       
        ----------------------
        --- (5) MORTALITY
        if r_product.prod_type = 'T' then
            -- tranditional product
            begin
                select trim(xmorind), xmorper, calprm
                into r_cprrid.mortcls, r_cprrid.mortprcnt, r_cprrid.extraprem
                from mpolicya
                where polno = r_product.polno and covercd = r_product.covercd and xmorind is not null and rownum < 2;
            exception
            when no_data_found then
                r_cprrid.mortcls    := null;
                r_cprrid.mortprcnt  := null;
                r_cprrid.extraprem  := 0;
            end;
--  TODO:           
--            begin
--                select xmorind
--                into r_cprrid.premload
--                from mpolicya
--                where polno = r_product.polno and covercd = r_product.covercd and xmorind = 'PM' and rownum < 2;
--            exception
--            when no_data_found then
--                r_cprrid.premload := 0;
--            end;
                r_cprrid.premload := 0;
        else
            -- linked product
            begin
                select xmorind, xmorper, calprm
                into r_cprrid.mortcls, r_cprrid.mortprcnt, r_cprrid.extraprem
                from mulpola
                where polno = r_product.polno and covercd = r_product.covercd and xmorind is not null and rownum < 2;
            exception
            when no_data_found then
                r_cprrid.mortcls    := null;
                r_cprrid.mortprcnt  := null;
                r_cprrid.extraprem  := 0;
            end;
--TODO:            --character to number conversion error
--            begin
--                select trim(xmorind)
--                into r_cprrid.premload
--                from mulpola
--                where polno = r_product.polno and covercd = r_product.covercd and xmorind = 'PM' and rownum < 2;
--            exception
--            when no_data_found then
--                r_cprrid.premload := 0;
--            end;
        end if;
       
       
        ----------------------
        --- (6) LA age, nx prem date, UWSTS, occupation class, dt
        begin
            select
                p.laage,
                p.npdd,
                p.uwla,
                p.jlind,
                p.laocpcls,
                p.ontrandt,
                p.dtaccp
            into r_cprrid.anbccd,
                r_cprrid.nxpremdate,
                r_cprrid.uwsts,
                v_jlind,
                r_cprrid.occcls,
                v_ontrandt,
                v_dtaccp
            from mpolicy p
            where  p.polno = r_product.polno                    
                    and (r_cprrid.mevtdte >= p.dtonmov and r_cprrid.mevtdte  <= p.dtofmov ) and rownum < 2;
            exception
                when no_data_found then
                    r_cprrid.anbccd     := null;
                    r_cprrid.nxpremdate := null;
                    r_cprrid.uwsts       := null;
                    v_jlind             := null;
                    r_cprrid.occcls     := null;
                    v_ontrandt          := null;
                    v_dtaccp            := null;
        end;
       
        if v_lifeclaim <> '1' then
            begin
                select p.agenb, occpcls
                into r_cprrid.anbccd, r_cprrid.occcls
                from mpolicyn p
                where p.polno = r_product.polno and p.mtype = v_jlind and rownum < 2;
            exception
            when no_data_found then
                r_cprrid.anbccd := null;
                r_cprrid.occcls := null;
            end;
        end if;
       
       
       
        ----------------------
        --- (7) status
        r_cprrid.statcode   := r_product.accstatus_a;
        r_cprrid.cstatcode  := r_product.accstatus_a;
        r_cprrid.pstatcode  := '';
        r_cprrid.cpstatcode := '';
       
       
        ----------------------
        --- (8) rider details
        r_cprrid.prodprem   := r_product.calprm;
       
        r_cprrid.effterm    := r_product.benterm;
        r_cprrid.tpermterm  := r_product.prmterm;
       
        r_cprrid.crrcd      := r_product.trndt;
        r_cprrid.rcesdte    := add_months(r_product.effect, 12 * r_product.benterm) - 1;
        r_cprrid.terdate    := add_months(r_product.effect, 12 * r_product.benterm) - 1;
        r_cprrid.pcesdte    := r_product.lpdd;
        r_cprrid.bcesdte    := r_product.lpdd;
        r_cprrid.crdate     := null;
       
        r_cprrid.incpdate   := r_product.effect;
        r_cprrid.upgdate    := null;
       
        --- effective date
        if r_product.claimtype = 'MEDICAL' then
            begin
                select t.coverdtfr
                into r_cprrid.regdate
                from tsclmpmm t
                where t.polno = r_product.polno and t.covercd = r_product.covercd and rownum < 2;
            exception
            when no_data_found then
                r_cprrid.regdate := null;
            end;
           
            r_cprrid.effdate    := r_cprrid.regdate;
            r_cprrid.generdate  := r_cprrid.regdate;
        else
            r_cprrid.regdate    := r_product.effect;
            r_cprrid.effdate    := r_product.effect;
            r_cprrid.generdate  := r_product.effect;
        end if;
       
        --- reinstatement date
        begin
            --select p.onmovdt
            select p.dtonmov           
            into r_cprrid.reinsdate
            from mpolicy p
            where p.onmovcd in ('11', '19', '72', '18', '20', '17', '25', '26') -- Revival movement code
                    and rownum < 2;
        exception
        when no_data_found then
            r_cprrid.reinsdate := null;
        end;
       
       
        --- opt
        r_cprrid.opt1   := null;
        r_cprrid.opt2   := null;
        r_cprrid.opt3   := null;
        r_cprrid.opt4   := null;
        r_cprrid.opt5   := null;
       
        r_cprrid.clntcode   := null;
        r_cprrid.decreview  := null;
        r_cprrid.cnvrtfr    := null;
        r_cprrid.cnvrtprod  := null;
        r_cprrid.cnvrtsa    := null;
       
        r_cprrid.baseind    := r_product.basicind;
       
       
       
        ----------------------
        --- (9) claim type
        if r_product.claimtype in ('CA', 'DEATH') then
            r_cprrid.mctype := 'DTH';
        elsif r_product.claimtype in ('ACC', 'PDB') then
            r_cprrid.mctype := 'TPD';
        elsif r_product.claimtype = 'CC' then
            r_cprrid.mctype := 'CIL';
        elsif r_product.claimtype in ('PRULADY', 'SI', 'COMP') then
            if buname = 'A' then
                r_cprrid.mctype := 'OTH';
            else
                r_cprrid.mctype := 'CIL';
            end if;
        elsif r_product.claimtype in ('MEDICAL', 'INDEMNITY', 'AMR', 'SN') then
            r_cprrid.mctype := 'MED';
        else
            r_cprrid.mctype := null;
        end if;
       
       
       
        ----------------------
        --- (10) evl status
        r_cprrid.cfmastatus := 'Y';
        r_cprrid.cfmstatus  := 'Y';
       
        if r_product.statuscc = 'E' then
            r_cprrid.evlastatus := 'EXG';
        elsif r_product.statuscc = 'A' then
            r_cprrid.evlastatus := 'ELI';
        elsif r_product.statuscc = 'D' then
            r_cprrid.evlastatus := 'REJ';
        elsif r_product.statuscc = 'C' then
            r_cprrid.evlastatus := 'CLS';
        else
            r_cprrid.evlastatus := 'PED';
        end if;
       
        r_cprrid.evlstatus  := r_cprrid.evlastatus;
        r_cprrid.clsstatus  := r_cprrid.evlastatus;
        r_cprrid.csclmsts   := r_cprrid.evlastatus;
       
        r_cprrid.dupastatus := 'N';
        r_cprrid.dupstatus  := 'N';
        r_cprrid.relastatus := null;
        r_cprrid.relstatus  := null;
       
        --- rejection
       
        if r_cprrid.evlastatus = 'REJ' then
            begin
            r_cprrid.rejlcode := trim(r_product.reason);
            exception
            when others then
                dbms_output.put_line(length(trim(r_product.reason)));
            end;   
           
            begin
                select descdeath
                into r_cprrid.rejdscr
                from mtclmilld
                where code = r_product.reason and rownum < 2;
            exception
            when no_data_found then
                r_cprrid.rejdscr := '';
            end;
        else
            r_cprrid.rejlcode := null;
            r_cprrid.rejdscr  := null;
        end if;
       
        --- exg
        if r_cprrid.evlastatus = 'EXG' then
            r_cprrid.exgrapct := trim(r_product.reason);
        else
            r_cprrid.exgrapct := null;
        end if;
       
        r_cprrid.pybkind := null;
       
        r_cprrid.rescstatus := (case when r_cprrid.mctype in ('DTH', 'TPD') then 'R' else 'N' end);
        r_cprrid.cresccode  := null;
        r_cprrid.resccode   := null;
        r_cprrid.rescdate   := null;
        r_cprrid.rsstrtdate := null;
        r_cprrid.rsenddate  := null;
       
        r_cprrid.wpind      := (case when v_enpw = 'Y' then '1' else '0' end);
        r_cprrid.wpaind     := r_cprrid.wpind;
        r_cprrid.wpbenfid   := null;
        r_cprrid.autwpbeid  := null;
        r_cprrid.wpstrtdate := null;
        r_cprrid.wpenddate  := null;
        r_cprrid.wopinstnum := null;
        r_cprrid.wopinstfreq:= null;
        r_cprrid.wpamt      := null;
       
       
       
       
        ----------------------
        --- (11) premium
        begin
            select min(m.duedt), max(m.duedt), sum(nvl(m.amount, 0))
            into r_cprrid.prstrtdate, r_cprrid.prenddate, r_cprrid.pramt
            from mpremium m
            where m.polno = r_product.polno and m.duedt >= r_cprrid.mevtdte
                and m.duedt <= r_cprrid.nxpremdate and rownum <=2;
        exception
        when no_data_found then
            r_cprrid.prstrtdate := null;
            r_cprrid.prenddate  := null;
            r_cprrid.pramt      := null;
        end;      
       
       
       
        ----------------------
        --- (12) payment amount
        r_cprrid.pyamt      := 0.0;
        r_cprrid.pocurrency := 'MYR';
        r_cprrid.latefee    := 0.0;
        r_cprrid.polaccval  := 0.0;
        r_cprrid.odedctpay  := 0.0;
        r_cprrid.polaccadj  := 0.0;
        r_cprrid.latefeeadj := 0.0;
        r_cprrid.rndadjust  := 0.0;
        r_cprrid.cmpamt     := 0.0;
       
        if r_cprrid.baseind = 'Y' and r_cprrid.mctype = 'DTH' then
            -- basic plan for death
            r_cprrid.totpayamt := r_product.actamtpaid + v_lupvalue;
           
        else
            -- other riders
            r_cprrid.totpayamt := r_product.actamtpaid;
           
           
        end if;
       
        if r_cprrid.baseind = 'Y' then
            r_cprrid.taxamt := v_sertax;
        else
            r_cprrid.taxamt := 0.0;
        end if;
       
       
       
       
        ----------------------
        --- (13) TODO: newly required fields for MY
        r_cprrid.grosslimit := r_product.sa;
        r_cprrid.trno       := 1;
-- TODO: NO TABLE STRUCTURE       
--        BEGIN
--            select t.category
--            into r_cprrid."account"
--            from tpolcov t
--            where t.poltype = v_poltype and t.covercd = r_product.covcode and rownum < 2;
--        EXCEPTION
--        WHEN no_data_found THEN
--            r_cprrid."account" := null;
--        END;
       
        begin
            select m.annexcd
            into r_cprrid.annexcd
            from mtannexcd  m
            where m.poltype = v_poltype and m.covercd = r_product.covcode and rownum < 2;
        exception
        when no_data_found then
            r_cprrid.annexcd := null;
        end;
       
        if buname = 'A' then
            -- PAMB
            if r_product.polno < '32800000' then
                -- tranditional product
                r_cprrid.coverdt := r_product.trndt;
            elsif r_product.polno >= '32800000' and r_product.polno < '48000000' then
                -- linked product
                r_cprrid.coverdt := v_ontrandt;
            elsif r_product.polno >= '48000000' and r_product.polno < '72800000' then
                -- group product
                r_cprrid.coverdt := v_dtaccp;
            else
                r_cprrid.coverdt := null;
            end if;
        else
            -- PBTB
            if r_product.polno >= '95000000' and r_product.polno < '99999999' then
                -- linked product
                r_cprrid.coverdt := v_ontrandt;
            elsif r_product.polno >= '40000000' and r_product.polno < '89999999' then
                -- group product
                r_cprrid.coverdt := v_dtaccp;
            else
                -- tranditional product
                r_cprrid.coverdt := r_product.trndt;
            end if;
        end if;
       
        begin
            select s.defer_prd, s.int_rate
            into r_cprrid.graceperiod, r_cprrid.interestrate
            from mpolicys2 s
            where s.polno = r_product.polno and rownum < 2;
        exception
        when no_data_found then
            r_cprrid.graceperiod := null;
            r_cprrid.interestrate := null;
        end;
--       
            sp_usrid(r_product.admitby,r_cprrid.crtby,r_cprrid.crtbyn,v_temp_char);
            r_cprrid.lastmoddte    := r_product.dtadmit;
            sp_usrid(r_product.admitby,r_cprrid.lastmodby,r_cprrid.lastmodbyn,v_temp_char);
            r_cprrid.lastmodbyn    := 'NCS_CONV';
             
       ----------------------
       --- (14) finally insert into rider table
       insert into cprrid values r_cprrid;

       if mod(v_row_count,5000) = 0 then
           commit;
       end if;
       
       
    end loop;
   
    close c_product;
   

    insert
    into dmlog
        (
            prg,
            type,
            msg,
            crntime
        )
        values
        (
            'sp_convert_claim_rider',
            'I',
            'succeed in '|| v_row_count||' rows',
            systimestamp
        );
    commit;
   
    exception
    when others then
        dbms_output.put_line
        (
            'SQLCODE = '||sqlcode
        )
        ;
        dbms_output.put_line
        (
            'SQLERRM = '||sqlerrm
        )
        ;
        dbms_output.put_line
        (
            'DBMS_UTILITY.FORMAT_ERROR_STACK  = '||dbms_utility.format_error_stack
        )
        ;
        dbms_output.put_line
        (
            'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE  = '|| dbms_utility.format_error_backtrace
        )
        ;
        v_sqlerrm := substr
        (
            'error record key is claimno='||r_product.claimno||',polocyno is '||r_product.polno||',covercd is '||r_product.covercd||',SQLCODE = '||sqlcode||' '||dbms_utility.format_error_stack||' '|| dbms_utility.format_error_backtrace,1,255
        )
        ;
        dbms_output.put_line
        (
            v_sqlerrm
        )
        ;
       
    insert
    into dmlog
        (
            prg,
            type,
            msg,
            crntime
        )
        values
        (
            'sp_convert_rider',
            'E',
            v_sqlerrm,
            systimestamp
        );
    commit;
end sp_convert_claim_rider;