question

OracleApprender avatar image
OracleApprender asked

Difference between a FUNCTION, PROCEDURE and PACKAGE in Oracle ?

I need the differences bettwen a FUNCTION, PROCEDURE and PACKAGE in Oracle. Which one is better to use in most of the times ? Thank you.

oracleprocedurepackagefunction
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Andrew Mobbs avatar image
Andrew Mobbs answered

Functions return a value, procedures don't, otherwise they're identical.

A Package is a container that may have many functions and procedures within it. It provides useful capabilities such as scoping, interface definition and modular development.

It's generally advisable to put your procedures and functions into packages with well designed interfaces.

4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Procedures can return values via OUT params.
0 Likes 0 ·
True, but that's not quite the same as a function returning a value. Since functions guarantee a return value in a standard way, they can be part of an expression, whereas procedures can't. e.g. SELECT sqrt(x) + sqrt(y) FROM dual;
0 Likes 0 ·
So other than the return values there is no difference in between Functions and Procedures. I mean in point of performance.
0 Likes 0 ·
There's no intrinsic difference in performance, a single call to a function is just as fast as a single call to a procedure. However because functions can be used in expressions, they can be used to write poorly performing queries. For example, a function can be put in a WHERE clause, which may cause it to be evaluated many times. That isn't a performance issue with the function, the problem is in how it is being used.
0 Likes 0 ·
Anil kumar avatar image
Anil kumar answered

Package: A Collection of related variables,cursor,procedure,and functions Package will support oops features like encaplution and data hiding and function overloading(8.0), to declare package in 2 steps 1.package specification 2.package body SubPrograms: A set of pl/sql statements is stored in database and used to perform a task; there are 2 types of subprograms 1.Procedures 2.Functions Procedure:A subprogram type perform a task and will not return value. and used to perform dml operations on database Function:A subprogram type perform a task and may or may not return value,but it will return only one value.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nrenyadav avatar image
nrenyadav answered
-procedures accept 'in,out,inout' mode of parameter while function use 'in' mode. -procedure may or not return one or more value but function return a value. -procedure can not user return keyword but in function we use return keyword. -procedure can not execute in select statement but function can execute in select statement. -we can perform commit operation in procedure but in funtion we can not perform commit operation(using pragma autonomous_transaction we can perform commit operation). -package is collection of procedure,function,cursor which follow oop concept like encapsulation,data hiding.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Metmuler avatar image
Metmuler answered
The most important difference between procedure and a function is: procedure is compiled only once. Function is compiled every time you call it. So basically stored procedure is more efficient than function. Both function and procedure return a value. As mentioned above, package is like a container for function and stored procedure. It is like a way to define API. Once you define your Package, you can access it from outside just alike any API.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Metmuler I understand that you say if my function and procedure are within a package I can call/access them from anywhere. Does this mean now even on Java I can just access it?
0 Likes 0 ·
ecomma avatar image
ecomma answered
Check the difference between Storeprocedure and function [On stackoverflow site with samples][1] [1]: http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Omprakash avatar image
Omprakash answered

Why procedure does not return statement? If we are using return statement what type of error.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.