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
<source lang="sql">
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.
</source>
Boolean data type with If statement
<source lang="sql">
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>
</source>
Boolean expressions in where clause
<source lang="sql">
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.
</source>
Boolean literals
<source lang="sql">
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>
</source>
Define and use boolean value
<source lang="sql">
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>
</source>
PL/SQL BOOLEAN datatypes accept only TRUE, FALSE, NULL
<source lang="sql">
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
</source>
Use the NVL() against a non-initialized BOOLEAN variable:
<source lang="sql">
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>
</source>
