Oracle扩展PL/SQL简介(二)

4.    块语句
1)    块的结构和变量声明：
DECLARE
        变量说明部分；
BEGIN
        执行语句部分；
[EXCEPTION
        例外处理部分；]
END;
2)    控制语句：
IF语句：
IF condition THEN
        Sequence_of_statements;
END IF;

IF condition THEN
        Sequence_of_statement1;
ELSE
        Sequence_of_statement2;
END IF;

IF condition1 THEN
        Sequence_of_statement1;
ELSIF condition2 THEN
        Sequence_of_statement2;
ELSIF condition3 THEN
        Sequence_of_statement3;
END IF;

举例：
declare
 v1 date:=to_date(' 2007-04-2', 'yyyy-mm-dd');
 v2 boolean;
begin
 if months_between(sysdate,v1) >5 then
v2 := true;
 dbms_output.put_line('true');
else
 v2:=false;
 dbms_output.put_line('false');
end if;
end;
/

[<<label_name>>]
CASE selector
  WHEN expression1 THEN
     sequence_of_statements1;
  WHEN expression2 THEN
     sequence_of_statements2;
   ...
  WHEN expressionn THEN
     sequence_of_statementsn;
   [ELSE sequence_of_statementsN+1;]
END [CASE [label_name];

举例：
declare
 v1 char(1):=upper('&v1');
v2 varchar2(20);
begin
 v2:=case v1
 when 'A' then 'Excellent'
when 'B' then 'Very Good'
when 'C' then 'Good'
else 'No such grade'
end;
dbms_output.put_line(chr(10) ||'level '|| v1 ||' is '||v2);
end;
/
3)    循环语句：
基本循环
LOOP
    Sequence_of_statements;
    IF condition THEN
        EXIT;
    END IF;
END LOOP;
例子：
create table t1 (c1 number(2));
---------------------------
declare
 v1 number(2):=1;
begin
loop
 insert into t1 values(v1);
 v1:=v1+1;
  exit when v1>10;
end loop;
end;
/

WHILE循环
WHILE condition LOOP
        Sequence_of_statements;
END LOOP;
举例：
declare
 v1 number(2):=1;
begin
 while v1<10 loop
  insert into t1 values(v1);
  v1:=v1+1;
 end loop;
end;
/
FOR循环
FOR counter IN lower_bound..higher_bound LOOP
        Sequence_of_statements;
END LOOP;
举例：
begin
dbms_output.put_line(chr(10)||' ----');
for v1 in reverse 1..9 loop
dbms_output.put_line(v1);
insert into t1 values(v1);
end loop;
dbms_output.put_line(' ----');
end;
/