11 Replies Latest reply: Nov 5, 2004 8:06 AM by 27876 RSS

    difference between procedure and function

    Onenessboy
      hi

      please give solution to below discussion:

      Interviewer: What is the difference between Procedure and Function ?
      Myself: Procedure may or may not return a value and can return multiple values and Function must return a value.
      Interviewer : Can function return multiple values ?
      myself: Yes, It can return multiple values.
      Interviewer: Then, there is no need to use procedures any more, according to your previous answer (function can return multiple values) "we can do all the things by using procedures by using functions". Then why there is differentiation between procedure and function ?
      myself : no reply (simply frustated at this question)

      The above is conversation between me and interviewer.
      Please suggest me what would be my reply to above topic.
      In one book i find that using functions we can return multiple values but it is poor programming practice.
      why this is a programming practice ?

      please suggest me solution
      thanks in advance
      prasanth as.

        • 1. Re: difference between procedure and function
          121011
          myself: Yes, It can return multiple values.
          It's wrong. Oracle PL/SQL functions can't return multiple values.
          In one book i find that using functions we can return multiple values but it is poor programming practice.
          why this is a programming practice ?

          What it behind the book? May be, the author meant, that returned variable can contain multiple values. It is really a "poor programming practice".

          It was necessary to answe, that the functions can be used both in SQL and PL/SQL. Procedures can be used only in PL/SQL.
          • 2. Re: difference between procedure and function
            125473
            Hi,

            Function & Procedure Return Mulitple Value through Out Parameter.
            When We don't need to return a value then we should use procedure.

            Function Can Use in SQL Query &
            Index (Deterministic Function)

            Bye
            Chitta
            • 3. Re: difference between procedure and function
              Laurent Schneider
              Hi 122470,
              Thanks for pointing out that a function may use OUT parameter. I never saw it before.

              It is not fair to say that a procedure does return one or more values.

              A function ALLWAYS return one value and a procedure return NOTHING.

              Both can use IN/OUT parameters. The IN OUT are like pointers in C, and have nothing to do with a "return value", which is what a function returns.

              A function can return a collection of multiple values, like a table. This is very usefull !

              Regards
              Laurent Schneider
              OCM DBA
              • 4. Re: difference between procedure and function
                125473
                Hi Laurent,

                My return means get.
                Sorry for misunderstanding

                Regards,
                Chitta
                • 5. Re: difference between procedure and function
                  107862
                  Hi,

                  "Both can use IN/OUT parameters. The IN OUT are like pointers in C, "

                  OUT and IN OUT Parameter Types are Used by Call by Value fashion,

                  to make it Call by Reference like pointer u have to use
                  NOCOPY Construct.

                  pls add your suggestion and comments

                  Correct me!!

                  Regards
                  Raj Ganga
                  mail : rs_ganga@yahoo.com
                  • 6. Re: difference between procedure and function
                    APC
                    Raj

                    You are basically correct, although according to the docs NOCOPY is a hint, not a directive, so even with NOCOPY the parameters might be passed by value.

                    NOCOPY offers us better performance but unhandled exceptions may cause problems by leaving the parameters in an indeterminate state.

                    On the topic of bad programming practice, I think creating functions with OUT parameters is bad form, but my reasons for believing that are aesthetic or emotional rather than the result of any scientific study.

                    And although the return value for a function can be a nested table, an array of objects or a ref cursor I would still count them as returning a single value because the function's result has to be assigned to a variable.

                    Cheers, APC
                    • 7. Re: difference between procedure and function
                      52207
                      So can someone please give a point-wise details of all possible difference between function and procedure.

                      Function Procedure
                      -----------------------------------------------------
                      1. Can be used in SQL Cannot be used in SQL
                      2. Always returns Value Not required always
                      3. ? ?
                      • 8. Re: difference between procedure and function
                        Laurent Schneider
                        Thanks for pointing out that a function may use OUT parameter. I never saw it before.
                        I just noticed a function with an out parameter is not usable in sql.

                        SQL> var s varchar2(40)
                        SQL> var i number
                        SQL> select DBMS_UTILITY.GET_PARAMETER_VALUE ('db_block_size',:i, :s) from dual;
                        select DBMS_UTILITY.GET_PARAMETER_VALUE ('db_block_size',:i, :s) from dual
                        *
                        ERROR at line 1:
                        ORA-06572: Function GET_PARAMETER_VALUE has out arguments

                        • 9. Re: difference between procedure and function
                          115987
                          Another difference is function must return something. There is no such restriction on procedure.

                          So to call a procedure where there's is no need of return value you can do.

                          begin
                          proc;
                          end;

                          but if function were to implement this then

                          declare
                          return variable var;
                          begin
                          var := function;
                          end;

                          • 10. Re: difference between procedure and function
                            115987
                            oops , ignore my post it has already been said before.
                            • 11. Re: difference between procedure and function
                              27876
                              Another difference is function must return something. There is no such restriction on procedure.
                              In fact, a procedure CANNOT contain an expression in its RETURN statement.
                              SQL> create or replace procedure test_return is
                                2  begin
                                3    return(10) ;
                                4  end ;
                                5  /
                              
                              Warning: Procedure created with compilation errors.
                              
                              SQL> show errors
                              Errors for PROCEDURE TEST_RETURN:
                              
                              LINE/COL ERROR
                              -------- -----------------------------------------------------------------
                              3/3      PLS-00372: In a procedure, RETURN statement cannot contain an
                                       expression
                              
                              3/3      PL/SQL: Statement ignored
                              SQL>
                              And, a procedure cannot be called as part of a expression (it must be a function).
                              SQL> create or replace procedure test_return is
                                2  begin
                                3    return ;
                                4  end ;
                                5  /
                              
                              Procedure created.
                              
                              SQL> variable x number ;
                              SQL> exec :x := test_return ;
                              BEGIN :x := test_return ; END;
                              
                                          *
                              ERROR at line 1:
                              ORA-06550: line 1, column 13:
                              PLS-00222: no function with name 'TEST_RETURN' exists in this scope
                              ORA-06550: line 1, column 7:
                              PL/SQL: Statement ignored
                              
                              
                              SQL>