More on IF statements
The following table is being used to do an embedded IF statement test. This code has
embedded IF statements within IF statements. Probably the best way to understand the code
is to draw a flowchart and analyze it.In this test, the idno is 12121 which means RI and
greater than 250, therefore the yrgoal is increaed by 25% to 550 as shown below.
SQL CODE:
SQL> SELECT * FROM donornew;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 440 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 158.4 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa
6 rows selected.
SQL> edit donor2
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) :=&input_idno;
v_yrgoal NUMBER(7,2);
v_newgoal NUMBER(7,2);
v_state VARCHAR2(2);
BEGIN
SELECT yrgoal, state INTO v_yrgoal, v_state
FROM donornew
WHERE idno = v_idno;
IF v_state = 'MA' THEN
IF v_yrgoal > 300 THEN
v_newgoal := v_yrgoal * 1.3;
ELSE
IF v_yrgoal > 100 THEN
v_newgoal := v_yrgoal * 1.2;
ELSE
v_newgoal := v_yrgoal * 1.1;
END IF;
END IF;
ELSE
IF v_state = 'RI' THEN
IF v_yrgoal > 250 THEN
v_newgoal := v_yrgoal * 1.25;
ELSE
v_newgoal := v_yrgoal * 1.15;
END IF;
ELSE
v_newgoal := v_yrgoal;
END IF;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
COMMIT;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ donor2
Enter value for input_idno: 12121
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM donornew;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 550 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 158.4 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa
6 rows selected.
The IF statement in the code below uses an embedded AND.
SQL CODE:
SQL> edit donor3
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) :=&input_idno;
v_yrgoal NUMBER(7,2);
v_newgoal NUMBER(7,2);
v_state VARCHAR2(2);
BEGIN
SELECT yrgoal, state INTO v_yrgoal, v_state
FROM donornew
WHERE idno = v_idno;
IF v_yrgoal > 250 AND v_state = 'MA' THEN
v_newgoal := v_yrgoal * 1.1 ;
ELSE
v_newgoal := v_yrgoal * 1.2;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ donor3
Enter value for input_idno: 23456
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM donornew;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 550 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa
6 rows selected.
The code in donor3a has an embedded OR which asks if state = MA or yrgoal > 250. There is
an else that handles all other conditions. The two examples below meet the criteria and
are therefore processed with a 10% increase.
SQL CODE:
SQL> edit donor3a
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) :=&input_idno;
v_yrgoal NUMBER(7,2);
v_newgoal NUMBER(7,2);
v_state VARCHAR2(2);
BEGIN
SELECT yrgoal, state INTO v_yrgoal, v_state
FROM donornew
WHERE idno = v_idno;
IF v_yrgoal > 250 OR v_state = 'MA' THEN
v_newgoal := v_yrgoal * 1.1 ;
ELSE
v_newgoal := v_yrgoal * 1.2;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ donor3a
Enter value for input_idno: 12121
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> @ donor3a
Enter value for input_idno: 33333
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM donornew;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 55 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa
6 rows selected.
The following code uses both AND and OR. It follows the normal conventions of AND gets
resolved before OR. Therefore, condA AND condB or condC would be resolved as condA and
condB must be true or just condC must be true. If I want the resolution to be condA must
be true and either condB or condC must be true, I need to use parenthesis to change the
order of operation so the OR will be resolved before the AND. In the example below I am
asking for state = MA and either yrgoal less than 100 or datefst greater than
January 1, 1998. The first entry is for 33333 who has a state of MA and has a yrgoal
less than 100 but is not before January 1, 1998. Since the yrgoal and the date are either
OR, person 33333 meets the criteria and the change is made. The second entry is 12121 who
is RI so is immediately disqualified. The third entry is for 23456 who is MA but neither
the yrgoal nor the date meet the OR criteria so no change is made.
SQL CODE:
SQL> edit donor3b
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) :=&input_idno;
v_yrgoal donornew.yrgoal%TYPE;
v_newgoal donornew.yrgoal%TYPE;
v_state donornew.state%TYPE;
v_datefst donornew.datefst%TYPE;
BEGIN
SELECT yrgoal, state, datefst INTO v_yrgoal, v_state, v_datefst
FROM donornew
WHERE idno = v_idno;
IF v_state = 'MA' AND (v_yrgoal < 100 OR v_datefst > '01-JAN-98') THEN
v_newgoal := v_yrgoal * 1.1 ;
ELSE
v_newgoal := v_yrgoal;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ donor3b
Enter value for input_idno: 33333
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> @ donor3b
Enter value for input_idno: 12121
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> @ donor3b
Enter value for input_idno: 23456
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM donornew;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 60.5 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa
6 rows selected.
In this example, I have eliminated the parenthesis, so the AND is resolved before the OR.
This means it will look for state of MA and yrgoal < 100 or simply the datefst greater than
January 1, 1998. In testing it I entered 12121 which meets MA but not the yrgoal and not
the standalone date, so no change is made. I then entered 33333 which meets MA and the
yrgoal so the change is made.
SQL CODE:
SQL> edit donor3c
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) :=&input_idno;
v_yrgoal donornew.yrgoal%TYPE;
v_newgoal donornew.yrgoal%TYPE;
v_state donornew.state%TYPE;
v_datefst donornew.datefst%TYPE;
BEGIN
SELECT yrgoal, state, datefst INTO v_yrgoal, v_state, v_datefst
FROM donornew
WHERE idno = v_idno;
IF v_state = 'MA' AND v_yrgoal < 100 OR v_datefst > '01-JAN-98' THEN
v_newgoal := v_yrgoal * 1.1 ;
ELSE
v_newgoal := v_yrgoal;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ donor3c
Enter value for input_idno: 12121
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> @ donor3c
Enter value for input_idno: 33333
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM donornew;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 66.55 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa
6 rows selected.
Remember that you can check NULL operator with the IS NULL and that any expression that
contains a NULL operator evaluates to NULL. In the example below if the state is RI and
the yrgoal is either below 100 or NULL, the yrgoal gets reset to 100. Record 22222 is from
RI and has NULL as the yrgoal so the change gets made and yrgoal is reset to 100.
SQL CODE:
SQL> edit donor3d
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) :=&input_idno;
v_yrgoal donornew.yrgoal%TYPE;
v_newgoal donornew.yrgoal%TYPE;
v_state donornew.state%TYPE;
BEGIN
SELECT yrgoal, state INTO v_yrgoal, v_state
FROM donornew
WHERE idno = v_idno;
IF v_state = 'RI' AND (v_yrgoal < 100 OR v_yrgoal IS NULL) THEN
v_newgoal := 100;
ELSE
v_newgoal := v_yrgoal;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ donor3d
Enter value for input_idno: 22222
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM donornew;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- -----------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 845 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 605 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 100 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 190.08 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 66.55 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 63.53 Amy Costa
6 rows selected.