Oracle PL/SQL Tutorial/PL SQL Data Types/Boolean
Содержание
- 1 BOOLEAN
- 2 Boolean literals
- 3 Boolean type parameter
- 4 Boolean variables can be assigned either directly by using values TRUE, FALSE, or NULL or as the results of logical expressions
- 5 Demonstrates the effects of NULLs on Boolean expressions
- 6 Exit when a boolean conditioin
- 7 How to assign a TRUE value to a variable of type boolean
- 8 Logic operator and boolean value
- 9 Use "and" to connect two boolean expressions
- 10 Use BOOLEAN type variable
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>