What is a function?
A function is an entity that accepts inputs and returns output.
Syntax
CREATE OR REPLACE FUNCTION <Function Name > [argument list[In | Out | InOut ] DataType ]
RETURN ReturnType [IS | AS]
BEGIN
Function Body
END
Example 1: A simple function to return sum of two numbers
SQL> Create or Replace Function Addition
2 (
3 Num1 IN INT,
4 Num2 IN INT
5 )
6 Return INT Is
7
8 -- Variable declarations
9 result INT;
10 Begin
11 result := Num1 + Num2;
12 Return result;
13 End Addition;
14 /
Function created.
Next we need to invoke the function.So do it as shown under
SQL> Begin
2 DBMS_OUTPUT.PUT_LINE('The result is : ' || Addition(10,20));
3 End;
4 /
The result is : 30
PL/SQL procedure successfully completed.
Example 2: Overloaded function
We can overload the above defined function as shown under
SQL> Create or Replace Function Addition
2 (
3 Num1 IN INT,
4 Num2 IN INT,
5 Num3 IN INT
6 )
7 Return INT Is
8
9 -- Variable declarations
10 result INT;
11 Begin
12 result := Num1 + Num2 + Num3;
13 Return result;
14 End Addition;
15 /
Function created.
Next we need to invoke the function.So do it as shown under
SQL> Begin
2 DBMS_OUTPUT.PUT_LINE('The result is : ' || Addition(10,20,30));
3 End;
4 /
The result is : 60
PL/SQL procedure successfully completed.
Example 3: Create a function based on %Type
SQL> Create or Replace Function GetPlayerName(V_PlayerID tblPlayers.PlayerID%Type)
2 Return VARCHAR2
3 AS
4 V_PlayerName VARCHAR(50);
5 BEGIN
6 SELECT
7 PlayerFirstName || ' ' || PlayerLastName
8 INTO
9 V_PlayerName
10 FROM
11 tblPlayers
12 WHERE
13 PlayerID = V_PlayerID;
14 RETURN V_PlayerName;
15 END;
16 /
Function created.
The method of invocation is as under
SQL> Begin
2 DBMS_OUTPUT.PUT_LINE('For Player ID : ' || 10 || ' the Player Name is ' || GetPlayerName(10));
3 End;
4 /
For Player ID : 10 the Player Name is J Jwar
PL/SQL procedure successfully completed.
Find errors in Functions
Now we have seen as how to create functions in Oracle. Now let us look as how to find errors in our function. Let us observe the below statement
SQL> Create OR Replace function Subtraction
2 (
3 Num,1 INT,
4 Num2 INT
5 )
6 AS
7 BEGIN
8 RETURN NUM1 - NUM2;
9 END Subtraction;
10 /
Warning: Function created with compilation errors.
We have deliberately written some wrong stuff in our function and while compile, it shows that the function has been created with compilation error but not the precise error. For obtaining the exact errors, we need to use SHOW ERROR statment as ahown under
SQL> SHOW ERROR;
Errors for FUNCTION SUBTRACTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/6 PLS-00103: Encountered the symbol "," when expecting one of the
following:
in out <an identifier> <a double-quoted delimited-identifier>
... LONG_ double ref char time timestamp interval date binary
national character nchar
Importance of USER_OBJECTS system table
One point to note is , which system table these functions will be available. Well we can query the USER_OBJECTS tables for that.
SQL> Select
2 Object_Name,
3 Object_ID,
4 Object_Type,
5 Created
6 From User_Objects
7 Where Object_Type = 'FUNCTION';
OBJECT_NAME OBJECT_ID OBJECT_TYPE CREATE
---------------------------------------------------
MYFUNC 53240 FUNCTION 15-SEP
COLLECTION2STRING 53249 FUNCTION 17-SEP
ADDITION 53409 FUNCTION 27-SEP
GETPLAYERNAME 53416 FUNCTION 28-SEP
Importance of USER_SOURCE system table
We can obtain the source code of the function from USER_SOURCE table
SQL> Select Text
2 From User_Source
3 Where Name='GETPLAYERNAME'
4 Order By Line;
TEXT
------------------------------------------------------------
Function GetPlayerName(V_PlayerID tblPlayers.PlayerID%Type)
Return VARCHAR2
AS
V_PlayerName VARCHAR(50);
BEGIN
SELECT
PlayerFirstName || ' ' || PlayerLastName
INTO
V_PlayerName
FROM
tblPlayers
WHERE
PlayerID = V_PlayerID;
RETURN V_PlayerName;
END;
15 rows selected.
Perform DML operations from function
There is a major difference between Oracle user define function and Sql Server User define function. In Oracle, we can perform DML operations from functions which is not at all possible from Sql Server prespective. Let us see an example. We already have our student table as under
SQL> Select * from tblStudents;
STUDENTID STUDENTNAME MARKS1 MARKS2 MARKS3 MARKS4
---------- --------------- ---------- ---------- ---------- ----------
1 A.Raman 45 50 30 88
2 B.Sekhar 56 84 99 100
3 C.Das 32 22 78 34
4 D.Nath 58 67 11 55
5 E.Elizabeth 99 92 69 89
Now we will insert a record to it by using a function
SQL> Create or Replace Function fn_InsertStudentTable
2 Return INT AS
3 flag INT;
4 Begin
5 Insert Into tblStudents (StudentID,StudentName,Marks1,Marks2,Marks3,Marks4)
6 Values(6,'F.Faran',50,60,70,80);
7 flag := 1;
8 return flag;
9 End fn_InsertStudentTable;
10 /
Function created.
As can be figure out that we are inserting one record via the function fn_InsertStudentTable into tblStudents.Let us now invoke it as under
SQL> Begin
2 DBMS_OUTPUT.PUT_LINE('Invoking insert function :' || fn_InsertStudentTable());
3 End;
4 /
PL/SQL procedure successfully completed.
Projecting the record from tblStudents yield the below
SQL> select * from tblStudents;
STUDENTID STUDENTNAME MARKS1 MARKS2 MARKS3 MARKS4
---------- --------------- ---------- ---------- ---------- ----------
1 A.Raman 45 50 30 88
2 B.Sekhar 56 84 99 100
3 C.Das 32 22 78 34
4 D.Nath 58 67 11 55
5 E.Elizabeth 99 92 69 89
6 F.Faran 50 60 70 80
6 rows selected.
So we have added a new row to the tblStudents by using function. We can perform any other DML operations by this way.
Drop a function
We use the DROP function statement for droping a function.e.g.
SQL> Drop Function MyFunc;
Function dropped.
Hope this is useful. Thanks for reading