Procedures or Functions (Pl/sql)

Question

I am currently learning pl/sql using oracle 10g I have a certain confusion When should I use stored procedures and when should i go for functions? Please help me out with some real world example. Thank you.

Answer 1

A function returns a value, although that could actually be an object like a cursor.

Also only a function can be defined with the following (as of 11.1):

1. DETERMINISTIC option, which helps the optimizer avoid redundant function calls.
2. PARALLEL_ENABLED option, which allows the function to be used safely in slave sessions of parallel DML evaluations.
3. PIPELINED option, which returns the results of a table function iteratively.
4. RESULT_CACHE option, which stores function results in the PL/SQL function result cache.
5. RESULT_CACHE clause, which specifies the data sources on which the results of a function.

So if you need to return multiple values, use a procedure. However be aware that the above five features are then not available.

If you want to include a PL/SQL subprogram in a SQL statement then you probably want a function.

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#CHDBEJGF

Answer 2

DECLARE
    l_user_id       VARCHAR2(1);
    l_received_user VARCHAR2(30);

    PROCEDURE print_user_name(user_name_in IN VARCHAR2)
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('The user''s name is: ' || INITCAP(user_name_in));
    END print_user_name;

    FUNCTION get_user_name(user_id_in IN VARCHAR2) RETURN VARCHAR2
    AS
        l_user_name VARCHAR2(30);
    BEGIN
        SELECT  'Amanda'
        INTO    l_user_name
        FROM    dual
        WHERE   dummy = user_id_in;

        RETURN  l_user_name;
    END get_user_name;
BEGIN
    -- excute an action --
    print_user_name('John');

    l_user_id := 'X';

    -- hold action's result in a variable --
    l_received_user := get_user_name(l_user_id);

    -- work with the received result/variable --
    DBMS_OUTPUT.PUT_LINE('The received user''s name is: ' || INITCAP(l_received_user));

    IF l_received_user  = 'John' THEN
        DBMS_OUTPUT.PUT_LINE('The received user''s name is John');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The received user''s name is not John');
    END IF;
END;
/*
The user's name is: John
The received user's name is: Amanda
The received user's name is not John
*/

Answer 3

Difference is that sored procedure do something, while functions do something and return result (variable or table).