-3

What is the need of function in databse as everything that a function does, can also be achieved by Procedures in database.

Sambhav jain
  • 882
  • 2
  • 12
  • 16
  • http://stackoverflow.com/questions/178128/functions-vs-stored-procedures – Dave Newton Jan 19 '13 at 18:23
  • 4
    A very broad question and your assertion of "everything that a function does, can also be achieved by Procedures in database" is false. a function can be used in sql whereas a procedure cannot (so there's one reason for them). – DazzaL Jan 19 '13 at 18:58
  • 1
    They share a lot of common characteristics. So, it is reasonable to have it as a question. – igr Jan 19 '13 at 19:27

2 Answers2

4

In Oracle they are very similar. Principal difference is that function returns result, procedure doesn't. (Not to be confused with output parameters which is possible to have in both of them...)

This difference makes possible to use functions inside SQL statements like 

select user_name, myFunction( some_attribute ) from ...

or 

select ... where fn2(col1)=something

You can think of it as some kind of transformation.

Of course, for efficient use inside SQL statements, they should be fast, as can be executed many times for each line in output or each examined line. (And/or be sure that number of lines will not be too big).

Also, in recent versions is possible to use return as real time dbms_output (search for pipe row).

APC
  • 144,005
  • 19
  • 170
  • 281
igr
  • 3,409
  • 1
  • 20
  • 25
  • 2
    It is generally considered *bad practice* to have OUT parameters in the signature of a function. – APC Jan 20 '13 at 12:07
1

You can also use functions in function based indexes. And these indexes can also be unique indexes. With function based unique indexes you can build sophisticated unique constraints. 

create unique index UK_MYINDEX
 on TBL_MYTABLE (case when IS_ACTUAL(MY_COLUMN_2) then MY_COLUMN_1 end);

In this example I use the IS_ACTUAL function in a unique index in order to enforce the unique MY_COLUMN_1 constraint only on the actual records.

Donato Szilagyi
  • 4,279
  • 4
  • 36
  • 53