More on Procedures
This is an internal procedure that has been embedded in the DECLARE portion of the PL/SQL
block. When the call is made from the block BEGIN, the declared procedure is executed.
Notice the way the execution works. The block BEGIN is executed. If v_contamt is greater
than 20 then the AddDonProc is executed and in the process the input stored in v_idno,
v_driveno, and v_contamt are sent to the procedure where they are stored as p_idno,
p_driveno and p_contamt. The procedure is executed and the passed values are inserted into
the new record that is written on the new_donation table in the fields idno, driveno,
contamt.
SQL CODE:
SQL> edit Call_Adddonproc1
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno new_donation.idno%TYPE :='&input_idno';
v_driveno new_donation.driveno%TYPE :='&input_driveno';
v_contamt new_donation.contamt%TYPE :=&input_contamt;
PROCEDURE AddDonProc
(p_idno new_donation.idno%TYPE,
p_driveno new_donation.driveno%TYPE,
p_contamt new_donation.contamt%TYPE) AS
BEGIN
INSERT INTO new_donation(idno, driveno, contamt)
VALUES(p_idno, p_driveno, p_contamt);
END AddDonProc;
BEGIN
IF v_contamt > 20 THEN
AddDonProc (v_idno, v_driveno, v_contamt);
END IF;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ Call_Adddonproc1
Enter value for input_idno: 12121
Enter value for input_driveno: 100
Enter value for input_contamt: 100
PL/SQL procedure successfully completed.
SQL> SELECT * FROM new_donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
23456 300 14-JUN-99 10
12121 300 10-JUN-99 75
12121 100 500
11111 100 7777
12121 100 100
Notice that this code does not use the contamt in the INSERT, so it is blank in the tests
that I ran.
In the next example, I decided to take the input contribution and up in by 10% and add that
to the file - in other words I guess I "doctored/cooked" the books. To do this, I did the
calculation in the procedure. First I added a variable to the block (outside the procedure)
called v_newcontamt and then within the processing part of the block, I calculated
v_newcontamt by taking the result of the pass to p_contamt and multiplying it by 1.1. I
then changed the values in the output to output this new amount instead of the content of
p_contamt. The results are shown below.
SQL CODE:
SQL> edit Call_Adddonproc2
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno new_donation.idno%TYPE :='&input_idno';
v_driveno new_donation.driveno%TYPE :='&input_driveno';
v_contamt new_donation.contamt%TYPE :=&input_contamt;
v_newcontamt new_donation.contamt%TYPE;
PROCEDURE AddDonProc
(p_idno new_donation.idno%TYPE,
p_driveno new_donation.driveno%TYPE,
p_contamt new_donation.contamt%TYPE) AS
BEGIN
v_newcontamt := p_contamt * 1.1;
INSERT INTO new_donation(idno, driveno, contamt)
VALUES(p_idno, p_driveno, v_newcontamt);
END AddDonProc;
BEGIN
IF v_contamt > 20 THEN
AddDonProc (v_idno, v_driveno, v_contamt);
END IF;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ Call_Adddonproc2
Enter value for input_idno: 11111
Enter value for input_driveno: 100
Enter value for input_contamt: 120
PL/SQL procedure successfully completed.
SQL> SELECT * FROM new_donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
23456 300 14-JUN-99 10
12121 300 10-JUN-99 75
12121 100 500
11111 100 7777
12121 100 100
11111 100 132
13 rows selected.
In the example below I ran the procedure with an input_contamt of 10. The procedure only
gets executed if the contribution amount is greater than 20 so there was no activity and no
records were added to the table.
SQL CODE:
SQL> @ Call_Adddonproc2
Enter value for input_idno: 22222
Enter value for input_driveno: 200
Enter value for input_contamt: 10
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM new_donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
23456 300 14-JUN-99 10
12121 300 10-JUN-99 75
12121 100 500
11111 100 7777
12121 100 100
11111 100 132
13 rows selected.
An alternative method of doing the calculation is shown below. This time I did the
calculation in the block BEGIN using the v_contamt where the user entry was stored. When
I called the procedure, I passed the result of the calculation, v_newcontamt to the
procedure as the third element so it was stored in p_contamt. However, I didn't actually
use p_contamt since the INSERT used v_newcontamt. Then I redid the program so the value
used p_contamt and the results were the same. Then I decided why pass anything to
p_contamt since I can write v_newcontamt and I did that. In other words, there are a
variety of approaches you can take.
SQL CODE:
SQL> edit Call_Adddonproc3
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno new_donation.idno%TYPE :='&input_idno';
v_driveno new_donation.driveno%TYPE :='&input_driveno';
v_contamt new_donation.contamt%TYPE :=&input_contamt;
v_newcontamt new_donation.contamt%TYPE;
PROCEDURE AddDonProc
(p_idno new_donation.idno%TYPE,
p_driveno new_donation.driveno%TYPE,
p_contamt new_donation.contamt%TYPE) AS
BEGIN
INSERT INTO new_donation(idno, driveno, contamt)
VALUES(p_idno, p_driveno, v_newcontamt);
END AddDonProc;
BEGIN
IF v_contamt > 20 THEN
v_newcontamt := v_contamt * 1.1;
AddDonProc (v_idno, v_driveno, v_newcontamt);
END IF;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ Call_Adddonproc3
Enter value for input_idno: 33333
Enter value for input_driveno: 300
Enter value for input_contamt: 300
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM new_donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
23456 300 14-JUN-99 10
12121 300 10-JUN-99 75
12121 100 500
11111 100 7777
12121 100 100
11111 100 132
33333 300 330
14 rows selected.
SQL> edit Call_Adddonproc4
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno new_donation.idno%TYPE :='&input_idno';
v_driveno new_donation.driveno%TYPE :='&input_driveno';
v_contamt new_donation.contamt%TYPE :=&input_contamt;
v_newcontamt new_donation.contamt%TYPE;
PROCEDURE AddDonProc
(p_idno new_donation.idno%TYPE,
p_driveno new_donation.driveno%TYPE,
p_contamt new_donation.contamt%TYPE) AS
BEGIN
INSERT INTO new_donation(idno, driveno, contamt)
VALUES(p_idno, p_driveno, p_contamt);
END AddDonProc;
BEGIN
IF v_contamt > 20 THEN
v_newcontamt := v_contamt * 1.1;
AddDonProc (v_idno, v_driveno, v_newcontamt);
END IF;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ Call_Adddonproc4
Enter value for input_idno: 12121
Enter value for input_driveno: 200
Enter value for input_contamt: 225
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM new_donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
23456 300 14-JUN-99 10
12121 300 10-JUN-99 75
12121 100 500
11111 100 7777
12121 100 100
11111 100 132
33333 300 330
12121 200 247.5
15 rows selected.
SQL> edit Call_Adddonproc5
PL/SQL CODE:
SET VERIFY OFF
DECLARE
v_idno new_donation.idno%TYPE :='&input_idno';
v_driveno new_donation.driveno%TYPE :='&input_driveno';
v_contamt new_donation.contamt%TYPE :=&input_contamt;
v_newcontamt new_donation.contamt%TYPE;
PROCEDURE AddDonProc
(p_idno new_donation.idno%TYPE,
p_driveno new_donation.driveno%TYPE) AS
BEGIN
INSERT INTO new_donation(idno, driveno, contamt)
VALUES(p_idno, p_driveno, v_newcontamt);
END AddDonProc;
BEGIN
IF v_contamt > 20 THEN
v_newcontamt := v_contamt * 1.1;
AddDonProc (v_idno, v_driveno);
END IF;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ Call_Adddonproc5
Enter value for input_idno: 11111
Enter value for input_driveno: 200
Enter value for input_contamt: 500
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM new_donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
11111 200 12-JUN-99 35
23456 300 14-JUN-99 10
12121 300 10-JUN-99 75
12121 100 500
11111 100 7777
12121 100 100
11111 100 132
33333 300 330
12121 200 247.5
11111 200 550
16 rows selected.