| Procedure [message #472975] |
Wed, 25 August 2010 02:57  |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
If we ask any body the difference between Procedure and Function
most of the people will tell .
Function returns a value directly and Procedure returns a value indirectly by using OUT parameters.
CREATE OR REPLACE PROCEDURE P1
Is
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/
In this code I am not using any OUT parameters ,without using OUT parameters we are retrieving salary of an employee.
I think according to my code this difference is INVALID.
Any body could you please tell me ,my assumption is correct or not.
Please help me.
|
|
|
|
| Re: Procedure [message #472976 is a reply to message #472975] |
Wed, 25 August 2010 03:07  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
That still doesn't "return a value" in a technological sense. It writes a line to output.
A more distinct difference would be that you can use a function in a SQL statement, and it returns a value there.
|
|
|
|
| Re: Procedure [message #472980 is a reply to message #472975] |
Wed, 25 August 2010 03:25 |
 |
Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Your procedure uses a "side effect", that is modifies something which is not in its context, here the dbms_output buffer.
The question is then irrelevant.
Regards
Michel
[Updated on: Wed, 25 August 2010 03:56] Report message to a moderator |
|
|
|
| Re: Procedure [message #472989 is a reply to message #472976] |
Wed, 25 August 2010 03:51 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ThomasG wrote on Wed, 25 August 2010 09:07
A more distinct difference would be that you can use a function in a SQL statement, and it returns a value there.
Except that that isn't accurate since not all functions can be called from SQL.
|
|
|
|
|
|
|
|
| Re: Procedure [message #474321 is a reply to message #474319] |
Sun, 05 September 2010 10:03 |
|
Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
ramesh55.sse wrote on Sun, 05 September 2010 16:48 "return a value" is different from writes a line to output.
Could you please explain to me.
Read again my post... and the links we already provided you many times... but I think it is useless ro repeat it as you have not the skills to be able to understand what we say.
Regards
Michel
[Updated on: Sun, 05 September 2010 10:04] Report message to a moderator |
|
|
|
| Re: Procedure [message #474324 is a reply to message #472975] |
Sun, 05 September 2010 11:48 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
According to the Oracle online documentation here:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#sthref779
"The only difference between procedures and functions is that functions always return a single value to the caller, while procedures do not return a value to the caller."
Your confusion seems to be in understanding what return means. It means actually using the return keyword. Every function must have:
CREATE FUNCTION function_name
RETURN data_type
AS
BEGIN
RETURN value_of_data_type;
END;
/
Some functions may be used in a SQL select statement like:
SELECT function_name() FROM table_name;
Some functions may only be called from SQL*Plus or PL/SQL, if they do things like DML that aren't allowed in a select statement, like:
VARIABLE variable_name data_type
EXEC :variable_name := function_name()
or:
DECLARE
variable_name data_type;
BEGIN
variable_name := function_name();
END;
/
Although a function must return a single value, that value can be an object or a ref cursor that contains multiple values.
A procedure does not use the return keyword to return a value. A procedure may or may not output data by different means, such as using an out parameter or using dbms_output to print something to the screen, but these do not constitute "returning" a value, and they are not required in order to be considered a procedure.
CREATE PROCEDURE procedure_name
(out_parameter OUT data_type) -- optional
AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('whatever'); -- optional
END;
/
Functions can also have out parameters and use dbms_output. A procedure cannot be used in a select statement. A procedure can be called from SQL*Plus:
VARIABLE variable_name data_type
EXEC procedure_name (:variable_name)
or a procedure can be called from pl/sql:
DECLARE
variable_name data_type;
BEGIN
procedure_name (variable_name);
END;
/
An easy way to tell one from the other is if you see the word return in it, then it is a function. If you do not see the word return in a named stored PL/SQL block, then it is a procedure, not a function.
|
|
|
|
| Re: Procedure [message #474326 is a reply to message #474324] |
Sun, 05 September 2010 12:26 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Sun, 05 September 2010 12:48An easy way to tell one from the other is if you see the word return in it, then it is a function. If you do not see the word return in a named stored PL/SQL block, then it is a procedure, not a function.
SQL> create or replace
2 procedure p1
3 is
4 begin
5 return;
6 end;
7 /
Procedure created.
SQL>
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
