一、条件分支语句:
--1. if..else 条件判断
declare
v_comm emp.comm%type;
v_empno emp.empno%type := &no;
begin
select comm into v_comm from emp where empno = v_empno;
if v_comm < 500 then
update emp set comm = comm + 500 where empno = v_empno;
elsif v_comm >= 500 and v_comm < 1000 then
update emp set comm = comm + 300 where empno = v_empno;
else
update emp set comm = comm + 100 where empno = v_empno;
end if;
end;
/
--2. case匹配单一值
declare
v_deptno emp.deptno%type := &deptno;
begin
case v_deptno
when 10 then
update emp set comm = 1111 where deptno = v_deptno;
when 20 then
update emp set comm = 2222 where deptno = v_deptno;
when 30 then
update emp set comm = 3333 where deptno = v_deptno;
else
dbms_output.put_line('没有该部门...');
end case;
end;
/
--3. case匹配复杂条件
declare
v_comm emp.comm%type;
v_empno emp.empno%type := &no;
begin
case
when v_comm < 500 then
update emp set comm = comm + 500 where empno = v_empno;
when v_comm >= 500 and v_comm < 100 then
update emp set comm = comm + 300 where empno = v_empno;
else
update emp set comm = comm + 100 where empno = v_empno;
end case;
end;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
二、循环语句:
--1.基本循环 loop
declare
v_i int := 1;
begin
loop
insert into temp values(v_i);
exit when v_i=10;
v_i := v_i+1;
end loop;
end;
/
--2.while循环
declare
i int := 1;
begin
while i < 10 loop
dbms_output.put_line('i: ' || i);
i := i+1;
end loop;
end;
/
--3.for循环
begin
for i in 1..10 loop
dbms_output.put_line('i: ' || i );
end loop;
end;
/
--4.for循环 加入reverse关键字 表示反向开始
begin
for i reverse in 1..10 loop
dbms_output.put_line('i: ' || i );
end loop;
end;
/
--5.嵌套循环和标号
declare
v_result int;
begin
<<outer1>>
for i in 1..10 loop
<<inter1>>
for j in 1..10 loop
v_result := i*j;
exit outer1 when v_result = 100;
exit when v_result = 50;
end loop inter1;
dbms_output.put_line(v_result);
end loop outer1;
dbms_output.put_line(v_result);
end;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
三、顺序控制语句:
--1.GOTO
declare
i int := 1;
begin
loop
insert into temp values(i);
if i=10 then
goto end_loop;
end if;
i := i + 1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
/
--2.null
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = &no1;
if v_sal > 300 then
update emp set sal = 1000 where empno = &no2;
else
null;
end if;
end;
/