ObjectClarity.com

Every now and then I need to write a block of PL/SQL that’s a little more involved. I’ve had this block of script put aside as an example of how to include functions and procedures in an anonymous block.

BEGIN
   DECLARE
       -- Equivalent of a package level variable
       i   INTEGER := 0;

      PROCEDURE doStuff (description IN VARCHAR2)
      IS
         i   INTEGER;
      BEGIN
         DBMS_OUTPUT.put_line ('description is ' || description);

         i := i + 1;
      END;

      FUNCTION calcStuff (i_value IN INTEGER)
      return integer
      iS
         i_dummy   INTEGER;
      BEGIN
         i := i + 1;
         DBMS_OUTPUT.put_line ('Function called ' || i || ' time(s)' );

         return i_value + i;
      END;

   BEGIN
      -- Call our shiny new procedure
      doStuff ('one');
      doStuff ('two');
      doStuff ('three');

      -- Call our new function
      DBMS_OUTPUT.put_line ('Function returned: ' || calcStuff(100));
      DBMS_OUTPUT.put_line ('Function returned: ' || calcStuff(150));
      DBMS_OUTPUT.put_line ('Function returned: ' || calcStuff(200));

   END;
END;