Oracle流程控制示例

/*if*/
declare 
  v_fenju nvarchar2(10);
begin 
  select enter.fenjuid into v_fenju from enterpriseinfo enter 
  where enter.enterprisecode='4302010005';
  if v_fenju='110000' then 
     dbms_output.put_line('隶属北京公安分局');
  elsif v_fenju='120000' then
        dbms_output.put_line('隶属天津公安分局');
  else 
        dbms_output.put_line('未知所属');
  end if;
end;
/*case1*/
declare 
  v_fenju nvarchar2(10);
begin
  select enter.fenjuid into v_fenju from enterpriseinfo enter 
  where enter.enterprisecode='4302010005';
  case 
        when v_fenju='110000' then 
             dbms_output.put_line('隶属北京公安分局');
        when v_fenju='120000' then
             dbms_output.put_line('隶属天津公安分局');
        else
             dbms_output.put_line('未知所属');
  end case;
end;
/*case2*/
declare 
  v_fenju nvarchar2(10);
begin
  select enter.fenjuid into v_fenju from enterpriseinfo enter 
  where enter.enterprisecode='4302010005';
  case v_fenju
        when '110000' then 
             dbms_output.put_line('隶属北京公安分局');
        when '120000' then
             dbms_output.put_line('隶属天津公安分局');
        else
             dbms_output.put_line('未知所属');
  end case;
end;
/*loop*/
declare 
  v_i number :=1;
  v_s number :=0;
begin
  loop
      exit when v_i>100;
      v_s:=v_s+v_i;
      v_i:=v_i+1;
  end loop;
  dbms_output.put_line(v_s);
end;
/*while loop*/
declare 
  v_i number :=1;
  v_s number :=0;
begin
  while v_i<=100 loop
        v_s:=v_s+v_i;
        v_i:=v_i+1;
  end loop;
  dbms_output.put_line(v_s);
end;
/*for loop*/
declare
      v_s number :=0;
begin
      for v_i in 1..100 loop
          v_s:=v_s+v_i;
      end loop;
      dbms_output.put_line(v_s);
end;
/*above example is display 1+2+3+...+100*/
/*nesting loop display prime number between 1 and 100*/
declare 
      v_m number:=1;--start
      v_i number;
      v_n number:=0;--id
begin
      while v_m<100 loop
            v_i:=2;
            loop
                if mod(v_m,v_i)=0 then
                   v_i:=0;
                   exit;
                end if;
                v_i:=v_i+1;
                exit when v_i>v_m-1;
            end loop;
            if v_i>0 then
               v_n:=v_n+1;
               dbms_output.put_line(v_n || ' '|| v_m);
            end if;
            v_m:=v_m+2;
      end loop;
end;