现有A表和B表，有bill_id，detail_data两个字段，其中 bill_id为数字型，是用户手机号码；detail_data是个字符串总长度为4000，每16位是一组，每组由两部分组成，费用代码(7位)和费用（9位）（注：位数包括空格），多个组拼成该字段的内容。现要将A表中的bill_id和B表的bill_id相等的记录，将其detail_data 合入B表中的detail_data，原则是费用代码相同的，将费用相加，A表中有但B表中没有的费用代码，将费用代码和费用追加到B表的 detail_data中。<br><br>写出存储过程。<br><br>create or replace procedure test_sp_afa<br><br>is<br><br> ls_detail_data varchar2(4000);<br><br> cursor c1 is select a.bill_id from a, b where a.bill_id=b.bill_id;<br><br>begin<br><br> for iii in c1 loop<br><br> --拆分b表,写入test_b(这个过度表很恶心)<br><br> insert into test_b<br><br> select substr(detail_data,rownum*16-15,7) daima0,substr(detail_data,rownum*16-15,9) feiy00<br><br> from b<br><br> start with bill_id=iii.bill_id<br><br> connect by bill_id=iii.bill_id and rownum<=ceil(length(trim(detail_data))/16);<br><br> --拆分a表,和test_b连接.<br><br> merge into test_b bb<br><br> using (<br><br> select substr(detail_data,rownum*16-15,7) daima0,substr(detail_data,rownum*16-15,9) feiy00<br><br> from a<br><br> start with bill_id=iii.bill_id<br><br> connect by bill_id=iii.bill_id and rownum<=ceil(length(trim(detail_data))/16)<br><br> ) aa<br><br> on (aa.daima0=bb.daima0)<br><br> when matched then<br><br> update set bb.feiy00+aa.feiy00<br><br> when not matched then<br><br> insert values (aa.daima0,aa.feiy00);<br><br> --取连接后的detail_data 值<br><br> select max(detail_data) into ls_detail_data <br><br> from <br><br> (<br><br> select replace(sys_connect_by_path(rpad(daima0,7,' ')||rpad(feiy00,9,' '),','),',') detail_data<br><br> from <br><br> (select a.*,rownum rn <br><br> from test_b a ) <br><br> start with rn=1 <br><br> connect by rn=prior rn+1<br><br> );<br><br> --修改b表对应记录<br><br> update b<br><br> set detail_data =ls_detail_data<br><br> where bill_id=iii.bill_id;<br><br> --删除test_b内容<br><br> truncate table test_b;<br><br>end loop;<br><br> --exception<br><br>end;
亚信科技（asiainfo）软件工程师面试经验&面试问题
面试问题
简单的自我介绍后，就出了上面的题目，巨汗
面试过程
现有A表和B表，有bill_id，detail_data两个字段，其中 bill_id为数字型，是用户手机号码；detail_data是个字符串总长度为4000，每16位是一组，每组由两部分组成，费用代码(7位)和费用（9位）（注：位数包括空格），多个组拼成该字段的内容。现要将A表中的bill_id和B表的bill_id相等的记录，将其detail_data 合入B表中的detail_data，原则是费用代码相同的，将费用相加，A表中有但B表中没有的费用代码，将费用代码和费用追加到B表的 detail_data中。<br><br>写出存储过程。<br><br>create or replace procedure test_sp_afa<br><br>is<br><br> ls_detail_data varchar2(4000);<br><br> cursor c1 is select a.bill_id from a, b where a.bill_id=b.bill_id;<br><br>begin<br><br> for iii in c1 loop<br><br> --拆分b表,写入test_b(这个过度表很恶心)<br><br> insert into test_b<br><br> select substr(detail_data,rownum*16-15,7) daima0,substr(detail_data,rownum*16-15,9) feiy00<br><br> from b<br><br> start with bill_id=iii.bill_id<br><br> connect by bill_id=iii.bill_id and rownum<=ceil(length(trim(detail_data))/16);<br><br> --拆分a表,和test_b连接.<br><br> merge into test_b bb<br><br> using (<br><br> select substr(detail_data,rownum*16-15,7) daima0,substr(detail_data,rownum*16-15,9) feiy00<br><br> from a<br><br> start with bill_id=iii.bill_id<br><br> connect by bill_id=iii.bill_id and rownum<=ceil(length(trim(detail_data))/16)<br><br> ) aa<br><br> on (aa.daima0=bb.daima0)<br><br> when matched then<br><br> update set bb.feiy00+aa.feiy00<br><br> when not matched then<br><br> insert values (aa.daima0,aa.feiy00);<br><br> --取连接后的detail_data 值<br><br> select max(detail_data) into ls_detail_data <br><br> from <br><br> (<br><br> select replace(sys_connect_by_path(rpad(daima0,7,' ')||rpad(feiy00,9,' '),','),',') detail_data<br><br> from <br><br> (select a.*,rownum rn <br><br> from test_b a ) <br><br> start with rn=1 <br><br> connect by rn=prior rn+1<br><br> );<br><br> --修改b表对应记录<br><br> update b<br><br> set detail_data =ls_detail_data<br><br> where bill_id=iii.bill_id;<br><br> --删除test_b内容<br><br> truncate table test_b;<br><br>end loop;<br><br> --exception<br><br>end;