Inlining PL/SQL code

In this recipe, we will see the benefits of inlining the PL/SQL code in our functions and procedures.

How to do it...

The following steps will demonstrate how to make PL/SQL functions inline:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a SIMPLE_FUNCTION function, which returns the area of a triangle given the length of he base and the height:

   CREATE OR REPLACE FUNCTION SIMPLE_FUNCTION (N IN NUMBER,
    K IN NUMBER) RETURN NUMBER
   IS
   BEGIN
     RETURN (N * K / 2);
   END;
   /
   

3. Create a STRESS procedure, which calculates the area for a number of triangles using the SIMPLE_FUNCTION function created in step 2:

   CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER)
   IS
     AVAL NUMBER;
     T1 NUMBER;
   BEGIN
     T1 := DBMS_UTILITY.get_time;
     FOR J IN 1..ANUM LOOP
       AVAL := SIMPLE_FUNCTION (50,ANUM);
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('TIME: ' ||
       (DBMS_UTILITY.get_time - T1));
   END;
   /
   

4. Create the same procedure as in the previous step, inlining the SIMPLE_FUNCTION by adding a PRAGMA INLINE...