Oracle PL/SQL/Data Type/Boolean
Содержание
- 1 Assign a value to a variable of type boolean. Don"t use quotes around the value like this
- 2 Boolean data type with If statement
- 3 Boolean expressions in where clause
- 4 Boolean literals
- 5 Define and use boolean value
- 6 PL/SQL BOOLEAN datatypes accept only TRUE, FALSE, NULL
- 7 Use the NVL() against a non-initialized BOOLEAN variable:
Assign a value to a variable of type boolean. Don"t use quotes around the value like this
SQL>
SQL> DECLARE
2 v_boolean BOOLEAN;
3 BEGIN
4 v_boolean := "TRUE";
5 END;
6 /
v_boolean := "TRUE";
*
ERROR at line 4:
ORA-04045: errors during recompilation/revalidation of sqle.LOG_ERRORS
ORA-01031: insufficient privileges
ORA-06550: line 4, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
SQL>
SQL>
SQL> -- Instead, do this
SQL>
SQL> DECLARE
2 v_boolean BOOLEAN;
3 BEGIN
4 v_boolean := TRUE;
5 END;
6 /
PL/SQL procedure successfully completed.
Boolean data type with If statement
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> <<default_test>>
2 DECLARE
3 lv_first_num NUMBER(10) DEFAULT 0; -- Defaulted to 0
4 lv_second_num NUMBER(10) := 10; -- Defaulted to 10
5 lv_third_num NUMBER(10); -- Defaulted to NULL
6 lv_processed_bln BOOLEAN DEFAULT FALSE; -- Defaulted to FALSE
7 lv_complete_bln1 BOOLEAN; -- Defaulted to NULL
8 lv_complete_bln2 BOOLEAN; -- Defaulted to NULL
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE("lv_first_num: " ||
11 TO_CHAR(lv_first_num) ||
12 CHR(10) || "lv_second_num: " ||
13 TO_CHAR(lv_second_num) ||
14 CHR(10) || "lv_third_num: " ||
15 TO_CHAR(lv_third_num) ||
16 CHR(10) || "lv_processed_bln: " || "FALSE" ||
17 CHR(10) || "lv_complete_bln1: " || "" ||
18 CHR(10) || "lv_complete_bln2: " || "" ||
19 CHR(10) ||
20 "default_test.lv_second_num: " ||
21 TO_CHAR(default_test.lv_second_num) || CHR(10));
22 DBMS_OUTPUT.PUT_LINE("Is lv_second_num > lv_third_num?");
23 IF lv_second_num > lv_third_num THEN
24 DBMS_OUTPUT.PUT_LINE("lv_second_num > lv_third_num" || CHR(10));
25 ELSE
26 DBMS_OUTPUT.PUT_LINE("lv_second_num < lv_third_num" || CHR(10));
27 END IF;
28 DBMS_OUTPUT.PUT_LINE("Is lv_first_num = lv_third_num?");
29 IF lv_first_num = lv_third_num THEN
30 DBMS_OUTPUT.PUT_LINE("lv_first_num = lv_third_num");
31 ELSE
32 DBMS_OUTPUT.PUT_LINE("lv_first_num <> lv_third_num");
33 END IF;
34 DBMS_OUTPUT.PUT_LINE("Is lv_complete_bln1 = TRUE?");
35 IF lv_complete_bln1 THEN
36 DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 = TRUE");
37 ELSE
38 DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 <> TRUE");
39 END IF;
40 DBMS_OUTPUT.PUT_LINE("Is NOT lv_complete_bln1 = TRUE?");
41 IF NOT lv_complete_bln1 THEN
42 DBMS_OUTPUT.PUT_LINE("NOT lv_complete_bln1 = TRUE");
43 ELSE
44 DBMS_OUTPUT.PUT_LINE("NOT lv_complete_bln1 <> TRUE");
45 END IF;
46 DBMS_OUTPUT.PUT_LINE("Is lv_complete_bln1 = lv_complet_bln2?");
47 IF lv_complete_bln1 = lv_complete_bln2 THEN
48 DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 = lv_complete_bln2");
49 ELSE
50 DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 <> lv_complete_bln2");
51 END IF;
52 END default_test;
53 /
lv_first_num: 0
lv_second_num: 10
lv_third_num:
lv_processed_bln: FALSE
lv_complete_bln1:
lv_complete_bln2:
default_test.lv_second_num: 10
Is lv_second_num > lv_third_num?
lv_second_num < lv_third_num
Is lv_first_num = lv_third_num?
lv_first_num <> lv_third_num
Is lv_complete_bln1 = TRUE?
lv_complete_bln1 <> TRUE
Is NOT lv_complete_bln1 = TRUE?
NOT lv_complete_bln1 <> TRUE
Is lv_complete_bln1 = lv_complet_bln2?
lv_complete_bln1 <> lv_complete_bln2
PL/SQL procedure successfully completed.
SQL>
Boolean expressions in where clause
SQL>
SQL> create table gift(
2 gift_id integer primary key
3 ,emp_id integer
4 ,register_date date not null
5 ,total_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,message varchar2(100)
12 );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL>
SQL> select gift_id, total_price, payment
2 from gift
3 where (payment = "CA" OR payment = "VS" OR payment = "VG")
4 AND total_price > 15;
GIFT_ID TOTAL_PRICE PA
---------- ----------- --
1 123.12 CA
2 50.98 CA
3 35.99 VS
4 19.95 CA
4 rows selected.
SQL>
SQL> drop table gift;
Table dropped.
Boolean literals
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
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 IF v_false
11 THEN
12 DBMS_OUTPUT.PUT_LINE("false");
13 END IF;
14 IF v_null
15 THEN
16 DBMS_OUTPUT.PUT_LINE("null");
17 END IF;
18 END;
19 /
true
PL/SQL procedure successfully completed.
SQL>
Define and use boolean value
SQL>
SQL> -- Effects of nulls on boolean expressions.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 a INTEGER;
3 n INTEGER; -- be our null value.
4 b BOOLEAN;
5 BEGIN
6 -- Assign a value to the variable A, but leave N null.
7 a := 2;
8 b := (a <> n);
9
10 IF b THEN
11 DBMS_OUTPUT.PUT_LINE("a <> n is TRUE");
12 ELSIF NOT b THEN
13 DBMS_OUTPUT.PUT_LINE("a <> n is FALSE");
14 ELSE
15 DBMS_OUTPUT.PUT_LINE("a <> n is NULL");
16 END IF;
17 END;
18 /
a <> n is NULL
PL/SQL procedure successfully completed.
SQL>
PL/SQL BOOLEAN datatypes accept only TRUE, FALSE, NULL
SQL> -- Boolean datatypes
SQL>
SQL> -- PL/SQL BOOLEAN datatypes accept only TRUE, FALSE, NULL.
SQL>
SQL> DECLARE
2 x NUMBER := 10;
3 y NUMBER := 20;
4 v_flag BOOLEAN;
5 BEGIN
6 v_flag := (x > y);
7
8 DBMS_OUTPUT.PUT_LINE(v_flag);
9 END;
10 /
DBMS_OUTPUT.PUT_LINE(v_flag);
*
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00306: wrong number or types of arguments in call to "PUT_LINE"
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored
Use the NVL() against a non-initialized BOOLEAN variable:
SQL> DECLARE
2
3 my_var BOOLEAN;
4 BEGIN
5
6 IF NOT NVL(my_var,FALSE) THEN
7 dbms_output.put_line("This should happen!");
8 ELSE
9 dbms_output.put_line("This can""t happen!");
10 END IF;
11 END;
12 /
This should happen!
PL/SQL procedure successfully completed.
SQL>