Notes 0012

Functions

Functions are special types of procedures that have the capability to return a value.

It is a very shady question of when to use what, either functions or procedures. A good rule of thumb is: if you're interested in the "results" of the code, then you use a function, and return those results. If you're interested in the "side effects" (like table updates, etc.) and not about the "result" when you should use a procedure. Usually it doesn't affect your code all that much if you use a procedure or a function.

General Format

The general format of a function is very similar to the general format of a procedure:

CREATE OR REPLACE
FUNCTION function_name (function_params) RETURN return_type IS
BEGIN
    function_body
    RETURN something_of_return_type;
END;

For example, to write a function that computes the sum of two numbers, you might do something like this:

CREATE OR REPLACE
FUNCTION ADD_TWO (A INT,B INT) RETURN INT IS
BEGIN
    RETURN (A + B);
END;

To run it, we'll write a small piece of code that calls this:

BEGIN
    DBMS_OUTPUT.PUT_LINE('RESULT IS: ' || ADD_TWO(12,34));
END;

Which procudes the output:

RESULT IS: 46

All of a sudden, we know how to make functions (since we already know how to crate procedures). That's really all there is to it.

Dropping Functions

To drop a function, you do it in a similar way to a procedure. You simply say:

DROP FUNCTION function_name;

Oh, btw, to display the list of procedures/functions or plain general user objects that you have you can run a query:

SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION';

You can do a similar thing for procedures.

That's all there is to stored procedures and functions! You should practice writing these things. There is no better way to learn these than to do it yourself and get familiar with all the concepts and code.

© 2006, Particle