PLSQL

PL/SQL Functions

PL/SQL Functions

Functions are said to be the same as procedure, except function returns a value. 

Syntax:
CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

Where 

  1. function_name is the name of the function.
  2. [OR REPLACE] allows us to modify an existing function.
  3. Function must contain return statement.
  4. RETURN clause specifies the data type that we are going to return.
  5. AS keyword is used instead of IS, for creating standalone function.

Example: creating table 

CREATE TABLE TEACHERS (

 ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADRESS CHAR (25)

SALARY DECIMAL (18,2)

PRIMARY KEY (ID)

);

 Let us now insert some values in the table-

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (1, ‘RANJITHA’, 34, ‘BANGALORE’, 2000.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (2, ‘SAI’, 30, ‘HYDERABAD’, 3000.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (3, ‘SHAKTHI’, 34, ‘PUNE’, 2500.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (4, ‘GAYATHRI’, 36, ‘BANGALORE’, 4000.00);

INSERT INTO TEACHERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES (5, ‘VIGNESH’, 34, ‘MUMBAI’, 4500.00);

Select * from teachers

 

ID

NAME

AGE

ADDRESS

SALARY

1

Ranjitha

34

Bangalore

2000

2

Sai

30

Hyderabad

2500

3

Shakthi

34

Pune

3000

4

Gayathri 

36

Bangalore

4000

5

Vignesh

34

Mumbai

4500

CREATE OR REPLACE FUNCTION totalteachers 
RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM teachers; 
   RETURN total; 
END; 
/
When we execute above code, we get following result:
Function created.

Calling a Function

When we are creating a function, we provide the definition of what the function has to do. For using a function, we should call a function for performing the defined task. And then when the program calls, the program control is transferred for the called function. And then the called function performs the defined task and when the return statement is executed or if the last end statement is reached, control goes back to the main program.

Example:
DECLARE 
   t number(2); 
BEGIN 
   t := totalteachers(); 
   dbms_output.put_line('Total no. of teachers: ' || t); 
END; 
/
Output:
Total no. of teachers: 5
PL/SQL procedure successfully completed.

PL/SQL RECURSIVE Functions

When subprogram calls itself, it is referred to as recursive call and this process is known to be recursion.
Example: 
DECLARE 
   num number; 
   factorial number;  
   
FUNCTION fact(x number) 
RETURN number  
IS 
   f number; 
BEGIN 
   IF x=0 THEN 
      f := 1; 
   ELSE 
      f := x * fact(x-1); 
   END IF; 
RETURN f; 
END;  

BEGIN 
   num:= 3; 
   factorial := fact(num); 
   dbms_output.put_line(' Factorial  of '|| num || ' is ' || factorial); 
END; 
/
Output:
Factorial of 3 is 6 
PL/SQL procedure successfully completed.