INTERVIEW QUESTIONS DATABASE PL/SQL DETAILS

Question: Describe the difference between a procedure, function and anonymous pl/sql block.

Answer: Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.

Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to. Source: CoolInterview.com

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.

2. Function can be called from SQL statements where as procedure can not be called from the sql statements

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5. Function returns 1 value only. Procedure can return multiple values (max 1024).

6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.

7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.

9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Source: CoolInterview.com

Function:

*It is a database object.

*It is one type of subprogram.

*It is recomended to use perform a dql(select) operations.

*Function should return a value.

Procedure:

* It is also a database object.
* It is a another type of subprogram.

* It is recomended to perform dml(insert,update,delete) operation. Source: CoolInterview.com

procedure and function are block of executable statements which may or mayn't accept agrement.procedure never return any value whereas function returns a value.. Source: CoolInterview.com

A procedure cannot be used in a select statement where as a function can be used. Source: CoolInterview.com

procedure doesn't have to return. They are compiled pl/sql block.
function must return something. U can use them in normal select statements. they r also pre-compiled pl/sql code.
PL/SQL block, is compiled every time. We do not have handle like names to call themfrom different places. You need to copy code for that. Source: CoolInterview.com

not only function ,procedure can also a return a value but difference is we can not use procedures in sql statements but we can use functions in sql statement Source: CoolInterview.com

function-must return a value in Pl/Sql.
Procedure may return a value in Pl/sql.
Source: CoolInterview.com

Function can return only one value at a time while procedure can return multiple values at a time. Source: CoolInterview.com

Function can return only one value at a time while procedure can return multiple values at a time. Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question