PLSQL Function

PLSQL Function is a PLSQL block with a name and declared with FUNCTION keyword. It may accept some input arguments, and perform some activities. A function always returns a value. So, when a function is called the value is need to be stored in a variable.

Syntax of PLSQL Function

CREATE  OR REPLACE FUNCTION func_name([param1,[param2,],…..]) RETURN datatype
AS/IS
[ local variable declaration]
 BEGIN 
function statements 
[EXCEPTION 
exception handlers ] 
RETURN value;
END [func_name];

A PLSQL function is created by CREATE statement with FUNCTION keyword. Including OR REPLACE keywords in function header will replace the existing function definition with its new definition. In absence of OR REPLACE, if the function already exists, an error message will be displayed. The message is about failure to create due to name being already in use.  In case you omit OR REPLACE keywords, you need to remove the existing function definition from schema using DROP statement.

Function Parameters, Local variable declaration and exception section of the function are optional. Function name is mandatory since it is used to be call it in other PLSQL blocks.

A function can be declared with no parameters or one or more parameters. A function, like a PLSQL Block, must have these two sections.

• Header section– It precedes AS/ IS keyword.  It must have name of the function and names, types and data types of the parameters. Data types can be those allowed in Oracle.
• Body section – It starts from AS keyword. The local variables used in the PLSQL Function are declared as the first thing and are optional. After the BEGIN keyword logic of the actions to be implemented for the function is given as PLSQL statements. It may include the Exception Section starting with keyword EXCEPTION. The last statement of the BODY section must be a return statement with RETURN keyword followed by the variable/expression or value to be returned.

Calling the Function

A declared and successfully compiled PLSQL Function can be called in another PLSQL block like an anonymous block, procedure or function. It can be directly called by specifying a variable on left hand side of the assignment operator followed by name of function and the actual arguments. After execution of the function the returned value is stored in this variable.

v_max_sal:= max_sal(10)

Use of PLSQL Functions

Functions are created as middle layer for an application. The functions can be called in other blocks, Procedures, functions or in the front end tool code.  The compiled functions in the schema of a database can be called any number of times from different applications. They can be called simultaneously from different user interfaces. The use of PLSQL function is reusability and structuring of database applications.

Type of Parameters

We discussed the types of parameters in the post on PLSQL procedures. IN, OUT and IN OUT parameters can be also be passed in Functions.

Examples of PLSQL Function

The first function uses the “match” table that stores the details about runs scored by a cricketer in a specific match. The following function returns the max runs among all the records. Function is created without input arguments.

CREATE OR REPLACE FUNCTION MAX_RUNS    
RETURN NUMBER    
IS    
N3 NUMBER(8);    
BEGIN    
SELECT MAX(RUNS) INTO N3 FROM MATCH;
RETURN N3;    
END;

Calling the function-

DECLARE 
RES NUMBER;
BEGIN
RES:=MAX_RUNS;
DBMS_OUTPUT.PUT_LINE(RES);
END;

The following function returns the max runs among all the records less than the numeric argument is passed.  Function is created with one input argument a value.

CREATE OR REPLACE FUNCTION MAX_RUNS(VAL IN NUMBER)    
RETURN NUMBER    
IS     
N3 NUMBER(8);    
BEGIN    
SELECT MAX(RUNS) INTO N3 FROM MATCH WHERE RUNS<VAL;
RETURN N3;    
END;

Calling the function-

DECLARE 
RES NUMBER;
BEGIN
RES:=MAX_RUNS(100);
DBMS_OUTPUT.PUT_LINE(RES);
END;

The following function returns the max salary among all the records of employees in a department working at a specific job position. The function accepts a department id and job id as argument.  Function is created with two input arguments a number and a varchar string.

CREATE OR REPLACE FUNCTION MAX_SAL(DID NUMBER, JID VARCHAR2)    
RETURN NUMBER    
IS     
MX NUMBER(8);    
BEGIN    
SELECT MAX(SALARY) INTO MX FROM EMPLOYEES WHERE DEPARTMENT_ID=DID AND JOB_ID=JID ;
RETURN MX;    
END;

Calling the function-

DECLARE 
RES NUMBER;
BEGIN
RES:=MAX_SAL(50,'ST_CLERK');
DBMS_OUTPUT.PUT_LINE(RES);
END;

DECLARE 
RES NUMBER;
BEGIN
RES:=MAX_SAL(60,'IT_PROG');
DBMS_OUTPUT.PUT_LINE(RES);
END;

admin