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.
[code]
— 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;
[/code]