Условные операторы и переходы (Conditional and Sequential Control)

[Marley]

IF THEN END IF;
IF THEN ELSE END IF;
IF THEN ELSIF ELSE END IF;

[Marley]

IF condition
THEN
... sequence of executable statements ...
END IF;

Пример:

IF salary > 40000
THEN
give_bonus (employee_id,500);
END IF;

[Marley]

IF condition
THEN
... TRUE sequence of executable statements ...
ELSE
... FALSE/NULL sequence of executable statements ...
END IF;

Пример:

IF salary <= 40000
   THEN
      give_bonus (employee_id, 0);
   ELSE
      give_bonus (employee_id, 500);
END IF;

Если возможно что salary NULL, лучше использовать:

IF NVL(salary,0) <= 40000
  THEN
       give_bonus (employee_id, 0);
   ELSE
       give_bonus (employee_id, 500);
END IF;

[Marley]

IF condition-1
   THEN
      statements-1

ELSIF condition-N
   THEN
      statements-N

[ELSE
      else_statements]

END IF;

Пример:

IF salary BETWEEN 10000 AND 20000
THEN
      give_bonus(employee_id, 1500);

ELSIF salary BETWEEN 20000 AND 40000
THEN
      give_bonus(employee_id, 1000);

ELSIF salary > 40000
THEN
      give_bonus(employee_id, 500);

ELSE
      give_bonus(employee_id, 0);
END IF;

[Marley]

CASE expression
WHEN result1 THEN
statements1

WHEN result2 THEN
statements2

...

ELSE
statements_else

END CASE;

Пример:

CASE TRUE
WHEN salary >= 10000 AND salary <=20000
THEN
give_bonus(employee_id, 1500);

WHEN salary > 20000 AND salary <= 40000
THEN
give_bonus(employee_id, 1000);

WHEN salary > 40000
THEN
give_bonus(employee_id, 500);

ELSE
give_bonus(employee_id, 0);

END CASE;

DECLARE

boolean_true BOOLEAN := TRUE;
boolean_false BOOLEAN := FALSE;
boolean_null BOOLEAN;

FUNCTION boolean_to_varchar2 (flag IN BOOLEAN) RETURN VARCHAR2 IS
   BEGIN
      RETURN 
         CASE flag
            WHEN TRUE THEN 'True'
            WHEN FALSE THEN 'False'
         
           ELSE 'NULL'
         END;
    END;

BEGIN

   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true));
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false));
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null));

END;