Another example of internal procedures and functions

The first thing that I did for this example, was create a test table/file and make a copy of it. The tables are called testprfn and testprfn2.

SQL CODE:

SQL> DESC testprfn;
 Name                            Null?    Type
 ------------------------------- -------- ----
 FLD1                                     NUMBER(3)
 FLD2                                     VARCHAR2(10)
 FLD3                                     NUMBER(5)
 FLD4                                     VARCHAR2(10)

SQL> SELECT * FROM testprfn;

     FLD1 FLD2            FLD3 FLD4
--------- ---------- --------- ----------
      123 Boston         12345 John
      234 Providence     23456 Ann
      345 Fall River     34567 Linda
      456 Boston         45678 Lawrence
      567 Fall River     56789 Susan

Then I wrote a program as a PL/SQL block with an embedded Procedure and an embedded Function. This program shows code but is not realistic in its achievement!

First the program declares a series of variables in the DECLARE that will be available throughout the code. They are variables to receive the data from the file and a variable to hold the result of a calculation. Since multiple records will be processed, I set up a cursor next. The cursor takes all of the records from the table testprfn2 and uses the FOR UPDATE clause since the table will be updated as a result of the code. Note that the procedures and functions are done after the variables and cursor.

The procedure is called change_fld4 and if you look at the logic, the function is done first. The procedure is passed p_fldtot which will only be used for input to the procedure and p_fld4 which will be changed and passed back. Notice again the way the fields being received are set up with ( ) around them and the word AS following them. In the procedure, I test to see if the p_fldtot which was passed is greater than 30000 and if the length of the p_fld4 that was passed is less than 8. If this is true, then p_fld4 is changed to have an * in front of and behind the name.

The function receives the contents of fld1 and fld3 from the record on the table (sent as v_fld1 and v_fld3 since this is where the fetch puts them). Within the function they are declared as f_fld1 and f_fld3 and as numbers without the length constraint that functions can not handle. The return field is also defined. A calculation is done that subtracts f_fld1 from f_fld3 and returns the defined return field using the RETURN syntax.

In the main block of the program, the cursor is opened and the initializing fetch is done, putting the fields from the record/row on the table in to v_fld1, v_fld2, v_fld3, and v_fld4. The loop is entered and will be executed until the fetch can no longer fine a record/row in the cursor to be processed. Then the calc_num function is executed using the statement: v_fldtot := calc_num(v_fld1, v_fld3); The result of the function execution is stored in v_fldtot. This is the field that is then passed change_fld4 along with v_fld4. The change is done within the procedure and the v_fld4 which was declared as IN OUT returns the adjusted name when appropriate. This is then used to update the current record/row from the cursor on the table. The dbms displays a record on the screen and then the fetch is done to fetch the next record/row from the cursor. After the loop has ended the cursor is closed and the block is ended.

SQL CODE:

SQL> edit procfnc2

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  v_fld1    testprfn2.fld1%TYPE;
  v_fld2    testprfn2.fld2%TYPE;
  v_fld3    testprfn2.fld3%TYPE;
  v_fld4    testprfn2.fld4%TYPE;
  v_fldtot  testprfn2.fld4%TYPE;

CURSOR fld_cursor IS
    SELECT * FROM testprfn2
       FOR UPDATE; 

  PROCEDURE change_fld4
    (p_fldtot IN     testprfn2.fld1%TYPE,
     p_fld4   IN OUT testprfn2.fld4%TYPE) AS
     
  BEGIN
     IF p_fldtot > 30000 AND 
       LENGTH (p_fld4) < 8 THEN
        p_fld4 := ('*'||p_fld4||'*');
     END IF;
  END change_fld4;

  FUNCTION calc_num
    (f_fld1 NUMBER, f_fld3 NUMBER)
    RETURN NUMBER IS
      f_fldtot   testprfn2.fld4%TYPE;
    BEGIN
      f_fldtot := f_fld3 - f_fld1;
      RETURN f_fldtot;
  END calc_num;
    
BEGIN
   OPEN fld_cursor;
   FETCH fld_cursor into v_fld1, v_fld2, v_fld3, v_fld4;
   WHILE fld_cursor%FOUND LOOP
      v_fldtot := calc_num(v_fld1, v_fld3);
      change_fld4(v_fldtot, v_fld4);  
      UPDATE testprfn2
         SET fld4 = v_fld4
         WHERE CURRENT of fld_cursor;
      dbms_output.put_line(v_fld1||' '||v_fld4||' '||v_fldtot);
      FETCH fld_cursor into v_fld1, v_fld2, v_fld3, v_fld4;  
   END LOOP;
   CLOSE fld_cursor;
END;
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> edit procfnc2
SQL> @ procfnc2
123 John 12222
234 Ann 23222
345 *Linda* 34222
456 Lawrence 45222
567 *Susan* 56222

PL/SQL procedure successfully completed.

Input truncated to 22 characters
SQL> select * from testprfn2;

     FLD1 FLD2            FLD3 FLD4
--------- ---------- --------- ----------
      123 Boston         12345 John
      234 Providence     23456 Ann
      345 Fall River     34567 *Linda*
      456 Boston         45678 Lawrence
      567 Fall River     56789 *Susan*

The output changes fld4 where the result of the calculation was greater than 30000 (remember the calculation was to subtract fld1 from fld3) and where the name is less than 8 characters long. Therefore Linda and Susan got changed. For John and Ann the result of the calculation was not greater than 30000 and for Lawrence the length of fld4 was not less than 8.