Oracle PL/SQL/Data Type/Boolean — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 12:58, 26 мая 2010
Содержание
- 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>