PL/SQL - Using IF statements
There are a variety of IF statement uses that can be used in PL/SQL. See the tutorials I
suggested or the books you bought for the syntax. In the first example, I am using a
simple IF statement. Since I am planning to modify data in the file, I decided to make a
new copy of donor and call it new donor.
SQL CODE:
SQL> create table donornew as select * from donor;
Table created.
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 500 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 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 120 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 50 Amy Costa
6 rows selected.
SQL> DESC donornew;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(5)
NAME VARCHAR2(15)
STADR VARCHAR2(15)
CITY VARCHAR2(10)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)
DATEFST DATE
YRGOAL NUMBER(7,2)
CONTACT VARCHAR2(12)
Now I am doing to write the code donor0. I issue the command edit donor0 which will bring
up the editor and give the text entered the name donor0.SQL. Within the code, first I am
setting verify OFF so that when I input I will not get the old/new feedback. Next I am
declaring to variables v_idno and v_yrgoal. The VARCHAR2 and NUMBER used match the idno
and yrgoal fields in the table donornew. Because I gave the field v_idno an initial value
that uses the & for data input, when donor0 is run, the user will be prompted to key in an
idno.
The first part of the BEGIN is the SELECT statement to put the yrgoal on the record where
the idno in the table matches the idno that was inputed into v_idno into v_yrgoal.
The next code is a simple IF that asks if v_yrgoal is greater than 250. If it is, then
v_yrgoal is multiplied by 1.1 to increase the goal by 10%. Notice that if v_yrgoal is not
greater than 250, there is no activity. The last step in the processing part of the block
is to UODATE the record on the table that matches v_idno by putting v_yrgoal in as yrgoal.
If v_yrgoal was changed in the IF, there will be a new value in the field/column for that
row. If no calculation was done, v_yrgoal remained the same and is simply put back.
Notice the structure which has IF condition THEN and a semi-colon after the processing and
an END IF followed by a semi-colon to terminate the IF. The COMMIT is optional, I put it
in to commit the processing that I completed.
SQL CODE:
SQL> edit donor0
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) := &input_idno;
v_yrgoal NUMBER(7,2);
BEGIN
SELECT yrgoal INTO v_yrgoal
FROM donornew
WHERE idno = v_idno;
IF v_yrgoal > 250 THEN
v_yrgoal := v_yrgoal * 1.1;
END IF;
UPDATE donornew
SET yrgoal = v_yrgoal
WHERE idno = v_idno;
COMMIT;
END;
/
SET VERIFY ON
To execute the code, I can either enter START donor0 or @ donor0. When prompted for the
idno, I entered 12121. The processing was done and the successful message was returned.
To verify the processing I then did a select to show the file and indeed record 12121 was
increased from 400 to 440.
SQL CODE:
SQL> @ donor0
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 500 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 120 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 50 Amy Costa
6 rows selected.
I then reran the PL/SQL code and input an id where the goal was not > 250. As you can see,
no change was made to the table.
SQL CODE:
SQL> @ donor0
Enter value for input_idno: 34567
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 500 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 120 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 50 Amy Costa
6 rows selected.
I now when in and modified the code to put an ELSE clause in. When the v_yrgoal is greater
than 250 I will increase the goal by 10% otherwise I will increase the goal by 5%. In
looking at the code, notice that the processing that takes place in the IF is followed by a
; and the processing that takes place in the ELSE is followed by a ;. Also remember the
structure which has the IF condition THEN and the terminating END IF;.
SQL CODE:
SQL> edit donor0a
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) := &input_idno;
v_yrgoal NUMBER(7,2);
BEGIN
SELECT yrgoal INTO v_yrgoal
FROM donornew
WHERE idno = v_idno;
IF v_yrgoal > 250 THEN
v_yrgoal := v_yrgoal * 1.1;
ELSE
v_yrgoal := v_yrgoal * 1.05;
END IF;
UPDATE donornew
SET yrgoal = v_yrgoal
WHERE idno = v_idno;
COMMIT;
END;
/
SET VERIFY ON
I then verified the change by entering the idno for a person that had a goal less than >250
and the goal was upped by 5%.
SQL CODE:
SQL> @ donor0a
Enter value for input_idno: 34567
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 500 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 120 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 52.5 Amy Costa
6 rows selected.
Now I am including the code IFELSE. If the goal is greater than 300 then I want to up in
by 30%, if greater than 200 I want to up it by 20%, if greater than 100 I want to up it by
10% and everything else I want to up by 5%. This is done with ELSE followed by the next IF.
At the end, all of the IF statements are closed down with the END IF.
SQL CODE:
SQL> edit donor0b
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) := &input_idno;
v_yrgoal NUMBER(7,2);
BEGIN
SELECT yrgoal INTO v_yrgoal
FROM donornew
WHERE idno = v_idno;
IF v_yrgoal > 300 THEN
v_yrgoal := v_yrgoal * 1.3;
ELSE
IF v_yrgoal > 200 THEN
v_yrgoal := v_yrgoal * 1.2;
ELSE
IF v_yrgoal > 100 THEN
v_yrgoal := v_yrgoal * 1.1;
ELSE
v_yrgoal := v_yrgoal * 1.05;
END IF;
END IF;
END IF;
UPDATE donornew
SET yrgoal = v_yrgoal
WHERE idno = v_idno;
COMMIT;
END;
/
SET VERIFY ON
To test this code I first put in idno 23456 which had a yrgoal that was > 100 and it was
increased by 10%. Then I put in idno 11111 which had a yrgoal greater than 300 and it was
increased by 30%.
SQL CODE:
SQL> @ donor0b
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 500 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 132 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 52.5 Amy Costa
6 rows selected.
SQL> @ donor0b
Enter value for input_idno: 11111
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 650 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 132 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 52.5 Amy Costa
Now I am going to test the IF..ELSIF which works like a case structure. IF the first
condition is true it is executed. Otherwise the second condition is tested etc. The final
ELSE is the catch-all for anything that did not meet one of the previous conditions.
Notice that in this example, instead of assigning the yrgoal back to itself, I introduced a
new variable called v_newgoal where I put the result of the calculation. This is simply
another approach. If the yrgoal is less than a 100 it is increased by 10% and the if is
exited. If not then yrgoal is checked to see if it is less than 250, if it is then it is
increased by 20% and the if is exited. Anything else falls through to the else and is
increased by 30%.
SQL CODE:
SQL> edit donor1
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno VARCHAR2(5) :=&input_idno;
v_yrgoal NUMBER(7,2);
v_newgoal NUMBER(7,2);
BEGIN
SELECT yrgoal INTO v_yrgoal
FROM donornew
WHERE idno = v_idno;
IF v_yrgoal < 100 THEN
v_newgoal := v_yrgoal * 1.1;
ELSIF v_yrgoal < 250 THEN
v_newgoal := v_yrgoal * 1.2 ;
ELSE
v_newgoal := v_yrgoal * 1.3;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
COMMIT;
END;
/
SET VERIFY ON
This code was tested with idno 34567 which should result in a 10 % increase and 23456 which
should result in a 20% increase.
SQL CODE:
SQL> @ donor1
Enter value for input_idno: 34567
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 650 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 132 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 57.75 Amy Costa
6 rows selected.
SQL> @ donor1
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 650 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 57.75 Amy Costa
6 rows selected.
When you define things in the declare section, you can use the %TYPE to take the type and
length from the definition in the table. For example instead of saying VARCHAR2(5) for
v_idno, I can say donornew.idno%TYPE. It will then use the VARCHAR2(5) as the length and
type. Note that donornew defines the table and idno defines the column. This means I
don't have to keep checking the structure of the table to determine size and type.
SQL CODE:
SQL> edit donor1a
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno donornew.idno%TYPE :=&input_idno;
v_yrgoal donornew.yrgoal%TYPE;
v_newgoal donornew.yrgoal%TYPE;
BEGIN
SELECT yrgoal INTO v_yrgoal
FROM donornew
WHERE idno = v_idno;
IF v_yrgoal < 100 THEN
v_newgoal := v_yrgoal * 1.1;
ELSIF v_yrgoal < 250 THEN
v_newgoal := v_yrgoal * 1.2 ;
ELSE
v_newgoal := v_yrgoal * 1.3;
END IF;
UPDATE donornew
SET yrgoal = v_newgoal
WHERE idno = v_idno;
COMMIT;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ donor1a
Enter value for input_idno: 34567
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 650 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.