Oracle operation 2

1. String concatenation

Update tbl_main d set D. area_id = '0' | D. area_id

2. Stored Procedure and cursor

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 to be accepted

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> = '2017-04-01 'and t1.accept _ time <= '2017-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;

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;