Oracle游标或存储过程

/*
方式1：可执行选取代码块允许
*/
declare cursor cur_tmp is
(

  select 
   '' as tmp_status
  from dual
  
);
begin
  for tmp_row in cur_tmp loop
    if tmp_row.tmp_status='0' then
      begin
       dbms_output.put_line('1111');
      end;
    elsif tmp_row.tmp_status!='0' then
      begin
       dbms_output.put_line('2222');
      end;
    else
      begin
       dbms_output.put_line('3333');
      end;
    end if; 
  end loop;
  
  commit;  
  
end;


/*
方式2：可执行选取代码块允许
*/
declare 
  v_id1 varchar2(40);
  v_id2 varchar2(40):='aaaaaaa';
  v_id3 varchar2(40);
cursor cur_tmp is
(

  select 
   '555' as tmp_status
  from dual
  
);
begin
  for tmp_row in cur_tmp loop
    
    v_id1 := tmp_row.tmp_status;
    dbms_output.put_line(v_id1);
    dbms_output.put_line(v_id2);
    
    if tmp_row.tmp_status='0' then
      begin
       dbms_output.put_line('1111');
      end;
    elsif tmp_row.tmp_status!='0' then
      begin
       dbms_output.put_line('2222');
      end;
    else
      begin
       dbms_output.put_line('3333');
      end;
    end if; 
  end loop;
  
 commit;  
  
end;



/*
方式3：存储过程模式--无入参
*/
create or replace procedure 用户名.pro_cus_temp01
as

  v_id1 varchar2(40);
  v_id2 varchar2(40);
  v_id3 varchar2(40);

begin 
  
  declare cursor cur_tmp is
  (

    select 
     '555' as tmp_status
    from dual
    
  );
  begin
    for tmp_row in cur_tmp loop
      
      v_id1 := tmp_row.tmp_status;
      dbms_output.put_line(v_id1);
      dbms_output.put_line(v_id2);
      
      if tmp_row.tmp_status='0' then
        begin
         dbms_output.put_line('1111');
        end;
      elsif tmp_row.tmp_status!='0' then
        begin
         dbms_output.put_line('2222');
        end;
      else
        begin
         dbms_output.put_line('3333');
        end;
      end if; 
    end loop;
    
  end;
  
  commit;

end;
---调用存储过程--无入参
call 用户名.pro_cus_temp01() ;


/*
方式4：存储过程模式--有入参
*/
create or replace procedure 用户名.pro_cus_temp02
(
  in_id1 in varchar2,
  in_no in decimal
)
is
  
  v_id1 varchar2(40);
  v_id2 varchar2(40);
  v_id3 varchar2(40);

begin

  declare cursor cur_tmp is
  (

    select
     '555' as tmp_status
    from dual

  );
  begin
    for tmp_row in cur_tmp loop

      v_id1 := in_id1;
      dbms_output.put_line(v_id1);

      if tmp_row.tmp_status='0' then
        begin
         dbms_output.put_line('1111');
        end;
      elsif tmp_row.tmp_status!='0' then
        begin
         dbms_output.put_line('2222');
        end;
      else
        begin
         dbms_output.put_line('3333');
        end;
      end if;
    end loop;

  end;

  commit;

end;
---调用存储过程--有入参
call 用户名.pro_cus_temp02('kkkkk',0) ;