Opening Hours :7AM to 9PM
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,
Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+
CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END; /When the above code is executed using the SQL prompt, it will produce the following result
DECLARE c number(2); BEGIN c := totalCustomers(); dbms_output.put_line('Total no. of Customers: ' || c); END; /Output:
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; /When the above code is executed at the SQL prompt, it produces the following result
n! = n*(n-1)! = n*(n-1)*(n-2)! ... = n*(n-1)*(n-2)*(n-3)... 1The following program calculates the factorial of a given number by calling itself recursively
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:= 6; factorial := fact(num); dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); END; /Output: