SQL - how to choose between a procedure and a function?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 102 of 171

how to choose between a procedure and a function?

Total Answers and Comments: 8

Last Update: January 11, 2007 Asked by: swathiraj

Sponsored Links

Best Rated Answer

—

Submitted by: adarsh_sp

1.function invoke as a part of an expression. Procedure executes as a plsql statement

2. function must contain return clause in hedder. Procedure donot contain return clause in hedder

3. function must return a single value. Procedure can return none,one or multiple values

Above answer was rated as good by the following members:
spohilko

September 22, 2006 05:14:37

DIPALI RINAYAT

RE: how to choose between a procedure and a function?

function returns value but procedure not.

Is this answer useful? Yes | No

September 25, 2006 05:13:17

suresh.kandukuru

Member Since: September 2006 Contribution: 3

RE: how to choose between a procedure and a function?

Both function and procedure returns value.function has explicit return statement.procedure returns multiple valueswithin the function body we cant put DML statements.

Is this answer useful? Yes | No

October 03, 2006 03:24:14

adarsh_sp

Member Since: October 2006 Contribution: 13

RE: how to choose between a procedure and a function?

1.function invoke as a part of an expression. Procedure executes as a plsql statement

2. function must contain return clause in hedder. Procedure donot contain return clause in hedder

3. function must return a single value. Procedure can return none one or multiple values

Is this answer useful? Yes | No

1 0

Overall Rating: +1

October 10, 2006 03:59:26

Mohammed Irfan

RE: how to choose between a procedure and a function?

if you are performing an operation which does not require to return any value

or wants to return multiple values go for procedures

if ur confident that ur operation will return only one value then go for functions

and remember to have function & procedure name very cohesive with the operations they perform.

Is this answer useful? Yes | No

November 02, 2006 08:30:16

RE: how to choose between a procedure and a function?

If want 2 calculate something and not interested in return value

then Use Procedure otherwise F(x)

Is this answer useful? Yes | No

November 20, 2006 04:36:06

Debi Prasad Maharana,Wipro Technologies.

RE: how to choose between a procedure and a function?

hi when to select function :(1) when you want your program unit to return some value.(2) when you want your program unit to be a part of pl/sql expression(3) when you want your program unit to be called in the SQL statement. ( using the function in SQL statement there are some restrictions are there if you have any doubt regarding this please get back to me.)when to select procedure :(1) when you want your program unit to execute some business logic .(2) when you don't want any return statement from your program unit.

Is this answer useful? Yes | No

November 23, 2006 04:13:26

Manish

RE: how to choose between a procedure and a function?

1. SP can not return the table datatype whereas function can return.

2. in UDFunction if one sql statement raise the error then whole function is terminated where in SP if one sql statement raise the error then other sql statement can be executed.

Is this answer useful? Yes | No

January 11, 2007 14:25:28

sharib

RE: how to choose between a procedure and a function?

i think you are wrong.we can write dml in both function and procedure.

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank