Results 1 to 2 of 2

Thread: Question

  1. #1
    Join Date
    Oct 2002

    Unanswered: Question


    what is the difference between stored procedures and stored functions.

    this question has been asked in an interview

    one answer i gave that functions can return one value and procedures can return more than one value and second is a function can be called within a sql select statement and can be incorporated within a dml statement. but they said some thing else that functions are non_deterministic. what exactly does it mean can u plz clarify my doubt and explain me any more differences are there between a stored procedure and stored function

    Thank you

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Question

    The words "deterministic" and "non-deterministic" refer only to functions - they do not apply to procedures at all.
    A deterministic function is one that will always return the same result given the same inputs - e.g. UPPER, SUBSTR, TO_NUMBER,...
    A non-deterministic function is one that may return different results at different times for the same inputs. This would be a function that selects from the database, or refers to package global variables, etc. For example, a function "GET_EMP_NAME( p_id IN ) RETURN VARCHAR2" could return different results for the same ID if you updated the EMP table in between.
    Oracle requires that you declare functions to be DETERMINISTIC before you can use them to create function-based indexes.

    Apart from that, your list of differences between procedures and functions is correct.

Posting Permissions

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