Basic Function

I'm training for a half-marathon, but have to run a certain number of miles per hour to be able to qualify. Can I do it?

Two values get passed to the function in the order in which they are presented. The function returns one value to the program.

The Function:

Create Or Replace
 Function My_Speed
   (My_Mileage Number
   ,My_Time    Number
   )
Return Number
Is
Begin
  Return My_Mileage/My_Time;
End;
/

Calling the Function:

Begin
   DBMS_Output.Put_Line ('MPH: '
                        || My_Speed(&Miles,&Hours)
                        );
End;
/

Function to Capitalize Correctly with 'Mc' or 'Mac'

Select    EName
     , Mc(EName)
  From Emp
 Where EName Like 'M%'

ENAME           MC(ENAME)
--------------- ---------------
MCNEIL          McNeil
MACINTYRE       MacIntyre
MARTIN          Martin
MILLER          Miller

This is a start at capitalizing names correctly. This function will look at the name and capitalize in two parts if it finds either 'MC' or 'MAC' in the first two to three characters.

Create Or Replace Function Mc(Name Varchar2)
Return Varchar2
Is
  Begin
    Return(Case ---------------------------------------
                -- When the name starts with 'MC' or 
                -- 'MAC'...
                ---------------------------------------
                When Upper(Substr(Name,1,2))='MC' 
                  Or Upper(Substr(Name,1,3))='MAC'
                ---------------------------------------
                -- InitCap in two sections. The first 
                -- is up to the first 'C' found. The
                -- 2nd is from the first 'C' to the end.
                ---------------------------------------
                Then Initcap
                           (Substr
                                 (Name,1,
                                  Instr(Upper(Name)
                                       ,'C'
                                       ,1)
                                       )
                                  )
                  || Initcap
                           (Substr
                                 (Name
                                 ,Instr(Upper(Name)
                                       ,'C'
                                       ,1)+1
                                  )
                           )
                ---------------------------------------
                -- If not, 'MC' or 'MAC' InitCap the 
                -- whole name.
                ---------------------------------------
                Else Initcap(Name)
           End);
  End;
/

Embedded Function

Declare
    Function fHired (vHireDate Date)   
    Return Number
        Is 
            Begin                              
                Return Round((Sysdate-vHireDate)/365);
            End;          
Begin
   For vRec In (Select Empno, Hiredate 
                  From Emp) 
   Loop
   Dbms_Output.Put_Line
     ('Emp # '         || vRec.Empno             
    ||' Hired '        || To_Char(vRec.Hiredate,'Yyyy')
    ||' Years worked: '|| fHired(vRec.Hiredate));
   End Loop;
End;
/

Function

Create or Replace Function f_CkSal(Amt Number)
Return Boolean 
Is
   Begin
      Return ( Amt Between 100 And 1000);
   End;                                 
/
Select * 
  From User_Source 
 Where Name = 'F_CKSAL';

Declare
   Sal   Number(8,2)  := &Salary;
   Flag  Boolean;
Begin
   Flag := F_CkSal(Sal);
   If Flag                    /* or "If Flag = True" */
      Then Dbms_Output.Put_Line ( Sal || ' Good' );
      Else Dbms_Output.Put_Line ( Sal || ' Bad'  );
   End If;
End;
/