How to use function in PL/SQL?

 

Example 1

CREATE OR REPLACE FUNCTION AddItems (a Number, b Number)
Return Number
Is
Total_Sum Number;
Begin
Total_Sum:=a+b;
Return Total_Sum;
End;



Declare
Total_Sum Number;
Begin
Total_Sum:=AddItems(2,2);
dbms_output.put_line('The sum of two number is '|| Total_Sum);
End;

Example 2

create or replace function greaternumber(a Number, b Number)
Return Number
Is
Result Number;
Begin
If a>b then
Result:=a;
Else
Result:=b;
End If;
Return Result;
End;



Declare
Result Number;
Begin
Result:=GreaterNumber(10,5);
dbms_output.put_line('The Greatest number is '|| Result);
End;

Example 3

create or replace FUNCTION salary
RETURN NUMBER IS
    result NUMBER;
BEGIN
    SELECT COUNT(*) INTO result FROM Employees;
    RETURN result;
END;


SELECT salary FROM dual;

Example 4

create or replace FUNCTION get_employee_salary(
    id IN NUMBER
) RETURN NUMBER 
IS
    result NUMBER;
BEGIN
    SELECT Salary INTO result FROM Employees WHERE Employee_ID = id;
    RETURN result;
END;



Declare
Result Number;
Begin
Result:=get_employee_salary(198);
dbms_output.put_line(Result);
End;