oracle控制流

控制流
1)if then  end if;
2)if then elsif else end if;
3)if then else end if;
4)loop end loop;
5)while loop end loop;
6)for ..in reverse .. loop .. end loop;
1)案例
create or replace procedure sp_pro4(v_name varchar2)
is
  v_sal emp1.sal%type;
  v_sal_addroute number(2,1):=0.1;
begin
  select sal into v_sal from emp1 where ename=v_name;
  if v_sal<2000 then
    v_sal:=v_sal*(1+v_sal_addroute);
  end if;
   dbms_output.put_line('user:'||v_name||'sal:'||v_sal);
end;
2)案例
create or replace procedure sp_pro5(v_name varchar2)
is
  v_comm emp1.comm%type;
begin
  select nvl(comm,0) into v_comm from emp1 where ename=v_name;
  if v_comm!=0 then
    v_comm:=v_comm+100;
  else
    v_comm:=200;
  end if;
   dbms_output.put_line('user:'||v_name||'   comm:'||v_comm);
end;
3)案例
create or replace procedure sp_pro6(v_no number)
is
  v_sal emp1.sal%type;
  v_job emp1.job%type;
begin
  select job,sal into v_job,v_sal from emp1 where empno=v_no;
  if v_job='PRESIDENT' then
    v_sal:=v_sal+1000;
  elsif v_job='MANAGER' then
    v_sal:=v_sal+500;
  else
    v_sal:=v_sal+200;
  end if;
   dbms_output.put_line('userID:'||v_no||'   sal:'||v_sal);
end;
4)案例
create or replace procedure sp_pro7(v_name varchar2) is
  v_id users.id%type:=1;
begin
  loop 
    insert into users values(v_id,v_name);
    exit when v_id=10;
    v_id:=v_id+1;
  end loop;
end;
5)案例
create or replace procedure sp_pro8(v_name varchar2) is
  v_id users.id%type:=11;
begin
  while v_id<=20
  loop 
    insert into users values(v_id,v_name);
    v_id:=v_id+1;
  end loop;
end;
6)案例
create or replace procedure sp_pro9(v_name varchar2) is
  v_id users.id%type:=21;
begin
  for i in reverse v_id+1 .. 30
  loop 
    insert into users values(v_id,v_name);
    v_id:=v_id+1;
  end loop;
end;