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
- function_name is the name of the function.
- [OR REPLACE] allows us to modify an existing function.
- Function must contain return statement.
- RETURN clause specifies the data type that we are going to return.
- 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.