Oracle PL/SQL/PL SQL/Boolean Expression

Commit or rollback based on success flag

 
SQL> CREATE TABLE emp (
  2    emp_id               NUMBER,
  3    ename             VARCHAR2(40),
  4    hire_date        DATE DEFAULT sysdate,
  5    end_date DATE,
  6    rate     NUMBER(5,2),
  7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
  8  );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL> DECLARE
  2    success_flag BOOLEAN;
  3  BEGIN
  4    BEGIN
  5      UPDATE emp
  6         SET rate = rate * 1.10;
  7      success_flag := TRUE;
  8    EXCEPTION
  9      WHEN OTHERS THEN
 10        success_flag := false;
 11    END;
 12
 13    IF success_flag THEN
 14      COMMIT;
 15    ELSE
 16      ROLLBACK;
 17      DBMS_OUTPUT.PUT_LINE("The UPDATE failed.");
 18    END IF;
 19  END;
 20  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>

Effects of nulls on boolean expressions: =

 
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  BEGIN
  5     -- Assign a value to the variable A, but leave N null.
  6     a := 2;
  7     -- Note that the test for A=N fails.
  8     IF a = n THEN
  9       DBMS_OUTPUT.PUT_LINE("a = n is true");
 10     ELSE
 11       DBMS_OUTPUT.PUT_LINE("a = n is not true");
 12     END IF;
 13  END;
 14  /
a = n is not true
PL/SQL procedure successfully completed.
SQL>

Effects of nulls on boolean expressions: >,

 
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  BEGIN
  5     -- Assign a value to the variable A, but leave N null.
  6     a := 2;
  7     -- But also note that the test for a <> n fails.
  8     IF a <> n THEN
  9       DBMS_OUTPUT.PUT_LINE("a <> n is true");
 10     ELSE
 11       DBMS_OUTPUT.PUT_LINE("a <> n is not true");
 12     END IF;
 13
 14  END;
 15  /
a <> n is not true
PL/SQL procedure successfully completed.
SQL>

Use and to link two boolean expressions

 
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  BEGIN
  5     -- Assign a value to the variable A, but leave N null.
  6     a := 2;
  7     -- TRUE and NULL = NULL
  8     IF (a = 2) AND (a <> n) THEN
  9       DBMS_OUTPUT.PUT_LINE("TRUE and NULL = TRUE");
 10     ELSE
 11       DBMS_OUTPUT.PUT_LINE("TRUE and NULL = NULL");
 12     END IF;
 13  END;
 14  /
TRUE and NULL = NULL
PL/SQL procedure successfully completed.
SQL>