pl/sql学习笔记之四 控制结构

1、PL/SQL supports lexical symbols, symbol sets, and identifiers as valid comparison operators.

2、关键字及保留字

    AND
          BEGIN

     IF 1 = 1 AND 2 = 2 THEN
         dbms_output.put_line('True.');
     END IF;
  END;

 

     BETWEEN  判断一个变量是否在两个变量之间，数据类型须相同。也是一个包含操作符，查看变量是否在边界内。
         BEGIN

     IF 1 BETWEEN 1 AND 3 THEN
         dbms_output.put_line('In the range.');
     END IF;
 END;

     IN       判断是否在一个值集内
        BEGIN

    IF 1 IN (1,2,3) THEN
        dbms_output.put_line(‘In the set.’);
    END IF;
END;

     IS EMPTY  check whether a VARRAY or NESTED TABLE

collection variable is empty. Empty means that the collection was constructed without
any default elements. This means no space was allocated to the SGA for elements in
the collection.

        DECLARE

    TYPE list IS TABLE OF INTEGER;
    a LIST := list();
BEGIN
    IF a IS EMPTY THEN
        dbms_output.put_line('"a" is empty.');
    END IF;
END;

     IS NULL 变量值是否为空，可以用NVL保证值不为空
        DECLARE

   var BOOLEAN;
   BEGIN
       IF var IS NULL THEN
          dbms_output.put_line('It is null.');
   END IF;
END;

     IS A SET 判断变量是不是VARRAY 或者 NESTED TABLE
        DECLARE
           TYPE list IS TABLE OF INTEGER;
           a LIST :=list();
        BEGIN
            IF a IS A SET THEN
                dbms_output.put_line('"a" is a set');
            END IF;
        END;
     LIKE 一个字符串是否是另一个的一部分
         DECLARE
            IF 'Str%' LIKE 'String' THEN
                dbms_output.put_line('Match');
            END IF;
         END;
     MEMBER OF 判断变量是否是collection中的一员。
     DECLARE
          TYPE list IS TABLE OF NUMBER;

  n VARCHAR2(10) := 'One';
  a LIST := list('One', 'Two', 'Three');

     BEGIN

 IF n MEMBER OF a THEN
    dbms_output.put_line('"n" is member.');
 END IF;

     END;
     SUBMULTISET check whether a VARRAY or NESTED TABLE collection is a subset of a mirrored datatype.

     DECLARE
        TYPE list IS TABLE OF INTEGER;

a LIST := list(1,2,3);
b LIST := list(1,2,3,4);

     BEGIN

IF a SUBMULTISET b THEN
     dbms_output.put_line('Subset.');
END IF;

     END;
    
     NOT/OR
3、Conditional Compilation Statements
    使用$IF, $THEN, $ELSE,$ELSIF, $ERROR, and $END 指令 创建 conditional compilation code block
    ALTER SESSION SET PLSQL_CCFLAGS = 'debug:1';
    BEGIN
      $IF $$DEBUG = 1 $THEN
           dbms_output.put_line('Debug Level 1 Enabled.');
      $END
    END;  
    $$DEBUG引用compile-time变量。
    与普通的if-else-then 最大区别在于使用$END结束$IF块；
    Please note that you cannot encapsulate a placeholder（占位符）, or bind variable, inside a conditional compilation block.
    不能封装占位符或者绑定变量;

4、支持simple, FOR, FORALL, and WHILE 循环，没有正式支持until loop，不过可以用simple来模拟until loop；
   
     simple loop
     exit可以在loop块的开头或者结尾，区别在于exit在loop 结尾多执行一次；
     EXIT WHEN NOT counter 可以实现until loop;
     Range FOR 循环语句
     BEGIN
       FOR i IN 1..3 LOOP
            dbms_output.put_line('Iteration ['||i||']');
       END LOOP;
     END;

     Cursor FOR Loop Statements
        BEGIN

   FOR i IN (SELECT COUNT(*) AS on_hand
                          , item_title
                          , item_rating
                  FROM item
                WHERE item_title LIKE 'Harry Potter%'
                    AND item_rating_agency = 'MPAA'
            GROUP BY item_title
                        , item_rating)
  LOOP
     dbms_output.put('('||i.on_hand||') ');
     dbms_output.put(i.item_title||' ');
     dbms_output.put_line('['||i.item_rating||']');
  END LOOP;
END;    

       WHILE LOOP Statements
        DECLARE

   counter NUMBER := 1;
   BEGIN
   WHILE (counter < 3) LOOP
       dbms_output.put_line('Index ['||counter||'].');
       IF counter >= 1 THEN
          counter := counter + 1;
       END IF;
   END LOOP;
END;

      WHILE LOOP 和Simple Loop的区别：1、while loop 必须给计数器赋初始值2、simple的第一次循环无法比较值?(The second difference is that you no longer check for the first versus subsequent
      iterations through the loop.)
      用GOTO和Label解决WHILE中continue问题
        DECLARE

   counter NUMBER := 1;
   BEGIN
   WHILE (counter < 3) LOOP
       IF counter = 2 THEN
          GOTO loopindex;
       ELSE
          dbms_output.put_line('Index ['||counter||'].');
       END IF;
       << loopindex >>
       IF counter >= 1 THEN
          counter := counter + 1;
       END IF;
   END LOOP;
END;

     隐式游标显示游标
     DML语句会生成隐式游标
     单行隐式游标  INSERT
     多行隐式游标
     可以使用%ROWCOUNT来查看隐式游标的行数
     显式游标
     DECLARE中明确定义的游标
     
     游标没有取到数据不会抛出异常，需要自己处理。

     DECLARE

TYPE item_record IS RECORD
( id NUMBER
, title VARCHAR2(60));
item ITEM_RECORD;
CURSOR c IS
SELECT item_id
, item_title
FROM item
WHERE item_id = -1;

    BEGIN

OPEN c;
LOOP
FETCH c INTO item;
  IF c%NOTFOUND THEN
     IF c%ROWCOUNT = 0 THEN
          dbms_output.put_line('No Data Found');
     END IF;
     EXIT;
  ELSE
          dbms_output.put_line('Title ['||item.title||']');
  END IF;
END LOOP;

    END;
    使用传入参数的动态游标
      DECLARE

lowend NUMBER := 1010;
highend NUMBER := 1020;
TYPE item_record IS RECORD
( id NUMBER
, title VARCHAR2(60));
item ITEM_RECORD;
CURSOR c IS
SELECT item_id
, item_title
FROM item
WHERE item_id BETWEEN lowend AND highend;

BEGIN

      lowend := TO_NUMBER(NVL(&1,1005));
      highend := TO_NUMBER(NVL(&2,1021));
   OPEN c;
  LOOP
  FETCH c INTO item;
  EXIT WHEN c%NOTFOUND;
       dbms_output.put_line('Title ['||item.title||']');
  END LOOP;
END;