Creating functions and procedures in Oracle

Firstly, start by logging in to your Oracle SQL *Plus. You must have registered for this to be able to do so.

Functions

The following shows how to create a function:

create or replace function getAge(p_dob date) return number
IS
v_age number;
begin
v_age := trunc(months_between(sysdate,p_dob)/12);
return v_age;
end;

To use this function try something like the following:

SELECT getAge('29-oct-1980') FROM dual;

Procedures

The following shows how to create a procedure:

create or replace procedure overtime(wh number)
is
ot number;
begin
if wh > 35 then
ot := wh - 35;
else
ot := 0;
end if;
dbms_output.put_line('Overtime :' || ot || ' hours');
end;

To use this procedure try something like the following:

set serveroutput on
execute overtime(37);

Comments are closed.