PL/SQL基本程序结构和语句

1.条件结构

CASE

语法

CASE selector
        WHEN 'value1' THEN S1;
        WHEN 'value2' THEN S2;
        WHEN 'value3' THEN S3;
        ...
        ELSE Sn;  -- default case
    END CASE;
           

实例

DECLARE
   grade char() := 'A';
BEGIN
   CASE grade
      when 'A' then dbms_output.put_line('Excellent');
      when 'B' then dbms_output.put_line('Very good');
      when 'C' then dbms_output.put_line('Well done');
      when 'D' then dbms_output.put_line('You passed');
      when 'F' then dbms_output.put_line('Better try again');
      else dbms_output.put_line('No such grade');
   END CASE;
END;
           

IF

语法

1. IF THEN 结构

   IF condition THEN 
          S;
       END IF;
              

2. IF THEN ELSIF ELSE 语法

   IF(boolean_expression 1)THEN 
      S1; -- Executes when the boolean expression 1 is true 
   ELSIF( boolean_expression 2) THEN
      S2;  -- Executes when the boolean expression 2 is true 
   ELSIF( boolean_expression 3) THEN
      S3; -- Executes when the boolean expression 3 is true 
   ELSE 
      S4; -- executes when the none of the above condition is true 
   END IF;
              

3. IF THEN ELSE 语法

   IF condition THEN
      S1; 
   ELSE 
      S2;
   END IF;
              

实例

1. IF THEN 结构实例

DECLARE
   a number() := ;
BEGIN
   a:= ;
  -- check the boolean condition using if statement 
   IF( a <  ) THEN
      -- if condition is true then print the following  
      dbms_output.put_line('a is less than 20 ' );
   END IF;
   dbms_output.put_line('value of a is : ' || a);
END;
/
           

1. IF THEN ELSIF ELSE 结构实例

DECLARE
   a number(3) := 100;
BEGIN
   -- check the boolean condition using if statement 
   IF( a <  ) THEN
      -- if condition is true then print the following  
      dbms_output.put_line('a is less than 20 ' );
   ELSE
      dbms_output.put_line('a is not less than 20 ' );
   END IF;
   dbms_output.put_line('value of a is : ' || a);
END;
/
           

1. IF THEN ELSE 结构实例

DECLARE
   a number(3) := 100;
BEGIN
   -- check the boolean condition using if statement 
   IF( a <  ) THEN
      -- if condition is true then print the following  
      dbms_output.put_line('a is less than 20 ' );
   ELSE
      dbms_output.put_line('a is not less than 20 ' );
   END IF;
   dbms_output.put_line('value of a is : ' || a);
END;
/
           

2.循环结构

LOOP

语法

LOOP
  Sequence of statements;
END LOOP;
           

实例

DECLARE
   x number := ;
BEGIN
   LOOP
      dbms_output.put_line(x);
      x := x + ;
      IF x >  THEN
         exit;
      END IF;
   END LOOP;
   -- after exit, control resumes here
   dbms_output.put_line('After Exit x is: ' || x);
END;
/
           

FOR

语法

FOR counter IN initial_value .. final_value LOOP
   sequence_of_statements;
END LOOP;
           

实例

DECLARE
   a number(2);
BEGIN
   FOR a in  ..  LOOP
       dbms_output.put_line('value of a: ' || a);
  END LOOP;
END;
/
           

3) WHILE

语法

WHILE condition LOOP
   sequence_of_statements
END LOOP;
           

实例

DECLARE
   a number() := ;
BEGIN
   WHILE a <  LOOP
      dbms_output.put_line('value of a: ' || a);
      a := a + ;
   END LOOP;
END;
/
           

3.顺序结构

GOTO

在PL/SQL编程语言的GOTO语句提供无条件跳转到在同一个子程序的GOTO标签的语句。

注意：GOTO语句是不建议使用在任何编程语言，因为它使得程序难以跟踪控制流程，使程序难以理解，难以修改。如果使用GOTO的任何程序可以改写，就尽量不要使用GOTO语句。

语法

GOTO label;
..
..
<< label >>
statement;
           

实例

DECLARE
   a number() := ;
BEGIN
   <<loopstart>>
   -- while loop execution 
   WHILE a <  LOOP
      dbms_output.put_line ('value of a: ' || a);
      a := a + ;
      IF a =  THEN
         a := a + ;
         GOTO loopstart;
      END IF;
   END LOOP;
END;
/
           

NULL

NULL只是把控制权传递给后面的语句。

NULL语句的用处：

n 为GOTO语句提供一个目标

n 通过使条件语句的含义和行为更加清晰来提高可读性

n 创建占位符和子程序桩

n 表明清楚这种可能性，但不需要进行处理。