2011
10.06

Every now and then I need to write a block of Oracle 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.

-- PLSQL code
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;
  • http://www.facebook.com/people/Jester-Celis/100003369231587 Jester Celis

    Thanks for the information. I’m looking for these for quite some time now. Your an angel!

    “Jester” for autoradio USB 

  • http://myvirtualteams.com/ Myvirtual Teams

    Thanks for the sharing this SQL Code its Really good knowledge for me . http://myvirtualteams.com/