Oracle操作2

1.字串拼接

update tbl_main d set d.area_id='0'||d.area_id

2.預存程序及遊標

create or replace procedure PRC_INSET_DEAL
 as
begin
  declare
   cursor main_depart
   is
   select * from main_bak ;
   temp main_bak%rowtype;
   begin
   open main_depart;
   loop
   fetch main_depart into temp;
   exit when main_depart%notfound;
    insert into main_deal s (
        s.main_no,
        s.accept_time,
        s.tele_no     

    )
values (       
       temp.main__no,
        temp.time,
        temp.tele_no

        ) ;
  end loop;
  close main_depart;
  end;
end PRC_INSET_DEAL;

 

 

 

--待受理 create or replace procedure insert_main  

as  

begin

  declare  

cursor emp_cur  

is

select distinct(a1.main_no) from tbl_a a1 where a1.main_no not in(select t1.main_no from tbl_main t1 where t1.accept_time>='2012-04-01' and t1.accept_time<='2012-04-12) 

  l_emp varchar2(100);  

 CATEGORY varchar2(10);  

AREA_ID  varchar2(10);  

begin  

open emp_cur;  

loop  

fetch emp_cur into l_emp;  

exit when emp_cur%notfound;  

dbms_output.put_line(l_emp);    

select t.tele_area into AREA_ID from tbl_info t where  t.main_no=l_emp;        

dbms_output.put_line(VEST_AREA_ID);      

if substr(l_emp,1,1)='1' then   CATEGORY:='1';  

dbms_output.put_line(CATEGORY);   

declare   

cursor emp_cur1   

is   

select * from tbl_a t where  rownum<=1 and t.main_sheet_flow_no=l_emp;    

A tbl_a%rowtype;     

begin   open emp_cur1;  

loop   fetch emp_cur1 into A;  

exit when emp_cur1%notfound;   

dbms_output.put_line(A.TELE_NO);     

insert into tbl_deal (MAIN_NO, SHCATEGORY_ID,  AREA_ID, TELE_NO) values (l_emp, CATEGORY, '', '');    

end loop;  

close emp_cur1;

 end;

  end if;  

 end loop;  

close emp_cur;

 end;  

end insert_mainsheet;