The create or replace procedure p_lx is
The begin
UPDATE AB01_QHD1 t set t.w=1 where t.n ame in (select t.n ame from AB01_QHD1 t, AA01_QHD2 t2 where t.n ame=t2. The name).
commit;

End p_lx;

How to use vernier way to realize the process of the cursor in the form of a SQL statement for me

CodePudding user response:

To, don't use cursor, very affect performance,
You don't need to use a cursor,

 
- the update statement 
The create or replace procedure p_lx 
Is 
The begin 

The update AB01_QHD1 t 
The set t.w=1 
Where the exists (select null from AA01_QHD2 t2 where t2. The name=t1. The name). 

commit; 

End p_lx; 


- (2) using a for loop 
The create or replace procedure p_lx 
Is 
The begin 

Loop for res in (select the name from AA01_QHD2) 
The update AB01_QHD1 set w=1 where name=res. Name; 
End loop; 

commit; 

End p_lx; 

CodePudding user response:

 
- 1. The update statement 
The create or replace procedure p_lx 
Is 
The begin 

The update AB01_QHD1 t 
The set t.w=1 
Where the exists (select null from AA01_QHD2 t2 where t2. The name=t.n ame); 

commit; 

End p_lx; 

CodePudding user response:

No problem, you can put up,

CodePudding user response:

The create or replace procedure p_lx is
CURSOR C_EMP IS -- sound obvious CURSOR type
SELECT the name FROM AA01_QHD2;
C_ROW C_EMP % ROWTYPE; - the cursor variables, the type of the variable based on records of vernier C_EMP
The begin

- For loop
FOR C_ROW C_EMP IN LOOP
The UPDATE AB01_QHD1 t set t.w=1 where t.n ame=C_ROW. NAME;
commit;
END LOOP;

- the Fetch cycle
The OPEN C_EMP; - must be clear to open and close the cursor
LOOP
The FETCH C_EMP
INTO C_ROW;
EXIT the WHEN C_EMP % NOTFOUND;
The UPDATE AB01_QHD1 t set t.w=1 where t.n ame=C_ROW. NAME;
commit;
END LOOP;
The CLOSE C_EMP;

- While loop
The OPEN C_EMP; - must be clear to open and close the cursor
The FETCH C_EMP
INTO C_ROW;
WHILE C_EMP % FOUND LOOP
The UPDATE AB01_QHD1 t set t.w=1 where t.n ame=C_ROW. NAME;
commit;
The FETCH C_EMP
INTO C_ROW;
END LOOP;
The CLOSE C_EMP;

End p_lx;