控制语句

条件控制

IF语句

当PL/SQL执行到IF控制分支的语句时，首先判断条件，根据条件表达式的值选择相应的语句执行（放弃另一部分语句的执行）。使用范围：

1.支持控制语句的嵌套

2.分支结构包括单分支、双分支和多分支三种形式。

★IF THEN语句

在本例中，THEN到END IF之间的语句仅在sales大于quota+200的情况下执行。

CREATE PROCEDURE p_3_1_1  (sales NUMBER, quota NUMBER, emp_id NUMBER) IS
  bonus NUMBER := 0;
  updated VARCHAR2(3) := 'No';
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
      UPDATE employees
      SET salary = salary + bonus
      WHERE employee_id = emp_id;
      updated := 'Yes';
    END IF;
    DBMS_OUTPUT.PUT_LINE ('Table updated? ' || updated || ', ' ||
      'bonus = ' || bonus || '.');
  END p;
/
  call p(10100, 10000, 120);
  call p(10500, 10000, 121);

--Result:
--Table updated? No, bonus = 0.
--Table updated? Yes, bonus = 125.

★IF THEN ELSE语句

在本例中，如果当且仅当sale的值大于quota+200时，才会运行THEN到ELSE之间的语句；否则，运行ELSE和END IF之间的语句。

CREATE PROCEDURE p_3_1_2 (sales NUMBER, quota NUMBER, emp_id NUMBER) IS
  bonus NUMBER := 0;
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
    ELSE
      bonus := 50;
    END IF;
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
    UPDATE employees
    SET salary = salary + bonus
    WHERE employee_id = emp_id;
  END p;
/
  call p(10100, 10000, 120);
  call p(10500, 10000, 121);

--Result:
--bonus = 50
--bonus = 125

★嵌套IF THEN ELSE语句

CREATE PROCEDURE p_3_1_3 (sales NUMBER, quota NUMBER, emp_id NUMBER) IS
  bonus NUMBER := 0;
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
    ELSE
      IF sales > quota THEN
        bonus := 50;
      ELSE
        bonus := 0;
      END IF;
    END IF;
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
    UPDATE employees
    SET salary = salary + bonus
    WHERE employee_id = emp_id;
  END p;
/
  call p(10100, 10000, 120);
  call p(10500, 10000, 121);
  call p(9500, 10000, 122);

--Result:
--bonus = 50
--bonus = 125
--bonus = 0

★IF THEN ELSIF语句

在这个例子中，当sales大于50000时，第一个和第二个条件是正确的。但是，因为第一个条件是正确的，所以分配了bonus值1500，而第二个条件没有经过测试。在bonus被分配了价值之后,控制传递到DBMS_OUTPUT.PUT_LINE调用。

CREATE PROCEDURE p_3_1_4 (sales NUMBER) IS
  bonus NUMBER := 0;
  BEGIN
    IF sales > 50000 THEN
      bonus := 1500;
    ELSIF sales > 35000 THEN
      bonus := 500;
    ELSE
      bonus := 100;
    END IF;
    DBMS_OUTPUT.PUT_LINE ('Sales = ' || sales || ', bonus = ' || bonus || '.');
  END p;
/
  call p_3_1_4 (55000);
  call p_3_1_4 (40000);
  call p_3_1_4 (30000);

--Result:
--Sales = 55000, bonus = 1500.
--Sales = 40000, bonus = 500.
--Sales = 30000, bonus = 100.

★IF THEN ELSIF语句模拟简单的CASE语句

这个例子使用IF THEN ELSIF语句和许多ELSIF子句来比较单个值和许多可能的值。简单的CASE语句更清楚。

CREATE PROCEDURE p_3_1_5 AS
  grade CHAR(1);
