| questions about creating function [message #643616] |
Tue, 13 October 2015 16:12  |
 |
wisdomofmetis
Messages: 2 Registered: October 2015 Location: istanbul
|
Junior Member |
|
|
Helllo, i didn't understand the below code from tutorialspoint, i read and see many examples about creating function. However, in the below example, function is created without create keyword.Is it correct? Also function is defined inside an anonymous block. I didn't find any document which tells about them. Can you please explain me?
The following is one more example which demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
In the ORACLE's site, create statement is given as below :
[CREATE [OR REPLACE ] ]
FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN
datatype
[ AUTHID { DEFINER | CURRENT_USER } ]
[ PARALLEL_ENABLE
[ { [CLUSTER parameter BY (column_name [, column_name ]... ) ] |
[ORDER parameter BY (column_name [ , column_name ]... ) ] } ]
[ ( PARTITION parameter BY
{ [ {RANGE | HASH } (column_name [, column_name]...)] | ANY }
) ]
]
[DETERMINISTIC] [ PIPELINED [ USING implementation_type ] ]
[ AGGREGATE [UPDATE VALUE] [WITH EXTERNAL CONTEXT]
USING implementation_type ] {IS | AS}
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ local declarations ]
BEGIN
executable statements
[ EXCEPTION
exception handlers ]
END [ name ];
Does [] means that optional? Then [CREATE [OR REPLACE ] ] means that followings are possible for creating a function:
- create function myfunc ...
- create or replace function myfunc ...
- myfunct function ..
Also please don't forget my other question about creating a function inside anonymous block Is it possible to create a function inside another function(named block)?
[Updated on: Tue, 13 October 2015 16:14] Report message to a moderator
|
|
|
|
| Re: questions about creating function [message #643618 is a reply to message #643616] |
Tue, 13 October 2015 16:24   |
John Watson
Messages: 9003 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote: However, in the below example, function is created without create keyword.Is it correct? If you want to know if something is correct, just try it:orclz>
orclz> set serverout on
orclz>
orclz> DECLARE
2 a number;
3 b number;
4 c number;
5 FUNCTION findMax(x IN number, y IN number)
6 RETURN number
7 IS
8 z number;
9 BEGIN
10 IF x > y THEN
11 z:= x;
12 ELSE
13 Z:= y;
14 END IF;
15
16 RETURN z;
17 END;
18 BEGIN
19 a:= 23;
20 b:= 45;
21
22 c := findMax(a, b);
23 dbms_output.put_line(' Maximum of (23,45): ' || c);
24 END;
25 /
Maximum of (23,45): 45
PL/SQL procedure successfully completed.
orclz> Seems OK to me
And, yes, a clause enclosed within [] is optional - depending on the context, and what you want to do.
|
|
|
|
| Re: questions about creating function [message #643619 is a reply to message #643616] |
Tue, 13 October 2015 16:26   |
 |
Littlefoot
Messages: 21826 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote:
function is created without create keyword.Is it correct? Also function is defined inside an anonymous block
Yes, as it belongs to a PL/SQL block; it is a "private" function (i.e you can use it within that block, but can't call it from outside.
[] means "optional". If you, for example, "CREATE FUNCTION f_xyz" and you want to modify it a second later, it'll fail because a function (actually, an object) with the same name already exists. Therefore, you first have to "DROP FUNCTION f_xyz" and then "CREATE FUNCTION f_xyz" again. In order to shorten the process, you "CREATE OR REPLACE" the function (create if it doesn't exist, replace if it already exists).
The third option you mentioned is wrong (you can't create it that way), except - as your previous example shows - as a part of a PL/SQL block (or package).
|
|
|
|
| Re: questions about creating function [message #643637 is a reply to message #643619] |
Wed, 14 October 2015 09:48  |
 |
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
Quote:Also please don't forget my other question about creating a function inside anonymous block Smile Is it possible to create a function inside another function(named block)?
Yes, you can have Program Units (Function or Procedure) declared within another Program Unit. As LittleFoot stated, Program Units declared within the "Declaration" section of another (Parent) Program Unit makes it Private to the Parent program unit - meaning it is only visible (in scope) to the Parent Program Unit.
Craig...
|
|
|
|