Oracle PL/SQL Tutorial/PL SQL Data Types/Boolean

Материал из SQL эксперт
Перейти к: навигация, поиск

BOOLEAN

The BOOLEAN datatype is used to store true/false values.

Its range is only the two values, true and false.

The Syntax for the BOOLEAN Datatype



   <source lang="sql">

variable_name BOOLEAN;</source>


Boolean literals

   <source lang="sql">

SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2     v_true BOOLEAN := TRUE;
 3     v_false BOOLEAN := FALSE;
 4     v_null BOOLEAN := NULL;
 5  BEGIN
 6     IF v_true
 7     THEN
 8        DBMS_OUTPUT.PUT_LINE("true");
 9     END IF;
10
11     IF v_false
12     THEN
13        DBMS_OUTPUT.PUT_LINE("false");
14     END IF;
15
16     IF v_null
17     THEN
18        DBMS_OUTPUT.PUT_LINE("null");
19     END IF;
20  END;
21  /

true PL/SQL procedure successfully completed. SQL></source>


Boolean type parameter

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE plsb (str IN VARCHAR2, bool IN BOOLEAN)

 2  IS
 3  BEGIN
 4     IF bool
 5     THEN
 6        DBMS_OUTPUT.PUT_LINE (str || " - TRUE");
 7     ELSIF NOT bool
 8     THEN
 9        DBMS_OUTPUT.PUT_LINE (str || " - FALSE");
10     ELSE
11        DBMS_OUTPUT.PUT_LINE (str || " - NULL");
12     END IF;
13  END plsb;
14  /

SP2-0804: Procedure created with compilation warnings</source>


Boolean variables can be assigned either directly by using values TRUE, FALSE, or NULL or as the results of logical expressions

   <source lang="sql">

SQL> SQL> SQL> declare

 2      v_b BOOLEAN:=false;
 3  begin
 4      v_b:=extract(year from sysdate)>2000;
 5      if v_b then
 6          DBMS_OUTPUT.put_line("21st Century!");
 7      end if;
 8  end;
 9  /

21st Century! PL/SQL procedure successfully completed. SQL></source>


Demonstrates the effects of NULLs on Boolean expressions

   <source lang="sql">

SQL> DECLARE

 2     x NUMBER := NULL;
 3  BEGIN
 4     IF x = 2 THEN
 5        DBMS_OUTPUT.PUT_LINE("x contains 2");
 6     ELSE
 7        DBMS_OUTPUT.PUT_LINE("x doesn""t contain 2");
 8     END IF;
 9
10     IF x <> 2 THEN
11        DBMS_OUTPUT.PUT_LINE("x doesn""t contain 2");
12     ELSE
13        DBMS_OUTPUT.PUT_LINE("x contains 2");
14     END IF;
15  END;
16  /

PL/SQL procedure successfully completed. SQL> SQL></source>


Exit when a boolean conditioin

   <source lang="sql">

SQL> SQL> DECLARE

 2    boolean_condition  BOOLEAN := TRUE;
 3  BEGIN
 4    LOOP
 5      -- ... body of loop ...
 6      EXIT WHEN boolean_condition;
 7    END LOOP;
 8  END;
 9  /

PL/SQL procedure successfully completed. SQL> SQL></source>


How to assign a TRUE value to a variable of type boolean

   <source lang="sql">

SQL> SQL> DECLARE

 2     v_boolean BOOLEAN;
 3  BEGIN
 4     v_boolean := TRUE;
 5  END;
 6  /

PL/SQL procedure successfully completed.</source>


Logic operator and boolean value

   <source lang="sql">

SQL> SQL> SQL> DECLARE

 2    end_of_file1  BOOLEAN := TRUE;
 3    end_of_file2  BOOLEAN := FALSE;
 4    checkline  VARCHAR2(80) := "1234";
 5    againstline  VARCHAR2(80) := "abcd";
 6    retval  BOOLEAN;
 7  BEGIN
 8    LOOP
 9      -- ...
10      IF (end_of_file1 AND end_of_file2)
11      THEN
12        retval := TRUE;
13        EXIT;
14      ELSIF (checkline != againstline)
15      THEN
16        retval := FALSE;
17        EXIT;
18      ELSIF (end_of_file1 OR end_of_file2)
19      THEN
20        retval := FALSE;
21        EXIT;
22      END IF;
23    END LOOP;
24  END;
25  /

PL/SQL procedure successfully completed. SQL> SQL></source>


Use "and" to connect two boolean expressions

   <source lang="sql">

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    age   POSITIVE;
 3
 4    current_year  NATURAL;    --a year of 00 is valid.
 5    current_month POSITIVE;
 6    current_day   POSITIVE;
 7
 8    birth_year    NATURAL;    --a year of 00 is valid.
 9    birth_month   POSITIVE;
10    birth_day     POSITIVE;
11
12  BEGIN
13
14    current_year := 2121;
15    current_month := 12;
16    current_day := 13;
17
18
19    birth_year := 1224;
20    birth_month := 12;
21    birth_day := 12;
22
23    IF current_month > birth_month THEN
24      age := current_year - birth_year;
25    ELSIF (current_month = birth_month) and (current_day >= birth_day) THEN
26      age := current_year - birth_year;
27    ELSE
28      age := current_year - birth_year - 1;
29    END IF;
30  END;
31  /

SQL></source>


Use BOOLEAN type variable

   <source lang="sql">

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2    error_flag  BOOLEAN := false;
 3
 4  BEGIN
 5    DBMS_OUTPUT.PUT_LINE("We are going to count from 100 to 1000.");
 6
 7    DECLARE
 8      hundreds_counter  NUMBER(1,-2);
 9    BEGIN
10      hundreds_counter := 100;
11      LOOP
12        DBMS_OUTPUT.PUT_LINE(hundreds_counter);
13        hundreds_counter := hundreds_counter + 100;
14        IF hundreds_counter > 1000 THEN
15          EXIT;
16        END IF;
17       END LOOP;
18    EXCEPTION
19    WHEN OTHERS THEN
20      error_flag := true;
21    END;
22
23    IF error_flag THEN
24      DBMS_OUTPUT.PUT_LINE("Sorry, I cannot count that high.");
25     ELSE
26      DBMS_OUTPUT.PUT_LINE("Done.");
27    END IF;
28  END;
29  /

We are going to count from 100 to 1000. 100 200 300 400 500 600 700 800 900 Sorry, I cannot count that high. PL/SQL procedure successfully completed. SQL> SQL></source>