More on Procedures and Functions

Remember, I am only INTRODUCING FUNCTIONS AND PROCEDURES in this course because we seem to have some extra time. You really need to do extensive reading on this topic because there are a lot of effective ways of approaching them and a lot of constraints that you need to make yourself aware of.

We are now going to look at a PL/SQL block of code that has both an internal procedure and an internal function. In the main block, I am looking at an input contribution amount. Depending on the amount, I set a code that will be passed to the function. The function uses the code to calculate the new contribution amount - my this is what you should give therefore what you did give - and passes it back to the main block which now calls the procedure to insert the new amounts in the new_donation table. In many ways this is adding extra work that would not be there if I had eliminate functions and procedures, but simple examples frequently have redundancy in them.

Looking at the code. The function is receiving the v_code and the v_amount that are passed and is storing them as f_code and f_contamt. Remember the pass for the function is accomplished with the statement: v_newcontamt := CalcNewDon(v_code, v_contamt); where CalcNewDon is the name of the function and the values in v_code and v_contamt are being passed to it. Note that v_newcontamt will received the result that is returned after the function has been executed. The statement: RETURN f_newcontamt; is the statement that sends back what is in f_newcontamt and it will be assigned to v_newcontamt.

There is one significant rule that we need to be aware of. When I described f_code and f_contamt, I gave them a type and not a length. The constraint of a length is not allowed in this definition area within a function. I did try using TYPE here, for example giving f_code a type of v_code and that worked. If you want more information on this, it is available in the PL/SQL book that was suggested for the course or you can look for notes up functions and parameters on the Internet.

Note that in the function, I passed the amount to it as well as the code. The processing would have worked if I had simply passed the code and used v_contamt instead of f_contamt in the processing. However, it is a good idea to pass the function the things that it needs and have it basically self-sufficient.

The procedure is the same as that in previous examples. The statement: AddDonProc (v_idno, v_driveno, v_newcontamt); sends the three pieces of data to the procedure where they are stored as p_idno, p_driveno and p_contamt. They are then used in the INSERT to write a new record/row to the new_donation table.

SQL CODE:

SQL> edit call_adddonproc6

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;
  v_code       VARCHAR2(2);

  FUNCTION CalcNewDon
    (f_code   VARCHAR2, f_contamt NUMBER)
    RETURN NUMBER IS
      f_newcontamt  new_donation.contamt%TYPE;
    BEGIN
      IF f_code = 'EX' THEN
         f_newcontamt := f_contamt * 1.3;
      ELSE
         IF f_code = 'VG' THEN
            f_newcontamt := f_contamt * 1.2;
         ELSE
            IF f_code = 'OK' THEN
               f_newcontamt := f_contamt * 1.1;
            ELSE
               f_newcontamt := f_contamt;
            END IF;
         END IF;
       END IF;
       RETURN f_newcontamt;
   END CalcNewDon;  
 
  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 > 500 THEN
     v_code := 'EX';
     v_newcontamt := CalcNewDon(v_code, v_contamt);
  ELSE
     IF v_contamt > 250 THEN
        v_code := 'VG';
        v_newcontamt := CalcNewDon(v_code, v_contamt);  
     ELSE
       IF v_contamt > 100 THEN
          v_code := 'OK';
          v_newcontamt := CalcNewDon(v_code, v_contamt);
       ELSE
          v_code := 'NG';
          v_newcontamt := CalcNewDon(v_code, v_contamt);
       END IF;
     END IF;
  END IF;

  AddDonProc (v_idno, v_driveno, v_newcontamt);
END;
/
SET VERIFY ON

SQL CODE:

SQL> @ call_adddonproc6
Enter value for input_idno: 11111
Enter value for input_driveno: 100
Enter value for input_contamt: 700

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                 100
11111 100                 910

11 rows selected.

SQL> @ call_adddonproc6
Enter value for input_idno: 22222
Enter value for input_driveno: 200
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                 100
11111 100                 910
22222 200                 360

12 rows selected.