Handy PL/SQL

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]

2 thoughts on “Handy PL/SQL”

Comments are closed.