PL学习-Control

DECLARE
  PROCEDURE p (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;
BEGIN
  p(55000);
  p(40000);
  p(30000);
END;
/

DECLARE
  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;
/

DECLARE
  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;
/

DECLARE
  s  PLS_INTEGER := 0;
  i  PLS_INTEGER := 0;
  j  PLS_INTEGER;
BEGIN
  <<outer_loop>>
  LOOP
    i := i + 1;
    j := 0;
    <<inner_loop>>
    LOOP
      j := j + 1;
      s := s + i * j; -- Sum several products
      EXIT inner_loop WHEN (j > 5);
      EXIT outer_loop WHEN ((i * j) > 15);
    END LOOP inner_loop;
  END LOOP outer_loop;
  DBMS_OUTPUT.PUT_LINE
    (‘The sum of products equals: ‘ || TO_CHAR(s));
END;
/

[ label ] WHILE condition LOOP
  statements
END LOOP [ label ];

DECLARE
  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;
/

[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
  statements
END LOOP [ label ];

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;
/

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
  TYPE NumList IS VARRAY(10) OF NUMBER;
  depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN
  FORALL j IN 4..7
    DELETE FROM employees_temp WHERE department_id = depts(j);
END;
/

DECLARE
  done  BOOLEAN;
BEGIN
  FOR i IN 1..50 LOOP
    IF done THEN
      GOTO end_loop;
    END IF;
    <<end_loop>>
    NULL;  ----- 一定要有null;语句
  END LOOP;
END;
/