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.