BEGIN
  grade := 'B';
  IF grade = 'A' THEN
    DBMS_OUTPUT.PUT_LINE('Excellent');
  ELSIF grade = 'B' THEN
    DBMS_OUTPUT.PUT_LINE('Very Good');
  ELSIF grade = 'C' THEN
    DBMS_OUTPUT.PUT_LINE('Good');
  ELSIF grade = 'D' THEN
    DBMS_OUTPUT. PUT_LINE('Fair');
  ELSIF grade = 'F' THEN
    DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No such grade');
  END IF;
END;
/
--Result:
--Very Good

CASE语句

case语句可以使程序结构比较清晰。分为简单case语句和搜索式case语句两种。使用范围：

1. 当只有一个搜索条件时，计算选择简单case语句。Case关键字后会跟selector表达式，selector只计算一次，并和表达式的值进行比较。如果两者匹配，对应的case语句的statement会被执行。
2. 当有多个搜索条件时，需要选择搜索式case语句。注意，case关键字后面没有selector表达式，当特定条件满足时，会执行该条件相关的statement。

★简单的CASE语句

CREATE PROCEDURE p_3_1_6 AS
  grade CHAR(1);
BEGIN
  grade := 'B';
  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('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/
--Result:
--Very Good

★搜索CASE语句

CREATE PROCEDURE p_3_1_7 AS
  grade CHAR(1);
BEGIN
  grade := 'B';
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE
      DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/
--Result:
--Very Good

循环控制

PL/SQL用循环结构可以实现有规律的重复计算处理。当程序执行到循环控制语句时，

根据循环判定条件对一组语句重复执行多次。循环结构可以看成是一个条件判断语句和一个向回转向语句的组合。另外，循环结构的三个要素：循环变量、循环体和循环终止条件。循环结构在程序框图中是利用判断框来表示，判断框内写上条件，两个出口分别对应着条件成立和条件不成立时所执行的不同指令，其中一个要指向循环体，然后再从循环体回到判断框的入口处。

LOOP语句

★基本LOOP语句中的CONTINUE语句

CREATE PROCEDURE p_3_2_2 AS
  x NUMBER := 0;
BEGIN
  LOOP -- After CONTINUE statement, control resumes here
    DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
    x := x + 1;
    IF x < 3 THEN
      CONTINUE;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
    EXIT WHEN x = 5;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
END;
/
--Result:
--Inside loop: x = 0
--Inside loop: x = 1
--Inside loop: x = 2
--Inside loop, after CONTINUE: x = 3
--Inside loop: x = 3
--Inside loop, after CONTINUE: x = 4
--Inside loop: x = 4
--Inside loop, after CONTINUE: x = 5
--After loop: x = 5

★基本LOOP语句中的CONTINUE WHEN语句

CREATE PROCEDURE p_3_2_3 AS
  x NUMBER := 0;
BEGIN
  LOOP -- After CONTINUE statement, control resumes here
    DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
    x := x + 1;
    CONTINUE WHEN x < 3;
    DBMS_OUTPUT.PUT_LINE('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
    EXIT WHEN x = 5;
  END LOOP;
    DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
END;
/
--Result:
--Inside loop: x = 0
--Inside loop: x = 1
--Inside loop: x = 2
--Inside loop, after CONTINUE: x = 3
--Inside loop: x = 3
--Inside loop, after CONTINUE: x = 4
--Inside loop: x = 4
--Inside loop, after CONTINUE: x = 5
--After loop: x = 5

FOR LOOP语句

★基本LOOP语句

CREATE PROCEDURE p_3_2_4 AS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('lower_bound < upper_bound');
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE (i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('lower_bound = upper_bound');
  FOR i IN 2..2 LOOP
    DBMS_OUTPUT.PUT_LINE (i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('lower_bound > upper_bound');
  FOR i IN 3..1 LOOP
  DBMS_OUTPUT.PUT_LINE (i);
  END LOOP;
END;
/
--Result:
--lower_bound < upper_bound
--1
--2
--3
--lower_bound = upper_bound
--2
--lower_bound > upper_bound
--3
--2
--1

★反向FOR LOOP语句

CREATE PROCEDURE p_3_2_5 AS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('upper_bound > lower_bound');
  FOR i IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE (i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('upper_bound = lower_bound');
  FOR i IN REVERSE 2..2 LOOP
    DBMS_OUTPUT.PUT_LINE (i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('upper_bound < lower_bound');
  FOR i IN REVERSE 3..1 LOOP
    DBMS_OUTPUT.PUT_LINE (i);
  END LOOP;
END;
/
--Result:
--upper_bound > lower_bound
--3
--2
--1
--upper_bound = lower_bound
--2
--upper_bound < lower_bound
--1
--2
--3

★FOR LOOP语句中模拟STEP子句

CREATE PROCEDURE p_3_2_6 AS
  step PLS_INTEGER := 5;
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE (i*step);
  END LOOP;
END;
/
--Result:
--5
--10
--15

★FOR LOOP语句尝试更改索引值

CREATE PROCEDURE p_3_2_7 AS
BEGIN
  FOR i IN 1..3 LOOP
    IF i < 3 THEN
      DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
    ELSE
      i := 2;
    END IF;
  END LOOP;
END;
/
--Result:
--  660: Loop variable(i) cannot be modified.
--Error in line 7
--Near character position 6

★FOR LOOP语句索引的外部语句引用

CREATE PROCEDURE p_3_2_8 AS
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));
END;
/
--Result:
--  667: Variable(i) not declared.
--Error in line 6
--Near character position 57

★FOR LOOP语句索引与变量名称相同

CREATE PROCEDURE p_3_2_9 AS
  i NUMBER := 5;
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE ('Inside loop, i is ' || TO_CHAR(i));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('Outside loop, i is ' || TO_CHAR(i));
END;
/
--Result:
--Inside loop, i is 1
--Inside loop, i is 2
--Inside loop, i is 3
--Outside loop, i is 5

★FOR LOOP语句引用与索引相同名称的变量

CREATE PROCEDURE p_3_2_10 AS
BEGIN
<<main>> -- Label block.
DECLARE
  i NUMBER := 5;
BEGIN
  FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE ('local: ' || TO_CHAR(i) || ', global: ' || TO_CHAR(main.i) );
-- Qualify reference with block label.
  END LOOP;
END main;
END;
/
--Result:
--local: 1, global: 5
--local: 2, global: 5
--local: 3, global: 5

★具有相同的索引名称的嵌套FOR LOOP语句

CREATE PROCEDURE p_3_2_11 AS
BEGIN
  <<outer_loop>>
  FOR i IN 1..3 LOOP
    <<inner_loop>>
    FOR i IN 1..3 LOOP
      IF outer_loop.i = 2 THEN
        DBMS_OUTPUT.PUT_LINE('outer: ' || TO_CHAR(outer_loop.i) || ' inner: '|| TO_CHAR(inner_loop.i));
      END IF;
    END LOOP inner_loop;
  END LOOP outer_loop;
END;
/
--Result:
--outer: 2 inner: 1
--outer: 2 inner: 2
--outer: 2 inner: 3

★FOR LOOP语句边界

CREATE PROCEDURE p_3_2_12 AS
  first INTEGER := 1;
  last INTEGER := 10;
  high INTEGER := 100;
  low INTEGER := 12;
BEGIN
  -- Bounds are numeric literals:
  FOR j IN -5..5 LOOP
    NULL;
  END LOOP;
  -- Bounds are numeric variables:
  FOR k IN REVERSE first..last LOOP
    NULL;
  END LOOP;
  -- Lower bound is numeric literal,
  -- Upper bound is numeric expression:
  FOR step IN 0..(TRUNC(high/low) * 2) LOOP
    NULL;
  END LOOP;
END;
/

★在运行时指定FOR LOOP语句边界

DROP TABLE temp;
CREATE TABLE temp (
  emp_no NUMBER, email_addr VARCHAR2(50)
);

CREATE PROCEDURE p_3_2_13 AS
  emp_count NUMBER;
BEGIN
  SELECT COUNT(employee_id) INTO emp_count
  FROM employees;
  FOR i IN 1..emp_count LOOP
    INSERT INTO temp (emp_no, email_addr) VALUES(i, 'to be added later');
  END LOOP;
END;
/

★在FOR LOOP语句中的EXIT WHEN语句

CREATE PROCEDURE p_3_2_14 AS
  v_employees employees%ROWTYPE;
  CURSOR c1 is SELECT * FROM employees;
BEGIN
  OPEN c1;
  -- Fetch entire row into v_employees record:
  FOR i IN 1..10 LOOP
    FETCH c1 INTO v_employees;
    EXIT WHEN c1%NOTFOUND;
  -- Process data here
  END LOOP;
  CLOSE c1;
END;
/

★在内部FOR LOOP语句中的EXIT WHEN语句

CREATE PROCEDURE p_3_2_15 AS
  v_employees employees%ROWTYPE;
  CURSOR c1 is SELECT * FROM employees;
BEGIN
  OPEN c1;
  -- Fetch entire row into v_employees record:
  <<outer_loop>>
  FOR i IN 1..10 LOOP
    -- Process data here
    FOR j IN 1..10 LOOP
      FETCH c1 INTO v_employees;
      EXIT outer_loop WHEN c1%NOTFOUND;
      -- Process data here
    END LOOP;
  END LOOP outer_loop;
  CLOSE c1;
END;
/

★在内部FOR LOOP语句中的CONTINUE WHEN语句

CREATE PROCEDURE p_3_2_16 AS
  v_employees employees%ROWTYPE;
  CURSOR c1 is SELECT * FROM employees;
BEGIN
  OPEN c1;
  -- Fetch entire row into v_employees record:
  <<outer_loop>>
  FOR i IN 1..10 LOOP
    -- Process data here
    FOR j IN 1..10 LOOP
      FETCH c1 INTO v_employees;
      CONTINUE outer_loop WHEN c1%NOTFOUND;
      -- Process data here
    END LOOP;
  END LOOP outer_loop;
  CLOSE c1;
END;
/

WHILE LOOP语句

CREATE PROCEDURE p_3_2_17 AS
  done BOOLEAN := FALSE;
BEGIN
  WHILE done LOOP
    DBMS_OUTPUT.PUT_LINE ('This line does not print.');
    done := TRUE; -- This assignment is not made.
  END LOOP;
  WHILE NOT done LOOP
    DBMS_OUTPUT.PUT_LINE ('Hello, world!');
    done := TRUE;
  END LOOP;
END;
/
--Result:
--Hello, world!

顺序控制

NULL语句

★NULL 语句

程序体中的NULL 语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用NULL 主要是为了提高PL/SQL的可读性。

CREATE PROCEDURE p_3_3_5 AS
  v_job_id VARCHAR2(10);
  v_emp_id NUMBER(6) := 110;
BEGIN
  SELECT job_id INTO v_job_id
  FROM employees
  WHERE employee_id = v_emp_id;
  IF v_job_id = 'SA_REP' THEN
    UPDATE employees
    SET commission_pct = commission_pct * 1.2;
  ELSE
    NULL; -- Employee is not a sales rep
  END IF;
END;
/

★在子程序创建期间，NULL语句作为占位符

CREATE OR REPLACE PROCEDURE award_bonus (
  emp_id NUMBER, bonus NUMBER) AUTHID DEFINER AS
BEGIN -- Executable part starts here
  NULL; -- Placeholder(raises "unreachable code" if warnings enabled)
END award_bonus;
/

★简单CASE语句的ELSE子句中的NULL语句

CREATE OR REPLACE PROCEDURE print_grade (grade CHAR) AUTHID DEFINER AS
  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('Good');
      WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
      WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
      ELSE NULL;
    END CASE;
  END;
/
  call print_grade('A');
  call print_grade('S');

--Result:
--Excellent