Oracle PL/SQL Oracle functions

Syntax for building functions

Create or Replace function name (parameter input or output parameter type) return data type

is return value data type of return value

Begin

Action to be done by the function

End

SQL Statement Demo:

Create or Replace function My_fun (in_no in number) is Out_name VARCHAR2 (64);
Begin
Select Ename to Out_name from EMP where emp.empno=in_no;
return out_name;
End My_fun;

Note: There is no longer end my_fun and end;

Function call:

Declare
Var_name VARCHAR2 (32);
Begin
Var_name:=my_fun (7369);
Dbms_output.put_line (Var_name);
End

Oracle PL/SQL Oracle functions