循环进入光标，直到每个IF ELSE在oracle中为true [英] LOOP into cursor until each IF ELSE true in oracle

问题描述

我写了一个 cursor ，在其中我希望遍历每一列，直到变为真为止。因此，如果所有IF语句都匹配，那么我想将数据插入 VALID 表中，或者最后我想将错误的数据插入无效表。

I have written a cursor where I want to LOOP each and every column until it becomes true. So if all the IF statement matches to true then I want to insert the data into VALID table or at last I want to insert the incorrect data into the INVALID TABLE.

下面是光标。请让我知道我的步骤是正确的还是需要做任何更改。

Below is the cursor. Kindly let me know whether my step is accurate or Do I need to make any changes in that.

create or replace procedure fiber_transm_valid_data as 
begin
  for cur_r in (select rj_span_id, 
                       rj_maintenance_zone_name,
                       rj_maintenance_zone_code                
                from app_fttx.transmedia@sat               
               )
  loop               
    if cur_r.rj_span_id > '0' then       
		elsif cur_r.rj_maintenance_zone_name = 'aa' then
			elsif  cur_r.rj_maintenance_zone_code = 'A123' then
				INSERT INTO VALID TABLE 
				(span_id, maintenance_zone_name,rj_maintenance_zone_code)
       values (cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code);
	   
	   ELSE
			INSERT INTO INVALID TABLE 
				(span_id, maintenance_zone_name,rj_maintenance_zone_code)
       values (cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code);
  end loop;  
end fiber_transm_valid_data;

推荐答案

不太像那样； IF 是错误的。

Not quite like that; IF is wrong. Have a look at this.

create or replace procedure fiber_transm_valid_data as 
  l_state_name table_of_states.rj_state_name%type;
begin
  for cur_r in (select rj_span_id, 
                       rj_maintenance_zone_name,
                       rj_maintenance_zone_code,
                       rj_state_name
                from app_fttx.transmedia@sat               
               )
  loop         
    select max(rj_state_name) 
      into l_state_name
      from table_of_states
      where rj_state_name = cur_r.rj_state_name
        and rownum = 1;

    if     cur_r.rj_span_id > '0' 
       and cur_r.rj_maintenance_zone_name = 'aa' 
       and cur_r.rj_maintenance_zone_code = 'A123'
       and l_state_name = 1       
    then       
       INSERT INTO VALID_TABLE 
         (span_id, maintenance_zone_name,rj_maintenance_zone_code)
          values 
         (cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code);
    else
        INSERT INTO INVALID_TABLE 
          (span_id, maintenance_zone_name,rj_maintenance_zone_code)
           values 
          (cur_r.rj_span_id, cur_r.rj_maintenance_zone_name, cur_r.rj_maintenance_zone_code);
    end if;       
  end loop;  
end fiber_transm_valid_data;

这篇关于循环进入光标，直到每个IF ELSE在oracle中为true的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！