pl/sql控制结构

2019独角兽企业重金招聘Python工程师标准>>>

控制结构
1.if
2.循环
3.控制句---goto null
条件分支
if-- then , if -- then --else , if --then -- elsif -- else

create or replace procedure sp_pro6(spName varchar2) is

v_sal emp.sal%type;
begin
  select sal into v_sal from emp where ename=spName;
  if v_sal<2000 then
    update emp set sal=sal*1.1 where ename=spName;
  end if;
end;  

create or replace procedure sp_pro7(spName varchar2) is

v_comm emp.comm%type;
begin
  select comm into v_comm from emp where ename=spName;
  if v_comm<>0 then
      update emp set comm=v_comm+100 where ename=spName;
    else
      update emp set comm = 200 where ename=spName;
  end if;
end;

create or replace procedure sp_pro8(spNo number) is
v_job emp.job%type;
begin
  select job into v_job from emp where empno = spNo;
  if v_job='PRESIDENT' then
    update emp set sal=sal+1000 where empno = spNo;
  elsif v_job='MANAGER'then
    update emp set sal=sal+500 where empno = spNo;
  else
    update emp set sal=sal+200 where empno = spNo;
  end if;  
end;

create or replace procedure sp_pro9(spName varchar2) is

v_num number:=1;
begin
  loop
    insert into mytest values(v_num,spName);
    --判断是否退出循环
    exit when v_num=10;
    --自增
    v_num:=v_num+1;
  end loop;
end;

create or replace procedure sp_pro10(spName varchar2) is

v_num number:=11;
begin
  while v_num<=20 loop
    insert into mytest values(v_num,spName);
    v_num:=v_num+1;
  end loop;
end;

declare
i int:=1;
begin
  loop
    dbms_output.put_line('输出i='||i);
    if i=10 then
    goto end_loop;
    end if;
    i:=i+1;
    end loop;
    dbms_output.put_line('循环结束1');
    <<end_loop>>
    dbms_output.put_line('循环结束2');
end;

declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal
from emp where empno=&no;
if v_sal<3000 then
update emp set comm=sal*0.1 where ename=v_ename;
else
null;
end if;
end;

转载于:https://my.oschina.net/869088067/blog/2249230