Home » SQL & PL/SQL » SQL & PL/SQL » questions about creating function
questions about creating function [message #643616] Tue, 13 October 2015 16:12 Go to next message
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 Smile 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 Go to previous messageGo to next message
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 Smile

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 Go to previous messageGo to next message
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 Go to previous message
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...
Previous Topic: Requirement to extract sql script output and attach the same in a email and send to multiple users
Next Topic: fine tuning my sql query for a grouping report
Goto Forum:
  


Current Time: Wed Jul 15 11:09:10 CDT 2026