Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: PLSQL Stored Procedures Vs PLSQL functions?

    which is best PLSQL Stored Procedures or PLSQL functions?
    could you please list the pros and cons of these db objects?
    Is there any guideline as to when I should use a procedure instead of a function?
    Last edited by Shefu; 02-09-07 at 06:41.

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    A procedure can change data; a function cannot.
    Last edited by ivon; 02-09-07 at 07:09.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Shefu
    Is there any guideline as to when I should use a procedure instead of a function?
    The same as for other programming languages: use a function when you need to return a value, use a procedure if you don't

  4. #4
    Join Date
    Apr 2005
    Posts
    127
    Is it possible to use any DMLs like SELECT, INSERT or DDLs like CREATE, SQL statements inside a Function?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    (Some) DML yes, DDL no. Unless I'm wrong about it.

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    You can use DDL by executing dynamic sql by using the "execute immediate" statement...
    eg.
    Code:
    create or replace procedure test_proc is
    begin
    
       --create a table
       execute immediate 'create table test_tbl(id number, name varchar2(32))';
    
    end test_proc;
    
    create or replace function test_fnc is
    begin
    
       --create a table
       execute immediate 'drop table test_tbl purge';
       return true;
    
    end test_fnc;

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    From the manual I cannot find any difference between a function and a procedure as far as usage of the usage of DML is concerned:

    "Generally, you use a procedure to perform an action and a function to compute a value."

    Whether a function can update the database (use DML) depends on the context where it's called.
    The restrictions are described in the SQL Reference

    The manual even mentions certains situations where functions are explicitely used for manipulating data:

    "Functions have a number of optional keywords, used to declare a special class of functions known as table functions. They are typically used for transforming large amounts of data in data warehousing applications"

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Stupid me. Yes, I was wrong about it. What was I thinking of?!?

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I really like shammats' quote
    "Generally, you use a procedure to perform an action and a function to compute a value."

    I think that pretty much sums it up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •