1 2 Previous Next 15 Replies Latest reply: Nov 24, 2011 4:54 AM by Nicolas.Gasparotto RSS

    Functions vs Stored Procedures

    390534
      I have a dumb question for all the Oracle Experts....

      What is the difference between using Stored Procedures and Functions in Oracle 9i (all Oracle for that matter I guess).

      They both can return values, they both take parameters... Is there any difference?

      I know in the app dev world functions can be overloaded in an inheritance structure but not sure about the dif in a DB world?????

      What are Pros and Cons of each?

      Just curious,
      Miller
        • 1. Re: Functions vs Stored Procedures
          JustinCave
          Functions are generally restricted to returning a single value, while procedures can have multiple OUT parameters. There are situations where functions are more useful in queries (i.e. you want to create your own function that can be used in a query like the standard functions UPPER, etc.). In some client-side languages, it's slightly easier to call stored procedures rather than stored functions.

          99% of the time, I've seen people opt for stored procedures. I suspect this is more because of convention than becauseone is technically superior to the other.

          Justin
          • 2. Re: Functions vs Stored Procedures
            403551
            The functions can return only one value and procedures not. Functions can be call from SQL Statements, procedures not and there are some things that you can
            do in a stored procedure that you can not do in a function.

            ing_joelperez@hotmail.com

            Joel P�rez
            • 3. Re: Functions vs Stored Procedures
              APC
              Only functions return a value. Procedures can only have OUT parameters. Therefore, we can use functions in a SELECT statement but we cannot use procedures similarly.

              There is a convention that procedures should be used whenever DML is required and functions should be used for "read-only" calls. Sometimes it seems as though this is more honoured in the breach than the observance.

              Oracle doesn't really do "inheritance", at least not in the full-blooded way that (say) Java does.

              Cheers, APC
              • 4. Re: Functions vs Stored Procedures
                APC
                Three answers, all slightly different, but all ain agreement. Cool.
                • 5. Re: Functions vs Stored Procedures
                  551565
                  There is one more difference between stored procedure and functions that stored procedures compiled only once and can be called again and again without being compiled each time, this improves performance and saves time, on the other hand functions compile each time they get called
                  • 6. Re: Functions vs Stored Procedures
                    545506
                    is this really happened ?

                    Pls reply ....
                    • 7. Re: Functions vs Stored Procedures
                      462393
                      one more thing i would like to notice according Fun v/s SProc.

                      u can write Insert, Update, Delete Statements in Sproc, but can't in Fun. Except Select.

                      Mahesh
                      • 8. Re: Functions vs Stored Procedures
                        SomeoneElse
                        What?
                        SQL> create or replace
                          2  function delete_emps(dept in number) return number as
                          3
                          4  begin
                        5 delete emp
                        6 where emp.deptno = dept;

                          7
                          8     return sql%rowcount;
                          9  end;
                        10  /

                        Function created.

                        SQL> var rowsdel number
                        SQL> exec :rowsdel := delete_emps(30);

                        PL/SQL procedure successfully completed.

                        SQL> print rowsdel

                                     ROWSDEL
                        --------------------
                                           6
                        • 9. Re: Functions vs Stored Procedures
                          SomeoneElse
                          Oh wait...I know what you mean.

                          You can't do DML in a function that is called from within a select statement.
                          • 10. Re: Functions vs Stored Procedures
                            462393
                            can it be possible in Oracle8i?, if yes, then i m extremly sry for that.

                            Mahesh
                            • 11. Re: Functions vs Stored Procedures
                              462393
                              so better way to write SProc in such situations (Writting Insert, Delete, Update Statements)

                              Mahesh
                              • 12. Re: Functions vs Stored Procedures
                                APC
                                You can't do DML in a function that is called from within a select statement.
                                Er, up to a point Lord Copper.
                                SQL> select empno, ename from emp
                                  2  where deptno = 10
                                  3  /
                                     EMPNO ENAME
                                ---------- ----------
                                      7782 BOEHMER
                                      7839 SCHNEIDER
                                      7934 ROBERTSON

                                SQL> create or replace function delete_emps
                                  2      (dept in number) return number as
                                  3       pragma autonomous_transaction;
                                  4       return_value number;
                                  5  begin
                                  6        delete from emp
                                  7        where  emp.deptno = dept;
                                  8      return_value := sql%rowcount;
                                  9      commit;
                                10      return return_value;
                                11  end;
                                12  /

                                Function created.

                                SQL> select delete_emps(10) from dual
                                  2  /
                                DELETE_EMPS(10)
                                ---------------
                                              3

                                SQL> rollback;

                                Rollback complete.

                                SQL> select empno, ename from emp
                                  2  where deptno = 10
                                  3  /

                                no rows selected

                                SQL>
                                Of course, using the AUTONOMOUS_TRANSACTION pragma in such a fashion is extremely dangerous. In fact, this is the sort of thing which makes Tom Kyte say, "I really wish I hadn't told you how to do that, because it's such a bad idea".

                                Cheers, APC
                                • 13. Re: Functions vs Stored Procedures
                                  SomeoneElse
                                  3 pragma autonomous_transaction;
                                  Yes, of course. Best forgotten.

                                  Wait...what were we talking about?

                                  ;-)
                                  • 14. Re: Functions vs Stored Procedures
                                    901972
                                    main advantages is every times the function is called different value is returned. sp can be compiled only once and called again and again without compiled which saves time improves performance
                                    1 2 Previous Next