when we use procedure and when we use function?

Question

Which two tasks should be created as functions instead of as procedures? (Choose two.)

A.reference host or bind variables in a PL7SQL block of code.

B.tasks that compute and return multiple values to the calling environment.

C.tasks that compute a value that must be returned to the calling environment.

D.tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application.

whats the answer

and can we reference host or bind variable in function or procedure ?

Answer 1

Well, let's see:

A. Reference host or bind variables in a PL/SQL block of code - both procedures and functions can accept bound variables from host code, so this applies to both.

B. Tasks that compute and return multiple values to the calling environment - both procedures and function can do this via OUT parameters, so this also applies to both.

C. Tasks that compute a value that must be returned to the calling environment - both procedures and functions can do this (procedures can use OUT parameters), thus again this applies to both - although the use of the word "returned" in the question suggests that the writer meant "returns a value through the use of the RETURN keyword" so let's give this one to functions only and ignore the fact that we know better.

D. Tasks performed in SQL that increase data independence...blah-blah-blah... - I'm going with functions on this one, as an SQL statement can only call a function, not a procedure - plus, I have to choose two answers so this has got to be one of them since the first two are obviously not attributes of functions ONLY.

So, C and D.

How'd I do? :-)

Share and enjoy.

Answer 2

Function is required when a value must be returned to the calling environment and/or when it is expected to be used in queries. Procedure can't do either of those.