控制结构
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;